Oracl／SQL

1.分组合并的问题

SELECT T .DEPTNO, listagg (T .ENAME, ',') WITHIN GROUP (ORDER BY T .ENAME) names FROM SCOTT.EMP T WHERE T .DEPTNO = '20' GROUP BY T .DEPTNO

2.sql函数，取两个字符串的差值

create or replace function GetMinus(
   all_str varchar2, --被减字符串：以，分隔字符串
   minus_str varchar2 -- 以，分隔字符串
)  return  varchar2
 is
   rst_str  varchar2(256);
begin
select
case  
  when listagg(id, ',') WITHIN GROUP(ORDER BY id)  ='[]'
    then ''
   else
    listagg(id, ',') WITHIN GROUP(ORDER BY id) 
   end str
  into rst_str
  from (select regexp_substr(id, '[^,]+', 1, rownum) id
          from (select all_str id from dual)
        connect by rownum <= length(regexp_replace(id, '[^,]+')) + 1
        minus --取差值
        select regexp_substr(id, '[^,]+', 1, rownum) id
          from (select minus_str id from dual)
        connect by rownum <= length(regexp_replace(id, '[^,]+')) + 1);

return rst_str ;

end;
 一、实际的实践问题

（1）字符的特殊处理

select  listagg(xx) with group(order by xx) from ( select  regexp_substr('1,2,3', '[^,]+', 1, rownum)  xx from dual  connect by rownum <= length(regexp_replace('1,2,3', '[^,]+')) +  1）

（2）查询循环的条件

connect by +条件

（3）orcale一行转成多行regexp_substr()

（4）字符串的替换

select regexp_replace('+86 13856427896','^[(]\+(\+[0-1]{2}+)\+[\)] [0-9]{11}$','(\1)\3-\4-\5') as new_str from dual; 
select regexp_replace('0123456789','01234','0abc') from dual;--regexp_replace（原字符，被替换字符，替换字符）

(5)字符串截取: SUBSTR(string,start_position,[length])    求子字符串，返回字符串

(6)字符串位置;select  instr('1235x7','x')  from dual;--=5

(7) 直接替换：select replace('abcddgg','dd','aa') from dual

4.语法格式：row_number() over(partition by 分组列 order by 排序列 desc)

5.not exists(子查询) 实际是一种子查询的用法，效率比not in()快，整体就是一个true/false的条件select t.*, t.rowid from Tab_bak t where  not  exists(select * from tab_back1 t1 where t1.alg_name=t.alg_name and t.alg_name='1')

6.connect by 实际上相当于一个循环体使用，就是用来循环的

7.整数取法 ceil(22.3) =24 ; round(22.3) =22 

8.补零函数select lpad(23,4,'0')  from dual;--0024 

9.替换函数 select *,replace(address,'区','呕')  from dual；address=’深圳区‘

10.order by 排序 order by A desc , B asc 首先先根据A的条件排序，再根据B条件进行局部的排序

11.多行的某个字段合成一列使用，listagg(列名,' 分割符号') within group(order by 列值被拼接的顺序) ，比如按部门，拿出所在部门的员工

12.忘记oracle的sys用户密码怎么修改以及Oracle 11g 默认用户名和密码 - Aloys_Code - 博客园