Basic Elements of Oracle SQL, 6 of 10

Pseudocolumns

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:

CURRVAL and NEXTVAL

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:

CURRVAL

returns the current value of a sequence.

NEXTVAL

increments the sequence and returns the next value.

You must qualify CURRVAL and NEXTVAL with the name of the sequence:

sequence.CURRVAL
sequence.NEXTVAL

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 SELECT ANY SEQUENCE system privilege, and you must qualify the sequence with the schema containing it:

schema.sequence.CURRVAL
schema.sequence.NEXTVAL

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:

schema.sequence.CURRVAL@dblink
schema.sequence.NEXTVAL@dblink

See Also:
"Referring to Objects in Remote Databases" for more information on referring to database links.

Where to Use Sequence Values

You can use CURRVAL and NEXTVAL in:

The SELECT list of a SELECT statement that is not contained in a subquery, materialized view, or view
The SELECT list of a subquery in an INSERT statement
The VALUES clause of an INSERT statement
The SET clause of an UPDATE statement

Restrictions: You cannot use CURRVAL and NEXTVAL:

A subquery in a DELETE, SELECT, or UPDATE statement
A query of a view or of a materialized view
A SELECT statement with the DISTINCT operator
A SELECT statement with a GROUP BY clause or ORDER BY clause
A SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator
The WHERE clause of a SELECT statement
DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
The condition of a CHECK constraint

Also, within a single SQL statement that uses CURVAL or NEXTVAL, all referenced LONG columns, updated tables, and locked tables must be located on the same database.

How to Use Sequence Values

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 NEXTVAL.

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 CURRVAL and NEXTVAL, Oracle increments the sequence and returns the same value for both CURRVAL and 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".

Example 1

This example selects the current value of the employee sequence:

SELECT empseq.currval 
    FROM DUAL;

Example 2

This example increments the employee sequence and uses its value for a new employee inserted into the employee table:

INSERT INTO emp
    VALUES (empseq.nextval, 'LEWIS', 'CLERK',
             7902, SYSDATE, 1200, NULL, 20);

Example 3

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);

LEVEL

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 LEVEL values.

Figure 2-2 Hierarchical Tree

To define a hierarchical relationship in a query, you must use the START WITH and CONNECT BY clauses.

See also:
"SELECT and Subqueries" for more information on using the LEVEL pseudocolumn.

ROWID

For each row in the database, the ROWID pseudocolumn returns a row's address. Oracle8i rowid values contain information necessary to locate a row:

the data object number of the object
which data block in the datafile
which row in the data block (first row is 0)
which datafile (first file is 1). The file number is relative to the tablespace.

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 or UROWID.

See Also:
"ROWID Datatype" and "UROWID Datatype".

Rowid values have several important uses:

They are the fastest way to access a single row.
They can show you how a table's rows are stored.
They are unique identifiers for rows in a table.

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 SELECT and 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 ROWID pseudocolumn.

Example

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

ROWNUM

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:

SELECT * FROM emp WHERE ROWNUM < 10;

If an ORDER BY clause follows ROWNUM in the same query, the rows will be reordered by the ORDER BY clause. The results can vary depending on the way the rows are accessed. For example, if the ORDER 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:

SELECT * FROM emp WHERE ROWNUM < 11 ORDER BY empno;

If you embed the ORDER 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":

SELECT * FROM
   (SELECT empno FROM emp ORDER BY empno)
   WHERE ROWNUM < 11;

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.

See Also:
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:

SELECT * FROM emp
    WHERE ROWNUM > 1;

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:

UPDATE tabx
    SET col1 = ROWNUM;

Note:
Using ROWNUM in a query can affect view optimization. For more information, see Oracle8i Concepts.

`CURRVAL`	returns the current value of a sequence.
`NEXTVAL`	increments the sequence and returns the next value.