oracle常用函数

SQL Cookbook 笔记（Oracle）

（1）条件选择 case when
（2）随机排序 order by dbms_random.value()
（3）空值替换 coalesce、nvl
（6）null值排序处理
    select * from emp t order by t.comm desc 【nulls last | nulls first】
（7）条件排序
    select * from emp t order by t.job, case when t.job = 'SALESMAN' then comm else sal end desc, t.empno
    
（8）集合操作
    intersect、minus
    
（9）标量子查询：放置在 select列表中的子查询

（10）in、update 均可多行

（11）update可使用内联视图

//=============使用字符串===============================

（1）cast 类型转换
    select cast(e.sal as char(9)) sal from emp e;
    
（2）lower、upper 大小写
    select lower('AbcD') from dual; --abcd
    select upper('AbcD') from dual; --ABCD
    
（4）translate 转码
    select translate('abc', 'ab', '12') from dual; -- '12c'
    
（5）replace 串替换

（6）rpad，lpad 字符截取、用特定字符填充
    select rpad('abc', 2, 'X') from dual; --ab
    select rpad('abc', 4, 'X') from dual; --abcX
    select lpad('abc', 2, 'X') from dual; --ab
    select lpad('abc', 4, 'X') from dual; --Xabc

（7）substr 截子串

（8）递归查询，汇总
    select 
    deptno,
    ltrim(sys_connect_by_path(ename, ','), ',') emps
    from (
        select 
          deptno,
          ename,
          row_number() over (partition by deptno order by ename) rn,
          count(*) over (partition by deptno) cnt
        from emp
        where deptno is not null
    )
    where level = cnt
    start with rn = 1 connect by prior deptno = deptno and prior rn = rn - 1
    --output
    DEPTNO    EMPS
    10        CLARK,KING,MILLER
    20        ADAMS,FORD,JONES,SCOTT,SMITH
    30        ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
    
（9）
--列出每个部门最高/最低工资
    --1
    select 
       deptno,
       min_sal,
       max_sal
    from (
        select 
          deptno,
          ename,
          sal,
          row_number() over (partition by deptno order by sal) rn,
          count(*) over (partition by deptno) cnt,
          min(sal) over (partition by deptno) min_sal,
          max(sal) over (partition by deptno) max_sal
        from emp
    )
    where rn = cnt
    --2
    select 
       deptno,
       min_sal,
       max_sal
    from (
        select 
          deptno,
          min(sal) over (partition by deptno) min_sal,
          max(sal) over (partition by deptno) max_sal,
          row_number() over (partition by deptno order by empno) rn
        from emp
    )
    where rn = 1