--Create a temporary table, specify a good format, is necessary, different from SQL Server so arbitrary; Create Global temporary table Record4 ( YLJGDM VARCHAR2) not null, Jzlsh VARCHAR2 () not null, kh VARCHAR2 (+), klx VARCHAR2 (+), hzxm VARCHAR2 (+), JZLX VARCHAR2 ( 3), ZZYSGH VARCHAR2 (+), Zzysxm VARCHAR2 (+), JZKSMC VARCHAR2 (+), jlgxsj DATE default sysdate,& nbsp ZBDM VARCHAR) on commit preserve rows; create Global temporary Table fee ( Jzlsh VARCHAR2 (a) not null, Mxxmje number (15,3) default 0, tfbz VARCHAR2 (1) not null ) on commit preserve rows; --run a debugged script, insert According to the temporary table insert into RECORD4 (YLJGDM,JLGXSJ,ZZYSGH,ZZYSXM,JZKSMC,ZBDM,JZLSH,KH,KLX,HZXM,JZLX) Select A.yljgdm,a. JLGXSJ,A.ZZYSGH,A.ZZYSXM,A.JZKSMC, ' Mjzyzb ', a.jzlsh,a.kh,a.klx,a.hzxm,a.jzlx from CEN_CORE_V20. Tb_yl_mz_medical_record A WHERE to_char (add_months (sysdate,-1), ' Yyyymm ') =to_char (A.JLGXSJ, ' yyyymm ');--select * FROM Record4; insert to Feeselect b.jzlsh,sum (B.mxxmje), b.tfbz from Cen_core_v20. Tb_his_mz_fee_detail B where To_char (Add_months (sysdate,-1), ' Yyyymm ') =to_char (B.STFSJ, ' yyyymm ') and b.mxfylb in (' 12 ' , ' + ', ' + ') GROUP by B.jzlsh,b.tfbz; --select * from Fee; --select * from biolap.tb_yljg_sjzl_hz;--after processed data, After running INSERT into target table insert into Biolap.tb_yljg_sjzl_hz (Yljgdm,tjsj,ysgh,ysxm,ksmc,zbdm,jzlsh,kh,klx,hzxm,jzlx,mxxmje) Select A.yljgdm,to_char (A.JLGXSJ, ' yyyymm '), A.ZZYSGH,A.ZZYSXM,A.JZKSMC,A.ZBDM,A.JZLSH,A.KH,A.KLX,A.HZXM,A.JZLX, SUM (case if b.tfbz= ' 1 ' then b.mxxmje when b.tfbz= ' 2 ' then-1*b.mxxmje else 0 end) JE from record4 a,fee bwhere A . Jzlsh=b.jzlshgroup by A.yljgdm,to_char (A.JLGXSJ, ' yyyymm '), A.zzysgh,a.zzysxm,a.jzksmc,a.zbdm,a.jzlsh,a.kh,a.klx,a . hzxm,a.jzlx; --delete something that might be problematic in the target table (it should not be written here, but because the column is used for every query of the program, so it can be written) delete from biolap.tb_yljg_sjzl_hz Where YLJGDM is null; --commits commit;--this type of preserve temporary table is session-based, even if committed, the data in the staging table will still exist-another delete type of temporary table, in transaction units, The table will still exist after the commit, but the data inside the table will be emptied-to avoid confusion, delete the temporary table directly, avoid prompting the table name when the table structure is updated, or mistakenly inherit the last data returned by the drop TAble Record4;drop Table Fee; --select * from biolap.tb_yljg_sjzl_hz where zbdm= ' Mjzyzb ';
Use an example to illustrate the Oracle staging table, create the process,