存储过程范例

最新推荐文章于 2024-04-24 14:34:29 发布

原创最新推荐文章于 2024-04-24 14:34:29 发布 · 295 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#存储

存储专栏收录该内容

5 篇文章

订阅专栏

本文介绍了一个数据库过程，用于批量更新客户的风险评估记录。该过程通过查询特定类型的客户风险测试记录，并将相关信息复制到客户的资金账户表中，同时清理旧的数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

drop procedure ac.cust_risk_test1
@
create procedure ac.cust_risk_test1()
language sql

begin
declare sqlcode INTEGER default 0;
declare M_CUSTID BIGINT DEFAULT 0;
declare M_BEGINDATE INTEGER DEFAULT 0;
declare M_EFFECTSERIALNO CHARACTER (30) DEFAULT '';
declare M_RISKTESTTYPE CHARACTER (4) DEFAULT '';
declare M_RISKTESTMETHOD CHARACTER (4) DEFAULT '';
declare M_RISKTESTVERSION CHARACTER (10) DEFAULT '';
declare M_RISKTESTANSWER VARCHAR (254) DEFAULT '';
declare M_RISKTESTRESULTLEVEL CHARACTER (4) DEFAULT '';
declare M_RISKTESTRESULTGRADE INTEGER DEFAULT 0;
declare M_RISKTESTVALIDBEGINDATE INTEGER DEFAULT 0;
declare M_RISKTESTVALIDENDDATE INTEGER DEFAULT 0;
declare M_RISKTESTNOTE VARCHAR (254) DEFAULT '';
declare M_RISKTESTVALIDTERM INTEGER DEFAULT 0;
declare M_CUSTCREDITLEVEL INTEGER DEFAULT 0;
declare M_FUNDACCOUNTID BIGINT DEFAULT 0;
declare M_BRANCHCODE CHARACTER (3) DEFAULT '';
declare M_EVALUATESTATUS CHARACTER (1) DEFAULT '';
declare M_SETEMP CHARACTER (6) DEFAULT '';
declare M_SETDATE CHARACTER (8) DEFAULT '';
declare M_SETTIME CHARACTER (8) DEFAULT '';
declare M_EVALUATEDICT CHARACTER (3) DEFAULT '';
declare M_INVERSTDEADLINE CHARACTER (3) DEFAULT '';
declare M_INVERSTVARIETY VARCHAR (80) DEFAULT '';

declare EXTEND cursor for
SELECT CUSTID,FUNDACCOUNTID
FROM AC.CUST_FUND_ACCOUNT
WHERE CUSTID in (SELECT CUSTID FROM AC.CUST_RISK_TEST WHERE RISKTESTTYPE = '1');
open EXTEND;
select_cust_risk_test1:
loop
fetch EXTEND into M_CUSTID,M_FUNDACCOUNTID;

if sqlcode=100 then
leave select_cust_risk_test1;
end if;

SELECT CUSTID,BEGINDATE,EFFECTSERIALNO,RISKTESTTYPE,RISKTESTMETHOD,RISKTESTVERSION,RISKTESTANSWER,RISKTESTRESULTLEVEL,RISKTESTRESULTGRADE,RISKTESTVALIDBEGINDATE,RISKTESTVALIDENDDATE,RISKTESTNOTE,RISKTESTVALIDTERM,CUSTCREDITLEVEL,BRANCHCODE,EVALUATESTATUS,SETEMP,SETDATE,SETTIME,EVALUATEDICT,INVERSTDEADLINE,INVERSTVARIETY
INTO M_CUSTID,M_BEGINDATE,M_EFFECTSERIALNO,M_RISKTESTTYPE,M_RISKTESTMETHOD,M_RISKTESTVERSION,M_RISKTESTANSWER,M_RISKTESTRESULTLEVEL,M_RISKTESTRESULTGRADE,M_RISKTESTVALIDBEGINDATE,M_RISKTESTVALIDENDDATE,M_RISKTESTNOTE,M_RISKTESTVALIDTERM,M_CUSTCREDITLEVEL,M_BRANCHCODE,M_EVALUATESTATUS,M_SETEMP,M_SETDATE,M_SETTIME,M_EVALUATEDICT,M_INVERSTDEADLINE,M_INVERSTVARIETY
FROM AC.CUST_RISK_TEST
WHERE CUSTID=M_CUSTID
and RISKTESTTYPE='1' FETCH FIRST 1 ROWS ONLY;
INSERT INTO AC.CUST_RISK_TEST (CUSTID,FUNDACCOUNTID,BEGINDATE,EFFECTSERIALNO,RISKTESTTYPE,RISKTESTMETHOD,RISKTESTVERSION,RISKTESTANSWER,RISKTESTRESULTLEVEL,RISKTESTRESULTGRADE,RISKTESTVALIDBEGINDATE,RISKTESTVALIDENDDATE,RISKTESTNOTE,RISKTESTVALIDTERM,CUSTCREDITLEVEL,BRANCHCODE,EVALUATESTATUS,SETEMP,SETDATE,SETTIME,EVALUATEDICT,INVERSTDEADLINE,INVERSTVARIETY)
VALUES (M_CUSTID,M_FUNDACCOUNTID,M_BEGINDATE,M_EFFECTSERIALNO,M_RISKTESTTYPE,M_RISKTESTMETHOD,M_RISKTESTVERSION,M_RISKTESTANSWER,M_RISKTESTRESULTLEVEL,M_RISKTESTRESULTGRADE,M_RISKTESTVALIDBEGINDATE,M_RISKTESTVALIDENDDATE,M_RISKTESTNOTE,M_RISKTESTVALIDTERM,M_CUSTCREDITLEVEL,M_BRANCHCODE,M_EVALUATESTATUS,M_SETEMP,M_SETDATE,M_SETTIME,M_EVALUATEDICT,M_INVERSTDEADLINE,M_INVERSTVARIETY);
end loop select_cust_risk_test1;
DELETE FROM AC.CUST_RISK_TEST
WHERE RISKTESTTYPE = '1'
AND FUNDACCOUNTID = 0;
end
@