#ORACLE 每日一点#Oracle CONNECT BY 使用
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结合起来使用
例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进制值
上一篇: MySQL中空格与空字符串的问题
推荐阅读