深入理解和使用Oracle中with as語句以及與增刪改查的結合使用____Oracle

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 文法 單個文法： with  tempName  as  ( select  ....) select  ...
多個文法： with  tempName1  as  ( select  ....)， tempName2  as  ( select  ....)， tempName3  as  ( select  ....) ... select  ...  
With查詢語句不是以select開始的，而是以“WITH”關鍵字開頭 可認為在真正進行查詢之前預先構造了一個暫存資料表TT，之後便可多次使用它做進一步的分析和處理
二、WITH AS執行個體 例：現在要從1-19中得到11-14。一般的sql如下： select   *   from (              --類比生一個20行的資料               SELECT   LEVEL   AS  lv                 FROM  DUAL          CONNECT  BY   LEVEL   <   20 ) tt   WHERE  tt.lv  >   10   AND  tt.lv  <   15   使用With as 的SQL為： with TT as ( --類比生一個20行的資料 SELECT LEVEL AS lv FROM DUAL CONNECT BY LEVEL < 20 ) select lv from TT WHERE lv > 10 AND lv < 15
多個暫存資料表執行個體： WITH T3 AS ( SELECT T1.ID, T1.CODE1, T2.DESCRIPTION FROM TB_DATA T1, TB_CODE T2 WHERE T1.CODE1 = T2.CODE ), T4 AS ( SELECT T1.ID, T1.CODE2, T2.DESCRIPTION FROM TB_DATA T1, TB_CODE T2 WHERE T1.CODE2 = T2.CODE ) SELECT T3.ID, T3.DESCRIPTION, T4.DESCRIPTION FROM T3, T4 WHERE T3.ID = T4.ID ORDER BY ID;  
三、WITH Clause方法的優點      增加了SQL的易讀性，如果構造了多個子查詢，結構會更清晰；更重要的是：“一次分析，多次使用”，這也是為什麼會提供效能的地方，達到了“少讀”的目標。      第一種使用子查詢的方法表被掃描了兩次，而使用WITH Clause方法，表僅被掃描一次。這樣可以大大的提高資料分析和查詢的效率。      另外，觀察WITH Clause方法執行計畫，其中“SYS_TEMP_XXXX”便是在運行過程中構造的中間統計結果暫存資料表。
四、WITH AS 與增刪改查結合用法 注意：1. with必須緊跟引用的select語句 2.with建立的暫存資料表必須被引用，否則報錯 4.1與select查詢語句結合使用 查詢同一個單據編號對應的借款單和核銷單中，借款金額不相等的單據

with verificationInfo as (select ment.fnumber,         sum(t.famount) vLoanSum,         ment.fnumber "單據編號",         sum(t.famount) "核銷單中借款總額"    from shenzhenjm.t_finance_expenseremburseitem t    left join shenzhenjm.t_finance_expenserembursement ment      on ment.fid = t.fkrembursementid   where 1 = 1   group by ment.fnumber),loanInfo as (select ment.fnumber,         sum(t.famount) loanSum,         ment.fnumber "單據編號",         sum(t.famount) "借款單中借款總額"    from shenzhenjm.t_finance_expenseremburseitem2 t    left join shenzhenjm.t_finance_expenserembursement ment      on ment.fid = t.fkrembursementid   where 1 = 1   group by ment.fnumber)select *  from verificationInfo v, loanInfo l where l.fnumber = v.fnumber   and l.loanSum != v.vLoanSum;

4.2與insert結合使用 如下的with as語句，不能放在insert前，而是放在緊接著要調用的地方前 要求將同一個單據編號對應的借款單和核銷單中，借款金額不相等的單據，對應的借款單刪除，並將對應的核銷單插入到借款單表中 （借款單和核銷單表結構完全一樣）

insert into T_finance_ExpenseRemburseItem2  (FID,   FKREMBURSEMENTID,   FAMOUNT,   FKCREATEBYID,   FCREATETIME,   FKCUID,   FKCOSTTYPEID,   FCOSTTYPENAME)  with verificationInfo as   (select ment.fnumber,           sum(t.famount) vLoanSum,           ment.fnumber "單據編號",           sum(t.famount) "核銷單中借款總額"      from shenzhenjm.t_finance_expenseremburseitem t      left join shenzhenjm.t_finance_expenserembursement ment        on ment.fid = t.fkrembursementid     where 1 = 1     group by ment.fnumber),    loanInfo as   (select ment.fnumber,           sum(t.famount) loanSum,           ment.fnumber "單據編號",           sum(t.famount) "借款單中借款總額"      from shenzhenjm.t_finance_expenseremburseitem2 t      left join shenzhenjm.t_finance_expenserembursement ment        on ment.fid = t.fkrembursementid     where 1 = 1     group by ment.fnumber)    select sys_guid(),         ment.fid,         t.famount,         ment.fkcreatebyid,         ment.fcreatetime,         ment.fkcuid,         t.fkcosttypeid,         t.fcosttypename    from T_finance_ExpenseRemburseItem t    left join t_finance_expenserembursement ment      on ment.fid = t.fkrembursementid   where 1 = 1     and exists (select *            from verificationInfo v, loanInfo l           where l.fnumber = v.fnumber             and l.loanSum != v.vLoanSum             and v.fnumber = ment.fnumber);

4.3 與delete刪除結合使用

delete from t_finance_expenseremburseitem2 item2 where exists(with temp as (select t.fnumber,                             sum(item1.famount) vloanSum,                             sum(item1.frealityamount) vSum,                             sum(item2.famount) loanSum                        from t_finance_expenserembursement t                        left join t_finance_expenseremburseitem item1                          on item1.fkrembursementid = t.fid                        left join t_finance_expenseremburseitem2 item2                          on item2.fkrembursementid = t.fid                       where 1 = 1                         and t.frembursementtype = 'LOAN_REPORT'                         and to_char(t.fcreatetime, 'yyyy') > '2017'                       group by t.fnumber                       order by t.fnumber asc)    select 1     from temp t     left join t_finance_expenserembursement ment       on t.fnumber = ment.fnumber     left join t_finance_expenseremburseitem2 item       on item.fkrembursementid = ment.fid    where t.vloanSum != t.loanSum      and item.fid = item2.fid);

4.4與update結合使用

update dest b   set b.NAME =       (with t as (select * from temp)         select a.NAME from temp a where a.ID = b.ID)