Pseudocolumn in Oracle SQL

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

Hello everyone, I am Bishal KUMAR Dubey and most importantly an idea creator. I just love Programming languages and love to know new concepts everyday,every minute,every second. Here to help Other GEEKS!!!

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




Article Tags :
Practice Tags :


1


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.