Re: Can a stored procedure or function return a recordset

From: <jeanch_at_my-deja.com>
Date: 2000/07/14
Message-ID: <8kmt7u$5o0$1@nnrp1.deja.com>#1/1

In article <8klhei$gkj$1_at_acs2.byu.edu>,
  "Jake Anderson" <jakeanderson_at_byu.edu> wrote:
> I know this is a dumb question but I am kind of new at oracle. I am
 almost
> certain that it is possible but I can't seem to do it.
> How would I get a function or procedure to return a recordset.
In the package speficication have
...

    TYPE t_sub IS RECORD (

        id	TAB.id%TYPE,
        name	TAB.name%TYPE,
        surname	NUMBER(2)    );

    TYPE t_subsystems IS REF CURSOR RETURN t_sub;

In package body do
...

   FUNCTION myFunct(ID IN TAB.ID%TYPE) RETURN t_subsystems IS

        results t_subsystems;
    BEGIN

        OPEN results FOR
        SELECT id, name FROM TAB
        WHERE id = ID;
        -- If selected - return it!

        RETURN results;
	END myFunct;

I just want
> it to return the results of a select query. But I can't use a view
 because
> it has parameters. Unless you can do parameters in a view. Which I
 also
> wouldn't know how to do. Thanks for the help.
You can also attach functions to a view like this CREATE OR REPLACE VIEW my_V ( ID,
NAME) AS SELECT (pkg_name.myFunct(V.ID),V.NAME FROM TAB V;

.....
let us know if you want to know more on View+function implementation

Cheers
JC

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Jul 14 2000 - 00:00:00 CDT