Was this helpful?
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: 04/03/2024