OPEN (Snowflake Scripting) | Snowflake Documentation (original) (raw)

ReferenceScripting referenceOPEN

Opens a cursor.

For more information on cursors, see Working with cursors.

See also:

DECLARE, FETCH, CLOSE

Syntax¶

OPEN [ USING (bind_variable_1 [, bind_variable_2 ...] ) ] ;

Where:

_cursorname_

The name of the cursor.

_bindvariable_

A bind variable holds a value to be used in the cursor’s query definition (e.g. in a WHERE clause).

An example of binding is included in the examples later in this section.

Usage notes¶

• The result set of a query can be thought of as a set of rows. Internally, opening a cursor executes the query, reads the rows, and positions an internal pointer to the first of the rows.
• As with any SQL query, if the query definition does not contain anORDER BY at the outermost level, then the result set has no defined order. When the result set for the cursor is created, its order persists until the cursor is closed. However, re-declaring or re-opening the cursor might produce the rows in a different order.
• Similarly, if a cursor is closed, and then the underlying table(s) are updated before it is re-opened, the result set can also change.

Examples¶

DECLARE c1 CURSOR FOR SELECT price FROM invoices; BEGIN OPEN c1; ...

The following shows how to bind a variable when opening a cursor:

DECLARE price_to_search_for FLOAT; price_count INTEGER; c2 CURSOR FOR SELECT COUNT(*) FROM invoices WHERE price = ?; BEGIN price_to_search_for := 11.11; OPEN c2 USING (price_to_search_for);

For a more complete example of using a cursor, seethe introductory cursor example.