Script categories

Thursday, 29 September 2011

pipelined Date function

The requirement for this function is to
list the months ahead of the month supplied as parameters (both  numbers).
The number of months ahead of the month supplied would also
be supplied as a parameter.


create or replace type dtTable as table of date;
/
create or replace function p (pYear       in number
                             ,pMonth      in number
                             ,pMth_Ahead  in pls_integer) 
  return dtTable pipelined as

 l_In_Date date := to_date(to_char(pYear)||to_char(pMonth), 'yyyymm');

begin

  for i in (select add_months(l_In_Date,(level-1)) dt
            from dual
            connect by level <= pMth_Ahead) loop
    pipe row(i.dt);
  end loop;
  return;
end;
/
SQL> select p.column_Value as Month from  table(p(2008,9,6)) p;

MONTH
---------
01-SEP-08
01-OCT-08
01-NOV-08
01-DEC-08
01-JAN-09
01-FEB-09

6 rows selected.

No comments:

Post a Comment