| Please help with a cursor function [message #30] |
Fri, 04 January 2002 12:07  |
Jordi Lopez
Messages: 1
Registered: January 2002
|
Junior Member |
|
|
Hello. I have to make a pl/sql function to return a list of integers, but I do not know how to do this. Can you help me?
My function is:
create or replace function badYears
return integer is
cursor cursorResult is select year from behaviors group by year having avg(points)<5;
yearFetch integer;
begin
open cursorResult;
loop
fetch cursorResult into yearFetch;
exit when cursorResult%NOTFOUND;
/* WHAT GOES HERE? */
end loop;
close cursorResult;
end;
/
What I have to write before the "end loop"? In
Informix I remember it was "return...with resume", but I am a newbie in Oracle and I am very confused.
Thanks.
----------------------------------------------------------------------
|
|
|
|
| Re: Please help with a cursor function [message #33 is a reply to message #30] |
Sat, 05 January 2002 05:03  |
Suresh Vemulapalli
Messages: 624
Registered: August 2000
|
Senior Member |
|
|
function you are create return one value per call.
If you want to return more than value from function consider return type as REF CURSOR or PL/SQL tables.
Note : ref cusrors and pl/sql tables should be declared in package before passing as parameters.
Example:
create or replace package x1 IS
type r1 is ref cursor;
end;
/
create or replace function badYears
return x1.r1 is
ref1 x1.r1;
begin
open ref1 for 'select year from behaviors group by year having avg(points)<5';
return ref1;
end;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
