Cursor examples

Example of a cursor with ORDER BY

The cursor CUR_CONTACTS defines a section of the CONTACTS table containing the last name, first name and department for all customers with customer numbers greater than 103. The rows are to be sorted in ascending sequence by department and, within the departments in descending sequence by last name.

DECLARE cur_contacts CURSOR FOR SELECT lname, fname, department FROM contacts WHERE cust_num > 103 ORDER BY department ASC, lname DESC

The cursor is opened with the OPEN statement

OPEN cur_contacts

At this point, the cursor table includes the following rows:

lname	fname	department
Buschmann	Anke
Bauer	Xaver
Heinlein	Robert	Purchasing
Davis	Mary	Purchasing

Null values are shown in the table above as empty fields. When rows are sorted using ORDER BY in SESAM/SQL, null values are regarded as being less than any non-null value.

In an ESQL program, the cursor table can be read row by row in a loop. The column values are passed to the host variables NAME, FIRSTNAME and DEPT.

FETCH cur_contacts INTO :LNAME, :FIRSTNAME INDICATOR :IND_FIRSTNAME, :DEPT INDICATOR :IND_DEPT

Example of SQL data manipulation using a cursor

Use the cursor CUR_VAT to select all services for which no VAT is calculated. It is specified with WITH HOLD so that it remains open even after a COMMIT WORK provided that it was open at the end of the transaction:

DECLARE CUR_VAT CURSOR WITH HOLD FOR SELECT service_num, service_text, vat FROM service WHERE vat=0.00 OPEN cur_vat

The following cursor table is produced when the cursor is opened:

service_num	service_text	vat
4	Systems analysis	0.00
5	Database design	0.00
10	Travel expenses	0.00

A VAT rate of 15% is to be charged for these services. A sequence of FETCH and UPDATE statements allows the rows of the SERVICE table to be updated. FETCH NEXT positions the cursor on the next row.

FETCH NEXT cur_vat INTO :SERVICE_NUM, :SERVICE_TEXT INDICATOR :IND_SERVICE_TEXT :VAT INDICATOR :IND_VAT UPDATE service SET vat=0.15 WHERE CURRENT OF cur_vat

The cursor is then positioned on the second row of the cursor table:

FETCH NEXT cur_vat INTO :SERVICE_NUM, :SERVICE_TEXT INDICATOR :IND_SERVICE_TEXT :VAT INDICATOR :IND_VAT UPDATE service SET vat=0.15 WHERE CURRENT OF cur_vat

The transaction is closed with COMMIT WORK. Because of the WITH HOLD clause, the cursor can be positioned on the third row of the cursor table by issuing a FETCH statement immediately after COMMIT WORK.