ORACLE未明白语句

转置截取字符串

STR
大
家
好

    SELECT SUBSTR('大家好',LEVEL,1)
      FROM DUAL
    CONNECT BY LEVEL<=LENGTH('大家好');

•  

 

Oracle分割字符串中的所有逗号 REGEXP_SUBSTR

2017年09月06日 11:11:46

阅读数：3271

分割字符串中所有的逗号，然后成多行
参数说明，
参数1: 待分割字符串
参数2：正则表达式
参数3：起始位置，从第几个字符开始正则表达式匹配（默认为1）
参数4：标识第几个匹配组，默认为1
参数5：模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。）


SELECT REGEXP_SUBSTR('34,56,-23', '[^,]+', 1, LEVEL, 'i') AS STR
FROM DUAL
CONNECT BY LEVEL <= LENGTH('34,56,-23') - LENGTH(REGEXP_REPLACE('34,56,-23', ',', '')) + 1;

 

 

 

ORACLE不常用但实用的技巧- 树查询 level用法

2016年03月04日 17:20:31

阅读数：6156

树查询

使用树查询的前提条件是： 在一条记录中记录了当前节点的ID和这个节点的父ID。

注意:一旦数据中出现了循环记录，如两个节点互为对方的父结点，系统就会报 ORA-01436错误(ORA-01436: 用户数据中的CONNECT BY 循环)

 

第一步：创建表

 

[sql] view plain copy

1. create table MENU  
2. (  
3.   MENU_ID   NUMBER not null,  
4.   PARENT_ID NUMBER,  
5.   MENU_NAME NVARCHAR2(20)  
6. );  

 

第二步：插入数据

 

 

[sql] view plain copy

1. insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (1, null,'AAAA');  
2. insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (2, 1, 'BBBB');  
3. insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (3, 1, 'CCCC');  
4. insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (4, 1, 'DDDD');  
5. insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (5, 2, 'EEEE');  
6. insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (6, 2, 'FFFF');  
7. insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (7, 2, 'GGGG');  
8. insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (8, 3, 'HHHH');  
9. commit;  

 

第三步：查询

 

[sql] view plain copy

1. select menu_id,rpad(' ',(level-1)*4)||menu_name from menu  
2. connect by parent_id = prior menu_id  
3. start with parent_id is null  

 

 

显示出树的级别查询

[sql] view plain copy

1. select menu_id,rpad(' ',(level-1)*4)||menu_name,level from menu   
2.  connect by parent_id = prior menu_id   
3.  start with parent_id is null;  

 

 

也可以这样写：

 

[sql] view plain copy

1. select role_id,parent_grade_id,level from audit_role   
2.  where level > 1  
3.  connect by prior role_id = parent_grade_id      
4.  start with role_id=1     
5.  order by level;  

 

 

 

 

或用oracle 取交集函数intersect

select regexp_substr(nme, '[^,]+', 1, rownum) nme
from (select '北京,杭州,上海' nme from dual)
connect by rownum <= length(regexp_replace(nme, '[^,]+')) +1
intersect
select regexp_substr(nme, '[^,]+', 1, rownum) nme
from (select '北京,上海,四川,郑州' nme from dual)

connect by rownum <= length(regexp_replace(nme, '[^,]+')) +1;

 

 

 

   
select * 
  from auth_function af 
 where af.id 
    in 
(select regexp_substr((select a.code
  from auth_user u,user_role r,auth_role a
 where u.username = 'sola' 
   and u.id = r.user_id
   and a.id = r.role_id), '[^,]+', 1, level, 'i') as str
from dual
--connect by level<= 
connect by level <= length((select a.code
  from auth_user u,user_role r,auth_role a
 where u.username = 'sola' 
   and u.id = r.user_id
   and a.id = r.role_id)) - length(regexp_replace((select a.code
  from auth_user u,user_role r,auth_role a
 where u.username = 'sola' 
   and u.id = r.user_id
   and a.id = r.role_id), ',', '')) + 1)