FOR UPDATE OF in PLSQL

Note that the columns in OF clause only specify which tables’ rows are locked. The specific columns of the table that you specify are not significant. If you omit the OF clause, Oracle7 locks the selected rows from all the tables in the query.

1)     The following statement locks rows in the EMP table with clerks located in New York and locks rows in the DEPT table with departments in New York that have clerks:

    SELECT empno, sal, comm

    FROM emp, dept

    WHERE job = ’CLERK’

    AND emp.deptno = dept.deptno

    AND loc = ’NEW YORK’

    FOR UPDATE;

2)  The following statement only locks rows in the EMP table with clerks located in New York; no rows are locked in the DEPT table:

    SELECT empno, sal, comm

    FROM emp, dept

    WHERE job = ’CLERK’

    AND emp.deptno = dept.deptno

    AND loc = ’NEW YORK’

    FOR UPDATE OF emp;