源码-PL/SQL从入门到精通-第九章-SQL内置函数-Part 2

有些函数如果没有实际的使用需求，光看简单的示例也很难理解。

--NVL函数（如exp1为空，则返回exp2的值，否则返回exp1?）
SELECT last_name 英文名,
       salary 薪资,
       NVL(commission_pct, 0) 提成比率,
       (salary * 12) + (salary * 12 * NVL(commission_pct, 0)) 年度薪水
FROM   employees

--NVL2函数（如exp1为空，则返回exp3的值，否则，返回exp2的值）
        SELECT last_name 英文名,
               salary 薪资,
               commission_pct 提成,
               NVL2(commission_pct, salary * 12 * commission_pct, salary * 12) 收入
        FROM   employees
        WHERE  department_id IN (50, 80);

--Nullif函数（如exp1和exp2等值，则返回null,否则，返回exp1的值）
SELECT NULLIF(commission_pct,0)
FROM   employees;


SELECT empno, ename, NVL(NULLIF(job, '销售人员'), '业务人员') job
FROM   emp
WHERE  deptno = 20;

SELECT *
FROM   emp;

--Coalesce函数（返回第一个非空字段）
SELECT empno, ename, COALESCE(mgr, deptno, empno) 员工
FROM   emp
WHERE  empno > 7700 AND ROWNUM < 10;

--Decode函数（可替代Case when表达式）
SELECT last_name,
       job_id,
       salary,
       DECODE(job_id,
              'IT_PROG',
              1.10 * salary,
              'ST_CLERK',
              1.15 * salary,
              'SA_REP',
              1.20 * salary,
              salary) REVISED_SALARY
FROM   employees;

SELECT *
FROM   employees;

SELECT *
FROM   emp;


SELECT empno,
       ename,
       DECODE(job,
              '职员',
              1.15 * sal,
              '销售人员',
              1.20 * sal,
              'SR.CONT',
              2.0 * sal,
              '分析人员',
              1.12 * sal,
              sal) 调薪表
FROM   emp
WHERE  ROWNUM < 12;

--9.2 Oracle分析函数
--分析函数提供了简洁的语法、较高的性能
--sum over分析函数
SELECT ename,
       deptno,
       sal,
       SUM(sal) OVER(ORDER BY deptno, ename) 运行总计,
       SUM(sal) OVER(PARTITION BY deptno ORDER BY ename) 分部门运行总计,
       ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY ename) 序列
FROM   emp
WHERE  deptno IN (10, 20)
ORDER  BY deptno, ename;

--统计不同职位的累计工资（似乎挺方便的）
SELECT empno,ename,
       deptno,job,
       sal,
       SUM(sal) OVER(partition by job order by job,sal) 职位累计工资,
       ROW_NUMBER() OVER(ORDER BY empno) 序列
FROM  emp

SELECT ename, deptno, sal, SUM(sal) OVER(ORDER BY ename) 运行总计
FROM   emp
WHERE  deptno IN (10, 20)
ORDER  BY deptno, ename;


SELECT o.deptno,
       o.job,
       SUM(o.sal) 部门职级汇总,
       SUM(SUM(o.sal)) OVER(PARTITION BY o.deptno ORDER BY o.job) 部门薪资运行总计
FROM   emp o
WHERE  deptno IN (10, 20, 30) AND job IS NOT NULL
GROUP  BY o.deptno, o.job
ORDER  BY o.deptno;

--分析函数结构（ranking,windowing, reporting, lag/lead, var_pop, etc.）
SELECT o.deptno,
       o.job,
       SUM(o.sal) 部门职级汇,
       RANK() OVER(ORDER BY SUM(o.sal) DESC) 薪资等级,
       DENSE_RANK() OVER(ORDER BY SUM(o.sal) DESC) DENSE_RANK排名,
       ROW_NUMBER() OVER(PARTITION BY o.deptno ORDER BY SUM(o.sal) DESC) 分组行号,
       SUM(SUM(o.sal)) OVER(PARTITION BY o.deptno ORDER BY o.job) 部门薪资运行总计
FROM   emp o
WHERE  deptno IN (10, 20, 30) AND job IS NOT NULL
GROUP  BY o.deptno,
          o.job
          
--分区子句          
            SELECT deptno,
                   empno,
                   ename,
                   job,
                   ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY empno) 分组行号
            FROM   emp
            WHERE  deptno IN (10, 20, 30);