This shows you the differences between two versions of the page.
— |
allocate-cursor [2015/09/23 20:26] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ====== ALLOCATE CURSOR Statement ====== | ||
+ | ALLOCATE CURSOR statement allows you to declare a cursor and associate it with a result set returned from a stored procedure. | ||
+ | |||
+ | **Syntax**: | ||
+ | |||
+ | <code language=sql> | ||
+ | ALLOCATE cursor_name CURSOR FOR PROCEDURE procedure_name; -- Teradata compatibility | ||
+ | | | ||
+ | ALLOCATE cursor_name CURSOR FOR RESULT SET locator_name; -- DB2 compatibility | ||
+ | </code> | ||
+ | |||
+ | **Example 1:** | ||
+ | |||
+ | Sample stored procedure returning a single result set: | ||
+ | |||
+ | <code language=sql> | ||
+ | CREATE PROCEDURE spOpenIssues | ||
+ | DYNAMIC RESULT SETS 1 | ||
+ | BEGIN | ||
+ | DECLARE cur CURSOR WITH RETURN FOR | ||
+ | SELECT id, name FROM issues; | ||
+ | OPEN cur; | ||
+ | END; | ||
+ | </code> | ||
+ | |||
+ | Call a stored procedure and process the returned result set (Teradata compatibility): | ||
+ | |||
+ | <code language=sql> | ||
+ | DECLARE id INT; | ||
+ | DECLARE name VARCHAR(30); | ||
+ | |||
+ | CALL spOpenIssues; | ||
+ | ALLOCATE c1 CURSOR FOR PROCEDURE spOpenIssues; | ||
+ | |||
+ | FETCH c1 INTO id, name; | ||
+ | WHILE (SQLCODE = 0) | ||
+ | DO | ||
+ | PRINT id || ' - ' || name; | ||
+ | FETCH c1 INTO id, name; | ||
+ | END WHILE; | ||
+ | CLOSE c1; | ||
+ | </code> | ||
+ | |||
+ | Call a stored procedure and process the returned result set (IBM DB2 compatibility): | ||
+ | |||
+ | <code language=sql> | ||
+ | DECLARE id INT; | ||
+ | DECLARE name VARCHAR(30); | ||
+ | DECLARE loc RESULT_SET_LOCATOR VARYING; | ||
+ | |||
+ | CALL spOpenIssues; | ||
+ | ASSOCIATE RESULT SET LOCATOR (loc) WITH PROCEDURE spOpenIssues; | ||
+ | ALLOCATE c1 CURSOR FOR RESULT SET loc; | ||
+ | |||
+ | FETCH c1 INTO id, name; | ||
+ | WHILE (SQLCODE = 0) | ||
+ | DO | ||
+ | PRINT id || ' - ' || name; | ||
+ | FETCH c1 INTO id, name; | ||
+ | END WHILE; | ||
+ | CLOSE c1; | ||
+ | </code> | ||
+ | |||
+ | **Example 2:** | ||
+ | |||
+ | Sample stored procedure returning multiple result sets: | ||
+ | |||
+ | <code language=sql> | ||
+ | CREATE PROCEDURE spOpenIssues2 | ||
+ | DYNAMIC RESULT SETS 2 | ||
+ | BEGIN | ||
+ | DECLARE cur CURSOR WITH RETURN FOR | ||
+ | SELECT id, name FROM issues; | ||
+ | DECLARE cur2 CURSOR WITH RETURN FOR | ||
+ | SELECT id, name FROM issues_hold; | ||
+ | OPEN cur; | ||
+ | OPEN cur2; | ||
+ | END; | ||
+ | </code> | ||
+ | |||
+ | Call a stored procedure and process 2 result sets (Teradata compatibility): | ||
+ | |||
+ | <code language=sql> | ||
+ | DECLARE id INT; | ||
+ | DECLARE name VARCHAR(30); | ||
+ | |||
+ | CALL spOpenIssues2; | ||
+ | |||
+ | -- First result set | ||
+ | ALLOCATE c1 CURSOR FOR PROCEDURE spOpenIssues2; | ||
+ | FETCH c1 INTO id, name; | ||
+ | WHILE (SQLCODE = 0) | ||
+ | DO | ||
+ | -- ... | ||
+ | FETCH c1 INTO id, name; | ||
+ | END WHILE; | ||
+ | CLOSE c1; | ||
+ | |||
+ | -- Second result set | ||
+ | ALLOCATE c2 CURSOR FOR PROCEDURE spOpenIssues2; | ||
+ | FETCH c2 INTO id, name; | ||
+ | WHILE (SQLCODE = 0) | ||
+ | DO | ||
+ | -- ... | ||
+ | FETCH c2 INTO id, name; | ||
+ | END WHILE; | ||
+ | CLOSE c2; | ||
+ | </code> | ||
+ | |||
+ | Call a stored procedure and process 2 result sets (IBM DB2 compatibility): | ||
+ | |||
+ | <code language=sql> | ||
+ | DECLARE id INT; | ||
+ | DECLARE name VARCHAR(30); | ||
+ | DECLARE loc1 RESULT_SET_LOCATOR VARYING; | ||
+ | DECLARE loc2 RESULT_SET_LOCATOR VARYING; | ||
+ | |||
+ | CALL spOpenIssues2; | ||
+ | ASSOCIATE RESULT SET LOCATOR (loc1, loc2) WITH PROCEDURE spOpenIssues2; | ||
+ | |||
+ | -- First result set | ||
+ | ALLOCATE c1 CURSOR FOR RESULT SET loc1; | ||
+ | FETCH c1 INTO id, name; | ||
+ | WHILE (SQLCODE = 0) | ||
+ | DO | ||
+ | -- ... | ||
+ | FETCH c1 INTO id, name; | ||
+ | END WHILE; | ||
+ | CLOSE c1; | ||
+ | |||
+ | -- Second result set | ||
+ | ALLOCATE c2 CURSOR FOR RESULT SET loc2; | ||
+ | FETCH c2 INTO id, name; | ||
+ | WHILE (SQLCODE = 0) | ||
+ | DO | ||
+ | -- ... | ||
+ | FETCH c2 INTO id, name; | ||
+ | END WHILE; | ||
+ | CLOSE c2; | ||
+ | </code> | ||
+ | |||
+ | **Compatibility:** IBM DB2 and Teradata | ||
+ | |||
+ | **Version:** HPL/SQL 0.3.11 | ||
+ | |||
+ | **See also:** | ||
+ | * [[open|OPEN]] | ||
+ | * [[fetch|FETCH]] | ||
+ | * [[close|CLOSE]] | ||
+ | * [[sqlcode|SQLCODE]] |