Home » SQL & PL/SQL » SQL & PL/SQL » Please help with a cursor function
Please help with a cursor function [message #30] Fri, 04 January 2002 12:07 Go to next message
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 Go to previous message
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;
/
Previous Topic: get database procedures
Next Topic: very much a newbe, from Visual basic
Goto Forum:
  


Current Time: Fri Aug 14 09:59:06 CDT 2020