I searched extensively but I couldn't find clarity, whether Primary Key (In this case, composite of Foo1ID and Foo2ID) should be selected into foo_cursor, even if not used in the procedure?
Is it sufficient to rely on some internal mechanism (ROWID?) that will be able to uniquely identify and update the records in the loop, or is it better to specifically select Primary Key?
If possible, with an answer, can you point me to the documentation section for this?
Thanks
CREATE TABLE Foo (
Foo1ID VARCHAR2(255),
Foo2ID VARCHAR2(255),
FooCategory VARCHAR2(255),
FooValue NUMBER,
PRIMARY KEY (Foo1ID, Foo2ID)
);
CREATE OR REPLACE PROCEDURE UpdateFooValue(
p_FooCategory Foo.FooCategory%TYPE
) AS
CURSOR foo_cursor IS
-- Should we include Foo1ID, Foo2ID here ?
SELECT Foo1ID, Foo2ID, FooValue FROM Foo
WHERE Foo.FooCategory = p_FooCategory
FOR UPDATE;
v_FooValue Foo.FooValue%TYPE := 0;
BEGIN
FOR foo_rec IN foo_cursor LOOP
IF foo_rec.FooValue > 50 THEN
v_FooValue := 25;
ELSE
v_FooValue := 10;
END IF;
UPDATE Foo SET FooValue = v_FooValue WHERE CURRENT OF foo_cursor;
END LOOP;
END UpdateFooValue;
The database identifies the row in this code
The CURRENT OF clause, a PL/SQL extension to the WHERE clause of the SQL statements UPDATE and DELETE, restricts the statement to the current row of the cursor https://docs.oracle.com/en/database/oracle/oracle-database/23/lnpls/transaction-processing-and-control.html#GUID-6A1C2FD9-9476-4F8D-A2E4-2C967D7F8C08 So there's no need for you to select the primary key or rowid.
That said, I would remove the for loop from this code completely. The best way to have a slow database process is to have a loop that writes one row at a time.
Fast database processes change all the rows in one execution. To do this, remove the loop and copy the cursor's where clause into the update. Move the logic in the IF statement into a CASE expression in the SET clause.
This gives something like:
CREATE OR REPLACE PROCEDURE UpdateFooValue(
p_FooCategory Foo.FooCategory%TYPE
) AS
CURSOR foo_cursor IS
SELECT Foo1ID, Foo2ID, FooValue FROM Foo
WHERE Foo.FooCategory = p_FooCategory
FOR UPDATE;
BEGIN
OPEN foo_cursor; -- if needed for lock management
UPDATE Foo SET FooValue = CASE
WHEN foovalue > 50 then 25
ELSE 10
END
WHERE Foo.FooCategory = p_FooCategory;
CLOSE foo_cursor; -- if needed for lock management
END UpdateFooValue;
/
You could remove the cursor and the statements to open/close it too. Keep these if you want more control when other sessions have the rows locked; e.g. to set a wait timeout (FOR UPDATE WAIT n). You may also need to open the SELECT FOR UPDATE cursor if this is part of a larger process which changes many tables to preserve data consistency.