Oracle CONNECT BY PRIOR函数的使用

语法

{
SELECT demo01,demo02,demo03
FROM demotable
START WITH [condition] 
CONNECT BY [NOCYCLE] PRIOR [CONDITION]
}

创建表

--建表
CREATE TABLE employee(
    emp_id NUMBER(18),
    lead_id NUMBER(18),
    emp_name VARCHAR2(50),
    salary NUMBER(10,2),
    dept_no VARCHAR2(11)
);

--插入数据
insert into employee values('1',0,'孙笑川','9999.00','001');
insert into employee values('2',1,'刘波','8888.00','002');
insert into employee values('3',1,'Giao哥','7777.00','003');
insert into employee values('4',2,'卢本伟','6666.00','002');
insert into employee values('5',2,'药水哥','5555.00','002');
insert into employee values('6',3,'伞兵一号','3333.00','003');
insert into employee values('7',3,'老中医','4444.00','003');
commit;

查询以emp_id为0开始的节点的所有直属节点

--查询以emp_id为0开始的节点的所有直属节点
SELECT emp_id,lead_id,emp_name,prior emp_name as lead_name,salary
FROM EMPLOYEE
START WITH emp_id=1
CONNECT BY PRIOR emp_id=lead_id;

level格式化层级

--level格式化层级
SELECT LPAD('  ', LEVEL*2, '  ')||emp_name as name,emp_id,lead_id,salary,level
FROM employee
START WITH lead_id=0
CONNECT BY PRIOR emp_id=lead_id;

查找根节点CONNECT_BY_ROOT

--查找根节点CONNECT_BY_ROOT
SELECT CONNECT_BY_ROOT emp_name as root,emp_name,lead_id,salary
FROM employee
START WITH lead_id=0
CONNECT BY PRIOR emp_id=lead_id;

标注循环行CONNECT_BY_ISCYCLE

--标注循环行CONNECT_BY_ISCYCLE
INSERT INTO employee VALUES('3', 7, '老中医1号', '15000.00', '003');
commit;

SELECT emp_id,emp_name,lead_id,salary,CONNECT_BY_ISCYCLE
FROM employee
START WITH lead_id=0
CONNECT BY NOCYCLE PRIOR emp_id=lead_id;

判断是否为叶子节点CONNECT_BY_ISLEAF

--判断是否为叶子节点CONNECT_BY_ISLEAF
SELECT emp_id,emp_name,lead_id,salary,CONNECT_BY_ISLEAF
from employee
START WITH lead_id=0
CONNECT BY NOCYCLE PRIOR emp_id=lead_id;