Dynamically Specified Cursor Names
The following example illustrates the use of host variables to dynamically declare cursor names, and the use of a recursive routine to scan a table that contains a "tree" structure (in this example, an organization chart).
In this example, the table "orgchart" contains three columns: employee name, title, and the name of the employee's manager. The program uses a subroutine that displays the employees that report to a manager. If an employee is also a manager, the subroutine calls itself to list the employees he or she manages.
The subroutine declares a cursor for each level it scans. The cursor name is defined as "C" plus the number of the level being scanned (C1, C2, and so on).
## character-string ename(25)
## integer level
## ingres "mydatabase"
/* First, print the president's name */
## retrieve (ename=orgchart.employee)
## where orgchart.title="president"
print "the president is ", ename
/* initialize level for recursive calls */
level=0
## begin transaction
printorg(level, ename)
## end transaction
## /*****************************
## * display employees **
## * for each manager **
## ****************************/
printorg(alevel, amanager)
## character amanager(25), cursorname(2), cname(25),
## character title(25), cmanager(25)
## integer alevel, end_of_query, ecount
## /* is this employee a manager? */
## retrieve (ecount=count(orgchart.manager
## where orgchart.manager=amanager))
## /* no, return */
if ecount=0
return
endif
cursorname = "c" + char(alevel+1)
## declare cursor cursorname for retrieve
## (orgchart.employee,
## orgchart.title,
## orgchart.manager)
## where orgchart.manager=amanager
## open cursor cursorname
/* cursor loop reads all employees for manager */
end_of_query=0
loop while end_of_query = 0
## retrieve cursor cursorname (cname, ctitle, cmanager)
## inquire_ingres(end_of_query=endquery)
if end_of_query = 0
indent to appropriate level, print cname, ctitle
/* see if this employee is a manager */
call printorg(alevel+1, cname)
end if
end while loop
## close cursor cursorname
return