So far in this series we've been concerned with simply reading data from tables using a cursor.
This clause can also be extended to lock only the intended table in the SELECT query containing more than one table in joins using the FOR UPDATE OF clause.
This clause becomes meaningless if we use a single table SELECT clause as the minimum Oracle has provided us with the WHERE CURRENT OF clause for both DELETE and UPDATE statements inside a cursor’s range to make changes to the last fetched row(s) from the cursor with an ease.
When we want to update or delete the cursor fetched row(s) from the database, we don’t have to form In the below code listing, the employees joined before the year 2000 are archived and during the cursor process, no other session is permitted to make any changes to those employees using the FOR UPDATE clause.
The employee deletion from the transaction table is performed using the WHERE CURRENT OF clause as shown below.
Programmers cannot control the implicit cursors and the information in it.
Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement.
The first variable will hold the running total of Oscars while the second will be used to read each individual film's Oscars as the cursor moves through the record set.
Next, we'll declare our cursor, open it and move it to the first record.
To demonstrate how to use a cursor to update records we'll create a running total of the number of Oscars our films have won.
To begin with we'll declare a couple of variables and set their default values.
Once we open a cursor having a FOR UPDATE clause, all the rows returned by the SELECT statement are locked for our changes until a commit or a rollback is placed to release the lock.