Re: extracting records using a cursor within a cursor

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 25 May 2005 09:03:29 -0700
Message-ID: <1117036745.580641@yasure>

andrewst_at_onetel.com wrote:
> DA Morgan wrote:
>

>>But even if simple DML will not work ... the solution is bulk
>>binding with BULK COLLECT and FORALL. There is no valid reason
>>to open a cursor loop in a situation such as your sample.

>
>
> This is an overstatement. Hell, even Tom Kyte uses simple FOR loops
> sometimes!

I do too when doing public demonstrations and lecturing so that people will understand what I am doing. But I would never do so in production code.

Run the following and see if you can justify a cursor loop.

CREATE TABLE parent (
part_num NUMBER(10),
part_name VARCHAR2(15));

CREATE TABLE child AS
SELECT *
FROM parent
WHERE 1=2; DECLARE j PLS_INTEGER := 1;
k parent.part_name%TYPE := 'Transducer';

BEGIN
   FOR i IN 1 .. 20000
   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;
/

CREATE OR REPLACE PROCEDURE slow_way IS

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;
/

CREATE OR REPLACE PROCEDURE fast_way IS

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;
/

set timing on

exec slow_way;

exec fast_way;

set timing off

I can't. And if Tom Kyte, or anyone else, has some information to the contrary I would personally be thrilled to learn something new.

-- 
Daniel A. Morgan
Relational theory is not something that is simply a nice-to-have.
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)

Received on Wed May 25 2005 - 11:03:29 CDT