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

Oracle 前端之家
前端之家收集整理的这篇文章主要介绍了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.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！

猜你在找的Oracle相关文章

[Oracle]记一次由sequence引发的enq sv-contention等待事件
数据库版本：11.2.0.4 RAC(1)问题现象从EM里面可以看到，在23号早上8：45~8：55时，数据库...
oracle统计信息的锁定与解锁
（一）问题背景最近在对一个大约200万行数据的表查看执行计划时，发现存在异常，理论上应该...
【转】RMAN删除过期备份或非过期备份
（一）删除备份--DELETE命令用于删除RMAN备份记录及相应的物理文件。当使用RMAN执行备份操...
数据恢复顾问（DRA）
（1）DRA介绍 数据恢复顾问（Data Recovery Advise）是一个诊断和修复数据库的工具，DRA能...
RMAN备份与恢复（一）--认识RMAN
RMAN（Recovery Manager）是Oracle恢复管理器的简称，是集数据库备份（backup）、修复（re...
RMAN备份与恢复（二）--RMAN常用操作学习
（1）连接目标数据库 在RMAN中可以建立与目标数据库或恢复目录数据库的连接。与目标数据库...
ORA-00245问题总结
（1）问题描述 在进行数据库归档备份时（备份归档日志文件和控制文件），有时成功，有时失...
使用RMAN对数据文件进行恢复
（1）备份数据库 在使用RMAN进行数据库恢复之前，先用RMAN进行全库备份 （2）删除数据文件...
Oracle使用fy_recover_data恢复truncate删除的数据
(一)truncate操作概述在生产中，truncate是使用较多的命令，在使用不当的情况下，往往会造...
备份与恢复（四）--RMAN使用恢复目录catalog来保存档案资料库
（一）恢复目录概述RMAN档案资料库用于存储数据库备份、修复以及恢复所需的信息。这些信息...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问