DB2中循环日期跑数据

1.数据库版本：

2.具体实现方式：

CREATE 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循环，设置退出方式，简单实用。

转载于:https://www.cnblogs.com/Alex-Zeng/p/4758784.html