Re: How to create Stored Procedure to return rows

From: <xmark.powell_at_eds.com.x>
Date: 26 Mar 2001 14:15:26 GMT
Message-ID: <99nitu$d29$1@news.netmar.com>

In article <3abd6a58_at_newsgate.imsbiz.com>, Bill <haha666_at_writeme.com> writes:
>Dear All,
>
>I'm a newbie in Oracle and want to create a stored procedure to return rows
>just as "Select * from equipment".
>
>Could anyone kindly give me the sample script or for it?
>
>Do I need to use PL/SQL and PACKAGE?
>
>Regards,
>
>Bill
>kwlau_at_writeme.com
>
>

Any time you talk about database stored code you are talking about pl/sql or java. A stored procedure using scalar out or in/out variables can return only one row at a time. To return a complete row set requires the use of a reference cursor or if the row set is not too large returning the columns in pl/sql tables (single dimension arrays). You can find examples in the pl/sql manual. Your ability to use these techniques depend on the tool you are using on the front-end as not all third-party tools support reference cursors.

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

• Mark D. Powell --
  • Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web ----- http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups NewsOne.Net prohibits users from posting spam. If this or other posts made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net

Received on Mon Mar 26 2001 - 08:15:26 CST