In these type of situations, it is ideal to use a Update Cursor, than using the default read only one.
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.
Rows Only does not seem to work with the FOR UPDATE clause.
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.
This is a complete book on PL/SQL with everything you need to know to write efficient and complex PL/SQL code.
Thanks for the many SQL tips you and others provide in Four Hundred Guru. Maybe you can explain something I have yet to figure out.
You can make things a little more obvious by explicitly stating that the cursor is to be used for updating, as shown below: This cursor can be used to update any field from the underlying data set.
To make things even more specific you can list which columns from the data set you would like to make updatable, as shown below: The code above ensures that only one field from the underlying data set can be updated.
Sales Order Detail WHERE Sales Order ID=43659 -- open cursor and fetch first row into variables OPEN update_cursor FETCH FROM update_cursor -- check for a new row WHILE @@FETCH_STATUS=0 BEGIN -- do update operation UPDATE Sales.
Sales Order Detail SET Order Qty=100 WHERE CURRENT OF update_cursor -- get next available row into variables FETCH NEXT FROM update_cursor END close update_cursor Deallocate update_cursor The above query updates the orderqty column to 100 for a particular salesorderid.
I have highlighted the areas which differ compared with a normal cursor.