Locking The Result Set

In a busy organization, where the applications are pulling, updating, purging records, we need to be really careful when doing a maintenance work or designing a new application or procedures. How do you make sure the data that you want to modify is locked for you so other application or users can't modify or purged. This is really important when designing a procedure or application which does the update or delete. We must lock the only records that you would like to make changes on so the rest are available for other resource to perform DML activities.

SELECT ... FOR UPDATE
Locks the rows selected by the SELECT statements which gets released only when the COMMIT or ROLLBACK is issued.
SYNTAX:

SELECT col1, col2, 
FROM table 
FOR UPDATE [col_name] [NOWAIT];

Where col_name is the column name that you wish to update and NOWAIT does not wait for other resources to be free from lock.
Example:

SELECT empno
FROM emp
WHERE job ='CLERK'
FOR UPDATE sal;

It lock the row in emp table where the job is Clerk. This lock is released when the app or user issues COMMIT or ROLLBACK. All returned set of data hold row-level exclusive locks where other sessions can only query but cannot update, delete or  SELECT for UPDATE. This feature allows developer to lock a set of Oracle rows until the transaction is completed.
 
The WHERE CURRENT OF CLAUSE is used in some UPDATE and DELETE statements. The WHERE CURRENT OF is used if you are going to delete or update the records referenced by SELECT FOR UPDATE cursor.
Example:

UPDATE SYNTAX:
 UPDATE tbl_name
  SET set_clause
  WHERE CURRENT OF cursor_name;
  
DELETE SYNTAX: 
 DELETE FROM tbl_name
 WHERE CURRENT OF cursor_name;

Example: Using SELECT FOR UPDATE & WHERE CURRENT OF

DECLARE
 CURSOR emp_update_cur 
 AS 
 SELECT sal, empno
 FROM emp_bk
 FOR UPDATE sal;
 l_emp_update_cur mp_update_cur%ROWTYPE;
BEGIN
 OPEN emp_update_cur;
 LOOP
  FETCH emp_update_cur INTO l_emp_update_cur;
  EXIT WHEN emp_update_cur%NOTFOUND;
  UPDATE emp
  SET sal = l_emp_update_cur.sal
  WHERE CURRENT OF emp_update_cur;
  COMMIT;
 END LOOP;
 CLOSE emp_update_cur;
END;
/

Alternative to above PL/SQL is using a SQL UPDATE statement using sub-query.

UPDATE emp e
 SET sal = (SELECT  sal from emp_bk b
   WHERE e.empno = b.empno)
WHERE EXISTS
 (SELECT 1 FROM emp_bk b WHERE e.empno = b.empno);

The SQL is good for small numbers of update and you know for sure the data from emp_bk won't change. The PL/SQL is good for any number of rows (small, large, or very large).You can control when you want to commit change and add if statements any where.The cursors holds a lock on emp_bk for the given condition and updates emp table with the sal from emp_bk.