Oracle - 层次查询
2017-06-13 14:13 abce 阅读(1843) 评论(0) 编辑 收藏 举报如果表中含有层次数据，可以通过使用层次查询有序地查看层次数据。
语法：
condition：指一个或多个表达式和逻辑(布尔)运算符的组合，并返回TRUE、FALSE或UNKNOWN
start with：指定层次查询的根数据行
connect by：指明父行和子行之间的关系：
(1)nocycle：nocycle参数指示数据库返回查询的结果行，即使数据中存在connect by循环。nocycle参数和connect_by_iscycle伪列一起使用，可以查看哪些行数据包含循环。
(2)一个层次查询中，condition中的每个表达式必须有一个prior关键字来限定指向父行。比如：
... PRIOR expr = expr or ... expr = PRIOR expr
如果connect by是组合条件，只有一个条件需要prior操作符；尽管可以使用多个prior操作符。比如：
CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id ...
CONNECT BY PRIOR employee_id = manager_id and
PRIOR account_mgr_id = customer_id ...
prior是一元操作符，和算数操作符"+"、"-"有相同的优先级。prior根据层级查询中的表达式立刻计算出当前行的父行。
prior常用于比较等值操作比较列值(prior关键字可以出现在等值(=)操作符任意一侧)。prior指示oracle使用父行在该列上的值。理论上讲，除了等值(=)操作符，其它任意操作符也是可能出现在connect by语句中，但是这些操作符创建的条件可能会导致无限循环，运行时oracle会检测死循环并返回error消息。
connect by条件和prior表达式可以使用使用非相关子查询。但是prior表达式不可以使用sequence，因为currval和nextval在prior表达式中是无效。
可以使用connect_by_root进一步定义层次查询，不仅返回当前行的父行，还返回所有祖先行。
Oracle处理层次查询的过程：
1.如果有join操作，首先执行join操作，无论join出现在from后还是出现在where后
2.执行connect by条件
3.执行其他where条件
4.使用1-3的结果形成层次结果：
(1)选择根结果行，根结果行要满足start with条件
(2)选择每个根行的子行，子行要满足connect by条件
(3)继续选择子行的子行
(4)如果查询包含where子句，但是没有join操作。oracle会排除所有不满足where语句的行。oracle是逐行比较这些行，而不是直接移除不满足条件行的子行。
(5)按照下图的方式返回有序的结果
为了找到父行的子行，oracle会对表中的行计算父行的connect by条件的prior表达式以及其他表达式，满足条件的就是子行。connect by条件中可以包含其它的条件来过滤满足查询的行。
如果connect by条件导致了层次循环产生了循环，oracle会返回一个error。
在层次查询中，不要指定order by 或者group by子句，因为connect by会打乱他们的顺序。如果想排序同一个父行的子行，可以使用order siblings by子句。
connect by示例：
SQL> select empno,ename,mgr from emp connect by empno= prior mgr;
EMPNO ENAME MGR
---------- ---------- ----------
7369 SMITH 7902
7902 FORD 7566
7566 JONES 7839
7839 KING
7499 ALLEN 7698
7698 BLAKE 7839
7839 KING
7521 WARD 7698
7698 BLAKE 7839
7839 KING
7566 JONES 7839
7839 KING
7654 MARTIN 7698
7698 BLAKE 7839
7839 KING
7698 BLAKE 7839
7839 KING
7782 CLARK 7839
7839 KING
7788 SCOTT 7566
7566 JONES 7839
7839 KING
7839 KING
7844 TURNER 7698
7698 BLAKE 7839
7839 KING
7876 ADAMS 7788
7788 SCOTT 7566
7566 JONES 7839
7839 KING
7900 JAMES 7698
7698 BLAKE 7839
7839 KING
7902 FORD 7566
7566 JONES 7839
7839 KING
7934 MILLER 7782
7782 CLARK 7839
7839 KING
39 rows selected.
SQL> select empno,ename,mgr from emp connect by prior empno= mgr;
EMPNO ENAME MGR
---------- ---------- ----------
7788 SCOTT 7566
7876 ADAMS 7788
7902 FORD 7566
7369 SMITH 7902
7499 ALLEN 7698
7900 JAMES 7698
7844 TURNER 7698
7654 MARTIN 7698
7521 WARD 7698
7934 MILLER 7782
7876 ADAMS 7788
7566 JONES 7839
7788 SCOTT 7566
7876 ADAMS 7788
7902 FORD 7566
7369 SMITH 7902
7782 CLARK 7839
7934 MILLER 7782
7698 BLAKE 7839
7499 ALLEN 7698
7900 JAMES 7698
7844 TURNER 7698
7654 MARTIN 7698
7521 WARD 7698
7369 SMITH 7902
7839 KING
7566 JONES 7839
7788 SCOTT 7566
7876 ADAMS 7788
7902 FORD 7566
7369 SMITH 7902
7782 CLARK 7839
7934 MILLER 7782
7698 BLAKE 7839
7499 ALLEN 7698
7900 JAMES 7698
7844 TURNER 7698
7654 MARTIN 7698
7521 WARD 7698
39 rows selected.
SQL>
levle示例：
SQL> select empno,ename,mgr,level from emp connect by prior empno=mgr ;
EMPNO ENAME MGR LEVEL
---------- ---------- ---------- ----------
7788 SCOTT 7566 1
7876 ADAMS 7788 2
7902 FORD 7566 1
7369 SMITH 7902 2
7499 ALLEN 7698 1
7900 JAMES 7698 1
7844 TURNER 7698 1
7654 MARTIN 7698 1
7521 WARD 7698 1
7934 MILLER 7782 1
7876 ADAMS 7788 1
7566 JONES 7839 1
7788 SCOTT 7566 2
7876 ADAMS 7788 3
7902 FORD 7566 2
7369 SMITH 7902 3
7782 CLARK 7839 1
7934 MILLER 7782 2
7698 BLAKE 7839 1
7499 ALLEN 7698 2
7900 JAMES 7698 2
7844 TURNER 7698 2
7654 MARTIN 7698 2
7521 WARD 7698 2
7369 SMITH 7902 1
7839 KING 1
7566 JONES 7839 2
7788 SCOTT 7566 3
7876 ADAMS 7788 4
7902 FORD 7566 3
7369 SMITH 7902 4
7782 CLARK 7839 2
7934 MILLER 7782 3
7698 BLAKE 7839 2
7499 ALLEN 7698 3
7900 JAMES 7698 3
7844 TURNER 7698 3
7654 MARTIN 7698 3
7521 WARD 7698 3
39 rows selected.
SQL> select empno,ename,mgr,level from emp connect by empno= prior mgr ;
EMPNO ENAME MGR LEVEL
---------- ---------- ---------- ----------
7369 SMITH 7902 1
7902 FORD 7566 2
7566 JONES 7839 3
7839 KING 4
7499 ALLEN 7698 1
7698 BLAKE 7839 2
7839 KING 3
7521 WARD 7698 1
7698 BLAKE 7839 2
7839 KING 3
7566 JONES 7839 1
7839 KING 2
7654 MARTIN 7698 1
7698 BLAKE 7839 2
7839 KING 3
7698 BLAKE 7839 1
7839 KING 2
7782 CLARK 7839 1
7839 KING 2
7788 SCOTT 7566 1
7566 JONES 7839 2
7839 KING 3
7839 KING 1
7844 TURNER 7698 1
7698 BLAKE 7839 2
7839 KING 3
7876 ADAMS 7788 1
7788 SCOTT 7566 2
7566 JONES 7839 3
7839 KING 4
7900 JAMES 7698 1
7698 BLAKE 7839 2
7839 KING 3
7902 FORD 7566 1
7566 JONES 7839 2
7839 KING 3
7934 MILLER 7782 1
7782 CLARK 7839 2
7839 KING 3
39 rows selected.
SQL>
start with示例：
SQL> select ename,empno,mgr,level from emp start with empno = 7369 connect by prior empno=mgr order siblings by ename; ENAME EMPNO MGR LEVEL ---------- ---------- ---------- ---------- SMITH 7369 7902 1 SQL> select ename,empno,mgr,level from emp start with empno = 7369 connect by empno= prior mgr order siblings by ename; ENAME EMPNO MGR LEVEL ---------- ---------- ---------- ---------- SMITH 7369 7902 1 FORD 7902 7566 2 JONES 7566 7839 3 KING 7839 4 SQL>
Steven King是公司的头头，没有领导；John Russell是一名雇员，是部门80的头头
如果尝试将John Russell的改成King的领导，就会产生循环：
SQL> update employees set manager_id = 145 2 where employee_id = 100; 1 row updated. SQL> SELECT last_name "Employee", 2 LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path" 3 FROM employees 4 WHERE level <= 3 AND department_id = 80 5 START WITH last_name = 'King' 6 CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4; ERROR: ORA-01436: CONNECT BY loop in user data no rows selected SQL>
nocycle关键字指示oracle继续返回数据，尽管有循环。connect_by_iscycle伪列指出哪行数据含有循环：
SQL> SELECT last_name "Employee", CONNECT_BY_ISCYCLE "Cycle", 2 LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path" 3 FROM employees 4 WHERE level <= 3 AND department_id = 80 5 START WITH last_name = 'King' 6 CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 4 7 ORDER BY "Employee", "Cycle", LEVEL, "Path"; Employee Cycle LEVEL Path ------------------------- ---------- ---------- ---------------------------------------- Abel 0 3 /King/Zlotkey/Abel Ande 0 3 /King/Errazuriz/Ande Banda 0 3 /King/Errazuriz/Banda Bates 0 3 /King/Cambrault/Bates Bernstein 0 3 /King/Russell/Bernstein Bloom 0 3 /King/Cambrault/Bloom Cambrault 0 2 /King/Cambrault Cambrault 0 3 /King/Russell/Cambrault Doran 0 3 /King/Partners/Doran Errazuriz 0 2 /King/Errazuriz Fox 0 3 /King/Cambrault/Fox Greene 0 3 /King/Errazuriz/Greene Hall 0 3 /King/Russell/Hall Hutton 0 3 /King/Zlotkey/Hutton Johnson 0 3 /King/Zlotkey/Johnson King 0 1 /King King 0 3 /King/Partners/King Kumar 0 3 /King/Cambrault/Kumar Lee 0 3 /King/Errazuriz/Lee Livingston 0 3 /King/Zlotkey/Livingston Marvins 0 3 /King/Errazuriz/Marvins McEwen 0 3 /King/Partners/McEwen Olsen 0 3 /King/Russell/Olsen Ozer 0 3 /King/Cambrault/Ozer Partners 0 2 /King/Partners Russell 1 2 /King/Russell Sewall 0 3 /King/Partners/Sewall Smith 0 3 /King/Cambrault/Smith Smith 0 3 /King/Partners/Smith Sully 0 3 /King/Partners/Sully Taylor 0 3 /King/Zlotkey/Taylor Tucker 0 3 /King/Russell/Tucker Tuvault 0 3 /King/Russell/Tuvault Vishney 0 3 /King/Errazuriz/Vishney Zlotkey 0 2 /King/Zlotkey 35 rows selected. SQL>
