Re: Oracle 7.2 Stored Procedures and Result Sets

From: Thomas J Kyte <tkyte_at_us.oracle.com>
Date: 1996/05/13
Message-ID: <31968c45.1886993_at_dcsun4>#1/1

gary.schneir_at_sandiegoca.ncr.com (Gary Schneir) wrote:

>
>
>
>I heard some conflicting information regarding which version of Oracle
>would support Result sets in Stored Procedures.
>
>For example I could say something like
>
> select s,b,c from table_xyz
>
>without the "into " clause. Then when I run the SP, it would return
>all records. Sybase and MS SQLServer have supported something like
>this for a while and depending on who I talk to, I hear Oracle 7.2,
>7.3 or 8.0 will support this type of feature. Can anyone shed any
>light on this.
>
>
>
>I hope someone will be able to give me some accurate information and
>maybe an example or two if it is possible in 7.2
>
>

It is version 7.2 that supports cursor variables. The stored procedure/function opens a cursor that is fetched from by some other process. The following example shows a stored procedure that depending on the inputs to it, will return an open cursor with different where clauses. The pro*c program simply tries every way of calling the procedure to show you the different results. This was done with Oracle 7.2.2....

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;
/

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;
/

And the PRO*C program (partial) ....

static void process()
{
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;
	}

}

>
>Thank you,
>
>
>Gary Schneir
>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government

---

opinions and statements are mine and do not necessarily reflect the opinions of Oracle Corporation. Received on Mon May 13 1996 - 00:00:00 CEST