存储过程范例

最新推荐文章于 2022-10-10 10:58:52 发布

barry_yan

最新推荐文章于 2022-10-10 10:58:52 发布

阅读量275

点赞数

分类专栏：存储文章标签：存储

本文链接：https://blog.csdn.net/barry_yan/article/details/52808388

版权

存储专栏收录该内容

5 篇文章 0 订阅

订阅专栏

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
@