Re: PL/SQL Stored Functions Question

From: <markp7832_at_my-deja.com>
Date: Mon, 01 Nov 1999 17:30:03 GMT
Message-ID: <7vkiml$ah2$1@nnrp1.deja.com>

In article <Pine.GSO.4.10.9911010831410.16513- 100000_at_sawasdee.cc.columbia.edu>,
  Ilya Gaysinskiy <isg8_at_columbia.edu> wrote:
> Hi, all.
>
> I am new to Oracle and having installed it on my Linux server I am
pretty
> happy with it so far. However, I still can't figure out how to make a
> stored function that would return a SQL query result (I guess that
would
> be a cursor). Can anyone post a small example of such a function? For
> example, I want a function which recieves one parameter:
> soc_sec_num and returns the following query result:
>
> select lname, fname from employee where ssn=soc_sec_num
>

I think what you want is known as a Reference Cursor; here is what Oracle had to say about them in the 7.2 SQLPlus README. Note - this is taken from a e-mail to our development team.

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.

--
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 Mon Nov 01 1999 - 11:30:03 CST