Skip to Main Content
  • Questions
  • Is primary key required in cursor when using WHERE CURRENT OF

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Nenad.

Asked: April 04, 2025 - 1:02 pm UTC

Last updated: April 04, 2025 - 3:14 pm UTC

Version: 19c

Viewed 100+ times

You Asked

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;


with LiveSQL Test Case:

and Chris said...

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.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library