![]() |
Copyright © TIBCO Software Inc. All Rights Reserved |
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
1. A table name, an optional WHERE clause, optional ORDERED clauses, an optional UNTIL clause, and actions.
3. Actions, which comprise the body of the loop and follow the colon. Each action starts on a separate line.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.
• 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 FORALLAs with all table access statements, parameters and selection on fields are specified in a WHERE clause, as shown in the second example in Examples.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).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.
• A FORALL loop cannot contain a SIGNAL statement. For information about SIGNAL statements, refer to SIGNAL Statement.
• The DATAREFERENCE exception is signaled if an error is detected in the specification of the selection criteria.Refer to Chapter 6, Exception Handling for a description of how rules handle exceptions.Termination of a FORALL Statement
• 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.
1.
2.
3.
4.
• Example 1 retrieves all occurrences in the MANAGER table and calls PRINT_MANAGER to print the occurrences.
• 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:
• 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.TIBCO Object Service Broker Managing Data about TIBCO Object Service Broker tables.
![]() |
Copyright © TIBCO Software Inc. All Rights Reserved |