ORACLE-WITH 子句详解

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

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

 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 ;

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 ;