FOR Statement

statement_FOR ::= [ label : ] FOR statement_variable AS [ cursor_name [ SENSITIVE | INSENSITIVE] 
    CURSOR FOR ] cursor_specification 
DO statement END FOR [ label ];

The FOR statement serves for repeating an order of statements for all rows of a query result specified by the cursor_specification. This statement will terminate prematurely if the LEAVE statement is executed or if an error occurs.

You can work with column values on the current row inside the FOR statement using the following notation:

statement_variable.column_name

This can be used for passing a column value to a procedure (see example 2). However, if you assign the column a new value, it will have no effect on the data in the database.

You must use the UPDATE CURRENT OF statement in order to edit the column. Then you can address the columns with their names without the statement_variable (see example 1).

If the end label is set, a start label must be set. No statement may be designated with the same label inside the FOR statement.

Information about cursor specification and the SENSITIVE and INSENSITIVE specifications are described here.

If the cursor_name is set, you can use the cursor name inside the FOR statement in the UPDATE CURRENT OF and DELETE CURRENT OF statements. You cannot use it in the OPEN, CLOSE or FETCH statements.

Example 1:

Lower the prices of items beginning with X by 10%, raise the prices of items beginning with A by 100%, and delete the rest.

PROCEDURE PriceChange();
BEGIN 
  FOR row AS curprice SENSITIVE CURSOR FOR 
    SELECT item_number, price
    FROM PriceList
  DO 
    BEGIN
     IF SUBSTRING(row.item_number FROM 1 FOR 1) = 'X' THEN 
       UPDATE  SET price = price*1.1 WHERE CURRENT OF curprice;
     ELSEIF SUBSTRING(row.item_number FROM 1 FOR 1) = 'A' THEN 
       UPDATE  SET price = price*2 WHERE CURRENT OF curprice;
     ELSE
       DELETE WHERE CURRENT OF curprice;
     END IF;
    END;
  END FOR;
END;

Example 2:

Delete the book reservation whose reservation exceeds a specified limit.

PROCEDURE CancelRes(IN lim INT);
BEGIN

  DECLARE PROCEDURE CheckResBook(code CHAR(8));  // temporary local procedure
  BEGIN
    DECLARE count INT;
    SELECT COUNT(co) INTO count                // how many times is the book reserved?
    FROM Reservation
    WHERE Reservation.co = code;

    IF count = 1 THEN                          // if only once then remove reservation
      UPDATE Books
      SET Books.reserved = FALSE
      WHERE Books.code = code;
    END IF;
  END;

  FOR row AS pomc SENSITIVE CURSOR FOR      // do the following for reservations exceeding the limit
    SELECT * 
    FROM Reservation
    WHERE CURRENT_DATE - Reservation.res_date > lim
  DO BEGIN
      CALL CheckResBook(row.co);            // change the flag if it is only a single reservation
      DELETE WHERE CURRENT OF pomc;           // delete a record in the Reservation tab
    END;
  END FOR;
END

Example 3:

Loop through a table.

FOR row_table AS TABLE Table_test DO
  ...
  CALL Log_write(row_table.column1);
END FOR;

LEAVE Statement

Control Statements

CALL Statement