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';