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
>
> I know it's not that simple in Oracle but I need a clear example of
 how to
> do it.
>

its pretty much that simple.

ultimately it'll boil down to something like:

create or replace function sp_ListEmp return types.cursortype as

    l_cursor types.cursorType;
begin

    open l_cursor for select ename, empno from emp order by ename;     return l_cursor;
end;
/

See http://osi.oracle.com/~tkyte/ResultSets/index.html for more info.

> 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)?
>

The reason it goes faster as a stored proc in Sybase is because they do not have the concept of a SHARED POOL like we do. The first time we see a query/procedure we parse it and cache it for the next person. The actual parsing/optimization of a query may take upto 90% or more the total execution time of the query. By caching these plans, we can in some cases realize an order of magnitude or more increase in performance (now, if the query is scanning 1,000,000,000 rows -- it'll not be as great but for indexed reads and such, its huge).

In sybase the only way to achive anything similar is to put the static queries into a stored procedure -- they will compile a query into a plan if you put it in a stored procedure.

In general, the performance of a ref cursor opened in PLSQL and a cursor opened in the client will be the same. The mechanics are identical. I would keep the logic in the database however if at all possible for the simple reason that bug fixing and tuning is infinitely easier if you can fix the code in the database and avoid having to fix a 3gl client (and then redistribute and make sure everyone has the same version and so on).

> 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."
>
>

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.