looping and binding

From: Phil R Lawrence <prlawrence_at_lehigh.edu>
Date: Fri, 22 Sep 2000 16:48:31 -0400
Message-ID: <8qggj2$kio@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

DECLARE
/* Outer loop cursor gets pidms for our population of interest */ CURSOR pidm_all IS

    SELECT distinct(rk000.pidm) pidm

      FROM rk000, rk002
     WHERE rk000.rk105_number_student = rk002.student_id
           AND rk002.ks1_kstat_registered = '1'
           AND rk002.kstrm_key_status_term != '000000'
     ORDER BY rk000.pidm

;

BEGIN
FOR pidm_rec IN pidm_all
LOOP
    DECLARE
    CURSOR shrtram_term_code_all IS

        SELECT distinct(shrtram_term_code_entered) term_code
          FROM shrtram
         WHERE shrtram_pidm = pidm_rec.pidm

;

    CURSOR shrttrm_term_code_all IS

        SELECT distinct(shrttrm_term_code) term_code
          FROM shrttrm
         WHERE shrttrm_pidm = pidm_rec.pidm
           AND NOT EXISTS
               (
               SELECT *
                 FROM shrcgpa
                WHERE shrcgpa_pidm = shrttrm_pidm
                  AND shrcgpa_term_code = shrttrm_term_code
               )

;

    BEGIN
    FOR shrtram_term_code_rec IN shrtram_term_code_all     LOOP

        INSERT INTO shrcgpa
                    (
                    shrcgpa_pidm,
                    shrcgpa_term_code,
                    shrcgpa_activity_date
                    )
             VALUES (
                    pidm_rec.pidm,
                    shrtram_term_code_rec.term_code,
                    SYSDATE
                    )
        ;

    END LOOP;
    FOR shrttrm_term_code_rec IN shrttrm_term_code_all     LOOP

        INSERT INTO shrcgpa
                    (
                    shrcgpa_pidm,
                    shrcgpa_term_code,
                    shrcgpa_activity_date
                    )
             VALUES (
                    pidm_rec.pidm,
                    shrttrm_term_code_rec.term_code,
                    SYSDATE
                    )
        ;

    END LOOP;
    commit;
    END;
END LOOP;
END;
/ Received on Fri Sep 22 2000 - 15:48:31 CDT