Pseudo Column in Oracle

 

Pseudocolumn: A pseudo-column behaves like a table column but is not actually physical stored in the table. You can select from pseudo-columns,put particular where conditions with some particular operators, but we cannot insert, update, or delete their values. A pseudo-column is also similar to a function without arguments.

This section describes these pseudo-columns:

1. CURRVAL and NEXTVAL
2. LEVEL
3. ROWID
4. ROWNUM

EMPNO	ENAME	HIREDATE	DOB	GENDER	SAL	DEPTNO	JOB
1000	Sachin	11-Sep-85	01-Mar-20	M	25000	10	MANAGER
1001	Kumar	01-Aug-82	11-Mar-20	M	125000	10
1002	Sonu	11-Sep-85	01-Mar-20	M	2500	10	CLERK
1003	Chavi	25-Mar-81	15-Feb-20	F	5000	10	CLERK
1004	Ravi	11-Sep-89	01-Mar-19	M	2500	10	CLERK
1005	Vinod	18-Sep-86	01-Mar-20	M	17000	10	DEVELOPER
1006	Neel	28-Jan-88	19-Jul-19	M	27000	10	MANAGER
1007	Manoj	11-Sep-85	09-Mar-18	M	18000	10	DEVELOPER
1008	Amit	11-Sep-80	01-Jan-20	M	10000	10	DEVELOPER
1009	Ram	28-Mar-88	19-Jul-18	M	30000	20	MANAGER

1.    CURRVAL and NEXTVAL: basically there 2 pseudo columns are used with sequence, as we know sequence are also database objects. For containing current and next value we use these 2 pseudo columns.

·         CURRVAL : Returns the current value of a sequence.

·         NEXTVAL : Increments the sequence and returns the next value.

Examples:
SELECT XX_EMP_SEQ.NEXTVAL, XX_EMP_SEQ.CURRVAL  FROM DUAL

Result :- 1, 1

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.

SELECT EMPNO, ENAME,  LEVEL

FROM SCOTT.EMP

START WITH ENAME = 'KING'  

CONNECT BY PRIOR EMPNO = MGR

ORDER BY 3     

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-Dec-80	800		20	Level-3
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30
7521	WARD	SALESMAN	7698	22-Feb-81	1250	500	30
7566	JONES	MANAGER	7839	02-Apr-81	2975		20	Level-1
7654	MARTIN	SALESMAN	7698	28-Sep-81	1250	1400	30
7698	BLAKE	MANAGER	7839	01-May-81	2850		30
7782	CLARK	MANAGER	7839	09-Jun-81	2450		10
7788	SCOTT	ANALYST	7566	09-Dec-82	3000		20	Level-2
7839	KING	PRESIDENT		17-Nov-81	5000		10
7844	TURNER	SALESMAN	7698	08-Sep-81	1500	0	30
7876	ADAMS	CLERK	7788	12-Jan-83	1100		20	Level-3
7900	JAMES	CLERK	7698	03-Dec-81	950		30
7902	FORD	ANALYST	7566	03-Dec-81	3000		20	Level-2
7934	MILLER	CLERK	7782	23-Jan-82	1300		10

3.    ROWNUM: is a pseudo-column that returns the row number in a result set.

Example:

SELECT * FROM XX_EMP_TL

WHERE ROWNUM<6

EMPNO	ENAME	HIREDATE	DOB	GENDER	SAL	DEPTNO	JOB
1000	Sachin	11-Sep-85	01-Mar-20	M	25000	10	MANAGER
1001	Kumar	01-Aug-82	11-Mar-20	M	125000	10
1002	Sonu	11-Sep-85	01-Mar-20	M	2500	10	CLERK
1003	Chavi	25-Mar-81	15-Feb-20	F	5000	10	CLERK
1004	Ravi	11-Sep-89	01-Mar-19	M	2500	10	CLERK

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 (3 Character) means Table Number.

·         DataBlockNo : DataBlockNo(12 Character) means the space assigned by the oracle sql engine to save the record.

·         RecordNo : Oracle engine mantains(3 Character) the record number for each record.

Example:

SELECT ROWID, EMPNO, ENAME, HIREDATE, DOB, GENDER, SAL, DEPTNO, JOB

FROM XX_EMP_TL

ROWID	EMPNO	ENAME	HIREDATE	GENDER	SAL	DEPTNO	JOB
AAnsg+AIsAAEfnjAAA	1000	Sachin	11-Sep-85	M	25000	10	MANAGER
AAnsg+AIsAAEfnjAAB	1002	Kumar	01-Aug-82	M	125000	10
AAnsg+AIsAAEfnjAAC	1008	Sonu	11-Sep-85	M	2500	10	CLERK
AAnsg+AIsAAEfnjAAD	1003	Chavi	25-Mar-81	F	5000	10	CLERK
AAnsg+AIsAAEfnjAAE	1006	Ravi	11-Sep-89	M	2500	10	CLERK
AAnsg+AIsAAEfnjAAF	1005	Vinod	18-Sep-86	M	17000	10	DEVELOPER
AAnsg+AIsAAEfnjAAG	1004	Neel	28-Jan-88	M	27000	10	MANAGER
AAnsg+AIsAAEfnjAAH	1007	Manoj	11-Sep-85	M	18000	10	DEVELOPER
AAnsg+AIsAAEfnjAAI	1001	Amit	11-Sep-80	M	10000	10	DEVELOPER
AAnsg+AIsAAEfnlAAA	1009	Ram	28-Mar-88	M	30000	20	MANAGER

LAB

--====================================================

-- Pesudo Columns

This section describes these pseudo-columns:

1.    ROWNUM

2.    ROWID

3.    LEVEL

4.    CURRVAL and NEXTVAL

5.    CONNECT_BY_ISLEAF

what is ROWNUM, ROWID ? :- these are pesudo columns in oracle at table level.

--====================================================

--ROWNUM

-- What Is the use of ROWNUM

--we can not use > and = operator on Rownum

SELECT * FROM XX_EMP_TL

SELECT ROWNUM, A.* FROM XX_EMP_TL A

WHERE ROWNUM  < 9

--ROWID

-- What Is the use ROWID

--ROWID:- Contains physical address of any records ( File number/Table number(3), data block number(12), record number(3))

--there is no physical column in our table but here we are able to select the ROWNUM value.

SELECT ROWID, EMPNO, ENAME, HIREDATE, DOB, GENDER, SAL, DEPTNO, JOB

FROM XX_EMP_TL

--  NEXTVAL  and CURRVAL

CREATE SEQUENCE XX_EMP_SEQ

CREATE SEQUENCE APPS.XX_EMP_SEQ

  START WITH 1

  MAXVALUE 9999999999999999999999999999

  MINVALUE 1

  NOCYCLE

  CACHE 20

  NOORDER;

select  XX_EMP_SEQ.NEXTVAL from DUAL

SELECT  XX_EMP_SEQ.CURRVAL  FROM DUAL

 SELECT XX_EMP_SEQ.NEXTVAL, XX_EMP_SEQ.CURRVAL  FROM DUAL

--LEVEL

select * from SCOTT.EMP

SELECT EMPNO, ENAME,  LEVEL

FROM SCOTT.EMP

START WITH ENAME = 'JONES'

CONNECT BY PRIOR EMPNO = MGR

ORDER BY 3

select empno, ename

, level

,CONNECT_BY_ISLEAF

from SCOTT.EMP

start with ename = 'JONES'

connect by prior empno = mgr

order by level