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
1 – Format
(B)0[m[4mFOR[m qwqqqqqqqqqq>qqqqqqqqwqq> rse qqwqqqqqqq>qqqqqqqwqqk
mq> handle-options qj mqq> on-error qqj x
lqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqwq> statement qwqqqqqq> [4mEND_FOR[m qqqqqqqqq>
mqqqqqqqq<qqqqqj
1.1 – rse
Any valid record selection expression. Request HELP on RSE for a
complete discussion of record selection expressions.
1.2 – on-error
(B)0[m[4mON[m [4mERROR[m qwq> statement qwq> [4mEND_ERROR[m
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.
1.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.
1.4 – handle-options
(B)0[mhandle-options =
qq> ( qwq> [4mREQUEST_HANDLE[m qqq> var qqqqqqqqqqqqqqqqqqqqqqqqqqqwq> ) qq>
tq> [4mTRANSACTION_HANDLE[m qqq> var qqqqqqqqqqqqqqqqqqqqqqqu
mq> [4mREQUEST_HANDLE[m q> var , [4mTRANSACTION_HANDLE[m q> var qj
1.4.1 – 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.
1.4.2 – 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
3 – 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
4 – 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
4.1 – Format
(B)0[m[4mFOR[m qq> context-var qqq> IN qq> ss-field qqwqqqqqqq>qqqqqqwqk
mq> on-error qqj x
lqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqwq> get-statement qwqqq> [4mEND_FOR[m qqq>
mqqqqqqqq<qqqqqqqqqj
4.1.1 – context-var
A context variable.
4.1.2 – 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.1.3 – 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.1.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.
4.2 – 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.
4.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.