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<