Declaring a Cursor

DECLARE CURSOR statement

A DECLARE CURSOR statement associates a cursor by name with the results of a data query. In OLAP DML, it has the following syntax.

SQL DECLARE cursor-name CURSOR FOR select-statement

Cursor name requirements

A cursor name can consist of 1 to 18 alphanumeric characters or the symbols @, _, $, or #. A name containing @, $, or # must be enclosed in single quotes. The first character cannot be a number.

Example: Declaring a cursor

In the following example, the cursor declaration selects rows from a table named Products that has columns for product identification codes (PROD_ID) and descriptive labels (PROD_NAME). A third column, SUGGESTED_PRICE, is used in a WHERE clause to limit the returned rows to only those in which the suggested price is greater than $20.00.

sql declare highprice cursor for -
   select Prod_ID, Prod_Name from products -
      where Suggested_Price > 20

Re-using a cursor name

If you try to declare a cursor with the same name as one that is already declared, but with a different SELECT statement, then an error is signaled. You must first free the cursor with a COMMIT or ROLLBACK statement when you wish to associate it with a different selection.

Definition: Input host variables

Instead of providing literal values in the WHERE clause of a SELECT statement, you can use the values of input host variables. An input host variable is supplied by the host program as a parameter to the SELECT statement. Note that the value of an input host variable is assigned when the cursor is opened, not when it is declared.

An input host variable can be any expression preceded by a colon. However, if you specify a multidimensional expression, such as a variable or dimension, then the first value in status is used.

Using input host variables

When you use input host variables in a WHERE clause to match the data in a relational table, any required conversions between data types is performed wherever conversion is possible.

The following are examples of expressions that can be used as input host variables.

Type of Expression	Example
Variable (database or local)	:set_price
Dimension	:prod
Qualified data reference	:units(prod 'P8', geog 'G12', - time 'T36')
Program argument	:newval
Text expression	:joinchars('first_name' 'last_name')
Arithmetic expression	:intpart(6.3049) + 1
User-defined function	:getgeog

Example: Using input host variables

The following program fragment modifies the SQL command shown previously. Instead of using an explicit value in the WHERE clause, it uses the value of a local variable named SET_PRICE.

variable set_price short
set_price = 20
sql declare highprice cursor for -
   select Prod_ID, Prod_Name from products -
      where Suggested_Price > :set_price

Using conjunctions in a WHERE clause

Because both the OLAP DML and SQL include AND and OR as part of their language syntax, you must use parentheses when using one of these conjunctions. Otherwise, the command might be ambiguous and produce unexpected results. Place the parentheses around the input host variable preceding AND or OR.

If a host variable expression begins with a parenthesis, then the matching right parenthesis is interpreted as the end of the host variable expression. If you plan to add more text to the expression after a matching right parenthesis, then you must enclose the entire expression with an extra set of parentheses.

Example: Using conjunctions in a WHERE clause

The following program fragment uses the values of two arguments to limit the range of values selected for the time dimension.

arg start_time date
arg end_time date
      .
      .
      .
sql declare addtime cursor for -
   select Timestamp from shipments -
      where Timestamp between :(start_time) -
      and :end_time