业务代码
以下为调用执行存储后，获取返回参数
纯查询，不建议用存储过程
private void statFieldCollectByOpFlag(int opFlag){
log.info("开始统计成果量opflag-{}", opFlag);
try {
Date date = new Date();
String statDate = DateUtil.formatDate(date, "yyyy-MM-dd");
// 如果当前时间在6点以前，则统计前一天的
int hour = date.getHours();
if (hour <= 6){
statDate = DateUtil.formatDate(DateUtil.dateDiffer(date, "D",-1), "yyyy-MM-dd");
}
Map<String, Object> map = new HashMap<String, Object>();
map.put("i_op_flag", opFlag);
map.put("i_stat_date", statDate);
map.put("o_err_no", 0);
map.put("o_err_msg", "");
try {
statFruitRecordService.statFruitCountInfoByProc(map);
BigDecimal bigDecimal= (BigDecimal)map.get("o_err_no");
Integer errNo = bigDecimal.intValue();
if ( !errNo.equals(0) ){
String errMsg = (String)map.get("o_err_msg");
log.error("统计成果量["+opFlag+"]异常["+errNo.toString()+"]"+errMsg);
}
} catch (Exception e) {
log.error("统计成果量["+opFlag+"]异常", e);
}
log.info("结束统计成果量opflag-{}", opFlag);
} catch (Exception e) {
log.error("结束统计成果量["+opFlag+"]异常", e);
}
}
Mapper代码
<select id="statFruitCountInfo" parameterType="map" statementType="CALLABLE">
{
call sp_stat_fruit_record(
#{map.i_op_flag , mode=IN , jdbcType=NUMERIC},
#{map.i_stat_date , mode=IN , jdbcType=VARCHAR},
#{map.o_err_no , mode=OUT , jdbcType=NUMERIC},
#{map.o_err_msg , mode=OUT , jdbcType=VARCHAR}
)
}
</select>
存储过程
-- --------------------------------------------------------
-- 主机: xx.xx.xx.xx
-- 服务器版本: 5.7.27-log - MySQL Community Server (GPL)
-- 服务器操作系统: linux-glibc2.12
-- HeidiSQL 版本: 11.0.0.5919
-- --------------------------------------------------------
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
-- 导出 存储过程 sp_stat_fruit_record 结构
DROP PROCEDURE IF EXISTS `sp_stat_fruit_record`;
DELIMITER //
CREATE PROCEDURE `sp_stat_fruit_record`(
IN `i_op_flag` int,
IN `i_stat_date` varchar(10),
OUT `o_err_no` int,
OUT `o_err_msg` varchar(128)
)
COMMENT '[104]入库成果量统计'
update_proc:
BEGIN
#Routine body goes here...
DECLARE sp_code INT DEFAULT 104;
-- START Declare Conditions
DECLARE sql_err_code INT DEFAULT 0;
DECLARE var_cnt INT DEFAULT 0;
DECLARE var_size INT DEFAULT 0;
DECLARE var_idx INT DEFAULT 0;
DECLARE var_tail varchar(16) DEFAULT '';
DECLARE CONTINUE HANDLER FOR 1054 SET sql_err_code = 1054;
-- Unknown column
DECLARE CONTINUE HANDLER FOR 1062 SET sql_err_code = 1062;
-- Duplicate entry
DECLARE CONTINUE HANDLER FOR 1329 SET sql_err_code = 0;
-- No data - zero rows fetched, SELECT ed, or processed
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION set sql_err_code = 9999;
-- END Declare Conditions
-- check param
-- START TRANSACTION;
IF 1 != 1
OR (i_op_flag IS NULL AND '' <> (@param_name := 'i_op_flag'))
OR (i_stat_date IS NULL AND '' <> (@param_name := 'i_stat_date'))
THEN
SET o_err_no = fn_sp_errno(sql_err_code, sp_code, 1);
SET o_err_msg = CONCAT('缺少参数', @param_name);
LEAVE update_proc;
END IF;
SET @element_codes = 'aoi,building,aoi_gate,unit,poi,room,internal_road';
-- i_stat_date格式 'YYYY-MM-DD'
-- i_op_flag
-- 1 转历史
-- 2 截止昨日总量统计
-- 3 当日审核入库统计
-- 4 当日采集入库统计
IF 1 = i_op_flag THEN
# 保留1周的数据
SET @del_date = CONCAT(DATE_SUB(i_stat_date, INTERVAL 1 WEEK), ' 00:00:00');
SET var_size = LENGTH(@element_codes)- LENGTH(REPLACE(@element_codes,",","")) + 1;
SET var_idx=1;
WHILE var_idx <= var_size DO
SET var_tail = SUBSTRING_INDEX(SUBSTRING_INDEX(@element_codes,',',var_idx),',',-1);
SET @sql_str = CONCAT('INSERT INTO his_fruit_', var_tail, ' SELECT * FROM t_fruit_', var_tail, ' WHERE update_time < "', @del_date, '"');
PREPARE STMT FROM @sql_str;EXECUTE STMT;DEALLOCATE PREPARE STMT;
IF 0 <> sql_err_code THEN
SET o_err_no = fn_sp_errno(sql_err_code, sp_code, 11);
SET o_err_msg = CONCAT('审核成果转历史失败', var_tail);
LEAVE update_proc;
LEAVE update_proc;
END IF;
SET @sql_str = CONCAT('DELETE FROM t_fruit_', var_tail, ' WHERE update_time < "', @del_date, '"');
PREPARE STMT FROM @sql_str;EXECUTE STMT;DEALLOCATE PREPARE STMT;
IF 0 <> sql_err_code THEN
SET o_err_no = fn_sp_errno(sql_err_code, sp_code, 12);
SET o_err_msg = CONCAT('审核成果转历史失败', var_tail);
LEAVE update_proc;
END IF;
SET @sql_str = CONCAT('INSERT INTO his_collect_fruit_', var_tail, ' SELECT * FROM t_collect_fruit_', var_tail, ' WHERE update_time < "', @del_date, '"');
PREPARE STMT FROM @sql_str;EXECUTE STMT;DEALLOCATE PREPARE STMT;
IF 0 <> sql_err_code THEN
SET o_err_no = fn_sp_errno(sql_err_code, sp_code, 13);
SET o_err_msg = CONCAT('采集成果转历史失败', var_tail);
LEAVE update_proc;
END IF;
SET @sql_str = CONCAT('DELETE FROM t_collect_fruit_', var_tail, ' WHERE update_time < "', @del_date, '"');
PREPARE STMT FROM @sql_str;EXECUTE STMT;DEALLOCATE PREPARE STMT;
IF 0 <> sql_err_code THEN
SET o_err_no = fn_sp_errno(sql_err_code, sp_code, 14);
SET o_err_msg = CONCAT('采集成果转历史失败', var_tail);
LEAVE update_proc;
END IF;
SET var_idx = var_idx + 1;
END WHILE;
-- 2 截止昨日总量统计
ELSEIF 2 = i_op_flag THEN
SELECT COUNT(1) INTO var_cnt FROM t_stat_fruit_record WHERE stat_date = '0000-00-00';
IF 0 <> sql_err_code THEN
SET o_err_no = fn_sp_errno(sql_err_code, sp_code, 21);
SET o_err_msg = '截止昨日总量统计失败';
LEAVE update_proc;
END IF;
IF var_cnt = 0 THEN
INSERT INTO t_stat_fruit_record
SELECT '0000-00-00', sum(right_count), sum(error_count), 'ALL',sum(right_count), sum(error_count), now(), now() FROM t_stat_fruit_record;
INSERT INTO t_stat_fruit_record
SELECT '0000-00-00', sum(right_count), sum(error_count), element_code, sum(right_count), sum(error_count), now(), now() FROM t_stat_fruit_record
WHERE element_code != 'ALL'
GROUP BY element_code;
ELSE
UPDATE t_stat_fruit_record SET plan_project_id = right_count, plan_collect_id = error_count, update_time = now() WHERE stat_date = '0000-00-00';
END IF;
IF 0 <> sql_err_code THEN
SET o_err_no = fn_sp_errno(sql_err_code, sp_code, 22);
SET o_err_msg = '截止昨日总量统计失败';
LEAVE update_proc;
END IF;
-- 3 当日审核入库统计
ELSEIF 3 = i_op_flag THEN
SET @start_date = CONCAT(i_stat_date, ' 00:00:00');
SET @end_date = CONCAT(i_stat_date, ' 23:59:59');
SET var_size = LENGTH(@element_codes)- LENGTH(REPLACE(@element_codes,",","")) + 1;
SET var_idx=1;
WHILE var_idx <= var_size DO
SET var_tail = SUBSTRING_INDEX(SUBSTRING_INDEX(@element_codes,',',var_idx),',',-1);
SET @sql_str = CONCAT('
INSERT INTO t_stat_fruit_record
SELECT
"',i_stat_date,'", project_manage_id, plan_id, "', var_tail,'"
, SUM(CASE WHEN result_type = 1 THEN 1 ELSE 0 END) AS rightCount
, SUM(CASE WHEN result_type = 2 THEN 1 ELSE 0 END) AS errorCount
, now(), now()
FROM t_fruit_', var_tail, '
WHERE update_time >= "', @start_date, '" AND update_time <= "', @end_date, '"
GROUP BY project_manage_id, plan_id
ON DUPLICATE KEY UPDATE
right_count = VALUES(right_count)
, error_count = VALUES(error_count)
, update_time = VALUES(update_time) '
);
-- SELECT @sql_str;
PREPARE STMT FROM @sql_str;EXECUTE STMT;DEALLOCATE PREPARE STMT;
IF 0 <> sql_err_code THEN
SET o_err_no = fn_sp_errno(sql_err_code, sp_code, 31);
SET o_err_msg = CONCAT('截止昨日总量统计失败', var_tail);
LEAVE update_proc;
END IF;
SET var_idx = var_idx + 1;
END WHILE;
-- 4 当日采集入库统计
ELSEIF 4 = i_op_flag THEN
SET @start_date = CONCAT(i_stat_date, ' 00:00:00');
SET @end_date = CONCAT(i_stat_date, ' 23:59:59');
SET var_size = LENGTH(@element_codes)- LENGTH(REPLACE(@element_codes,",","")) + 1;
SET var_idx=1;
WHILE var_idx <= var_size DO
SET var_tail = SUBSTRING_INDEX(SUBSTRING_INDEX(@element_codes,',',var_idx),',',-1);
SET @sql_str = CONCAT('
INSERT INTO t_stat_fruit_collect
SELECT
"',i_stat_date,'", project_manage_id, plan_id, "', var_tail,'"
, SUM(CASE WHEN result_type = 1 THEN 1 ELSE 0 END) AS rightCount
, SUM(CASE WHEN result_type = 2 THEN 1 ELSE 0 END) AS errorCount
, now(), now()
FROM t_collect_fruit_', var_tail, '
WHERE update_time >= "', @start_date, '" AND update_time <= "', @end_date, '"
GROUP BY project_manage_id, plan_id
ON DUPLICATE KEY UPDATE
right_count = VALUES(right_count)
, error_count = VALUES(error_count)
, update_time = VALUES(update_time) '
);
-- SELECT @sql_str;
PREPARE STMT FROM @sql_str;EXECUTE STMT;DEALLOCATE PREPARE STMT;
IF 0 <> sql_err_code THEN
SET o_err_no = fn_sp_errno(sql_err_code, sp_code, 41);
SET o_err_msg = CONCAT('截止昨日总量统计失败', var_tail);
LEAVE update_proc;
END IF;
SET var_idx = var_idx + 1;
END WHILE;
ELSE
SET o_err_no = fn_sp_errno(sql_err_code, sp_code, 99);
SET o_err_msg = '未支持的操作类型';
LEAVE update_proc;
END IF;
-- ok
-- COMMIT;
-- ok
SET o_err_no = 0;
END//
DELIMITER ;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
9300
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
