Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: HELP returning rows from stored proc
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 );
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 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 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 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;
http://govt.us.oracle.com/ -- downloadable utilities