Update multiple rows in cursor

Question

I am trying to work on this program step by step. Like in the code below I want to print old price and print new price after updating table inventory using cursor.

Using cursor to update many rows of data, create a procedure that accepts a number represent the percentage increase in price. The procedure will display the old price, new price, and update the database with the new price.

CREATE OR REPLACE PROCEDURE p5 
AS
CURSOR invent_cur IS
SELECT inv_price
FROM inventory;
v_inv_price inventory.inv_price%TYPE;
BEGIN
OPEN invent_cur;
LOOP
FETCH invent_cur INTO v_inv_price;
EXIT WHEN invent_cur%NOTFOUND;

IF (v_inv_price <= 0)
THEN
UPDATE inventory
set inv_price = inv_price*10;
DBMS_OUTPUT.PUT_LINE(v_inv_price);
END IF;

END LOOP;
CLOSE invent_cur;
END;
/

Answer 1

Just try this around your update statement:

CREATE OR REPLACE PROCEDURE p5 (in_pc IN number)
AS
CURSOR invent_cur IS
SELECT inv_price
FROM inventory
WHERE inv_price > 0 
FOR UPDATE;
v_inv_price inventory.inv_price%TYPE;
BEGIN
OPEN invent_cur;
LOOP
FETCH invent_cur INTO v_inv_price;
EXIT WHEN invent_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('before: '||v_inv_price);
v_inv_price := v_inv_price * in_pc ;

UPDATE inventory
set inv_price = v_inv_price
WHERE CURRENT OF invent_cur;

DBMS_OUTPUT.PUT_LINE('after: '||v_inv_price);

END LOOP;

In order to execute:

SET serveroutput ON
exec p5(10);
commit; -- to save the changes