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
分享到：
相关推荐
Oracle PL/SQL学习官方教材，英文版,覆盖了PL/SQL的所有知识，适合入门者或作为参考资料.
PL/SQL从入门到精通学习帮助手册,PL/SQL简介,PL/SQL操作,存储过程,触发器等
PL/SQL是Oracle对标准数据库语言的扩展，Oracle公司已经将PL/SQL整合到Oracle 服务器和...通过本教程的学习，您将初步掌握PL/SQL语言，能够利用PL/SQL语言完成建表、查询、添加、删除数据以及事务处理语句的基本用法。
pl/sql数据库学习笔记，包含一些基础知识，和案例代码
oracle10g pl/sql完备教程，供初学者学习与开发者参考
oracle PL/SQL pdf学习资料oracle PL/SQL pdf学习资料oracle PL/SQL pdf学习资料oracle PL/SQL pdf学习资料oracle PL/SQL pdf学习资料
这是我在学习PL/SQL中总结的一些东西,希望对他家有帮助.
pl/sql资料,很全面的,共十一个pdf文件,感兴趣的可以下下看.
PL/SQL学习总结是本人学习ORACLE报表开发及FROM开发的总结,包括存储程序及,包等
本书是专门为oracle开发人员而提供的编程指南 通过学习本书 读者不仅可以掌握编写sql语句和pl／sql块的基础知识 而且还可以掌握sql高级特征 正则表达式 flashback查询 merge语句 sql：1999连接 和pl／sql高级特征 ...
PL／SQL本身涉及的知识点浩瀚、庞杂，初学者根本无法依靠自身能力理清头绪，学习成本极高．本书对知识点进行了体系化的梳理，化繁杂为有序，突出重点，直指核心，循序渐进，尽可能为学习者提供“捷径”，仅仅只是这...
oracle 中 pl/sql 只是学习方法，喜欢 oracle 的朋友都下了看看吧
PL/SQL学习教程，包括相应的代码以及相关的学习笔记，适合初学者快速入门。
一个很好的学习 pl/sql的入门文章 作者总结的非常好 非常适合入门学者
本书包含了真实世界的例子，涵盖了PL / SQL的所有高级功能。反过来，每个主要的认证主题都在一个单独的章节中介绍，使理解概念更容易。在每章的最后，你会发现很多练习题，以加强和测试你的学习。 如果您是PL / SQL...
PL/SQL x学习常见用法，学习从最基本开始！
最容易上手的PL/SQL学习教程-有经过测试的丰富案例，可以直接复制代码至SQL*PLUS中运行，知识点精确明了，是快速学习PL/SQL的绝好资料。
pl/sql编写的院校招生系统（通过包、存储过程、触发器、视图等完成的），可供学习oracle数据库pl/sql编程使用
pl/sql中的一些基本概念，讲解了pl/sql语句的编写方法。其中包含了pl/sql的简单语句、循环语句、条件语句等的相关知识，重点介绍了存储过程、函数、游标、触发器等的编写和运用。
通过学习本书，读者不仅可以掌握PL/SQL的基础编程知识(嵌入SQL语句和编写子程序)，而且还可以掌握PL/SQL的所有高级开发特征(使用记录类型、集合类型、对象类型和大对象类型)。另外，本书还为应用开发人员提供了大量...