Cursors and Retrieve Loops Compared
Use cursors:
• When a program needs to scan a table to update or delete rows
• When a program requires access to other tables (or cursors) while processing rows
• When more than one table needs to be scanned simultaneously ("parallel query")
• When more than one table needs to be scanned in a nested fashion, for example, in a master-detail application
Use retrieve loops if the program is scanning the rows to:
• Generate a report, or
• Accumulate general statistics
For straightforward reading operations, the retrieve loop runs faster than a cursor. However, you cannot execute other queries inside a retrieve loop.
The following example shows the use of a retrieve loop and a cursor to scan a table:
begin program
## ename character_string(21)
## salary float
## eno, thatsall integer
## ingres "personnel"
## range of e is employee
## /* retrieve loop */
## retrieve (ename = e.empname, eno = e.empnum,
## salary = e.#salary)
## sort by #ename
## {
print ename, eno, salary
## }
## /* cursor retrieve */
## declare cursor c1 for
## retrieve (e.empname, e.empnum, e.#salary)
## sort by empname
## open cursor c1
loop until thatsall = 1
## retrieve cursor c1 (ename, eno, salary)
## inquire_ingres (thatsall = endquery)
if thatsall = 0 then
print ename, eno, salary
end if
end loop
## close cursor c1
## exit
end program
Last modified date: 01/30/2023