你好,游客 登录 注册 搜索
背景:
阅读新闻

Oracle “CONNECT BY” 使用

[日期:2014-06-26] 来源:Linux社区  作者:Linux [字体: ]

Oracle “CONNECT BY”是层次查询子句,一般用于树状或者层次结果集的查询。其语法是:

[ 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

-- Create table
create table DEP
(
DEPID number(10) not null,
DEPNAME varchar2(256),
UPPERDEPID number(10)
)
;

初始化一些数据

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

 

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”来实现树状查询结果

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函数显示详细路径,并用“/”分隔

 

更多详情见请继续阅读下一页的精彩内容http://www.linuxidc.com/Linux/2014-06/103718p2.htm

 

--------------------------------------------------------------------------------

Linux-6-64下安装Oracle 12C笔记 http://www.linuxidc.com/Linux/2013-07/86805.htm

CentOS 6.4下安装Oracle 11gR2(x64) http://www.linuxidc.com/Linux/2014-02/97374.htm

Oracle 11gR2 在VMWare虚拟机中安装步骤 http://www.linuxidc.com/Linux/2013-09/89579p2.htm

Debian 下 安装 Oracle 11g XE R2 http://www.linuxidc.com/Linux/2014-03/98881.htm

--------------------------------------------------------------------------------

linux
相关资讯       Connect By 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款