Plsql updating in cursor
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.
So far in this series we've been concerned with simply reading data from tables using a cursor.
In this final part we'll look at how you can use a cursor to modify data as well.
In this chapter, we will discuss the cursors in PL/SQL. A cursor holds the rows (one or more) returned by a SQL statement.
When the Fetch clause is used with a cursor having a FOR UPDATE clause, the PL/SQL unit fails when we try to open the cursor with an ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc. The error description shows that the internal mechanism for the Fetch clause uses either a DISTINCT or a GROUP BY clause which is not permitted alongside the FOR UPDATE clause.
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.
At the same time we'll read the first film's Oscar wins into the variable we declared earlier. We'll start by setting up the basic structure of the loop and adding a quick test to ensure that our variable is accumulating the number of Oscars.
To put the results we've calculated into the table we can replace our simple PRINT statement with an UPDATE statement.