oracle connect by用法

Wesley13
• 阅读 375

Oracle中可以通过**START WITH . . . CONNECT BY . . .**子句来实现SQL的层次查询.
自从Oracle 9i开始,可以通过 SYS_CONNECT_BY_PATH 函数实现将父节点到当前行内容以“path”或者层次元素列表的形式显示出来。

自从Oracle 10g 中,还有其他更多关于层次查询的新特性 。例如,有的时候用户更关心的是每个层次分支中等级最低的内容。
那么你就可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子。如果是叶子就会在伪列中显示“1”,
如果不是叶子而是一个分支(例如当前内容是其他行的父亲)就显示“0”。

在Oracle 10g 之前的版本中,如果在你的树中出现了环状循环(如一个孩子节点引用一个父亲节点),
Oracle 就会报出一个错误提示:“ ORA-01436: CONNECT BY loop in user data”。如果不删掉对父亲的引用就无法执行查询操作。
而在 Oracle 10g 中,只要指定“NOCYCLE”就可以进行任意的查询操作。与这个关键字相关的还有一个伪列——CONNECT_BY_ISCYCLE
如果在当前行中引用了某个父亲节点的内容并在树中出现了循环,那么该行的伪列中就会显示“1”,否则就显示“0”。

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).
It is also being used when an sql execution plan is explained.

syntax:
select ... [start with initial-condition] connect by [nocycle] recurse-condition

level
With level it is possible to show the level in the hierarchical relation of all the data.

--oracle 9i
sys_connect_by_path
With sys_connect_by_path it is possible to show the entire path from the top level down to the 'actual' child.

--oracle 10g
connect_by_root
connect_by_root is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.
connect_by_is_leaf
connect_by_isleaf is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.
connect_by_iscycle
connect_by_is_cycle is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.

下面举例说明:
**[例1]
**创建一个部门表,这个表有4个字段,分别对应部门ID,部门名称,上级部门ID ,本部门直属员工人数。
create table DEP

DEPID      number(10) not null, 
DEPNAME    varchar2(32), 
UPPERDEPID number(10),
NUMOFEMP   number(10)
) ;
下面插入一些数据,结果如下:

[sql] view plaincopy

  1. hr@MYTEST2> select * from dep;  

  2.      DEPID DEPNAME         UPPERDEPID   NUMOFEMP  

  3. ---------- --------------- ---------- ----------  

  4.          0 Dev Center                          2  

  5.          1 DevA                     0          3  

  6.          2 DevA Team1               1         10  

  7.          3 DevA Team2               1          8  

  8.          4 DevB                     0          2  

  9.          5 DevB Team1               4         12  

  10.          6 Test Center                         1  

  11.          7 Test Team1               6          5  

  12.          8 Test Team2               6          5  

  13. 9 rows selected.

  

现在根据“CONNECT BY”来实现树状查询:

[sql] view plaincopy

  1. column depname format a15;  

  2. column rootdep format a15;  

  3. column path format a30;  

  4. select rpad(' ', 2*(level-1), '-') || depname "DEPNAME",  

  5. connect_by_root depname "ROOTDEP",  

  6. connect_by_isleaf "ISLEAF",  

  7. level,  

  8. sys_connect_by_path(depname, '/') "PATH"  

  9. from dep  

  10. start with upperdepid is null  

  11. connect by prior depid = upperdepid  

  12. /

  

结果如下:

[sql] view plaincopy

  1. DEPNAME         ROOTDEP             ISLEAF      LEVEL PATH  

  2. --------------- --------------- ---------- ---------- ---------------------------  

  3. Dev Center      Dev Center               0          1 /Dev Center  

  4.  -DevA          Dev Center               0          2 /Dev Center/DevA  

  5.  ---DevA Team1  Dev Center               1          3 /Dev Center/DevA/DevA Team1  

  6.  ---DevA Team2  Dev Center               1          3 /Dev Center/DevA/DevA Team2  

  7.  -DevB          Dev Center               0          2 /Dev Center/DevB  

  8.  ---DevB Team1  Dev Center               1          3 /Dev Center/DevB/DevB Team1  

  9. Test Center     Test Center              0          1 /Test Center  

  10.  -Test Team1    Test Center              1          2 /Test Center/Test Team1  

  11.  -Test Team2    Test Center              1          2 /Test Center/Test Team2

  

下面计算Dev Center和Test Center部门的总人数:

[sql] view plaincopy

  1. select ROOTDEPID, sum(numofemp) "TOTALEMP"  

  2. from (select connect_by_root depid "ROOTDEPID", numofemp from dep  

  3. start with upperdepid is null  

  4. connect by prior depid = upperdepid)  

  5. group by ROOTDEPID  

  6. /

  

结果如下:

[plain] view plaincopy

  1. ROOTDEPID   TOTALEMP  

  2. --------- ----------  

  3.         6         11  

  4.         0         37

  

**[例2]
**通过CONNECT BY用于十六进度转换为十进制

[sql] view plaincopy

  1. CREATE OR REPLACE FUNCTION f_hex_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2 IS  

  2.     ----------------------------------------------------------------------------------------------------------------------  

  3.     -- 对象名称: f_hex_to_dec  

  4.     -- 对象描述: 十六进制转换十进制  

  5.     -- 输入参数: p_str 十六进制字符串  

  6.     -- 返回结果: 十进制字符串  

  7.     -- 测试用例: SELECT f_hex_to_dec('78A') FROM dual;  

  8.     ----------------------------------------------------------------------------------------------------------------------  

  9.     v_return  VARCHAR2(4000);  

  10.   BEGIN  

  11.     SELECT SUM(DATA) INTO v_return  

  12.       FROM (SELECT (CASE upper(substr(p_str, rownum, 1))  

  13.                      WHEN 'A' THEN '10'  

  14.                      WHEN 'B' THEN '11'  

  15.                      WHEN 'C' THEN '12'  

  16.                      WHEN 'D' THEN '13'  

  17.                      WHEN 'E' THEN '14'  

  18.                      WHEN 'F' THEN '15'  

  19.                      ELSE substr(p_str, rownum, 1)  

  20.                    END) * power(16, length(p_str) - rownum) DATA  

  21.               FROM dual  

  22.             CONNECT BY rownum <= length(p_str));  

  23.     RETURN v_return;  

  24.   EXCEPTION  

  25.     WHEN OTHERS THEN  

  26.       RETURN NULL;  

  27.   END;

  

说明:
1. CONNECT BY rownum <= length(p_str))对输入的字符串进行逐个遍历
2. 通过CASE语句,来解析十六进制中的A-F对应的10进制值

测试结果如下:

[plain] view plaincopy

  1. hr@MYTEST2> variable dec varchar2(32);  

  2. hr@MYTEST2> exec :dec := f_hex_to_dec('1FF');  

  3. PL/SQL procedure successfully completed.  

  4. hr@MYTEST2> print dec  

  5. DEC  

  6. ----------  

  7. 511

  

**[例3]
**通过CONNECT BY生成序列
对于connect by,现在大多数人已经很熟悉了,connect by中的条件就表示了父子之间的连接关系,比如 connect by id=prior pid。

但如果connect by中的条件没有表示记录之间的父子关系
那会出现什么情况?
常见的,connect by会在构造序列的时候使用
用select rownum from dual connect by rownum<xxx 代替早期版本的 select rownum from all_objects where rownum <xxx

我们注意到,dual是一个只有一条记录的表,如果表有多条记录,将会怎样?
下面开始实验
CREATE TABLE T(ID VARCHAR2(1 BYTE));

INSERT INTO T ( ID ) VALUES ( 'A');
INSERT INTO T ( ID ) VALUES ( 'B');
INSERT INTO T ( ID ) VALUES ( 'C');
COMMIT;
然后执行以下查询:

[plain] view plaincopy

  1. hr@MYTEST2> column id format a2;  

  2. hr@MYTEST2> select id,level from t connect by level<2;  

  3. ID      LEVEL  

  4. -- ----------  

  5. A           1  

  6. B           1  

  7. C           1  

  8. hr@MYTEST2> select id,level from t connect by level<3;  

  9. ID      LEVEL  

  10. -- ----------  

  11. A           1  

  12. A           2  

  13. B           2  

  14. C           2  

  15. B           1  

  16. A           2  

  17. B           2  

  18. C           2  

  19. C           1  

  20. A           2  

  21. B           2  

  22. C           2  

  23. 12 rows selected.

  

无需多说,我们很快可以找到其中的规律,假设表中有N条记录
则记F(N,l)为 select id,level from t connect by level<l 的结果集数目
那么,
F(N,1)=N
F(N,l) = F(N,l-1)*N+N

于是可以总结出
F(N,l)=∑power(N,p), p取值为[1,l)

要解释,也很容易。
当连接条件不能限制记录之间的关系时
每一条记录都可以作为自己或者其他记录的叶子
如下所示:
A          1
A          2
A          3
B          3
C          3
B          2
A          3
B          3
C          3
C          2
A          3
B          3
C          3

在这里,我们看到的是
Oracle采用了深度优先的算法

转载自:
http://www.blogjava.net/wxqxs/archive/2008/08/15/222338.html
http://www.cnblogs.com/lettoo/archive/2010/08/03/1791239.html
http://www.itpub.net/thread-994465-1-1.html

点赞
收藏
评论区
推荐文章
blmius blmius
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Jacquelyn38 Jacquelyn38
2年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Peter20 Peter20
2年前
mysql中like用法
like的通配符有两种%(百分号):代表零个、一个或者多个字符。\(下划线):代表一个数字或者字符。1\.name以"李"开头wherenamelike'李%'2\.name中包含"云",“云”可以在任何位置wherenamelike'%云%'3\.第二个和第三个字符是0的值wheresalarylike'\00%'4\
Stella981 Stella981
2年前
Python之time模块的时间戳、时间字符串格式化与转换
Python处理时间和时间戳的内置模块就有time,和datetime两个,本文先说time模块。关于时间戳的几个概念时间戳,根据1970年1月1日00:00:00开始按秒计算的偏移量。时间元组(struct_time),包含9个元素。 time.struct_time(tm_y
Wesley13 Wesley13
2年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Stella981 Stella981
2年前
Android蓝牙连接汽车OBD设备
//设备连接public class BluetoothConnect implements Runnable {    private static final UUID CONNECT_UUID  UUID.fromString("0000110100001000800000805F9B34FB");
Stella981 Stella981
2年前
Django中Admin中的一些参数配置
设置在列表中显示的字段,id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
Wesley13 Wesley13
2年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
Python进阶者 Python进阶者
1个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这