Deep understanding and use of the WITH AS statement in Oracle and the use of ____oracle in conjunction with the incremental search

The with as phrase, also called the subquery section (subquery factoring), can do many things, defining an SQL fragment that will be used by the entire SQL statement. Sometimes it is to make the SQL statement more readable, or it may be in different parts of union all, as part of providing data. Particularly useful for union all. Because each part of union all may be the same, the cost is too high if each part is executed again, so you can use the with as phrase, just do it once. If the table name defined by the with as phrase is called more than two times, the optimizer automatically puts the data obtained by the with as phrase into a temp table, and if it is only invoked once, it does not. The hint materialize is to force the data in the with as phrase into a global temporary table.
One, with as syntax a single syntax: With Tempname as (select ...) select ...
Multiple syntaxes: With TempName1 as (select ...), tempName2 as (select ...), TempName3 as (select ...) ... select ...
The WITH query statement does not start with a select, but rather begins with the "with" keyword to assume that a temporary table TT was constructed beforehand before the query was actually made, and can then be used more than once for further analysis and processing
Two, with as example: now to get 11-14 from 1-19.          The general SQL is as follows: SELECT * FROM (---simulate a 20-row data Select level as LV from DUAL CONNECT by Level < TT WHERE tt.lv > tt.lv < 15 using with AS SQL: with TT As (--simulate a 20 row of data Select level as LV from DUAL CONNECT by level <) Select LV from TT WHERE LV > Ten and LV < 15
Multiple temporary table instances: 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;
The advantages of the WITH clause method increase the readability of SQL, if multiple subqueries are constructed, the structure will be clearer; More importantly: "One analysis, multiple use", which is why the performance of the place, reached the "less read" goal. The first method table that uses a subquery is scanned two times, and a table is scanned only once using the WITH clause method.      This can greatly improve the efficiency of data analysis and query. In addition, observe the WITH clause method execution plan, where "sys_temp_xxxx" is the intermediate statistic result temporary table constructed during the operation.
Four, with AS and the use of the combination of search and deletion note: 1. The temporary table created with the SELECT statement 2.with that must be referenced immediately must be referenced, otherwise the error 4.1 is used in conjunction with the SELECT query statement to query for documents that are not equal in the borrowing amount and the write-off slips that correspond to the same document number.

With Verificationinfo as
 (select Ment.fnumber,
         sum (t.famount) vloansum,
         ment.fnumber "document Number",
         sum ( T.famount) "Total borrowings in write-off orders" 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 "document Number",
         sum (t.famount) "Total borrowings in the loan sheet" "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!=;

4.2 Use the following with AS statement in conjunction with INSERT, can not be placed before the insert, but placed immediately before the place to call to request the same document number corresponding to the loan slip and write-off list, the amount of the loan is not equal to the bill, the corresponding loan slip deleted, and the corresponding write-off sheet inserted into the loan sheet (The loan slip is identical to the write-off sheet structure)

Insert into T_FINANCE_EXPENSEREMBURSEITEM2 (FID, Fkrembursementid, Famount, Fkcreatebyid, Fcreatetime, FK CUiD, Fkcosttypeid, fcosttypename) with Verificationinfo as (select Ment.fnumber, sum (t.famount) VL Oansum, Ment.fnumber "document Number", SUM (t.famount) "Total amount of borrowings in the write-off form" from Shenzhenjm.t_finance_expenserembur  Seitem 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 "Document Number", SUM (t.famount) "Total borrowings in the loan list" from SHENZHENJM.T_FINANCE_EXPENSEREMBURSEITEM2 t L EFT Join shenzhenjm.t_finance_expenserembursement ment on ment.fid = t.fkrembursementid where 1 = 1 Grou P by Ment.fnumber) select Sys_guid (), Ment.fid, T.famount, Ment.fkcreatebyid, men
         T.fcreatetime,Ment.fkcuid, T.fkcosttypeid, t.fcosttypename from T_finance_expenseremburseitem T-left join T_FI
            nance_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 In conjunction with delete delete

Delete from t_finance_expenseremburseitem2 item2  where exists (with temp as (select T.fnumber,    & nbsp;                          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 Use in conjunction with update

Update dest b
   Set b.name =
       (with t as (SELECT * to Temp)
         Select A.name from temp a where a.id = b.ID)