SQL/MX Programming Manual for C and COBOL (G06.24+, H06.03+)

Static Rowsets
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
7-6
Using Rowset Arrays for Output
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.
Using Rowset Arrays in DML Statements
Selecting Rows Into Rowset Arrays
Use a SELECT INTO statement using a rowset as output to retrieve multiple rows of
data from one or more tables or views and place column values into corresponding
host variable arrays. The set of rows returned in a single SELECT statement is called
the rowset, and the columns of the rows are the arrays composing the rowset.
Technique Description
Selecting Rows Into Rowset Arrays
Multiple rows of data are retrieved from a
table or a view, and the specified column
values are placed into host variable arrays.
Multiple search conditions can also be
specified by using host variable arrays in the
WHERE clause.
Inserting Rows From Rowset Arrays
Multiple rows are inserted into a table or
view by using arrays of values in the
VALUES clause of an INSERT statement.
Updating Rows by Using Rowset Arrays
Multiple logical executions of an UPDATE
statement are performed by using arrays of
values in the SET and WHERE clause.
Deleting Rows by Using Rowset Arrays
Multiple logical executions of the DELETE
statement are performed by using arrays of
values in the WHERE clause.
Note. This list of where you can use rowset arrays is not exhaustive. In general, wherever you
specify a scalar host variable in an SQL statement, you can substitute a rowset array host
variable of equivalent type.