mysql 存储过程文本计算公式解析归集

mysql5.6以上版本，使用存储过程和函数实现定时计算任务

一，计算公式表(解析p0[-1]+p1[-1]+p2[-1] )

ms_indi_id	ms_no	indi_no	cal_foumal
1202	T001	C037	p0[-1]+p1[-1]+p2[-1]

ms_indi_id	level	parma_code	parma_val
1202	1	p0	T001.C066
1202	1	p1	T001.C068
1202	1	p2	T001.C067

二，写一个定时器，定时调用计算的存储过程

略

三，计算存储过程

DELIMITER $$

USE `ies`$$

DROP PROCEDURE IF EXISTS `p_up_calculate_cursor_new`$$

CREATE DEFINER=`dept`@`%` PROCEDURE `p_up_calculate_cursor_new`()
BEGIN
/**********************************************************************************************************/
/*名称: p_up_calculate_cursor_new                                                                     		  */
/*实现功能 : 统计计算公式数据，并插入到实时表，更新状态表                  				  */
/*功能说明：根据计算公式，计算数据，并把计算结果插入到实时表r-data_curr,更新到r_status_curr 		  */
/*逻辑说明：level_id 计算公式等级，从1级开始计算，然后计算2级，以此类推                                   */
/*创建时间: 2019-12-20                                                                                    */
/*创建人: 胡海江                                                                                          */
/**********************************************************************************************************/
-- DECLARE level_id INT ;
-- DECLARE num INT default 1;
DECLARE v_dtEndTime DATETIME DEFAULT ADDDATE(DATE_FORMAT(NOW(),'%Y-%m-%d %H:00:00'),INTERVAL FLOOR(MINUTE(NOW())/15)*15 MINUTE);
DECLARE v_dtStartTime DATETIME DEFAULT SUBDATE(v_dtEndTime,INTERVAL 15 MINUTE);
DECLARE v_sql VARCHAR(1000);
DECLARE v_val DECIMAL(16,4);-- 计算结果
DECLARE v_level INT ; -- 计算等级
DECLARE v_ms VARCHAR(10); -- 计算的对象编号
DECLARE v_indi VARCHAR(8);-- 计算的对象的指标编号
DECLARE v_ms_indi_id INT(13) ;-- 计算公式的ID
DECLARE cal_fromula_tmp VARCHAR(1000);-- 计算公式
DECLARE v_cal_str VARCHAR(2000);-- 计算公式转换后的字符串
DECLARE v_pk INT DEFAULT 0; -- 异常变量
DECLARE v_done INT DEFAULT 0;-- 循环无数据变量

DECLARE v_calculate_str CURSOR FOR 
	SELECT m.`ms_indi_rela_id`,m.`ms_no`,m.`indi_no`,m.level,r.`CALC_FORMULA` FROM r_calculate_formula m LEFT JOIN r_ms_indi_rela r ON r.`MS_INDI_ID` = m.`ms_indi_rela_id` ORDER BY m.level;
	
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET v_pk = 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;


	OPEN v_calculate_str;
		 DEALING01:LOOP                     -- 循环语句的关键词
		   FETCH v_calculate_str INTO v_ms_indi_id,v_ms,v_indi,v_level,cal_fromula_tmp;
		   IF v_done=1 THEN
			LEAVE DEALING01;
		   END IF;
			-- 调用函数，替换计算公式中的实际数据，返回的是替换实际数据的计算公式，并不是计算后的结果
			SET v_cal_str = f_get_cal_val(v_ms_indi_id,v_ms,v_indi,cal_fromula_tmp,v_dtStartTime);
			
			SET v_sql = CONCAT('REPLACE INTO r_data_curr_20191217(ms_no,indi_no,data_date,rval) VALUES(\'',v_ms,'\',\'',v_indi,'\',\'',v_dtStartTime,'\',',v_cal_str,')');
			
			SET @v_sql1 = v_sql;	
			PREPARE stmt FROM @v_sql1;    
			EXECUTE stmt; 
			DEALLOCATE PREPARE stmt;

			IF v_pk = 1 THEN 
				INSERT INTO t_sys_cal_error(ms_indi_id,ms_no,indi_no,cal_formula,cal_time) VALUES(v_ms_indi_id,v_ms,v_indi,cal_fromula_tmp,v_dtEndTime);
			END IF;

		 END LOOP;
	CLOSE v_calculate_str;

    END$$

DELIMITER ;

四，解析函数

DELIMITER $$

USE `ies`$$

DROP FUNCTION IF EXISTS `f_get_cal_val`$$

CREATE DEFINER=`dept`@`%` FUNCTION `f_get_cal_val`(`id` INT,cal_ms_no VARCHAR(10),cal_indi_no VARCHAR(8),cal_fromula VARCHAR(1000),cal_time VARCHAR(20)) RETURNS VARCHAR(3000)
BEGIN
	DECLARE v_cal_no VARCHAR(5);
	DECLARE v_cal_no_str VARCHAR(8);
	DECLARE v_cal_ms_indi_str VARCHAR(20);
	DECLARE cal_fromula_tmp VARCHAR(3000);
	DECLARE v_ms VARCHAR(10);
	DECLARE v_indi VARCHAR(8);
	DECLARE v_val DECIMAL(12,4);
	DECLARE v_sql TEXT;
	DECLARE v_done INT DEFAULT 0;
	
	DECLARE v_pk INT(2) DEFAULT 0;
	DECLARE v_calculate_str CURSOR FOR 
		SELECT PARAM_CODE,PARAM_VAL  FROM r_ms_calculate WHERE MS_INDI_ID = id;
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET v_pk = 1;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;
	SET cal_fromula_tmp = cal_fromula;
	
	OPEN v_calculate_str ;-- 打开游标
	    DEALING01:LOOP                     -- 循环语句的关键词
		   FETCH v_calculate_str INTO v_cal_no,v_cal_ms_indi_str;
		   IF v_done=1 THEN
			LEAVE DEALING01;
		   END IF;
		   
		   SET v_ms = SUBSTRING_INDEX(v_cal_ms_indi_str,'.',1);
		   SET v_indi = SUBSTRING_INDEX(v_cal_ms_indi_str,'.',-1);
		   SELECT r.rval INTO v_val FROM r_data_curr r WHERE r.ms_no = v_ms AND r.indi_no = v_indi AND r.data_date = cal_time;
		   SET v_cal_no_str = CONCAT(v_cal_no,'[-1\]');
		   SET cal_fromula_tmp = REPLACE(cal_fromula_tmp,v_cal_no_str,v_val);     
	       
	     END LOOP;
	    
	 CLOSE v_calculate_str;     -- 关闭游标
	 
	 RETURN cal_fromula_tmp;
	 
	 
	 
END$$

DELIMITER ;

编写过程遇到的问题

1，函数中不可以执行预编译语句

2，赋值问题

-- 如果v_val变量类型是字符串类型，赋值后v_val = '1+1+1'
select '1+1+1' into v_val
-- 如果v_val变量类型是数值类型，赋值后v_val = 1
select '1+1+1' into v_val