WITH AS用法

一．WITH AS的含义
         WITH AS短语，也叫做子查询部分（subquery factoring），可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNION ALL的不同部分，作为提供数据的部分。

        特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用WITH AS短语，则只要执行一遍即可。如果WITH AS短语所定义的表名被调用两次以上，则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里，如果只是被调用一次，则不会。而提示materialize则是强制将WITH AS短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。

        使用WITH AS 语句可以为一个子查询语句块定义一个名称，使用这个子查询名称可以在查询语句的很多地方引用这个子查询。Oracle 数据库像对待内联视图或临时表一样对待被引用的子查询名称，从而起到一定的优化作用。with子句是9i新增语法。

　　你可以在任何一个顶层的SELECT 语句以及几乎所有类型的子查询语句前，使用子查询定义子句。被定义的子查询名称可以在主查询语句以及所有的子查询语句中引用，但未定义前不能引用。

　　with子句中不能嵌套定义<也就是with子句中不能有with子句>，但子查询中出现的子查询定义语句可以引用已定义的子查询名称。<可以引用前面已经定义的with子句>

二．使用方法

语法：

[ WITH <common_table_expression> [ ,n ] ] 
<common_table_expression>::= 
        expression_name [ ( column_name [ ,n ] ) ] 
    AS 
        ( CTE_query_definition )

•  在使用CTE时应注意如下几点：
  •  CTE后面必须直接跟使用CTE的SQL语句（如select、insert、update等），否则，CTE将失效。
  •  CTE后面也可以跟其他的CTE，但只能使用一个with，多个CTE中间用逗号（,）分隔。
  •  如果CTE的表达式名称与某个数据表或视图重名，则紧跟在该CTE后面的SQL语句使用的仍然是CTE，当然，后面的SQL语句使用的就是数据表或视图了。
  •  CTE 可以引用自身，也可以引用在同一 WITH 子句中预先定义的 CTE。不允许前向引用。

  •  不能在 CTE_query_definition 中使用以下子句：

    （1）COMPUTE 或 COMPUTE BY

    （2）ORDER BY（除非指定了 TOP 子句）

    （3）INTO

    （4）带有查询提示的 OPTION 子句

    （5）FOR XML

    （6）FOR BROWSE

  • 如果将 CTE 用在属于批处理的一部分的语句中，那么在它之前的语句必须以分号结尾。

  •  CTE除了可以简化嵌套SQL语句外，还可以进行递归调用。

三．WITH子句的优点

• SQL可读性增强。比如对于特定with子查询取个有意义的名字等。
• with子查询只执行一次，将结果存储在用户临时表空间中，可以引用多次，增强性能。