This shows you the differences between two versions of the page.
— |
open [2015/09/23 20:27] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ====== OPEN Statement - PL/HQL Reference ====== | ||
+ | OPEN statement opens a cursor. | ||
+ | |||
+ | **Syntax**: | ||
+ | |||
+ | <code language=sql> | ||
+ | OPEN cursor_name [FOR expression | select_statement]; | ||
+ | </code> | ||
+ | |||
+ | **Description:** | ||
+ | | cursor_name | The name of the previously declared cursor if FOR clause is not specified | | ||
+ | | FOR expression | Variable or expression that contains a dynamic SQL | | ||
+ | | FOR select_statement | SELECT statement | | ||
+ | |||
+ | **Examples:** | ||
+ | |||
+ | Open the previously declared cursor: | ||
+ | |||
+ | <code language=sql> | ||
+ | DECLARE tabname VARCHAR(20) 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> | ||
+ | |||
+ | Open a cursor using a dynamic SQL: | ||
+ | |||
+ | <code language=sql> | ||
+ | DECLARE tabname VARCHAR(20) DEFAULT 'db.orders'; | ||
+ | DECLARE id INT; | ||
+ | OPEN cur FOR 'SELECT id FROM ' || tabname; | ||
+ | FETCH cur INTO id; | ||
+ | WHILE SQLCODE=0 THEN | ||
+ | PRINT id; | ||
+ | FETCH cur INTO id; | ||
+ | END WHILE; | ||
+ | CLOSE cur; | ||
+ | </code> | ||
+ | |||
+ | **Compatibility:** Oracle, IBM DB2, Teradata, SQL Server, PostgreSQL, MySQL and Netezza. | ||
+ | |||
+ | **See also:** | ||
+ | * [[declare-cursor|DECLARE CURSOR]] | ||
+ | * [[fetch|FETCH]] | ||
+ | * [[close|CLOSE]] | ||
+ | * [[sqlcode|SQLCODE]] |