oracle-connect_oracle connect

---

title: oracle-connect
date: 2019-09-22 20:39:04
tags: oracle

connect使用案例

1、获得连续数字列表

select rownum id from dual connect by rownum <= 3;

2、获得子节点

测试数据：

create table t_test_menu(id varchar2(40), parent_id number, menu_name varchar2(40));

insert into t_test_menu(id, parent_id, menu_name) values('10000', '0', '后台系统');
insert into t_test_menu(id, parent_id, menu_name) values('10001', '10000', '系统管理');
insert into t_test_menu(id, parent_id, menu_name) values('10002', '10000', '工单管理');
insert into t_test_menu(id, parent_id, menu_name) values('10003', '10001', '组织管理');
insert into t_test_menu(id, parent_id, menu_name) values('10004', '10001', '用户管理');
insert into t_test_menu(id, parent_id, menu_name) values('10005', '10002', '工单发起');
insert into t_test_menu(id, parent_id, menu_name) values('10006', '10002', '工单审核');
insert into t_test_menu(id, parent_id, menu_name) values('10007', '10002', '工单查询');
commit;
1
2
3
4
5
6
7
8
9
10
11

• 2.1 从根节点查找子节点：

select a.*, level lv
  from t_test_menu a
 start with a.id = '10000'
connect by prior a.id = a.parent_id;
1
2
3
4

• 2.2 从子节点查找上层节点

--第一种,修改prior关键字位置
select a.*, level lv, 
       sys_connect_by_path(a.menu_name, '--') path
  from t_test_menu a
 start with a.id = '10007'
connect by a.id = prior a.parent_id;

---第二种,prior关键字不动 调换后面的id=parent_id逻辑关系的顺序
select a.*, level lv, 
       sys_connect_by_path(a.menu_name, '--') path
  from t_test_menu a
 start with a.id = '10007'
connect by prior a.parent_id = a.id;
1
2
3
4
5
6
7
8
9
10
11
12
13

3、字符串分隔变多行

• 3.1 分割01#02#03#04这种有规律的字符串

select REGEXP_SUBSTR('01#02#03#04', '[^#]+', 1, rownum) as newport 
    from dual connect by rownum <= REGEXP_COUNT('01#02#03#04', '[^#]+');
1
2

4、获得指定范围的数据

• 4.1 获得指定范围数据

create table range_table(range_num varchar2(40));

insert into range_table(range_num) values('1-5');
insert into range_table(range_num) values('20-30');
commit;
1
2
3
4
5

实现1-5,20-30的数据递增返回1、2、3、4、5、20、21、22、23、24、25、26、27、28、29、30总共16条记录。

with temp0 as (
   select t.range_num,
          to_number(REGEXP_SUBSTR(t.range_num, '[^-]+', 1, 1)) minNum, --最小num
          to_number(REGEXP_SUBSTR(t.range_num, '[^-]+', 1, 2)) maxNum --最大num
     from range_table t
)
select t1.range_num ,t2.lv 
       from temp0 t1 join (
         select level lv from dual 
               CONNECT BY LEVEL <= (select max(maxNum) from temp0 )
          ) t2
          on (t2.lv >=t1.minNum and t2.lv <=t1.maxNum);
1
2
3
4
5
6
7
8
9
10
11
12

• 4.2 对不连续数据分组统计

对1、2、3、4、5、20、21、22、23、24、25、26、27、28、29、30总共16条记录统计获得1-5,20-30：

with data_table as(
     select rownum id from dual connect by rownum <= 5
      union all
      select rownum + 19 id from dual connect by rownum <= 11
), temp0 as(
     select a.id, a.id - rownum d_value 
       from data_table a
      order by a.id
)
select min(b.id) || '-' || max(b.id)
  from temp0 b
 group by b.d_value
 order by b.d_value;
1
2
3
4
5
6
7
8
9
10
11
12
13

• 4.3 稍微复杂点的范围数据

初始化数据：

create table range_table2(range_num varchar2(40));

insert into range_table2(range_num) values('1-5,7-8');
insert into range_table2(range_num) values('50-55,60-65,70-75');
commit;
1
2
3
4
5

with temp0 as (
  select b.range_num,
         REGEXP_SUBSTR(b.range_num, '[^,]+', 1, c.lv) as newport,
         to_number(REGEXP_SUBSTR(REGEXP_SUBSTR(b.range_num, '[^,]+', 1, c.lv), '[^-]+', 1, 1)) minNum,
         to_number(REGEXP_SUBSTR(REGEXP_SUBSTR(b.range_num, '[^,]+', 1, c.lv), '[^-]+', 1, 2)) as maxNum
  from (select regexp_count(a.range_num, '[^,]+') AS cnt, 
               range_num
          from range_table2 a) b
  join (select LEVEL lv from dual CONNECT BY LEVEL <= 40) c 
        on c.lv <= b.cnt
)
select t1.range_num,t2.lv 
    from temp0 t1 
    join (
       select level lv from dual 
         CONNECT BY LEVEL <= (
             select max(to_number(maxNum)) from temp0 
         )
       ) t2 
    on ((t2.lv >=t1.minNum and t2.lv <=t1.maxNum));
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20