Copyright © TIBCO Software Inc. All Rights Reserved
Copyright © TIBCO Software Inc. All Rights Reserved


Chapter 5 The Action Statements : FORALL Statement

FORALL Statement
The FORALL statement is a looping construct that processes a set of occurrences retrieved from the database. The body of the loop consists of the statements to be executed for each occurrence that satisfies the selection criteria. FORALL statements can be nested.
Usage of FORALL
A FORALL statement contains:
1.
2.
3.
Action sequence numbers are not permitted within a FORALL loop; since a FORALL loop constitutes a single statement, all actions within it are executed whenever a FORALL is executed.
4.
Usage Notes
If you make updates to a table, you must commit the updates to the database before you can retrieve them with a FORALL statement. Refer to the COMMIT Statement for details about committing data.
Selection within a FORALL
As with all table access statements, parameters and selection on fields are specified in a WHERE clause, as shown in the second example in Examples.
Ordering in Selection
When a FORALL statement is executed, table occurrences are selected in the order in which they are stored, unless a different order is specified by one of the following:
The following shows an example of the ORDERED clause. In this example, the occurrences are ordered by descending values of the field PRICE, then by ascending values of the field MODEL, and then by ascending values of the primary key LICENSE# (the default for ordering is ascending).
FORALL CARS WHERE CITY = INVOICE.CITY ORDERED DESCENDING
   PRICE & ORDERED ASCENDING MODEL :
   CALL $PRINTLINE('CAR ID ' || CARS.LICENSE# ||
    ' MODEL ' || CARS.MODEL || ' RETAIL PRICE:$' ||
    CARS.PRICE);
END;
Exception Handling
No exceptions are raised if occurrences are not selected by the FORALL statement. The actions in the body of the FORALL statement are not executed and processing continues for statements following the END statement.
Refer to Chapter 6, Exception Handling for a description of how rules handle exceptions.
Termination of a FORALL Statement
FORALL statement execution terminates under either of these circumstances:
An exception is detected (and not handled by rules inside the FORALL loop) during the execution of the statements comprising the loop.
The table buffer is undefined after all occurrences satisfying the FORALL selection criteria are processed. Accessing CARS.MODEL after the FORALL statement in Selection within a FORALL would not provide the model of the last car but would raise the UNASSIGNED exception.
Examples
1.
FORALL MANAGER:
   CALL PRINT_MANAGER;
END;
2.
FORALL EMPLOYEES WHERE REGION = 'MIDWEST'
   & HIREDATE > *.BIRTHDATE + 40 :
   ....
END;
3.
4.
About the Examples
Example 2 retrieves all occurrences in the MIDWEST table instance of the EMPLOYEES table where the value of the HIREDATE field is greater than the value of the BIRTHDATE field plus 40. The asterisk (*) represents the current table.
Example 3 retrieves all occurrences in the MANAGER table where the MANAGER_NAME field ends in ‘SON’. To get all the manager names that do not end in ‘SON’, write the FORALL statement like this:
FORALL MANAGER WHERE ¬ (MANAGER_NAME LIKE '*SON') :
Example 4 retrieves all occurrences in the MANAGER table until the GETFAIL exception is signaled. Refer to UNTIL Statement for a description of how an UNTIL clause can be handled.
See Also
TIBCO Object Service Broker Managing Data about TIBCO Object Service Broker tables.

Copyright © TIBCO Software Inc. All Rights Reserved
Copyright © TIBCO Software Inc. All Rights Reserved