Oracle “CONNECT BY” 使用

Oracle “CONNECT BY” 使用

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

 

更多详情见请继续阅读下一页的精彩内容：

 

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

Linux-6-64下安装Oracle 12C笔记

在CentOS 6.4下安装Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虚拟机中安装步骤

Debian 下 安装 Oracle 11g XE R2

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

1 2 下一页