存储过程动态sql执行

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `Prc_TelSuccess_Snapshot_Update`$$

CREATE DEFINER=`ccstest`@`%` PROCEDURE `Prc_TelSuccess_Snapshot_Update`(IN backDate VARCHAR(8))
BEGIN
	DECLARE pBatchID VARCHAR(8) DEFAULT '305';-- 批处理batchId
	DECLARE pCASEID INT(11); 	 -- 案件ID
	DECLARE pcaseterms INT(11);	 -- 案件批次号
	DECLARE pTOUSERTYPE INT(11); -- 案件类型
	DECLARE done BOOLEAN DEFAULT FALSE;
	
	DECLARE currTable VARCHAR(100); -- 快照表
	DECLARE preTable  VARCHAR(100); -- 前一天的快照表
	DECLARE pNOTONDATEfrom VARCHAR(10); -- 出库日期from
	DECLARE pNOTONDATEto VARCHAR(10);   -- 出库日期to
	DECLARE pCASESUCCESS INT(11);       -- case电催成功标志
	
	
	-- 异常处理
	-- 退出存储工程
	-- =====================================================================
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
			GET DIAGNOSTICS CONDITION 1
			@pSqlState = RETURNED_SQLSTATE,@pErrMsg = MESSAGE_TEXT;	-- 获取异常处理的错误代码和错误信息
			-- 将获取的错误代码和错误信息插入到批处理log中
			-- ROLLBACK;					-- 回滚机制
			INSERT INTO d_runninglog VALUES('err_log',pBatchID,CONCAT('Prc_TelSuccess_dhcs_snapshot_Update错误代码(SQLSTATE)：',@pSqlState, '; 错误信息：', @pErrMsg),'SQLEXCEPTION',NOW());
			UPDATE m_event SET BATCHSTATUS = '90', STARTTIME = NOW(), RESULT = '执行失败。' WHERE BATCHID = pBatchID;
	END;
	-- =====================================================================
	-- 异常处理
	-- 继续执行存储过程
	-- =====================================================================
	DECLARE CONTINUE HANDLER FOR SQLWARNING
	BEGIN
			GET DIAGNOSTICS CONDITION 1
			@pSqlState = RETURNED_SQLSTATE,@pErrMsg = MESSAGE_TEXT;	-- 获取异常处理的错误代码和错误信息
			-- 将获取的错误代码和错误信息插入到批处理log中
			INSERT INTO d_runninglog VALUES('warn_log',pBatchID,CONCAT('Prc_TelSuccess_dhcs_snapshot_Update警告代码(SQLSTATE)：',@pSqlState, '; 警告信息：', @pErrMsg),'SQLWARNING',NOW());
	END;
	
	-- 更新批处理执行开始时间
	UPDATE m_event SET BATCHSTATUS = '99', STARTTIME = NOW() WHERE BATCHID = pBatchID;
	INSERT INTO d_runninglog VALUES('start_log',pBatchID,'start Prc_TelSuccess_dhcs_snapshot_Update in proc','start',NOW());
	
	-- 查询要处理的快照表
	SELECT  CONCAT('dhcs_snapshot.d_telcs_assign_log_', backDate),
					CONCAT('dhcs_snapshot.d_telcs_assign_log_', DATE_FORMAT(DATE_ADD(backDate,INTERVAL -1 DAY),'%Y%m%d')) INTO currTable,preTable;
			
	
	-- 判断前一天的快照表是否存在
	IF NOT EXISTS(
			SELECT 1 FROM information_schema.tables WHERE TABLE_SCHEMA = 'dhcs_snapshot'
			AND TABLE_NAME = CONCAT('d_telcs_assign_log_', DATE_FORMAT(DATE_ADD(backDate,INTERVAL -1 DAY),'%Y%m%d'))) THEN
			SET preTable = NULL;
	END IF;
	
	START TRANSACTION;
	
	-- 第一次更新
	IF preTable IS NULL THEN
		-- 只处理2019-09-01后的数据
		SET @createView = CONCAT('create or replace view vw_myproc as SELECT distinct CASEID,caseterms,TOUSERMTYPE FROM ', currTable," WHERE ASSIGNDATE >='2019-09-01' AND TOUSERMTYPE IS NOT NULL AND NOT (STATUS=0 AND TOUSERMTYPE=4 AND isoverdue=0) ORDER BY CASEID,caseterms,TOUSERMTYPE");
    PREPARE stm FROM @createView;
    EXECUTE stm;
		
		BEGIN
			DECLARE cur CURSOR FOR SELECT CASEID,caseterms,IFNULL(TOUSERMTYPE,0) FROM vw_myproc;
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
		
			-- 打开游标	
			OPEN cur;
			read_loop: LOOP
				-- 将游标中的值赋值给变量，注意：变量名不要和返回的列名同名，变量顺序要和sql结果列的顺序一致
				FETCH cur INTO pCASEID, pcaseterms, pTOUSERTYPE;
				IF done THEN
					LEAVE read_loop;
				END IF;
				
				SELECT Fnc_IsTelCs_Success_Case(pCASEID, pcaseterms, pTOUSERTYPE, backDate) INTO pCASESUCCESS;
				IF pCASESUCCESS = 1 THEN
					-- 执行更新业务逻辑
                                        SET @SqlUpdate = CONCAT('UPDATE ',currTable,' SET CASETELSUCCESSFLG = 1 WHERE CASEID=',pCASEID,' AND caseterms=',pcaseterms,' AND TOUSERMTYPE=',pTOUSERTYPE);
                                        PREPARE stmUpdate FROM @SqlUpdate;
                                        EXECUTE stmUpdate;
				END IF;
				
			END LOOP read_loop;
			CLOSE cur; -- 关闭游标
		END;
	END IF;
	
	-- 第二次更新
	IF preTable IS NOT NULL THEN
		-- 本次和上次的快照对比  上次成功的本次的CASEASSIGNID数据也是成功的
		SET @SqlUpdate = CONCAT('update ', currTable,' t INNER JOIN ', preTable ,' t2 on t2.CASEASSIGNID=t.CASEASSIGNID AND t2.CASETELSUCCESSFLG = 1 set t.CASETELSUCCESSFLG = 1 where t.CASETELSUCCESSFLG = 0');
		PREPARE stmUpdate FROM @SqlUpdate;
		EXECUTE stmUpdate;
		
		-- TOUSERMTYPE=4  and isoverdue=0  电催中的数据 认为是 0, 不处理
		-- 处理 催收中 + 昨天结案的 数据
		SELECT DATE_FORMAT(backDate,'%Y%m%d') INTO pNOTONDATEfrom;
		SELECT DATE_FORMAT(DATE_ADD(backDate,INTERVAL 1 DAY),'%Y%m%d') INTO pNOTONDATEto;
		SET @createView = CONCAT('create or replace view vw_myproc as SELECT distinct CASEID,caseterms,TOUSERMTYPE FROM ', currTable," WHERE (STATUS=0 AND NOT (STATUS=0 AND TOUSERMTYPE=4 AND isoverdue=0)) OR (STATUS=1 AND NOTONDATE>='",pNOTONDATEfrom,"' AND NOTONDATE<'",pNOTONDATEto,"') ");
		PREPARE stm FROM @createView;
		EXECUTE stm;
		
		SET done = FALSE;
			
		BEGIN
			DECLARE cur CURSOR FOR SELECT CASEID,caseterms,IFNULL(TOUSERMTYPE,0) FROM vw_myproc;
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
			  
			-- 打开游标	
			OPEN cur;
			read_loop: LOOP
				-- 将游标中的值赋值给变量，注意：变量名不要和返回的列名同名，变量顺序要和sql结果列的顺序一致
				FETCH cur INTO pCASEID, pcaseterms, pTOUSERTYPE;
				IF done THEN
					LEAVE read_loop;
				END IF;
				
				SELECT Fnc_IsTelCs_Success_Case(pCASEID, pcaseterms, pTOUSERTYPE, backDate) INTO pCASESUCCESS;
				IF pCASESUCCESS = 1 THEN
					-- 执行更新业务逻辑
                                        SET @SqlUpdate = CONCAT('UPDATE ',currTable,' SET CASETELSUCCESSFLG = 1 WHERE CASEID=',pCASEID,' AND caseterms=',pcaseterms,' AND TOUSERMTYPE=',pTOUSERTYPE);
                                        PREPARE stmUpdate FROM @SqlUpdate;
                                        EXECUTE stmUpdate;
				END IF;
			
			END LOOP read_loop;
			CLOSE cur;
		END;
	END IF;
	
	-- 执行更新单条电催案件是否电催成功(0=失败， 1=成功)
	SET @SqlUpdate = CONCAT('UPDATE ',currTable,' t inner join (SELECT CASEID,caseterms,max(CASEASSIGNID) as CASEASSIGNID FROM ',currTable, ' where CASETELSUCCESSFLG = 1 GROUP BY CASEID,caseterms) t2 on t2.CASEASSIGNID=t.CASEASSIGNID set t.TELSUCCESSFLG = 1 where t.TELSUCCESSFLG = 0');
	PREPARE stmUpdate FROM @SqlUpdate;
	EXECUTE stmUpdate;
	
	-- 更新批处理执行结束时间
	UPDATE m_event SET BATCHSTATUS = '0', RESULT = '执行成功。', ENDTIME = NOW() WHERE BATCHID = pBatchID;
	COMMIT;
	
	INSERT INTO d_runninglog VALUES('end_log',pBatchID, 'end Prc_TelSuccess_dhcs_snapshot_Update in proc' ,'end',NOW());
END$$

DELIMITER ;