|Oracle8i SQL Reference
Release 2 (8.1.6)
Basic Elements of Oracle SQL, 6 of 10
A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values. This section describes these pseudocolumns:
A sequence is a schema object that can generate unique sequential values. These values are often used for primary and unique keys. You can refer to sequence values in SQL statements with these pseudocolumns:
returns the current value of a sequence.
increments the sequence and returns the next value.
You must qualify
NEXTVAL with the name of the sequence:
To refer to the current or next value of a sequence in the schema of another user, you must have been granted either
SELECT object privilege on the sequence or
SEQUENCE system privilege, and you must qualify the sequence with the schema containing it:
To refer to the value of a sequence on a remote database, you must qualify the sequence with a complete or partial name of a database link:
"Referring to Objects in Remote Databases" for more information on referring to database links.
You can use
SELECTlist of a
SELECTstatement that is not contained in a subquery, materialized view, or view
SELECTlist of a subquery in an
VALUESclause of an
SETclause of an
Restrictions: You cannot use
SELECTstatement with the
SELECTstatement with a
SELECTstatement that is combined with another
SELECTstatement with the
WHEREclause of a
DEFAULTvalue of a column in a
Also, within a single SQL statement that uses
NEXTVAL, all referenced
LONG columns, updated tables, and locked tables must be located on the same database.
When you create a sequence, you can define its initial value and the increment between its values. The first reference to
NEXTVAL returns the sequence's initial value. Subsequent references to
NEXTVAL increment the sequence value by the defined increment and return the new value. Any reference to
CURRVAL always returns the sequence's current value, which is the value returned by the last reference to
NEXTVAL. Note that before you use
CURRVAL for a sequence in your session, you must first initialize the sequence with
Within a single SQL statement, Oracle will increment the sequence only once per row. If a statement contains more than one reference to
NEXTVAL for a sequence, Oracle increments the sequence once and returns the same value for all occurrences of
NEXTVAL. If a statement contains references to both
NEXTVAL, Oracle increments the sequence and returns the same value for both
NEXTVAL regardless of their order within the statement.
A sequence can be accessed by many users concurrently with no waiting or locking. For information on sequences, see "CREATE SEQUENCE".
This example selects the current value of the employee sequence:
This example increments the employee sequence and uses its value for a new employee inserted into the employee table:
This example adds a new order with the next order number to the master order table. It then adds suborders with this number to the detail order table:
INSERT INTO master_order(orderno, customer, orderdate) VALUES (orderseq.nextval, 'Al''s Auto Shop', SYSDATE); INSERT INTO detail_order (orderno, part, quantity) VALUES (orderseq.currval, 'SPARKPLUG', 4); INSERT INTO detail_order (orderno, part, quantity) VALUES (orderseq.currval, 'FUEL PUMP', 1); INSERT INTO detail_order (orderno, part, quantity) VALUES (orderseq.currval, 'TAILPIPE', 2);
For each row returned by a hierarchical query, the
LEVEL pseudocolumn returns 1 for a root node, 2 for a child of a root, and so on. A root node is the highest node within an inverted tree. A child node is any nonroot node. A parent node is any node that has children. A leaf node is any node without children. Figure 2-2 shows the nodes of an inverted tree with their
To define a hierarchical relationship in a query, you must use the
"SELECT and Subqueries" for more information on using the
For each row in the database, the
ROWID pseudocolumn returns a row's address. Oracle8i rowid values contain information necessary to locate a row:
Usually, a rowid value uniquely identifies a row in the database. However, rows in different tables that are stored together in the same cluster can have the same rowid.
Values of the
ROWID pseudocolumn have the datatype
Rowid values have several important uses:
You should not use
ROWID as a table's primary key. If you delete and reinsert a row with the Import and Export utilities, for example, its rowid may change. If you delete a row, Oracle may reassign its rowid to a new row inserted later.
Although you can use the
ROWID pseudocolumn in the
WHERE clause of a query, these pseudocolumn values are not actually stored in the database. You cannot insert, update, or delete a value of the
This statement selects the address of all rows that contain data for employees in department 20:
SELECT ROWID, ename FROM emp WHERE deptno = 20; ROWID ENAME ------------------ ---------- AAAAqYAABAAAEPvAAA SMITH AAAAqYAABAAAEPvAAD JONES AAAAqYAABAAAEPvAAH SCOTT AAAAqYAABAAAEPvAAK ADAMS AAAAqYAABAAAEPvAAM FORD
For each row returned by a query, the
ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.
You can use
ROWNUM to limit the number of rows returned by a query, as in this example:
BY clause follows
ROWNUM in the same query, the rows will be reordered by the
BY clause. The results can vary depending on the way the rows are accessed. For example, if the
BY clause causes Oracle to use an index to access the data, Oracle may retrieve the rows in a different order than without the index. Therefore, the following statement will not have the same effect as the preceding example:
If you embed the
BY clause in a subquery and place the
ROWNUM condition in the top-level query, you can force the
ROWNUM condition to be applied after the ordering of the rows. For example, the following query returns the 10 smallest employee numbers. This is sometimes referred to as a "top-N query":
In the preceding example, the
ROWNUM values are those of the top-level
SELECT statement, so they are generated after the rows have already been ordered by EMPNO in the subquery.
Oracle8i Application Developer's Guide - Fundamentals for more information about top-N queries.
Conditions testing for
ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:
The first row fetched is assigned a
ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.
You can also use
ROWNUM to assign unique values to each row of a table, as in this example: