Re: Can you pass a cursor into a procedure?

From: <markp7832_at_my-deja.com>
Date: Wed, 29 Mar 2000 20:59:12 GMT
Message-ID: <8btqql$orn$1@nnrp1.deja.com>

Sean, what you want to do, i.e., pass a cursor into and out of stored procedures requires using a reference cursor. Here is something I passed out to my developers.

Cursor Variables

Another topic available earlier but not mentioned in the DBA Upgrade document for version 7.2 or 7.3 is the Pl/sql cursor variable.

It has been possible since pl/sql version 2.2 to declare a cursor and pass it as a variable. In the past where several applications needed to execute the same SQL it could be coded into a stored procedure, but then the manipulation of the cursor rows had to be done in the stored code. If several different programs needed access to cursor rows then the SQL had to be coded into each program. Using cursor variables can eliminate the need to duplicate the cursor in multiple programs.

Example code taken from the Application Developer’s Guide:

CREATE OR REPLACE PACKAGE emp_data AS

TYPE emp_val_cv_type IS REF CURSOR RETURN emp%ROWTYPE;

  PROCEDURE open_emp_cv (emp_cv      IN OUT emp_val_cv_type,
                         dept_number     IN INTEGER);
  PROCEDURE fetch_emp_data (emp_cv       IN emp_val_cv_type,
                            emp_row     OUT emp%ROWTYPE);

END emp_data;

CREATE OR REPLACE PACKAGE BODY emp_data AS

  PROCEDURE open_emp_cv (emp_cv      IN OUT emp_val_cv_type,
                         dept_number     IN INTEGER) IS

BEGIN
OPEN emp_cv FOR SELECT * FROM emp WHERE deptno = dept_number; END open_emp_cv;

  PROCEDURE fetch_emp_data (emp_cv       IN emp_val_cv_type,
                            emp_row     OUT emp%ROWTYPE) IS

  BEGIN
    FETCH emp_cv INTO emp_row;
  END fetch_emp_data;
END emp_data;

You can then reference the cursor using code similar to the code on the next page.

DECLARE
-- declare a cursor variable
emp_curs emp_data.emp_val_cv_type;

dept_number dept.deptno%TYPE;
emp_row emp%ROWTYPE;

BEGIN
dept_number := 20;

open the cursor using a variable emp_data.open_emp_cv(emp_curs, dept_number);
fetch the data and display it LOOP emp_data.fetch_emp_data(emp_curs, emp_row); EXIT WHEN emp_curs%NOTFOUND; DBMS_OUTPUT.PUT(emp_row.ename || ' '); DBMS_OUTPUT.PUT_LINE(emp_row.sal); END LOOP; END;

Reference cursors are also available in sql*plus.

The VARIABLE command now has a REFCURSOR clause.

New syntax: VAR[IABLE] [variable {NUMBER|CHAR|CHAR (n)|VARCHAR2 (n) |

                   REFCURSOR}]

The REFCURSOR clause creates a variable of type REFCURSOR.

Purpose:

SQL*Plus REFCURSOR bind variables may be used to reference PL/SQL 2.2 Cursor Variables allowing PL/SQL output to be formatted by SQL*Plus. Refer to the PL/SQL 2.2 documentation for more information about PL/SQL REF CURSOR cursor variables.

create or replace package body name_pck is

      procedure get_ednames (maxdeptno in number, a in out ecurtype) as
        begin
        open a for
          select   ename, dname
            from   emp, dept
           where   emp.deptno = dept.deptno
             and   emp.deptno = maxdeptno
          order by ename;
        end;

end;
/

    SQL> variable b refcursor;
    SQL>
    SQL> column ename heading Name
    SQL> column dname heading Department
    SQL>
    SQL> execute name_pck.get_ednames(30, :b)

       PL/SQL procedure successfully completed.


       Name        Department
       ---------- --------------
       ALLEN       SALES
       BLAKE       SALES
       JAMES       SALES
       MARTIN      SALES
       TURNER      SALES
       WARD        SALES

Remember reference cursors are not new and are available with pl/sql 2.2.

In article <8btpb7$n2j$1_at_nnrp1.deja.com>, Sharkie <sharkie2_at_my-deja.com> wrote:
> Please post a sample code, or a URL to where this code can be seen.
>
> In article <8bthq5$e05$1_at_nnrp1.deja.com>,
> Sean <dolans_at_my-deja.com> wrote:
> > Pardon me if this is a dumb question, but I can't figure out how to
do
> > this. I have a PL/SQL package that has 4 defined cursors : each
> > returns two columns but are based on a lot of different WHERE, IN,
and
> > EXISTS clauses.
> >
> > I would like to base which cursor is used based on the parameters
that
> > are passed into the calling main procedure. No problem. I can get
> > what cursor to use. I can get the results and they successfully are
> > inputted into a PL/SQL table. What I would like to do is make a
> > procudure/function that I can just pass which cursor to use in, let
it
> > find the answers, and then put the results into the PL/SQL table
> > structure.
> >
> > Am I going about this wrong? I'd appreciate any help.
> > Thanks,
> > Sean
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
> --
> If the human brain were so simple
> that we could understand it,
> we would be so simple we couldn't.
> -Makes Sense... don't it?
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

--
Mark D. Powell -- The only advice that counts is the advice that you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Mar 29 2000 - 14:59:12 CST