[펌] oracle : connect by ~ start with ~

출처 : http://www.psoug.org/reference/connectby.html

다음 내용은 최신버전이 아닐 수 있습니다. 최신의 글은 위 사이트를 참조하세요.

Syntax 1	CONNECT BY [NOCYCLE] <condition> START WITH <condition>
Syntax 2	START WITH <condition> CONNECT BY [NOCYCLE] <condition>
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 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 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; set pagesize 20
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 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 set linesize 121 col emp format a20 col mgr format a20 SELECT "Name", SUM(salary) "Total_Salary" FROM (   SELECT CONNECT_BY_ROOT last_name "Name", salary   FROM employees   WHERE department_id = 110   CONNECT BY PRIOR employee_id = manager_id) GROUP BY "Name"; -- Thanks Colin for the correction
CONNECT_BY_ISCYCLE Pseudocolumn
The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which is also its ancestor. Otherwise it returns 0
	conn hr/hr UPDATE employees SET manager_id = 145 WHERE employee_id = 100; set linesize 121 col path format a50 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 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 it returns 0. This information indicates whether a given row can be further expanded to show more of the hierarchy.
	conn hr/hr 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;
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
	conn hr/hr 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;
SYS_CONNECT_BY_PATH
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 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 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 SELECT name, SUM(salary) "Total_Salary" FROM (   SELECT CONNECT_BY_ROOT last_name name, salary   FROM employees   WHERE department_id = 110   CONNECT BY PRIOR employee_id = manager_id) GROUP BY name;
Demos
Indenting	conn hr/hr 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:
	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:
	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 PRIOR employee_id = manager_id) v WHERE (v.employee_id, v.lev) IN (   SELECT employee_id, 2 FROM employees);