Oracle 递归函数

1、自连接

--需求-1：
--员工编号、员工姓名、上级领导编号、上级领导姓名、自连接
SELECT e.empno, m.ename
FROM emp e, emp m
WHERE e.mgr=m.empno;

 

 

 2、递归

1）求出员工编号为7369的所有上级领导

--需求-2：
--求出员工编号为7369的所有上级领导
SELECT empno, ename
FROM emp e
START WITH empno=7369
CONNECT BY empno=PRIOR mgr;

 

 

 2）求出员工编号为7369的所有下属

--需求-3：
--求出员工编号为7369的所有下属
SELECT empno, ename
FROM emp e
START WITH empno=7369
CONNECT BY PRIOR empno= mgr;

 

 

 3）求出各个员工的所有上级领导

--需求-4：
--求出各个员工的所有上级领导
SELECT a1.*, (
  SELECT SUBSTR(SYS_CONNECT_BY_PATH(empno, '->'), 3) NAME_PATH
  FROM emp a
  WHERE a.mgr IS NULL
  START WITH a.empno = a1.empno
  CONNECT BY PRIOR a.mgr = a.empno
) AS NAME_PATH
FROM emp a1;

 

 

 4）取编号为104001008002城市的所有上级城市 南关区

--需求5：
--取编号为104001008002城市的所有上级城市 南关区
SELECT * FROM china
WHERE c_id = 104001008002;
------LEVEL可以对得到的所有上级/下级进行排序，可以表示出层级大小
------SYS_CONNECT_BY_PATH()可以用指定的分隔符对各层级进行分隔
SELECT c_id, c_name, LEVEL,
       SYS_CONNECT_BY_PATH(c_name, '->')
FROM china
START WITH c_id = 104001008002
CONNECT BY c_id = PRIOR superior_c_id;

 

 5）查询中南地区各下级城市列表

--需求6：
--查询中南地区各下级城市列表
SELECT 中南地区下级城市列表,
       REGEXP_SUBSTR(中南地区下级城市列表, '[^->]+', 1, 1)地区,
       REGEXP_SUBSTR(中南地区下级城市列表, '[^->]+', 1, 2)省份,
       REGEXP_SUBSTR(中南地区下级城市列表, '[^->]+', 1, 3)城市,
       REGEXP_SUBSTR(中南地区下级城市列表, '[^->]+', 1, 4)区县
FROM(
SELECT c_id, c_name, LEVEL le, 
       SYS_CONNECT_BY_PATH(c_name, '->') as 中南地区下级城市列表
FROM china
START WITH c_id = 104
CONNECT BY PRIOR c_id = superior_c_id
)
where le=4;