CREATE PROCEDURE "DB2INST1"."TB_CON_BORR_ACCT_SUMMARY_PROC" (OUT RETURNCODE INTEGER,OUT ERRORMSG VARCHAR(255))
LANGUAGE SQL
P: BEGIN
DECLARE SQL_CODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLMSG VARCHAR(200) DEFAULT '';
DECLARE DT_STMT VARCHAR(200) DEFAULT '';
DECLARE V_CNT INTEGER DEFAULT 0;
DECLARE V_BORROW_NUM VARCHAR(26);
DECLARE V_LOAN_BAL DECIMAL(20,2) DEFAULT 0.00;
--DECLARE STMT VARCHAR(1024);
DECLARE STMT STATEMENT;
--声明CURSOR
DECLARE C_LOAN_BAL CURSOR FOR S1; --计算当前贷款余额
--异常处理，出现异常回滚，并记录异常代码，将异常代码返回
DECLARE EXIT HANDLER FOR SQLEXCEPTION ,SQLWARNING
BEGIN
SET ERRORMSG = SQLSTATE;
SET SQL_CODE = SQLCODE ;
SET RETURNCODE = SQL_CODE;
SET ERRORMSG = ERRORMSG||':出现异常，事务回滚';
END;
--处理NOT FOUND 异常
DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
SET SQL_CODE = SQLCODE;
SET RETURNCODE = SQL_CODE;
END;
--判断目标是否存在记录
SELECT COUNT(1) INTO V_CNT FROM TB_CON_BORR_ACCT_SUMMARY;
IF V_CNT > 0 THEN
--清空目标表
--SET DT_STMT = 'DELETE FROM TB_CON_BORR_ACCT_SUMMARY';
DELETE FROM TB_CON_BORR_ACCT_SUMMARY;
--EXECUTE IMMEDIATE DT_STMT;
COMMIT;
END IF;
--开始抽数
INSERT
INTO
TB_CON_BORR_ACCT_SUMMARY --借据帐务信息
(
PAYOUT_ACCT_SUMMARY_ID, --支用帐务信息ID
PAYOUT_INFO_DETAIL_ID, --支用明细ID
BORROW_NUM, --借据号
CONTRACT_NUM, --主合同编号
CUSTOMER_NUM, --CMIS客户编号
CURRENCY_CD , --币种代码
EXPIRATION_DATE , --到期日期
CREDIT_BALANCE , --当前授信余额
-- LOAN_BALANCE , --当前贷款余额
CLASSIFICATION_RESULT --五级分类结果
)
SELECT
"DB2INST1"."UUIDGENERATOR3" () AS PAY_SUM_ID,
"DB2INST1"."UUIDGENERATOR3" () AS PAY_INF_ID,
A.LOAN_NO,
A.COMAPACT_NO,
A.CSTM_NO,
DECODE(A.CURR_TYPE,'CNY','001','HKD','013','USD','014','JPY','027','EUR','033','GBP','012','CAD','028') AS CURR_TYPE, --币种
TIMESTAMP(SUBSTR(CAST(A.DUE_DATE AS CHAR(12)),1,4)|| SUBSTR(CAST(A.DUE_DATE AS CHAR(12)),6,2)||SUBSTR(CAST(A.DUE_DATE AS CHAR(12)),9,2)||'000000') AS DUE_DATE,
COALESCE(A.DLV_GUIDELINE,0.00) AS DLV_GUIDELINE, --发放指标 金额 默认为0.00
-- SUM(b.BAL), --余额
SUBSTR(A.FLAG,4,1) --五级分类标志位 取flag第4位
FROM
T_LOAN_IOU A;
IF SQL_CODE <> 0 THEN
SET SQLMSG = '处理公司客户-借据登记簿(T_LOAN_IOU)数据到TB_CON_BORR_ACCT_SUMMARY(借据帐务信息表)出错!';
SET ERRORMSG = SQLMSG;
GOTO ERROR_RETURN;
END IF;
COMMIT;
--声明定义临时表
-- INSERT INTO SESSION.TEMP_LOAN_BAL(borrow_num1,loan_bal1)
--select b.LOAN_NO,sum(b.BAL) from T_LOAN_LEDGER b group by b.LOAN_NO
-- having exists
-- (select 1 from T_LOAN_LEDGER a,TB_CON_BORR_ACCT_SUMMARY b where a.LOAN_NO=b.BORROW_NUM);
--计算当前贷款余额
-- SET STMT = 'select a.LOAN_NO,sum(a.BAL) from T_LOAN_LEDGER a,TB_CON_BORR_ACCT_SUMMARY b where a.LOAN_NO=b.BORROW_NUM group by a.LOAN_NO';
SET V_CNT =0;
SET STMT ='select a.LOAN_NO,sum(a.BAL) from T_LOAN_LEDGER a,TB_CON_BORR_ACCT_SUMMARY b
where a.LOAN_NO=b.BORROW_NUM group by a.LOAN_NO ';
PREPARE S1 FROM STMT;
OPEN C_LOAN_BAL;
C_LOAN_BAL_FETCH_LOOP:
LOOP
FETCH C_LOAN_BAL INTO V_BORROW_NUM,V_LOAN_BAL;
SET V_CNT = V_CNT+1;
IF SQL_CODE <> 0 THEN --注意sqlcode已经在前面被初始化了，所以这里要用 sql_code
LEAVE C_LOAN_BAL_FETCH_LOOP;
END IF;
UPDATE TB_CON_BORR_ACCT_SUMMARY TB
SET TB.LOAN_BALANCE =COALESCE( V_LOAN_BAL,0.00)
WHERE TB.BORROW_NUM =V_BORROW_NUM;
END LOOP C_LOAN_BAL_FETCH_LOOP;
CLOSE C_LOAN_BAL;
COMMIT;
SET SQLMSG = '处理公司客户-借据登记簿(T_LOAN_IOU)数据到TB_CON_BORR_ACCT_SUMMARY(借据帐务信息表)成功!';
SET ERRORMSG = SQLMSG;
SET SQL_CODE = 0;
SET RETURNCODE = SQL_CODE;
RETURN 1;
ERROR_RETURN:
ROLLBACK;
RETURN -1;
END P
请问 该过程中游标处理部分有什么问题，我单步跟踪没问题，就不到两万条数据，执行十多分钟还没执行完，问题出在什么地方呢？
收起