DB2中循环日期跑数据

OR REPLACE PROCEDURE DB2USER.RUN_PROCEDURE_BY_LOOP(IN begin_date VARCHAR(8),IN end_date VARCHAR(8)) /****************************************************************************** NAME: PURPOSE: REVISIONS: Ver Date Author Description --------- ---------- ------------ ------------------------------------ 1 20150825 ZEN 循环跑多天的数据 ******************************************************************************/ LANGUAGE SQL BEGIN DECLARE V_LOCATION VARCHAR(100); DECLARE V_START_TIME TIMESTAMP; DECLARE V_END_TIME TIMESTAMP; DECLARE V_SQLMSG VARCHAR(255); DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS EXCEPTION 1 V_SQLMSG = MESSAGE_TEXT; CALL DB2USER.PRO_LOG(TO_CHAR(V_START_TIME,‘YYYYMMDD‘),‘RUN_PROCEDURE_BY_LOOP‘,‘循环跑多天的数据‘,V_START_TIME,current timestamp,‘ERROR‘,V_LOCATION,V_SQLMSG); END; /*清空目标表*/ SET V_START_TIME = TO_DATE(begin_date,‘YYYYMMDD‘); SET V_END_TIME = TO_DATE(end_date,‘YYYYMMDD‘); SET V_LOCATION = ‘清空数据‘; time_loop: LOOP INSERT INTO Temp_TEST VALUES(V_START_TIME,TO_CHAR(V_START_TIME,‘YYYYMMDD‘)); --CALL DB2USER.PRO_S_CLM_PFM_DAY(TO_CHAR(V_START_TIME,‘YYYYMMDD‘)); SET V_START_TIME = V_START_TIME + 1 day ; IF (V_START_TIME > V_END_TIME) THEN LEAVE time_loop; END IF; END LOOP time_loop; COMMIT; --CALL DB2USER.PRO_LOG(TO_CHAR(V_START_TIME,‘YYYYMMDD‘),‘RUN_PROCEDURE_BY_LOOP‘,‘循环跑多天的数据‘,V_START_TIME,current timestamp,‘SUCCESS‘,‘‘,‘‘); END;

3.总结：

a.DB2中可以直接判断退出循环，如果被oracle的for循环惯坏了，想到这一点不太容易。

b.直接标签loop循环，设置退出方式，简单实用。

DB2中循环日期跑数据

标签：