declare-cursor

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

declare-cursor [2015/09/23 20:27]
declare-cursor [2015/09/23 20:27] (current)
Line 1: Line 1:
 +====== DECLARE CURSOR Statement - PL/HQL Reference ======
  
 +You can use DECLARE CURSOR statement to declare a cursor using a dynamic SQL.
 +
 +**Syntax**:
 +
 +<code language=sql>​
 +DECLARE name CURSOR FOR | AS | IS dynamic_sql_string | select_statement;​
 +</​code>​
 +
 +**Parameters:​**
 +
 +| **Parameter** | **Type** | **Value** | **Description** |
 +| dynamic_sql_string | VARCHAR | Variable or expression | Dynamic SQL to define the cursor |
 +| select_statement | | | SQL SELECT statement to define the cursor |
 +
 +**Notes:**
 +
 +  * //​dynamic_sql_string//​ expression is evaluated at cursor open time, not declare time.
 +
 +**Example 1:**
 +
 +Using a dynamic SQL string:
 +
 +<code language=sql>​
 +DECLARE tabname VARCHAR DEFAULT '​db.orders';​
 +DECLARE id INT;
 +DECLARE cur CURSOR FOR '​SELECT id FROM ' || tabname;
 +OPEN cur;
 +FETCH cur INTO id;
 +WHILE SQLCODE=0 THEN
 +  PRINT id;
 +  FETCH cur INTO id;
 +END WHILE;
 +CLOSE cur;
 +</​code>​
 +
 +**Example 2:**
 +
 +Using a SQL SELECT statement:
 +
 +<code language=sql>​
 +DECLARE id INT;
 +DECLARE cur CURSOR FOR SELECT id FROM db.orders;
 +OPEN cur;
 +FETCH cur INTO id;
 +WHILE SQLCODE=0 THEN
 +  PRINT id;
 +  FETCH cur INTO id;
 +END WHILE;
 +CLOSE cur;
 +</​code>​
 +
 +**Compatibility:​** IBM DB2, MySQL, Teradata.
 +
 +**Version:​** PL/HQL 0.01
 +
 +**See also:**
 +  * [[open|OPEN]]
 +  * [[fetch|FETCH]]
 +  * [[close|CLOSE]]
 +  * [[sqlcode|SQLCODE]]