The following examples close cursors in embedded SQL.
EXEC SQL CLOSE employee_cursor;
EXEC SQL CLOSE :cursor_var;
The following procedure uses a cursor.
CREATE PROCEDURE TopCustomer (OUT TopCompany CHAR(35), OUT TopValue INT)
BEGIN
DECLARE err_notfound EXCEPTION
FOR SQLSTATE '02000';
DECLARE curThisCust CURSOR FOR
SELECT CompanyName, CAST( sum(SalesOrderItems.Quantity *
Products.UnitPrice) AS INTEGER) VALUE
FROM Customers
LEFT OUTER JOIN SalesOrders
LEFT OUTER JOIN SalesOrderItems
LEFT OUTER JOIN Products
GROUP BY CompanyName;
DECLARE ThisValue INT;
DECLARE ThisCompany CHAR(35);
SET TopValue = 0;
OPEN curThisCust;
CustomerLoop:
LOOP
FETCH NEXT curThisCust
INTO ThisCompany, ThisValue;
IF SQLSTATE = err_notfound THEN
LEAVE CustomerLoop;
END IF;
IF ThisValue > TopValue THEN
SET TopValue = ThisValue;
SET TopCompany = ThisCompany;
END IF;
END LOOP CustomerLoop;
CLOSE curThisCust;
END