|
|
5#
楼主 |
发表于 2008-12-25 16:43
|
只看该作者
问题解决了。
关键在于这一条：DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_endc1 = 1;
下面在做DELETE的时候因为表中没有当天的数据，所以删除的记录数为0，返回的时候就产生NOT FOUND了，所以只做一次循环就退出程序了。
以下是修改后的完整程序，测试通过。
-----------------------------
CREATE PROCEDURE PETL.P_ETL_HIS_MID_DAY(IN v_txdate CHAR(8),IN v_serialno INT,IN v_partition CHAR(2),OUT v_ret INT)
SPECIFIC P_ETL_HIS_MID_DAY
LANGUAGE SQL
--/************************************************************************************
-- 程序名称: PETL.P_ETL_HIS_MID_DAY
-- 功能描述: MID层日表历史数据统一处理程序
-- 输入参数: '20080901' - <作业的处理日期>
-- 0 - <作业流水号，手工调度默认为0>
-- 0 - <分区名，手工调度默认为0>
-- 输出参数: 0 - <0为正常结束，其余为异常>
-- 输入资源:
-- 输出资源:
-- 创建人员: 胡红强
-- 创建日期: 2008.12.24
-- 修改人员:
-- 修改日期:
-- 修改原因:
-- 执行说明：
-- 公司名称: si-tech
--/************************************************************************************
--执行MM月的数据，输入的日期参数为YYYY(MM+1)DD，如'20080901'则是执行08月份的数据。
--CALL PETL.P_ETL_HIS_MID_DAY('20080901',0,'0',?);
-- 编写规则说明
-- 说明1：所有自定义变量均用小写，并以v_打头；所有字段名均用大写
-- 说明2: 所有关键值均用大字；模式名、表名、函数名、存储过程名均用大写
-- 说明3: 段落之间的缩进为三个空格
-- 说明4: SQL语句目标和源要齐整
-- 说明5: 对每个表的数据作改变后都要显示的提交
BEGIN
-----程序固定变量，不要修改--------
DECLARE v_err_txt VARCHAR(70) DEFAULT 'NO ERROR';
DECLARE v_stepnum INT DEFAULT 0;
DECLARE v_rcount INT DEFAULT 0;
DECLARE v_rtable INT DEFAULT 0;
DECLARE v_prcname VARCHAR(60) DEFAULT 'P_ETL_HIS_MID_DAY';
DECLARE SQLCODE INT DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE MESSAGE_TEXT VARCHAR(70) DEFAULT 'NO ERROR';
-----在此自定义变量----------------
DECLARE v_errstate CHAR(5) DEFAULT '00000'; -- 运行代码变量
DECLARE v_errcode INTEGER DEFAULT 0; -- 运行状态变量
DECLARE v_thisyyyymmdd CHAR(8); -- 当日年月日变量，如当前2007年11月2日，则为20071102(一般当前日期的前一天)
DECLARE v_nextyyyymmdd CHAR(8); -- 后一日年月日变量,如当前2007年11月2日，则为20071103(一般是当前系统日期时间)
DECLARE v_lastyyyymmdd CHAR(8); -- 昨日年月日变量，如当前2007年11月2日，则为20071101
DECLARE v_ltmnyyyymmdd CHAR(8); -- 上月同日年月日变量，如当前2007年11月2日，则为20071002
DECLARE v_dlldyyyymmdd CHAR(8); -- 处理月的最后一天，如当前2007年11月2日，则为20071031
DECLARE v_thisyymmdd CHAR(6); -- 当日年月日变量
DECLARE v_thismmdd CHAR(4); -- 当日月日变量
DECLARE v_thismm CHAR(2); -- 当月变量
DECLARE v_thisdd CHAR(2); -- 当日变量
DECLARE v_thisyyyymm CHAR(6); -- 本月，取事务日期的YYYYMM，主要用于日处理
DECLARE v_dealyyyymm CHAR(6); -- 处理月，如当前2007年11月2日，则为200710
DECLARE v_lastyyyymm CHAR(6); -- 处理前1个月，如当前2007年11月2日，则为200709
DECLARE v_last2yyyymm CHAR(6); -- 处理前2个月，如当前2007年11月2日，则为200708
DECLARE v_last3yyyymm CHAR(6); -- 处理前3个月，如当前2007年11月2日，则为200707
DECLARE v_last12yyyymm CHAR(6); -- 上年的同月(处理月)
DECLARE v_thisyymm CHAR(4); -- 本月，取事务日期的YYMM，主要用于日处理
DECLARE v_dealyymm CHAR(4); -- 处理月，事务日期加一天后取上一月，主要用于月处理
DECLARE v_lastyymm CHAR(4); -- 处理月，事务日期加一天后取上一月，主要用于月处理
DECLARE v_dealmm CHAR(2); -- 上年的同处理月
DECLARE v_thisyyyy CHAR(4); -- 本年
DECLARE v_dealyyyy CHAR(4); -- 处理年，事务日期加一天后取上一月所处年，主要用于月处理
DECLARE v_nextyymm CHAR(4); --下月年月，如当前2007年11月2日，则为0712
DECLARE v_strtyyyymmdd CHAR(8); --v_nextyymm月份的第一天
DECLARE v_endyyyymmdd CHAR(8); --v_nextyymm月份的最后一天
DECLARE v_loop CHAR(8); --日期循环
DECLARE v_his_day1 CHAR(4); --历史分月表1
DECLARE v_his_day2 CHAR(4); --历史分月表2
DECLARE v_his_day3 CHAR(4); --历史分月表3
DECLARE v_his_day4 CHAR(4); --历史分月表4
DECLARE v_his_day9 CHAR(4); --历史分月表9
DECLARE v_his_day CHAR(4); --历史分月表
DECLARE v_his_date1 CHAR(8); --历史日期1
DECLARE v_his_date2 CHAR(8); --历史日期2
DECLARE v_his_date3 CHAR(8); --历史日期3
DECLARE v_his_date4 CHAR(8); --历史日期4
DECLARE v_his_date9 CHAR(8); --历史日期9
DECLARE v_his_date CHAR(8); --历史日期
DECLARE v_his_next CHAR(8); --历史日期的第二天
DECLARE v_his_next9 CHAR(8); --历史日期9的第二天
DECLARE v_table_name VARCHAR(50);
DECLARE v_table_owner VARCHAR(10);
DECLARE v_sql_str VARCHAR(500);
DECLARE v_table_flag INT;
DECLARE v_long_flag INT;
DECLARE v_endc1 INT DEFAULT 0;
DECLARE v_endc2 INT DEFAULT 0;
DECLARE v_sql VARCHAR(6000) DEFAULT ''; -- 动态SQL变量
-----SQL异常处理，插入到错误日志表中
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SELECT SQLSTATE,SQLCODE,MESSAGE_TEXT INTO v_errstate,v_errcode,v_err_txt FROM SYSIBM.SYSDUMMY1;
CALL PETL.P_ETL_DBG_LOG(v_txdate,v_serialno,v_stepnum,v_prcname,v_errcode,v_errstate,v_err_txt,v_sql);
SET v_ret = -1;
END;
---- 变量初始化
SET v_ret = 0;
SET v_thisyyyymmdd =PETL.CALDATE(v_txdate,'D',0);
SET v_nextyyyymmdd =PETL.CALDATE(v_thisyyyymmdd,'D',1);
SET v_lastyyyymmdd =PETL.CALDATE(v_thisyyyymmdd,'D',-1);
SET v_ltmnyyyymmdd =PETL.CALDATE(v_thisyyyymmdd,'A',-1);
SET v_dlldyyyymmdd =PETL.CALDATE(PETL.CALDATE(v_thisyyyymmdd,'A',-1),'L',0);
SET v_thisyymmdd =SUBSTR(v_thisyyyymmdd,3,6);
SET v_thismmdd =SUBSTR(v_thisyyyymmdd,5,4);
SET v_thismm =SUBSTR(v_thisyyyymmdd,5,2);
SET v_thisdd =SUBSTR(v_thisyyyymmdd,7,2);
SET v_thisyyyymm =SUBSTR(v_thisyyyymmdd,1,6);
SET v_dealyyyymm =PETL.CALDATE(v_nextyyyymmdd,'M',-1);
SET v_lastyyyymm =PETL.CALDATE(v_nextyyyymmdd,'M',-2);
SET v_last2yyyymm =PETL.CALDATE(v_nextyyyymmdd,'M',-3);
SET v_last3yyyymm =PETL.CALDATE(v_nextyyyymmdd,'M',-4);
SET v_last12yyyymm =PETL.CALDATE(v_nextyyyymmdd,'M',-13);
SET v_thisyymm =SUBSTR(v_thisyyyymm,3,4);
SET v_dealyymm =SUBSTR(v_dealyyyymm,3,4);
SET v_lastyymm =SUBSTR(v_lastyyyymm,3,4);
SET v_dealmm =SUBSTR(v_dealyyyymm,5,2);
SET v_thisyyyy =SUBSTR(v_thisyyyymmdd,1,4);
SET v_dealyyyy =SUBSTR(v_dealyyyymm,1,4);
SET v_nextyymm =SUBSTR(PETL.CALDATE(v_thisyyyymmdd,'M',1),3,4);
SET v_strtyyyymmdd =RTRIM(PETL.CALDATE(v_thisyyyymmdd,'M',1))||'01';
SET v_endyyyymmdd =PETL.CALDATE(v_strtyyyymmdd,'L',0);
--10天前的历史日期
SET v_his_date1 =PETL.CALDATE(v_thisyyyymmdd,'D',-10);
--1月前的历史日期
SET v_his_date2 =PETL.CALDATE(v_thisyyyymmdd,'A',-1);
--前1月前10天的历史日期
SET v_his_date3 =PETL.CALDATE(PETL.CALDATE(v_thisyyyymmdd,'A',-1),'D',-10);
--前2月前10天的历史日期
SET v_his_date4 =PETL.CALDATE(PETL.CALDATE(v_thisyyyymmdd,'A',-2),'D',-10);
--前3月的历史日期
SET v_his_date9 =PETL.CALDATE(v_thisyyyymmdd,'A',-3);
SET v_his_day1 =SUBSTR(v_his_date1,3,4);
SET v_his_day2 =SUBSTR(v_his_date2,3,4);
SET v_his_day3 =SUBSTR(v_his_date3,3,4);
SET v_his_day4 =SUBSTR(v_his_date4,3,4);
SET v_his_day9 =SUBSTR(v_his_date9,3,4);
SET v_stepnum =0;
-- 正文SQL处理
--UPDATE COMMAND OPTIONS USING C ON(OFF);
--LIST COMMAND OPTIONS
SET v_table_owner='PMID';
--获取需要处理的结果表数量
BEGIN
--查询所有的基表
DECLARE c2 CURSOR WITH HOLD FOR SELECT COUNT(*) FROM SYSIBM.SYSTABLES WHERE CREATOR=v_table_owner AND NAME LIKE 'TB%' AND SUBSTR(NAME,LENGTH(NAME)-3+1,3) IN ('DAY');
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_endc2 = 1;
OPEN c2;
SET v_endc2 = 0;
loop2:
LOOP
FETCH c2 INTO v_rtable;
IF v_endc2=1 THEN LEAVE loop2; END IF;
END LOOP loop2;
CLOSE c2;
END;
--------------------------------loop1 begin-------------------------------
BEGIN
--查询所有的基表
DECLARE c1 CURSOR WITH HOLD FOR SELECT NAME FROM SYSIBM.SYSTABLES WHERE CREATOR=v_table_owner AND NAME LIKE 'TB%' AND SUBSTR(NAME,LENGTH(NAME)-3+1,3) IN ('DAY') ORDER BY NAME;
OPEN c1;
SET v_endc1 = 0;
loop1:
LOOP
FETCH c1 INTO v_table_name;
SET v_endc1=v_endc1+1;
IF v_endc1>v_rtable THEN LEAVE loop1; END IF;
--将各分类历史变量赋值给统一历史数据变量
IF v_table_name IN ('TB_MID_BLL_VC_SUM_DAY','TB_MID_ITC_SUM_DAY') THEN
SET v_his_day = v_his_day1;
SET v_his_date= v_his_date1;
ELSEIF SUBSTR(v_table_name,8,3) IN ('FIN') THEN
SET v_his_day = v_his_day2;
SET v_his_date= v_his_date2;
ELSEIF SUBSTR(v_table_name,8,3) IN ('PAR','GRP','MKT') THEN
SET v_his_day = v_his_day3;
SET v_his_date= v_his_date3;
ELSEIF SUBSTR(v_table_name,8,3) IN ('BLL','ITC') THEN
SET v_his_day = v_his_day4;
SET v_his_date= v_his_date4;
ELSE
SET v_his_day = v_his_day9;
SET v_his_date= v_his_date9;
END IF;
--历史日期的第二天
SET v_his_next = PETL.CALDATE(v_his_date,'D',1);
SET v_his_next9= PETL.CALDATE(v_his_date9,'D',1);
--查询库中是否存在该基表对应的历史月表
SELECT CASE WHEN v_table_name IN
(SELECT SUBSTR(NAME,1,LENGTH(NAME)-4)
FROM SYSIBM.SYSTABLES
WHERE CREATOR=v_table_owner
AND NAME=v_table_name||v_his_day)
THEN 1 ELSE 0 END INTO v_table_flag FROM SYSIBM.SYSDUMMY1;
--查询库中是否存在该基表对应的长历史月表
SELECT CASE WHEN v_table_name IN
(SELECT SUBSTR(NAME,1,LENGTH(NAME)-4)
FROM SYSIBM.SYSTABLES
WHERE CREATOR=v_table_owner
AND NAME=v_table_name||v_his_day9)
THEN 1 ELSE 0 END INTO v_long_flag FROM SYSIBM.SYSDUMMY1;
--如果存在该月表,则删除其数据,否则不处理
IF v_table_flag=1 THEN
--如果该数据为当月最后一天,则不删除该数据(留到后面再删),否则删除
IF SUBSTR(v_his_next,7,2)<>'01' THEN
SET v_sql='DELETE FROM '||v_table_owner||'.'||v_table_name||v_his_day||' WHERE DEAL_DATE='''||v_his_date||'''';
EXECUTE IMMEDIATE v_sql;
-- 记录日志
GET DIAGNOSTICS v_rcount=ROW_COUNT;
SET v_stepnum=v_stepnum+1;
INSERT INTO PETL.ETL_JOB_LOG VALUES(v_txdate,v_serialno,v_stepnum,'D',v_table_owner||'.'||v_table_name||v_his_day||':'||v_his_date,CURRENT TIMESTAMP,v_errcode,v_errstate,v_rcount,0,v_prcname);
--不要用下面语句,因为PETL.P_ETL_JOB_LOG中有COMMIT会关闭游标,出现错误
--CALL PETL.P_ETL_JOB_LOG(v_txdate,v_serialno,v_stepnum,'D',v_table_owner||'.'||v_table_name||v_his_day||':'||v_his_date,v_errcode,v_errstate,v_rcount,v_prcname);
END IF;--SUBSTR(v_his_next,7,2)<>'01'
END IF;--v_table_flag=1
--如果是月最后一天,并且历史保留时间到期,则删除该月最后一天的数据
IF v_long_flag=1 AND SUBSTR(v_his_next9,7,2)='01' THEN
SET v_sql='DELETE FROM '||v_table_owner||'.'||v_table_name||v_his_day9||' WHERE DEAL_DATE='''||v_his_date9||'''';
EXECUTE IMMEDIATE v_sql;
-- 记录日志
GET DIAGNOSTICS v_rcount=ROW_COUNT;
SET v_stepnum=v_stepnum+1;
INSERT INTO PETL.ETL_JOB_LOG VALUES(v_txdate,v_serialno,v_stepnum,'D',v_table_owner||'.'||v_table_name||v_his_day9||':'||v_his_date9,CURRENT TIMESTAMP,v_errcode,v_errstate,v_rcount,0,v_prcname);
END IF;
END LOOP loop1;
CLOSE c1;
END;
--------------------------------loop1 end-------------------------------
COMMIT;
END; |
|
IP卡
狗仔卡
发表于 2008-12-24 14:25
收藏
提升卡
置顶卡
变色卡
显身卡
