Oracle connect by 层级查询详解

文章目录

• 1 概述
• 2 语法
• 3 示例
• • 3.1 查询机构信息
  • 3.2 拆分字符串
• 4 常见报错
• • 4.1 CONNECT BY loop in user data
  • 4.2 子查询返回多行

1 概述

1. 主要作用
   (1) 简单树查询（递归查询）,如：'查询机构信息'
   (2) 拆分字符串

 
 

2 语法

select *, level
  from <table_name>
 where ...          -- 可选条件: 过滤条件
 start with ...     -- 可选条件: 查询的起点(根节点)
connect by prior... -- 必选条件: prior 和父节点连用，表示向上查询；反之向下查询
-- 可选 '伪列'
level		       	                  : 节点 '层级'
connect_by_root(column_name) 	      : 返回当前 '根节点'
connect_by_isleaf  			 	      : 判断是否为 '叶子节点'，1：是，0：否
sys_connect_by_path(column_name, '/') : 显示 '详细路径'，并用 '/' 分隔

 
 

示例：查询机构号码为 8611 及其下属机构的所有信息

select t.*
  from branch_info t
 start with t.branch_code = '8611'
connect by prior t.branch_code =  t.parent_code;

 
 

图示：

3 示例

3.1 查询机构信息

with t_branch_info as (
   select '86' branch_code, '0' parent_code, '中国' branch_name from dual
   union all
   select '8611' branch_code, '86' parent_code, '湖北省' branch_name from dual
   union all
   select '8612' branch_code, '86' parent_code, '广东省' branch_name from dual
   union all
   select '861111' branch_code, '8611' parent_code, '武汉市' branch_name from dual
   union all
   select '861112' branch_code, '8611' parent_code, '咸宁市' branch_name from dual
   union all
   select '861211' branch_code, '8612' parent_code, '广州市' branch_name from dual
   union all
   select '861212' branch_code, '8612' parent_code, '深圳市' branch_name from dual
)
select t.*, 
       level 层级,
       connect_by_root(t.branch_code) 根节点,
       connect_by_isleaf 是否叶子节点,
       sys_connect_by_path(t.branch_code, '/') 路径
  from t_branch_info t
 start with t.branch_code = '8611'
connect by t.branch_code = prior t.parent_code;

 
 

测试结果 ：

3.2 拆分字符串

例1：循环 5 次

select rownum, level  -- 两者均可
  from dual
connect by rownum <= 5;

 
 

查询结果：

列2：拆分字符串

--****************************************************************
-- regexp_substr(string, pattern, position, occurrence, modifier)
-- string: 要处理的字符串
-- pattern：正则表达式，[^,]+ ： 至少有一个 ','
-- position: 起始位置，默认 1
-- occurrence: 获取第几个分隔出来的组（字符串分隔后排列成组）
-- modifier: 模式（'i': 不区分大小写，'c': 区分大小写， 默认 'c'）
--****************************************************************
select rownum 次数,
       regexp_substr('abc1,cbd2,db3,db5', '[^,]+', 1, rownum) 字符串
  from dual
connect by rownum <= length('abc1,cbd2,db3,db5') -
           length(replace('abc1,cbd2,db3,db5', ',', '')) + 1;

 
 

查询结果：

4 常见报错

4.1 CONNECT BY loop in user data

报错截图：

报错原因： 子节点 = 父节点，导致进入死循环
解决办法： 增加关键字：nocycle

错误重现：（增加 nocycle 或 修改原数据均可解决问题）

with t_branch_info as (
   -- 子节点 branch_code = 父节点 parent_code
   select '8611' branch_code, '8611' parent_code, '湖北省' branch_name from dual
)
select * 
  from t_branch_info t
 start with t.branch_code = '8611'
connect by /*nocycle*/ prior t.branch_code =  t.parent_code;

 
 

4.2 子查询返回多行

错误截图：

报错原因： xx_level 配置有误
解决办法： 修改 xx_level 配置信息 或 取一条数据

错误重现：

-- 实际开发过程中，我们会通过 xx_level 表示 "层级"
with t_branch_info as (
   select '86' branch_code, '0' parent_code, '01' branch_level, '中国' branch_name from dual
   union all
   select '8611' branch_code, '86' parent_code, '02' branch_level, '湖北省' branch_name from dual
   union all
   -- 此处 branch_level 应是 03
   select '861111' branch_code, '8611' parent_code, '02' branch_level, '武汉市' branch_name from dual
)
select (select t.branch_name
          from t_branch_info t
         where t.branch_level = '02'
           -- and rownum = 1 -- 取一条记录
         start with t.branch_code = t1.branch_code
        connect by prior t.branch_code = t.parent_code) 二级机构
  from t_branch_info t1
 where t1.branch_code = '8611';