ORACLE层次查询学习 level connect by



层次查询的概念

语法格式：

select [level], column, expr... from table
　　[where condition]
　　start with condition
　　connect by [prior column1= column2 |
　　column1 = prior column2];

层次查询是通过start with和connect by子句标识的：

1.其中level关键字是可选的，表示等级，1表示root,2表示root的child,其他相同的规则。

2.From之后可以是table,view但是只能是一个table。

3.Where条件限制了查询返回的行，但是不影响层次关系，属于将节点截断，但是这个被截断的节点的下层child不受影响。

4.Start with是表示开始节点，对于一个真实的层次关系，必须要有这个子句，但是不是必须的。

5.connect by prior是指定父子关系，其中prior的位置不一定要在connect by之后，对于一个真实的层次关系，这也是必须的。

对于from是视图的，那么这个view不能包含join。

层次查询限制

1.层次查询from之后如果是table，只能是一个table，不能有join。

2.from之后如果是view，则view不能是带join的。

3.使用order by子句，order子句是在等级层次做完之后开始的，所以对于层次查询来说没有什么意义，除非特别关注level，获得某行在层次中的深度，但是这两种都会破坏层次。见增强特性中的使用siblings排序。

4.在start with中表达式可以有子查询，但是connect by中不能有子查询。

层次查询的增强特性

1、SYS_CONNECT_BY_PATH

Oracle 9i提供了sys_connect_by_path(column,char),其中column是字符型或能自动转换成字符型的列名。它的主要目的就是将父节点到当前节点的”path”按照指定的模式展现出现。这个函数只能使用在层次查询中。

下面的是oracle10g新增特性

2、 CONNECT_BY_ISLEAF

   在oracle9i的时候，查找指定root下的叶子节点，是很复杂的，oracle10g引入了一个新的函数，connect_by_isleaf,如果行的值为0表示不是叶子节点，1表示是叶子节点。

3、CONNECT_BY_ISCYCLE和NOCYCLE关键字

   如果从root节点开始找其子孙，找到一行，结果发生和祖先互为子孙的情况，则发生循环，oracle会报ORA-01436: CONNECT BY loop in user data，在9i中只能将发生死循环的不加入到树中或删除，在10g中可以用nocycle关键字加在connect by之后，避免循环的参加查询操作。并且通过connect_by_iscycle得到哪个节点发生循环。0表示未发生循环，1表示发生了循环。

4、CONNECT_BY_ROOT

   Oracle10g新增connect_by_root,用在列名之前表示此行的根节点的相同列名的值。

5、使用SIBLINGS关键字排序

   对于层次查询如果用order by排序，比如order by last_name则是先做完层次获得level,然后按last_name排序，这样破坏了层次，比如特别关注某行的深度，按level排序，也是会破坏层次的。

    在oracle10g中，增加了siblings关键字的排序。

语法：order siblings  by <expre>

它会保护层次，并且在每个等级中按expre排序。

示例：

1、构建测试表与插入测试语句

 

[c-sharp] view plaincopyprint?

1. create table tab_connect_by (child number,parent number);  
2. insert into tab_connect_by (CHILD, PARENT) values(2, 5);  
3. insert into tab_connect_by (CHILD, PARENT) values(3, 5);  
4. insert into tab_connect_by (CHILD, PARENT) values(10, 15);  
5. insert into tab_connect_by (CHILD, PARENT) values(5, 15);  
6. insert into tab_connect_by (CHILD, PARENT) values(9, 17);  
7. insert into tab_connect_by (CHILD, PARENT) values(8, 17);  
8. insert into tab_connect_by (CHILD, PARENT) values(15, 38);  
9. insert into tab_connect_by (CHILD, PARENT) values(17, 38);  
10. insert into tab_connect_by (CHILD, PARENT) values(6, 38);  
11. insert into tab_connect_by (CHILD, PARENT) values(13, 26);  
12. insert into tab_connect_by (CHILD, PARENT) values(1, 26);  
13. insert into tab_connect_by (CHILD, PARENT) values(12, 26);  
14. insert into tab_connect_by (CHILD, PARENT) values(11, 18);  
15. insert into tab_connect_by (CHILD, PARENT) values(7, 18);  
16. insert into tab_connect_by (CHILD, PARENT) values(38, null);  
17. insert into tab_connect_by (CHILD, PARENT) values(26, null);  
18. insert into tab_connect_by (CHILD, PARENT) values(18, null);  
19. commit;  

create table tab_connect_by (child number,parent number);insert into tab_connect_by (CHILD, PARENT) values(2, 5);insert into tab_connect_by (CHILD, PARENT) values(3, 5);insert into tab_connect_by (CHILD, PARENT) values(10, 15);insert into tab_connect_by (CHILD, PARENT) values(5, 15);insert into tab_connect_by (CHILD, PARENT) values(9, 17);insert into tab_connect_by (CHILD, PARENT) values(8, 17);insert into tab_connect_by (CHILD, PARENT) values(15, 38);insert into tab_connect_by (CHILD, PARENT) values(17, 38);insert into tab_connect_by (CHILD, PARENT) values(6, 38);insert into tab_connect_by (CHILD, PARENT) values(13, 26);insert into tab_connect_by (CHILD, PARENT) values(1, 26);insert into tab_connect_by (CHILD, PARENT) values(12, 26);insert into tab_connect_by (CHILD, PARENT) values(11, 18);insert into tab_connect_by (CHILD, PARENT) values(7, 18);insert into tab_connect_by (CHILD, PARENT) values(38, null);insert into tab_connect_by (CHILD, PARENT) values(26, null);insert into tab_connect_by (CHILD, PARENT) values(18, null);commit;

2、查询语句1

 

[c-sharp] view plaincopyprint?

1. select a.child,  
2.        a.parent,  
3.        level "层次",  
4.        sys_connect_by_path(child, '<-') "合并层次",  
5.        prior a.child "父节点",  
6.        connect_by_root a.child "根节点",  
7.        decode(connect_by_isleaf, 1, a.child, null) "子节点",  
8.        decode(connect_by_isleaf, 1, '是', '否') "是否子节点"  
9.   from tab_connect_by a  
10.  start with a.parent is null --从parent为空开始扫描  
11. connect by prior a.child = a.parent --以child为父列连接parent  
12.  order siblings by child desc --对层次排序  
13. ;  

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 tab_connect_by a start with a.parent is null --从parent为空开始扫描connect by prior a.child = a.parent --以child为父列连接parent order siblings by child desc --对层次排序;

3、查询语句2

 

[c-sharp] view plaincopyprint?

1. Select level, connect_by_iscycle,connect_by_isleaf,parent, child   
2. 　　From tab_connect_by   
3. 　　Connect by nocycle prior child = parent   
4. 　　Start with parent is null;  

connect by level 应用

1、当需要把一个字符串按某一分隔符分隔后，变为数据列，即把字符串行变为列，可以使用level关键字,例子：

 

[sql] view plaincopyprint?

1. with t as  
2.  (select 'a;b;c;d;e' as str from dual)  
3. select level,  
4.        t.str,  
5.        substr(t.str, 2 * (level - 1) + 1, 1) as str_signle  
6.   from t  
7. connect by level <= length(t.str) - length(replace(t.str, ';', '')) + 1;  

with t as (select 'a;b;c;d;e' as str from dual)select level,   t.str,   substr(t.str, 2 * (level - 1) + 1, 1) as str_signle  from tconnect by level <= length(t.str) - length(replace(t.str, ';', '')) + 1;

 

运行结果：

 

2、上面的写法只是适用于一般有规律的字符串行，当遇到不规则字符串行时，可以使用oracle的正则表达式函数，请看下面的例子：

 

[sql] view plaincopyprint?

1. with t as  
2.  (select 'i;am;a;test;hahahhah' as str from dual)  
3. select level,  
4.        str,  
5.        regexp_substr(t.str, '[^;]+', 1, level) str_single  
6.   from t  
7. connect by level <= length(t.str) - length(replace(t.str, ';', '')) + 1;  

with t as (select 'i;am;a;test;hahahhah' as str from dual)select level,   str,   regexp_substr(t.str, '[^;]+', 1, level) str_single  from tconnect by level <= length(t.str) - length(replace(t.str, ';', '')) + 1;

 

运行结果：

或者不使用正则表达式：

 

[sql] view plaincopyprint?

1. with t_org as  
2.  (select 'I am a complicated string' as str from dual),  
3. t_sep as  
4.  (select ' ' as sep from dual),  
5. t as  
6.  (select t_org.str as orign_str,  
7.          t_sep.sep || t_org.str || t_sep.sep as str  
8.     from t_org,  
9.          t_sep)  
10. select level,  
11.        t.orign_str,  
12. /*     instr(t.str, t_sep.sep, 1, level) as separator_postion,  
13.        instr(t.str, t_sep.sep, 1, level) + 1 as str_postion_begin,  
14.        instr(t.str, t_sep.sep, 1, level + 1) -  
15.        instr(t.str, t_sep.sep, 1, level) - 1 as str_single_len,*/  
16.        substr(t.str, instr(t.str, t_sep.sep, 1, level) + 1, instr(t.str, t_sep.sep, 1, level + 1) -  
17.                instr(t.str, t_sep.sep, 1, level) - 1) as str_signle  
18.   from t,  
19.        t_sep  
20. connect by level < length(t.str) - length(replace(t.str, t_sep.sep, ''));  

Oracle中REGEXP_SUBSTR函数

 

Oracle中REGEXP_SUBSTR函数的使用说明：

 

题目如下:

在oracle中，使用一条语句实现将'17,20,23'拆分成'17'，'20'，'23'的集合。

 

REGEXP_SUBSTR函数格式如下：

function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)

__srcstr     ：需要进行正则处理的字符串

__pattern    ：进行匹配的正则表达式

__position   ：起始位置，从第几个字符开始正则表达式匹配（默认为1）

__occurrence ：标识第几个匹配组，默认为1

__modifier   ：模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。）

 

1、查询使用正则分割后的第一个值，也就是17

[sql] 

SELECT REGEXP_SUBSTR('17,20,23','[^,]+',1,1,'i') AS STR FROM DUAL;  

结果:  www.2cto.com  

STR

-----

17

 

2、查询使用正则分割后的最后一个值，也就是23

[sql] 

SELECT REGEXP_SUBSTR('17,20,23','[^,]+',1,3,'i') AS STR FROM DUAL;  

结果:

STR

----

23