Oracle--其他复杂查询

                                       其他复杂查询

     当开发数据库应用程序时，除了使用基本查询、数据分组、连接查询、子查询之外，还需要使用集合操作符、层次查询、Flashback查询等。
一、使用集合操作符
       集合操作符专门用于合并多条SELECT语句的结果，包括UNION、UNION ALL、INTERSECT和MINUS四个操作符。
这些集合操作符具有相同的优先级，当同时使用多个操作符时，会按照先后顺序引用这些集合操作符。
当使用集合操作符时，必须确保不同查询的列个数和数据类型匹配。
另外，使用集合操作符具有以下限制：
    集合操作符不适用于LOB、VARRAY和嵌套表列。
    UNION、INTERSECT、MINUS操作符不适用于LONG列。
    如果选择列表包含有表达式或者函数，那么必须为表达式或者函数定义列别名。
在介绍如何使用这些集合操作符之前，首先建立MANAGER表和WORKER表。
CREATE TABLE manager(id,name,job,sal) AS
SELECT empno,ename,job,sal FROM emp
where empno in(select distinct nvl(mgr,0) FROM emp);
CREATE TABLE worker(id,name,job,sal) AS
SELECT empno,ename,job,sal FROM emp
where empno not in (select distinct nvl(mgr,0) FROM emp);
1.UNION
UNION操作符用于取得俩个结果的并集。当使用该操作符时，会自动去掉结果集中的重复行，并且会以第一列的结果升序排列。
SELECT id,name,job FROM manager UNION
SELECT empno,ename,job FROM emp;
2.UNION ALL
UNION ALL操作符用于取得俩个结果集的并集。但与UNION操作符不同，该操作符不会取消重复值，并且不会对结果集数据进行排序。
SELECT id,name,job FROM worker UNION ALL
SELECT empno,ename,job FROM emp;
3.INTERSECT
INTERSECT操作符用于取得俩个结果集的交集。当使用该操作符时，只会显示同时存在俩个结果集中的数据，并且会以第一列的结果进行升序排序。
SELECT id,name,job FROM worker INTERSECT
SELECT empno,ename,job FROM emp;
4.MINUS
MINUS操作符用于取得俩个结果集的差集。当使用该操作符时，只会显示在第一个结果集中存在，
在第二个结果集中不存在的数据，并且会以第一列的结果进行升序排列。
SELECT empno,ename,job FROM emp MINUS
SELECT id,name,job FROM worker;
5.控制结果顺序
当使用集合操作符UNION、INTERSECT和MINUS时，默认情况下会自动基于第一列进行升序排序；而当使用集合操作符UNION ALL时，不会进行排序。
为了控制结果的排序顺序，可以使用order by子句。注意：该子句必须放在最后一条SELECT语句之后。
当指定order by子句时，列名相同可以直接使用列名排序，列名不同必须使用列位置排序。
SELECT id,name,job FROM worker INTERSECT
SELECT empno,ename,job FROM emp order by 2;

二、层次查询
层次查询用于检索具有层次结构的表行数据。在Oracle数据库中，数据库数据是以关系结构方式存储的，但是层次数据可以存放在单个表中。
当表具有层次结构的数据时，通过使用层次查询可以更直观地显示数据结果，并显示其数据之间的层次关系。层次查询的语法如下：
SELECT [LEVEL],column,expr... FROM table
[WHERE condition]
START WITH condition
CONNECT BY [PRIOR column1 = column2 | column = PRIOR column];
如上所示，伪列LEVEL用于返回层次结构的层次(1:根行，2：第二级行，3：第三级行，......)，START WITH子句用于指定层次查询的根行，
CONNECT BY 子句用于指定父行和子行之间的关系，当定义父行和子行的关系时，必须使用PRIOR关键字，并且column1和column2对应于父键或者子健列。
当使用层次查询显示层次结构的表行数据时，可以采用从顶向下或者从底向上俩种方式显示数据。
1.示例一，使用PRIOR column1=column2从顶向下显示层次数据
SELECT LPAD(' ',3*(LEVEL-1))||ename ename,
LPAD(' ',3*(LEVEL-1))||job job FROM emp
start with ename='JONES' connect by prior empno=MGR;
2.示例二，使用column1=PRIOR column2从顶向下显示层次数据
SELECT LPAD(' ',3*(LEVEL-1))||ename ename,
LPAD(' ',3*(LEVEL-1))||job job FROM emp
start with ename='BLAKE' connect by mgr=PRIOR empno;
3.示例三，使用PRIOR column1=column2从底向上显示层次数据
SELECT LPAD(' ',3*(LEVEL-1))||ename ename,
LPAD(' ',3*(LEVEL-1))||job job FROM emp
start with ename='SMITH' connect by PRIOR mgr=empno;
4.示例四，column1=PRIOR column2从底向上显示层次数据
SELECT LPAD(' ',3*(LEVEL-1))||ename ename,
LPAD(' ',3*(level-1))||job job FROM emp
start with ename='ALLEN' connect by empno=PRIOR mgr;
三、使用条件表达式
1.使用DECODE函数
语法如下：
DECODE(col|expr,search1,result1[,search2,result2,...,][,default])
如上所示,col用于指定列名，expr用于指定表达式，如果列或者表达式的结果匹配于search1,则返回result1，以此类推；
如果列或者表达式不匹配与任何search，则返回default。
SELECT ename,deptno,sal,DECODE(deptno,10,sal*1.2,20,sal*1.1,sal) "Actual Salary"
FROM emp;
2.使用CASE表达式
使用CASE表达式处理多重条件分支有两种方法，第一种方法是使用单一选择符进行等值比较；第二种方法是使用多种条件进行非等值比较。
①在CASE表达式中使用单一选择符进行等值比较
当使用CASE表达式执行多重条件分支时，如果条件选择符完全相同，并且条件表达式为相等条件选择，那么可以选择使用单一条件选择符进行等值比较。
语法如下：
CASE selector
     WHEN expr1 THEN return_expr1
     WHEN expr2 THEN return_expr2
     ...
     WHEN exprN THEN return_exprN
     [ELSE return_expr]
END
  如下所示，selector用于指定条件选择符，expr用于指定条件表达式，return_expr用于指定要返回的值表达式。
SELECT ename,deptno,sal,CASE deptno
    WHEN 10 THEN sal*1.2 WHEN 20 THEN sal*1.1
    ELSE sal END "Actual Salary"
FROM emp;
②在CASE表达式中使用多种条件比较
当使用单一条件选择符进行等值比较时，可以使用CASE selector语法实现。如果包含有多种条件进行不等比较，那么必须在WHEN子句中指定比较条件。
语法如下：
CASE
    WHEN search_condition1 THEN return_expr1
    WHEN search_condition2 THEN return_expr2
    ...
    WHEN search_conditionN THEN return_exprN
    [ELSE return_expr]
END
如上所示，search_condition用于指定不同比较条件，return_expr用于指定返回值的表达式。
SELECT ename,sal,CASE WHEN sal<2000 THEN sal*1.2
    WHEN sal<3000 THEN sal*1.1 ELSE sal END "Actual Salary"
FROM emp;
四、Flashback查询
当执行查询操作时，默认情况下会显示当前数据。通过利用Flashback特征，可以显示过去特定时间点或者特定SCN的表数据。
注意：如果使用Flashback查询，那么要求数据库必须采用自动UNDO管理方式。
在介绍如何使用Flashback之前，首先执行如下语句：
conn system/123456
SELECT systimestamp FROM dual;
SELECT sal FROM scott.emp WHERE empno=7788;
commit;
SELECT current_scn FROM v$database;

SQL> select current_scn from v$database;  （v$database--系统视图）

CURRENT_SCN
-----------
    2048219
update scott.emp set sal=2500 where empno=7788;
commit;    

1.显示当前数据
当执行查询操作时，默认情况下会显示当前数据。
SELECT sal FROM scott.emp WHERE empno=7788;

SQL> SELECT sal FROM scott.emp WHERE empno=7788;

       SAL
----------
      2500
2.使用AS OF TIMESTAMP子句查看过去时间点的数据
当执行Flashback查询时，通过使用AS OF TIMESTAMP子句，可以查看过去时间点的数据。
SELECT sal FROM scott.emp AS OF TIMESTAMP TO_TIMESTAMP('07-5月 -19 10.26.07') WHERE empno=7788;

SQL> SELECT sal FROM scott.emp AS OF TIMESTAMP TO_TIMESTAMP('07-5月 -19 10.26.07') WHERE empno=7788;

       SAL
----------
      1600
    
3.使用AS OF SCN子句查看过去SCN点的数据
当执行Flashback查询时，通过使用AS OF SCN子句，可以查看过去SCN点的数据。
SELECT SAL FROM SCOTT.EMP AS OF SCN 2048219 WHERE EMPNO=7788;

SQL> SELECT SAL FROM SCOTT.EMP AS OF SCN 2048219 WHERE EMPNO=7788;

       SAL
----------
      2000