User Tools

Site Tools

A PCRE internal error occured. This might be caused by a faulty plugin

Sidebar

**HPL/SQL is included to Apache Hive since version 2.0** * [[home|Home]]\\ * [[why|Why HPL/SQL]]\\ * [[features|Key Features]]\\ * [[start|Get Started]]\\ * [[doc|HPL/SQL Reference]]\\ * [[download|Download]]\\ * [[new|What's New]]\\ * [[about|About]]

allocate-cursor

====== 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]]