Oracle Connect By Prior用法
摘要：
1、connect by
中的条件就表示了父子之间的连接关系
，比如
connect by id=prior pid。
2、
prior，表示在一表上，prior
所在列pid的某一值A的记录行的父亲是列id上值等于列pid上值A的记录行。
3、
LEVEL
伪列
表示树的深度（或叫高度）。
oracle中的select语句可以用START WITH...CONNECT BY
PRIOR
子句实现
递归查询（或叫树状查询）
，connect by 是结构化查询中用到的，
其基本语法是：
select ... from <TableName>
where <Conditional-1>
start with <Conditional-2>
connect by <Conditional-3>
<Conditional-1>：过滤条件，用于对返回的所有记录进行过滤。
<Conditional-2>：
该限定条件，表示查询结果以谁作为起始根结点的。当然可以放宽该限定条件，以取得
多个根结点，实际就是多棵树。
<Conditional-3>：连接条件，即表示
不同节点间以该
连接条件构成一个
父子关系
|
1
2
|
[ START WITH condition ]
CONNECT BY [ NOCYCLE ] condition
|
其中 connect by 与 start with 语句摆放的先后顺序不影响查询的结果，[where 条件1]可以不需要，若是出现则要放在
connect by 与 start with 语句
之前，否则出错。
[where 条件1]、[条件2]、[条件3]各自作用的范围都不相同：
[where 条件1]
是在根据“connect by [条件2] start with [条件3]”选择出来的记录中进行过滤，是针对单条记录的过滤， 不会考虑树的结构（
最后的过滤
）；
[ connect by 条件2]
指定构造树的条件，以及对树分支的过滤条件，在这里执行的过滤会把符合条件的记录及其下的所有子节点都过滤掉；
[ start with 条件3]
限定作为搜索起始点的条件，如果是自上而下的搜索则是限定作为根节点的条件，如果是自下而上的搜索则是限定作为叶子节点的条件；
要根据connect by 从上到下还是从下到上，来确定起始节点，可能是叶节点，也可能是父节点，这些开始节点可以是多个，并且包含这些节点。
oracle 11g r2貌似不支持
从下到上
的遍历？？
oracle 10g支持
从下到上
的遍历
？？
【
oracle 的 start with ... connect by ...
】
层级查询语句（hierarchical query）中，where子句先被执行，再执行
CONNECT BY子句以及其附属子句。
LEVEL
伪列
表示树的深度（或叫高度）。
使用
LEVEL
伪列
：
在具有树结构的表中，每一行数据都是树结构中的一个节点，由于节点所处的层次位置不同，所以每行记录都可以有一个层号。层号根据节点与根节点的距离确定。不论从哪个节点开始，该起始根节点的层号始终为1，根节点的子节点为2， 依此类推。
1、这里说的节点指的是层级查询语句（hierarchical query）中from子句里的表的每一数据行。
2、层级查询语句（hierarchical query）中，CONNECT BY子句是必选的，而
START WITH
子句是可选的，
START WITH
子句是用来修饰
CONNECT BY子句的。
3、prior关键字放在CONNECT BY子句中。其中用PRIOR表示上一条记录，比如 CONNECT BY PRIOR id = parent_id就是说上一条记录的id 是本条记录的parent_id，即本记录的父亲是上一条记录。
connect by
parent_id
=
prior
id
表示的是一数据行中
列名为
parent_id
的列上的值为该行的父节点的编号值，而
父节点的编号值
都是来源于表中列名为
id
的列上的各个值。
总之，
prior
放在
connect by
子句连接条件里的哪一边，哪一边就是
父节点的编号值
的来源
，而
connect by
子句连接条件里等号另一边就是记录一数据行其对应的父节点的编号值。
START WITH 子句为可选项，用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，则表示所有满足查询条件（即where子句里的条件）的行作为根节点。
具体例子见下文二大点里的注释。
START WITH： 不但可以指定一个根节点，还可以指定多个根节点。
“START WITH 子句
被省略，则表示所有满足查询条件（即where子句里的条件）的行作为根节点”
这个特性
可以用来证明
”层级查询语句（hierarchical query）中，where子句先被执行，再执行
CONNECT BY子句以及其附属子句“。
START WITH 子句
和
CONNECT BY子句
是两个相互独立的子句，即并没有规定
START WITH 子句
出现的列就是要为
CONNECT BY子句里那个带有关键字prior的列，
START WITH 子句
出现的列
可以来自表里的任何列，也就是说
START WITH 子句
出现的列
可以没有在
START WITH 子句
里出现，因为
START WITH 子句
的作用就是根据
START WITH 子句
的
限定条件来筛选出哪些数据行作为根节点而已。例子，
select * from t2 start with id = 1 connect by prior id= root_id;
select * from t2 start with root_id = 0 connect by prior id = root_id;
层级查询语句（hierarchical query），
Oracle采用了自上而下的深度优先的算法。
下面以一个具体例子来说明层级查询语句（hierarchical query）connect by 用法。
现在有一个表t2，
其上各个数据行间的父子关系的
结构图
（即
数据组织结构图
）
如下：
表t2的表结构如下：
create table t2(
root_id number,
id number,
name varchar(5),
description varchar(10)
insert into t2(root_id,id,name,description)values(0,1,'a','aaa');
insert into t2(root_id,id,name,description)values(1,2,'a1','aaa1');
insert into t2(root_id,id,name,description)values(1,3,'a2','aaa2');
insert into t2(root_id,id,name,description)values(0,4,'b','bbb');
insert into t2(root_id,id,name,description)values(4,5,'b1','bbb1');
insert into t2(root_id,id,name,description)values(4,6,'b2','bbb2');
获取完整树：
select * from t2
start withroot_id = 0
connect by prior id = root_id;
简单说来是将一个树状结构存储在一张表里，比如一个表中存在两个字段: root_id,id。那么通过表示每一条记录的父记录是谁，就可以形成一个树状结构。
用上述语法的查询可以取得这棵树的所有记录。
上述SQL语句具体执行过程分析：
1） 首先，执行的是该select语句中的
start with
子句部分，具体地说，就是执行该select语句的服务器进程先从表t2中找出
所有
root_id
字段的值为
0
的数据行
。在上述例子中，就是
name
字段分别为
a
和
b
的两条数据行，它们两构成了上图中树的第一层。
2
）接着，执行的是该
select
语句中的
connect by
子句部分，具体地说，就是执行该
select
语句的服务器进程先从表
t2
剩下的数据行（即除去了已经被选中的那些数据行）中
找出
所有
root_ id
字段的值等于在上图的树中位于其上一层中各个
数据行的
id
字段的值的数据行
。在上述例子中，
上图中树的第一层
里
name
字段为
a
的数据行的
id
字段
值为
1
，所以
服务器进程
就从
表
t2
剩下的数据行
（即出去了
上图中树的第一层
里的那两条数据行）
中找出
所有
root_ id
字段的值等于
1
的数据行，即
name
字段分别为
a1
和
a2
的两条数据行；
上图中树的第一层
里
name
字段为
b
的数据行的
id
字段
值为
4
，所以
服务器进程
就从
表
t2
剩下的数据行
（即出去了
上图中树的第一层
里的那两条数据行以及前面刚被选中的两条数据行）
中找出
所有
root_ id
字段的值等于
4
的数据行，即
name
字段分别为
b1
和
b2
的两条数据行
。于是，
name
字段分别为
a1
和
a2
，
b1
和
b2
的四条数据行，构成了上图中树的第二层。
3)
再接着，服务器进程还是要重复执行
该
select
语句中的
connect by
子句部分，
直到表中不存在符合
prior id = root_id
这个条件的数据行为止。
此时，服务器进程
发现表
t2
中剩余的数据行为空了。
这样，该
select
语句整个过程就执行完毕了。如果表
t2
中还是有剩余的数据行的，则
服务器进程重复执行
2
）步骤。
1）
In a hierarchicalquery, one expression in
condition
must be qualifiedwith the
PRIOR
operator to refer to the parentrow. For example,
... PRIOR expr = expr
... expr = PRIOR expr
在 层次查询（或叫递归查询）中，
connect by子句的条件里的表达式必须要有prior运算符 放在一个字段的前面来表示该字段是父数据行（（即树结构图中位于子数据行所在层的直接上一层里的数据行））的该字段。例如，
... PRIOR expr = expr
... expr = PRIOR expr
也就是说，当connect by子句的条件里出现的字段是普通字段，不是伪列字段rownum或是level时，connect by子句的条件里的表达式必须要有prior运算符 放在一个字段的前面；当connect by子句的条件里出现的是伪列字段rownum或是level时，connect by子句的条件里的表达式不用出现prior运算符。
还有一点要说明的是，
start with
子句不是一个独立的子句，即
start with
子句
是一个不能单独出现在
SQL
语句的子句，必须
在
SQL
语句中出现了
connect by
子句
后，才能出现。换句话说，
start with
子句是来配合
connect by
子句的。
例如，
SQL> select * from t2 start with id = 1;
select * from t2 start withid = 1
第 1 行出现错误:
ORA-01788: 此查询块中要求 CONNECT BY 子句
附加：其他依赖于CONNECT BY 子句的伪列，如level、
CONNECT_BY_ISCYCLE
等也会有相同的提示，若是独立出现的话。
当
connect by
子句没有带
start with
子句时
，
例如，select * from t2 connect by prior id = root_id，则因为带prior的id字段是
父数据行的字段，所以
执行该select语句的服务器进程先从表t2中找出所有 id字段值非空的数据行（差不多就是表t2的所有数据行），它们构成了树结构的第一层。接着，执行该select语句的服务器进程从表t2
中
找出所有root_ id字段的值等于在树结构的第一层中各个数据行的id字段值的数据行，它们构成了树结构的第二层。再接着，服务器进程从表t2
中
找出所有root_ id字段的值等于在树结构的第二层中各个数据行的id字段值的数据行。就这样，一直重复执行connect by prior id = root_id这一个子句，直到表中不存在符合prior id = root_id这个条件的数据行为止。
下面是该SQL语句的执行结果：
SQL> select t2.*,level from t2
connect by prior id = root_id;
LEVEL
ID
ROOT_ID NAME DESCRIPTIO
---------- ---------- ----- ---------- ----------
1 1
0 a aaa
1 2
1 a1 aaa1
1 3
1 a2 aaa2
1 4
0 b bbb
1 5
4 b1 bbb1
1 6
4 b2 bbb2
2
2
1
a1 aaa1
2
3
1
a2 aaa2
2
5
4
b1 bbb1
2
6
4 b2 bbb2
已选择
10
行。
ID表示树状结构图中各个节点的编号值。
LEVEL
伪列
表示树的深度（或叫高度）。
当
connect by
子句带有
start with
子句时
，
例如，select * from t2 start with root_id = 0 connect by prior id = root_id，则执行该select语句的服务器进程首先执行的是该select语句中的start with子句部分。start with子句部分的功能就是让服务器进程根据start with子句里的字段找到树结构的第一层里所有的数据行。找完第一层的数据行后，服务器进程执行的操作和
connect by子句没带有
start with子句时的操作就是一样的了，也是重复执行connect by prior id = root_id这一个子句，直到表中不存在符合prior id = root_id这个条件的数据行为止。
SQL>
select * from t2 start with root_id = 0 connect by prior id = root_id
;
LEVEL
ID
ROOT_ID NAME DESCRIPTIO
---------- ---------- ----- ---------- ----------
1 1
0 a aaa
1 4
0 b bbb
1 5
4 b1 bbb1
1 6
4 b2 bbb2
2
2
1
a1 aaa1
2
3
1
a2 aaa2
已选择6行。
虽然这个
SQL
语句中
start with
子句里的字段和
connect by
子句里的带
prior
的字段不一样，但是不会相互影响的。
2）
在connect by 子句里出现的两个字段都是同一个表里的两个字段。
3）
“执行该
select
语句的服务器进程先从表
t2
剩下的数据行（即除去了已经被选中的那些数据行）
中
查找数据行“这样的表述是不正确的。其实，每次服务器进程都是从表
t2
中的所有的数据行上查找的。例如，
SQL> select t2.*,level from t2
connect by prior id = root_id;
ROOT_ID ID NAME DESCRIPTIO LEVEL
---------- ---------- ----- --------------------
0 1 a aaa 1
1 2 a1 aaa1 1
1 3 a2 aaa2 1
0 4 b bbb 1
4 5 b1 bbb1 1
4 6 b2 bbb2 1
1
2 a1 aaa1 2
1 3 a2 aaa2 2
4 5 b1 bbb1 2
4 6 b2 bbb2 2
已选择
10
行。
从上面的例子可以看出，处于树结构第二层（
level=2
）的数据行也在处于树结构第一层（
level=1
）中出现。这就说明了，
每次服务器进程都是从表
t2
中的所有的数据行上查找的。
4）
当表中有重复行时，这些重复行不会合并在一起，而是单独算的。例如，
SQL> insert into t2(root_id,id,name,description)values(4,6,'b2','bbb2');
已创建 1 行。
SQL> select t22.*,level from t2 connect by prior id = root_id order bylevel,i
ROOT_ID ID NAME DESCRIPTIO LEVEL
---------- ---------- ----- ---------- ----------
0 1 a aaa 1
1 2 a1 aaa1 1
1 3 a2 aaa2 1
0 4 b bbb 1
4 5 b1 bbb1 1
4 6 b2 bbb2 1
4 6 b2 bbb2 1
1 2 a1 aaa1 2
1 3 a2 aaa2 2
4 5 b1 bbb1 2
4 6 b2 bbb2 2
4 6 b2 bbb2 2
已选择12行。
获取特定子树：
select * from t2 start with id = 1 connect by prior id= root_id;
select * from t2 start with id = 4 connect by prior id = root_id;
如果connect by prior中的prior被省略，则查询将不进行深层递归。如：
select * from t2 start with root_id = 0 connect by id = root_id;
//此时
start with
子句还是有被执行的
select * from t2 start with id = 1 connect by id = root_id;如：
上述两个例子如果connect by prior中的关键字prior被省略，且在connect by 里的字段不是伪列rownum或是伪列level（伪列
level
只有在SQL语句中出现了connect by 子句时才有的），那么
connect by
子句会被忽略不被执行的（
此时
start with
子句还是有被执行的
）
。
http://www.360doc.com/content/11/0608/11/5287961_122421161.shtml
http://www.cnblogs.com/einyboy/archive/2012/08/01/2617939.html
http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries003.htm
Hierarchical Queries
http://www.cnblogs.com/zhf/archive/2008/09/10/1288101.html
==================================================================
Oracle “CONNECT BY” 使用
Oracle “CONNECT BY”是层次查询子句，一般用于树状或者层次结果集的查询。其语法是：
|
1
2
|
[ START WITHcondition ]
CONNECT BY [ NOCYCLE ] condition
|
The start with .. connect by clause can be used toselect data that has a hierarchical relationship (usually some sort ofparent->child (boss->employee or thing->parts).
说明：
1. START WITH：告诉系统以哪个节点作为根结点开始查找并构造结果集，该节点即为返回记录中的最高节点。
2. 当分层查询中存在上下层互为父子节点的情况时，会返回ORA-01436错误。此时，需要在connect by后面加上NOCYCLE关键字。同时，可用
connect_by_iscycle
伪列
定位出存在互为父子循环的具体节点。 connect_by_iscycle必须要跟关键字NOCYCLE结合起来使用，也就说，
connect_by_iscycle
伪列是一个不能单独出现在
SQL
语句的伪列，必须
在
SQL
语句中出现了关键字
NOCYCLE
后，才能出现。
例如，
select connect_by_iscycle from t2 start with root_id = 0 connect byNOCYCLE prior id = root_id;
接下来，用一些示例来说明“CONNECT BY”的用法。
创建一个部门表，这个表有三个字段，分别对应部门ID，部门名称，以及上级部门ID
|
1
2
3
4
5
6
7
8
|
-- Create table
create table DEP
(
DEPID number(10)notnull,
DEPNAME varchar2(256),
UPPERDEPID number(10)
)
;
|
初始化一些数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL> INSERTINTODEP(DEPID, DEPNAME, UPPERDEPID)VALUES(0, '总经办',null);
1 row inserted
SQL> INSERTINTODEP(DEPID, DEPNAME, UPPERDEPID)VALUES(1, '开发部', 0);
1 row inserted
SQL> INSERTINTODEP(DEPID, DEPNAME, UPPERDEPID)VALUES(2, '测试部', 0);
1 row inserted
SQL> INSERTINTODEP(DEPID, DEPNAME, UPPERDEPID)VALUES(3, 'Sever开发部', 1);
1 row inserted
SQL> INSERTINTODEP(DEPID, DEPNAME, UPPERDEPID)VALUES(4, 'Client开发部', 1);
1 row inserted
SQL> INSERTINTODEP(DEPID, DEPNAME, UPPERDEPID)VALUES(5, 'TA测试部', 2);
1 row inserted
SQL> INSERTINTODEP(DEPID, DEPNAME, UPPERDEPID)VALUES(6, '项目测试部', 2);
1 row inserted
SQL> commit;
Commit complete
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL> SELECT*FROM DEP;
DEPID DEPNAME UPPERDEPID
----------- -------------------------------------------------------------------------------- -----------
0 General Deparment
1 Development 0
2 QA 0
3 Server Development 1
4 Client Development 1
5 TA 2
6 Porject QA 2
7 rowsselected
|
现在我要根据“CONNECT BY”来实现树状查询结果
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL> SELECTRPAD(' ', 2*(LEVEL-1),'-') || DEPNAME "DEPNAME",
CONNECT_BY_ROOT DEPNAME"ROOT",
CONNECT_BY_ISLEAF "ISLEAF",
LEVEL ,
SYS_CONNECT_BY_PATH(DEPNAME,'/')"PATH"
FROM DEP
START WITHUPPERDEPIDIS NULL
CONNECT BY PRIOR DEPID = UPPERDEPID;
DEPNAME ROOT ISLEAF LEVELPATH
------------------------------ ------------------- ---------- ---------- --------------------------------------------------------------------------------
General Deparment General Deparment 0 1 /General Deparment
-Development General Deparment 0 2 /General Deparment/Development
---Server Development General Deparment 1 3 /General Deparment/Development/Server Development
---Client Development General Deparment 1 3 /General Deparment/Development/Client Development
-QA General Deparment 0 2 /General Deparment/QA
---TA General Deparment 1 3 /General Deparment/QA/TA
---Porject QA General Deparment 1 3 /General Deparment/QA/Porject QA
7 rowsselected
|
说明：
1. CONNECT_BY_ROOT （
伪列
）返回当前节点的最顶端节点
2. CONNECT_BY_ISLEAF
（伪列）
判断是否为叶子节点，如果这个节点下面有子节点，则不为叶子节点
3.
LEVEL
伪列
表示树的深度（或叫高度）
4.
SYS_CONNECT_BY_PATH
函数
显示详细路径，并用“/”分隔
CONNECT BY的应用例子
通过CONNECT BY用于十六进度转换为十进制
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
CREATE OR REPLACE FUNCTION f_hex_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2 IS
----------------------------------------------------------------------------------------------------------------------
-- 对象名称: f_hex_to_dec
-- 对象描述: 十六进制转换十进制
-- 输入参数: p_str 十六进制字符串
-- 返回结果: 十进制字符串
-- 测试用例: SELECT f_hex_to_dec('78A') FROM dual;
----------------------------------------------------------------------------------------------------------------------
v_return VARCHAR2(4000);
BEGIN
SELECT SUM(DATA) INTO v_return
FROM (SELECT (CASE upper(substr(p_str, rownum, 1))
WHEN 'A' THEN '10'
WHEN 'B' THEN '11'
WHEN 'C' THEN '12'
WHEN 'D' THEN '13'
WHEN 'E' THEN '14'
WHEN 'F' THEN '15'
ELSE substr(p_str, rownum, 1)
END) * power(16, length(p_str) - rownum) DATA
FROM dual
CONNECT BY rownum <= length(p_str));
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
|
1. CONNECT BY rownum <= length(p_str))对输入的字符串进行逐个遍历
2. 通过CASE语句，来解析十六进制中的A-F对应的10进制值
通过CONNECT BY生成序列
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL> SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM <= 10;
ROWNUM
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected
|
本例子的具体详解见：《
深入理解
connect byrownum<xxx
》
http://www.cnblogs.com/lettoo/archive/2010/08/03/1791239.html
http://www.blogjava.net/freeman1984/archive/2011/05/06/349668.html
connect by prior
百度 connect by prior
Oracle Connect By Prior用法oracle中的select语句可以用START WITH...CONNECT BY PRIOR子句实现递归查询，connect by 是结构化查询中用到的，其基本语法是： select ... from where start with connect by ; ：过滤条件，用于对返回的
在本文中，我们研究了
Oracle
字符识别和一般草图识别。 首先，收集
Oracle
字符的数据集进行分析，这些数据是中国最古老的象形文字，但仍是现代汉字的一部分。 其次，评估与形状和草图相关的作品中的典型视觉表示。 我们分析了解决这些表示形式时遇到的问题，并确定了几种表示形式设计标准。 在分析的基础上，我们提出一种新颖的分层表示，结合了Gabor相关的低级表示和稀疏编码器相关的中级表示。 大量的实验表明，所提出的表示在
Oracle
字符识别和一般草图识别中都是有效的。 提出的表示形式还可以补充基于卷积神经网络（CNN）的模型。 我们介绍了一种解决方案，可将建议的表示与基于CNN的模型相结合，并在两种方法上均能实现更好的性能。 这种解决方案在识别通用草图方面已经击败了人类。
oracle
的start with
connect
by prior如何使用
oracle
的start with
connect
by prior是根据条件递归查询"树"，分为四种使用情况：
第一种：start with 子节点ID=’…’
connect
by prior 子节点ID = 父节点ID
查询结果是：自己所有的后代节点（包括自己）。
select OFFICE_CODE,PARENT_CODE,OFFICE_NAME from js_sys_office o start
with o
人大金仓适配 com.kingbase8.util.KSOLException: 错误:
CONNECT
BY clause required in t
hi
s
query
block
最近有个需求是选中某个商品分类时需要递归查询该分类以及 该分类下所有的子分类以及子分类下的子分类等等 的商品，正常来说需要递归查询某个分类以及所有的子孙分类需要使用递归查询，一直到该分类没有子节点为止，但是这样效率是比较慢的，还会造成对数据库的多次访问，为了提高效率，从网上了解学习了下sql有没有办法直接实现这样递归查询的功能，
Oracle
数据库 start with
connect
by 恰好提供了这样的功能。
表 type 表结构为 ( id v
arc
har2(10) , name v
文章目录1 概述2 语法3 示例3.1 查询机构信息3.2 拆分字符串4 常见报错4.1
CONNECT
BY loop in user data4.2 子查询返回多行
1. 主要作用
(1) 简单树查询（递归查询）,如：'查询机构信息'
(2) 拆分字符串
select *, level
from <table_name>
where ... -- 可选条件: 过滤条件
start with ... -- 可选
Oracle
树查询的最重要的就是select...start with...
connect
by...prior
语法了。依托于该语法，我们可以将一个表形结构的中以树的顺序列出来。在下面列述了
Oracle
中树型查询的常用查询方式以及经常使用的与树查询相关的
Oracle
特性函数等，在这里只涉及到一张表中的树查询方式而不涉及多表中的关联等。
以我做过的一个项目中的表为例，表结构如下：
