oracle的connect by语句

最新推荐文章于 2024-04-21 22:59:02 发布

weixin_33778544

最新推荐文章于 2024-04-21 22:59:02 发布

阅读量85

点赞数

文章标签：数据库测试

原文链接：https://my.oschina.net/hulubo/blog/52624

版权

为什么80%的码农都做不了架构师？>>>

oracle中可以用CONNECT BY子句实现递归查询，其基本语法是：

select ... from <TableName>
where <Conditional-1>
start with <Conditional-2>
connect by [nocycle]<Conditional-3>
order siblings by <conditional-4>;

<Conditional-1>：过滤条件，用于对返回的所有记录进行过滤。（关联条件和过滤条件有区别，关联条件最先执行）
<Conditional-2>：根节点条件，只用于第一次过滤出根节点。
<Conditional-3>：连接条件，nocycle关键字，防止循环；prior操作符，用于层级条件，没有prior操作符不会发生层级关联，一个connect by语句中可以有多个prior条件，也可以有其他普通条件，但是prior不能用于sequence序列。
<conditional-4>：同级节点排序条件。

执行顺序：
1、语句中有多表关联，先执行关联，无论是join还是where条件中的关联条件。
2、执行start with的条件，选出第一个节点。
3、执行connect by 的条件，层级关联，选出子节点。
4、执行where中的过滤条件，排除结果集中不满足条件的记录，但是不会因为排除一条记录而把它对应的子节点排除。
5、执行order siblings by的排序条件，对同级节点排序。

伪劣：
level：标记层级级数，最上层节点为1，之后为2、3……。
CONNECT_BY_ISCYCLE：标记此节点是否为某一个祖先节点的父节点，导致循环，1为是，0为否。
CONNECT_BY_ISLEAF ：标记此节点是否为叶子节点，即没有子节点，1为是，0为否。
CONNECT_BY_ROOT：标记此节点的祖先节点，后面加列名或表达式，取祖先节点的记录值。

SYS_CONNECT_BY_PATH（column,char）函数：记录根节点到此节点的路径，column是每个节点的路径值，以char分割。column和char都必须是char,varchar2,nchar,或者nvarchar2。
例如：LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path"表示以last_name为路径，'/'分割，记录根到节点的全路径。lpad是优化显示。

（以下来自网上）例子：

select a.child,
       a.parent,
       level "层次",
       sys_connect_by_path(child, '->') "合并层次",
       prior a.child "父节点",
       connect_by_root a.child "根节点",
       decode(connect_by_isleaf, 1, a.child, null) "子节点",
       decode(connect_by_isleaf, 1, '是', '否') "是否子节点"
from test_connect_by a
start with a.parent is null --从parent为空开始扫描
connect by prior a.child = a.parent --以child为父列连接parent
order siblings by child desc --对层次排序
;

一个特殊的使用,使用connect by rownum<=N可以使表的第一行重复N次，如果无记录则无效果：
生成1到10的序列：

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

10 rows selected

借助这个功能，拆分字符串的每一个字符：

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;

转载于:https://my.oschina.net/hulubo/blog/52624