Related Articles

Related Articles

Pseudocolumn in Oracle SQL
  • Difficulty Level : Basic
  • Last Updated : 21 Mar, 2018

Pseudocolumn: A pseudo-column behaves like a table column but is not actually stored in the table. You can select from pseudo-columns, but you cannot insert, update, or delete their values. A pseudo-column is also similar to a function without arguments. This section describes these pseudo-columns:

  • CURRVAL and NEXTVAL
  • LEVEL
  • ROWID
  • ROWNUM
  1. 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.

    Examples:
    SELECT STUDENTSEQ.currval FROM DUAL;
    INSERT INTO STUDENT VALUES (STUDENTSEQ.nextval, ‘BISHAL’, ‘JAVA’, 7902);

  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.
  3. ROWNUM: Oracle engine maintains the number of each record inserted by users in table. By the help of ROWNUM clause we can access the data according to the record inserted.

    Example:
    SELECT * FROM EMP WHERE ROWNUM <= 3;

  4. ROWID:For each row in the database, the ROWID pseudocolumn returns a row\’s address. The ROWID contains 3 information about row address:

    • FileNo : FileNo means Table Number.
    • DataBlockNo : DataBlockNo means the space assigned by the oracle sql engine to save the record.
    • RecordNo : Oracle engine mantains the record number for each record.

    Example:
    SELECT ROWID, ename FROM emp WHERE deptno = 20;

My Personal Notes arrow_drop_up
Recommended Articles
Page :