Hierarchical Query Pseudo Columns

• CONNECT_BY_ISCYCLE pseudocolumn
• CONNECT_BY_ISLEAF pseudocolumn
• LEVEL pseudo column

1. --CONNECT_BY_ISCYCLEPseudo column:If the child of the current row is also its parent, Then return1. Otherwise it returns0.
2. --CONNECT_BY_ISLEAFPseudo column:If the current row is byCONNECT BYLeaves of conditionally defined trees, Then return1. Otherwise it returns0. This information indicates whether a given row can be further extended to show more hierarchies.
3. SELECT last_name "Employee",
4. connect_by_isleaf "IsLeaf",
5. LEVEL,
6. sys_connect_by_path(last_name, '/') "Path"
7. FROM hr.employees
8. WHERE LEVEL <= 3
9. AND department_id = 80
10. START WITH employee_id = 100
11. CONNECT BY PRIOR employee_id = manager_id
12. AND LEVEL <= 4
13. ORDER BY "Employee", "IsLeaf";
14. --LEVELPseudo column:For each row returned by a hierarchical query, LEVELPseudo column return 1, For root rows,2For the root childLEVELAnd so on. The root row is the highest row in the fallen tree. Subrows are any non-root rows. The parent row is any row with child levels. Leaf rows are any rows without subclasses.

Sequence pseudorandom

• CURRVAL
• NEXTVAL

1. CURRVAL: Returns the current value of the sequence
2. - NEXTVAL: Incremental sequence and returns the next value
3. Sequences are architectural objects that can generate unique sequential values. These values are usually used for primary and unique keys. The sequence values in the SQL statement can be referenced using the following pseudocolumns:
4. CURRVAL and NEXTVAL must be qualified by the name of the sequence:
5. sequence.CURRVAL
6. sequence.NEXTVAL
7. To reference the current or next value of a sequence in another user architecture, SELECT privileges must be granted on the sequence or
8. - SELECTANYSEQUENCE system privileges, and must limit the sequence containing its architecture: (schema for user name) (sequence for sequence name)
9. schema.sequence.CURRVAL
10. schema.sequence.NEXTVAL
11. To refer to the value of a sequence on a remote database, a complete or partial name of a database link must be used to qualify the sequence:
12. schema.sequence.CURRVAL@dblink
13. schema.sequence.NEXTVAL@dblink

Sequence pseudocolumns cannot be used in the following constructions:

1. /
2. *DELETE,SELECTorUPDATESubqueries in statements
3. *Queries for views or instantiated views
4. *HaveDISTINCTOperatorSELECTSentence
5. *HaveGROUP BYClause orORDER BYClauseSELECTSentence
6. *And anotherSELECTStatement combinationUNION,INTERSECTorMINUSSet OperatorSELECTSentence
7. *SELECTSentenceWHEREclause
8. *CREATETABLEorALTERTABLEColumn in statementDEFAULTvalue
9. *CHECKConditions of constraints
10. *in useCURRVALorNEXTVALSingle SQL Statement, All referencedLONGColumns, updated tables, and locked tables must all be in the same database.
11. *If any of these locations contains multiple pairsNEXTVALQuotation, be Oracle Incremental sequence once, And for allNEXTVALThe matches returned the same value..
12. *If any of these locations contains pairsCURRVALandNEXTVALQuotation, be Oracle Incremental sequence, Also forCURRVALandNEXTVALReturns the same value..
13. */
14. --Find the next value of the sequence: Example
15. --This example selects the sample architecturehrNext value in the employee sequence:
16. SELECT hr.employees_seq.nextval
17. FROM DUAL;
18. --Insert sequence values into tables: Example
19. --Incremental Employee Sequence for this example, Use its value to insert into the sample table hr New employees in Chinahr.employees:
20. INSERT INTO hr.employees
21. VALUES (hr.employees_seq.nextval, 'John', 'Doe', 'jdoe', '555-1212',
22. TO_DATE(SYSDATE), 'PU_CLERK', 2500, null, null, 30);
23. --Current values of reuse sequences: Example
24. --This example adds a new order to the next order number in the main order form. Then will suborders Add to the detailed order form:
25. INSERT INTO oe.orders (order_id, order_date, customer_id)
26. VALUES (oe.orders_seq.nextval, TO_DATE(SYSDATE), 106);
27. INSERT INTO oe.order_items (order_id, line_item_id, product_id)
28. VALUES (oe.orders_seq.currval, 1, 2359);
29. INSERT INTO oe.order_items (order_id, line_item_id, product_id)
30. VALUES (oe.orders_seq.currval, 2, 3290);
31. INSERT INTO oe.order_items (order_id, line_item_id, product_id)
32. VALUES (oe.orders_seq.currval, 3, 2381);

VERSION QUERY(Version query) Pseudo column

Version query sequence is only oracle Effective in Flashback Version Query, This is oracle A Form of Flash Back Query:

• VERSIONS_STARTSCN and VERSIONS_STARTTIME: Start the system change number when creating the line version (SCN) or TIMESTAMP. This pseudocolumn identifies the time when the data first has the value reflected in the row version. Use this pseudocolumn identifier oracle Flashback table or oracle Flash back the query's past target time. If this pseudo-listed NULL, The line version is created before the start.
• VERSIONS_ENDSCN and VERSIONS_ENDTIME: When the line version expires, SCN or TIMESTAMP. If pseudo-listed NULL, Then the line version is current at query time, Or the row corresponds to DELETE Operation.
• VERSIONS_XID: Identifiers for creating row versions of transactions (RAW number).
• VERSIONS_OPERATION: Operations performed by transactions: I For insertion, D To delete, perhaps U For updates. This version inserts, deletes, or updates rows. Namely, stay INSERT Lines after operation, DELETE Lines before operation or by UPDATE The rows affected by the operation.
• User updates for index keys, Oracle Flashback version queries may DELETE and UPDATE Operations are treated as two operations, Namely first DELETE after INSERT, Represented as two version lines.

COLUMN_VALUE pseudocolumn

1. When referring to an XMLTable construct without a "COLUMNS" clause, or when referring to a scalar nested table type using a TABLE set expression, the database returns a virtual table with a single column. The pseudocolumn name is COLUMN_VALUE.
2. -- In the context of XMLTable, the value returned is the data type XMLType.
3. For example, the following two statements are equivalent, and both output shows COLUMN_VALUE as the name of the column to be returned:
4. SELECT *
5. FROM XMLTABLE('<a>123</a>');
6. SELECT COLUMN_VALUE
7. FROM (XMLTable('<a>123</a>'));
8. -- In the context of a TABLE set expression, the returned value is the data type of the set element.
9. CREATE TYPE phone AS TABLE OF NUMBER;
10. /
11. CREATE TYPE phone_list AS TABLE OF phone;
12. /
13. Use COLUMN_VALUE to select from the phone type:
14. SELECT t.column_value FROM TABLE(phone(1, 2, 3)) t;
15. In nested types, pseudocolumns can be used in selection lists and TABLE set expressions
16. SELECT t.column_value
17. FROM TABLE(phone_list(phone(1, 2, 3))) p, TABLE(p.column_value) t;
18. Keyword COLUMN_VALUE is also the name generated by Oracle database for scalar values of internal nested tables, without column or attribute names.
19. -- As shown in the following example. In this context, COLUMN_VALUE is not a pseudocolumn, but the actual column name.
20. CREATE TABLE my_customers (
21. cust_id NUMBER,
22. name VARCHAR2(25),
23. phone_numbers phone_list,
24. credit_limit NUMBER)
25. NESTED TABLE phone_numbers STORE AS outer_ntab
26. (NESTED TABLE COLUMN_VALUE STORE AS inner_ntab);

OBJECT_ID pseudocolumn

• The OBJECT_ID pseudocolumn returns the object identifier of the column of the object table or view. Oracle uses this pseudocolumn as the primary key of the object table. OBJECT_ID is useful for replaceable row IDs in views and INSTEAD OF triggers.
• In earlier versions, this pseudocolumn was called SYS_NC_OID$. The name still supports backward compatibility. However, Oracle recommends that you use the more intuitive name OBJECT_ID.

OBJECT_VALUE pseudocolumn

• The OBJECT_VALUE pseudocolumn returns the system-generated name of the column of an object table, an XML Type table, an object view, or an XML Type view. This pseudocolumn identifies the values of alternative rows in the object table and creates an object view using the WITH OBJECT IDENTIFIER clause.
• In earlier versions, this pseudocolumn was called SYS_NC_ROWINFO$. The name still supports backward compatibility. However, Oracle recommends that you use the more intuitive name OBJECT_VALUE

ORA_ROWSCN pseudocolumn

• ORA_ROWSCN reflects the system change number (SCN) of the latest change to the row. This change can be at the block (coarse) or row (fine-grained) level. The latter is provided by row-level dependency tracking.
• External tables do not support this pseudocolumn

1. --Using pseudo columnseudocolumn Obtain "employees" System change number for the last operation on the table
2. SELECT ORA_ROWSCN, last_name
3. FROM employees
4. WHERE employee_id = 188;
5. --Using pseudo columns andSCN_TO_TIMESTAMPFunction to determine the timestamp of the operation:
6. SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN), last_name
7. FROM employees
8. WHERE employee_id = 188;

ROWID Pseudo column

Although it can be queried SELECT and WHERE Use in Clauses ROWID Pseudo column, But these pseudo column values are not actually stored in the database.Cannot insert, update or delete ROWID Pseudo column value.

1. This statement selects the address of all rows that contain employee data in Department 20:
2. SELECT ROWID, last_name
3. FROM hr.employees
4. WHERE department_id = 20;

ROWNUM pseudo column

When using ROWNUM pseudocolumns, the ROWNUM condition should not be greater than a positive integer.

1. --For each row returned by the query, ROWNUMPseudo column returns a number, Instructions Oracle The order in which rows are selected from a table or join row set.
2. --Selected first lineROWNUMby 1, The second is 2, Wait.
3. --You can useROWNUMTo limit the number of rows returned by the query, As shown in the following example:
4. SELECT * FROM employees WHERE rownum < 11;
5. --IfORDER BY by The clause follows the same queryROWNUM , Will followORDER BY by The clauses are reordered.
6. --Depending on how rows are accessed, The results may vary.
7. --for example, IfORDER BYClause cause oracle Use indexes to access data, be oracle Rows can be retrieved in different order,
8. --Instead of indexing. therefore, The following statement does not necessarily return the same row as the previous example:
9. SELECT * FROM employees WHERE rownum < 11 ORDER BY last_name;
10. --If embedded in a subqueryORDER BY by Clause and willROWNUMConditions are placed in top-level queries,
11. --It can be forced to apply after row sortingROWNUMConditions.
12. --for example, The following query returns a10An employee with the smallest employee number. This is sometimes called the top. N Presentation:
13. SELECT *
14. FROM (SELECT * FROM employees ORDER BY employee_id)
15. WHERE rownum < 11;
16. --In the previous example, ROWNUMThe value is the top.SELECTSentence,
17. --So they are in sub-queriesemployee_idGenerated after sorting.
18. --For integers larger than positive integersROWNUMConditional testing of values is always wrong.
19. --for example, This query does not return any rows:
20. SELECT * FROM employees WHERE rownum > 1;
21. --The first line of extraction is specified as1OfROWNUM , And make the condition as follows false. The second line to extract is now the first line,
22. --And one was assigned.ROWNUM 1 And make the condition as follows false. subsequently, None of the rows met the criteria., So no rows are returned.
23. --You can also useROWNUMAssign unique values to each row in the table, As shown in this example:
24. UPDATE my_table SET column1 = rownum;

XMLDATA Pseudo column

1. Oracle stores XMLType data in LOB or object relational columns based on pseudocolumn information and how to specify storage clauses.
2. -- The XMLDATA pseudocolumn allows you to access the underlying LOB or object relational columns to specify additional storage clause parameters, constraints, indexes, and so on.
3. The following statement illustrates the use of this pseudocolumn. Suppose you use a CLOB column to create a simple XML Type table:
4. CREATE TABLE xml_lob_tab of XMLTYPE
5. XMLTYPE STORE AS CLOB;
6. To change the storage characteristics of the underlying LOB column, you can use the following statement:
7. ALTER TABLE xml_lob_tab
8. MODIFY LOB (XMLDATA) (STORAGE (MAXSIZE 2G) CACHE);
9. Now suppose you have created an XML Schema-based table.
10. For example, the xwarehouses table created by using XML in SQL statements.
11. -- Then, you can use the XMLDATA column to set the properties of the underlying column, as shown in the following statement:
12. ALTER TABLE xwarehouses
13. ADD (UNIQUE(XMLDATA."WarehouseId"));