602SQL Documentation Index  
<­Sequences UpSQL Language in 602SQL Transactions and Savepoints­>

Cursors in SQL

Cursors offer a tool to process query results. Cursors allow you to read, modify or delete rows from the result. A cursor is always set on a certain row of the result, before a certain row or after the last row.

A local cursor can be declared in any block statement. You can work with global cursors, queries stored in a database application, created using the CREATE VIEW statement or in the query designer.

Each cursor must be first opened using the OPEN statement. Read rows using the FETCH statement. Make modifications using the UPDATE CURRENT OF and DELETE CURRENT OF statements. Close the cursor using the CLOSE statement.

Local cursors that are not closed manually are automatically closed on leaving the block statement they are declared.

Another method of opening a cursor is the FOR statement. You do not have to declare the NOT FOUND handler when using FOR.

If an error occurs when opening a cursor (e.g. not a syntax or compiler error, but some value cannot be converted, insufficient privileges, etc.), some cursors behaves differently. If the cursor is non-editable or INSENSITIVE, a new copy of the cursor will be created and must be evaluated, therefore the error will appears. If the cursor is editable and SENSITIVE, this error will appear when passing through the cursor in a FOR loop or when moving to the record using FETCH. If the error is caught using the CONTINUE handler, the cursor will be set on the next record. Otherwise a ROLLBACK is executed.


Example:

PROCEDURE Best (OUT MaxCompany CHAR(20), OUT MaxOrder INT); 
BEGIN 
  DECLARE err_notfound BIT DEFAULT FALSE;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' BEGIN SET err_notfound=TRUE; END;
  DECLARE cur CURSOR FOR 
    SELECT Companies.address, CAST (SUM(Orders.ordered) AS INT) 
    FROM Companies, Orders 
    WHERE Orders.company=Companies.company 
    GROUP BY Companies.company 
  FOR READ ONLY; 
  DECLARE ThisValue INT; 
  DECLARE ThisCompany CHAR(20); 
  SET MaxOrder = 0; 
  OPEN cur; 
  LabelLoop: LOOP 
     FETCH NEXT FROM cur INTO ThisCompany, ThisValue; 
     IF err_notfound IS TRUE THEN LEAVE LabelLoop; END IF; 
     IF ThisValue > MaxOrder THEN 
        SET MaxOrder = ThisValue; 
        SET MaxCompany = ThisCompany; 
      END IF; 
  END LOOP LabelLoop; 
  CLOSE cur; 
END ;

List of topics:

<­Sequences UpSQL Language in 602SQL Transactions and Savepoints­>