SQL/MX 3.2.1 Programming Manual for C and COBOL (H06.26+, J06.15+)

Static Rowsets
HP NonStop SQL/MX Release 3.2.1 Programming Manual for C and COBOL—663854-005
7-6
Using Rowset Arrays for Input
FROM employee END-EXEC.
...
Using Rowset Arrays for Input
Use array host variables as input in the WHERE clause of SELECT, DELETE, or
UPDATE statements, the HAVING clause of a SELECT statement, the SET clause of
an UPDATE statement, and the VALUES clause of an INSERT statement.
Use rowset arrays for input to provide a looping mechanism equivalent to multiple
logical executions of the same SQL statement, once for each set of input values.
However, diagnostic information is returned only for the whole SQL statement and not
for each set of input values. When you use more than one array host variable as input
in an SQL statement, the input arrays might not all be of uniform size. In this situation,
the number of input values is equal to the size of the smallest input array. If you use
scalar host variables along with some rowset arrays as input, the scalar values are
duplicated as many times as the size of the smallest input array. This scenario is
semantically equivalent to replacing the scalar input host variable with an array (whose
size is the same as the smallest input array in that SQL statement), every element of
which has the same value as the scalar host variable.
When you use rowset arrays as input, check that all array elements up to the size of
the smallest input array have valid values. If not all elements of the input array are
used, specify the size of the input array size to be a smaller value by using the
ROWSET FOR INPUT SIZE syntax. See Specifying Size and Row ID for Rowset
Arrays on page 7-24.
Using Rowset Arrays for Output
Use array host variables for output in the INTO clause of SELECT and FETCH
statements.
Use rowset arrays for output to retrieve multiple rows from the result table by executing
a single SQL statement. When more than one array host variable is used as output in
an SQL statement, the output arrays might not be of uniform size. In this situation, the
number of output rows retrieved is equal to the size of the smallest output array.
When you use the SELECT INTO statement, check that the number of rows in the
result table is not larger than the size of the smallest output array. If the result table is
larger than the output array size, you must declare a cursor, and the FETCH statement
must be executed multiple times to retrieve all the rows in the result table.
Do not use rowset arrays for output in a cursor declaration or with dynamic rowsets.