Using Cursors to Delete Data
The cursor version of the DELETE statement has the following syntax:
EXEC SQL DELETE FROM tablename
WHERE CURRENT OF cursor_name;
The DELETE statement deletes the current row. The cursor must be positioned on a row (as the result of a FETCH statement) before a cursor delete can be performed. After the row is deleted, the cursor points to the position after the row (and before the next row) in the set. To advance the cursor to the next row, issue the FETCH statement.
You do not have to declare a cursor for update to perform a cursor delete.
Example of Updating and Deleting with Cursors
This example illustrates updating and deleting with a cursor:
exec sql include sqlca;
exec sql begin declare section;
name character_string(15);
salary float;
exec sql end declare section;
exec sql whenever sqlerror stop;
exec sql connect personnel/rdb;
exec sql declare c1 cursor for
select ename, sal
from employee
for update of sal;
exec sql open c1;
exec sql whenever not found goto closec1;
loop while more rows
exec sql fetch c1 into :name, :salary;
print name, salary;
/* Increase salaries of all employees earning less
than 60,000. */
if salary < 60,000 then
print 'Updating ', name;
exec sql update employee
set sal = sal * 1.1
where current of c1;
/* Fire all employees earning more than 300,000. */
else if salary > 300,000 then
print 'Terminating ', name;
exec sql delete from employee
where current of c1;
end if;
end loop;
closec1:
exec sql close c1;
exec sql disconnect;
Last modified date: 01/30/2023