What is Pseudocolumn in Oracle SQL? Learn with Examples
Earlier, we discussed Duplicate Records in SQL. Today, we will see Pseudocolumns in Oracle SQL. Also, we will understand the 4 different types of Pseudocolumn with their examples.
So, let us start SQL Pseudocolumn tutorial.
Keeping you updated with latest technology trends, Join DataFlair on Telegram
1. What is a Pseudocolumn in SQL?
A Pseudocolumn which may behave like a table column but actually it is not a part of a table or stored in a table. The values of a Pseudocolumn cannot be inserted, updated or deleted but you can select from Pseudocolumns. A function and Pseudocolumns are same but without the argument.
The examples of Pseudocolumns are –
We will study the following pseudocolumns-
- CURRVAL and NEXTVAL
2. Types of Pseudocolumn
i. CURRVAL and NEXTVAL Pseudocolumn in Oracle
This is a sequence with schema objects that can generate values which are unique and sequential. We often use these values as unique and primary keys.
The Pseudocolumns can refer in sequence values as –
CURRVAL – This returns the current value
NEXTVAL- This Pseudocolumn increment the sequence and thus returns the next value
- To qualify the sequence –
- For referring to the current or next value of a sequence in the schema of another user we must be granted with SELECT or SELECT ANY SEQUENCE system privilege.
- For referring to the value of a sequence on a remote database
ii. LEVEL Pseudocolumn in Oracle
This pseudocolumn returns the level pseudocolumn for each student row by the hierarchical query. For example, it will return 1 for a root node and 2 for a child of a root and so on. In this type in an inverted tree, a root row is the highest row whereas a child row can be any nonroot row.
A row with children is known as parent row, while which doesn’t have is considered as a leaf.
iii. ROWID Pseudocolumn in Oracle
This will return rows’ address for each Row in the database the ROWID pseudo name contains 3 information about every row address
Fileno- It means the table number
Datablockno – It means the space assigned by the engine to save the record
Recordno- It maintains a record number for every record
They are the fastest way to access a single row, and also they can show you how the rows in a table are stored.
They also act as unique identifiers for rows in a table.
SELECT ROWID, ename FROM emp WHERE deptno = 10;
iv. ROWNUM Pseudocolumn in Oracle
Analytical engine dips record of every number of Record inserted by the user in the table and with the help of this SQL clause we can access the data according to the records inserted.
SELECT * FROM EMP WHERE ROWNUM <= 3;
If in any case an ORDER BY clause follows ROWNUM in the same query, then the rows will be reordered by the ORDER BY clause. And also the results can vary depending on the type the rows are accessed.
SELECT * FROM employees WHERE ROWNUM < 11 ORDER BY last_name;
If ORDER BY clause is embed in a subquery and place the ROWNUM condition in the top-level query, then you can force the ROWNUM condition to be applied after the ordering of the rows. This is sometimes referred to as top-N reporting:
SELECT * FROM (SELECT * FROM employees ORDER BY employee_id) WHERE ROWNUM < 11;
Recommended Reading – SQL Stored Procedure
So, this was all in Pseudocolumns in SQL. Hope you liked our explanation.
Hence, in this Pseudocolumn in SQL tutorial, we studied about the different pseudo columns in SQL. Moreover, we discussed 4 Pseudocolumns and that are CURRVAL and NEXTVAL, LEVEL, ROWID, and ROWNUM with their examples and syntax.
Still, if you have any query, ask in the comment tab.
You must check –