存储过程范例

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
@