We have already read about the Oracle cursor in the below post
Let’s check about the select for update oracle. How it is beneficial and How to use it in PLSQL
For Update clause of Oracle Cursor
(1) For Update Clause can be used within the cursor query. This means that rows returned by the query are locked exclusively when the OPEN statement is processed. Since locks are released at the end of the transaction. Commit command should not be given across fetches from an explicit cursor if FOR UPDATE is used.
2) The For Update clause is the last clause in a select statement, even after the ORDER BY.
3) Lock only those records which are satisfied by condition.
4) NOWAIT: Returns an Oracle error if the rows are locked by another session
5) we use the below clause when to update the rows in the cursor with an update
Where current of <cursor name>
Example
DECLARE Cursor cur1 is SELECT emp_salary from emp_master where country=’IN’ FOR UPDATE OF emp_salary NOWAIT; BEGIN FOR emp_record in cur1 LOOP IF emp_record. emp_salary < 10000 then Update emp_master set emp_salary= 2*emp_record.emp_salary Where Current of cur1; END IF; END LOOP; END; /
Related Articles
How to work with date in Oracle sql
Oracle PLSQL Tables
Everything about Oracle PLSQL records
Most commonly asked 25 oracle plsql interview questions
Oracle PLSQL Block Structure and Oracle PLSQL Variable
Cursor attributes
