ORACLE-WITH 子句详解

概述

with 子句是在oracle 9i release 2 中引入的。with 子句又叫做子查询构造语句。可以用来给一个子查询块命名。

该查询块的检索结果会被oracle 保存在用户的临时表空间中，该命名块就像虚表或者内联视图一样。

语法

WITH 
subquery_name
AS
(the aggregation SQL statement)
SELECT
(query naming subquery_name);

With查询语句不是以select开始的，而是以“WITH”关键字开头。

可认为在真正进行查询之前预先构造了一个临时表TT，之后便可多次使用它做进一步的分析和处理

---

优点

使用SQL with 子句的优点，

• 增加了SQL的易读性，如果构造了多个子查询，结构会更清晰
• 更重要的是：“一次分析，多次使用”。当在一个sql 语句中需要多次使用到某个子查询的结果的时候，可以提升sql 语句的性能。 这也是为什么会提供性能的地方，达到了“少读”的目标。

---

栗子

取 5到10的数据，通常我们写如下sql:

 select * from (
     select level as lv 
     from dual 
     connect by level < 10 ) tt 
 where tt.lv between 5 and 10 ;

现在改为 使用With as 的SQL：

 with tt as (
      select level as lv 
      from dual 
      connect by level <10
 )
 select tt.lv  from tt where tt.lv between 5 and 10 ;

实战

查询系统菜单的后台sql

WITH TEMPVIEW AS
 (SELECT B.MENU_ID AS PARTY_ID,
         '1' AS TYPE,
         B.DIR_ID AS PARENT_ID,
         A.PRIV_NAME AS PARTY_NAME,
         A.URL,
         A.IS_HOLD,
         A.IS_AUTHORIZED
    FROM BFM_PRIV A, BFM_MENU_DIR B
   WHERE A.PRIV_ID = B.MENU_ID
     AND A.STATE = 'A'
     AND B.DIR_ID IN (SELECT DIR_ID
                        FROM BFM_DIR
                       START WITH DIR_ID = :1 --绑定变量
                      CONNECT BY PRIOR DIR_ID = PARENT_ID
                             AND STATE = 'A')
  UNION ALL
  SELECT C.DIR_ID AS PARTY_ID,
         '0' AS TYPE,
         C.PARENT_ID,
         C.DIR_NAME AS PARTY_NAME,
         NULL,
         NULL,
         NULL
    FROM BFM_DIR C
   START WITH DIR_ID = :2 --绑定变量
  CONNECT BY PRIOR DIR_ID = PARENT_ID
         AND STATE = 'A')
SELECT *  FROM TEMPVIEW
 START WITH PARTY_ID = :3 --绑定变量
CONNECT BY PRIOR PARTY_ID = PARENT_ID
 ORDER SIBLINGS BY PARTY_ID ;

ORDER SIBLINGS BY的意思就是在兄弟姐妹之间的排序，和order by所表示的含义绝对不同，针对树状sql，我觉得ORDER SIBLINGS BY更有意义，树状sql查询出来的结果本身就是按照层次（hierarchy）结构排好序的，而加上关键字SIBLINGS 其实是在hierarchy内部进行排序。

在层次查询中如需对亲兄弟的先后顺序进行限定，就必须要使用特有的“ORDER SIBLINGS BY”而非“ORDER BY”子句。切记之。