ALLOCATE | Teradata Vantage - ALLOCATE - Advanced SQL Engine - Teradata Database

Purpose

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

Invocation

Executable.

Stored procedures.

Syntax

ALLOCATE cursor_name CURSOR FOR PROCEDURE procedure_name ;

cursor_name

The name of a previously opened cursor to be referenced.

procedure_name

The name of the SQL stored procedure being called.

ANSI Compliance

ANSI/ISO SQL:2011-compliant.

Authorization

None.

Rules

• 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 the rows, then 7 rows are returned. The result set begins at the 4th row, but it appears as if it’s 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 prior to 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 of the result sets have been fetched, the SQLSTATE completion condition of '02001' is returned (that is, no additional dynamic result sets returned).
• Upon reaching the end of the first result set, the FETCH statement will be given an SQLSTATE of '02000' (that is, no data). To get the next result set, if any, the open cursor should be closed.
• If there are no additional result sets, the closing of the cursor will cause 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 will be 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;