Modify and drop a cursor

You can modify or drop specified data rows from a table based on the cursor.

The FOR UPDATE option must be used in cursor query statements. This way, all or part of the table columns of the data rows corresponding to the result set of the cursor are locked when the cursor is opened.

The FOR UPDATE clause will lock the rows selected by the result set of the cursor to prevent the data from being modified by other sessions. Other sessions cannot lock these data rows until the transaction is committed or rolled back.

Syntax:

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

If a session has locked rows in an active set, the SELECT FOR UPDATE operation will wait until this session releases the lock. In this case, if the rows are locked by another session when the NOWAIT clause is added, OPEN will exit immediately and report the following error: ORA-30006: resource busy; acquire with WAIT timeout expired.

If FOR UPDATE is used to declare a cursor, the WHERE CURRENT OF cursor_name clause can be used in the DELETE and UPDATE statements to modify or drop the data row from the database table corresponding to the current data row in the result set of the cursor.

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