Modify and delete cursors

Cursor modification and deletion operations refer to modifying or deleting specified data rows in a table when the cursor is positioned.

The FOR UPDATE option must be specified in the cursor query statement to lock all or some columns of the corresponding data rows in the table when the cursor is opened.

The FOR UPDATE clause locks the rows selected by the cursor to prevent other sessions from modifying the data. Other sessions cannot lock these rows until the transaction is committed or rolled back.

Syntax:

SELECT . . . FROM ... FOR UPDATE [OF column[, column]...] [NOWAIT]

If another session has locked rows in the active set, the SELECT FOR UPDATE operation will wait until the locks are released before proceeding. In this case, if the NOWAIT clause is specified and the rows are locked by another session, the OPEN operation will immediately exit and return an error: OBE-30006: resource busy; acquire with WAIT timeout expired.

If the FOR UPDATE clause is used to declare the cursor, you can use the WHERE CURRENT OF cursor_name clause in DELETE and UPDATE statements to modify or delete the current row in the cursor result set from the corresponding database table.

Here is an example:

obclient> SELECT empname, salary FROM emp WHERE deptno=4;
+---------+--------+
| EMPNAME | SALARY |
+---------+--------+
| Karen   |  10000 |
| Guy     |  10000 |
+---------+--------+
2 rows in set

obclient>DECLARE
         V_deptno emp.deptno%TYPE :=4;
     CURSOR c_emp IS SELECT empname, salary
             FROM emp WHERE deptno=V_deptno FOR UPDATE NOWAIT;
     BEGIN
     FOR emp_record IN c_emp LOOP
             IF emp_record.salary < 12000 THEN
                 UPDATE emp SET salary=12000 WHERE CURRENT OF c_emp;
             END IF;
     END LOOP;
     END;
     /
Query OK, 0 rows affected

obclient> SELECT empname, salary FROM emp WHERE deptno=4;
+---------+--------+
| EMPNAME | SALARY |
+---------+--------+
| Karen   |  12000 |
| Guy     |  12000 |
+---------+--------+
2 rows in set