Re: How to create Stored Procedure to return rows
Date: 26 Mar 2001 14:15:26 GMT
Message-ID: <99nitu$d29$1_at_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) ISBEGIN
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) ISBEGIN
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;
- 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 [Quoted] NewsOne.Net prohibits users from posting spam. If this or other posts made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net