Re: Stored procedure returning records

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 10 Sep 2003 06:30:30 -0700
Message-ID: <2687bb95.0309100530.506b7bc0@posting.google.com>

thorsten.kettner_at_web.de (Thorsten Kettner) wrote in message news:<74a9c367.0309092336.d39b136_at_posting.google.com>...
> Hi all,
>
> I would like to write a stored procedure that returns records and use
> that like I would use a table or view. For example:
>
> select emp_no, dept_no
> from emps_hired_in(2003)
>
> where emps_hired_in is a stored procedure. We have changed recently
> from Ora 7 to 9i, and I am almost sure this was not possible in Ora 7.
> Is it possible with 9i? If so, how?

Thorsten, A reference cursor introduced with which ever version 7.2 or 7.3 that introduced pl/sql 2.2 can return a row set.

The following examples are taken from past editions of Oracle documentation from 7.2 and 7.3. I believe that the SQLPlus example came from the README file located in the $ORACLE_HOME directory.

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;

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

HTH -- Mark D Powell -- Received on Wed Sep 10 2003 - 08:30:30 CDT