oracle触发器和存储过程简单例子

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.bpdl13,

t2.bpdl14,

t2.bpfil2,

t2.bprp21,

t2.bpdl15,

t2.bprp22

INTO lv_glc,

lv_dl11,

lv_dl13,

lv_dl14,

lv_fil2,

lv_rp21,

lv_dl15,

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, 'yyyy-mm-dd hh24:mi:sssss'), '|'); --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, 'yyyy-mm-dd hh24:mi:sssss'),

'|'); --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, 'yyyy-mm-dd hh24:mi:sssss'),

'|'); --TEST

elsif sql%rowcount = 0 then

INSERT INTO TEST_LOG

VALUES

('更新失败',

'|',

TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:sssss'),

'|'); --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.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, 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, 'yyyy-mm-dd hh24:mi:sssss'),

'|'); --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

('关联上预收 不用开票',

'|',

TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:sssss'),

'|'); --TEST*/

--关联上预收，此时不开票(开票状态urab=4，开票金额aa1=0)

-- 来自应收不重复开票那获取到的流水单编号

-- 开票金额 aa1

ln_aa1 := 0;

--发票状态

lv_g_urab := 4;

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,

-- rpdl01a,

-- rpdl02a,

-- rpurab,

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_g_dl12,

-- lv_dl12,

lv_dl13,

lv_dl14,

lv_fil2,

lv_rp21,

lv_dl15,

lv_rp22,

lv_g_dl02a,

lv_g_dl01a,

lv_g_urab,

lv_pyid,

ln_dmtj,

-- lv_g_dl01a,

-- lv_g_dl02a,

-- lv_g_urab,

f03b11_rec.rpuser,

f03b11_rec.rppid,

f03b11_rec.rpupmj,

f03b11_rec.rpupmt,

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定义全局异常 EXCEPTION WHEN OTHERS THEN rollback;

2 捕获具体异常 要把可能出异常的crud放到begin end;里面并且捕获异常--如果不捕获具体异常就会跳到全局异常

3 常见的异常 no_data_found

4 调试触发器和存储过程 借助log表和test window!