The FOR Clause
You can add a FOR clause to a DECLARE CURSOR statement. This FOR-clause has two forms. By using the first form, FOR UPDATE, you specify that you want to update or remove rows through cursors; use the second form to indicate explicitly that the rows of the cursor will be queried only, with no updates. Start with the first form.
A special version of the UPDATE statement enables you to update the current row of a given cursor. Instead of a set-oriented change, we make changes in a specific row. For this reason, it is called a positioned update. The "normal" UPDATE statement is sometimes called a searched update.
Here is the extended definition of the UPDATE statement:
To use this positioned update, a FOR clause must be included in the DECLARE CURSOR statement of the cursor being updated. In this clause, you specify which of the columns will possibly be updated.
Figure. The following program is based on the RAISE_WON_2 program from Section 26.6. We have made the following changes: The program shows the matches information for team 1, row by row, and asks, for each row, whether the number of sets won should be increased by one.
Explanation The only change in this program, compared to the original version, is that the DECLARE CURSOR statement has been expanded with a FOR clause. By doing this, we are making a provision for the values in the WON column to be updated at some point. In the UPDATE statement, we specify in the WHERE clause that in the row that is current for the C_MAT cursor, the WON column should be increased by one.
However, not all cursors can be updated. If the table expression of the cursor contains, for example, a GROUP BY clause, the cursor is read-only by definition. The rules that determine whether a cursor can be changed are the same as the rules that determine whether the virtual contents of a view can be changed. (These rules were described in Section 21.8, in Chapter 21, "Views.")
In addition, the rule applies that if the keywords INSENSITIVE or SCROLL, or an ORDER BY clause has been specified, the cursor cannot be updated.
It may be possible to update the table expression, but the program has no intention to change the result. SQL still assumes that a change is about to occur. This can be prevented by closing the cursor declaration with FOR READ ONLY. Then, the system knows that no change is going to be made.