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-37
Updating Rows by Using Rowset-Derived Tables
:hva_salary INDICATOR :hva_salary_indicator)
AS rs(empnum,first_name,last_name,deptnum,jobcode,salary);
...
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 SQLSTATE pic x(5).
01 rs.
02 ROWSET[100] hvaempnum pic 9(4) comp.
02 ROWSET[100] hvafirstname pic x(15).
02 ROWSET[100] hvalastname pic x(20).
02 ROWSET[100] hvadeptnum pic 9(4) comp.
02 ROWSET[100] hvajobcode pic 9(4) comp.
02 ROWSET[100] hvasalary pic 9(4) comp.
02 ROWSET[100] hvasalaryindicator pic 9(5).
EXEC SQL END DECLARE SECTION END-EXEC.
...
**** Populate the host variables arrays in some way ****
**** Store -1 in the indicator array for the first 50 ****
**** input values. ****
PERFORM VARYING i FROM 1 BY 1 UNTIL i = 50
Move -1 to hvasalaryindicator(i)
end-perform.
**** Store 0 in the indicator array for the next 50 ****
**** input values. It is assumed that there are valid****
**** values for salary in the hvasalary rowset array ****
**** from element no. 51 upto element no. 100 ****
PERFORM VARYING i FROM 51 BY 1 UNTIL i = 100 ****
Move 0 to hvasalaryindicator(i)
end-perform.
EXEC SQL
INSERT INTO employee
SELECT empnum, first_name, last_name, deptnum,
jobcode, salary
FROM
ROWSET(:hvaempnum, :hvafirstname, :hvalastname,
:hvadeptnum, :hvajobcode,
:hvasalary INDICATOR :hvasalaryindicator)
AS rs(empnum, first_name, last_name, deptnum,
jobcode, salary)
END-EXEC.
...
Updating Rows by Using Rowset-Derived Tables
Use a rowset-derived table in an UPDATE statement to indicate which rows are to be
updated from the database table. In this case, the values of the rowset are generated
from a subquery placed in the WHERE clause of the UPDATE statement.
COBOL