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.
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