DROP PROCEDURE IF EXISTS tw_rems.procTB_RTU_ERROR_UPDT;
CREATE PROCEDURE tw_rems.`procTB_RTU_ERROR_UPDT`(OUT PROCEDURE_EXE_MSG VARCHAR(225))
BEGIN
DECLARE dTB_RTU_CNT INTEGER DEFAULT 0;
DECLARE dTB_RTU_ENRGY_TYPE_CNT INTEGER DEFAULT 0;
DECLARE dTB_RTU_ENRGY_TYPE INTEGER DEFAULT 0;
DECLARE dTB_RTU_FW_UPDT_CNT INTEGER DEFAULT 0;
DECLARE dTB_RTU_IP_ADDR_CNT INTEGER DEFAULT 0;
DECLARE dTB_RTU_RCV_DATA_CNT INTEGER DEFAULT 0;
DECLARE dTB_RTU_RESET_CNT INTEGER DEFAULT 0;
DECLARE dTB_RTU_SND_DATA_CNT INTEGER DEFAULT 0;
DECLARE sTB_RTU_CNT INTEGER DEFAULT 0;
DECLARE sTB_RTU_ENRGY_TYPE_CNT INTEGER DEFAULT 0;
DECLARE sTB_RTU_ENRGY_TYPE INTEGER DEFAULT 0;
DECLARE sTB_RTU_FW_UPDT_CNT INTEGER DEFAULT 0;
DECLARE sTB_RTU_IP_ADDR_CNT INTEGER DEFAULT 0;
DECLARE sTB_RTU_RCV_DATA_CNT INTEGER DEFAULT 0;
DECLARE sTB_RTU_RESET_CNT INTEGER DEFAULT 0;
DECLARE sTB_RTU_SND_DATA_CNT INTEGER DEFAULT 0;
DECLARE sumTB_RTU_CNT INTEGER DEFAULT 0;
DECLARE sumTB_RTU_ENRGY_TYPE_CNT INTEGER DEFAULT 0;
DECLARE sumTB_RTU_ENRGY_TYPE INTEGER DEFAULT 0;
DECLARE sumTB_RTU_FW_UPDT_CNT INTEGER DEFAULT 0;
DECLARE sumTB_RTU_IP_ADDR_CNT INTEGER DEFAULT 0;
DECLARE sumTB_RTU_RCV_DATA_CNT INTEGER DEFAULT 0;
DECLARE sumTB_RTU_RESET_CNT INTEGER DEFAULT 0;
DECLARE sumTB_RTU_SND_DATA_CNT INTEGER DEFAULT 0;
DECLARE done INT DEFAULT FALSE;
DECLARE chkIMEI VARCHAR(200);
DECLARE vIMEI VARCHAR(200);
DECLARE vENRGY VARCHAR(200);
DECLARE vMACHN VARCHAR(200);
DECLARE vMULTI VARCHAR(200);
DECLARE cur1 CURSOR FOR
SELECT A.IMEI
, B.ENRGY
, B.MACHN
, B.MULTI
FROM TB_RTU A
LEFT OUTER JOIN TB_RTU_ENRGY_TYPE B ON A.IMEI = B.IMEI
LEFT OUTER JOIN TB_REMS_AUTH_INFO C ON B.AUTH_IDX = C.AUTH_IDX
WHERE A.CTN IS NOT NULL
AND A.LOCATION IS NULL
AND A.BIZ_YEAR IS NULL
AND A.BIZ_DIV IS NULL
AND A.BIZ_REGION IS NULL
AND (C.CID IS NULL OR C.AUTH_KEY IS NULL)
;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE ;
SELECT "PROCEDURE procTB_RTU_ERROR_UPDT JOB SKIP"
INTO PROCEDURE_EXE_MSG
FROM DUAL;
OPEN cur1;
my_loop: LOOP
FETCH cur1
INTO vIMEI
, vENRGY
, vMACHN
, vMULTI
;
IF done THEN
LEAVE my_LOOP;
END IF;
IF vIMEI <> chkIMEI THEN
SET chkIMEI = vIMEI;
SELECT COUNT(*)
INTO sTB_RTU_CNT
FROM TB_RTU
WHERE IMEI = chkIMEI
;
SET sumTB_RTU_CNT = sumTB_RTU_CNT + sTB_RTU_CNT;
IF sTB_RTU_CNT > 0 THEN
DELETE FROM TB_RTU WHERE IMEI = chkIMEI;
SET dTB_RTU_CNT = dTB_RTU_CNT + (SELECT ROW_COUNT());
END IF;
SELECT COUNT(*)
INTO sTB_RTU_ENRGY_TYPE_CNT
FROM TB_RTU_ENRGY_TYPE
WHERE IMEI = chkIMEI
;
SET sumTB_RTU_ENRGY_TYPE_CNT = sumTB_RTU_ENRGY_TYPE_CNT + sTB_RTU_ENRGY_TYPE_CNT;
IF sTB_RTU_ENRGY_TYPE_CNT > 0 THEN
DELETE FROM TB_RTU_ENRGY_TYPE WHERE IMEI = chkIMEI;
SET dTB_RTU_ENRGY_TYPE_CNT = dTB_RTU_ENRGY_TYPE_CNT + (SELECT ROW_COUNT());
END IF;
SELECT COUNT(*)
INTO sTB_RTU_FW_UPDT_CNT
FROM TB_RTU_FW_UPDT
WHERE IMEI = chkIMEI
;
SET sumTB_RTU_FW_UPDT_CNT = sumTB_RTU_FW_UPDT_CNT + sTB_RTU_FW_UPDT_CNT;
IF sTB_RTU_FW_UPDT_CNT > 0 THEN
DELETE FROM TB_RTU_FW_UPDT WHERE IMEI = chkIMEI;
SET dTB_RTU_FW_UPDT_CNT = dTB_RTU_FW_UPDT_CNT + (SELECT ROW_COUNT());
END IF;
SELECT COUNT(*)
INTO sTB_RTU_IP_ADDR_CNT
FROM TB_RTU_IP_ADDR
WHERE IMEI = chkIMEI
;
SET sumTB_RTU_IP_ADDR_CNT = sumTB_RTU_IP_ADDR_CNT + sTB_RTU_IP_ADDR_CNT;
IF sTB_RTU_IP_ADDR_CNT > 0 THEN
DELETE FROM TB_RTU_IP_ADDR WHERE IMEI = chkIMEI;
SET dTB_RTU_IP_ADDR_CNT = dTB_RTU_IP_ADDR_CNT + (SELECT ROW_COUNT());
END IF;
SELECT COUNT(*)
INTO sTB_RTU_RCV_DATA_CNT
FROM TB_RTU_RCV_DATA
WHERE IMEI = chkIMEI
;
SET sumTB_RTU_RCV_DATA_CNT = sumTB_RTU_RCV_DATA_CNT + sTB_RTU_RCV_DATA_CNT;
IF sTB_RTU_RCV_DATA_CNT > 0 THEN
DELETE FROM TB_RTU_RCV_DATA WHERE IMEI = chkIMEI;
SET dTB_RTU_RCV_DATA_CNT = dTB_RTU_RCV_DATA_CNT + (SELECT ROW_COUNT());
END IF;
SELECT COUNT(*)
INTO sTB_RTU_RESET_CNT
FROM TB_RTU_RESET
WHERE IMEI = chkIMEI
;
SET sumTB_RTU_RESET_CNT = sumTB_RTU_RESET_CNT + sTB_RTU_RESET_CNT;
IF sTB_RTU_RESET_CNT > 0 THEN
DELETE FROM TB_RTU_RESET WHERE IMEI = chkIMEI;
SET dTB_RTU_RESET_CNT = dTB_RTU_RESET_CNT + (SELECT ROW_COUNT());
END IF;
SELECT COUNT(*)
INTO sTB_RTU_SND_DATA_CNT
FROM TB_RTU_SND_DATA
WHERE IMEI = chkIMEI
;
SET sumTB_RTU_SND_DATA_CNT = sumTB_RTU_SND_DATA_CNT + sTB_RTU_SND_DATA_CNT;
IF sTB_RTU_SND_DATA_CNT > 0 THEN
DELETE FROM TB_RTU_SND_DATA WHERE IMEI = chkIMEI;
SET dTB_RTU_SND_DATA_CNT = dTB_RTU_SND_DATA_CNT + (SELECT ROW_COUNT());
END IF;
END IF;
END LOOP;
IF sumTB_RTU_CNT = dTB_RTU_CNT AND
sumTB_RTU_ENRGY_TYPE_CNT = dTB_RTU_ENRGY_TYPE_CNT AND
sumTB_RTU_FW_UPDT_CNT = dTB_RTU_FW_UPDT_CNT AND
sumTB_RTU_IP_ADDR_CNT = dTB_RTU_IP_ADDR_CNT AND
sumTB_RTU_RCV_DATA_CNT = dTB_RTU_RCV_DATA_CNT AND
sumTB_RTU_RESET_CNT = dTB_RTU_RESET_CNT AND
sumTB_RTU_SND_DATA_CNT = dTB_RTU_SND_DATA_CNT
THEN
SELECT "PROCEDURE procTB_RTU_ERROR_UPDT JOB SUCCESS"
INTO PROCEDURE_EXE_MSG
FROM DUAL;
COMMIT;
ELSE
SELECT "PROCEDURE procTB_RTU_ERROR_UPDT JOB ERROR"
INTO PROCEDURE_EXE_MSG
FROM DUAL;
ROLLBACK;
END IF;
CLOSE cur1;
END;
위의 프로시져를 실행하면 정상적으로 수행은 되는데 최종 결과 값을 콘솔에 출력하는 방법이 어떻게 되나요.?
PROCEDURE_EXE_MSG에 메세지를 대입했는데 콘솔에 출력이 안되네요.