CREATE PROCEDURE db2admin.get_accounts_info ( IN io_id_estimate INTEGER,
                                    IN io_id_sestimate INTEGER,
                                    IN io_id_gr INTEGER,
                                    IN io_id_item INTEGER,
                                    IN io_id_sitem INTEGER,
                                    IN io_id_expense VARCHAR( 5 ) )
    SPECIFIC db2admin.get_accounts_info
    DYNAMIC RESULT SETS  1 

P1: BEGIN
    -- Объявить переменные
    DECLARE io_est_name VARCHAR( 100 ) DEFAULT '';
    DECLARE io_sest_name VARCHAR( 100 ) DEFAULT '';
    DECLARE io_gr_name VARCHAR( 100 ) DEFAULT '';
    DECLARE io_item_name VARCHAR( 100 ) DEFAULT '';
    DECLARE io_sitem_name VARCHAR( 100 ) DEFAULT '';
    DECLARE io_exp_name VARCHAR( 100 ) DEFAULT '';

    DECLARE rec_count INT DEFAULT  0 ;

    DECLARE cursor1 CURSOR WITH RETURN FOR
        SELECT b.id_estimate,b.id_sestimate,b.id_gr,b.id_item,b.id_sitem,b.id_expense,
               e.est_name,se.sest_name,gr.gr_name,i.item_name,si.sitem_name,n.exp_name
        FROM budjet b
           left join estimate e on e.id_estimate=b.id_estimate
           left join subestimate se on se.id_sestimate=b.id_sestimate
           left join itemgroup gr on gr.id_gr=b.id_gr
           left join itemekr i on i.id_item=b.id_item
           left join subitemekr si on si.id_sitem=b.id_sitem
           left join nexpense n on n.id_expense=b.id_expense
        WHERE b.id_estimate=io_id_estimate and
           COALESCE(b.id_sestimate, 0 )=COALESCE(io_id_sestimate, 0 ) and
           COALESCE(b.id_gr, 0 )=COALESCE(io_id_gr, 0 ) and
           COALESCE(b.id_item, 0 )=COALESCE(io_id_item, 0 ) and
           COALESCE(b.id_sitem, 0 )=COALESCE(io_id_sitem, 0 ) and
           COALESCE(b.id_expense,'')=COALESCE(io_id_expense,'')
        ORDER BY b.id_estimate;
    
    --Определение кол-ва записей
    SELECT count(b.id_estimate) INTO rec_count FROM budjet b
    WHERE b.id_estimate=io_id_estimate and
          COALESCE(b.id_sestimate, 0 )=COALESCE(io_id_sestimate, 0 ) and
          COALESCE(b.id_gr, 0 )=COALESCE(io_id_gr, 0 ) and
          COALESCE(b.id_item, 0 )=COALESCE(io_id_item, 0 ) and
          COALESCE(b.id_sitem, 0 )=COALESCE(io_id_sitem, 0 ) and
          COALESCE(b.id_expense,'')=COALESCE(io_id_expense,'')
    GROUP BY b.id_estimate;
    --#################################################################

    --Объявить врем таблицу
    DECLARE GLOBAL TEMPORARY TABLE SESSION.GET_ACCOUNTS_INFO_GTT(io_id_estimate INTEGER,
                                                             io_id_sestimate INTEGER,
                                                             io_id_gr INTEGER,
                                                             io_id_item INTEGER,
                                                             io_id_sitem INTEGER,
                                                             io_id_expense VARCHAR( 5 ),
                                                             io_est_name VARCHAR( 100 ),
                                                             io_sest_name VARCHAR( 100 ),
                                                             io_gr_name VARCHAR( 100 ),
                                                             io_item_name VARCHAR( 100 ),
                                                             io_sitem_name VARCHAR( 100 ),
                                                             io_exp_name VARCHAR( 100 ))
    WITH REPLACE IN DB2ADMIN;
    --#################################################################

    OPEN cursor1;
    WHILE rec_count> 0  do
        FETCH cursor1 INTO io_id_estimate,
                           io_id_sestimate,
                           io_id_gr,
                           io_id_item,
                           io_id_sitem,
                           io_id_expense,
                           io_est_name,io_sest_name,io_gr_name,io_item_name,io_sitem_name,io_exp_name;
        INSERT INTO SESSION.GET_ACCOUNTS_INFO_GTT VALUES ( io_id_estimate,
                                                           io_id_sestimate,
                                                           io_id_gr,
                                                           io_id_item,
                                                           io_id_sitem,
                                                           io_id_expense,
                                                           io_est_name,io_sest_name,io_gr_name,io_item_name,io_sitem_name,io_exp_name);
        SET rec_count = rec_count -  1 ;
    END WHILE;
    CLOSE cursor1;
    --Объявить курсор на GTT
    BEGIN
    DECLARE cursor2 CURSOR WITH RETURN FOR
       SELECT * FROM SESSION.GET_ACCOUNTS_INFO_GTT;
       OPEN cursor2;
    END;
    --#################################################################
END P1