Was this helpful?
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