5. Embedded OpenSQL : Data Manipulation with Cursors : An Example of Cursor Processing
 
Share this page                  
An Example of Cursor Processing
This simple example of cursor processing prints the names and salaries of all the employees in the employee table and updates the salary of employees earning less than $10,000.
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

/* The WHENEVER NOT FOUND statement causes the loop
**   to be broken as soon as a row is not fetched. 
*/

exec sql fetch c1 into :name, :salary;

print name, salary;

if salary < 10000 then
    exec sql update employee
      set salary = 10000
      where current of c1;

end if;
end loop;

closec1:

exec sql close c1;

exec sql disconnect;