프로시저를 아래와 같이 생성한 후 실행을 하면 오류가 발생 합니다. 고수님들의 조언 부탁드립니다.
DROP PROCEDURE IF EXISTS tw_rems.procINSERT_TB_RTU_FW_UPDT;
CREATE PROCEDURE tw_rems.`procINSERT_TB_RTU_FW_UPDT`()
BEGIN
DECLARE insert_cnt int;
DECLARE result_cnt int;
DECLARE tb_rtu_fw_updt_select_cnt int;
DECLARE tb_rtu_fw_updt_insert_cnt int;
DECLARE procedure_exe_msg varchar(100);
SET @tb_rtu_fw_updt_select_cnt = (SELECT COUNT(*) FROM TB_RTU_FW_UPDT);
SELECT @tb_rtu_fw_updt_select_cnt;
SELECT A.IMEI
, B.FW_IDX
, B.REG_DT AS DOWN_DT
, A.INSTALL_DT
, 'E' AS INSTALL_STATUS
, A.REG_DT
, A.RTU_IDX AS REG_MEM_IDX
FROM (SELECT A.RTU_IDX
, A.IMEI
, A.FW_VERSION
, A.REG_DT
, A.USE_STATUS
, B.INSTALL_DT
FROM TB_RTU A
INNER JOIN (SELECT IMEI, MIN(RCV_DT) AS INSTALL_DT FROM TB_RTU_RCV_DATA GROUP BY IMEI) B ON A.IMEI = B.IMEI
) A
LEFT OUTER JOIN TB_RTU_FW B ON A.FW_VERSION = B.FW_VERSION
WHERE A.USE_STATUS <> 'C'
;
SET @tb_rtu_fw_updt_insert_cnt = ROW_COUNT();
SELECT @tb_rtu_fw_updt_insert_cnt;
IF @tb_rtu_fw_updt_insert_cnt > @tb_rtu_fw_updt_select_cnt THEN
INSERT INTO TB_RTU_FW_UPDT
( IMEI
, FW_IDX
, DOWN_DT
, INSTALL_DT
, INSTALL_STATUS
, REG_DT
, REG_MEM_IDX
)
(SELECT B.*
FROM TB_RTU_FW_UPDT A
RIGHT OUTER JOIN (SELECT A.IMEI
, B.FW_IDX
, B.REG_DT AS DOWN_DT
, A.INSTALL_DT
, 'E' AS INSTALL_STATUS
, A.REG_DT
, A.RTU_IDX AS REG_MEM_IDX
FROM (SELECT A.RTU_IDX
, A.IMEI
, A.FW_VERSION
, A.REG_DT
, A.USE_STATUS
, B.INSTALL_DT
FROM TB_RTU A
INNER JOIN (SELECT IMEI, MIN(RCV_DT) AS INSTALL_DT FROM TB_RTU_RCV_DATA GROUP BY IMEI) B ON A.IMEI = B.IMEI
) A
LEFT OUTER JOIN TB_RTU_FW B ON A.FW_VERSION = B.FW_VERSION
WHERE A.USE_STATUS <> 'C'
) B ON A.IMEI = B.IMEI AND A.FW_IDX = B.FW_IDX
WHERE A.IMEI IS NULL AND A.FW_IDX IS NULL
)
;
SET @insert_cnt = ROW_COUNT();
SELECT @insert_cnt;
SET @result_cnt = @tb_rtu_fw_updt_select_cnt + @insert_cnt;
SELECT @result_cnt;
IF @result_cnt = @tb_rtu_fw_updt_insert_cnt THEN
COMMIT;
SET @procedure_exe_msg = "PROCEDURE TB_RTU_FW_UPDT INSERT JOB OK";
SELECT @procedure_exe_msg;
ELSE
ROLLBACK;
SET @procedure_exe_msg = "PROCEDURE TB_RTU_FW_UPDT INSERT JOB ERROR";
SELECT @procedure_exe_msg;
END IF;
ELSE
SET @procedure_exe_msg = "PROCEDURE TB_RTU_FW_UPDT INSERT JOB SKIP";
SELECT @procedure_exe_msg;
END IF;
END;
호출: CALL tw_rems.procINSERT_TB_RTU_FW_UPDT();
첨부파일과 같은 오류가 발생합니다.