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-7
Using Rowset Arrays in DML Statements
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.
Use this general syntax:
For complete syntax, see the SELECT statement in the SQL/MX Reference Manual.
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.
SELECT column [,column]...
INTO :hostvar-array [,:hostvar-array]...
FROM table-name [,table-name]...
[WHERE search-condition]
[GROUP BY column [,column]...]
[HAVING search-condition]
[ORDER BY column [,column]...]
Note. Data mining operations—SAMPLE, SEQUENCE BY, and TRANSPOSE—are not
supported for operations with rowsets. Some Publish/Subscribe operations are not supported
with rowsets either. Specifically, you cannot use rowsets as input (in WHERE and SET clauses)
with embedded UPDATEs and DELETEs. Additionally, you cannot join a rowset-derived table
with an embedded UPDATE or DELETE.