Re: how to write a function that returns a dataset

RBTMEKRDQKDL_at_spammotel.com wrote:
> Hello,
>
> I'm quite new to oracle and probably there is no way to do what I want
> to do.
>
> I want to write a function that gets one input parameter and returns a
> dataset.
>
> Until now I have written a function that returns the date of the
> easter Sunday (one value / see at th bottom of the posting).
>
> THIS one I can query as follows:
> SELECT geteastersunday(2007) FROM DUAL
>
> Now I want to write another function that uses this one to calculate
> all banking holidays that depend on this date and return it as a
> recordset.
>
> Probably my attempt is totally wrong an I have to tackle this problem
> on a completely different approach.
>
> I'm open to any suggestions.
>
> Please excuse my bad English, I'm not a native speaker ;-)
>
> Thanks in advance
> Ray
>
>
>
> -------------------------------------------------------------------------------
> CREATE OR REPLACE FUNCTION geteastersunday (input IN INTEGER)
> RETURN DATE
> IS
> yaer INTEGER;
> a INTEGER;
> b INTEGER;
> c INTEGER;
> d INTEGER;
> e INTEGER;
> f INTEGER;
>
> BEGIN
> yaer := input;
>
> IF yaer = 0
> THEN
> yaer := TO_CHAR (SYSDATE, 'yyyy');
> END IF;
>
> a := yaer MOD 19;
> b := yaer / 100;
> c := (8 * b + 13) / 25 - 2;
> d := b - (yaer / 400) - 2;
> e := (19 * (yaer MOD 19) + ((15 - c + d) MOD 30)) MOD 30;
>
> IF e = 28
> THEN
> IF a > 10
> THEN
> e := 27;
> END IF;
> ELSIF e = 29
> THEN
> e := 28;
> END IF;
>
> f := (d + 6 * e + 2 * (yaer MOD 4) + 4 * (yaer MOD 7) + 6) MOD 7 +e
> + 21;
>
> RETURN TO_DATE('' || yaer || '0301', 'YYYYMMDD') + f;
>
> EXCEPTION
> WHEN NO_DATA_FOUND
> THEN
> NULL;
> WHEN OTHERS
> THEN
> RAISE;
> END geteastersunday;
> /

Multiple ways to do it: Try these links for starters: http://www.psoug.org/reference/pipelined.html http://www.psoug.org/reference/ref_cursors.html

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org