数据库

存储过程：
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;