Pseudocolumns in SQL

Pseudocolumns are not really columns in a table; they just have characteristics of columns. These pseudocolumns will return values when referenced just like real table columns. However, you cannot do any other DML or DDL statements on these pseudocolumns. Pseudocolumns are primarily used to give the programmer more tools to use in his code. They provide a convenient way to obtain information about different aspects of the database. The following are the Pseudocolumns we will discuss:

1.CURRVAL

2.NEXTVAL

3.ROWID

4.ROWNUM.

CURRVAL and NEXTVAL Pseudocolumns

The CURRVAL and NEXTVAL pseudocolumns are used in conjunction with sequences. The CURRVAL pseudocolumn returns the current value of the referenced sequence. The NEXTVAL pseudocolumn, when referenced, will increment the sequence value and then return the new sequence value.

To reference the CURRVAL and NEXTVAL pseudocolumns, the SQL dot notation must be used.

For example, the following statement will insert a new record into the Employee
table.
This insert statement will use the next increment of the emp_id_seq sequence for the value to be inserted into the emp_id column.

INSERT into employee
VALUES (emp_id_seq.NEXTVAL,’Stanton Bernard’);

This sample code inserts a single record into the Employee table. The employee
id is created and provide by the emp_id_seq sequence number. The employee
name is hard coded and provide in the insert statement.

ROWID Pseudocolumns

The ROWID pseudocolumn represents the binary address of a row in a table. You can use variables of type UROWID to store rowids in a readable format. In the following example,
you declare a variable named emp_row_id for that purpose:

DECLARE

emp_row_id UROWID;

When you select or fetch a rowid into a UROWID variable, you can use the function ROWIDTOCHAR, which converts the binary value to an 18-byte character string. Then, you can compare the UROWID variable to the ROWID pseudocolumn in the WHERE clause of an UPDATE or DELETE statement to identify the latest row fetched from a cursor.

ROWNUM Pseudocolumns

The ROWNUM pseudocolumn refers to the order in which data was retrieved from a table.For example, ROWNUM of 1 indicates that row was the first row retrieved from the table.

Likewise, ROWNUM of 2333 refers to 2333rd row retrieved from the table. ROWNUM values are assigned when the fetch occurs and are not affected by the ORDER BY clause.

The most common usage of the ROWNUM pseudocolumn is in the WHERE clause statement. For example, you may want to do an interactive select on a table until the ROWNUM is equal to some constant.
In this example, the interaction will continue until the ROWNUM is greater than 100.

DECLARE
CURSOR c1 IS SELECT sal
FROM employee
WHERE sal > 500 AND ROWNUM < 100;

The above cursor declaration code uses the ROWNUM pseudocolumn in the WHERE clause. The ROWNUM is used to limit the number of records processed to the first 99 records in the table.

Written by admin on December 18th, 2010 with comments disabled.
Read more articles on SQL tutorials.

Related articles

Comments disabled

Comments on this article have been disabled.