Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: looping and binding

Re: looping and binding

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sat, 23 Sep 2000 13:07:05 +0200
Message-ID: <969715079.8598.2.pluto.d4ee154e@news.demon.nl>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US