Pseudo Columns In SQL:
Pseudo columns are associated with table data , but nothing to do with table data and retrieved from database.
But it looks like retrieved from the table data directly .
Most commonly used Pseudo columns in Oracle
It shows the Current date from the local or remore database .
We can use the CURRENT_DATE also with for the same purpose.
SQl>SELECT SYSdate FROM DUAL
Systimestamp function returns the current system date and time
(including fractional seconds and time zone) on your database
SQl>SELECT SYSTIMESTAMP FROM DUAL
Rowid is a pseudo column that uniquely identifies a row within a table,
but not within a database. It is possible for two rows of two different tables stored
in the same cluster to have the same rowid
SQl>SELECT ROWID FROM EMP
Rownum numbers the records in a result set. The first record that meets the where criteria
in a select statement is given rownum=1, and every subsequent record meeting that same criteria increases rownum.
SQl>SELECT ROWNUM FROM EMP WHERE ROWNUM =1;
SQl>SELECT ROWNUM FROM EMP WHERE ROWNUM <=1;
SQl>SELECT ROWNUM FROM EMP WHERE ROWNUM >=10; –o/p: no rows returned SQl>SELECT ROWNUM FROM EMP WHERE ROWNUM >10; –o/p: no rows returned
Rownum does not work with > or >= ineuqlities.
User is a pseudo column that returns the name of the user currently connected to the session.
SQl>SELECT USER FROM DUAL;–o/p :SCOTT
Uid is a pseudo column that returns the id number of a user currently connected to the session.
sql>SELECT UID FROM DUAL;
LEVEL pseudo-column is an indication of how deep in the tree one is.
It is used in hierarchical queries along with CONNECT by clause.
SQl>SELECT level, empno, ename, mgr FROM scott.emp CONNECT BY PRIOR empno = mgr START WITH mgr IS NULL;
NEXTVAL is used to invoke a sequence. If nextval is invoked on a sequence,
it makes sure that a unique number is generated
SQl>SELECT SEQUENCE_NAME.NEXTVAL FROM DUAL;
CURRVAL can only be used if a session has already called nextval on a trigger.
currval will then return the same number that was generated with nextval.
SQl>SELECT SEQUENCE_NAME.CURRVAL FROM DUAL;