Difference between FOR UPDATE OF and FOR UPDATE

From the Oracle documentation:

Use the OF ... column clause to lock the select rows only for a particular table or view in a join. The columns in the OF clause only indicate which table or view rows are locked. The specific columns that you specify are not significant. However, you must specify an actual column name, not a column alias. If you omit this clause, then the database locks the selected rows from all the tables in the query.

If your query references a single table then there is no difference between FOR UPDATE and FOR UPDATE OF ..., but the latter may still be useful as self-documentation to indicate which columns you intend to update. It doesn't restrict what you can update though. If you have:

CURSOR cur IS SELECT * FROM emp FOR UPDATE OF sal;

then you can still do:

UPDATE emp SET comm = comm * 1.1 WHERE CURRENT OF cur;

But if there is more than one table then FOR UPDATE OF ... will only lock the rows in the tables that contain the columns you specify in the OF clause.

Contrary to what I think you're saying in the question. specifying FOR UPDATE OF sal does not only lock the sal column; you can never lock a single column, the minimum lock is at row level. (Read more about locks). It locks all rows in the table that contains the SAL column, which are selected by the query.

In the update to your question, your cursor query is joining emp and dept, but the OF clause only has sal, a column in the emp table. The rows in the emp table will be locked when the cursor is opened, and those locks won't be released until you commit or rollback that session. Within your cursor loop you can do:

UPDATE emp SET ... WHERE CURRENT OF emp_cur;

... to update the row in the emp table that relates to this iteration of the loop. You cannot do:

UPDATE dept SET ... WHERE CURRENT OF emp_cur;

... because rows in the dept table are not locked, because no columns were in the OF. That also means that in your second session the dept rows can be updated freely, as they are not locked by the first session.

one additional comment on for update. If you select from multiple tables and don't have where clause for referencing each table for the update then it will lock all of the tables until the update is done.