CLOSE Statement [ESQL] [SP]

(back to top)

• Example 1 closes cursors in Embedded SQL:

  EXEC SQL CLOSE employee_cursor;
  EXEC SQL CLOSE :cursor_var;

• Example 2 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