17,086
社区成员
发帖
与我相关
我的任务
分享
CREATE OR REPLACE PROCEDURE HBBTDX
(
AID IN VARCHAR2,--被合并对象ID
BID IN VARCHAR2,--目标对象ID
ACODE IN VARCHAR2,--被合并对象CODE
BCODE IN VARCHAR2 -- 目标对象CODE
)
AS
ACNT NUMBER(2);
BCNT NUMBER(2);
BEGIN
SELECT COUNT(*) INTO ACNT FROM BASEOBJECT WHERE OBJECTCODE=ACODE;
SELECT COUNT(*) INTO BCNT FROM BASEOBJECT WHERE OBJECTCODE=BCODE;
IF ACNT=1 AND BCNT=1 THEN
DELETE FROM FSREGISTRATION WHERE REGMONEY=0 AND OBJECTID=AID;--删除补贴登记表中被合并对象的金额为0的补贴登记数据
UPDATE FSREGISTRATION SET OBJECTID=BID,OBJECTCODE=BCODE WHERE OBJECTID=AID AND OBJECTCODE=ACODE;--更新补贴登记表中被合并对象补贴登记数据为目标对象的
UPDATE bankpublish set peasantno=BCODE where peasantno=ACODE;--更新银行发放记录表中数据
DELETE FROM BASEOBJECTACCOUNT WHERE OBJECTID=AID;--删除被合并对象的银行账号
DELETE FROM BASEOBJECT WHERE OBJECTID=AID;--删除被合并对象
COMMIT;
ELSE
RETURN;
DBMS_OUTPUT.put_line('被合并补贴对象有'+ACNT+'户,目标对象有'+BCNT+'户');
END IF;
END HBBTDX;