Using a Positioned Delete

Go Up to Deleting Multiple Rows

Use cursors to select rows for deletion when users should decide deletion on a row-by-row basis, and displaying pre- or post-modification values between row updates. Updating through a cursor is a seven-step process:

1. Declare host-language variables needed for the delete operation.

2. Declare a cursor describing the rows to retrieve for possible deletion, and include the FOR UPDATE clause. For more information about declaring and using cursors, see Selecting Multiple Rows.

3. Open the cursor.

4. Fetch a row.

5. Display current values and prompt for permission to delete.

6. Delete the currently selected row using the WHERE CURRENT OF clause to specify the name of the cursor.

7. Repeat steps 3 to 7 until all selected rows are deleted.

For example, the following C code deletes rows in the CITIES table that are in North America only if a user types Y when prompted:

. . .
EXEC SQL
BEGIN DECLARE SECTION;
char cityname[26];
EXEC SQL
END DECLARE SECTION;
char response[5];
. . .
main ()
{
EXEC SQL
DECLARE DELETECITY CURSOR FOR
SELECT CITY,
FROM CITIES
WHERE CONTINENT = 'North America';
EXEC SQL
OPEN DELETECITY;
while (!SQLCODE)
{
EXEC SQL
FETCH DELETECITY INTO :cityname;
if (SQLCODE)
{
if (SQLCODE == 100)
{
printf('Deletions complete.');
EXEC SQL
COMMIT;
EXEC SQL
CLOSE DELETECITY;
EXEC SQL
DISCONNECT ALL:
}
isc_print_sqlerr(SQLCODE, isc_status);
EXEC SQL
ROLLBACK;
EXEC SQL
DISCONNECT ALL;
exit(1);
}
printf("\nDelete %s (Y/N)?", cityname);
gets(response);
if(response[0] == 'Y' || response == 'y')
{
EXEC SQL
DELETE FROM CITIES
WHERE CURRENT OF DELETECITY;
if(SQLCODE && (SQLCODE != 100))
{
isc_print_sqlerr(SQLCODE, isc_status);
EXEC SQL
ROLLBACK;
EXEC SQL
DISCONNECT;
exit(1);
}
}
}