SELECT FOR UPDATE / WHERE CURRENT OF

SELECT FOR UPDATE
When a cursor is opened, the sql statement gets executed and builds the dataset.
Once the dataset(resultset) is built, the changes made to the data at the database level will not be reflected to the dataset built.

Example:

Session X  (session sleeps for 10 minutes)

Declare
  cursor emp_rec is select * from scott.emp;
  emp_record emp_rec%rowtype;
Begin
  open emp_rec;
-- sleep for 10 mins
  dbms_lock.sleep(600);
  loop
    fetch emp_rec into emp_record;
   dbms_output.put_line('emp no:' || emp_record.empno || ' salary:' || emp_record.sal);
   exit when emp_rec%notfound;
  end loop;
End;
/

Session Y (while the Session X is in sleep mode)

update emp set sal=sal*1.1;
commit;

The increased salaries will not be reflected to the cursor dataset.

If we want to lock the rows captured by the cursor, we use the FOR UPATE OF clause.

When we issue a SELECT...FOR UPDATE statement , Oracle automatically obtains exclusive row-level locks on all the rows identified by the SELECT statement.

No one else will be able to change any of these records until we perform a ROLLBACK or a COMMIT
but other sessions can still read the data.

we can use the FOR UPDATE clause in a select with multiple tables.All the tables with the columns in the OF list will be locked.

Example:

Cursor emp_cur is
  select
    emp.empno,
    emp.sal,
    dept.dept_name
  from emp,dept
  where emp.deptno=dept.deptno
  FOR UPDATE OF emp.SAL;

In the above Cursor only emp table rows will be locked for update not the dept table.

Once COmmit or Rollback is encountered after the cursor is open,locks will be released & we can no longer fetch data from the cursor.

Note:
we cannot execute another FETCH against a FOR UPDATE cursor after we COMMIT or ROLLBACK.

WHERE CURRENT OF :

"Delete the row I just fetched."
"Update the columns in the row I just fetched."

PL/SQL provides the WHERE CURRENT OF clause for both UPDATE and DELETE statements inside a cursor.

To update columns in the most recently fetched row, specify:
    UPDATE table_name
       SET set_clause
     WHERE CURRENT OF cursor_name;

To delete the row from the database for the most recently fetched record, specify:
    DELETE
      table_name
     WHERE CURRENT OF cursor_name;

EXAMPLE:

Declare
  cursor emp_rec is select * from scott.emp for update;
  emp_record emp_rec%rowtype;
  my_sal scott.emp.sal%type;
BEGIN
  open emp_rec;
  fetch emp_rec into emp_record;
  dbms_output.put_line(emp_record.sal);
  update scott.emp set sal=sal*2
    where current of emp_rec;
  select sal into my_sal from scott.emp where empno=emp_record.empno;
  dbms_output.put_line(my_sal);
  close emp_rec;
commit;
End;
/