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
@
@
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
@