Cursors for updating
An SQL postfix clause is positioned in the second position and will be appended to the SELECT statement, following the where clause.The SQL postfix clause is most commonly used for clauses such as ORDER BY. The order of values should be in the same order as the fields.
To put the results we've calculated into the table we can replace our simple PRINT statement with an UPDATE statement.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.You can add more column names in a comma-separated list if you need to make more fields updatable.The easiest way to declare a cursor that can be used to update records is to simply use the default cursor type: Using the code above will declare a cursor which can used to update records.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.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.
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.
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.
In this scenario, the cursor result set can be limited using the traditional When we associate a SELECT statement with more than one table joined together to a cursor with a FOR UPDATE clause, we end up locking all the tables in the FROM clause of the SELECT statement, where we just need to lock a single table for our purpose.