Differences

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

--- allocate-cursor [2015/09/23 20:26]
+++ 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]]

HPL/SQL - Procedural SQL on Hadoop, NoSQL and RDBMS

User Tools

Site Tools

Differences

Page Tools