Thursday, August 25, 2011

Oracle - Iterating Over Dates In For Loop

For loop in Oracle does not offer the possibility to use DATE data-type in iteration. It requires NUMBER data-type. The below Oracle procedure can be use to manipulate iteration through dates.

CREATE PROCEDURE DATE_ITERATION(FIRST_DATE IN VARCHAR2,
                                LAST_DATE  IN VARCHAR2) AS
  TEMP NUMBER;
  NUMB NUMBER;
BEGIN
 
  SELECT (TO_DATE(LAST_DATE, 'YYYY-MM-DD') -
         TO_DATE(FIRST_DATE, 'YYYY-MM-DD'))
    INTO TEMP
    FROM DUAL;
  
  FOR NUMB IN 0 .. TEMP LOOP
    DBMS_OUTPUT.put_line(TO_DATE(FIRST_DATE, 'YYYY-MM-DD') + NUMB);
  END LOOP;
 
END DATE_ITERATION;

Invocation of procedure is done as below.

CALL DATE_ITERATION('2011-06-01','2011-07-01')

The output produced.

01-GIU-11
02-GIU-11
03-GIU-11
.
.
.
30-GIU-11
01-LUG-11

No comments:

Post a Comment