Re: Stored procedures (HELP ME !!!)
Date: 1996/07/23
Message-ID: <31f425e7.4912223_at_dcsun4>
On 22 Jul 1996 17:18:52 GMT, surman_at_dlsun338.us.oracle.com (Scott Urman) wrote:
>In article <4sv30u$jm_at_client2.news.psi.net>, dmausner_at_brauntech.com (Dave Mausner) writes:
>|> In article <31F0CC9F.9D_at_worlnet.fr>,
>|> Jean-Marc ARZOUMANIAN <jmarzou_at_worlnet.fr> wrote:
>|> >Q: Does anyone know how to write an Oracle 7.x stored procedure that returns
>|> > a multiple result set ?
>|>
>|>
>|> A: nobody knows how to do this in Oracle7 because there is no such feature.
>|> best regards.
>
>Sure there is. Cursor variables were introduced in 7.2 and enhanced in Oracle
>7.3. Check out the PL/SQL, OCI and precompiler manuals.
Here is an example from 7.2:
pl-sql code:
create or replace package cv_types
as
cursor c1 is select emp.ename, emp.mgr, emp.deptno, dept.dname, dept.loc
from emp, dept;
type EmpCurType is ref cursor return C1%rowtype;
end;
/
l
show errors
create or replace procedure get_emp_data
( p_ename in varchar2 default NULL, p_mgr in number default NULL, p_dept in number default NULL, p_cursor in out cv_types.EmpCurType )
as
begin
if ( p_ename is not null ) then
open p_cursor for
select emp.ename, emp.mgr, emp.deptno, dept.dname, dept.loc
from emp, dept
where emp.deptno = dept.deptno
and emp.ename like upper( p_ename );
elsif ( p_mgr is not null ) then
open p_cursor for
select emp.ename, emp.mgr, emp.deptno, dept.dname, dept.loc
from emp, dept
where emp.deptno = dept.deptno
and emp.mgr = p_mgr;
elsif ( p_dept is not null ) then
open p_cursor for
select emp.ename, emp.mgr, emp.deptno, dept.dname, dept.loc
from emp, dept
where emp.deptno = dept.deptno
and dept.deptno = p_dept;
else
open p_cursor for
select emp.ename, emp.mgr, emp.deptno, dept.dname, dept.loc
from emp, dept
where emp.deptno = dept.deptno;
end if;
end;
/
PRO*C code:
{
EXEC SQL BEGIN DECLARE SECTION;
typedef char asciiz;
EXEC SQL TYPE asciiz IS STRING(100);
SQL_CURSOR my_cursor;
asciiz ename[40];
int mgr;
short mgr_i;
int deptno;
asciiz dname[50];
asciiz loc[50];
int i;
EXEC SQL END DECLARE SECTION; EXEC SQL WHENEVER SQLERROR DO sqlerror_hard(); EXEC SQL ALLOCATE :my_cursor;
for( i = 0; i < 4; i++ )
{
EXEC SQL EXECUTE
BEGIN
if :i = 0 then
get_emp_data( p_ename=>'K%', p_cursor=>:my_cursor );
elsif :i = 1 then
get_emp_data( p_mgr=>7698, p_cursor=>:my_cursor );
elsif :i = 2 then
get_emp_data( p_dept=>10, p_cursor=>:my_cursor );
elsif :i = 3 then
get_emp_data( p_cursor=>:my_cursor );
end if;
END;
END-EXEC;
printf( "Processing Iteration %d\n", i );
for( ;; )
{
EXEC SQL WHENEVER NOTFOUND DO BREAK;
EXEC SQL FETCH :my_cursor
INTO :ename, :mgr:mgr_i, :deptno, :dname, :loc;
printf( "%s, %d, %d, %s, %s\n",
ename, mgr_i?-1:mgr, deptno, dname, loc );
}
EXEC SQL CLOSE :my_cursor;
}
}
>
>
>|>
>|> --
>|> Dave Mausner, Consulting Manager, Braun Technology Group, Chicago.
>------------------------------------------------------------------------
>Scott Urman Oracle Corporation surman_at_us.oracle.com
>------------------------------------------------------------------------
>Author of _Oracle PL/SQL Programming_ ISBN 0-07-882176-2
>Published by Oracle Press - http://www.osborne.com/oracle/index.htm
>------------------------------------------------------------------------
>"The opinions expressed here are my own, and are not necessarily that of
> Oracle Corporation"
>------------------------------------------------------------------------
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database
statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Tue Jul 23 1996 - 00:00:00 CEST