DECLARE STATEMENT

DECLARE STATEMENT — declares a SQL statement identifier associated with connection

Synopsis

EXEC SQL [ AT connection_name ] DECLARE statement_name STATEMENT

Description

DECLARE STATEMENT declares a SQL statement identifier to be associated with connection.

DECLARE CURSOR with an SQL statement identifier can be written before PREPARE.

Parameters

connection_name

A database connection name established by the CONNECT command.

If the AT clause is omitted, the SQL statement identifier is associated with the DEFAULT connection.

statement_name

The name of the SQL statement identifier, either as an SQL identifier or a host variable.

Notes

The AT clause can be used with both DECLARE STATEMENT and other dynamic SQL statements. The following table illustrates how it affects the selected database connection.

Table 35.6. Scenario

Usage Scenario DECLARE STATEMENT Other Dynamic Statements Target Database
1 Without AT Without AT Default connection
2 With AT that connects to con1 Without AT con1
3 With AT that connects to con1 With AT that connects to con2 con1
4 Without AT With AT that connects to con2 con2

In scenario 4, DECLARE STATEMENT will be ignored.

Examples

EXEC SQL CONNECT TO postgres AS con1;
EXEC SQL AT con1 DECLARE sql_stmt STATEMENT;
EXEC SQL DECLARE cursor_name CURSOR FOR sql_stmt;
EXEC SQL PREPARE sql_stmt FROM :dyn_string;
EXEC SQL OPEN cursor_name;
EXEC SQL FETCH cursor_name INTO :column1;
EXEC SQL CLOSE cursor_name;

Compatibility

DECLARE STATEMENT is a PostgreSQL extension of the SQL standard, but can be used in Oracle and DB2.

See Also

CONNECT, DECLARE, OPEN