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;