mysql 存储过程实例 动态表名 if判断

DROP PROCEDURE IF EXISTSCountSkyDDSAuditByMonth;

DELIMITER //

CREATE PROCEDURE CountSkyDDSAuditByMonth(

       INtableName VARCHAR(64),

              INinsertType VARCHAR(6)

 

)

BEGIN

DECLARE totalMsg,totalMsgLength INT;

DECLARE    auditTime,msgId,sourcePlatCode,receivePlatCode,topicNameVARCHAR(64);

 

DECLARE finished INT DEFAULT 0;

DECLARE auditCursor CURSOR FOR SELECTCOUNT(AUDIT_TIME),SUM(DATA_LENGTH),MSG_ID,SOURCE_PLAT_CODE,RECEIVE_PLAT_CODE,TOPIC_NAME,AUDIT_TIMEFROM v_wondyfox GROUP BYMSG_ID,SOURCE_PLAT_CODE,RECEIVE_PLAT_CODE,TOPIC_NAME,MONTH(AUDIT_TIME) ORDER BYAUDIT_TIME DESC;

DECLARE CONTINUE HANDLER FOR NOT FOUND SETfinished = 1;

 

       DROPVIEW IF EXISTS v_wondyfox;

  SET@sql=concat('create view v_wondyfox as select * from ', tableName);

 PREPARE stmt1 FROM  @sql;

 EXECUTE stmt1 ;

 DEALLOCATE PREPARE stmt1;

      

       OPENauditCursor;

getAudit: LOOP

FETCH auditCursor INTOtotalMsg,totalMsgLength,msgId,sourcePlatCode,receivePlatCode,topicName,auditTime;

IF finished = 1 THEN

LEAVE getAudit;

END IF;

SET @current =DATE_FORMAT(CURRENT_TIMESTAMP,'%Y-%m-%d %H:%m:%s');

SET @currentDay =DATE_FORMAT(CURRENT_TIMESTAMP,'%Y-%m-%d');

CASE insertType

       WHEN'insert' THEN

              INSERTINTO skydds_transfer_log_count VALUES (null, auditTime, msgId, sourcePlatCode,receivePlatCode, topicName, totalMsg, totalMsgLength,'m',@current);

       WHEN'update' THEN

              UPDATEskydds_transfer_log_count SET AUDIT_TIME = auditTime, MSG_ID =msgId,SOURCE_PLAT_CODE = sourcePlatCode, RECEIVE_PLAT_CODE = receivePlatCode,TOPIC_NAME= topicName,TOTAL_MSG = totalMsg, TOTAL_MSG_LENGTH = totalMsgLength,CREATE_TIME = @current, COUNT_TYPE = 'm' WHEREDATE_FORMAT(AUDIT_TIME,'%Y-%m-%d') = DATE_FORMAT(@currentDay,'%Y-%m-%d');

       ELSE

              INSERTINTO skydds_transfer_log_count VALUES (null, auditTime, msgId, sourcePlatCode,receivePlatCode, topicName, totalMsg, totalMsgLength,'m',@current);

 

END CASE;

 

COMMIT;

END LOOP getAudit;

CLOSE auditCursor;

 

END //

DELIMITER ;