У меня есть сомнения относительно процедур в Db2. Я создал хранимую процедуру с помощью «CREATE PROCEDURE», а внутри у меня есть другая, объявленная с помощью «DECLARE PROCEDURE». Но эта процедура, созданная с помощью declare, не может быть вызвана с помощью «CALL».
Когда я пытаюсь запустить процедуру, Db2 возвращает:
[Код: -440, состояние SQL: 42884] Ошибка SQL DB2: SQLCODE = -440, SQLSTATE = 42884, SQLERRMC = DMTLDBR.SP_DASH_CALENDARIO.PROCURA_DIA_UTIL_POST_DIA1; ПРОЦЕДУРА, ДРАЙВЕР = 4.25.1301
Как мне вызвать заявленную процедуру? (PROCURA_DIA_UTIL_POST_DIA1)
Код:
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