Delete Data Using Cursors
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.
A cursor does not have to be declared for update to perform a cursor delete.
Example: Updating and Deleting with Cursors
The following 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;
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