Re: Oracle Stored Procedures and result sets

In article <8dk9fe$f7i$1_at_news2.isis.unc.edu>,   "Ray Porter" <ray_porter_at_unc.edu> wrote:
> Hi,
> I'm in the process of migrating several moderately large applications
 from
> Sybase to Oracle. I have two questions:
>
> 1) How do you create a stored procedure in Oracle to return a result
 set?
> In Sybase, all you have to do is:
>
> create proc myProcName (@parm1 char(9)) as
> select * from myTablename
> where myField = @parm1
> go
>

create function myProc(p IN varchar2)
return cursor
as
begin

   cursor c is select * from myTablename    where myField = p;

   return c;
end;

I strongly recommend you use package to wrapp your code in:

CREATE OR REPLACE PACKAGE myPckg AS
TYPE t_cursor IS REF CURSOR;
function myProc(p IN varchar2) return t_cursor; END myPckg;

CREATE OR REPLACE PACKAGE BODY myPckg AS function myProc(p IN varchar2) return t_cursor is cur t_cursor;
begin
OPEN cur as select * from myTab where myField = p; return cur;
end;
END myPckg;

> I know it's not that simple in Oracle but I need a clear example of
 how to
> do it.
>
> 2) Is there any performance gain from using stored procedures in
 Oracle to
> retrieve read-only data? Our experience with Sybase has been that a
 stored
> procedure gains us a performance boost on the order of a factor of 10-
 20
> over SQL embedded in an application. If there is no similar
 performance
> gain in Oracle, is there any other reason to use a stored procedure
 in this
> manner (other than the obvious security benefits)?
>
> Any help will be greatly appreciated,
> ================================================
> Ray Porter
> Applications Analyst Programmer
> Administrative Information Services, UNC-CH
> Phone: 966-5878
> email: ray_porter_at_unc.edu
> dragon_at_email.unc.edu
> Home Page: http://www.unc.edu/~dragon/
>
> "Meddle not in the affairs of dragons,
> for thou art crunchy and taste good with ketchup."
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Apr 19 2000 - 00:00:00 CDT