PL/SQL学习六

20. 合并查询
使用集合操作符号UNION、UNION ALL、INTERSECT、MINUS来进行查询结果的合并操作。
注意集合操作符号有以下限制：
1. 对与LOB，VARRAY和嵌套表来说，集合操作符是无效的。
2. 对与LONG列来说，UNION、INTERSECT、MINUS操作符是无效的。

UNION
UNION用于获取两个结果集的并集。使用UNION时，会自动去掉结果中的重复行，并且会以第一列的结果进行排序。

SQL> SELECT ename,sal,job FROM emp WHERE sal>2500
  2  UNION
  3  SELECT ename,sal,job FROM emp WHERE job='MANAGER';

ENAME             SAL JOB
---------- ---------- ---------
CLARK            2450 MANAGER
FORD             3000 ANALYST
JONES            2975 MANAGER
KING             5000 PRESIDENT

UNION ALL
UNION ALL也是用于获取两个结果集的并集，但是不会去掉结果中的重复行，也不会排序。

SQL> SELECT ename,sal,job FROM emp WHERE sal>2500
  2  UNION ALL
  3  SELECT ename,sal,job FROM emp WHERE job='MANAGER';

ENAME             SAL JOB
---------- ---------- ---------
FORD             3000 ANALYST
JONES            2975 MANAGER
KING             5000 PRESIDENT
JONES            2975 MANAGER
CLARK            2450 MANAGER

INTERSECT
INTERSECT用于获取两个结果集的交集。也就是只会显示同时存在于两个结果集中的数据，并且以结果第一列进行排序。

SQL> SELECT ename,sal,job FROM emp WHERE sal>2500
  2  INTERSECT
  3  SELECT ename,sal,job FROM emp WHERE job='MANAGER';

ENAME             SAL JOB
---------- ---------- ---------
JONES            2975 MANAGER

MINUS
MINUS用于获取两个结果集的差集。它只会显示在第一个结果集存在而在第二个结果集不存在的数据，并且会以第一列排序。

SQL> SELECT ename,sal,job FROM emp WHERE sal>2500
  2  MINUS
  3  SELECT ename,sal,job FROM emp WHERE job='MANAGER';

ENAME             SAL JOB
---------- ---------- ---------
FORD             3000 ANALYST
KING             5000 PRESIDENT

21. 复杂查询
21.1 层次查询
START WITH:用于指定层次查询的根。
CONNECT BY：用于指定父行和子行之间的关系。在条件表达式中必须使用PRIOR来引用父行。

--使用层次查询来显示EMP中雇员的上下级关系
--LPAD是填充函数，LEVEL返回层次
--仔细分析，START WITH指定查询的根，那么记录先找到mgr为NULL的那条，进行判断满足条件job!='CLERK'，然后就按格式输出
--再根据CONNECT BY的mgr=empno(父记录的)找到下一条记录CLARK，再输出，直到记录不满足CONNECT BY的条件就停止。
SQL> SELECT LPAD(' ',3*(LEVEL-1))||ename ename,
  2  LPAD(' ',3*(LEVEL-1))||job job FROM emp
  3  WHERE job!='CLERK'
  4  START WITH mgr IS NULL
  5  CONNECT BY mgr=PRIOR empno;

ENAME           JOB
--------------- ---------------
KING            PRESIDENT
   JONES           MANAGER
      FORD            ANALYST
   CLARK           MANAGER

21.2 CASE表达式
CASE类似于C中的SWITCH。

--CASE WHEN判断条件，THEN 满足条件输出,任何条件不满足就输出ELSE里的
--END后加别名,对熟悉C的人来说应该比较简单
SQL> SELECT ename,sal,
  2  CASE WHEN sal>3000 THEN 'Over 3000'
  3       WHEN sal>2000 THEN 'Over 2000 And Below 3000'
  4       WHEN sal>2000 THEN 'Over 2000 And Blew'
  5       ELSE 'Below 2000'
  6  END salLevel
  7  FROM emp WHERE deptno=10;

ENAME                  SAL SALLEVEL
--------------- ---------- ------------------------
CLARK                 2450 Over 2000 And Below 3000
KING                  5000 Over 3000
MILLER                 800 Below 2000

21.3 闪回查询
从oracle 9i开始，通过使用Flashback Query,可以查看到过去某时间点所提交的数据。闪回主要是依赖
UNDO实现的，并且初始化参数undo_retention限制了undo数据的保留时间。
利用闪回查看历史数据

--在AS OF子句中可以指定历史时间，也可以指定SCN
SQL> SELECT ename,sal FROM emp
  2  AS OF TIMESTAMP to_timestamp
  3  ('2012-08-23 20:00:00','YYYY-MM-DD HH24:MI:SS')
  4  WHERE ename='CLARK';

ENAME                  SAL
--------------- ----------
CLARK                 2450


--应当注意的是闪回不能看5分钟之内变化的数据,如我查看2分钟内的
SQL> SELECT ename,sal FROM emp
  2  AS OF TIMESTAMP to_timestamp
  3  ('2012-08-23 22:25:00','YYYY-MM-DD HH24:MI:SS')
  4  WHERE ename='CLARK';
SELECT ename,sal FROM emp
                      *
第 1 行出现错误:
ORA-08186: 指定的时间戳无效

利用包DBMS_FLASHBACK来获取特定的SCN

--注意SYS、SYSTEM用户不支持闪回
SQL> conn /as sysdba
已连接。
SQL> select dbms_flashback.get_system_change_number scn from dual;

       SCN
----------
   2667755

SQL> exec dbms_flashback.enable_at_system_change_number(2667100);
BEGIN dbms_flashback.enable_at_system_change_number(2667100); END;

*
第 1 行出现错误:
ORA-08185: 用户 SYS 不支持闪回
ORA-06512: 在 "SYS.DBMS_FLASHBACK", line 12
ORA-06512: 在 line 1

--将dbms_flashback包的执行权限给用户SCOTT
SQL> grant execute on dbms_flashback to scott;

授权成功。

SQL> conn scott/tigger
已连接。
SQL> select dbms_flashback.get_system_change_number scn from dual;

       SCN
----------
   2668202


SQL> insert into emp(empno,ename,job,hiredate,sal)
  2  values(135,'cry',5,sysdate,2000);

已创建 1 行。

SQL> commit;

提交完成。
SQL> select empno,ename,sal,job from emp where ename='cry';

     EMPNO ENAME             SAL JOB
---------- ---------- ---------- ---------
       135 cry              2000 5

SQL> exec dbms_flashback.enable_at_system_change_number(2668202);

PL/SQL 过程已成功完成。

SQL> select empno,ename,sal,job from emp where ename='cry';

未选定行

SQL> exec dbms_flashback.disable;

PL/SQL 过程已成功完成。

SQL> select empno,ename,sal,job from emp where ename='cry';

     EMPNO ENAME             SAL JOB
---------- ---------- ---------- ---------
       135 cry              2000 5

直接使用flashback来对表emp闪回到指定的SCN号
注意要对表开启行移动.

-- 未开启行移动功能时,不能用flashback table
SQL> flashback table emp to scn 2668202;
flashback table emp to scn 2668202
                *
第 1 行出现错误:
ORA-08189: 因为未启用行移动功能, 不能闪回表

SQL> alter table emp enable row movement;

表已更改。

SQL> select empno,ename,sal,job from emp where ename='cry';

     EMPNO ENAME             SAL JOB
---------- ---------- ---------- ---------
       135 cry              2000 5

SQL> flashback table emp to scn 2668202;

闪回完成。

SQL> select empno,ename,sal,job from emp where ename='cry';

未选定行

21.4 使用WITH...AS结构查询
多次使用相同子查询的时候，可使用WITH...AS子句给子查询指定一个名称，使得查询简化。

-- 查询部门工资总和和高于雇员工资总和三分之一的部门名称及工资总和
-- 使用子查询       
SQL> SELECT dname,SUM(sal) AS dept_total FROM emp,dept
  2  WHERE emp.deptno=dept.deptno GROUP BY dname
  3  HAVING SUM(sal)>
  4  (SELECT SUM(sal)*1/3 FROM emp,dept
  5   WHERE emp.deptno=dept.deptno);

DNAME          DEPT_TOTAL
-------------- ----------
ACCOUNTING          13125
SALES               14100

使用WITH...AS结构,相当于一个临时表使用

-- 先来个简单的
SQL> WITH test_table AS (SELECT 1,2,3,4,5 FROM DUAL)
  2  SELECT * FROM test_table;

         1          2          3          4          5
---------- ---------- ---------- ---------- ----------
         1          2          3          4          5
         
-- 使用WITH实现上面的功能         
SQL> WITH sum_sal AS (
  2  SELECT dname,SUM(sal) AS dept_total FROM emp,dept
  3  WHERE emp.deptno=dept.deptno GROUP BY dname
  4  )
  5  SELECT dname,dept_total FROM sum_sal WHERE dept_total>
  6  (SELECT SUM(dept_total)*1/3 FROM sum_sal);

DNAME          DEPT_TOTAL
-------------- ----------
ACCOUNTING          13125
SALES               14100