Re: HELP returning rows from stored proc
Date: 1997/07/16
Message-ID: <33cf0746.15488401_at_newshost>#1/1
On 16 Jul 1997 14:31:45 GMT, Marvin Frederickson <mfrederickson_at_kpmg.com> wrote:
>I'm trying to write a stored procedure to return rows, but not having
>any luck.
>
>In SQL Server, the rows from the last SELECT stmt in the stored proc is
>returned as data from the stored procedure. How do I do this in Oracle?
>
>Thanks.
>mfrederickson_at_kpmg.com
With 7.2 and 7.3 you have cursor variables. Cursor variables are cursors opened by a pl/sql routine and fetched from by another application or pl/sql routine (in 7.3 pl/sql routines can fetch from cursor variables as well as open them and support for 'weakly' typed cursors was added. this example uses a strongly typed cursor). The cursor variables are opened with the priveleges of the owner of the procedure and behave just like they were completely contained within the pl/sql routine. It uses the inputs to decide what database it will run a query on.
Here is a 7.2 / 7.3 example in SQL*Plus and PRO*C
create or replace package empCurType
as
cursor c1 is select ename, mgr, dept.deptno, dname, loc from emp, dept;
type empCur is ref cursor return c1%rowtype;
procedure open_cursor( p_cursor in out empCur, p_deptno in number );
end;
/
show errors
create or replace package body empCurType as
procedure open_cursor( p_cursor in out empcur, p_deptno in number )
is
begin
if ( p_deptno in ( 10, 20 ) ) then -- open local query open p_cursor for select ename, mgr, dept.deptno, dname, loc from emp, dept where emp.deptno = dept.deptno and dept.deptno = p_deptno; else open p_cursor for select ename, mgr, dept.deptno, dname, loc from emp_at_aria.world, dept_at_aria.world where emp.deptno = dept.deptno and dept.deptno = p_deptno; end if;
end open_cursor;
end empCurType;
/
show errors
REM --------------- SQL*Plus using RefCursor to display results ------
variable C refcursor
exec empCurType.open_cursor( :C, 10 )
print C
REM ----------- PRO*C Getting the cursor ------------------REM
REM And then the pro*c would look like 'normal' pro*c code except that the EXEC REM SQL OPEN Cursor statement would now be a pl/sql call as follows: REM
REM
REM static void process()
REM {
REM EXEC SQL BEGIN DECLARE SECTION;
REM typedef char asciiz; REM EXEC SQL TYPE asciiz IS STRING(100); REM SQL_CURSOR my_cursor; REM asciiz ename[40]; REM int mgr; REM short mgr_i; REM int deptno; REM asciiz dname[50]; REM asciiz loc[50]; REM int i;
REM EXEC SQL END DECLARE SECTION;
REM
REM EXEC SQL WHENEVER SQLERROR DO sqlerror_hard(); REM
REM
REM EXEC SQL ALLOCATE :my_cursor;
REM
REM EXEC SQL EXECUTE BEGIN empCurType.open_cursor( :my_cursor, 10 ); end; REM END-EXEC; REM REM for( ;; ) REM { REM EXEC SQL WHENEVER NOTFOUND DO BREAK; REM EXEC SQL FETCH :my_cursor REM INTO :ename, :mgr:mgr_i, :deptno, :dname, :loc; REM REM printf( "%s, %d, %d, %s, %s\n", REM ename, mgr_i?-1:mgr, deptno, dname, loc ); REM } REM EXEC SQL CLOSE :my_cursor;
REM } Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Jul 16 1997 - 00:00:00 CEST