The FOR Clause






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.

<declare cursor statement> ::=
   DECLARE <cursor name> CURSOR FOR <table expression>
   [ <for clause> ]

<for clause> ::=
   FOR UPDATE [ OF <column name>
      [ { , <column name> }... ] ] |
   FOR READ ONLY


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:

<update statement> ::=
   UPDATE <table reference>
   SET    <column assignment> [ { , <column 
assignment> }... ]
   [ WHERE { <condition> | CURRENT OF <cursor name> } ]

<table reference> ::=
   <table specification> [ [ AS ] <pseudonym> ]

<column assignment> ::=
   <column name> = <scalar expression>


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.

PROGRAM RAISE_WON_3;
DECLARATIONS
   pno    : SMALLINT;
   won    : INTEGER;
   choice : CHAR(1);
BEGIN
   # Cursor declaration
   DECLARE c_mat CURSOR FOR
      SELECT  PLAYERNO, WON
      FROM    MATCHES
      WHERE   TEAMNO = 1
      FOR     UPDATE OF WON;
   #
   OPEN c_mat;
   FETCH c_mat INTO :pno, :won;
   WHILE sqlcode = 0 DO
      WRITE 'Do you want the number of sets won for';
      WRITE 'player ', pno, ' to be increased by 1 (Y/N)?';
      READ choice;
      IF choice = 'Y' THEN
         UPDATE   MATCHES
         SET      WON = WON + 1
         WHERE    CURRENT OF c_mat;
      ENDIF;
      FETCH c_mat INTO :pno, :won;
   ENDWHILE;
   CLOSE c_mat;
   WRITE 'Ready';
END

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.



 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows