Re: update statement PL/SQL

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Fri, 29 Apr 2005 09:20:27 -0700
Message-ID: <1114791395.584822@yasure>

Paul wrote:

>
>
> DA Morgan <damorgan_at_x.washington.edu> wrote:
>
>

>>Not to prolong this ... I too read that he used a cursor. I also thought
>>to myself ... "Why?" As it turns out the cursor had no purpose. Not a
>>big stretch as there is almost no justification, in Oracle, to ever use
>>a cursor.

>
>
>
> Sorry, I may be confused here - am trying to learn PL/SQL. The book
> Learning Oracle PL/SQL by Bill Pribyl with Steven Feuerstein makes use
> of cursors - what's so bad about them?
>
>
> Paul...

Because in Oracle, and all other RDBMS products of which I am aware, a cursor fetches one row at a time. If you need to fetch 100 rows then you make 100 round trips to the database. They are inefficient and expensive.

When you can avoid looping the best possible solution is almost always to just write the SQL statement with an implicit cursor just as you would if writing in SQL*Plus which was exactly what the OP could have, and should have, done.

But in almost all other cases, especially those where people traditionally write cursor loops the correct solution for at least five years now has been to use bulk binding with the BULK COLLECT and FORALL statements.

Prove it to yourself:

• create two identical tables CREATE TABLE parent ( part_num NUMBER(10), part_name VARCHAR2(15));

CREATE TABLE child AS
SELECT *
FROM parent
WHERE 1=2;

• populate the parent table with data
• depending on your hardware increase
• the '50000' generated to make the
• "slow_way" code take about 3-5 secs.

DECLARE j PLS_INTEGER := 1;
k parent.part_name%TYPE := 'Transducer';

BEGIN
   FOR i IN 1 .. 50000
   LOOP

     SELECT DECODE(k, 'Transducer', 'Rectifier',
     'Rectifier', 'Capacitor',
     'Capacitor', 'Knob',
     'Knob', 'Chassis',
     'Chassis', 'Transducer')
     INTO k
     FROM dual;

     INSERT INTO parent VALUES (j+i, k);

   END LOOP;
   COMMIT;
END;
/

set timing on

• slow way with cursor loop BEGIN FOR r IN (SELECT * FROM parent) LOOP r.part_num := r.part_num * 10;

     INSERT INTO child
     VALUES
     (r.part_num, r.part_name);

   END LOOP;
   COMMIT;
END slow_way;
/

• fast way with bulk binding DECLARE

TYPE PartNum IS TABLE OF parent.part_num%TYPE INDEX BY BINARY_INTEGER; x PartNum;

TYPE PartName IS TABLE OF parent.part_name%TYPE INDEX BY BINARY_INTEGER; y PartName;

BEGIN
   SELECT part_num, part_name
   BULK COLLECT INTO x, y
   FROM parent;

   FOR i IN x.FIRST .. x.LAST
   LOOP
     x(i) := x(i) * 10;
   END LOOP;    FORALL i IN x.FIRST .. x.LAST
   INSERT INTO child
   (part_num, part_name)
   VALUES
   (x(i), y(i));
   COMMIT;
END fast_way;
/

And there are many variations on the theme. Some using arrays. Some using cursors to define what is being fetched ... but using a bulk bind rather than the normal cursor fetch. For example:

TYPE myarray IS TABLE OF all_objects%ROWTYPE; l_data myarray;

CURSOR c IS
SELECT *
FROM all_objects;

BEGIN
   OPEN c;
   LOOP
     FETCH c BULK COLLECT INTO l_data LIMIT 100;

Many of these are demonstrated in Morgan's Library at psoug.org.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Fri Apr 29 2005 - 11:20:27 CDT