数据库版本9.5 创建存储过程后执行报错
隐式系统操作类型 "5" 期间出错。对该错误返回的信息包括 SQLCODE "-204"、SQLSTATE "42704" 和消息标记 "SESSION.TMP_PROVISION_LOAN"。. SQLCODE=-727, SQLSTATE=56098, DRIVER=3.53.71
SESSION.TMP_PROVISION_LOAN 这张临时表在存储过程中已经创建,但还是报204的错误。 存储过程如下: |
CREATE PROCEDURE TBAS.PRO_DATAS_EXECUTE(IN v_record_date date)
NOT DETERMINISTIC
LANGUAGE SQL
MODIFIES SQL DATA
/**************************************************************************************************
($E)过程名称:pro_datas_execute
($N)中文名称:拨备导入数据处理
($D)中文描述:将导入的对公零售贷款余额数据整理为一张数据表
($C)执行周期:不定期
($A)来源层次:
($B)目标层次:
($F)输入数据:
($T)输出数据:SM_LOG_BATCH
($O)备注信息:
($P)参数列表:
----------------------------------------------------------------------------------------------
参数 IN/OUT 类型 说明
----------------------------------------------------------------------------------------------
record_date IN DATE 数据日期(上月月底数据)
($H)版本历史:
----------------------------------------------------------------------------------------------
作者 日期 版本号 说明
----------------------------------------------------------------------------------------------
**************************************************************************************************/
BEGIN
DECLARE v_step NUM(10); --当前执行的步骤
DECLARE v_english_name VARCHAR(60); --过程英文名称
DECLARE v_chinese_name VARCHAR(100);--过程中文名称
DECLARE v_start_time TIMESTAMP; --开始执行时间
DECLARE v_end_time TIMESTAMP; --结束执行时间
DECLARE v_rec_num INT; --记录数
DECLARE v_out_msg VARCHAR(1000);
DECLARE v_out_flag SMALLINT;
DECLARE sqlcode,sql_code INTEGER DEFAULT 0;--
DECLARE sqlstate,sql_state CHAR(5) DEFAULT '00000';--
DECLARE v_exec_sql VARCHAR(200);--动态执行SQL语句
DECLARE v_year varchar(4);
DECLARE v_month varchar(2);
DECLARE v_lastM date;
--异常情况处理
DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
-- SIGNAL SQLSTATE VALUE '38200';--
SET v_out_msg= '100:查询没有数据'; --
END;--
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
VALUES(SQLCODE,SQLSTATE) INTO sql_code,sql_state;--
SET v_out_msg = 'Step='||CHAR(v_step)||',DB2Error='||CHAR(sql_code)||'('||'【SQLState】'||sql_state||'):'||SQLERRM(sql_code);--
SET v_end_time = CURRENT TIMESTAMP;--
SET v_out_flag = -1;--
ROLLBACK;--
CALL p_rm_log_batch(v_record_date,v_english_name,v_chinese_name,v_start_time,v_end_time,v_out_flag,v_out_msg);--
RESIGNAL;--
END;--
DECLARE CONTINUE HANDLER FOR SQLWARNING
BEGIN
-- SIGNAL SQLSTATE VALUE SQLSTATE; --
VALUES(SQLCODE,SQLSTATE) INTO sql_code,sql_state;--
SET v_out_msg = 'Step='||CHAR(v_step)||',DB2Error='||CHAR(sql_code)||'('||'【SQLState】'||sql_state||'):'||SQLERRM(sql_code);--
SET v_end_time = CURRENT TIMESTAMP;--
SET v_out_flag = -2;--
CALL p_rm_log_batch(v_record_date ,v_english_name,v_chinese_name,v_start_time,v_end_time,v_out_flag,v_out_msg);--
END;--
DECLARE GLOBAL TEMPORARY TABLE session.tmp_provision_loan AS
(SELECT BRN_NBR,ADJUST_FLG,CP_FLG,LOAN_TYP,SUB_TYP,NORMAL1_AMT,NORMAL2_AMT,NORMAL3_AMT,NORMAL4_AMT,NORMAL5_AMT,FORCUS1_AMT,FORCUS2_AMT,
SECONDARY_AMT,DUBIOUS_AMT,LOSS_AMT,RECORD_DATE
FROM provision_loan
WHERE 1=2
)DEFINITION ONLY WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED IN TBAS_USRTMP PARTITIONING KEY(BRN_NBR);--
SET v_english_name = 'PRO_DATAS_EXECUTE';--
SET v_chinese_name = '拨备导入数据处理';--
SET v_start_time = CURRENT TIMESTAMP;--
SET v_step = 100;
SET v_year =CHAR(YEAR(v_record_date -DAY(v_record_date ) DAYS));
SET v_month =CHAR(MONTH(v_record_date -DAY(v_record_date ) DAYS));
SET v_lastM =v_record_date -day(v_record_date ) days;
INSERT INTO SESSION.tmp_provision_loan(BRN_NBR,ADJUST_FLG,CP_FLG,LOAN_TYP,SUB_TYP,
NORMAL1_AMT,NORMAL2_AMT,NORMAL3_AMT,NORMAL4_AMT,NORMAL5_AMT,FORCUS1_AMT,FORCUS2_AMT,
SECONDARY_AMT,DUBIOUS_AMT,LOSS_AMT,RECORD_DATE)
SELECT BRN_NBR,'0','pro_cmp','bus_cmp','',
CASE WHEN NORMAL1_AMT IS NULL THEN 0 ELSE NORMAL1_AMT END,
CASE WHEN NORMAL2_AMT IS NULL THEN 0 ELSE NORMAL2_AMT END,
CASE WHEN NORMAL3_AMT IS NULL THEN 0 ELSE NORMAL2_AMT END,
CASE WHEN NORMAL4_AMT IS NULL THEN 0 ELSE NORMAL4_AMT END,
CASE WHEN NORMAL5_AMT IS NULL THEN 0 ELSE NORMAL5_AMT END,
CASE WHEN FORCUS_AMT IS NULL THEN 0 ELSE FORCUS_AMT END,
CASE WHEN SP_FORCUS_AMT IS NULL THEN 0 ELSE SP_FORCUS_AMT END,
CASE WHEN SECONDARY_AMT IS NULL THEN 0 ELSE SECONDARY_AMT END,
CASE WHEN DUBIOUS_AMT IS NULL THEN 0 ELSE DUBIOUS_AMT END,
CASE WHEN LOSS_AMT IS NULL THEN 0 ELSE LOSS_AMT END,v_record_date
FROM tbas.PRO_COM_LOAN;
commit;
SET v_step= 200;
INSERT INTO SESSION.tmp_provision_loan(BRN_NBR,ADJUST_FLG,CP_FLG,LOAN_TYP,SUB_TYP,
NORMAL1_AMT,NORMAL2_AMT,NORMAL3_AMT,NORMAL4_AMT,NORMAL5_AMT,FORCUS1_AMT,FORCUS2_AMT,
SECONDARY_AMT,DUBIOUS_AMT,LOSS_AMT,RECORD_DATE)
SELECT BRN_NBR,'0','pro_rel','bus_loan','0',
CASE WHEN NORMAL1_AMT IS NULL THEN 0 ELSE NORMAL1_AMT END,
CASE WHEN NORMAL2_AMT IS NULL THEN 0 ELSE NORMAL2_AMT END,
CASE WHEN NORMAL3_AMT IS NULL THEN 0 ELSE NORMAL2_AMT END,
CASE WHEN NORMAL4_AMT IS NULL THEN 0 ELSE NORMAL4_AMT END,
CASE WHEN NORMAL5_AMT IS NULL THEN 0 ELSE NORMAL5_AMT END,
CASE WHEN FORCUS1_AMT IS NULL THEN 0 ELSE FORCUS1_AMT END,
CASE WHEN FORCUS2_AMT IS NULL THEN 0 ELSE FORCUS2_AMT END,
CASE WHEN SECONDARY_AMT IS NULL THEN 0 ELSE SECONDARY_AMT END,
CASE WHEN DUBIOUS_AMT IS NULL THEN 0 ELSE DUBIOUS_AMT END,
CASE WHEN LOSS_AMT IS NULL THEN 0 ELSE LOSS_AMT END,v_record_date
FROM tbas.PRO_PCH_LOAN;
commit;
SET v_step= 300;--
INSERT INTO SESSION.tmp_provision_loan(BRN_NBR,ADJUST_FLG,CP_FLG,LOAN_TYP,SUB_TYP,
NORMAL1_AMT,NORMAL2_AMT,NORMAL3_AMT,NORMAL4_AMT,NORMAL5_AMT,FORCUS1_AMT,FORCUS2_AMT,
SECONDARY_AMT,DUBIOUS_AMT,LOSS_AMT,RECORD_DATE)
SELECT BRN_NBR,'0','pro_rel','bus_loan','1',
CASE WHEN NORMAL1_AMT IS NULL THEN 0 ELSE NORMAL1_AMT END,
CASE WHEN NORMAL2_AMT IS NULL THEN 0 ELSE NORMAL2_AMT END,
CASE WHEN NORMAL3_AMT IS NULL THEN 0 ELSE NORMAL2_AMT END,
CASE WHEN NORMAL4_AMT IS NULL THEN 0 ELSE NORMAL4_AMT END,
CASE WHEN NORMAL5_AMT IS NULL THEN 0 ELSE NORMAL5_AMT END,
CASE WHEN FORCUS1_AMT IS NULL THEN 0 ELSE FORCUS1_AMT END,
CASE WHEN FORCUS2_AMT IS NULL THEN 0 ELSE FORCUS2_AMT END,
CASE WHEN SECONDARY_AMT IS NULL THEN 0 ELSE SECONDARY_AMT END,
CASE WHEN DUBIOUS_AMT IS NULL THEN 0 ELSE DUBIOUS_AMT END,
CASE WHEN LOSS_AMT IS NULL THEN 0 ELSE LOSS_AMT END,v_record_date
FROM tbas.PRO_SML_LOAN;
commit;
SET v_step= 400;--
INSERT INTO SESSION.tmp_provision_loan(BRN_NBR,ADJUST_FLG,CP_FLG,LOAN_TYP,SUB_TYP,
NORMAL1_AMT,NORMAL2_AMT,NORMAL3_AMT,NORMAL4_AMT,NORMAL5_AMT,FORCUS1_AMT,FORCUS2_AMT,
SECONDARY_AMT,DUBIOUS_AMT,LOSS_AMT,RECORD_DATE)
SELECT a.BRN_NBR,'0','pro_rel','nbus_loan','2',
CASE when a.NORMAL1_AMT IS NULL THEN 0 ELSE a.NORMAL1_AMT END -
CASE when b.NORMAL1_AMT IS NULL THEN 0 ELSE b.NORMAL1_AMT END -
CASE when c.NORMAL1_AMT IS NULL THEN 0 ELSE c.NORMAL1_AMT END,
CASE when a.NORMAL2_AMT IS NULL THEN 0 ELSE a.NORMAL2_AMT END -
CASE when b.NORMAL2_AMT IS NULL THEN 0 ELSE b.NORMAL2_AMT END -
CASE when c.NORMAL2_AMT IS NULL THEN 0 ELSE c.NORMAL2_AMT END,
CASE when a.NORMAL3_AMT IS NULL THEN 0 ELSE a.NORMAL3_AMT END -
CASE when b.NORMAL3_AMT IS NULL THEN 0 ELSE b.NORMAL3_AMT END -
CASE when c.NORMAL3_AMT IS NULL THEN 0 ELSE c.NORMAL3_AMT END,
CASE when a.NORMAL4_AMT IS NULL THEN 0 ELSE a.NORMAL4_AMT END -
CASE when b.NORMAL4_AMT IS NULL THEN 0 ELSE b.NORMAL4_AMT END -
CASE when c.NORMAL4_AMT IS NULL THEN 0 ELSE c.NORMAL4_AMT END,
CASE when a.NORMAL5_AMT IS NULL THEN 0 ELSE a.NORMAL5_AMT END -
CASE when b.NORMAL5_AMT IS NULL THEN 0 ELSE b.NORMAL5_AMT END -
CASE when c.NORMAL5_AMT IS NULL THEN 0 ELSE c.NORMAL5_AMT END,
CASE when a.FORCUS1_AMT IS NULL THEN 0 ELSE a.FORCUS1_AMT END -
CASE when b.FORCUS1_AMT IS NULL THEN 0 ELSE b.FORCUS1_AMT END -
CASE when c.FORCUS1_AMT IS NULL THEN 0 ELSE c.FORCUS1_AMT END,
CASE when a.FORCUS2_AMT IS NULL THEN 0 ELSE a.FORCUS2_AMT END -
CASE when b.FORCUS2_AMT IS NULL THEN 0 ELSE b.FORCUS2_AMT END -
CASE when c.FORCUS2_AMT IS NULL THEN 0 ELSE c.FORCUS2_AMT END,
CASE when a.SECONDARY_AMT IS NULL THEN 0 ELSE a.SECONDARY_AMT END -
CASE when b.SECONDARY_AMT IS NULL THEN 0 ELSE b.SECONDARY_AMT END -
CASE when c.SECONDARY_AMT IS NULL THEN 0 ELSE c.SECONDARY_AMT END,
CASE when a.DUBIOUS_AMT IS NULL THEN 0 ELSE a.DUBIOUS_AMT END -
CASE when b.DUBIOUS_AMT IS NULL THEN 0 ELSE b.DUBIOUS_AMT END -
CASE when c.DUBIOUS_AMT IS NULL THEN 0 ELSE c.DUBIOUS_AMT END,
CASE when a.LOSS_AMT IS NULL THEN 0 ELSE a.LOSS_AMT END -
CASE when b.LOSS_AMT IS NULL THEN 0 ELSE b.LOSS_AMT END -
CASE when c.LOSS_AMT IS NULL THEN 0 ELSE c.LOSS_AMT END,
v_record_date
FROM tbas.PRO_ALL_LOAN a LEFT JOIN tbas.PRO_PCH_LOAN b
on a.BRN_NBR=b.BRN_NBR
LEFT JOIN tbas.PRO_SML_LOAN c
on a.BRN_NBR=c.BRN_NBR;
commit;
--临时表数据插入实体表中
SET v_step= 500;--
INSERT INTO PROVISION_LOAN_HIS(BRN_NBR,ADJUST_FLG,CP_FLG,LOAN_TYP,SUB_TYP,NORMAL1_AMT,NORMAL2_AMT,NORMAL3_AMT,NORMAL4_AMT,NORMAL5_AMT,FORCUS1_AMT,FORCUS2_AMT,
SECONDARY_AMT,DUBIOUS_AMT,LOSS_AMT,YEAR,MONTH)
SELECT BRN_NBR,ADJUST_FLG,CP_FLG,LOAN_TYP,SUB_TYP,NORMAL1_AMT,NORMAL2_AMT,NORMAL3_AMT,NORMAL4_AMT,NORMAL5_AMT,FORCUS1_AMT,FORCUS2_AMT,
SECONDARY_AMT,DUBIOUS_AMT,LOSS_AMT,v_year,v_month
FROM PROVISION_LOAN WHERE RECORD_DATE=v_lastM ;
Delete From PROVISION_LOAN;
INSERT INTO provision_loan(BRN_NBR,ADJUST_FLG,CP_FLG,LOAN_TYP,SUB_TYP,NORMAL1_AMT,NORMAL2_AMT,NORMAL3_AMT,NORMAL4_AMT,NORMAL5_AMT,FORCUS1_AMT,FORCUS2_AMT,
SECONDARY_AMT,DUBIOUS_AMT,LOSS_AMT,RECORD_DATE)
SELECT BRN_NBR,ADJUST_FLG,CP_FLG,LOAN_TYP,SUB_TYP,NORMAL1_AMT,NORMAL2_AMT,NORMAL3_AMT,NORMAL4_AMT,NORMAL5_AMT,FORCUS1_AMT,FORCUS2_AMT,
SECONDARY_AMT,DUBIOUS_AMT,LOSS_AMT,RECORD_DATE
FROM SESSION.tmp_provision_loan;
commit;
SET v_end_time = CURRENT TIMESTAMP;--
SET v_out_flag = 0;--
SET v_out_msg = '执行pro_datas_execute成功';--
--写跟踪日志
CALL p_rm_log_batch(v_record_date ,v_english_name,v_chinese_name,v_start_time,v_end_time,v_out_flag,v_out_msg);--
END
;