Definition:
In Oracle PL/SQL, PRIOR refers to a hierarchical operator which is used with CONNECT_BY clause in hierarchical queries. It decides the direction of hierarchy flow based on the condition specified by CONNECT_BYclause. It can exist on either side of the CONNECT_BY equality conditions. PRIOR is most commonly used when comparing column values with the equality operator.
Note that sequence related expressions cannot be used with the PRIOR operator.
Example Syntax:
SELECT
FROM
START WITH
CONNECT BY [PRIOR] condition
Example Usage:
The SQL query below lists the all reportees to all managers in an organisation.
SELECT LEVEL, MGR MANAGER, LPAD(EMPNO,5*(LEVEL-1)) REPORTEE
FROM EMPLOYEE
START WITH MGR IS NULL
CONNECT_BY PRIOR EMPNO=MGR
ORDER SIBLINGS BY EMPNO
/
LEVEL MANAGER REPORTEE
---------- ---------- --------------------
1
2 100 110
3 110 160
4 160 180
3 110 170
2 100 120
3 120 140
3 120 150
2 100 130
Related Links: