Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: [Q] Multiple Rows ret. by StoredProc
On Mon, 28 Jul 1997 04:18:54 -0400, Vladislav <vlpa_at_daleen.com> wrote:
>Hello,
>A simple question:
>Can Oracle's stored proc return multiple rows at a time.
>We will be using RW DBtools.h++ to do that to execute
>a stored proc from our C++ code.
>
>Thanks in advance,
>Vladislav
>
>Please cc the answers to vlpa_at_daleen.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). The cursor variables are opened with the privelegs 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