Re: PRO*C or Stored procedure, which one faster ?
Date: Sun, 24 Jan 1999 16:05:55 GMT
Message-ID: <36ae4286.5526066_at_192.86.155.100>
A copy of this was sent to Michael Krolewski <vandra_at_u.washington.edu> (if that email address didn't require changing) On Sat, 23 Jan 1999 20:46:25 -0800, you wrote:
>Generally the stored procedures are the fastest --they use the internals
>of Oracle, etc. There are 2 (or more??) significant drawback to stored
>procedures. First the data is difficult to get out of the database and
>into other programs. Obviously there are mechanism, but it is harder.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
not trying to start an argument or anything but why do you say this? Lets say i have 7.2 or up and I wanted to return lots of data from an sp to a client program (say pro*c). It might look like this:
create or replace package my_pkg
as
type cursorType is ref cursor;
procedure do_something( p_refCur in out cursorType );
end;
/
create or replace package body my_pkg
as
procedure do_something( p_refCur in out cursorType )
as
begin
... lots of procedural code here ... if ( some_condition ) then
open p_refcur for select ename, empno from emp order by ename; elsif ( some_other_condition ) then
open p_refcur for select ename, empno from emp where ...; else
open p_refcur for select ename, empno from emp where someThingElse...;
end if;
end;
end my_pkg;
/
and the Pro*c to use this would look like:
static void process()
{
EXEC SQL BEGIN DECLARE SECTION;
SQL_CURSOR my_cursor;
VARCHAR ename[40];
int empno;
EXEC SQL END DECLARE SECTION; EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL ALLOCATE :my_cursor;
EXEC SQL EXECUTE BEGIN
my_pkg.do_something( :my_cursor );
END; END-EXEC;
for( ;; )
{
EXEC SQL WHENEVER NOTFOUND DO break;
EXEC SQL FETCH :my_cursor INTO :ename, empno;
printf( "'%.*s', %d\n", ename.len, ename.arr, empno );
}
EXEC SQL CLOSE :my_cursor;
}
Thats as easy (if not easier) then putting the EXEC SQL DECLARE C CURSOR FOR SELECT .... in the pro*c application itself. In fact, if you tried to do what we did in the stored procedure (open a different cursor based on some conditions) the pro*c code would start to get downright ugly (enter dynamic sql)....
>The second is the level of "security" on your system. Often the dba does
>not allow the introduction and modifications of SP without review,
>validation, etc. If you are doing ad hoc or prototype or rapid
>development work, this would be a problem.
>
then, imho, the dba has overstepped their bounds and is actually hampering their ability to manage the database (they've made the 100% WRONG decision on what to 'secure'). Whats different about some pl/sql procedure in a developers account then having gobs of pro*c they cannot control outside the database? they should encourage people to use sp's, not discourage it. at least if its a stored proc, they can manage the dependencies (what if I change this table -- what pieces of code will be affected). They can read the code if they believe it to be performing poorly (wouldn't a dba rather read pl/sql then C code? I as a C programmer and pl/sql programmer would rather read pl/sql and try to tune that)
>ProC has advantages on manipulation of data, more complete procedural
>constructs, etc.
>
>Mike Krolewski
>
>
>samshi wrote:
>
>> Hi ,
>>
>> Not considering the Client side , just talking in a huge central
>> database env .
>>
>> thanks in advance .
>>
>> Sam
>
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
-- http://govt.us.oracle.com/ -- downloadable utilities ---------------------------------------------------------------------------- Opinions are mine and do not necessarily reflect those of Oracle Corporation Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it.Received on Sun Jan 24 1999 - 17:05:55 CET