Skip to content

Updatable Cursors (FOR UPDATE) ✏️

Mentor's Note: Usually, a cursor is "Read-Only". But sometimes you want to "Lock" a row while you are looking at it and change its data immediately. This is what we call an Updatable Cursor. 💡

🌟 The Scenario: The Bank Audit 💰

Imagine you are a bank auditor. - You are going through a list of accounts. - When you find an account with a negative balance, you want to Apply a Fine immediately. - You don't want to search for the ID again—you just want to change the record you are currently holding in your hand. ✅

💻 1. The FOR UPDATE Clause

First, you must tell Oracle you intend to change the data by adding FOR UPDATE to your query. This Locks the rows so nobody else can change them while you are working.

DECLARE
   CURSOR c_sal IS 
      SELECT salary FROM employees 
      WHERE department_id = 10 
      FOR UPDATE; -- 🔒 Lock these rows

💻 2. The WHERE CURRENT OF Clause 📍

This magic clause tells Oracle: "Update the exact row that this cursor is currently pointing to."

BEGIN
   FOR r IN c_sal LOOP
      -- 🚀 Action: Give everyone in Dept 10 a small raise
      UPDATE employees
      SET salary = salary + 100
      WHERE CURRENT OF c_sal; -- 🎯 Target current row
   END LOOP;
   COMMIT; -- 🔓 Release locks
END;

🛡️ 3. Why use this? (Architect's Note)

  1. Accuracy: You avoid "Race Conditions." If you used WHERE id = r.id, another user could potentially delete that ID between your SELECT and your UPDATE. WHERE CURRENT OF is much safer.
  2. Performance: It is faster because Oracle already has the physical address (ROWID) of the row. It doesn't have to search the index again. ⚡
  3. Pessimistic Locking: FOR UPDATE is great for high-stakes banking or inventory systems where you must ensure data doesn't change while you calculate.

📈 Learning Path