Icon OPEN

Opens a result set cursor.

Syntax
OPEN <CursorName> [USING <Value> [,<Value>]]

Usage
Use this statement to open a result set cursor on a previously-prepared SELECT statement. If the statement is parameterized, then you can use the USING clause to specify the values to use for the parameters in left-to-right order corresponding to how they were declared in the SELECT statement.

After opening a cursor, you can use the ROWCOUNT function to determine the number of rows in the cursor and the SENSITIVE function to determine if the cursor is a sensitive or asensitive cursor. See the Result Set Cursor Sensitivity topic for more information on cursor sensitivity.

Examples
-- This procedure looks up the sales tax
-- rate for a given state and county

CREATE FUNCTION LookupSalesTaxRate(IN State CHAR(2), IN County VARCHAR)
RETURNS DECIMAL
BEGIN
   DECLARE TempCursor CURSOR FOR stmt;
   DECLARE Result DECIMAL DEFAULT 0;

   PREPARE stmt FROM 'SELECT * FROM SalesTaxes WHERE State = ? AND County = ?';

   OPEN TempCursor USING State, County;

   IF (ROWCOUNT(TempCursor) > 0) THEN
      FETCH FIRST FROM TempCursor ('TaxRate') INTO Result;
   END IF;

   CLOSE TempCursor;

   RETURN Result;
END

SQL 2003 Standard Deviations
This statement deviates from the SQL 2003 standard in the following ways:

DeviationDetails
Dynamic SQLThe use of dynamic SQL for DDL, DML, and administrative statement execution instead of static SQL in procedures and functions is both an ElevateDB extension and a deviation from the standard.
Image