A common way to update a series of rows is as follows:
The statements that open the cursor and that act on the rows of that cursor are related to each other by a result-set identifier, which must be unique across all result-set identifiers, program variables, and program parameters within the program. You specify that identifier in the open statement that opens the cursor, and you reference the same identifier in the get next, delete, and replace statements that affect an individual row, as well as on the close statement that closes the cursor. For additional details, see resultSetID.
The following code shows how to update a series of rows when you are coding the SQL yourself:
handleHardIOErrors = 1; try open selectEmp forUpdate with #sql{ // no space after "#sql" select empname from EMPLOYEE where empnum >= :myRecord.empnum for update of empname }; onException myErrorHandler(8); // exits program end try get next from selectEmp into :myRecord.empname; onException if (sysVar.sqlcode != 100) myErrorHandler(8); // exit the program end end while (sysVar.sqlcode != 100) myRecord.empname = myRecord.empname + " " + "III"; try execute #sql{ update EMPLOYEE set empname = :empname where current of selectEmp }; onException myErrorHandler(10); // exits program end try get next from selectEmp into :myRecord.empname; onException if (sysVar.sqlcode != 100) myErrorHandler(8); // exits program end end end // end while; cursor is closed automatically // when the last row in the result set is read sysLib.commit;
If you wish to avoid some of the complexity in the previous example, consider SQL records. Their use allows you to streamline your code and to use I/O error values that do not vary across database management systems. The next example is equivalent to the previous one but uses an SQL record called emp:
handleHardIOErrors = 1; try open selectEmp forUpdate for emp; onException myErrorHandler(8); // exits program end try get next emp; onException if (sysVar.sqlcode not noRecordFound) myErrorHandler(8); // exit the program end end while (sysVar.sqlcode not noRecordFound) myRecord.empname = myRecord.empname + " " + "III"; try replace emp; onException myErrorHandler(10); // exits program end try get next emp; on exception if (sysVar.sqlcode not noRecordFound) myErrorHandler(8); // exits program end end end // end while; cursor is closed automatically // when the last row in the result set is read sysLib.commit;
Later sections describe SQL records.
(C) Copyright IBM Corporation 1992, 2005. All Rights Reserved.