Oracle “CONNECT BY” 使用

来源：互联网发布：数据维护编辑：程序博客网时间：2024/05/11 19:14

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

-- DROP TALBEDROP TABLE dep CASCADE CONSTRAINT;COMMIT;-- CREATE TABLECREATE TABLE dep( depid      NUMBER(10) NOT NULL,  depname    VARCHAR2(256),  upperdepid NUMBER(10)  );COMMIT;

初始化一些数据

INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (0, 'General Deparment', null);INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (1, 'Development', 0);INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (2, 'QA', 0);INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (3, 'Server Development', 1);INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (4, 'Client Development', 1);INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (5, 'TA', 2);INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (6, 'Porject QA', 2);COMMIT;

SELECT * FROM dep; DEPID       DEPNAME                     UPPERDEPID-----       -------                     ----------0           General Deparment          1           Development                 02           QA                          03           Server Development          14           Client Development          15           TA                          26           Porject QA                  2

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

SELECT RPAD( ' ', 2*(LEVEL-1), '-' ) || DEPNAME "DEPNAME",CONNECT_BY_ROOT DEPNAME "ROOT",CONNECT_BY_ISLEAF "ISLEAF",LEVEL ,SYS_CONNECT_BY_PATH(DEPNAME, '/') "PATH"FROM DEPSTART WITH UPPERDEPID IS NULLCONNECT BY PRIOR DEPID = UPPERDEPID;

DEPNAME                             ROOT                        ISLEAF          LEVEL           PATHGeneral 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

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

[例2]
通过CONNECT BY生成序列

SQL> SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM <= 10;     ROWNUM----------         1         2         3         4         5         6         7         8         9        10 已选择10行。

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

CREATE OR REPLACE FUNCTION f_hex_to_dec(p_str IN VARCHAR2) 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 (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进制值

0 0