Oracle With Clause

        本文参考网址:http://psoug.org/reference/with.html

                            http://www.oracle-base.com/articles/misc/with-clause.php

                           http://gennick.com/with.html ------Understanding the WITH Claus

 

        参考文档下载地址:http://ishare.iask.sina.com.cn/f/21674385.html

     The WITH query_name clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying the query name. Oracle optimizes the query by treating the query name as either an inline view or as a temporary table.
(WITH语句只能位于一条语句的最顶级)
You can specify this clause in any top-level SELECT statement and in most types of subqueries. The query name is visible to the main query and to all subsequent subqueries except the subquery that defines the query name itself.

A WITH clause is really best used when the result of the WITH query is required more than one time in the body of the query such as where one averaged value needs to be compared against two or three times.

Restrictions on Subquery Factoring:

• You cannot nest this clause. That is, you cannot specify the subquery_factoring_clause within the subquery of another subquery_factoring_clause. However, a query_name defined in one subquery_factoring_clause can be used in the subquery of any subsequent subquery_factoring_clause.(WITH语句不允许嵌套，之前定义的WITH语句可以在它后面的任何一个WITH语句中使用)
  
• In a query with set operators, the set operator subquery cannot contain the subquery_factoring_clause, but the FROM subquery can contain the subquery_factoring_clause 
  

With语句的语法(AS后面的括号是不可以空缺的)

WITH <alias_name> AS (subquery_sql_statement)
SELECT <column_name_list> FROM <alias>;

简单的with语句:

WITH A AS
 (SELECT * FROM DUAL)
SELECT * FROM A

注意，定义了WITH语句必须在后边的查询中使用，否则提示错误信息:

WITH A AS
 (SELECT * FROM DUAL)
SELECT * FROM dual

(错误的原因是因为没有使用定义的WITH语句进行查询)

 

两个with语句的语法:

WITH <alias_one> AS 
  (subquery_sql_statement),
     <alias_two> AS
  (sql_statement_from_alias_one)
SELECT <column_name_list>
FROM <alias_one>, <alias_two>
WHERE <join_condition>;

测试例子:

WITH A AS
 (SELECT * FROM DUAL),
B AS
 (SELECT * FROM DUAL)
SELECT * FROM B, A

当在FROM关键子后面没有全部使用定义的WITH语句，他就会提示同上的错误信息:

(不可引用在with子句中定于的查询)

 

在视图中使用WITH语句进行连接:

CREATE OR REPLACE VIEW WITH_V AS
  WITH DEPT_V AS (SELECT * FROM DEPT),
   EMP_V AS (SELECT * FROM EMP)
   SELECT D.DNAME,D.LOC,E.* FROM EMP_V E
   LEFT JOIN DEPT_V D
   ON D.DEPTNO = E.DEPTNO

     使用WITH AS 语句可以为一个子查询语句块定义一个名称，使用这个子查询名称可以  在查询语句的很多地方引用这个子查询。Oracle 数据库像对待内联视图或临时表一样对待 被引用的子查询名称，从而起到一定的优化作用。with子句是9i新增语法。你可以在任何一个顶层的SELECT 语句以及几乎所有类型的子查询语句前，使用子查询定义子句。被定义的子查询名称可以在主查询语句以及所有的子查询语句中引用，但未定义前不能引用。with子句中不能嵌套定义<也就是with子句中不能有with子句>，但子查询中出现的“子查询定义”语句可以引用已定义的子查询名称。<可以引用前面已经定义的with子句>

WITH子句相关总结:

1、在同级select前有多个查询定义的时候，第1个用with，后面的不用with，并且用逗号隔开。

 

2、最后一个with 子句与下面的查询之间不能有逗号，只通过右括号分割,with 子句的查询必须用括号括起来

3、如果定义了with子句，而在查询中不使用，那么会报ora-32035 错误：未引用在with子句中定义的查询名。（至少一个with查询的name未被引用，解决方法是移除未被引用的with查询），注意：只要后面有引用的就可以，不一定非要在主查询中引用，比如后面的with查询也引用了，也是可以的。

 

4、前面的with子句定义的查询在后面的with子句中可以使用。但是一个with子句内部不能嵌套with子句。

 

5、with查询的结果列有别名，引用的时候必须使用别名或*。

 

WITH语句的优点:

(1). SQL可读性增强。比如对于特定with子查询取个有意义的名字等。

(2)、with子查询只执行一次，将结果存储在用户临时表空间中，可以引用多次，增强性能。

     举例:在进行导入EXCEL的过程中，有时候，需要将数据存储在临时表中，当下一次在进行导入的时候，进行清除临时表的数据，但是这时候，有时候发生并发问题的话，两个用户可能会分别操作对方的数据，所以，可能造成混乱，但是可以使用WITH函数和UNION语句拼接一个SQL语句，存储在SESSION中，当需要导出错误信息的时候，可以使用该语句构造数据。

---------------------------WITH语句的使用例子:

1、查询出部门的总薪水大于所有部门平均总薪水的部门。部门表s_dept，员工表s_emp。分析：做这个查询，首先必须计算出所有部门的总薪水，然后计算出总薪水的平均薪水，再筛选出部门的总薪水大于所有部门总薪水平均薪水的部门。那么第1 步with 查询查出所有部门的总薪水，第2 步用with 从第1 步获得的结果表中查询出平均薪水，最后利用这两次 的with 查询比较总薪水大于平均薪水的结果，如下：

WITH DEPT_COSTS AS --查询出部门的总工资
 (SELECT D.DNAME, SUM(E.SAL) DEPT_TOTAL
    FROM DEPT D, EMP E
   WHERE E.DEPTNO = D.DEPTNO
   GROUP BY D.DNAME),
AVE_COST AS   --查询出部门的平均工资，在后一个WITH语句中可以引用前一个定义的WITH语句
 (SELECT SUM(DEPT_TOTAL) / COUNT(*) AVG_SUM FROM DEPT_COSTS)
SELECT *
  FROM DEPT_COSTS DC
 WHERE DC.DEPT_TOTAL > (SELECT AC.AVG_SUM FROM AVE_COST AC)--进行比较

2、可以使用前面的数据，在后面的with语句中直接引用。需求，展示根据查询结果查询出的数据，并把根据查询出的结果进行统计，如最大工资，最小工资，平均工资，

进行级联，由于查询的统计数据的条数为1条，所以不会发生笛卡儿积的错误，

WITH TEMP_DATA AS --查询基础数据
 (SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM EMP),
TEM_SUM AS --查询统计数据
 (SELECT MAX(SAL), MIN(SAL), SUM(SAL) FROM TEMP_DATA)
SELECT *
  FROM TEM_SUM, TEMP_DATA --进行级联，由于查询的统计数据的条数为1条，所以不会发生笛卡儿积的错误

 

 

 

3、 with子查询不可嵌套定义，但是后面的with定义可以引用前面的结果集。

WITH SELECT_TRADE AS
 (SELECT TRADE_ID FROM PRODUCT WHERE ID = 1),
--后面的with子查询可以引用前面的结果
SELECT_TRADEMARK AS
 (SELECT NAME FROM TRADEMARK WHERE ID = (SELECT TRADE_ID FROM SELECT_TRADE))
SELECT * FROM SELECT_TRADEMARK;

 下面的语句错误:因为不允许嵌套定义with语句

--这条语句错误
WITH SELECT_TRADE AS
--with中有嵌套with,不允许
 (WITH TEMP AS
     (SELECT TRADE_ID FROM PRODUCT WHERE ID = 1)
    SELECT TRADE_ID FROM TEMP),
SELECT_TRADEMARK AS
 (SELECT NAME FROM TRADEMARK WHERE ID = (SELECT TRADE_ID FROM SELECT_TRADE))
SELECT * FROM SELECT_TRADEMARK;

 

4、一个复杂的WITH语句例子:在子查询中使用WITH，

SELECT A, B
  FROM (
       --第1个定义t_with
       WITH T_WITH AS (SELECT '1' A FROM DUAL)
       --子查询使用t_with
         SELECT X.A,
                (
                --内部定义了个t_with_z,并且使用t_with
                WITH T_WITH_Z AS (SELECT '1' A FROM T_WITH)
                  SELECT S_1.A
                    FROM T_WITH_Z S_1, T_WITH S_2) B
                    FROM T_WITH X,
                         --子查询中使用t_with,并且自己也定义了t_with_y
                         (WITH T_WITH_Y AS (SELECT '1' A FROM T_WITH)
                           SELECT F_1.A
                             FROM T_WITH_Y F_1, T_WITH F_2) Y
                            WHERE X.A = Y.A
                                 --定义t_with_x
                              AND X.A = (WITH T_WITH_X AS (SELECT '1' A
                                                             FROM T_WITH)
                             SELECT W_1.A
                               FROM T_WITH_X W_1, T_WITH W_2))

5、在列的子查询中引用WITH函数:

SELECT (WITH A AS (SELECT '1' FROM DUAL)
         SELECT * FROM A) FROM DUAL

6、引用WITH查询结果:

SELECT *
  FROM (WITH A AS (SELECT '1' FROM DUAL)
         SELECT * FROM A)

 7、一个查询，如果查询的结果行不满足是5 的倍数，则补空行，直到是查询出的行数是5 的倍数。

WITH TEMP_DATA AS
 (SELECT (5 - MOD(COUNT(*), 5)) COUNTS FROM DUAL CONNECT BY LEVEL < 3)
SELECT '1', '1'
  FROM DUAL
UNION ALL
SELECT NULL, NULL FROM DUAL CONNECT BY LEVEL < (SELECT * FROM TEMP_DATA)

也可以使用ALL_OBJECTS

WITH TEMP_DATA AS
 (SELECT (5 - MOD(COUNT(*), 5)) COUNTS FROM DUAL CONNECT BY LEVEL < 3)
SELECT '1', '1'
  FROM DUAL
UNION ALL
SELECT NULL, NULL FROM ALL_OBJECTS WHERE ROWNUM < (SELECT * FROM TEMP_DATA)

 查询结果：