How to use BULK COLLECT and FORALL to replace CURSOR FOR LOOP in PL/SQL?

Question

How to use BULK COLLECT and FORALL to replace CURSOR FOR LOOP in PL/SQL? I'd like to have a more efficient way to update records in a single table.

Suppose I have the following PL/SQL code:

DECLARE
   var_buy_more_shoes   inventory.buy_more_shoes%TYPE := NULL;
   var_buy_more_bananas   inventory.buy_more_bananas%TYPE := NULL;
   var_buy_more_iphone6s   inventory.buy_more_iphone6s%TYPE := NULL;

   CURSOR cur
   IS
      SELECT *
        FROM inventory
      FOR UPDATE;

BEGIN
   FOR rec IN cur
   LOOP
      IF rec.pair_of_shoes_left <= 100
      THEN
         var_buy_more_shoes := 'Yes';
      END IF;

      IF rec.weight_of_bananas_left <= 200
      THEN
         var_buy_more_bananas := 'Yes';
      END IF;

      IF rec.number_of_iphone6s_left <= 50
      THEN
         var_buy_more_iphone6s := 'Yes';
      END IF;

      UPDATE inventory a
         SET A.buy_more_shoes = var_buy_more_shoes,
             A.buy_more_bananas = var_buy_more_bananas,
             A.buy_more_iphone6s = var_buy_more_iphone6s
       WHERE CURRENT OF cur;
   END LOOP;

   COMMIT;
END;

Thanks.

DrabJay · Accepted Answer · 2014-09-23 16:50:40Z

5

This can be done in a single update statement:

UPDATE inventory
SET buy_more_shoes    = CASE
                          WHEN pair_of_shoes_left <= 100 THEN 'Yes'
                          ELSE NULL
                        END
  , buy_more_bananas  = CASE
                          WHEN weight_of_bananas_left <= 200 THEN 'Yes'
                          ELSE NULL
                        END
  , buy_more_iphone6s = CASE
                          WHEN number_of_iphone6s_left <= 50 THEN 'Yes'
                          ELSE NULL
                        END

answered Sep 23, 2014 at 16:50

DrabJay

3,0992 gold badges15 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rajkumar Pandi · Accepted Answer · 2018-07-10 18:10:16Z

0

You can create a table object and collections, then bulk collect the query results into the table collections.

CREATE OR REPLACE EDITIONABLE TYPE  "F_OBJ" AS OBJECT (
Employer_name VARCHAR2(100),
Employer_id VARCHAR2 ( 100 ))

CREATE OR REPLACE EDITIONABLE TYPE  "F_TAB" as table of _OBJ

create or replace function "fname"
return f_tab
is
 l_f_tab f_tab;

begin
SELECT f_obj(employee_name, employee_id) bulk collect into f_tab from employee_table
return f_tab;

answered Jul 10, 2018 at 18:10

Rajkumar Pandi

731 silver badge5 bronze badges

Collectives™ on Stack Overflow

How to use BULK COLLECT and FORALL to replace CURSOR FOR LOOP in PL/SQL?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related