DECLARE CURSOR Examples
The following examples provide details.
Example 1:
The following example declares a cursor for retrieval of employees from the shoe department, ordered by name (ascending) and salary (descending):
## declare cursor cursor1 for
## retrieve (employee.empname, employee.salary)
## where employee.dept = "shoes"
## sort by #ename:asc, sal:desc
Example 2:
The following example declares a cursor for updating the salaries and departments of shoe department employees:
## declare cursor cursor2 for
## retrieve (employee.empname, employee.salary)
## where employee.dept = "shoes"
## for update of (salary, dept)
Example 3:
The following example declares a cursor for retrieval and update of employee information:
begin program
## eno integer
## age integer
## thatsall integer
## ename character_string(26)
## salary float
## newsalary float
## ingres "personnel"
## declare cursor cursor4 for
## retrieve (employee.empname, employee.#age,
## employee.empnum, employee.#salary)
## for direct update of (#salary)
## open cursor cursor4
loop while no errors and endquery not reached
## retrieve cursor cursor4 (ename, age, eno, salary)
## inquire_ingres (thatsall = endquery)
if thatsall = 0 then
print ename, age, eno, salary
print "enter new salary: "
read newsalary from terminal
if newsalary > 0 and newsalary != salary then
## replace cursor cursor4 (#salary = newsalary)
end if
end if
end loop
##close cursor cursor4
##exit
end program
Example 4:
In the following example, the "for update" clause refers to the column named "salary" and not result column "res". The variables "eno_low" and "eno_high" must have previously been declared:
## declare cursor cursor5 for
## retrieve (employee.empname, res = employee.salary)
## where employee.empnum >= eno_low and
## employee.empnum <= eno_high
## for update of (#salary)
loop while more input
read eno_low, eno_high
## open cursor cursor5
print and process rows
## close cursor cursor5
end loop
Example 5:
The following example declares two cursors for the "department" and "employee" tables and opens them in master-detail fashion:
## declare cursor master_cursor for
## retrieve (dept.all)
## sort by dno
## declare cursor detail_cursor for
## where employee.edept = dept.dno
## sort by empname
## begin transaction
## open cursor master_cursor
loop while more departments
## retrieve cursor master_cursor
## inquire_equel (thatsall = endquery)
if thatsall = 0 then
## /* for each department retrieve all the employees
## and display the department and employee data.*/
## open cursor detail_cursor
loop while more employees
## retrieve cursor detail_cursor
## (name, age, idno, salary, edept)
## /* for each department retrieve all the employees
## and display the department and employee data.*/
## inquire_equel (thatsall = endquery)
if thatsall = 0 then
process and display data
end if
end loop
## close cursor detail_cursor
end loop
## end transaction
Example 6:
The following example declares a cursor that is a union of three tables with identically typed columns (the columns have different names). The expression "one + 1" in the target list of the declare cursor must be assigned to a result column. This is not the case with the other target list items, which are verbatim table columns. The name "two" must be dereferenced (with a # sign), because there is a host variable of the same name. The host variable is used later, in the retrieve cursor statement, to receive the value of the result column. The sort key names mentioned in the sort by clause are result column names, and must be dereferenced if they have the same names as any host variables.
begin program
## age integer
## thatsall integer
## one integer
## two integer
## minage integer
## ename character_string(26)
## salary float
## ingres "personnel"
## declare cursor cursor7 for
## retrieve (#two = one + 1, employee.empname,
## employee.#age)
## where employee.age minage
## sort by empname, #age
one = 1
minage = 21
## open cursor cursor7
loop while no errors and endquery not reached
## retrieve cursor cursor7 (two, ename, age)
## inquire_ingres (thatsall = endquery)
if thatsall = 0 then
print two, ename, age
end if
end loop
## close cursor cursor7
## exit
end program
Last modified date: 01/30/2023