Anchor row data type variable from declare cursor

Question

How can I declare variable using anchor data type (row) from a declared cursor in db2?

create or replace PROCEDURE     "SP_ATUALIZA_ID_DISTRIBUIDOR_FT"
    BEGIN

        DECLARE C_ID_DIST_ERRADOS CURSOR FOR
                SELECT DISTINCT 
                    F.ID_MES,
                    F.ID_DISTRIBUIDOR_SO,
                    D.CNPJ_DISTRIBUIDOR_SO,
                    F.ID_COMPANIA
                FROM    
                    DMTLDBR.TB_FATO_VENDAS_SELLOUT F,
                    DMTLDBR.TB_DIM_ECDISTRIBUIDOR_SO D
                WHERE ID_MES <= 201309 AND                                
                  F.ID_DISTRIBUIDOR_SO = D.ID_DISTRIBUIDOR_SO AND
                  F.ID_COMPANIA <> D.ID_COMPANIA
                ORDER BY ID_MES;
         BEGIN
         DECLARE REG_C_ID_DIST_ERRADOS ANCHOR ROW OF C_ID_DIST_ERRADOS;

         OPEN C_ID_DIST_ERADOS
         ...
         END;

    END;

I've a procedure that is declaring a internal cursor, which will be used to fetch results and insert into a variable 'reg_c_id_dist_errados'. I've tried to use "anchor row of" but db2 has alerted me.

Answer 1

You can anchor a type to a row of a table , among other things. There are different ways to use these things depending on your requirements.

Your query has a join so you can declare a type to match each row from the cursor, and you can then declare strongly-typed cursor-type for your rowtype. Later you can assign a query to a variable of that cursor-type.

You can pass cursors as parameters to stored procedures, and you can have a procedure return a cursor type, and you can declare variables of each of the types you create.

You don't show whether you want the result-set of the procedure to be a cursor-type, or whether you want to process the result-set inside the stored-procedure in typed variables.

Study the Db2 Knowledge Centre for your version of Db2-LUW to learn about strongly typed cursors and how to use them in SQL PL.

There is an example online (shown as a module, but you can use the same techniques in a stored procedures) in this file "modules.db2" which also appears under the SAMPLES directory on your DB2-LUW server if you installed the samples component at installation time.

You can see the example online at the example modules.sql

Here is one suggestion for using user defined types for a row-type that matches the cursor in your code:

--#SET TERMINATOR @


-- this type represents a row that might be returned in the cursor. 
-- Choose your own name for the type.
-- Choose the correct data-types for your tables.

create or replace type DE_ROW_T as row
(
id_mes bigint,
ID_DISTRIBUIDOR_SO bigint,
CNPJ_DISTRIBUIDOR_SO bigint,
ID_COMPANIA bigint
)@



create or replace type C_ID_DIST_ERRADOS_CURSOR_T as DE_ROW_T CURSOR@

create or replace PROCEDURE     "SP_ATUALIZA_ID_DISTRIBUIDOR_FT"
language sql
specific SP_ATUALIZA_ID_DISTRIBUIDOR_FT
    BEGIN

        DECLARE V_ROW DE_ROW_T;
        DECLARE C_ID_DIST_ERRADOS  C_ID_DIST_ERRADOS_CURSOR_T;


        SET C_ID_DIST_ERRADOS = CURSOR FOR 
                SELECT DISTINCT 
                    F.ID_MES,
                    F.ID_DISTRIBUIDOR_SO,
                    D.CNPJ_DISTRIBUIDOR_SO,
                    F.ID_COMPANIA
                FROM    
                    DMTLDBR.TB_FATO_VENDAS_SELLOUT F,
                    DMTLDBR.TB_DIM_ECDISTRIBUIDOR_SO D
                WHERE ID_MES <= 201309 AND                                
                  F.ID_DISTRIBUIDOR_SO = D.ID_DISTRIBUIDOR_SO AND
                  F.ID_COMPANIA <> D.ID_COMPANIA
                ORDER BY ID_MES;

         OPEN C_ID_DIST_ERRADOS;
         FETCH C_ID_DIST_ERRADOS INTO v_row;

    END@