It is possible to update and delete the row referenced by a cursor. For a row to be updatable, the query corresponding to the cursor must not be read-only. (For a description of what makes a query updatable or deletable, see the SQL Reference.)
To update with a cursor, use the WHERE CURRENT OF clause in an UPDATE statement. Use the FOR UPDATE clause to tell the system that you want to update some columns of the result table. You can specify a column in the FOR UPDATE without it being in the fullselect; therefore, you can update columns that are not explicitly retrieved by the cursor. If the FOR UPDATE clause is specified without column names, all columns of the table or view identified in the first FROM clause of the outer fullselect are considered to be updatable. Do not name more columns than you need in the FOR UPDATE clause. In some cases, naming extra columns in the FOR UPDATE clause can cause DB2 to be less efficient in accessing the data.
Deletion with a cursor is done using the WHERE CURRENT OF clause in a DELETE statement. In general, the FOR UPDATE clause is not required for deletion of the current row of a cursor. The only exception occurs when using dynamic SQL (see Chapter 3. "Writing Dynamic Programs" for information on dynamic SQL) for either the SELECT statement or the DELETE statement in an application which has been precompiled with LANGLEVEL set to SAA1, and bound with BLOCKING ALL. In this case, a FOR UPDATE clause is necessary in the SELECT statement. See the Command Reference for information on the precompiler options.
The DELETE statement causes the row being referenced by the cursor to be deleted. This leaves the cursor positioned before the next row and a FETCH statement must be issued before additional WHERE CURRENT OF operations may be performed against the cursor.
Cursors fall into three categories:
There can be performance advantages for read-only cursors. See "Row Blocking" for more information.
An ambiguous cursor is treated as read only if the BLOCKING ALL option is specified when precompiling or binding. Otherwise, it is considered updatable.
| Note: | Cursors processed dynamically are always ambiguous. |
For a complete list of criteria used to determine whether a cursor is read-only, updatable, or ambiguous, see the SQL Reference.
This example selects from a table using a cursor, opens the cursor, and fetches rows from the table. For each row fetched, it decides if the row should be deleted or updated (based on a simple criteria).
If an UPDATE is performed, the position of the cursor remains on this row because the UPDATE statement does not change the position of the current row.
If a DELETE statement is performed, a different situation arises, because the current row is deleted. This is equivalent to being positioned before the next row, and a FETCH statement must be issued before additional WHERE CURRENT OF operations are performed.
The CHECKERR macro/function is an error checking utility which is external to the program. The location of this error checking utility depends upon the programming language used:
See "Using GET ERROR MESSAGE in Example Programs" for the source code for this error checking utility.
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "util.h"
EXEC SQL INCLUDE SQLCA;
#define CHECKERR(CE_STR) if (check_error (CE_STR, &sqlca) != 0) return 1;
int main(int argc, char *argv[]) {
EXEC SQL BEGIN DECLARE SECTION;
char pname[10];
short dept;
char userid[9];
char passwd[19];
EXEC SQL END DECLARE SECTION;
printf( "Sample C program: OPENFTCH\n" );
if (argc == 1) {
EXEC SQL CONNECT TO sample;
CHECKERR ("CONNECT TO SAMPLE");
}
else if (argc == 3) {
strcpy (userid, argv[1]);
strcpy (passwd, argv[2]);
EXEC SQL CONNECT TO sample USER :userid USING :passwd;
CHECKERR ("CONNECT TO SAMPLE");
}
else {
printf ("\nUSAGE: openftch [userid passwd]\n\n");
return 1;
} /* endif */
EXEC SQL DECLARE c1 CURSOR FOR (1)
SELECT name, dept FROM staff WHERE job='Mgr'
FOR UPDATE OF job;
EXEC SQL OPEN c1; (2)
CHECKERR ("OPEN CURSOR");
do {
EXEC SQL FETCH c1 INTO :pname, :dept; (3)
if (SQLCODE != 0) break;
if (dept > 40) {
printf( "%-10.10s in dept. %2d will be demoted to Clerk\n",
pname, dept );
EXEC SQL UPDATE staff SET job = 'Clerk' (4)
WHERE CURRENT OF c1;
CHECKERR ("UPDATE STAFF");
} else {
printf ("%-10.10s in dept. %2d will be DELETED!\n",
pname, dept);
EXEC SQL DELETE FROM staff WHERE CURRENT OF c1;
CHECKERR ("DELETE");
} /* endif */
} while ( 1 );
EXEC SQL CLOSE c1; (5)
CHECKERR ("CLOSE CURSOR");
EXEC SQL ROLLBACK;
CHECKERR ("ROLLBACK");
printf( "\nOn second thought -- changes rolled back.\n" );
EXEC SQL CONNECT RESET;
CHECKERR ("CONNECT RESET");
return 0;
}
/* end of program : OPENFTCH.SQC */
Identification Division.
Program-ID. "openftch".
Data Division.
Working-Storage Section.
copy "sqlca.cbl".
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 pname pic x(10).
01 dept pic s9(4) comp-5.
01 userid pic x(8).
01 passwd.
49 passwd-length pic s9(4) comp-5 value 0.
49 passwd-name pic x(18).
EXEC SQL END DECLARE SECTION END-EXEC.
77 errloc pic x(80).
Procedure Division.
Main Section.
display "Sample COBOL program: OPENFTCH".
* Get database connection information.
display "Enter your user id (default none): "
with no advancing.
accept userid.
if userid = spaces
EXEC SQL CONNECT TO sample END-EXEC
else
display "Enter your password : " with no advancing
accept passwd-name.
* Passwords in a CONNECT statement must be entered in a VARCHAR format
* with the length of the input string.
inspect passwd-name tallying passwd-length for characters
before initial " ".
EXEC SQL CONNECT TO sample USER :userid USING :passwd
END-EXEC.
move "CONNECT TO" to errloc.
call "checkerr" using SQLCA errloc.
EXEC SQL DECLARE c1 CURSOR FOR (1)
SELECT name, dept FROM staff
WHERE job='Mgr'
FOR UPDATE OF job END-EXEC.
EXEC SQL OPEN c1 END-EXEC (2)
move "OPEN" to errloc.
call "checkerr" using SQLCA errloc.
* call the FETCH and UPDATE/DELETE loop.
perform Fetch-Loop thru End-Fetch-Loop
until SQLCODE not equal 0.
EXEC SQL CLOSE c1 END-EXEC. (5)
move "CLOSE" to errloc.
call "checkerr" using SQLCA errloc.
EXEC SQL ROLLBACK END-EXEC.
move "ROLLBACK" to errloc.
call "checkerr" using SQLCA errloc.
display "On second thought -- changes rolled back.".
EXEC SQL CONNECT RESET END-EXEC.
move "CONNECT RESET" to errloc.
call "checkerr" using SQLCA errloc.
End-Main.
go to End-Prog.
Fetch-Loop Section.
EXEC SQL FETCH c1 INTO :pname, :dept END-EXEC. (3)
if SQLCODE not equal 0
go to End-Fetch-Loop.
if dept greater than 40
go to Update-Staff.
Delete-Staff.
display pname, " in dept. ", dept,
" will be DELETED!".
EXEC SQL DELETE FROM staff WHERE CURRENT OF c1 END-EXEC.
move "DELETE" to errloc.
call "checkerr" using SQLCA errloc.
go to End-Fetch-Loop.
Update-Staff.
display pname, " in dept. ", dept,
" will be demoted to Clerk".
EXEC SQL UPDATE staff SET job = 'Clerk' (4)
WHERE CURRENT OF c1 END-EXEC.
move "UPDATE" to errloc.
call "checkerr" using SQLCA errloc.
End-Fetch-Loop. exit.
End-Prog.
stop run.
program openftch
implicit none
include 'sqlenv.f'
EXEC SQL INCLUDE SQLCA
EXEC SQL BEGIN DECLARE SECTION
character*10 pname
integer*2 dept
character*8 userid
character*18 passwd
EXEC SQL END DECLARE SECTION
character*80 errloc
print *, 'Sample FORTRAN program: OPENFTCH'
print *, 'Enter your user id (default none):'
read 101, userid
101 format (a8)
if( userid(1:1) .eq. ' ' ) then
EXEC SQL CONNECT TO sample
else
print *, 'Enter your password :'
read 101, passwd
EXEC SQL CONNECT TO sample USER :userid USING :passwd
end if
errloc = 'CONNECT'
call checkerr (sqlca, errloc, *999)
EXEC SQL DECLARE c1 CURSOR FOR (1)
c SELECT name, dept FROM staff WHERE job='Mgr'
c FOR UPDATE OF job
EXEC SQL OPEN c1 (2)
errloc = 'OPEN'
call checkerr (sqlca, errloc, *999)
100 continue
EXEC SQL FETCH c1 INTO :pname, :dept (3)
if (sqlcode .ne. 0) goto 200
if (dept .gt. 40) then
print *, pname, ' in dept. ', dept, ' will be demoted to Clerk.'
EXEC SQL UPDATE staff SET job = 'Clerk' (4)
c WHERE CURRENT OF c1
errloc = 'UPDATE'
call checkerr (sqlca, errloc, *999)
endif
if (dept .lt. 41) then
print *, pname, ' in dept. ', dept, ' will be DELETED!'
EXEC SQL DELETE FROM staff WHERE CURRENT OF c1
errloc = 'DELETE'
call checkerr (sqlca, errloc, *999)
endif
goto 100
200 EXEC SQL CLOSE c1 (5)
errloc = 'CLOSE'
call checkerr (sqlca, errloc, *999)
EXEC SQL ROLLBACK
errloc = 'ROLLBACK'
call checkerr (sqlca, errloc, *999)
print *, 'On second thought -- changes rolled back.'
EXEC SQL CONNECT RESET
errloc = 'CONNECT RESET'
call checkerr (sqlca, errloc, *999)
999 stop
end