Oracle PL/SQL – SELECT FOR UPDATE Statement

Last Updated on :November 15, 2019

When you execute SELECT FOR UPDATE statement, database acquires row level lock automatically against all the rows in the CURSOR result set. Locks on the rows are released after your session ends or COMMIT / ROLLBACK is executed from your code.
You can use SELECT FOR UPDATE statement in explicit cursor as follows

Example
In this case, all the employees rows return by the cursor will be locked.  Other session will not be able to change the same result set until you perform COMMIT or ROLLBACK

SELECT e.*
    FROM employees e,
      departments d
    WHERE e.department_id=d.department_id
    AND d.department_name='IT' FOR UPDATE;

Note 
When you try to COMMIT or ROLLBACK with in LOOP on same locked row then system raises “ORA-01002: fetch out of sequence “It is recommended to use COMMIT/ROLLBACK outside the loop

DECLARE
  CURSOR emp_cur
  IS
    SELECT e.*
    FROM employees e,
      departments d
    WHERE e.department_id=d.department_id
    AND d.department_name='IT' FOR UPDATE;
BEGIN
  dbms_output.put_line('Getting employees for department IT');
  FOR emp_rec IN emp_cur
  LOOP
    IF emp_rec.salary > 10000 THEN
      dbms_output.put_line('Increase the Salary Salary got increased by 10% for  '|| emp_rec.first_name);
      UPDATE employees
      SET salary       =salary+(salary*0.1)
      WHERE employee_id=emp_rec.employee_id;
      
      --COMMIT;
      --Don't use COMMIT or ROLLBACK inside the Loop
      
    END IF;
  END LOOP;
 
 COMMIT;
 
END;
/