转载自:http://www.cnblogs.com/lettoo/archive/2010/08/03/1791239.html
Oracle “CONNECT BY”是层次查询子句,一般用于树状或者层次结果集的查询。其语法是:
1
2
|
[ START WITH condition ]
CONNECT BY [ NOCYCLE ] condition
|
The start with .. connect by clause can be used to select data that has a hierarchical relationship (usually some sort of parent->child (boss->employee or thing->parts).
说明:
1. START WITH:告诉系统以哪个节点作为根结点开始查找并构造结果集,该节点即为返回记录中的最高节点。
2. 当分层查询中存在上下层互为父子节点的情况时,会返回ORA-01436错误。此时,需要在connect by后面加上NOCYCLE关键字。同时,可用connect_by_iscycle伪列定位出存在互为父子循环的具体节点。 connect_by_iscycle必须要跟关键字NOCYCLE结合起来使用
接下来,用一些示例来说明“CONNECT BY”的用法。
[例1]
创建一个部门表,这个表有三个字段,分别对应部门ID,部门名称,以及上级部门ID
1
2
3
4
5
6
7
8
|
-- Create table create table DEP
( DEPID number(10) not null ,
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> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (0, '总经办' , null );
1 row inserted SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (1, '开发部' , 0);
1 row inserted SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (2, '测试部' , 0);
1 row inserted SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (3, 'Sever开发部' , 1);
1 row inserted SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (4, 'Client开发部' , 1);
1 row inserted SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (5, 'TA测试部' , 2);
1 row inserted SQL> INSERT INTO DEP(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 rows selected
|
现在我要根据“CONNECT BY”来实现树状查询结果
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL> SELECT RPAD( ' ' , 2*( LEVEL -1), '-' ) || DEPNAME "DEPNAME" ,
CONNECT_BY_ROOT DEPNAME "ROOT" ,
CONNECT_BY_ISLEAF "ISLEAF" ,
LEVEL ,
SYS_CONNECT_BY_PATH(DEPNAME, '/' ) "PATH"
FROM DEP
START WITH UPPERDEPID IS NULL
CONNECT BY PRIOR DEPID = UPPERDEPID;
DEPNAME ROOT ISLEAF LEVEL PATH
------------------------------ ------------------- ---------- ---------- -------------------------------------------------------------------------------- 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 rows selected
|
说明:
1. CONNECT_BY_ROOT 返回当前节点的最顶端节点
2. CONNECT_BY_ISLEAF 判断是否为叶子节点,如果这个节点下面有子节点,则不为叶子节点
3. LEVEL 伪列表示节点深度
4. SYS_CONNECT_BY_PATH函数显示详细路径,并用“/”分隔
[例2]
通过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
|
[例3]
通过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 可以列出上下级关系 构造序列 求排列组合 逆转求出下上级的关系路径
在Oracle中用Start with...Connect By子句递归查询
NULL 博文链接:https://freejvm.iteye.com/blog/550858
本文章详细介绍了Oracle中connect by...start with...的用法。
通过实例比较了 SYS_CONNECT_BY_PATH 和 CONNECT_BY_ROOT 的异同,和返回树形的数据结构
oracle connect by 和 分析函数总结.doc
oracle中 connect by prior 递归算法 Oracle中start with...connect by prior子句用法 connect by 是结构化查询中用到的
Oracle Connect by
Oracle start with.connect by prior子句实现递归查询
oracle中的数查询,介绍的详细,有例子。
主要给大家介绍了关于Oracle递归查询start with connect by prior、的相关资料,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
ORACLE查询树型关系(connect_by_prior_start_with)
文档详述问题的解决办法,附资源instantclient_11_2.zip,一键解决问题
oracle跨库查询 --语法 /*定义 create [public] databse link _link_name connect to _username identified by _passowrd using '_servername/_serverurl'; */ /*调用 select * from _table_name@_link_name */...
介绍了将多行转为字符串的三种方案,并比较了三种方案的执行效率. 1.sys_connect_by_path + start with ... connect by ... prior + 分析函数 2.自定义Function/SP 3.使用 Oracle 10g 内置函数 wmsys.wm_concat
行列转换,层级关系,oracle sys_connect_by_path的用法
许多情况下,由于程序中需要将行转为列展示,如果使用ORACLE那么这个资源适合你。
oracle菜单树查询 使用实例 使用START WITH CONNECT BY PRIOR子句实现递归查询
Oracle_start_with_connect_by_prior_用法[文].pdf