The following example shows how to update a row by using a cursor.
*************************************************************************
* Declare a cursor that will be used to update the JOB column of the *
* EMP table. *
*************************************************************************
EXEC SQL
DECLARE THISEMP CURSOR FOR
SELECT EMPNO
, LASTNAME
, WORKDEPT
, JOB
FROM DSN8C10.EMP
WHERE WORKDEPT = 'D11'
FOR UPDATE OF JOB
END-EXEC.
*************************************************************************
* Open the cursor *
*************************************************************************
EXEC SQL
OPEN THISEMP
END-EXEC.
*************************************************************************
* Indicate what action to take when all rows in the result table have *
* been fetched. *
*************************************************************************
EXEC SQL
WHENEVER NOT FOUND
GO TO CLOSE-THISEMP
END-EXEC.
*************************************************************************
* Fetch a row to position the cursor. *
*************************************************************************
EXEC SQL
FETCH FROM THISEMP
INTO :EMP-NUM
, :NAME2
, :DEPT
, :JOB-NAME
END-EXEC.
*************************************************************************
* Update the row where the cursor is positioned. *
*************************************************************************
EXEC SQL
UPDATE DSN8C10.EMP
SET JOB = :NEW-JOB
WHERE CURRENT OF THISEMP
END-EXEC.
*************************************************************************
* Branch back to fetch and process the next row. *
*************************************************************************
*************************************************************************
* Close the cursor *
*************************************************************************
CLOSE-THISEMP.
EXEC SQL
CLOSE THISEMP
END-EXEC.
The following example shows how to retrieve data backward with a cursor.
*************************************************************************
* Declare a cursor to retrieve the data backward from the EMP table. *
* The cursor has access to changes by other processes. *
*************************************************************************
EXEC SQL
DECLARE THISEMP SENSITIVE STATIC SCROLL CURSOR FOR
SELECT EMPNO
, LASTNAME
, WORKDEPT
, JOB
FROM DSN8C10.EMP
END-EXEC.
*************************************************************************
* Open the cursor *
*************************************************************************
EXEC SQL
OPEN THISEMP
END-EXEC.
*************************************************************************
* Indicate what action to take when all rows in the result table *
* have been fetched. *
*************************************************************************
EXEC SQL
WHENEVER NOT FOUND GO TO CLOSE-THISEMP
END-EXEC.
*************************************************************************
* Position the cursor after the last row of the result table. *
* This FETCH statement cannot include the SENSITIVE or INSENSITIVE *
* keyword and cannot contain an INTO clause. *
*************************************************************************
EXEC SQL
FETCH AFTER FROM THISEMP
END-EXEC.
*************************************************************************
* Fetch the previous row in the table. *
*************************************************************************
EXEC SQL
FETCH SENSITIVE PRIOR FROM THISEMP
INTO :EMP-NUM
, :NAME2
, :DEPT
, :JOB-NAME
END-EXEC.
*************************************************************************
* Check that the fetched row is not a hole (SQLCODE +222). *
* If not, print the contents. *
*************************************************************************
IF SQLCODE IS GREATER THAN OR EQUAL TO 0 AND
SQLCODE IS NOT EQUAL TO +100 AND
SQLCODE IS NOT EQUAL TO +222 THEN
PERFORM PRINT-RESULTS.
*************************************************************************
* Branch back to fetch the previous row. *
*************************************************************************
*************************************************************************
* Close the cursor *
*************************************************************************
CLOSE-THISEMP.
EXEC SQL
CLOSE THISEMP
END-EXEC.
The following example shows how to update an entire rowset with a cursor.
*************************************************************************
* Declare a rowset cursor to update the JOB column of the EMP table. *
*************************************************************************
EXEC SQL
DECLARE EMPSET CURSOR WITH ROWSET POSITIONING FOR
SELECT EMPNO
, LASTNAME
, WORKDEPT
, JOB
FROM DSN8C10.EMP
WHERE WORKDEPT = 'D11'
FOR UPDATE OF JOB
END-EXEC.
*************************************************************************
* Open the cursor. *
*************************************************************************
EXEC SQL
OPEN EMPSET
END-EXEC.
*************************************************************************
* Indicate what action to take when end-of-data occurs in the rowset *
* being fetched. *
*************************************************************************
EXEC SQL
WHENEVER NOT FOUND
GO TO CLOSE-EMPSET
END-EXEC.
*************************************************************************
* Fetch next rowset to position the cursor. *
*************************************************************************
EXEC SQL
FETCH NEXT ROWSET FROM EMPSET
FOR :SIZE-ROWSET ROWS
INTO :HVA-EMPNO
, :HVA-LASTNAME
, :HVA-WORKDEPT
, :HVA-JOB
END-EXEC.
*************************************************************************
* Update rowset where the cursor is positioned. *
*************************************************************************
UPDATE-ROWSET.
EXEC SQL
UPDATE DSN8C10.EMP
SET JOB = :NEW-JOB
WHERE CURRENT OF EMPSET
END-EXEC.
END-UPDATE-ROWSET.
*************************************************************************
* Branch back to fetch the next rowset. *
*************************************************************************
*************************************************************************
* Update the remaining rows in the current rowset and close the cursor. *
*************************************************************************
CLOSE-EMPSET.
PERFORM UPDATE-ROWSET.
EXEC SQL
CLOSE EMPSET
END-EXEC.
The following example shows how to update specific rows with a rowset cursor.
*************************************************************************
* Declare a static scrollable rowset cursor. *
*************************************************************************
EXEC SQL
DECLARE EMPSET SENSITIVE STATIC SCROLL CURSOR
WITH ROWSET POSITIONING FOR
SELECT EMPNO
, WORKDEPT
, JOB
FROM DSN8C10.EMP
FOR UPDATE OF JOB
END-EXEC.
*************************************************************************
* Open the cursor. *
*************************************************************************
EXEC SQL
OPEN EMPSET
END-EXEC.
*************************************************************************
* Fetch next rowset to position the cursor. *
*************************************************************************
EXEC SQL
FETCH SENSITIVE NEXT ROWSET FROM EMPSET
FOR :SIZE-ROWSET ROWS
INTO :HVA-EMPNO
, :HVA-WORKDEPT :INDA-WORKDEPT
, :HVA-JOB :INDA-JOB
END-EXEC.
*************************************************************************
* Process fetch results if no error and no hole. *
*************************************************************************
IF SQLCODE >= 0
EXEC SQL GET DIAGNOSTICS
:HV-ROWCNT = ROW_COUNT
END-EXEC
PERFORM VARYING N FROM 1 BY 1 UNTIL N > HV-ROWCNT
IF INDA-WORKDEPT(N) NOT = -3
EVALUATE HVA-WORKDEPT(N)
WHEN ('D11')
PERFORM UPDATE-ROW
WHEN ('E11')
PERFORM DELETE-ROW
END-EVALUATE
END-IF
END-PERFORM
IF SQLCODE = 100
GO TO CLOSE-EMPSET
END-IF
ELSE
EXEC SQL GET DIAGNOSTICS
:HV-NUMCOND = NUMBER
END-EXEC
PERFORM VARYING N FROM 1 BY 1 UNTIL N > HV-NUMCOND
EXEC SQL GET DIAGNOSTICS CONDITION :N
:HV-SQLCODE = DB2_RETURNED_SQLCODE,
:HV-ROWNUM = DB2_ROW_NUMBER
END-EXEC
DISPLAY "SQLCODE = " HV-SQLCODE
DISPLAY "ROW NUMBER = " HV-ROWNUM
END-PERFORM
GO TO CLOSE-EMPSET
END-IF.
*************************************************************************
* Branch back to fetch and process the next rowset. *
*************************************************************************
*************************************************************************
* Update row N in current rowset. *
*************************************************************************
UPDATE-ROW.
EXEC SQL
UPDATE DSN8C10.EMP
SET JOB = :NEW-JOB
FOR CURSOR EMPSET FOR ROW :N OF ROWSET
END-EXEC.
END-UPDATE-ROW.
*************************************************************************
* Delete row N in current rowset. *
*************************************************************************
DELETE-ROW.
EXEC SQL
DELETE FROM DSN8C10.EMP
WHERE CURRENT OF EMPSET FOR ROW :N OF ROWSET
END-EXEC.
END-DELETE-ROW.
*************************************************************************
* Close the cursor. *
*************************************************************************
CLOSE-EMPSET.
EXEC SQL
CLOSE EMPSET
END-EXEC.
|
