数据库

存储过程：
CREATE OR REPLACE PROCEDURE PRO_JGZHMX_FKXX_ZFXX_YE_FAIL(TABNAME VARCHAR2,
                                                         V_ID    NUMBER,
                                                         V_Gjj   NUMBER
                                                         ) IS
     TYPE FKXX_TABLE_TYPE IS TABLE OF FKXX%ROWTYPE INDEX BY BINARY_INTEGER;
     FKXX_TABLE FKXX_TABLE_TYPE;
     TYPE ZFXX_TABLE_TYPE IS TABLE OF ZFXX%ROWTYPE INDEX BY BINARY_INTEGER;
     ZFXX_TABLE ZFXX_TABLE_TYPE;

     V_YWZJ_ID     JGZHMX.YWZJ_ID%TYPE; --业务主键
     V_XMSXXX_ID   JGZHMX.XMSXXX_ID%TYPE; --项目属性ID
     V_JFFSE       JGZHMX.JFFSE%TYPE; --资金监管账户借方发生额
     V_DFFSE       JGZHMX.DFFSE%TYPE; --资金监管账户贷方发生额
     V_TABNAME     USER_TABLES.TABLE_NAME%TYPE;
     V_ZJJGZHXX_ID FKXX.ZJJGZHXX_ID%TYPE; --资金监管专户信息_ID
     V_NUM         CHAR(1);
     V_HYSJ        FKXX.HYSJ%TYPE;
     V_ZJZHYE      JGZHMX.ZJZHYE%TYPE; --资金监管专户信息的当前余额
     V_VALUES      NUMBER(20, 2);
     V_Gjjjgxx_Id  Fkxx.Gjjjgxx_Id%Type;
     V_Zfxxgjj_Id  Zfxx.Gjjjgxx_Id%Type;
BEGIN
     V_TABNAME := UPPER(TABNAME);
     BEGIN
          /*
          当放款表里的完结标志为失效(2)时
          更新JGZHMX的标志位为1,同时更新监管账户表当前余额
          */
    
          IF V_TABNAME = 'FKXX' THEN
               SELECT * INTO FKXX_TABLE(1) FROM FKXX WHERE FKXX_ID = V_ID AND GJJJGXX_ID = V_Gjj;
         
               V_YWZJ_ID     := FKXX_TABLE(1).FKXX_ID;
               V_JFFSE       := FKXX_TABLE(1).JKJE; --fkxx借款金额
               V_ZJJGZHXX_ID := FKXX_TABLE(1).ZJJGZHXX_ID;
               V_HYSJ        := FKXX_TABLE(1).HYSJ;
               V_Gjjjgxx_Id  := FKXX_TABLE(1).GJJJGXX_ID;
         
               --提取监管账户明细中监管账户余额
               SELECT COUNT(*)
               INTO   V_ZJZHYE
               FROM   JGZHMX
               WHERE  YWZJ_ID = V_YWZJ_ID
               AND    GJJJGXX_ID = V_Gjjjgxx_Id;
         
               IF V_ZJZHYE <> 0 THEN
                    SELECT ZJZHYE
                    INTO   V_ZJZHYE
                    FROM   JGZHMX
                    WHERE  YWZJ_ID = V_YWZJ_ID
                    AND    GJJJGXX_ID = V_Gjjjgxx_Id;
              
               END IF;
               --查出JGZHMX交易日期>=fkxx核验时间的V_XMSXXX_ID
         
               SELECT COUNT(*)
               INTO   V_XMSXXX_ID
               FROM   JGZHMX J, FKXX F
               WHERE  J.JYRQ >= F.HYSJ
               AND    J.YWZJ_ID = V_YWZJ_ID
               AND    J.GJJJGXX_ID = V_Gjjjgxx_Id
               AND    F.GJJJGXX_ID = V_Gjjjgxx_Id;
         
               IF V_XMSXXX_ID <> 0 THEN
                    SELECT DISTINCT J.XMSXXX_ID
                    INTO   V_XMSXXX_ID
                    FROM   JGZHMX J, FKXX F
                    WHERE  J.JYRQ >= F.HYSJ
                    AND    J.YWZJ_ID = V_YWZJ_ID
                    AND    J.GJJJGXX_ID = V_Gjjjgxx_Id
                    AND    F.GJJJGXX_ID = V_Gjjjgxx_Id;
              
                    --资金监管专户信息的当前余额 '-' fkxx借款金额
                    V_VALUES := V_ZJZHYE - V_JFFSE;
              
                    --更新标志位，标志位为1  
                    UPDATE JGZHMX
                    SET    BZW = 1
                    WHERE  YWZJ_ID = V_YWZJ_ID
                    AND    XMSXXX_ID = V_XMSXXX_ID
                    AND    GJJJGXX_ID = V_Gjjjgxx_Id;
              
                    --更新监管账户表当前余额
                    UPDATE ZJJGZHXX
                    SET    DQYE = V_VALUES
                    WHERE  ZJJGZHXX_ID = V_ZJJGZHXX_ID
                    AND    GJJJGXX_ID = V_Gjjjgxx_Id;
                    COMMIT;
              
                    --更新JGZHMX交易日期>=fkxx核验时间的当前余额
                    UPDATE JGZHMX
                    SET    ZJZHYE = ZJZHYE - V_JFFSE
                    WHERE  JYRQ >= V_HYSJ
                    AND    XMSXXX_ID = V_XMSXXX_ID
                    AND    GJJJGXX_ID = V_Gjjjgxx_Id;
                    COMMIT;
              
               END IF;
         
          END IF;
     END;

     /*
     当支付表里的完结标志为2 时
      交易日期>=核验时间 更新本表里的标志位为1
      */

     BEGIN
    
          IF V_TABNAME = 'ZFXX' THEN
         
               SELECT * INTO ZFXX_TABLE(1) FROM ZFXX WHERE ZFXX_ID = V_ID AND GJJJGXX_ID = V_Gjj;
         
               V_YWZJ_ID     := ZFXX_TABLE(1).ZFXX_ID;
               V_DFFSE       := ZFXX_TABLE(1).ZFZE; --zfxx支付总额
               V_ZJJGZHXX_ID := ZFXX_TABLE(1).ZJJGZHXX_ID;
               V_HYSJ        := ZFXX_TABLE(1).HYSJ;
               V_Zfxxgjj_Id  := ZFXX_TABLE(1).GJJJGXX_ID;
         
               --提取监管账户明细中监管账户余额
               SELECT COUNT(*)
               INTO   V_ZJZHYE
               FROM   JGZHMX
               WHERE  YWZJ_ID = V_YWZJ_ID
               AND    GJJJGXX_ID = V_Zfxxgjj_Id;
         
               IF V_ZJZHYE <> 0 THEN
                    SELECT ZJZHYE
                    INTO   V_ZJZHYE
                    FROM   JGZHMX
                    WHERE  YWZJ_ID = V_YWZJ_ID
                    AND    GJJJGXX_ID = V_Zfxxgjj_Id;
              
               END IF;
         
               --提取JGZHMX当前余额
               SELECT COUNT(*)
               INTO   V_NUM
               FROM   JGZHMX
               WHERE  YWZJ_ID = V_YWZJ_ID
               AND    GJJJGXX_ID = V_Zfxxgjj_Id;
         
               IF V_NUM <> 0 THEN
                    --查出JGZHMX交易日期>=zfxx核验时间的V_XMSXXX_ID
              
                    SELECT COUNT(*)
                    INTO   V_XMSXXX_ID
                    FROM   JGZHMX J, ZFXX F
                    WHERE  J.JYRQ >= F.HYSJ
                    AND    J.YWZJ_ID = V_YWZJ_ID
                    AND    J.GJJJGXX_ID = V_Zfxxgjj_Id
                    AND    F.GJJJGXX_ID = V_Zfxxgjj_Id;
              
                    IF V_XMSXXX_ID <> 0 THEN
                         SELECT DISTINCT J.XMSXXX_ID
                         INTO   V_XMSXXX_ID
                         FROM   JGZHMX J, ZFXX F
                         WHERE  J.JYRQ >= F.HYSJ
                         AND    J.YWZJ_ID = V_YWZJ_ID
                         AND    J.GJJJGXX_ID = V_Zfxxgjj_Id
                         AND    F.GJJJGXX_ID = V_Zfxxgjj_Id;
                   
                         --资金监管专户信息的当前余额 '+' zfxx支付总额
                         V_VALUES := V_ZJZHYE + V_DFFSE;
                   
                         --更新当前余额和标志位，标志位为1  
                         UPDATE JGZHMX
                         SET    BZW = 1
                         WHERE  YWZJ_ID = V_YWZJ_ID
                         AND    XMSXXX_ID = V_XMSXXX_ID
                         AND    GJJJGXX_ID = V_Zfxxgjj_Id;
                   
                         COMMIT;
                   
                         --更新监管账户表当前余额
                         UPDATE ZJJGZHXX
                         SET    DQYE = V_VALUES
                         WHERE  ZJJGZHXX_ID = V_ZJJGZHXX_ID
                         AND    GJJJGXX_ID = V_Zfxxgjj_Id;
                         COMMIT;
                   
                         --更新JGZHMX交易日期>=fkxx核验时间的当前余额
                         UPDATE JGZHMX
                         SET    ZJZHYE = ZJZHYE + V_DFFSE
                         WHERE  JYRQ >= V_HYSJ
                         AND    XMSXXX_ID = V_XMSXXX_ID
                         AND    GJJJGXX_ID = V_Zfxxgjj_Id;
                         COMMIT;
                   
                    END IF;
               END IF;
          END IF;
    
     END;

EXCEPTION
     WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE(SQLERRM);
          DBMS_OUTPUT.PUT_LINE(SQLCODE || ' ' || '触发器-表名：' || V_TABNAME || ' ' ||
                               '业务id:' || V_YWZJ_ID);
END;


存储过程：
CREATE OR REPLACE PROCEDURE PRO_DZTZMX_SFWJ_FAIL(V_DAYS VARCHAR2) IS

     TYPE FKXX_REC_TYPE is record(
       V_FKXX_ID    FKXX.FKXX_ID%TYPE, --放款信息ID
       V_FK_GJJJGXX_ID    FKXX.GJJJGXX_ID%TYPE --放款中心信息ID
    );
    TYPE FKXX_TABLE_TYPE IS TABLE OF FKXX_REC_TYPE;
    FKXXID_TABLE FKXX_TABLE_TYPE;
    TYPE ZFXXID_REC_TYPE is record(
       V_ZFXX_ID    ZFXX.ZFXX_ID%TYPE, --支付信息ID
       V_ZF_GJJJGXX_ID    ZFXX.GJJJGXX_ID%TYPE --支付中心信息ID
    );
    TYPE ZFXX_TABLE_TYPE IS TABLE OF ZFXXID_REC_TYPE;
      ZFXXID_TABLE  ZFXX_TABLE_TYPE;
     
      VFkxx_Id     Fkxx.Fkxx_Id%Type;
      VZfxx_Id     Zfxx.Zfxx_Id%Type;
      VGjjjgxx_Id  Fkxx.Gjjjgxx_Id%Type;
      VZfxxgjj_Id  Zfxx.Gjjjgxx_Id%Type;
      V_SQL        VARCHAR2(2000);
BEGIN
     BEGIN
          --fkxx 当前时间大于核验时间V_DAYS
          BEGIN
         
               V_SQL := 'SELECT FKXX_ID,GJJJGXX_ID FROM FKXX WHERE  (SFWJ = 0 OR SFWJ IS NULL )  AND    FKZT = 7  AND    HYSJ IS NOT NULL AND    TO_CHAR(TRUNC(SYSDATE) - TRUNC(HYSJ)) > ' ||
                        V_DAYS;
         
         
               EXECUTE IMMEDIATE V_SQL BULK COLLECT
                    INTO FKXXID_TABLE;
         
               FOR I IN 1 .. FKXXID_TABLE.COUNT LOOP
                    VFkxx_Id := FKXXID_TABLE(I).V_FKXX_ID;
                    VGjjjgxx_Id := FKXXID_TABLE(I).V_FK_GJJJGXX_ID;
                    UPDATE FKXX SET SFWJ = 2 WHERE FKXX_ID = VFkxx_Id AND GJJJGXX_ID = VGjjjgxx_Id;
                    COMMIT;
               END LOOP;
          END;
    
          --zfxx 当前时间大于核验时间V_DAYS
          BEGIN
               V_SQL := 'SELECT ZFXX_ID,GJJJGXX_ID FROM zfxx WHERE  (SFWJ = 0 OR SFWJ IS NULL ) AND  zfzt = 7  AND HYSJ IS NOT NULL AND TO_CHAR(TRUNC(SYSDATE) - TRUNC(HYSJ)) > ' ||
                        V_DAYS;
         
               EXECUTE IMMEDIATE V_SQL BULK COLLECT
                    INTO ZFXXID_TABLE;
         
               FOR I IN 1 .. ZFXXID_TABLE.COUNT LOOP
                    VZfxx_Id := ZFXXID_TABLE(I).V_ZFXX_ID;
                    VZfxxgjj_Id := ZFXXID_TABLE(I).V_ZF_GJJJGXX_ID;
                    UPDATE ZFXX SET SFWJ = 2 WHERE ZFXX_ID = VZfxx_Id AND GJJJGXX_ID = VZfxxgjj_Id;
                    COMMIT;
               END LOOP;
          END;
     END;

EXCEPTION
     WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('错误' || SQLCODE);
          DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

触发器：
create or replace trigger tr_DZTZMX_BOC
before insert  On DZTZMX_BOC
For Each Row
Declare
  Pragma Autonomous_Transaction; --加自治事务 触发器中可以commit，否则报错！

  Type Fkxx_Table_Type Is Table Of Fkxx%Rowtype;
  Fkxx_Table Fkxx_Table_Type;

  V_Zjjgzhxx_Id Zjjgzhxx.Zjjgzhxx_Id%Type;
  V_Xmsxxx_Id   Zjjgzhxx.Xmcjsxx_Id%Type;
  V_Zh          Zjjgzhxx.Zh%Type;
  V_Fkxx_Id     Fkxx.Fkxx_Id%Type;
  V_Zfxx_Id     Zfxx.Zfxx_Id%Type;
  V_Gjjjgxx_Id  Fkxx.Gjjjgxx_Id%Type;
  V_Zfxxgjj_Id  Zfxx.Gjjjgxx_Id%Type;
  V_Amount      Dztzmx_Boc.Amount%Type;
  V_Jkje        Fkxx.Jkje%Type;
  V_Dztzmx_Id   Dztzmx_Boc.Dztzmx_Id%Type;
  V_Accno       Dztzmx_Boc.Accno%Type;
  V_Recipaccno  Dztzmx_Bocom.Recipaccno%Type;
  V_Drcrf       Dztzmx_Abc.Drcrf%Type; --借贷标志(D:借、C:贷)
Begin
  --获取插入时传入的新值
  V_Dztzmx_Id  := :New.Dztzmx_Id;
  V_Amount     := :New.Amount;
  V_Accno      := :New.Accno;
  V_Recipaccno := :New.Recipaccno;
  V_Drcrf      := :New.Drcrf;
  /*查找ZJJGZHXX中 ZJJGZHXX_ID,XMSXXX_ID, ZH.
  条件是 DZTZMX_BOC的账号 等于 ZJJGZHXX 账号*/
  Begin
    If V_Drcrf = 'C' Then
      For V_Record In (Select A.Zjjgzhxx_Id, A.Xmsxxx_Id, A.Zh
                         From Zjjgzhxx a
                        Where A.Zh = V_Accno) Loop
     
        V_Zjjgzhxx_Id := V_Record.Zjjgzhxx_Id;
        V_Xmsxxx_Id   := V_Record.Xmsxxx_Id;
        V_Zh          := V_Record.Zh;
     
        -- fkxx的借款金额=DZTZMX_BOC的发生额
     
        Select * Bulk Collect
          Into Fkxx_Table
          From Fkxx
         Where Zjjgzhxx_Id = V_Zjjgzhxx_Id
           And Xmsxxx_Id = V_Xmsxxx_Id
           And Nvl(Sfwj, 0) <> 1
           And Jkje = V_Amount / 100
           And Fkzt = 7
           And Bzw <> 1
           And Hysj Is Not Null;
     
        For i In 1 .. Fkxx_Table.Count Loop
          V_Jkje       := (Fkxx_Table(i).Jkje) * 100;
          V_Fkxx_Id    := Fkxx_Table(i).Fkxx_Id;
          V_Gjjjgxx_Id := Fkxx_Table(i).Gjjjgxx_Id;
          If V_Jkje = V_Amount Then
            --更新FKXX的是否完结为1
            Update Fkxx
               Set Sfwj = 1
             Where Fkxx_Id = V_Fkxx_Id
               And Gjjjgxx_Id = V_Gjjjgxx_Id;
            Commit;
            --更新FKXX的是否完结为1  张丽萍更新
            :New.Sfdz := 1;
            Exit;
          End If;
        End Loop;
     
      End Loop;
    End If;
  End;

  Begin
    /* zfxx的支付总额=DZTZMX_BOC的发生额
    DZTZMX_BOC账号=zfxx的账号*/
    If V_Drcrf = 'D' Then
      For V_Record In (Select Z.Zfxx_Id, Z.Gjjjgxx_Id
                         From Zfxx z
                        Where Z.Jydszh = V_Recipaccno
                          And Z.Zfze * 100 = V_Amount
                          And Nvl(Z.Sfwj, 0) <> 1
                          And Z.Zfzt = 7
                          And Z.Bzw <> 1
                          And Z.Hysj Is Not Null) Loop
     
        V_Zfxx_Id    := V_Record.Zfxx_Id;
        V_Zfxxgjj_Id := V_Record.Gjjjgxx_Id;
        --更新zfxx的是否完结为1
        Update Zfxx
           Set Sfwj = 1
         Where Zfxx_Id = V_Zfxx_Id
           And Gjjjgxx_Id = V_Zfxxgjj_Id;
        Commit;
        --更新FKXX的是否完结为1  张丽萍更新
        :New.Sfdz := 1;
        Exit;
      End Loop;
    End If;
  End;
Exception
  When Others Then
    Dbms_Output.Put_Line('错误' || Sqlcode);
    Dbms_Output.Put_Line(Sqlerrm);
End;

触发器：
CREATE OR REPLACE TRIGGER TRG_JGZHMX_FKXX_YE AFTER
UPDATE OF SFWJ ON FKXX
FOR EACH ROW

Declare
  Pragma Autonomous_Transaction;
  New_Sfwj  Fkxx.Sfwj%Type;
  Old_Sfwj  Fkxx.Sfwj%Type;
  V_Fkxx_Id Fkxx.Fkxx_Id%Type;
  V_Gjjjgxx_Id Fkxx.Gjjjgxx_Id%Type;
  V_Tabname Varchar2(10) := 'fkxx';
Begin
  New_Sfwj  := :New.Sfwj;
  Old_Sfwj  := :Old.Sfwj;
  V_Fkxx_Id := :New.Fkxx_Id;
  V_Gjjjgxx_Id := :New.Gjjjgxx_Id;

  If New_Sfwj = 2 And Old_Sfwj = 0 Then
    Pro_Jgzhmx_Fkxx_Zfxx_Ye_Fail(V_Tabname, V_Fkxx_Id, V_Gjjjgxx_Id);
  Elsif New_Sfwj = 1 And Old_Sfwj = 2 Then
    Pro_Jgzhmx_Fkxx_Zfxx_Ye_Finish(V_Tabname, V_Fkxx_Id, V_Gjjjgxx_Id);
  End If;

Exception
  When Others Then
    Dbms_Output.Put_Line(Sqlerrm);
    Dbms_Output.Put_Line(Sqlcode || ' ' || '触发器-表名：' || V_Tabname || ' ' ||
                         '业务id:' || V_Fkxx_Id);
End;