OraFAQ Forum: SQL & PL/SQL » Please help with a cursor function

Home » SQL & PL/SQL » SQL & PL/SQL » Please help with a cursor function

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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.

----------------------------------------------------------------------

Report message to a moderator

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;
/

Report message to a moderator

Previous Topic:	get database procedures
Next Topic:	very much a newbe, from Visual basic

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 23 07:40:09 CDT 2025