1 触发器
create or replace trigger testdta.trriger_f03b11----testdta是表空间 after insert or update on testdta.f03b11 for each row--行级触发器 declare---变量声明 returnid number; f03b11_rec f03b11%rowtype; V_TYPE test_log.l_typ%TYPE; fm11501bget_int06 fm11501b.neint06%TYPE; f58ag002get_rp01 f58ag002.rprp01%TYPE; begin
--TEST IF INSERTING THEN --INSERT触发 V_TYPE := 'INSERT'; DBMS_OUTPUT.PUT_LINE('记录已经成功插入,并已记录到日志'); ELSIF UPDATING THEN --UPDATE触发 V_TYPE := 'UPDATE'; DBMS_OUTPUT.PUT_LINE('记录已经成功更新,并已记录到日志'); ELSIF DELETING THEN --DELETE触发 V_TYPE := 'DELETE'; DBMS_OUTPUT.PUT_LINE('记录已经成功删除,并已记录到日志'); END IF;
IF V_TYPE = 'DELETE' THEN f03b11_rec.rpdoc := :old.rpdoc; f03b11_rec.rpdct := :old.rpdct; f03b11_rec.rpkco := :old.rpkco; f03b11_rec.rpsfx := :old.rpsfx; f03b11_rec.rppyid := :old.rppyid; ELSE f03b11_rec.rpdoc := :new.rpdoc; f03b11_rec.rpdct := :new.rpdct; f03b11_rec.rpkco := :new.rpkco; f03b11_rec.rpsfx := :new.rpsfx; f03b11_rec.rppyid := :new.rppyid; END IF;
INSERT INTO TEST_LOG VALUES ('log', V_TYPE, TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:sssss'), :NEW.rppost || '-' || :NEW.rpicut || '-' || :NEW.rpdct || '*' || :old.rpaap || '*' || :new.rpaap || '*' || :new.rpag || '*' || GT_PARK_INT_SEQ.nextVal || '|' || :new.rpvr01 || '|' || f58ag002get_rp01); --TEST
if :new.rppost = 'D' and :new.rpicut = 'RB' and :new.rpdct = 'RU' and NVL(:new.rpaap,0) != NVL(:new.rpag,0) NVL(:old.rpaap,0) != NVL(:new.rpaap,0) AND V_TYPE = 'UPDATE' then
prc_ins_f58ag001(f03b11_rec,returnid,V_TYPE); ---调用有参的存储过程
end if; end trriger_f03b11;
备注:(1) INSERTING,UPDATING ,DELETING 是操作类型关键字!
(2)建议创建一个log表(操作类型字段,序列字段,一个大长度的varchar2字段)来记录表的操作比如这里的test_log;
2存储过程
CREATE OR REPLACE PROCEDURE prc_ins_f58ag001(f03b11_rec f03b11%rowtype, on_return OUT NUMBER, intype IN VARCHAR2) IS --*************************************************************** -- CURSOR定义 --*************************************************************** CURSOR f58ag001_cur IS SELECT t1.rpdoc, t1.rpdct, t1.rpkco, t1.rpsfx, t1.rpan8, TRIM(t2.nealph) AS nealph, t1.rpdgj, t1.rpicut, t1.rpicu, t1.rpco, t1.rpglc, t1.rpan8j, t1.rppyr, t1.rppost, t1.rpcrcd, t1.rptxa1, t5.tatxr1, t1.rpexr1, t1.rpddj, t1.rppo, t1.rpunit, t1.rpmcu2, t2.neint06, TRIM(t3.abalph) AS abalph, case when (f03b11_rec.rpicut = '2B' and f03b11_rec.rpglc != 'PWF') then to_char(t3.abait) when (f03b11_rec.rpicut = '2B' and f03b11_rec.rpglc = 'PWF') then to_char(2) when (f03b11_rec.rpicut = 'RB' and trim(t9.ryurrf) != 'PWF') then to_char(t3.abait) when (f03b11_rec.rpicut = 'RB' and trim(t9.ryurrf) = 'PWF') then to_char(2) end AS rpait, t3.abdl11, TRIM(t3.abdl14) AS abfil3, t3.abcbnk, TRIM(t3.abd201) AS abd201, t3.abdl13, t1.rpag, t1.rpatxa, t1.rpstam, t3.abdti5, t1.rpvod, t1.rpaap, t6.kpan01, t7.wkd01, t7.wkd02, t8.njgent, t8.njdsv, t8.njurdt, t1.rppyid, t4.rprp01 FROM F03B11 t1 INNER JOIN FM11501B t2 ON t1.rppo = to_char(t2.nedoco) AND t2.nelsvr = (SELECT MAX(nelsvr) FROM FM11501B t WHERE t1.rppo = to_char(t.nedoco)) LEFT JOIN FM100013 t3 ON t2.neint06 = t3.aban8 LEFT JOIN F58ag002 t4 ON t2.neint06 = t4.rpan8 AND t1.rpco = t4.rpco AND t1.rppo = to_char(t4.rpdoco) AND t1.rpglc = t4.rpglc LEFT JOIN F4008 t5 ON t1.rptxa1 = t5.tatxa1 LEFT JOIN F58Hl006 t6 ON t1.rpkco = t6.kpco AND t1.rpdoc = t6.kpdoc AND t1.rppo = to_char(t6.kpdoco) AND t1.rpicu = t6.kpicu LEFT JOIN F58G0008 t7 ON t1.rpkco = t7.wkkco AND t1.rpdoc = t7.wkdoc AND t1.rppo = t7.wkpo AND t1.rpicu = t7.wkicu AND t7.wkckam != t7.wkaap LEFT JOIN F1511B t8 ON t1.rpkco = t8.njkco AND t1.rpdoc = t8.njdoc AND t1.rppo = to_char(t8.njdoco) AND t1.rpicu = t8.njicu LEFT JOIN F03B13 t9 ON t1.rppyid = t9.rypyid WHERE t1.rpkco = f03b11_rec.rpkco AND t1.rpdoc = f03b11_rec.rpdoc AND t1.rpdct = f03b11_rec.rpdct AND t1.rpsfx = f03b11_rec.rpsfx; --*************************************************************** -- 变量定义 --*************************************************************** -- n_count NUMBER; --get_DL02A NCHAR(200); --get_URAB NUMBER; exist_flg char(1); fm11501bget_int06 fm11501b.neint06%TYPE; f58ag002get_rp01 f58ag002.rprp01%TYPE; f03b14get_paap f03b14.rzpaap%TYPE; lv_atxa f58ag001.rpatxa%TYPE; lv_stam f58ag001.rpstam%TYPE; lv_txa1 fm110001.bptxa1%TYPE; lv_txr1 f4008.tatxr1%TYPE; lv_aaaj f03b14.rzaaaj%TYPE; lv_58gd01 f58g0008.Wkd01%TYPE; lv_58gd02 f58g0008.wkd02%TYPE; ln_ukid NUMBER; lv_dl01 fm101507.nhdl01%TYPE; ln_urdt1 f1511b.njurdt%TYPE; ln_urdt2 f1511b.njurdt%TYPE; ln_aa1 f58ag001.rpaa1%TYPE; ln_aa2 f58ag001.rpaa2%TYPE; ln_aa3 f58ag001.rpaa3%TYPE; lv_pyid f03b14.rzpyid%TYPE; lv_rsco f03b14.rzrsco%TYPE; ln_dmtj f03b14.rzdmtj%TYPE; ln_paap f03b14.rzpaap%TYPE; ln_aaaj f03b14.rzaaaj%TYPE; lv_glc f56g0001.bpglc%TYPE; lv_dl11 f56g0001.bpdl11%TYPE; lv_dl13 f56g0001.bpdl13%TYPE; lv_dl14 f56g0001.bpdl14%TYPE; lv_fil2 f56g0001.bpfil2%TYPE; lv_rp21 f56g0001.bprp21%TYPE; lv_dl15 f56g0001.bpdl15%TYPE; lv_rp22 f56g0001.bprp22%TYPE; lv_dl12 f58ag001.rpdl12%TYPE; lv_gent f1511b.njgent%TYPE; lv_g_dl01a f58ag001.rpdl01a%TYPE; lv_g_dl02a f58ag001.rpdl02a%TYPE; lv_g_urab f58ag001.rpurab%TYPE; lv_g_dl12 f58ag001.rpdl12%TYPE; lv_g_aa1 f58ag001.rpaa1%TYPE; ln_ag f58ag001.rpag%TYPE; ln_atxa f58ag001.rpatxa%TYPE; ln_stam f58ag001.rpstam%TYPE; f58ag001_rec f58ag001_cur%rowtype; pragma autonomous_transaction; BEGIN -- ukid BEGIN ln_ukid := 0; SELECT max(rpukid) INTO ln_ukid FROM f58AG001; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; --*************************************************************** -- F03B11数据LOOP处理,遍历游标 --*************************************************************** FOR f58ag001_rec IN f58ag001_cur() LOOP -- 单元名称 DL01 -- 付款号 PYID -- 收款日期 DMTJ -- 开票金额 PAAP BEGIN lv_dl01 := ' '; lv_pyid := 0; ln_dmtj := 0; ln_paap := 0; SELECT t.nhdl01, t.rzpyid, t.rzdmtj, t.rzrsco, t.bptxa1, t.rzpaap, t.rzaaaj INTO lv_dl01,lv_pyid,ln_dmtj,lv_rsco,lv_txa1,ln_paap,ln_aaaj FROM (SELECT t2.nhdl01, t3.rzpyid, t3.rzdmtj, t3.rzrsco, t4.bptxa1, (t3.rzpaap * -1) as rzpaap, t3.rzaaaj FROM F03B11 t1 INNER JOIN FM101507 t2 ON t1.rpunit = t2.nhunit LEFT JOIN F03B14 t3 ON t1.rpkco = t3.rzkco AND t1.rpdct = t3.rzdct AND t1.rpdoc = t3.rzdoc AND t1.rpsfx = t3.rzsfx LEFT JOIN FM110001 t4 ON t1.rpco = t4.bpco AND t1.rpurrf = t4.bpglc WHERE t1.rpkco = f03b11_rec.rpkco AND t1.rpdoc = f03b11_rec.rpdoc AND t1.rpdct = f03b11_rec.rpdct AND t1.rpsfx = f03b11_rec.rpsfx ORDER BY t3.rzpyid DESC,t3.rzdmtj DESC) t WHERE ROWNUM = 1; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; -- 历史开票金额 aa2 BEGIN ln_aa2 := 0; SELECT sum(t1.rpaa1) INTO ln_aa2 FROM f58AG001 t1 WHERE t1.rpkco = f03b11_rec.rpkco AND t1.rpdoc = f03b11_rec.rpdoc AND t1.rpdct = f03b11_rec.rpdct AND t1.rpsfx = f03b11_rec.rpsfx; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; -- 开票金额 aa1收款开票 IF f03b11_rec.rpicut = '2B' THEN ln_aa1 := ln_paap; lv_glc := f03b11_rec.rpglc; --发票信息 BEGIN SELECT t2.bpdl11, t2.bpdl13, t2.bpdl14, t2.bpfil2, t2.bprp21, t2.bpdl15, t2.bprp22 INTO lv_dl11, lv_dl13, lv_dl14, lv_fil2, lv_rp21, lv_dl15, lv_rp22 FROM F56G0001 t2 WHERE t2.bpco = f03b11_rec.rpkco AND t2.bpglc = f03b11_rec.rpglc; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; -- 费项开始日期 费项结束日期 IF (f03b11_rec.rpdct = 'RD' and f58ag001_rec.njgent = '1') or (f03b11_rec.rpdct = 'RJ' and f58ag001_rec.njgent = '3') or (f03b11_rec.rpdct = 'RN' and f58ag001_rec.njgent = '7') THEN ln_urdt1 := f58ag001_rec.njdsv; ln_urdt2 := f58ag001_rec.njurdt; END IF; IF f58ag001_rec.rprp01 = 'Y' THEN lv_pyid := ''; ln_dmtj := ''; END IF; END IF; -- 开票金额 aa1先开票后收款 IF f58ag001_rec.rprp01 = 'Y' THEN ln_aa1 := f03b11_rec.rpag; END IF; --预收款开票 IF f03b11_rec.rpicut = 'RB' and f03b11_rec.rpdct = 'RU' THEN -- 开票金额 aa1 ln_ag := f03b11_rec.rpag * -1; ln_aa1 := ln_ag; -- 费项开始日期 费项结束日期 ln_urdt1 := f58ag001_rec.wkd01; ln_urdt2 := f58ag001_rec.wkd02; --发票信息 BEGIN SELECT case when (t2.bpglc is not null) then t2.bpglc else trim(t1.ryurrf) end AS glc, t2.bpdl11, t2.bprp22 INTO lv_glc, lv_dl11, lv_rp22 FROM F03B13 t1 LEFT JOIN F56G0001 t2 ON trim(t1.ryurrf) = t2.bpglc WHERE t1.rypyid = trim(f03b11_rec.rppyid) AND t2.bpco = f03b11_rec.rpkco; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; END IF; -- 待开票金额 aa3 ln_aa3 := f03b11_rec.rpag - nvl(ln_aa1,0) - nvl(ln_aa2,0); --TEST --TEST -- 应收记录 收款开票 IF f03b11_rec.rpicut = '2B' and f03b11_rec.rppost = 'D' and f03b11_rec.rpvod != 'V' and f03b11_rec.rpexr1 = 'V' and f03b11_rec.rpglc not in ('YB ','LB ') THEN /* INSERT INTO TEST_LOG VALUES ('进来了','|',TO_CHAR(SYSDATE,'|'); --TEST*/ --GET INT06 BEGIN SELECT NEINT06 INTO fm11501bget_int06 FROM FM11501B WHERE TO_CHAR(NEDOCO) = f03b11_rec.Rppo; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; ---GET RP01 BEGIN SELECT RPRP01 INTO f58ag002get_rp01 FROM F58AG002 WHERE RPAN8 = fm11501bget_int06 AND RPCO = f03b11_rec.rpco AND TO_CHAR(RPDOCO) = f03b11_rec.rppo AND RPGLC = f03b11_rec.rpglc; EXCEPTION WHEN NO_DATA_FOUND THEN f58ag002get_rp01 := ' '; END; BEGIN f03b14get_paap := 0; IF f58ag002get_rp01 = 'Y ' THEN -- 开票金额 aa1先开票后收款 ln_aa1 := f03b11_rec.rpag; /* BEGIN INSERT INTO TEST_LOG VALUES ('更新为R', '|', TO_CHAR(SYSDATE, '|'); --TEST update F03B11 SET F03B11.RPVR01 = 'R ' WHERE RPDOC = f03b11_rec.Rpdoc AND RPDCT = f03b11_rec.rpdct AND RPKCO = f03b11_rec.rpkco AND RPSFX = f03b11_rec.Rpsfx; if sql%rowcount <> 0 then INSERT INTO TEST_LOG VALUES ('更新成功', '|', TO_CHAR(SYSDATE, '|'); --TEST elsif sql%rowcount = 0 then INSERT INTO TEST_LOG VALUES ('更新失败', '|'); --TEST end if; END;*/ ELSIF f58ag002get_rp01 != 'Y ' THEN -- 开票金额 aa1先收款后开票 ln_aa1 := ln_paap; lv_glc := f03b11_rec.rpglc; /*BEGIN SELECT RZPAAP INTO f03b14get_paap FROM F03B14 WHERE RZKCO = f03b11_rec.Rpkco AND RZDCT = f03b11_rec.rpdct AND RZDOC = f03b11_rec.rpdoc AND RZSFX = f03b11_rec.Rpsfx; EXCEPTION WHEN NO_DATA_FOUND THEN f03b14get_paap := 0; END;*/ END IF; END; --应收不重复开票标志(判断是否关联上预收记录,lv_gent不等于null表示关联上预收并且不开票) BEGIN /* SELECT t2.njgent, t1.rpdl01a, t1.rpdl02a, t1.rpurab, t1.rpdl12, t1.rpaa1 INTO lv_gent, lv_g_dl01a, lv_g_dl02a, lv_g_urab, lv_g_dl12, lv_g_aa1 FROM f58AG001 t1 INNER JOIN F1511B t2 ON t1.rpkco = t2.njkco AND to_number(t1.rppo) = t2.njdoco AND t1.rpglc = t2.njglc AND t1.rpurdt = t2.njdsv AND t1.rpd02 = t2.njurdt AND t1.rpmcu2 = t2.njmcu and t1.rpunit = t2.njunit WHERE t1.rpicut = 'RB' AND t1.rpdct = 'RU' and t1.rpdoc = f03b11_rec.rpdoc and t1.rpkco = f03b11_rec.rpkco and t1.rpsfx = f03b11_rec.rpsfx AND ROWNUM = 1;*/ SELECT t2.njgent, t1.rpaa1 INTO lv_gent, lv_g_aa1 FROM f58AG001 t1,f1511b t2 where t1.rpkco = f03b11_rec.rpkco AND t1.rppo = f03b11_rec.rppo AND t1.rpglc = f03b11_rec.rpglc AND t1.rpmcu2 = f03b11_rec.rpmcu2 and t1.rpunit = f03b11_rec.rpunit and t1.rpicut = 'RB' and t1.rpdct = 'RU' and f03b11_rec.rpkco = t2.njkco and f03b11_rec.rpdoc = t2.njdoc and f03b11_rec.rppo = t2.njdoco and f03b11_rec.rpicu = t2.njicu and t1.rpurdt = t2.njdsv and t1.rpd02 = t2.njurdt and ROWNUM = 1; EXCEPTION WHEN NO_DATA_FOUND THEN --lv_gent is null的情况 lv_gent := null; lv_g_dl01a := ' '; lv_g_dl02a := ' '; lv_g_urab := 0; --lv_g_dl12;下面获取! --lv_g_aa1;下面获取! END; -- ukid if ln_ukid is null then ln_ukid := 1; else ln_ukid := ln_ukid + 1; end if; ---lv_gent等于null表示应收sb没有关联上预收rb这时候插的状态是空并且需要开票,否则状态插的是4并且不开票! -- lv_gent := null; if lv_gent is null then /* INSERT INTO TEST_LOG VALUES ('未关联上预收', '|', TO_CHAR(SYSDATE, '|'); --TEST*/ -- 流水单编号 lv_dl12 := f03b11_rec.rpdoc || ln_ukid || f03b11_rec.rpupmj || f03b11_rec.rpupmt; INSERT INTO f58AG001 (rpdoc, rpdct, rpkco, rpsfx, rpukid, rpan8, rpalph, rpdgj, rpicut, rpicu, rpco, rpglc, rpan8j, rppyr, rppost, rpcrcd, rptxa1, rptxr1, rpexr1, rpddj, rppo, rpunit, rpmcu2, rpdl19, rpnick, rpait, rpdc, rpmd, rpcbnk, rpd201, rpdl13, rpdl11, rpurdt, rpd02, rpag, rpatxa, rpstam, rpaa1, rpaa2, rpaa3, rpdl01, rpan01, rpdl12, rpdl10, rpdl14, rpfil2, rprp21, rpdl15, rprp22, RPDL02A, RPDL01A, RPURAB, rppyid, rpdmtj, rpuser, rppid, rpupmj, rpupmt, rpjobn) VALUES (f03b11_rec.rpdoc, f03b11_rec.rpdct, f03b11_rec.rpkco, f03b11_rec.rpsfx, ln_ukid, f03b11_rec.rpan8, f58ag001_rec.nealph, f03b11_rec.rpdgj, f03b11_rec.rpicut, f03b11_rec.rpicu, f03b11_rec.rpco, lv_glc, f03b11_rec.rpan8j, f03b11_rec.rppyr, f03b11_rec.rppost, f03b11_rec.rpcrcd, f03b11_rec.rptxa1, f58ag001_rec.tatxr1, 'V', f03b11_rec.rpddj, f03b11_rec.rppo, f03b11_rec.rpunit, f03b11_rec.rpmcu2, f58ag001_rec.neint06, f58ag001_rec.abalph, f58ag001_rec.rpait, f58ag001_rec.abdl11, f58ag001_rec.abfil3, f58ag001_rec.abcbnk, f58ag001_rec.abd201, f58ag001_rec.abdl13, lv_dl11, ln_urdt1, ln_urdt2, f03b11_rec.rpag, f03b11_rec.rpatxa, f03b11_rec.rpstam, ln_aa1, ln_aa2, ln_aa3, lv_dl01, f58ag001_rec.kpan01, lv_dl12, lv_dl13, lv_dl14, lv_fil2, lv_rp21, lv_dl15, lv_rp22, ' ', 0, lv_pyid, ln_dmtj, f03b11_rec.rpuser, f03b11_rec.rppid, f03b11_rec.rpupmj, f03b11_rec.rpupmt, f03b11_rec.rpjobn); else /* INSERT INTO TEST_LOG VALUES ('关联上预收 不用开票', '|'); --TEST*/ --关联上预收,此时不开票(开票状态urab=4,开票金额aa1=0) -- 来自应收不重复开票那获取到的流水单编号 -- 开票金额 aa1 ln_aa1 := 0; --发票状态 lv_g_urab := 4; INSERT INTO f58AG001 (rpdoc, -- rpdl01a, -- rpdl02a, -- rpurab, -- 来自应收不重复开票那获取到的流水单编号 lv_g_dl12, -- lv_dl12, lv_g_dl02a, lv_g_dl01a, lv_g_urab, -- lv_g_dl01a, -- lv_g_dl02a, -- lv_g_urab, f03b11_rec.rpjobn); end if; END IF; IF f03b11_rec.rpicut = 'RB' and f03b11_rec.rppost = 'D' and f03b11_rec.rpvod != 'V' and f03b11_rec.rpurrf not in ('YB ','LB ') THEN -- 预收记录 预收款开票 /* IF f03b11_rec.rpicut = 'RB' and f03b11_rec.rpdct = 'RU' and f03b11_rec.rppost = 'D' and f03b11_rec.rpvod != 'V' and abs(f03b11_rec.rpag) != abs(f03b11_rec.rpaap) THEN*/ IF f03b11_rec.rpicut = 'RB' and f03b11_rec.rpdct = 'RU' and f03b11_rec.rppost = 'D' THEN --F4008 TXR1 begin select tatxr1 into lv_txr1 from f4008 where tatxa1 = lv_txa1; EXCEPTION WHEN NO_DATA_FOUND THEN null; end; -- ukid if ln_ukid is null then ln_ukid := 1; else ln_ukid := ln_ukid + 1; end if; -- 流水单编号 lv_dl12 := f03b11_rec.rpdoc || ln_ukid || f03b11_rec.rpupmj || f03b11_rec.rpupmt; -- 税 if lv_rsco = '25' then ln_stam := ln_aaaj; end if; begin select rzaaaj into lv_aaaj from f03b14 where rzpyid = f03b11_rec.rppyid and rzutic = '25'; EXCEPTION WHEN NO_DATA_FOUND THEN null; end; lv_stam := nvl(lv_aaaj,0); -- 不含税额 ln_atxa := ln_ag - nvl(ln_stam,0); lv_atxa := ln_ag - lv_stam; begin --费项开始日期,费项结束日期 SELECT WKD01,WKD02 INTO lv_58gd01,lv_58gd02 FROM F58G0008 WHERE wkpyid = f03b11_rec.rppyid AND wkrc5 = 0; EXCEPTION WHEN NO_DATA_FOUND THEN null; end; begin -- 开票金额 aa1 ln_ag := f03b11_rec.rpag * -1; ln_aa1 := ln_ag; -- 待开票金额 aa3 ln_aa3 := ln_ag - ln_aa1 - ln_aa2; end; --获取中间表发票状态和发票号码 /* begin select rpurab,rpdl02a into get_DL02A,get_URAB from f58ag001 where rpPYID = f03b11_rec.Rppyid; exception when no_data_found then null; end;*/ IF intype = 'UPDATE' AND f03b11_rec.Rpag != f03b11_rec.Rpaap THEN INSERT INTO f58AG001 (rpdoc, rpdct, rpkco, rpsfx, rpukid, rpan8, rpalph, rpdgj, rpicut, rpicu, rpco, rpglc, rpan8j, rppyr, rppost, rpcrcd, rptxa1, rptxr1, rpexr1, rpddj, rppo, rpunit, rpmcu2, rpdl19, rpnick, rpait, rpdc, rpmd, rpcbnk, rpd201, rpdl13, rpdl11, rpurdt, rpd02, rpag, rpatxa, rpstam, rpaa1, rpaa2, rpaa3, rpdl01, rpan01, rpdl12, rpdl10, rpdl14, rpfil2, rprp21, rpdl15, rprp22, RPDL02A, RPDL01A, RPURAB, rppyid, rpdmtj, rpuser, rppid, rpupmj, rpupmt, rpjobn) VALUES (f03b11_rec.rpdoc, f03b11_rec.rpdct, f03b11_rec.rpkco, f03b11_rec.rpsfx, ln_ukid, f03b11_rec.rpan8, f58ag001_rec.nealph, f03b11_rec.rpdgj, f03b11_rec.rpicut, f03b11_rec.rpicu, f03b11_rec.rpco, lv_glc, f03b11_rec.rpan8j, f03b11_rec.rppyr, f03b11_rec.rppost, f03b11_rec.rpcrcd, lv_txa1, lv_txr1, 'V', f03b11_rec.rpddj, f03b11_rec.rppo, f03b11_rec.rpunit, f03b11_rec.rpmcu2, f58ag001_rec.neint06, f58ag001_rec.abalph, f58ag001_rec.rpait, f58ag001_rec.abdl11, f58ag001_rec.abfil3, f58ag001_rec.abcbnk, f58ag001_rec.abd201, f58ag001_rec.abdl13, lv_dl11, -- ln_urdt1, -- ln_urdt2, lv_58gd01, lv_58gd02, ln_ag, -- f03b11_rec.rpatxa, -- f03b11_rec.rpstam, lv_atxa, lv_stam, ln_aa1, ln_aa2, ln_aa3, lv_dl01, --intype, f58ag001_rec.kpan01, lv_dl12, lv_dl13, lv_dl14, lv_fil2, lv_rp21, lv_dl15, lv_rp22, ' ', 0, lv_pyid, ln_dmtj, f03b11_rec.rpuser, f03b11_rec.rppid, f03b11_rec.rpupmj, f03b11_rec.rpupmt, f03b11_rec.rpjobn); END IF; END IF; END IF; END LOOP; on_return := 0; commit; EXCEPTION WHEN OTHERS THEN rollback; on_return := 1; END prc_ins_f58ag001;
备注:1定义全局异常EXCEPTIONWHEN OTHERS THEN rollback;
2 捕获具体异常 要把可能出异常的crud放到begin end;里面并且捕获异常--如果不捕获具体异常就会跳到全局异常
3 常见的异常 no_data_found
4 调试触发器和存储过程 借助log表和test window! (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|