Indexing for Table Joins - Relational Database Index Design and the Optimizers - page 153

SQL 8.12

Program B+ Subsequent transactions
DECLARE CURSORI1 CURSOR FOR
SELECT CNO, INO, IEUR
FROM INVOICE
WHERE IEUR = :IEURLAST
AND
INO > :INOLAST
AND
IEUR > :IEUR
ORDER BY IEUR DESC, INO
WE WANT 20 ROWS PLEASE

DECLARE CURSORI2 CURSOR FOR
SELECT CNO, INO, IEUR
FROM INVOICE
WHERE IEUR < :IEURLAST
AND
IEUR > :IEUR
ORDER BY IEUR DESC, INO
WE WANT 20 ROWS PLEASE

READ IEURLAST, INOLAST

OPEN CURSORI1
DO

FETCH CURSORI1 max 20 times

SELECT CNAME, CTYPE

FROM CUST

WHERE CNO = :CNO

AND
CCTRY = :CCTRY

DO END
CLOSE CURSORI1

OPEN CURSORI2
DO

FETCH CURSORI2 until 20 result rows

SELECT CNAME, CTYPE

FROM CUST

WHERE CNO = :CNO

AND
CCTRY = :CCTRY

DO END
CLOSE CURSORI2

SAVE / INSERT IEUR, INO the values of the last line
displayed to the user