(5/14) oracle查询优化的改写笔记第五章使用字符串

以你之姓@ 2023-02-20 13:37 9阅读 0赞

-- 生成连续数值
    select rownum as rn from emp where rownum <= 3;
    
    select rownum as rn from all_objects where rownum<=3;
    
    select level as rn from dual connect by level <=3;
    
    -- 5.2 遍历字符串
    create or replace view v5_2 as 
    select '天天向上' as 汉字,'TTXS' as 首拼 from dual;
    
    select 汉字, 首拼, LEVEL from v5_2 connect by LEVEL<= length(汉字);
    
    select 汉字, 
    	首拼, 
    	LEVEL ,
    	substr(汉字,LEVEL,1) as 汉字拆分,
    	substr(首拼，LEVEL, 1) as 首拼拆分,
    	'substr('''|| 汉字 || ''','||LEVEL || ',1)' AS fun 
    from v5_2 
    connect by LEVEL<= length(汉字);
    
    select substr('天天向上',3,1) from dual;
    
    -- 5.3 计算字符在字符串中出现的次数
    create or replace view v5_3 as 
    select 'CLARK,KING,MILLER' AS str from dual;
    
    select regexp_count(str,',') +1 as cnt from v5_3;
    
    -- 如果分隔符一个以上，计算出来的长度除以分隔符长度
    create or replace view v5_3_2 as 
    select '10$#CLARK$#MANAGER' as str from dual;
    
    select length(translate(str, '$#'||str,'$#'))/length('$#')+1 as cnt from v5_3_2;
    
    select regexp_count(str, '\$#')+1 as cnt from v5_3_2;
    
    -- 5.4 从字符串删除不需要的字符
    select ename, replace(translate(ename,'AEIOU','aaaaa'),'a','') stripped1 from emp 
    where deptno=10;
    
    select ename, translate(ename,'1AEIOU','1') stripped1 from emp where deptno=10;
    
    select ename, regexp_replace(ename, '[AEIOU]') as stripped1 from emp;
    
    -- 5.5 将字符串和数字数据分离
    drop table t5_5 purge;
    create table t5_5 as select dname || deptno as data from dept;
    
    select data from t5_5;
    
    select regexp_replace(data,'[0-9]','') dname ,regexp_replace(data,'[^0-9]','') deptno  from t5_5;
    
    select translate(data, 'a0123456789','a') dname, translate(data, '0123456789'||data, '0123456789') deptno from t5_5;
    
    -- 5.6 查询只包含字母或数字型的数据
    create or replace view v5_6 as
    select '123' as data from dual union all
    select 'abc' as data from dual union all
    select '123abc' as data from dual union all
    select 'abc123' as data from dual union all
    select 'a1b2c3' as data from dual union all
    select 'a1b2c3#' as data from dual union all
    select '3$' as data from dual union all
    select 'a 2' as data from dual;
    
    select data from v5_6 where regexp_like(data, '^[0-9a-zA-Z]+$');
    
    create or replace view v5_6_2 as
    select 'A' as data from dual union all
    select 'AB' as data from dual union all
    select 'BA' as data from dual union all
    select 'BAC' as data from dual ;
    
     -- 正则表达式 开头 结尾
     select * from v5_6_2 where regexp_like(data,'^A');
     select * from v5_6_2 where regexp_like(data,'A$');
     select * from v5_6_2 where regexp_like(data,'^A$');
     
    create or replace view v5_6_3 as
    select '167' as str from dual union all
    select '1234567' as str from dual ;
    
    select * from v5_6_3;
    
    select * from v5_6_3 where regexp_like(str,'16+');
    select * from v5_6_3 where regexp_like(str,'16*');
    
    drop table t5_6 purge;
    create table t5_6 as
    select * from (with x0 as (select level as lv from dual connect by level<= 3)
    
    drop table t5_6 purge;
    create table t5_6 as 
    select *
    from (with x0 as (select level as lv from dual connect by level<=3)
    select replace(sys_connect_by_path(lv,','),',') as s from x0 connect by nocycle prior lv<> lv)
    where length(s)<=2; 
    
    insert into t5_6 values(null);
    select * from t5_6;
    
    -- 5.7 提取姓名的大写首字母
    create or replace view v5_7 as 
    select 'Michael Hartsten'  as a1 from dual;
    select regexp_replace(a1,'([A-Z])([^A-Z]*)(A-Z)([^A-Z,]*)','\1.\3') as sx from v5_7; 
    
    -- 5.8 根据表中的行创建一个分隔列表
    select deptno, 
    	sum(sal) as total_sal,
    	listagg(ename, ',') within group (order by ename) as total_ename from emp
    	group by deptno;
    
    -- 5.9 提取第n个分隔的子串
    create or replace view v5_9 as 
    select 
    	listagg(ename, ',') within group (order by ename) as name from emp
    	where deptno in (10, 20)
    	group by deptno;
    
    select * from (select substr(name,1,3) from v5_9);
    
    select name,
    	第二个逗号后的位置,
    	第三个逗号后的位置,
    	长度,
    	substr(name, 第二个逗号后的位置，长度) as 子串
    from ( select name,
    		instr(src.name,',',1,2) +1 as 第二个逗号后的位置,
    		instr(src.name,',',1,(2+1)) as 第三个逗号后的位置,
    	    instr(src.name,',',1,(2+1)) - instr(src.name, ',', 1,2) -1 as 长度 
    		from ( select ',' || name || ',' as name from v5_9) src	
    	) x;
    
    select * from v5_9;
    
    
    select regexp_substr(name,'[^,]+',1,2) as 子串 from v5_9;
    
    --5.10 分解ip地址
    select regexp_substr(ip, '[^.]+', 1,1) a,
    		regexp_substr(ip, '[^.]+', 1,2) b,
    		regexp_substr(ip, '[^.]+', 1,3) c,
    		regexp_substr(ip, '[^.]+', 1,4) d
    from (select '192.168.1.118' as ip from dual);
    
    -- 将分隔数据转换为多值IN列表
    select * from emp where ename in ('CLARK,KING,MILLER')
    
    create or replace view v5_11 as 
    select 'CLARK,KING,MILLER' AS emps from dual;
    
    select regexp_substr(emps, '[^,]+', 1, level) as ename,
    	level
    	from v5_11 
    	connect by level <= (length(translate(emps, ',' || emps,','))+1);
    
    select deptno, ename, empno from emp where ename in (
    	select regexp_substr(emps, '[^,]+', 1, level) as ename
    	from v5_11 
    	connect by level <= (length(translate(emps, ',' || emps,','))+1)
    );
    
    -- 5.12 组合去重
    create or replace view v5_12 as
    select '白菜,猪肉' as c1 from dual
    union all
    select '土豆,牛肉' as c1 from dual
    union all
    select '牛肉,土豆' as c1 from dual;
    
    var v_c1 varchar2;
    exec :v_c1 := '土豆,牛肉';
    
    select regexp_substr(:v_c1,'[^,]+',1,level) as c1 from dual connect by level<= regexp_count(:v_c1,',')+1;