WHERE CURRENT OF in PL/SQL

Question

1. Why we need WHERE CURRENT OF clause in Oracle PL/SQL? We all know that FETCH retrieves only one row at a time and hence FETCH is used in LOOP to process all the rows of a cursor. Then why do we exclusively need a WHERE CURRENT OF clause? We can anyhow lock the cursor rows using FOR UPDATE or FOR UPDATE OF.

2. Can rows be unlocked (which are locked by FOR UPDATE or FOR UPDATE OF) once we close the cursor? Or do we need to COMMIT or ROLLBACK the transaction to unlock the rows?

Answer 1

Have a look at this block:

DECLARE

    CURSOR c1 IS
    SELECT course_number, ROWID AS RID
    FROM courses_tbl
    FOR UPDATE;

begin

    FOR aCourse IN c1 LOOP

        UPDATE courses_tbl SET course_number = aCourse.course_number + 1 
        WHERE CURRENT OF c1;
        UPDATE courses_tbl SET course_number = aCourse.course_number + 1 
        WHERE ROWID = aCourse.RID

    end loop;

end;

The two UPDATE statements are equivalent, WHERE CURRENT OF ... is just a shortcut for WHERE ROWID = ..., you can use either of them.

Actually your question should be "Why we need FOR UPDATE ...?" The reason is, the ROWID may change by other operations, e.g. ALTER TABLE ... SHRINK SPACE, moving tablespace or big DML's. FOR UPDATE locks the row, i.e. ensures that ROWID does not change until you finished your transaction.

No, you can release the lock only by finishing the transaction, i.e. ROLLBACK or COMMIT