小知识：Oracle中的层次查询
小知识：Oracle中的层次查询
Alfred Zhao
发布于 2021-06-10 00:07:40
发布于 2021-06-10 00:07:40
使用Oracle中的start with .. connect by prior ..语句可以轻松实现。 下面通过scott用户下的emp来做演示，使用自己的一个19c测试环境，结果发现默认并没有scott用户及其测试表，我们需要使用自带脚本添加：
@?/rdbms/admin/utlsampl.sql发现脚本跑完没有显示报错，但也没有成功创建表，进一步排查发现因为是我们使用的是19c的一个PDB，脚本中的连接库方式默认没有指定，需要修改下。 我们先在tnsnames.ora配置文件中添加这个PDB的配置：
--配置tnsnames.ora
CMDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db19c-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cmdb)
)
)修正utlsampl.sql脚本中连接库方式，指定配置好的PDB：
...
CONNECT SCOTT/tiger@cmdb
...再次执行，scott用户下面熟悉的测试表创建成功。 查询emp表：
SQL> conn scott/tiger@cmdb
Connected.
SQL> show pdbs
SP2-0382: The SHOW PDBS command is not available
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
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
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 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.利用层次查询中的伪列level和表达式sys_connect_by_path，查询如下：
select level, ename, job, sys_connect_by_path(ename,'->')
from emp
start with mgr is null
connect by prior empno = mgr
/查询结果如下：
SQL> col sys_connect_by_path(ename,'->') for a35
SQL> /
LEVEL ENAME JOB SYS_CONNECT_BY_PATH(ENAME,'->')
---------- ---------- --------- -----------------------------------
1 KING PRESIDENT ->KING
2 JONES MANAGER ->KING->JONES
3 SCOTT ANALYST ->KING->JONES->SCOTT
4 ADAMS CLERK ->KING->JONES->SCOTT->ADAMS
3 FORD ANALYST ->KING->JONES->FORD
4 SMITH CLERK ->KING->JONES->FORD->SMITH
2 BLAKE MANAGER ->KING->BLAKE
3 ALLEN SALESMAN ->KING->BLAKE->ALLEN
3 WARD SALESMAN ->KING->BLAKE->WARD
3 MARTIN SALESMAN ->KING->BLAKE->MARTIN
3 TURNER SALESMAN ->KING->BLAKE->TURNER
3 JAMES CLERK ->KING->BLAKE->JAMES
2 CLARK MANAGER ->KING->CLARK
3 MILLER CLERK ->KING->CLARK->MILLER
14 rows selected.这样就通过start with .. connect by prior ..语句轻松的将这个层次关系查询出来，当然也可以根据需求进一步排序：
SQL> ed
Wrote file afiedt.buf
1 select level, ename, job, sys_connect_by_path(ename,'->')
2 from emp
3 start with mgr is null
4 connect by prior empno = mgr
5* order by 1
SQL> /
LEVEL ENAME JOB SYS_CONNECT_BY_PATH(ENAME,'->')
---------- ---------- --------- -----------------------------------
1 KING PRESIDENT ->KING
2 JONES MANAGER ->KING->JONES
2 BLAKE MANAGER ->KING->BLAKE
2 CLARK MANAGER ->KING->CLARK
3 FORD ANALYST ->KING->JONES->FORD
3 WARD SALESMAN ->KING->BLAKE->WARD
3 JAMES CLERK ->KING->BLAKE->JAMES
3 MILLER CLERK ->KING->CLARK->MILLER
3 ALLEN SALESMAN ->KING->BLAKE->ALLEN
3 SCOTT ANALYST ->KING->JONES->SCOTT
3 MARTIN SALESMAN ->KING->BLAKE->MARTIN
3 TURNER SALESMAN ->KING->BLAKE->TURNER
4 ADAMS CLERK ->KING->JONES->SCOTT->ADAMS
4 SMITH CLERK ->KING->JONES->FORD->SMITH
14 rows selected.也可以指定关心的员工及其下属关系：
SQL> ed
Wrote file afiedt.buf
1 select level, ename, job, sys_connect_by_path(ename,'->')
2 from emp
3 start with ename = 'SCOTT'
4 connect by prior empno = mgr
5* order by 1
SQL> /
LEVEL ENAME JOB SYS_CONNECT_BY_PATH(ENAME,'->')
---------- ---------- --------- -----------------------------------
1 SCOTT ANALYST ->SCOTT
2 ADAMS CLERK ->SCOTT->ADAMS第一次看到这类SQL时，总觉得语法很怪，但其实明白其实现的功能后，就会发现这种写法真是既简单又高效。
本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读