Oracle “CONNECT BY” 使用_oracle connect by有什么用

作者：我家自动化 | 2024-06-09 03:18:38

踩

oracle connect by有什么用

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

      
1[ START WITHcondition ] 
2CONNECT  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-- Create table 
2create  table DEP 
3( 
4  DEPID      number(10)not null,
5  DEPNAME    varchar2(256),
6  UPPERDEPID number(10)
7) 
8;

初始化一些数据

      
  
01SQL> INSERTINTO DEP(DEPID, DEPNAME, UPPERDEPID)VALUES (0, '总经办', null);
021 row inserted 
03  
04SQL> INSERTINTO DEP(DEPID, DEPNAME, UPPERDEPID)VALUES (1, '开发部', 0); 
051 row inserted 
06  
07SQL> INSERTINTO DEP(DEPID, DEPNAME, UPPERDEPID)VALUES (2, '测试部', 0); 
081 row inserted 
09  
10SQL> INSERTINTO DEP(DEPID, DEPNAME, UPPERDEPID)VALUES (3, 'Sever开发部', 1); 
111 row inserted 
12  
13SQL> INSERTINTO DEP(DEPID, DEPNAME, UPPERDEPID)VALUES (4, 'Client开发部', 1); 
141 row inserted 
15  
16SQL> INSERTINTO DEP(DEPID, DEPNAME, UPPERDEPID)VALUES (5, 'TA测试部', 2); 
171 row inserted 
18  
19SQL> INSERTINTO DEP(DEPID, DEPNAME, UPPERDEPID)VALUES (6, '项目测试部', 2); 
201 row inserted 
21  
22SQL> commit;
23Commit  complete

      
  
01SQL> SELECT* FROM DEP;  
02  
03      DEPID DEPNAME                                                                           UPPERDEPID
04----------- -------------------------------------------------------------------------------- -----------
05          0 General Deparment                                                                
06          1 Development                                                                                0
07          2 QA                                                                                         0
08          3 Server Development                                                                         1
09          4 Client Development                                                                         1
10          5 TA                                                                                         2
11          6 Porject QA                                                                                 2
12  
137 rowsselected

现在我要根据“CONNECT BY”来实现树状查询结果

      
  
01SQL> SELECTRPAD( ' ', 2*(LEVEL-1),'-' ) || DEPNAME "DEPNAME", 
02CONNECT_BY_ROOT DEPNAME "ROOT",
03CONNECT_BY_ISLEAF "ISLEAF",
04LEVEL  , 
05SYS_CONNECT_BY_PATH(DEPNAME, '/')"PATH"
06FROM  DEP 
07START WITHUPPERDEPID IS NULL
08CONNECT  BY PRIOR DEPID = UPPERDEPID;
09  
10DEPNAME                        ROOT                    ISLEAF     LEVEL PATH 
11------------------------------ ------------------- ---------- ---------- --------------------------------------------------------------------------------
12General Deparment              General Deparment            0          1 /General Deparment
13 -Development                  General Deparment            0          2 /General Deparment/Development
14 ---Server Development         General Deparment            1          3 /General Deparment/Development/Server Development
15 ---Client Development         General Deparment            1          3 /General Deparment/Development/Client Development
16 -QA                           General Deparment            0          2 /General Deparment/QA
17 ---TA                         General Deparment            1          3 /General Deparment/QA/TA
18 ---Porject QA                 General Deparment            1          3 /General Deparment/QA/Porject QA
19                                                     
207 rowsselected

说明：
1. CONNECT_BY_ROOT 返回当前节点的最顶端节点
2. CONNECT_BY_ISLEAF 判断是否为叶子节点，如果这个节点下面有子节点，则不为叶子节点
3. LEVEL 伪列表示节点深度
4. SYS_CONNECT_BY_PATH函数显示详细路径，并用“/”分隔

[例2]

通过CONNECT BY生成序列

      
  
01SQL> SELECTROWNUM FROM DUAL  CONNECT BYROWNUM <= 10; 
02  
03    ROWNUM
04---------- 
05         1
06         2
07         3
08         4
09         5
10         6
11         7
12         8
13         9
14        10
15  
1610 rowsselected

[例3]

通过CONNECT BY用于十六进度转换为十进制

      
  
01CREATE  OR REPLACE FUNCTIONf_hex_to_dec(p_str INVARCHAR2)  RETURN VARCHAR2 IS
02    ----------------------------------------------------------------------------------------------------------------------
03    -- 对象名称: f_hex_to_dec
04    -- 对象描述: 十六进制转换十进制
05    -- 输入参数: p_str 十六进制字符串
06    -- 返回结果: 十进制字符串
07    -- 测试用例: SELECT f_hex_to_dec('78A') FROM dual;
08    ----------------------------------------------------------------------------------------------------------------------
09    v_return  VARCHAR2(4000);
10  BEGIN
11    SELECTSUM(DATA) INTOv_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                     ELSEsubstr(p_str, rownum, 1) 
20                   END) * power(16, length(p_str) - rownum) DATA
21              FROMdual 
22            CONNECTBY rownum <= length(p_str));
23    RETURNv_return; 
24  EXCEPTION
25    WHENOTHERS THEN
26      RETURNNULL; 
27  END;

说明：

1. CONNECT BY rownum <= length(p_str))对输入的字符串进行逐个遍历

2. 通过CASE语句，来解析十六进制中的A-F对应的10进制值

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/我家自动化/article/detail/692376