Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: looping and binding
Just take your cursors outside those loops and use parameters like
cursor <cursorname> (p_1 in number, p_2 varchar2) etc.
and
open cursor(var1, var2)
or
for <rec> in <cursor>(var1, var2)
Hth,
Sybrand Bakker, Oracle DBA
"Phil R Lawrence" <prlawrence_at_lehigh.edu> wrote in message
news:8qggj2$kio_at_fidoii.CC.Lehigh.EDU...
> I learned database prgramming with Perl and DBI::Oracle. Now I am trying
to
> do some things in straight PL/SQL (and it seems to run a LOT faster!)
>
> The DBI documentation taught me to declare my cursors outside of any loops
> using bind variables, like so:
> my $sth = $dbh->prepare(<<'');
> SELECT distinct(foo)
> FROM bar
> WHERE baz = ? # bind variable
>
> Then, while inside the loop, I could just call the prepared statement
handle
> and pass along the value I want to bind in. Example:
> while (@whatever) {
> $sth->execute($bind_this)
> ...
> }
>
> This practice promotes better performance as the cursor is only prepared
> once instead of every iteration through the loop.
>
> My question is how to do this in PL/SQL. Below I have listed the code
from
> a short script that works fine, but violates the above philosophy of
> preparing all cursors outside of loops. Can anyone show me how to change
> this script to move the cursor preparation outside of the loops?
>
> Thanks,
> Phil R Lawrence
>
>
>
>
>
>
Received on Sat Sep 23 2000 - 06:07:05 CDT