Re: parameterized views

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 11 Aug 2004 11:28:18 -0500
Message-ID: <u4qn97idd.fsf@standardandpoors.com>

On Wed, 11 Aug 2004, ernest_at_gesora.com wrote:
> Galen,
>
> Let's forget everything was discussed in this thread.
> Let's say I present you my new problem:

> I want to create a function/procedure that takes some
> parameters, make some processing and finally return a
> resultset/cursor/table to the client(ADO). My problem is that
> I don't know how to return the resultset. In the documentation,
> all of the samples of procedures/functions show how to
> insert/delete/update records but they do not show how to return
> resultsets; at least I wasn't able to find samples like this.
>
> I don't know what to declare as return type for a function
> returning a resultset. Should I use a TableType ? a cursor ? a
> generic cursor ? a object/row type with pipelined ?

Use a cursor.

> If you want me be more specific, consider I want to return a
> resultset having the following structure : ID Number(16,0)
> FirstName varchar2(30) LastName varchar2(30)
>
> Help me to create a function like this:
>
> Create function GetMyData(prm varchar2) return <??????>
> AS
> BEGIN
> -- doing some things
>
> /* show me how to return the result of the following command to
> the client : SELECT ID, FirstName, LastName From MyTable Where
> FirstName Like prm */

Okay,

Try this untested code:

   TYPE r_tbl IS RECORD (

      a_id           MyTable.id%TYPE,
      a_firstName    MyTable.FirstName%TYPE,
      a_lastName     MyTable.LastName%TYPE

   );

   TYPE c_tbl IS REF CURSOR

      RETURN r_tbl;

   FUNCTION GetMyData (prm IN varchar2)

      RETURN c_tbl
   AS

      v_tbl c_tbl;
   BEGIN

      OPEN v_tbl FOR
         SELECT  ID         a_id
                ,FirstName  a_firstName
                ,LastName   a_lastName
           FROM MyTable
          WHERE FirstName like prm
          ;
      RETURN v_tbl;

   END GetMyData;

-- 
Galen Boyer

Received on Wed Aug 11 2004 - 11:28:18 CDT