oracle with as delete,Oracle with as 临时表的高级用法

--利用  with as 临时表 进行插入(修改)操作

INSERT INTO TEMP_REFRESHMANAGERDETAIL (PLAZABUDGETINFOID, PLAZABUDGETID, ACCOUNTCODEID, CODEFORMAT, NFNBUDGETMONTH1, NFNBUDGETMONTH2, NFNBUDGETMONTH3, NFNBUDGETMONTH4, NFNBUDGETMONTH5, NFNBUDGETMONTH6, NFNBUDGETMONTH7, NFNBUDGETMONTH8, NFNBUDGETMONTH9, NFNBUDGETMONTH10, NFNBUDGETMONTH11, NFNBUDGETMONTH12, TAXRATE)

WITH NEWBUDGETDETAIL AS (

SELECT A.* FROM FM_BUDGETDETAIL A

INNER JOIN FM_PLAZABUDGETRELATION B ON A.BUDGETID = B.BUDGETID

INNER JOIN (

SELECT * FROM FM_ACCOUNTCODE WHERE RECORDSTATUS <> 'INACTIVE' AND TRIM(NVL(ISDELETE, 'N')) = 'N'

AND CODEFORMAT = V_FORMATCODE

START WITH ACCOUNTCODE = 'DSGPMF4' CONNECT BY  PARENTCODEID = PRIOR ACCOUNTCODEID

) ACC ON ACC.ACCOUNTCODEID = A.ACCOUNTCODEID

WHERE B.PLAZABUDGETID = P_PLAZABUDGETID

AND A.RECORDSTATUS <>'INACTIVE' AND B.RECORDSTATUS <> 'INACTIVE'

)

SELECT A.PLAZABUDGETINFOID, A.PLAZABUDGETID, A.ACCOUNTCODEID,V_FORMATCODE AS CODEFORMAT,

B.NFNBUDGETMONTH1,B.NFNBUDGETMONTH2,B.NFNBUDGETMONTH3,B.NFNBUDGETMONTH4,B.NFNBUDGETMONTH5,B.NFNBUDGETMONTH6,

B.NFNBUDGETMONTH7,B.NFNBUDGETMONTH8,B.NFNBUDGETMONTH9,B.NFNBUDGETMONTH10,B.NFNBUDGETMONTH11,B.NFNBUDGETMONTH12,B.TAXBUDGET AS TAXRATE

FROM FM_PLAZABUDGETSHOT A

INNER JOIN NEWBUDGETDETAIL B ON A.ACCOUNTCODEID = B.ACCOUNTCODEID

WHERE A.PLAZABUDGETID = P_PLAZABUDGETID AND A.RECORDSTATUS <> 'INACTIVE';

代码有点长，仅作为学习记录，有使用问题可以联系博主