SQL Programming Manual for Pascal

NonStop SQL Statements and Directives
HP NonStop SQL Programming Manual for Pascal—528614-001
3-41
Multirow Update Operation Using a Cursor
OR DEPTNUM = :DEPTNUM_UPD
FOR UPDATE OF DEPTNUM;
EXEC SQL
FETCH CHECK_EMP
INTO :DEPTNUM;
{ Program logic to test the data }
...
EXEC SQL
UPDATE EMPLOYEE
SET DEPTNUM = :NEWDEPT
WHERE CURRENT OF CHECK_EMP;
The following example also uses a cursor to position in the PARTS table on the part
number specified by host variable STARTING_PARTNUM. The program can then fetch
rows and determine whether to update data in the columns. The row updated is at the
current position of the cursor GET_BY_PARTNUM_CURSOR. The example declares
host variables NEW_PARTDESC, NEW_PRICE, and NEW_QTY and sets them to the
new values for the columns before executing the UPDATE statement.
EXEC SQL DECLARE GET_BY_PARTNUM_CURSOR CURSOR FOR
SELECT PARTNUM,
PARTDESC,
PRICE,
QTY_AVAILABLE
FROM SALES.PARTS
WHERE ( PARTNUM >= :STARTING_PARTNUM )
STABLE ACCESS
FOR UPDATE OF PARTDESC,
PRICE,
QTY_AVAILABLE;
...
{ Declare host variables NEW_PARTDESC, NEW_PRICE, }
{ and NEW_QTY. }
...
{ Get one row from the PARTS table: }
EXEC SQL FETCH GET_BY_PARTNUM_CURSOR .... ;
...
{ Determine whether this is a row to be updated
...
{ If the row is to be updated, assign update values }
{ to NEW_PARTDESC, NEW_PRICE, and NEW_QTY. }
{ If necessary, update the row at the current cursor}
{ position: }
EXEC SQL UPDATE SALES.PARTS
SET PARTDESC = :NEW_PARTDESC,
PRICE = :NEW_PRICE,
QTY_AVAILABLE = :NEW_QTY
WHERE CURRENT OF GET_BY_PARTNUM_CURSOR;