Example of Dynamically Specified Cursor Names
A dynamically specified cursor name (a cursor name specified using a host string variable) can be used to scan a table that contains rows that are related hierarchically, such as a table of employees and managers. In a relational database, this structure must be represented as a relationship between two columns. In an employee table, typically employees are assigned an ID number. One of the columns in the employee table contains the ID number of each employee's manager. This column establishes the relationships between employees and managers.
To use dynamically specified cursor names to scan this kind of table:
• Write a routine that uses a cursor to retrieve all the employees that work for a manager.
• Create a loop that calls this routine for each row that is retrieved and dynamically specifies the name of the cursor to be used by the routine.
The following example retrieves rows from the employee table that has the following format:
exec sql declare employee table
(ename varchar(32),
title varchar(20),
manager varchar(20));
This program scans the employee table and prints out all employees and the employees that they manage:
/* This program will print out, starting with
** the top manager,
** each manager and who they manage for the entire
** company. */
exec sql include sqlca;
/* main program */
exec sql begin declare section;
topmanager character string(21)
exec sql end declare section;
exec sql connect enterprise/db2udb;
exec sql whenever not found goto closedb;
exec sql whenever sqlerror call sqlprint;
/* Retrieve top manager */
exec sql select ename into :topmanager from employeewhere title = 'President';
print 'President', topmanager
call printorg(1, topmanager);
/* start with top manager */
/* closedb */
closedb:
exec sql disconnect;
/* This subroutine retrieves and displays employees
** who report to a given manager. This subroutine is
** called recursively to determine if a given
** employee is also a manager and if so,
** it will display who reports to them.
*/
subroutine printorg(level, manager)
level integer
exec sql begin declare section;
manager character string(21)
ename character string(33)
title character string(21);
cname character string(4);
exec sql end declare section;
/* set cursor name to 'c1', 'c2', ... */
cname = 'c' + level
exec sql declare :cname cursor for
select ename, title, manager from employee
where manager = :manager
order by ename;
exec sql whenever not found goto closec;
exec sql open :cname;
loop
exec sql fetch :cname into :ename, :title,
:manager;
/* Print employee's name and title */
print title, ename
/* Find out who (if anyone) reports to this employee*/
printorg(level+1, ename);
end loop
closec:
exec sql close :cname;
return;