Oracle7 Server SQL Reference

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

NEXTVAL

LEVEL

ROWID

ROWNUM

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

For more information on referring to database links, see the section "Referring to Objects in Remote Databases" .

If you are using Trusted Oracle7 in DBMS MAC mode, you can only refer to a sequence if your DBMS label dominates the sequence's creation label or if one of these criteria is satisfied:

If the sequence's creation label is higher than your DBMS label, you must have READUP and WRITEUP system privileges

If the sequence's creation label is lower than your DBMS label, you must have WRITEDOWN system privilege.

If the sequence's creation label and your DBMS label are not comparable, you must have READUP, WRITEUP, and WRITEDOWN system privileges.

If you are using Trusted Oracle7 in OS MAC mode, you cannot refer to a sequence with a lower creation label than your DBMS label.

Using Sequence Values

You can use CURRVAL and NEXTVAL in these places:

the SELECT list of a SELECT statement that is not contained in a subquery, snapshot 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

You cannot use CURRVAL and NEXTVAL in these places:

a subquery in a DELETE, SELECT, or UPDATE statement

a view's query or snapshot's query

a SELECT statement with the DISTINCT operator

a SELECT statement with a GROUP BY 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, 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 NEXTVAL.

If a statement contains more than one reference to NEXTVAL for a sequence, Oracle7 increments the sequence once and returns the same value for all occurrences of NEXTVAL. If a statement contains references to both CURRVAL and NEXTVAL, Oracle7 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 the CREATE SEQUENCE command .

Example I

This example selects the current value of the employee sequence:

SELECT empseq.currval

    FROM DUAL

Example II

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 III

This example adds a new order with the next order number to the master order table and then adds sub-orders 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 non-root node. A parent node is any node that has children. A leaf node is any node without children. Figure 2 - 3 shows the nodes of an inverted tree with their LEVEL values.

Figure 2 - 3. Hierarchical Tree

To define a hierarchical relationship in a query, you must use the START WITH and CONNECT BY clauses. For more information on using the LEVEL pseudocolumn, see the SELECT command .

ROWID

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

which data block in the data file

which row in the data block (first row is 0)

which data file (first file is 1)

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. For information on the ROWID datatype, see the section "ROWID 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.

A ROWID does not change during the lifetime of its row. However, 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, Oracle7 may reassign its ROWID to a new row inserted later.

Although you can use the ROWID pseudocolumn in the SELECT and WHERE clauses 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

------------------ ----------

0000000F.0000.0002 SMITH

0000000F.0003.0002 JONES

0000000F.0007.0002 SCOTT

0000000F.000A.0002 ADAMS

0000000F.000C.0002 FORD

ROWNUM

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle7 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

You can also use ROWNUM to assign unique values to each row of a table, as in this example:

UPDATE tabx

    SET col1 = ROWNUM

Oracle7 assigns a ROWNUM value to each row as it is retrieved, before rows are sorted for an ORDER BY clause, so an ORDER BY clause normally does not affect the ROWNUM of each row. However, if an ORDER BY clause causes Oracle7 to use an index to access the data, Oracle7 may retrieve the rows in a different order than without the index, so the ROWNUMs may differ than without the ORDER BY clause.

Note that 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.