Examples: Declare Cursor

The following are Declare Cursor statement examples:

1. Declare a cursor for a retrieval of employees from the shoe department, ordered by name (ascending) and salary (descending). (This can also be specified as a select loop.)

   exec sql declare cursor1 cursor for
       select ename, sal
       from employee
       where dept = 'shoes'
       order by 1 asc, 2 desc;

2. Declare a cursor for updating the salaries and departments of employees currently in the shoe department.

   exec sql declare cursor2 cursor for
       select ename, sal
       from employee
       where dept = 'shoes'
       for update of sal, dept;

3. Declare a cursor for updating the salaries of employees whose last names are alphabetically like a given pattern.

   searchpattern = 'a%';
   exec sql declare cursor3 cursor for
       select ename, sal
       from employee
       where ename like :searchpattern
       for update of sal;
   ...
   exec sql open cursor3;

   In the above example, the variable, searchpattern, must be a valid declaration in the host language at the time the statement, open cursor3, is executed. It also must be a valid embedded SQL declaration at the point where the cursor is declared.

4. Declare a cursor to print the results of a retrieval for runtime viewing and salary changes.

   exec sql declare cursor4 cursor for
       select ename, age, eno, sal
       from employee
       for direct update of sal;
   
   exec sql whenever sqlerror stop;
   exec sql whenever not found goto close_cursor;
   exec sql open cursor4;
   
   loop /* loop is broken when NOT FOUND becomes true. */
   exec sql fetch cursor4
           into :name, :age, :idno, :salary;
       print name, age, idno, salary;
       print 'New salary';
       read newsal;
       if (newsal > 0 and newsal <> salary) then
           exec sql update employee
               set sal = :newsal
               where current of cursor4;
       end if;
   end loop;
   close_cursor:
     exec sql close cursor4;

5. Declare a cursor for retrieval of specific data. The for update clause refers to column name, sal, and not, res.

   exec sql declare cursor5 cursor for
       select ename, sal as res
       from employee
       where eno between :eno_low and :eno_high
       for update of sal;
   . . .
   
   loop while more input
       read eno_low, eno_high;
   
   exec sql open cursor5;
   
   print and process rows;
   end loop;

6. Declare two cursors for the department and employee tables, and open them in a master-detail fashion.

   exec sql declare master_cursor cursor for
       select * from dept
       order by dno;
   
   exec sql declare detail_cursor cursor for
       select * from employee
       where edept = :dno
       order by ename;
       
   exec sql open master_cursor;
   
   loop while more department
   
   exec sql fetch master_cursor
       into :dname, :dno, :dfloor, :dsales;
   
   if not found break loop;
   
   /*
       ** For each department retrieve all the
       ** employees and display the department
       ** and employee data.
   */
   
   exec sql open detail_cursor;
   
   loop while more employees
   
   exec sql fetch detail_cursor
       into :name, :age, :idno, :salary, :edept;
       /*
       ** For each department retrieve all the
       ** employees and display the department
       ** and employee data.
       */
   
   process and display data;
   
   end loop;
       exec sql close detail_cursor;
   end loop;
   
   exec sql close master_cursor;

7. Declare a cursor that is a union of three tables with identical typed columns (the columns have different names). As each row returns, record the information and add it to a new table. Ignore all errors.

   exec sql declare shapes cursor for
       select boxname, box# from boxes
       where boxid > 100
       union
       select toolname, tool# from tools
       union
       select nailname, nail# from nails
       where nailweight > 4;
   
   exec sql open shapes;
   exec sql whenever not found goto done;
   
   loop while more shapes
   
   exec sql fetch shapes into :name, :number;
       record name and number;
       exec sql insert into hardware
       (:name, :number);
   
   end loop;
   
   done:
   
   exec sql close shapes;