ALLOCATE Statement | VantageCloud Lake - ALLOCATE - Teradata VantageCloud Lake

Allows a calling stored procedure to fetch result sets returned by a called procedure.

ANSI Compliance

ANSI/ISO SQL:2011-compliant.

Required Privileges

None.

Invocation

Executable.

Stored procedures.

Syntax

ALLOCATE cursor_name CURSOR FOR PROCEDURE procedure_name ;

Syntax Elements

cursor_name

The name of a previously opened cursor to be referenced.

procedure_name

The name of the SQL stored procedure being called.

Usage Notes

• If the cursor was originally opened with NO SCROLL, then the cursor is positioned at “first row” of the result set.

  For example, if the original cursor returned 10 rows and the stored procedure read 3 of those rows, 7 rows are returned. The result set begins at the 4th row, which appears as the first row.

• If the cursor was originally opened with SCROLL, then the cursor is positioned immediately after the most recent row fetched.

  For example, if the cursor returned all 10 rows, the initial position is the 4th row.

• If the cursor was opened with SCROLL, then the caller can reposition before the recent row.
• If the cursor was opened with NO SCROLL or SCROLL and there are multiple result sets, each set is fetched sequentially.
• If the procedure did not create any result sets or all result sets have been fetched, the SQLSTATE completion condition of '02001' is returned (that is, no additional dynamic result sets returned).
• On reaching the end of the first result set, the FETCH statement gets an SQLSTATE of '02000' (that is, no data). To get the next result set, if any, the open cursor must be closed.
• If there are no additional result sets, closing cursor causes the CLOSE statement to return with an SQLSTATE completion condition of '02001' (that is, no additional dynamic result sets returned).
• If there are additional results sets, then a warning is returned: '0100D' (that is, additional dynamic result sets returned).

Example: Using ALLOCATE

REPLACE PROCEDURE alloc007()
DYNAMIC RESULT SETS 1
BEGIN
    DECLARE EmpNo0  SMALLINT;
    DECLARE ProjId0 CHAR(8);
    DECLARE WkEnd0  DATE;
    DECLARE Hours0  DECIMAL(4,1);
    DECLARE ee0 CHAR(8);
    DECLARE ff0 VARCHAR(25);
    DECLARE gg0 DATE;
    DECLARE hh0 DATE;
    DECLARE ii0 DATE;
    CALL drs_temp5();
    ALLOCATE my_fetch CURSOR FOR PROCEDURE drs_temp5;
    FETCH FIRST FROM my_fetch INTO empno0,projid0,wkend0,hours0;
    INSERT INTO charges_temp2(empno0,projid0,wkend0,hours0);
    WHILE (SQLCODE = 0)
    DO
        FETCH NEXT FROM my_fetch INTO empno0,projid0,wkend0,hours0;
        IF (SQLCODE = 0)
        THEN
            INSERT INTO charges_temp2(empno0,projid0,wkend0,hours0);
        END IF;
    END WHILE;
    -- close the current result set cursor
    CLOSE my_fetch;
    -- see if there are result sets
    WHILE (SQLSTATE = '0100D')
    DO
        -- allocate the next one.
        ALLOCATE sp2 CURSOR FOR PROCEDURE drs_temp5;
        WHILE (SQLCODE = 0)
        DO
            FETCH NEXT FROM sp2 into ee0,ff0,gg0,hh0,ii0;
            IF (SQLCODE = 0)
            THEN
                INSERT INTO project_temp1(ee0,ff0,gg0,hh0,ii0);
            END IF;
        END WHILE;
        CLOSE sp2;
    END WHILE;
END;