oracle数据库中CONNECT BY递归查找树和拆分字符串

oracle中查询树结构和行转列

CONNECT BY使用详解

---

一、CONNECT BY查询树结构

创建表

CREATE TABLE TEST_TREE (
  ID VARCHAR2(50),
  NAME VARCHAR2(50),
  TREE_ID VARCHAR2(50)
);

插入测试数据

INSERT INTO TEST_TREE(ID,NAME,TREE_ID) values('1','根节点1','0');
INSERT INTO TEST_TREE(ID,NAME,TREE_ID) values('2','节点2','1');
INSERT INTO TEST_TREE(ID,NAME,TREE_ID) values('3','节点3','1');
INSERT INTO TEST_TREE(ID,NAME,TREE_ID) values('4','节点4','2');
INSERT INTO TEST_TREE(ID,NAME,TREE_ID) values('5','节点5','3');

插入数据组成一个简单的树形结构

从子节点查父节点

SELECT DISTINCT ID,NAME,TREE_ID FROM TEST_TREE START WITH ID in ('4','5') 
CONNECT BY NOCYCLE ID =PRIOR TREE_ID 

1.第一步 START WITH根据节点ID查询节点4和节点5，相当于where，START WITH需要配合CONNECT BY使用，前者作为递归的起始，后者作为递归的结束，依次递归匹配到的每一行数据，要想在当前数据行中匹配其他行数据使用 PRIOR关键字

2.第二步 CONNECT BY根据起始数据开始递归，ID为开始递归的起始数据(‘4’，‘5’)，PRIOR后跟需要找的其他数据行中的字段 TREE_ID。每次递归后匹配的行都会查询出来并且当成下次递归的起始数据，再次判断，一直向上到根节点

3.如果是从节点2开始找下级节点如下SQL
SELECT DISTINCT ID,NAME,TREE_ID FROM TEST_TREE START WITH ID = ‘2’
CONNECT BY NOCYCLE TREE_ID =PRIOR ID
先找节点2，再根据节点2的TREE_ID字段匹配其他数据的ID字段

问题
1.使用CONNECT BY查询递归数据的时候会有重复数据，节点4和节点5的共同上级都是根节点1，所以如果不加DISTINCT的时候根节点会查询出来两次
2.当数据父子节点关系不正确的时候，查询报错
ORA-30009 内存不足
ORA-01436 死循环
使用 CONNECT BY NOCYCLE 可以防止单层循环，但是数据循环依赖的时候还是会报错
没啥好办法，遇到循环LOOP先解决问题数据，不建议用NOCYCLE ，查询出来的数据可能不对。

 可以解决如下数据
INSERT INTO TEST_TREE(ID,NAME,TREE_ID) values('2','节点2','2');
不能解决循环
INSERT INTO TEST_TREE(ID,NAME,TREE_ID) values('4','节点4','2');
INSERT INTO TEST_TREE(ID,NAME,TREE_ID) values('2','节点2','4');

二、CONNECT BY和REGEXP_SUBSTR实现行转列

行转列查询

建表

CREATE TABLE TEST_DATA (ID NUMBER, F_VALUE VARCHAR2(100));

插入测试数据

INSERT INTO TEST_DATA (ID,F_VALUE) VALUES (1, 'A;B;C');
INSERT INTO TEST_DATA (ID,F_VALUE) VALUES (2, 'A;B;C;D;E;F');

查询，将F_VALUE字段按照分号分割后将分割后的数据转换成列

SELECT  ID,
REGEXP_SUBSTR(F_VALUE, '[^;]+', 1, LEVEL) AS VALUE,LEVEL
FROM (SELECT ID,F_VALUE FROM TEST_DATA WHERE ID = '1')
CONNECT BY REGEXP_SUBSTR(F_VALUE, '[^;]+', 1, LEVEL) IS NOT NULL

REGEXP_SUBSTR使用正则拆分字符串，该方法有三个参数
(1)要搜索的字符串
(2)正则表达式模式
(3)要提取的子字符串的起始位置
而LEVEL是一个伪列，用于表示层次结构中的层次深度，在oracle中用来和CONNECT BY语句配合做递归查询

行转列重复数据问题

当行转列的时候发现就查询单条记录的时候不会有什么问题，但是当数据多了，字符串分割后的数据就会有重复的，数据量稍微一多就会出现死循环

SELECT  ID,
REGEXP_SUBSTR(F_VALUE, '[^;]+', 1, LEVEL) AS VALUE,LEVEL
FROM (SELECT ID,F_VALUE FROM TEST_DATA WHERE ID in ('1','2'))
CONNECT BY REGEXP_SUBSTR(F_VALUE, '[^;]+', 1, LEVEL) IS NOT NULL

如上sql应该就查询出来9行，结果出来38行数据

主要还是循环递归导致的，在递归查询的时候CONNECT BY 仅仅判断了字符串是否为空，没有提供任何子父行的匹配条件，那在多行数据中意味着每一行都是其他行的子行，数据量异常庞大。
解决办法:添加父子行限制条件，并且添加guid使得每行独立。
完整sql如下
SELECT ID,
REGEXP_SUBSTR(F_VALUE, ‘[^;]+’, 1, LEVEL) AS value
FROM TEST_DATA
CONNECT BY ID = PRIOR ID
AND REGEXP_SUBSTR(F_VALUE, ‘[^;]+’, 1, LEVEL) IS NOT NULL
AND prior sys_guid() is not NULL

查询出来的数据就正常了，并且不会卡死循环

总结

• CONNECT BY用于做树结构查询的时候，一定要保证树结构正确，没有父子关系异常的数据，否则出现报错
• 字符串拆分并且行转列时候一定要写明父子关系并且加guid，否则每一行不会独立计算，会出现死循环