如何调用在另一个过程中声明的过程？

Question

我对DB2中的过程有疑问。我使用“create procedure”创建了一个存储过程，在这个存储过程中，我还有一个使用“declare procedure”声明的存储过程。但是，这个由declare创建的过程不能使用“call”调用。

当我尝试运行该过程时，Db2返回：

[代码：-440,SQL状态：42884]DB2 SQL错误:sqlcode=-440,sqlstate=42884,sqlerrmc=dmtldbr.sp_dash_calendario.procura_dia_util_post_dia1；过程，驱动程序=4.25.1301

create or replace PROCEDURE           "SP_DASH_CALENDARIO" (IN P_MES INTEGER)
BEGIN

------
DECLARE SQLSTATE                       CHAR(5) DEFAULT ' ';

DECLARE V_ANO_MES                      DECIMAL(6); 
DECLARE V_ID_DIA                       ANCHOR DATA TYPE TO DMTLDBR.TB_DIM_DIADIA.ID_DIA;

DECLARE V_VAL_REAL_CAL                 ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_VENDASDIARIO.VAL_TONLIQUIDO;
DECLARE V_VAL_TONVNEMA                 ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_VENDASDIARIO.VAL_TONVNEMA;
DECLARE V_VAL_FAT357_CAL               ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_VENDASDIARIO.VAL_FAT357; 
DECLARE V_VAL_PERDA_FAT_CAL            ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_VENDASDIARIO.VAL_PERDA;

DECLARE V_SUM_VAL_REAL_CAL             ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_DASH_CALENDARIO.VAL_REAL_CAL;
DECLARE V_SUM_VAL_TONVNE_CAL           ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_DASH_CALENDARIO.VAL_TONVNE_CAL;
DECLARE V_SUM_VAL_TONLIQUIDO_ZBCL_CAL  ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_DASH_CALENDARIO.VAL_TONLIQUIDO_ZBCL_CAL;
DECLARE V_SUM_VAL_TONLIQUIDO_CAL       ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_DASH_CALENDARIO.VAL_TONLIQUIDO_CAL;
DECLARE V_SUM_VAL_FAT357_CAL           ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_DASH_CALENDARIO.VAL_FAT357_CAL;   

DECLARE V_VAL_TONLIQUIDO_CAL           ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_DASH_CALENDARIO.VAL_TONLIQUIDO_CAL;
DECLARE V_VAL_TONVNE_CAL               ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_DASH_CALENDARIO.VAL_TONVNE_CAL;
DECLARE V_VAL_TONLIQUIDO_ZBCL_CAL      ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_DASH_CALENDARIO.VAL_TONLIQUIDO_ZBCL_CAL; 

DECLARE V_VAL_PLANO_FAT_CAL            ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_PLANEJ_MAN.VAL_PLANO; 
DECLARE V_VAL_PLANO                    ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_PLANEJ_MAN.VAL_PLANO; 
DECLARE V_TOTAL_PLANO_DESOVA_SEMANAL   ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_PLANEJ_MAN.TOTAL_PLANO_DESOVA_SEMANAL;

DECLARE V_ID_DIASEMANA                 ANCHOR DATA TYPE TO DMTLDBR.TB_DIM_DIADIA.ID_DIASEMANA;
DECLARE V_FLG_HOLIDAY                  ANCHOR DATA TYPE TO DMTLDBR.TB_DIM_DIADIA.FLG_HOLIDAY;

DECLARE V_IND_LEGENDA                  DECIMAL(1);
DECLARE V_VARIANTE                     ANCHOR DATA TYPE TO DMTLDBR.TB_DIM_PRODVARIANTE.ID_VARIANTE;

DECLARE V_VAL_PERDA                    ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_VENDASDIARIO.VAL_TONLIQUIDO;
DECLARE V_VAL_DESOVA                   ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_VENDASDIARIO.VAL_TONLIQUIDO;
DECLARE V_VAL_UHT                      ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_VENDASDIARIO.VAL_TONLIQUIDO;
DECLARE V_VAL_SUCO                     ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_VENDASDIARIO.VAL_TONLIQUIDO;
DECLARE V_VAL_REQ                      ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_VENDASDIARIO.VAL_TONLIQUIDO;
DECLARE V_FAT_UHT                      ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_VENDASDIARIO.VAL_FAT357;  
DECLARE V_FAT_REQ                      ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_VENDASDIARIO.VAL_FAT357; 


DECLARE V_ID_DIA_INI                   ANCHOR DATA TYPE TO DMTLDBR.TB_DIM_DIADIA.ID_DIA;
DECLARE V_ID_DIA_FIM                   ANCHOR DATA TYPE TO DMTLDBR.TB_DIM_DIADIA.ID_DIA;
DECLARE FLAG_DIA_UTIL                  BOOLEAN;
DECLARE V_SUM_VAL_TONVNEMA_CAL         ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_DASH_CALENDARIO.VAL_TONVNEMA_CAL;
DECLARE V_SUM_VAL_FAT357               ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_VENDASDIARIO.VAL_FAT357;
DECLARE V_SUM_VAL_ABATIMENTOREPORTADO  ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_VENDASDIARIO.VAL_ABATIMENTOREPORTADO;
DECLARE V_SUM_VAL_ICMSZFMREPORTADO     ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_VENDASDIARIO.VAL_ICMSZFMREPORTADO;

DECLARE V_PROC                         ANCHOR DATA TYPE TO DMTLDBR.TB_TMP_LOG_EXECUTION_PROC.SYNCPOINT_PROC;
SET V_PROC = 'SP_DASH_CALENDARIO';

-- =========================================================================================================
--   P R O C   P R O C U R A _ D I A _ U T I L _ P O S T _ D I A 1
--
--  OBTEM O PRIMEIRO DIA ÚTIL DO MÊS (QUE NÃO SEJA DOMINGO NEM FERIADO)
--
-- =========================================================================================================
BEGIN
DECLARE PROCEDURE PROCURA_DIA_UTIL_POST_DIA1(IN P_ID_MES INTEGER)
BEGIN

        DECLARE V_ACHOU  DECIMAL(1) DEFAULT 0;
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND BEGIN END;

        SET V_ID_DIA = NULL;

        L1: LOOP -- (LP01)

        BEGIN  -- (BE02.)

                DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' BEGIN END;

                SELECT MIN(ID_DIA) INTO V_ID_DIA
                FROM   DMTLDBR.TB_DIM_DIADIA
                WHERE  ID_MES     = P_ID_MES
                AND    ID_DIASEMANA  <> 1  -- DOMINGO
                AND    FLG_HOLIDAY = 0;

        END; -- (BE02.)

        IF SQLSTATE = '00000' THEN
                LEAVE L1;
         --SET V_ACHOU = 1;
        END IF;

        END LOOP L1; -- (LP01.)

END;
END;

CALL DMTLDBR.PROCURA_DIA_UTIL_POST_DIA1(201909);

END

Answer 1

复合SQL（编译）语句要求内部声明/语句的严格顺序。
例如：

--#SET TERMINATOR @
CREATE OR REPLACE PROCEDURE TEST_LOCAL(P_I INT)
BEGIN
    -- SQL-variable-declarations
    DECLARE L_I INT;

    -- DECLARE-CURSOR-statements
    DECLARE C1 CURSOR FOR VALUES 1;

    -- procedure-declaration
    DECLARE PROCEDURE TEST_LOCAL_NESTED(P_J INT) BEGIN END;

    -- handler-declarations
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN END;

    -- SQL-procedure-statements
    CALL TEST_LOCAL_NESTED(P_I);
END@

对于每个BEGIN END块：

1-st:allsql-variable-declarations
2-nd:alldeclare-cursor-statements
3-rd:allprocedure-declarations
4-th:allhandler-declarations