1  FOR
   Executes a statement or group of statements once for each record
   in a record stream formed by a record selection expression (RSE).
   Oracle Rdb evaluates all variables when the RSE is compiled, not
   when the statements within the FOR loop execute. For detailed
   information on the RSE, request HELP on RSE.

   Example:

   RDO>   FOR E IN EMPLOYEES WITH E.EMPLOYEE_ID = "00164"
   cont>    MODIFY E USING E.MIDDLE_INITIAL = "M"
   cont>    END_MODIFY
   cont>  END_FOR
 

2  Format

 (B)0FOR qwqqqqqqqqqq>qqqqqqqqwqq> rse qqwqqqqqqq>qqqqqqqwqqk
      mq> handle-options qj          mqq> on-error qqj  x
 lqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
 mqwq> statement qwqqqqqq> END_FOR qqqqqqqqq>
   mqqqqqqqq<qqqqqj

 

3  rse
   Any valid record selection expression. Request HELP on RSE for a
   complete discussion of record selection expressions.
 

3  on-error

 (B)0ON ERROR qwq> statement qwq> END_ERROR
           mqqqqqqqq<qqqqqj

   The ON ERROR clause. This clause specifies the action to be taken
   if an error occurs while Oracle Rdb is compiling the RSE. Request
   HELP for ON_ERROR for more information.
 

3  statement
   Any valid Oracle Rdb data manipulation statement or host language
   statement except INVOKE, COMMIT, or ROLLBACK.

   No statement within the FOR loop can redefine the context
   variable that was defined by the RSE in the FOR statement.
 

3  handle-options

 (B)0handle-options =
 
 qq> ( qwq> REQUEST_HANDLE qqq> var qqqqqqqqqqqqqqqqqqqqqqqqqqqwq> ) qq>
        tq> TRANSACTION_HANDLE qqq> var qqqqqqqqqqqqqqqqqqqqqqqu
        mq> REQUEST_HANDLE q> var , TRANSACTION_HANDLE q> var qj

 

4  REQUEST_HANDLE
   A keyword followed by a host language variable. A request handle
   points to the location of a compiled Oracle Rdb request. If you
   do not supply a request handle explicitly, Oracle Rdb associates a
   default request handle with the compiled request. Your must use
   a request handle when you want to make an identical query to two
   different databases.

   In Callable RDO, use !VAL as a marker for host language
   variables.

   You can put parentheses around the host language variable name.
 

4  TRANSACTION_HANDLE
   A keyword followed by a host language variable. A transaction
   handle identifies each instance of a transaction. If you do not
   declare the transaction handle explicitly, Oracle Rdb attaches an
   internal identifier to the transaction.

   In Callable RDO, use !VAL as a marker for host language
   variables.

   You can put parentheses around the host language variable name.

   Normally, you do not need to use this argument. The ability to
   declare a transaction handle is provided for compatibility with
   other database products and future releases of Oracle Rdb.
 

2  More
   You need the Oracle Rdb READ privilege to the records in a record
   stream to use the FOR statement.

   You can nest FOR loops as an alternative to the CROSS clause to
   perform a join operation. However, the performance of the CROSS
   clause is usually faster.

   However, the interactive RDO utility unbundles loops when an inner
   loop does not reference the context of an outer loop, such as that
   shown in  the following example:

   FOR F1 IN F1 WITH F1.F11 = "1"
       PRINT F1.*
       FOR F2 IN F2 WITH F2.F21 = "1"
           PRINT F2.*
           ERASE F2
       END_FOR
       ERASE F1
   END_FOR

   This loop will be executed as two discrete loops because RDO considers
   them independent.

   FOR F1 IN F1 WITH F1.F11 = "1"
       PRINT F1.*
       ERASE F1
   END_FOR

   FOR F2 IN F2 WITH F2.F21 = "1"
       PRINT F2.*
       ERASE F2
   END_FOR

   This simplifies the internal processing performed by RDO and in general
   will result in the desired action upon the database.

   However, if there is a FOREIGN KEY (or similar constraint) relationship
   from F2 to F1 and the constraint is evaluated at verb time, then this
   loop unbundling will result in an unexpected constraint violation.

   %RDB-E-INTEG_FAIL, violation of constraint F2_FOREIGN_0001 caused 
   operation to fail
   -RDB-F-ON_DB, on database DISK1:[SMITH.WORK]FORKEY_TEST.RDB;1

   To avoid such problems, you should either evaluate the constraint at
   commit time, or modify the nested loop so that there is a reference in
   the inner loop to context of the outer loop.  See the underlined portion
   of the following example.

   FOR F1 IN F1 WITH F1.F11 = "1"
       PRINT F1.*
       FOR F2 IN F2 WITH F2.F21 = F1.F11
                         _______________
           PRINT F2.*
           ERASE F2
       END_FOR
       ERASE F1
   END_FOR
 

2  Examples
   Example 1

   Create a record stream with a FOR statement in RDO:

   RDO> START_TRANSACTION READ_ONLY
   RDO>
   RDO> FOR D IN DEPARTMENTS WITH D.DEPARTMENT_CODE = "SEUR"
   cont>  PRINT D.DEPARTMENT_CODE,
   cont>        D.DEPARTMENT_NAME,
   cont>        D.MANAGER_ID
   cont>  END_FOR
   RDO>
   RDO> COMMIT

   These statements:

   o  Create a record stream defined by a record selection
      expression

   o  Retrieve three field values from each record in that stream

   Example 2

   Create a record stream with the FOR statement in BASIC:

   &RDB&  START_TRANSACTION READ_ONLY

   &RDB&  FOR E IN EMPLOYEES CROSS
   &RDB&  S IN SALARY_HISTORY OVER EMPLOYEE_ID
   &RDB&       WITH E.EMPLOYEE_ID = EMPLOYEE_ID
   &RDB&       AND S.SALARY_END MISSING
   &RDB&    ON ERROR
                 GOTO 3000
   &RDB&    END_ERROR
   &RDB&    GET
   &RDB&       LAST_NAME = E.LAST_NAME;
   &RDB&       FIRST_NAME = E.FIRST_NAME;
   &RDB&       SALARY = S.SALARY_AMOUNT
   &RDB&    END_GET
   &RDB&  END_FOR

   &RDB&  COMMIT

   This program fragment retrieves the current salary for an
   employee specified by the value of the EMPLOYEE_ID variable.
   The example:

   o  Establishes a record stream consisting of the record in the
      EMPLOYEES relation with the ID number that the user supplies
      in the host language variable EMPLOYEE_ID, joined with the
      corresponding current SALARY_HISTORY record

   o  Points to an error-handling subroutine, in case of errors from
      Oracle Rdb

   o  Assigns the values from the FIRST_NAME and LAST_NAME fields
      of EMPLOYEES and the SALARY_AMOUNT field of SALARY_HISTORY to
      host variables
 

2  segmented_string_FOR
   A special form of the FOR statement sets up a record stream
   consisting of segments from a segmented string field. Because
   a single segmented string field value is made up of multiple
   segments, a record stream that includes a segmented string field
   is "nested." The outer loop retrieves records that include the
   field and the inner loop retrieves the segments of each field
   value one at a time. Therefore, a FOR statement that retrieves
   segmented strings looks like a set of nested FOR statements.

   Example:

   RDO>  FOR R IN RESUMES
   cont>    FOR S IN R.RESUME
   cont>    PRINT S.RDB$LENGTH, S.RDB$VALUE
   cont>    END_FOR
   cont>  END_FOR
 

3  Format

 (B)0FOR qq> context-var qqq> IN qq> ss-field qqwqqqqqqq>qqqqqqwqk
                                            mq> on-error qqj x
       lqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqj
       mqwq> get-statement qwqqq> END_FOR qqq>
         mqqqqqqqq<qqqqqqqqqj

 

4  context-var
   A context variable.
 

4  ss-field
   A qualified field name that refers to a field defined with the
   SEGMENTED STRING data type. Note that this field name, like all
   field names in a FOR statement, must be qualified by its own
   context variable. This second context variable must match the
   variable declared in the outer FOR statement. See the Examples
   topic.
 

4  on-error
   The ON ERROR clause. This clause specifies the action to be
   taken if an Oracle Rdb error occurs while Oracle Rdb is trying to set
   up retrieval of the next segmented string. For more details,
   request HELP on ON_ERROR.
 

4  get-statement
   Any valid Oracle Rdb data manipulation language or host language
   statement except INVOKE, COMMIT, or ROLLBACK. The GET statement
   can reference only the RDB$VALUE and RDB$LENGTH fields.
 

3  More
   If you have invoked a database, you have the necessary privileges
   to use the FOR Statement with Segmented Strings.

   Oracle Rdb defines a special name to refer to the segments of a
   segmented string. This value expression is equivalent to a
   field name; it names the "fields" or segments of the string.
   Furthermore, because segments can vary in length, Oracle Rdb also
   defines a name for the length of a segment. The statement inside
   the segmented string FOR loop must use these names to refer to
   the segments of the string. These names are:

   RDB$VALUE          The value stored in a segment of a segmented
                      string
   RDB$LENGTH         The length in bytes of a segment
 

   Within a single context, such as the context of a single request, if
   an arithmetic expression contains the MISSING operator, the resulting
   expression will evaluate to MISSING. In the following example,
   A.FIELD_1 contains missing (unknown) values, and the query correctly
   interprets the values in A.FIELD_1 as missing (unknown), causing the
   expression A.FIELD_3 = VARIABLE + A.FIELD_1 to evaluate to MISSING:

   RDO> FOR A IN RELATION_A
   cont>  MODIFY A USING
   cont>  A.FIELD_3 = VARIABLE + A.FIELD_1
   cont>  END_MODIFY
   cont> END_FOR

   However, in nested queries that use multiple database requests, such
   as the following example, if B.FIELD_2 contains missing (unknown)
   values, the expression A.FIELD_3 = VARIABLE + B.FIELD_2 returns
   different results.  The second query (which begins with FOR A)
   retrieves a value, in this case the value defined as the field's
   MISSING_VALUE,  from B.FIELD_2 for its record selection expression
   (RSE).  However, because of RDO language limitations, the second query
   cannot use the fact that the field B.FIELD_2 has an unknown value and
   instead uses the missing value defined for the field with the DEFINE
   FIELD or CHANGE FIELD statement. Using this value for B.FIELD_2 instead
   of treating the value as unknown means that the A.FIELD_3 = VARIABLE +
   B.FIELD_2 expression does not evaluate to MISSING.

   RDO> FOR B IN RELATION_B
   cont> FOR A IN RELATION_A WITH A.FIELD_1 = B.FIELD_1
   cont>  MODIFY A USING
   cont>   A.FIELD_3 = VARIABLE + B.FIELD_2
   cont>  END_MODIFY
   cont> END_FOR
   cont> END_FOR

   The workaround is to use the SQL interface to Oracle Rdb. You can use
   the SQL indicator variables to detect the NULL attribute of the column
   (field) and therefore set the appropriate value for the column.


3  Example
   Create a stream whose records contain segmented string fields:

   RDO>  FOR R IN RECORD
   cont>    FOR S IN R.SS_FIELD
   cont>    PRINT S.RDB$LENGTH, S.RDB$VALUE
   cont>    END_FOR
   cont>  END_FOR

   This statement looks like a nested FOR loop.

   o  The outer loop sets up a record stream using the context
      variable R. The same context variable qualifies the field
      name, SS_FIELD, as in every FOR statement.

   o  The inner loop retrieves the segments of the string field one
      at a time.

   o  The context variable S identifies the segments.

   o  The special segmented string value expressions, RDB$VALUE and
      RDB$LENGTH are qualified by S, the context variable associated
      with the field.