欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

#ORACLE 每日一点#Oracle CONNECT BY 使用

程序员文章站 2022-06-03 18:37:52
...

Oracle “CONNECT BY”是层次查询子句,一般用于树状或者层次结果集的查询。其语法是: [ START WITHcondition ]CONNECT BY [ NOCYCLE ] condition The start with .. connect by clause can be used to select data that has a hierarchical relationship (

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

[ START WITHcondition ]
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)notnull,
  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 rowsselected 



现在我要根据“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     LEVELPATH
------------------------------ ------------------- ---------- ---------- --------------------------------------------------------------------------------
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 rowsselected 


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

例2

通过CONNECT BY生成序列

SQL> SELEC TROWNUM FROM DUAL CONNECT BY ROWNUM 

例3

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

CREATE OR REPLACE FUNCTION f_hex_to_dec(p_strINVARCHAR2)RETURN VARCHAR2 IS
    ----------------------------------------------------------------------------------------------------------------------
    -- 对象名称: f_hex_to_dec
    -- 对象描述: 十六进制转换十进制
    -- 输入参数: p_str 十六进制字符串
    -- 返回结果: 十进制字符串
    -- 测试用例: SELECT f_hex_to_dec('78A') FROM dual;
    ----------------------------------------------------------------------------------------------------------------------
    v_return  VARCHAR2(4000);
  BEGIN
    SELECT SUM(DATA)INTO v_return
      FROM(SELECT (CASEupper(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 

说明:

1. CONNECT BY rownum

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