Search code examples
oracle-databaseplsqlrowid

PL/SQL "WHERE CURRENT OF" vs "ROWID"


Why Oracle made "where current of" syntax when you can use "rowid"? Example:

BEGIN
  FOR rec IN (SELECT t.column1, t.rowid rid FROM test_table) LOOP
    UPDATE test_table tb SET column1 = some_function(rec.column1) WHERE tb.rowid = rec.rid;
  END LOOP;
  COMMIT;
END;

DECLARE 
  CURSOR cur IS SELECT t.column1 FROM test_table;
  param1 test_table.column1%TYPE;
BEGIN
  LOOP
    FETCH cur INTO param1;
    UPDATE test_table tb SET tb.column1 = some_function(param1) WHERE CURRENT OF cur;
    EXIT WHEN cur%NOTFOUND;
  END LOOP;
  COMMIT;
END;

Solution

  • Where Current Of is used to identify LAST FETCHED ROW in cursor. It's more safe, because You have 100% confidence, that f.e. You updating LAST FETCHED ROW from curosr. With Rowids there's danger, because it's really easy to mess up something.