ORACLE CONNECT BY 整理

最新推荐文章于 2024-03-21 15:30:56 发布

原创最新推荐文章于 2024-03-21 15:30:56 发布 · 462 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #connect by

ORACLE 专栏收录该内容

9 篇文章

订阅专栏

本文介绍了在工作中遇到的一个关于Oracle的CONNECT BY查询优化问题。原有的实现方式使用游标遍历，导致执行效率低下。通过深入研究，发现了CONNECT_BY_ROOT函数可以替代游标，并整理了CONNECT BY的一些实用技巧，如PRIOR控制级联方向、NOCYCLE防止死循环以及LEVEL显示级联层级。文章还分享了一个利用CONNECT BY解决的有趣案例。

工作中一次 connect by 优化记录

有一个需求要查询所有下级级联关系，拿到的业务做的实现如下，

这里可能业务人员不知道CONNECT_BY_ROOT这个函数用游标遍历一遍，生产环境由于游标的基数过大，导致此存储一天都没有执行完成。

DECLARE
CURSOR CU_TEMP IS  SELECT * FROM SCOTT.EMP;
TYPE_EMP SCOTT.EMP%ROWTYPE;
V_SQL VARCHAR2(3000):='';
BEGIN
        OPEN CU_TEMP;
        LOOP  FETCH CU_TEMP INTO TYPE_EMP;
                EXIT WHEN CU_TEMP%NOTFOUND;
                V_SQL:='INSERT INTO T1(CROOT,EMPNO,MGR)'|| 'SELECT '''||TYPE_EMP.EMPNO||''',EMPNO,MGR FROM SCOTT.EMP START WITH EMPNO='||TYPE_EMP.EMPNO||' CONNECT BY PRIOR EMPNO =  MGR';
                DBMS_OUTPUT.PUT_LINE(V_SQL);
                EXECUTE IMMEDIATE V_SQL;
        END LOOP;
        CLOSE CU_TEMP;
END;
/

在研究存储过程后发现，这样做的唯一可参考的地方就是在第一列展示根节点，但是这个可以用 CONNECT_BY_ROOT来代替~

于是整理了一下CONNECT BY 的一些用法

SELECT 
        SYS_CONNECT_BY_PATH(EMPNO,'/') C_PATH/*级联路径*/,
        CONNECT_BY_ROOT(EMPNO) C_ROOT/*获取根节点*/,
        CONNECT_BY_ISLEAF C_ISLEAF/*1表示最小子节点，0表示还存在子节点*/,
        CONNECT_BY_ISCYCLE C_CYCLE,/*循环级联到最后一级，此值为1*/
        LEVEL/*级联的层级*/,
        EMPNO,MGR
FROM TT
--WHERE EMPNO=7839 /*级联的范围，总记录结果集的范围*/
START WITH EMPNO=7839 /*确定开始位置*/
CONNECT BY NOCYCLE PRIOR EMPNO =  MGR /*PRIOR 会循环把这个变量赋值上一个级联的MGR的值，NOCYCLE 防止存在死循环的级联*/
ORDER BY LEVEL

自己觉得比较实用的一些用法

--PRIOR 此关键词的作用是会向下替换MGR的值，控制级联方向。

--NOCYCLE 防止级联中的死循环

with t as
(
select 1 c1,2 c2 from dual
union all
select 2,3 from dual
union all
select 3,1 from dual
)
select c1,c2 from t connect by prior c1 = c2 start with c1=1

ORA-01436: 用户数据中的 CONNECT BY 循环

修改之后

with t as
(
select 1 c1,2 c2 from dual
union all
select 2,3 from dual
union all
select 3,1 from dual
)
select c1,c2,connect_by_ISCYCLE  from t connect by NOCYCLE  prior c1 = c2 start with c1=1

结果

c1 c2 CONNECT_BY_ISCYCLE

1 2 0
3 1 0
2 3 1

--LEVEL 第几级关联

网上发现的一些经典案例：

①字符‘12313’各位相加 1+2+3+1+3

SELECT SUM(C_SUBSTR) FROM
(
SELECT TO_NUMBER(SUBSTR('12313',LEVEL,1)) C_SUBSTR,LEVEL FROM DUAL CONNECT BY LEVEL <= LENGTH('12313')
)

②生成日期序列

SELECT TO_CHAR(SYSDATE-LEVEL+1,'YYYYMMDD') C_DATE FROM DUAL CONNECT BY LEVEL<=10