SQL Programming Manual for TAL

NonStop SQL Statements and Directives
HP NonStop SQL Programming Manual for TAL—527887-001
3-45
UPDATE
When you use a cursor to select rows for subsequent update, you must use the FOR
UPDATE OF clause in the cursor declaration. If you determine you want to update the
data, you use a WHERE CURRENT OF clause to update the row from the table.
For audited tables and views, a TMF transaction must be in progress. The same TMF
transaction must include the OPEN, FETCH, and UPDATE operations.
This example declares a cursor, fetches and tests the data, then updates the row:
EXEC SQL
DECLARE check_emp CURSOR FOR
SELECT deptnum
FROM employee
WHERE deptnum = :deptnum^del
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 ! Update deptnum in current row
SET deptnum = :newdept
WHERE CURRENT OF check_emp ;
You can refer to a static SQL cursor only in the compilation unit where the cursor is
declared. Thus, for static SQL programs, the UPDATE WHERE CURRENT, DECLARE
CURSOR, OPEN, FETCH, and CLOSE statements that refer to the cursor must be in
the same compilation unit. If a program exits a procedure with an open cursor,
procedures that execute later can still refer to the cursor, provided the procedures are
in the same compilation unit.
For a dynamic SQL cursor, all statements referring to the cursor must appear in the
procedure where the cursor is defined. However, if you open the cursor and use it in a
call to the procedure where it is defined, you can still use the cursor in subsequent
calls without opening the cursor again.
This example uses a cursor to position in the PARTS table on the part number
specified by host variable STARTING^PARTNUM. The example 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.
Note. Do not use a stand-alone UPDATE operation to update a row that has been retrieved
using a cursor FETCH operation. This can invalidate the cursor's buffering for the table and
might degrade performance.