Oracle函数6—递归查询（start with...connect by、sys_connect_by_path、level）

一、准备数据

• 创建表

CREATE TABLE TEST_ORG
(ID VARCHAR2(64) NOT NULL PRIMARY KEY,NAME VARCHAR2(200),PARTEN_ID VARCHAR2(64)
);
comment on column TEST_ORG.ID is '主键';
comment on column TEST_ORG.NAME is '名称';
comment on column TEST_ORG.PARTEN_ID is '父级id';

• 插入数据

INSERT INTO EXECUTE_DB.TEST_ORG (ID, NAME, PARTEN_ID) VALUES('1', '北京市', '0');
INSERT INTO EXECUTE_DB.TEST_ORG (ID, NAME, PARTEN_ID) VALUES('4', '东城区', '1');
INSERT INTO EXECUTE_DB.TEST_ORG (ID, NAME, PARTEN_ID) VALUES('6', '天安门', '4');
INSERT INTO EXECUTE_DB.TEST_ORG (ID, NAME, PARTEN_ID) VALUES('5', '延庆区', '1');
INSERT INTO EXECUTE_DB.TEST_ORG (ID, NAME, PARTEN_ID) VALUES('7', '八达岭', '5');
INSERT INTO EXECUTE_DB.TEST_ORG (ID, NAME, PARTEN_ID) VALUES('2', '天津市', '0');
INSERT INTO EXECUTE_DB.TEST_ORG (ID, NAME, PARTEN_ID) VALUES('3', '上海市', '0');

二、基本使用

1、获取完整的树结构

 select * from TEST_ORG start with PARTEN_ID = 0 connect by prior id = PARTEN_ID;

执行结果：

id   name   parten_id1	 北京市	  04	 东城区	  16	 天安门	  45	 延庆区	  17	 八达岭 	  52	 天津市	  03	 上海市	  0

2、获取指定节点的全部子节点（包含指定节点）

例：获取北京市的全部子级，传入id=1

 select * from TEST_ORG start with id = 1 connect by prior id = PARTEN_ID;

执行结果：

id   name   parten_id
1	北京市	0
4	东城区	1
6	天安门	4
5	延庆区	1
7	八达岭	5

提示：会返回当前数据及全部子级节点。

3、获取指定节点的全部子节点（不包含指定节点）

例：获取北京市的全部子级，传入PARTEN_ID=1

 select * from TEST_ORG start with PARTEN_ID = 1 connect by prior id = PARTEN_ID;

执行结果：

id   name   parten_id
4	东城区	1
6	天安门	4
5	延庆区	1
7	八达岭	5

提示：只会返回当前数据的全部子级节点。

4、获取指定节点的全部父节点（包含指定节点）

例：获取天安门的全部父级

  select * from TEST_ORG start with id = 6 connect by prior PARTEN_ID = id;

执行结果：

id   name   parten_id
6	天安门	4
4	东城区	1
1	北京市	0

提示：会返回当前数据以及全部父级节点。

三、level函数

level字段为oracle特有的层级字段，可以通过level字段查询指定的层级。

例：获取天安门的全部父级上层级2的一层

  select t.*,level from TEST_ORG t WHERE level = 2 start with id = 6 connect by prior PARTEN_ID = id;

执行结果：

id   name   parten_id
4	东城区	1	2

提示：只会返回当前数据以及全部父级节点的层级为2的数据。

四、获取完整的全树路径

例：获取北京市以及全部子级，指定id=1表示北京市。

 select t.*,sys_connect_by_path(name, '->') text from TEST_ORG t start with id = 1 connect by prior id = PARTEN_ID;

执行结果：

id   name   parten_id  text
1	北京市	  0	      ->北京市
4	东城区	  1	      ->北京市->东城区
6	天安门	  4	      ->北京市->东城区->天安门
5	延庆区	  1	      ->北京市->延庆区
7	八达岭	  5	      ->北京市->延庆区->八达岭

提示：结合substr函数去除指定内容。

例如：去除最开始的->符号

select t.*,substr(sys_connect_by_path(name, '->'), 3) text from TEST_ORG t start with id = 1 connect by prior id = PARTEN_ID;

执行结果：

id   name   parten_id  text
1	北京市	   0	   北京市
4	东城区	   1	   北京市->东城区
6	天安门	   4	   北京市->东城区->天安门
5	延庆区	   1	   北京市->延庆区
7	八达岭	   5	   北京市->延庆区->八达岭