Monday, March 27, 2006

Passing a select as Cursor to a Function

create or replace function CursorSum(cur SYS_REFCURSOR) return number
AS
ln_int NUMBER;
ln_sum NUMBER := 0;
BEGIN
loop
fetch cur into ln_int;
exit when cur%NOTFOUND;
ln_sum := ln_sum + ln_int;
end loop;
return ln_sum;
END;
/

select CursorSum(Cursor(select 10 from dual union all select 20 from dual)) as sum
from dual

SUM
-----
30

No comments: