ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Syntax 1
CONNECT BY [NOCYCLE] <condition> START WITH <condition>
Syntax 2
START WITH <condition> CONNECT BY [NOCYCLE] <condition>
CONNECT BY LEVEL
A condition that identifies the relationship between parent rows and child rows of the hierarchy
CONNECT BY <child_value> = <parent_value>
conn uwclass/uwclass@pdbdev
CREATE TABLE t (
rid NUMBER(12),
col1 VARCHAR2(30),
col2 VARCHAR2(300));
CREATE SEQUENCE seq_t_rid;
INSERT INTO t
SELECT seq_t_rid.NEXTVAL, dbms_crypto.randombytes(15), dbms_crypto.randombytes(150)
FROM dual
CONNECT BY LEVEL <= 1000;
CONNECT BY PRIOR
A condition that identifies the relationship between parent rows and child rows of the hierarchy
CONNECT BY <child_value> = <parent_value>
conn hr/hr@pdbdev
SELECT employee_id, last_name, manager_id
FROM employees
CONNECT BY PRIOR employee_id = manager_id;
START WITH
Specifies a condition that identifies the row(s) to be used as the root(s) of a hierarchical query
START WITH (column_name) = <value>
conn hr/hr@pdbdev
SELECT last_name, employee_id, manager_id, LEVEL
FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id;
SELECT last_name, employee_id, manager_id, LEVEL
FROM employees
START WITH employee_id = 101
CONNECT BY PRIOR employee_id = manager_id;
set pagesize 0
col last_name format a30
SELECT LEVEL, LPAD(' ', LEVEL*3) || LAST_NAME AS LAST_NAME
FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id;
ORDER SIBLINGS BY
SIBLINGS BY preserves any ordering specified in the hierarchical query clause and then applies the order_by_clause to the siblings of the hierarchy
ORDER SIBLINGS BY (column_name);
conn hr/hr@pdbdev
SELECT last_name, employee_id, manager_id, LEVEL
FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;
CONNECT_BY_ROOT
CONNECT_BY_ROOT is a unary operator that is valid only in hierarchical queries. When you qualify a column with this operator, Oracle returns the column value using data from the root row.
Cannot be specified with the START WITH or CONNECT BY condition.
The following example returns the last name of each employee in department 110, each manager above that employee in the hierarchy, the number of levels between manager and employee, and the path between the two.
conn hr/hr@pdbdev
set linesize 121
col emp format a20
col mgr format a20
SELECT last_name
FROM employees
WHERE department_id = 110;
-- incorrect use (from Oracle docs)
SELECT last_name, CONNECT_BY_ROOT last_name MGR,
LEVEL-1 PATHLEN, SYS_CONNECT_BY_PATH(last_name, '/') PATH
FROM employees
WHERE LEVEL > 1
AND department_id = 110
CONNECT BY PRIOR employee_id = manager_id
ORDER BY last_name, MGR, PATHLEN, PATH;
-- correct use
SELECT last_name, CONNECT_BY_ROOT last_name,
LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
FROM employees
WHERE LEVEL <= 3 AND department_id = 80
START WITH last_name = 'King'
CONNECT BY NOCYCLE PRIOR employee_id = manager_id
AND LEVEL <= 4;
CONNECT_BY_ISCYCLE Pseudocolumn
The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which is also its ancestor. Otherwise returns 0.
conn hr/hr@pdbdev
UPDATE employees SET manager_id = 145
WHERE employee_id = 100;
set linesize 121
col path format a50
-- incorrect use
SELECT last_name, LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
FROM employees
WHERE LEVEL <= 3 AND department_id = 80
START WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4;
ERROR:
ORA-01436: CONNECT BY loop in user data
-- correct use
SELECT last_name, CONNECT_BY_ISCYCLE "Cycle", LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
FROM employees
WHERE LEVEL <= 3 AND department_id = 80
START WITH last_name = 'King'
CONNECT BY NOCYCLE PRIOR employee_id = manager_id
AND LEVEL <= 4;
ROLLBACK;
CONNECT_BY_ISLEAF Pseudocolumn
The CONNECT_BY_ISLEAF pseudocolumn returns 1 if the current row is a leaf of the tree defined by the CONNECT BY condition. Otherwise returns 0.
This information indicates whether a given row can be further expanded to show more of the hierarchy.
conn hr/hr@pdbdev
-- incorrect use
SELECT last_name "Employee", CONNECT_BY_ISLEAF "IsLeaf",
LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
FROM employees
WHERE level <= 3
AND department_id = 80
START WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id
AND LEVEL <= 4;
-- correct use
SELECT last_name "Employee", CONNECT_BY_ISLEAF "IsLeaf",
LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
FROM employees
WHERE level <= 3 AND department_id = 80
START WITH last_name = 'King'
CONNECT BY NOCYCLE PRIOR employee_id = manager_id
AND LEVEL <= 4;
LEVEL Pseudocolumn
For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root row, 2 for a child of a root, and so on
LEVEL RETURN NUMBER
conn hr/hr@pdbdev
SELECT employee_id, last_name, manager_id, LEVEL
FROM employees
CONNECT BY PRIOR employee_id = manager_id;
SELECT LPAD(' ',2*(LEVEL-1)) || last_name ORG_CHART,
employee_id, manager_id, job_id
FROM employees
START WITH job_id = 'AD_VP'
CONNECT BY PRIOR employee_id = manager_id;
Returns the path of a column value from root to node, with column values separated by char for each row returned by CONNECT BY condition
SYS_CONNECT_BY_PATH(<column>, <char>)
conn scott/tiger@pdbdev
col empname format a20
col cbp format a30
SELECT LPAD(' ', 2*LEVEL, ' ' ) || ename empName, dname, job,
sys_connect_by_path( ename, '/' ) cbp
FROM emp e, dept d
WHERE e.deptno = d.deptno
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY job;
SELECT LPAD(' ', 2*LEVEL, ' ' ) || ename empName, dname, job,
sys_connect_by_path(empno, '.') cbp
FROM scott.emp emp, scott.dept dept
WHERE emp.deptno = dept.deptno
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename;
Function Demo
Use A Function To Receive The Current Node and Search for Parents of the Current Node
CREATE OR REPLACE FUNCTION permissions_sub_tree_root (
the_id IN NUMBER,
the_level IN NUMBER)
RETURN NUMBER AUTHID DEFINER IS
sub_tree_root NUMBER(10);
BEGIN
SELECT id
INTO sub_tree_root
FROM hierarchy
WHERE level = the_level
-- Connect 'upwards', i.e. find the parent
CONNECT BY PRIOR PARENT = id
START WITH ID = the_id;
RETURN sub_tree_root;
END permissions_sub_tree_root;
/
SELECT id, name, username
FROM (
SELECT ID, PARENT, NAME,
permissions_sub_tree_root(id, LEVEL) ROOT
FROM hierarchy
CONNECT BY PRIOR id = PARENT) HIERARCHY, permissions
WHERE ROOT = hierarchy_id;
GROUP BY Demo
Group By Demo with CONNECT_BY_ROOT and CONNECT_BY_PRIOR
conn hr/hr@pdbdev
SELECT name, SUM(salary) "Total_Salary"
FROM (
SELECT CONNECT_BY_ROOT last_name name, salary
FROM employees
WHERE department_id = 110
CONNECT BY NOCYCLE PRIOR employee_id = manager_id)
GROUP BY name;
Demos
Indenting
conn hr/hr@pdbdev
col lname format a30
SELECT LPAD(' ', LEVEL*2, ' ') || last_name LNAME, d.department_id
FROM employees e, departments d
WHERE e.department_id = d.department_id
START WITH employee_id = 100
CONNECT BY PRIOR e.employee_id = e.manager_id;
Hierarchical Query with IN
In a [NOT] IN condition in a WHERE clause, if the right-hand side of the condition is a subquery, you cannot use LEVEL on the left-hand side of the condition.
However, you can specify LEVEL in a subquery of the FROM clause to achieve the same result. For example, the following statement is not valid:
-- this will fail SELECT employee_id, last_name FROM employees
WHERE (employee_id, LEVEL)
IN (SELECT employee_id, 2 FROM employees)
START WITH employee_id = 2
CONNECT BY PRIOR employee_id = manager_id;
But the following statement is valid because it encapsulates the query containing the LEVEL information in the FROM clause:
-- this will succeed SELECT v.employee_id, v.last_name, v.lev
FROM (
SELECT employee_id, last_name, LEVEL lev
FROM employees v
START WITH employee_id = 100
CONNECT BY NOCYCLE PRIOR employee_id = manager_id) v
WHERE (v.employee_id, v.lev) IN (
SELECT employee_id, 2 FROM employees);
