by connect nocycle oracle_oracle中connect by prior的使用

作用

connect by主要用于父子，祖孙，上下级等层级关系的查询

语法

{ CONNECT BY [ NOCYCLE ] condition [AND condition]... [ START WITH condition ]

| START WITH condition CONNECT BY [ NOCYCLE ] condition [AND condition]...}

解释：

start with: 指定起始节点的条件

connect by: 指定父子行的条件关系

prior: 查询父行的限定符，格式: prior column1 = column2 or column1 = prior column2 and ... ，

nocycle: 若数据表中存在循环行，那么不添加此关键字会报错，添加关键字后，便不会报错，但循环的两行只会显示其中的第一条

循环行: 该行只有一个子行，而且子行又是该行的祖先行

connect_by_iscycle: 前置条件:在使用了nocycle之后才能使用此关键字，用于表示是否是循环行，0表示否，1 表示是

connect_by_isleaf: 是否是叶子节点，0表示否，1 表示是

level: level伪列,表示层级，值越小层级越高，level=1为层级最高节点

例子

自定义数据

--创建表

create tableemployee(

emp_idnumber(18),

lead_idnumber(18),

emp_namevarchar2(200),

salarynumber(10,2),

dept_novarchar2(8)

);--添加数据

insert into employee values('1',0,'king','1000000.00','001');insert into employee values('2',1,'jack','50500.00','002');insert into employee values('3',1,'arise','60000.00','003');insert into employee values('4',2,'scott','30000.00','002');insert into employee values('5',2,'tiger','25000.00','002');insert into employee values('6',3,'wudde','23000.00','003');insert into employee values('7',3,'joker','21000.00','003');commit;

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

select emp_id,lead_id,emp_name,prior emp_name aslead_name,salaryfromemployee

startwith lead_id=0connectby prior emp_id =lead_id--等同于

select emp_id,lead_id,emp_name,prior emp_name aslead_name,salaryfromemployee

startwith emp_id=1connectby prior emp_id = lead_id

查询以emp_id为6开始的节点

selectemp_id,lead_id,emp_name,salaryfromemployee

startwith emp_id=6connectby prior lead_id=emp_id;

level伪列的使用，格式化层级

select lpad(' ',level*2,' ')||emp_name as name,emp_id,lead_id,salary,level

fromemployee

startwith lead_id=0connectby prior emp_id=lead_id

connect_by_root 查找根节点

selectconnect_by_root emp_name,emp_name,lead_id,salaryfromemployeewhere dept_no='002'startwith lead_id=1connectby prior emp_id = lead_id;

标注循环行

--插入一条数据，与另一条emp_id=7的数据组成循环行

insert into employee values('3',7,'joker_cycle','21000.00','003');commit;--connect_by_iscycle("CYCLE"), connect by nocycle

select emp_id,emp_name,lead_id,salary,connect_by_iscycle ascyclefromemployee

startwith lead_id=0connectby nocycle prior emp_id = lead_id;

connect_by_isleaf 是否是叶子节点

selectemp_id,emp_name,lead_id,salary,connect_by_isleaffromemployee

startwith lead_id=0connectby nocycle prior emp_id=lead_id;