This shows you the differences between two versions of the page.
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]] |