SQL & My SQL Tutorial
- What is SQL?
- SQL Microsoft
- SQL Management Tools
- SQL Developer
- Docker Commands
- Composite Key in SQL
- SQL Constraints
- Transactions in SQL Server
- SQL Server Data Types
- SQL Update Join
- SQL Operators
- SQL Clauses
- SQL Commands
- SQL Alter Command
- Distinct Keyword in SQL
- SQL Statements
- SQL Index
- SQL Injections
- Wildcards in SQL
- Alter and Truncate Command in SQL
- SQL Null Functions – ISNULL, IFNULL, Combine, & NULLIF
- SQL Sequence
- How to Find Duplicate Records in SQL
- Primary and Foreign Key in SQL with Examples
- COUNT Function
- SUM Function in SQL
- Dynamic SQL
- Database Tuning
- Pseudocolumn in Oracle SQL
- Triggers in SQL
- Embedded SQL in DBMS
Pseudocolumn in Oracle SQL
What are Pseudo Columns in SQL?
Pseudo Columns are columns that can be recovered by using queries but do not exist on the table physically. They are the alternate forms of variables in the high-level languages.
Here we can call various pseudo columns and we will get the required result from the system.
We can also compare the Pseudo column to the functions which do not have any arguments. When we use pseudo columns we need to take care that we can’t perform any update, delete or insert queries on these columns.
Some of the important Pseudo Columns are as follows:
NEXTVAL in SQL
With the help of Nextval pseudo column we get the flexibility to increment the sequence. We can then access the next value and use it for inserting or any other query on our database. The syntax of the Nextval pseudo column is as follows.
INSERT INTO tableNameVALUES(sequence.Nextval(),value1,value2,….);
CURRVAL in SQL
When we use the Currval pseudo column we get the flexibility to find the current value of our sequence. We can then access the current value and use it for functions and operations in our queries.
SELECT sequence.CURRVAL() FROM tableName;
SYSDATE in SQL
With the help of Sysdate pseudo column we can find the current date of our system. This helps us to run a quick check on our system.
SELECT SYSDATE() as an alias;
We use the ROWNUM to get the desired number of rows in the order they are inserted in the database. The syntax of the Rownum pseudo column is as follows.
SELECT * FROM tableName where ROWNUM condition;
ROWID in SQL
We use the ROWID pseudo column in our queries to find the address of the row which is the result of any particular query. The syntax and usage of the ROWID pseudo column are as follows.
SELECT rowid(columnName) as alias from tableName;
USER in SQL
We use the USER pseudo column to pull the name of the user of our database. With the help of the USER pseudo column, we can find who is currently accessing our database. This helps us to increase the security of our system and monitor it, to avoid any illegal access to our database.
SELECT USER() AS alias;
Uses of Pseudo Columns in SQL
Some of the important uses of the Pseudo Columns are as follows:
- Used to get the system-related data.
- Helps us to get the metadata of our database.
- Allow us to get the details of access rights on our database.
- Helps us to keep a check on our database.