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 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
-QA General Deparment 0 2 /General Deparment/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
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进制值
原文地址‘:http://www.cnblogs.com/lettoo/archive/2010/08/03/1791239.html
分享到:
相关推荐
Connect By 可以列出上下级关系 构造序列 求排列组合 逆转求出下上级的关系路径
### Oracle的Connect By使用示例 #### 一、引言 在关系数据库系统中,进行树状或层次结构数据查询时经常会遇到复杂性问题。Oracle数据库通过提供`START WITH...CONNECT BY`语句来简化这类查询操作。此功能最早出现...
Oracle的`CONNECT BY LEVEL`是数据库查询中用于构建层次结构数据的一种强大工具,尤其是在处理具有层级关系的数据时,如组织架构、产品分类等。本文将深入探讨`CONNECT BY`子句以及`LEVEL`伪列的使用,以帮助你更好...
本文章详细介绍了Oracle中connect by...start with...的用法。
Oracle 查询树型关系是指使用 START WITH 和 CONNECT BY 子句来实现 SQL 的层次查询。从 Oracle 9i 开始,可以通过 SYS_CONNECT_BY_PATH 函数实现将父节点到当前行内容以“path”或者层次元素列表的形式显示出来。 ...
"Connect By的使用探索" Connect By是一种递归查询语句,用于实现树形结构的查询和遍历。它是 Oracle 数据库中的一种特色语句,能够高效地查询树形结构的数据。 Connect By的基本语法 Connect By语句的基本语法...
#### 二、Connect By Prior 的基本语法 `Connect By Prior`的基本语法如下: ```sql SELECT ... FROM tablename START WITH 条件1 CONNECT BY 条件2 WHERE 条件3; ``` 其中: - **START WITH**:定义了树结构中...
### Oracle Start With.Connect By Prior 子句实现递归查询 #### 概述 在Oracle数据库中,`Start With.Connect By Prior`子句是执行递归查询的一种强大工具,主要用于处理层次结构数据。这类数据通常存在于组织...
Oracle的`CONNECT BY`和分析函数是数据库查询中的两种强大工具,主要用于处理复杂的数据结构和进行高级数据分析。本文将深入探讨这两种技术。 ### Oracle `CONNECT BY` 用法总结 `CONNECT BY` 是Oracle SQL中用于...
文章中给出了一个名为`t_test_connectby`的数据表例子,其中包含父子关系的节点数据。通过该表,我们可以更直观地了解Connect By的执行过程。 - 不指定起始条件时,使用`CONNECT BY`: ```sql SELECT * FROM t_test...
在Oracle数据库中,`CONNECT BY` 是一个非常重要的SQL语法,用于构建层次查询,它能够帮助我们处理具有层级关系的数据,比如组织结构、产品分类、树形菜单等。`CONNECT BY` 查询允许从一个表中抽取数据,并按照指定...
此问题通常是由于 PL/SQL Developer 的配置不当或者使用的 Oracle 客户端版本较低,不支持 `Connect By` 功能。 #### 三、解决步骤 本文将详细介绍如何解决以上问题,并提供了一键解决方法,即通过下载并正确配置 ...
Oracle数据库中的`CONNECT BY PRIOR`是一个强大的查询构造器,用于处理树形数据结构,尤其在组织层级、部门结构或者员工管理系统中非常常见。这个功能允许我们从一个或多个根节点开始,按照指定的规则遍历整个树结构...
通过本文,我们详细介绍了Oracle数据库中 `START WITH` 和 `CONNECT BY` 的使用方法以及应用场景。这两个关键字对于处理具有层级结构的数据非常有用。此外,我们还讨论了如何利用 `LEVEL` 关键字来显示节点所在的...
### Oracle中的START WITH CONNECT BY PRIOR 用法详解 #### 一、概念介绍 在Oracle数据库中,`START WITH` 和 `CONNECT BY PRIOR` 是两个非常强大的特性,主要用于处理具有层级结构的数据。这两个特性可以帮助我们...
通过实例比较了 SYS_CONNECT_BY_PATH 和 CONNECT_BY_ROOT 的异同,和返回树形的数据结构
CONNECT TO "username" IDENTIFIED BY "password" USING 'ODBC:MYSQLSERVERDSN';` 在上面的语句中,我们创建了一个名为“mysqlsrv”的数据库链接,使用 username 和 password 连接 SQL Server 数据库,使用 ODBC ...
`sys_connect_by_path`函数与`CONNECT BY`配合使用,可以追踪节点在整个树中的路径。例如,它可以将每个节点的路径作为字符串返回,便于理解和分析树结构。 在实际应用中,例如组织结构的展示,我们可以创建一个...
`START WITH...CONNECT BY PRIOR`是Oracle SQL中的一个特性,用于处理具有层级关系的数据。这个子句允许我们遍历和查询具有父子关系的数据,例如部门和其下属子部门,或者员工和他们的上级经理。 1. **START WITH...
通过使用 `Connect By Prior` 存储过程,我们可以有效地处理这类问题。本文将详细介绍如何利用 `Connect By Prior` 来构建和查询层次结构数据,并通过具体的例子来解释其工作原理。 #### 核心知识点详解 ### 1. `...