Cursors are used to retrieve rows one at a time from a query or stored procedure that has multiple rows in its result set. A cursor is a handle or an identifier for the query or procedure, and for a current position within the result set.
Managing a cursor is similar to managing a file in a programming language. The following steps are used to manage cursors:
row not found warning
is returned, signaling the end of the result set.By default, cursors are automatically closed at the end of a transaction (on COMMIT or ROLLBACK statements). Cursors that are opened using the WITH HOLD clause will be kept open for subsequent transactions until they are explicitly closed.
A cursor can be positioned at one of three places:
When a cursor is opened, it is positioned before the first row. The cursor position can be moved using the FETCH command (see "FETCH statement"). It can be positioned to an absolute position either from the start or from the end of the query results (using FETCH ABSOLUTE, FETCH FIRST, or FETCH LAST). It can also be moved relative to the current cursor position (using FETCH RELATIVE, FETCH PRIOR, or FETCH NEXT). The NEXT keyword is the default qualifier for the FETCH statement.
There are special positioned versions of the UPDATE and DELETE statements that can be used to update or delete the row at the current position of the cursor. If the cursor is positioned before the first row or after the last row, a no current row of cursor
Cursor positioning problems Inserts and some updates to DYNAMIC SCROLL cursors can cause problems with cursor positioning. The database engine will not put inserted rows at a predictable position within a cursor unless there is an ORDER BY clause on the SELECT statement. In some cases, the inserted row will not appear at all until the cursor is closed and opened again.With SQL Anywhere, this occurs if a temporary table had to be created to open the cursor (see "Temporary tables used in query processing" for a description).The UPDATE statement may cause a row to move in the cursor. This will happen if the cursor has an ORDER BY that uses an existing index (a temporary table is not created). Using STATIC SCROLL cursors alleviates these problems but is more expensive. |
The following procedure uses a cursor on a SELECT statement. It illustrates several features of the stored procedure language. It is based on the same query used in the ListCustomerValue procedure described in "Returning result sets from procedures".
CREATE PROCEDURE TopCustomerValue
( OUT TopCompany CHAR(36),
OUT TopValue INT )
BEGIN
-- 1. Declare the "error not found" exception
DECLARE err_notfound
EXCEPTION FOR SQLSTATE '02000';
-- 2. Declare variables to hold
-- each company name and its value
DECLARE ThisName CHAR(36);
DECLARE ThisValue INT;
-- 3. Declare the cursor ThisCompany
-- for the query
DECLARE ThisCompany CURSOR FOR
SELECT company_name,
CAST( sum( sales_order_items.quantity *
product.unit_price ) AS INTEGER )
AS value
FROM customer
INNER JOIN sales_order
INNER JOIN sales_order_items
INNER JOIN product
GROUP BY company_name;
-- 4. Initialize the values of TopValue
SET TopValue = 0;
-- 5. Open the cursor
OPEN ThisCompany;
-- 6. Loop over the rows of the query
CompanyLoop:
LOOP
FETCH NEXT ThisCompany
INTO ThisName, ThisValue;
IF SQLSTATE = err_notfound THEN
LEAVE CompanyLoop;
END IF;
IF ThisValue > TopValue THEN
SET TopCompany = ThisName;
SET TopValue = ThisValue;
END IF;
END LOOP CompanyLoop;
-- 7. Close the cursor
CLOSE ThisCompany;
END
The TopCustomerValue procedure has the following notable features:
For more information about exceptions, see "Errors and warnings in procedures and triggers".
The LOOP construct in the TopCompanyValue procedure is a standard form, exiting after the last row is processed. You can rewrite this procedure in a more compact form using a FOR loop. The FOR statement combines several aspects of the above procedure into a single statement.
CREATE PROCEDURE TopCustomerValue2(
OUT TopCompany CHAR(36),
OUT TopValue INT )
BEGIN
-- Initialize the TopValue variable
SET TopValue = 0;
-- Do the For Loop
CompanyLoop:
FOR CompanyFor AS ThisCompany
CURSOR FOR
SELECT company_name AS ThisName ,
CAST( sum( sales_order_items.quantity *
product.unit_price ) AS INTEGER )
AS ThisValue
FROM customer
INNER JOIN sales_order
INNER JOIN sales_order_items
INNER JOIN product
GROUP BY ThisName
DO
IF ThisValue > TopValue THEN
SET TopCompany = ThisName;
SET TopValue = ThisValue;
END IF;
END FOR CompanyLoop;
END