Oracle笔记 之 递归查询

递归查询

• 在信息系统中需要数据字典表等树状结构的数据，使用递归查询能够快速地获取树状结构数据的关联关系。
• 树状结构的数据存放在数据表中，数据之间的层次关系（父节点与子节点）通过表的列与列之间的关系来描述。
  以类别数据树为例，category表中的categoryid和categoryname表示该类别的编号和名称，headcatid表示上一级类别的编号，即子节点的headcatid值等于父节点的categoryid值。
  表的每一行中都有一个表示父节点的headcatid（根节点的headcatid=0），通过每个节点的父节点，就可以确定整个树结构。

递归查询语法

递归查询可以从任何节点开始自顶向下查（从根往枝、叶查询）或者从底而上查（从枝、叶开始往根查询）。

• 自顶向下查

Select categoryid, categoryname, headcatid
  From category
 Where headcatid != 0
 Start With categoryid = 19
Connect By Prior categoryid = headcatid;

#结果
CATEGORYID	CATEGORYNAME	HEADCATID
1910	文化用品	19
191010	文具用品	1910
19101010	办公盒	191010
19101011	文件夹	191010
19101012	订书机	191010
19101013	文件柜	191010
19101014	帐册系列	191010
19101015	墨水	191010
19101016	印泥	191010
19101017	现代办公用品	191010

• 从底而上查

Select categoryid, categoryname, headcatid
  From category
 Where headcatid != 0
 Start With categoryid = 19101013
Connect By Prior headcatid = categoryid;

#结果
CATEGORYID	CATEGORYNAME	HEADCATID
19101013	文件柜	191010
191010	文具用品	1910
1910	文化用品	19

• 查询方向由prior关键字指明
  prior关键字放在指向父节点的列（headcatid）前表示从底而上查询，prior关键字放在关键字（categoryid）前表示自顶向下查询。

递归查询关键字

select

指定显示的列或者别名。
可以包含以下关键字

• prior
  执行自顶向下的查询时可以用来获取父节点的某列属性用来展示
  例如：查询主分类19所有子节点（不包含自己），并展示每个子节点的父节点名称（使用prior categoryname）。

Select categoryid, categoryname, headcatid, Prior categoryname
  From category
 Where headcatid != 0
 Start With categoryid = 19
Connect By Prior categoryid = headcatid;
#查询结果
CATEGORYID	CATEGORYNAME	HEADCATID	PRIORCATEGORYNAME
1910	文化用品	19	非图书商品
191010	文具用品	1910	文化用品
19101010	办公盒	191010	文具用品
19101011	文件夹	191010	文具用品
19101012	订书机	191010	文具用品
19101013	文件柜	191010	文具用品
19101014	帐册系列	191010	文具用品
19101015	墨水	191010	文具用品
19101016	印泥	191010	文具用品
19101017	现代办公用品	191010	文具用品
19101018	其它	191010	文具用品
19101019	文具盒	191010	文具用品

• sys_connect_by_path()
  执行自顶向下的查询时可以用来展示某个指定列的分级名称。
  执行从底而上的查询时亦可以用来展示某个指定列的分级名称，但是顺序是由小及大，不建议使用。
  例如：

Select categoryid,
       categoryname,
       headcatid,
       sys_connect_by_path(categoryname, '/')
  From category
 Where headcatid != 0
 Start With categoryid = 19
Connect By Prior categoryid = headcatid;
#查询结果
CATEGORYID	CATEGORYNAME	HEADCATID	SYS_CONNECT_BY_PATH(CATEGORYNAME,'/')
1910	文化用品	19	/非图书商品/文化用品
191010	文具用品	1910	/非图书商品/文化用品/文具用品
19101010	办公盒	191010	/非图书商品/文化用品/文具用品/办公盒
19101011	文件夹	191010	/非图书商品/文化用品/文具用品/文件夹
19101012	订书机	191010	/非图书商品/文化用品/文具用品/订书机
19101013	文件柜	191010	/非图书商品/文化用品/文具用品/文件柜
19101014	帐册系列	191010	/非图书商品/文化用品/文具用品/帐册系列
19101015	墨水	191010	/非图书商品/文化用品/文具用品/墨水
19101016	印泥	191010	/非图书商品/文化用品/文具用品/印泥
19101017	现代办公用品	191010	/非图书商品/文化用品/文具用品/现代办公用品
19101018	其它	191010	/非图书商品/文化用品/文具用品/其它
19101019	文具盒	191010	/非图书商品/文化用品/文具用品/文具盒

• connect_by_isleaf
  执行自顶向下查询时用来指示某行是不是叶子节点（0=非叶子节点，1=叶子节点）。
  执行从底而上查询时用来指示某行是不是根节点（0=非节点，1=根节点）。

#自顶向下查询
Select categoryid, categoryname, headcatid, connect_by_isleaf
  From category
 Where headcatid != 0
 Start With categoryid = 19
Connect By Prior categoryid = headcatid;
#结果
CATEGORYID	CATEGORYNAME	HEADCATID	CONNECT_BY_ISLEAF
1910	文化用品	19	0
191010	文具用品	1910	0
19101010	办公盒	191010	1
19101011	文件夹	191010	1
19101012	订书机	191010	1
19101013	文件柜	191010	1
19101014	帐册系列	191010	1
19101015	墨水	191010	1
19101016	印泥	191010	1
19101017	现代办公用品	191010	1
19101018	其它	191010	1
19101019	文具盒	191010	1


#从底而上查询
Select categoryid, categoryname, headcatid, connect_by_isleaf
  From category
 Start With categoryid = 19101013
Connect By Prior headcatid = categoryid;
#查询结果
CATEGORYID	CATEGORYNAME	HEADCATID	CONNECT_BY_ISLEAF
19101013	文件柜	191010	0
191010	文具用品	1910	0
1910	文化用品	19	0
19	非图书商品	0	1

• connect_by_root
  纪录当前起始查询节点的内容。
  自顶向下查询记录根节点的指定列，从底而上查询记录叶子节点的指定列。
  例如：

#自顶向下查询
Select categoryid, categoryname, headcatid, connect_by_root(categoryid)
  From category
 Where headcatid != 0
 Start With categoryid = 19
Connect By Prior categoryid = headcatid;
#结果
CATEGORYID	CATEGORYNAME	HEADCATID	CONNECT_BY_ROOT(CATEGORYID)
1910	文化用品	19	19
191010	文具用品	1910	19
19101010	办公盒	191010	19
19101011	文件夹	191010	19
19101012	订书机	191010	19
19101013	文件柜	191010	19
19101014	帐册系列	191010	19
19101015	墨水	191010	19
19101016	印泥	191010	19
19101017	现代办公用品	191010	19
19101018	其它	191010	19
19101019	文具盒	191010	19


#从底而上查询
Select categoryid, categoryname, headcatid, connect_by_root(categoryid)
  From category
 Start With categoryid = 19101013
Connect By Prior headcatid = categoryid;
#结果
CATEGORYID	CATEGORYNAME	HEADCATID	CONNECT_BY_ROOT(CATEGORYID)
19101013	文件柜	191010	19101013
191010	文具用品	1910	19101013
1910	文化用品	19	19101013
19	非图书商品	0	19101013

• connect_by_iscycle
  判断递归查询是否有循环体，如果有循环体该字段为1,与nocycle（nocycle紧随connect by其后）配合使用。
  例如：

Select categoryid, categoryname, headcatid, connect_by_iscycle
  From category
 Where headcatid != 0
 Start With categoryid = 19
Connect By nocycle Prior categoryid = headcatid;
#结果
CATEGORYID	CATEGORYNAME	HEADCATID	CONNECT_BY_ISCYCLE
1910	文化用品	19	0
191010	文具用品	1910	0
19101010	办公盒	191010	0
19101011	文件夹	191010	0
19101012	订书机	191010	0
19101013	文件柜	191010	0
19101014	帐册系列	191010	0
19101015	墨水	191010	0
19101016	印泥	191010	0
19101017	现代办公用品	191010	0
19101018	其它	191010	0
19101019	文具盒	191010	0

from

指定树状结构数据集。

where

对查询结果树进行过滤。
注意：where条件虽然是对查询结果的过滤，但应写在start with 和 connect by 之前。
例如：过滤headcatid=0的根节点。

Select categoryid, categoryname, headcatid, connect_by_root(categoryid)
  From category
 Where headcatid != 0
 Start With categoryid = 19101013
Connect By Prior headcatid = categoryid;
#结果
CATEGORYID	CATEGORYNAME	HEADCATID	CONNECT_BY_ROOT(CATEGORYID)
19101013	文件柜	191010	19101013
191010	文具用品	1910	19101013
1910	文化用品	19	19101013

start with

指定查询起始位置。
注意：start with 子句和 connect by 子句可以交换位置。
例如：查询分类级别为4的所有分类的父节点。

Select categoryid, categoryname, headcatid
  From category
 Where headcatid != 0
 Start With deptlevelid = 4
Connect By Prior headcatid = categoryid;
#查询结果
60060501	传统工艺品	600605
600605	工艺品	6006
6006	创意文化	60
60060502	现代工艺品	600605
600605	工艺品	6006
6006	创意文化	60
60060503	外国工艺品	600605
600605	工艺品	6006

connect by

指定递归查询的关联条件和递归查询的方向（自顶向下或者从底而上）。

• prior
  prior关键字放在指向父节点的列（headcatid）前表示从底而上查询，prior关键字放在关键字（categoryid）前表示自顶向下查询。

包含所有关键字的示例

查询二级分类19的所有枝叶节点。

Select Level,
       categoryid,
       categoryname,
       headcatid,
       sys_connect_by_path(categoryname, '/'),
       connect_by_isleaf,
       connect_by_iscycle,
       connect_by_root(categoryid)
  From category
 Where headcatid != 0
 Start With categoryid = 19
Connect By nocycle Prior categoryid = headcatid;
#查询结果
2	1910	文化用品	19	/非图书商品/文化用品	0	0	19
3	191010	文具用品	1910	/非图书商品/文化用品/文具用品	0	0	19
4	19101010	办公盒	191010	/非图书商品/文化用品/文具用品/办公盒	1	0	19
4	19101011	文件夹	191010	/非图书商品/文化用品/文具用品/文件夹	1	0	19
4	19101012	订书机	191010	/非图书商品/文化用品/文具用品/订书机	1	0	19
4	19101013	文件柜	191010	/非图书商品/文化用品/文具用品/文件柜	1	0	19
4	19101014	帐册系列	191010	/非图书商品/文化用品/文具用品/帐册系列	1	0	19
4	19101015	墨水	191010	/非图书商品/文化用品/文具用品/墨水	1	0	19
4	19101016	印泥	191010	/非图书商品/文化用品/文具用品/印泥	1	0	19
4	19101017	现代办公用品	191010	/非图书商品/文化用品/文具用品/现代办公用品	1	0	19
4	19101018	其它	191010	/非图书商品/文化用品/文具用品/其它	1	0	19
4	19101019	文具盒	191010	/非图书商品/文化用品/文具用品/文具盒	1	0	19