PL/SQL学习六

来源：互联网发布：怎么安装java 编辑：程序博客网时间：2024/06/06 16:31

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 MANAGERFORD             3000 ANALYSTJONES            2975 MANAGERKING             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 ANALYSTJONES            2975 MANAGERKING             5000 PRESIDENTJONES            2975 MANAGERCLARK            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 ANALYSTKING             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 3000KING                  5000 Over 3000MILLER                 800 Below 2000

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

--在AS OF子句中可以指定历史时间，也可以指定SCNSQL> 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----------   2667755SQL> 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 12ORA-06512: 在 line 1--将dbms_flashback包的执行权限给用户SCOTTSQL> grant execute on dbms_flashback to scott;授权成功。SQL> conn scott/tigger已连接。SQL> select dbms_flashback.get_system_change_number scn from dual;       SCN----------   2668202SQL> 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 5SQL> 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 tableSQL> 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 5SQL> 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          13125SALES               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          13125SALES               14100