存储过程的使用场景
决定是否使用存储过程，其实是在权衡：应当将计算移动到数据，还是将数据移动到计算。
计算过程可以抽象为以下3个步骤：读数据，计算，写数据。或者说，操作=存取数据+计算。如果需要的数据量较大，存取数据容易成为瓶颈，这时应当将计算移动到数据。反之，如果需要的数据较少，但是计算较为复杂，应当将数据移动到计算。
现在来看存储过程。存储过程运行在数据库中，数据移动距离短，但计算能力有限。因此适合于需要读取大量的记录，而计算规则相对简单的OLAP应用。相反，对于业务规则复杂，数据量小的OLTP应用，更适合使用编程语言。
什么时候用存储过程合适
- 当一个事务涉及到多个SQL语句时或者涉及到对多个表的操作时就要考虑用存储过程；
- 当在一个事务的完成需要很复杂的商业逻辑时（比如，对多个数据的操作，对多个状态的判断更改等）要考虑；
- 还有就是比较复杂的统计和汇总也要考虑。
使用存储过程的优缺点
优点
- 速度快。尤其对于较为复杂的逻辑，减少了网络流量之间的消耗。
- 写程序简单，采用存储过程调用类，调用任何存储过程都只要1-2行代码。
- 升级、维护方便。
- 调试其实也并不麻烦，可以用查询分析器。
缺点
- 可移植性差。
- 数据量大时会比较耗时。
存储过程优化技巧
1、尽量用一些SQL语句来代替一些小循环，比如用上聚合函数、求平均函数。
2、采用拼凑语句，将更新语句在循环中拼凑后，再统一更新。
3、将中间结果存放于临时表，加索引。
4、查找语句要花不少时间，尽量不要放在循环内。
5、字段太多的查找，就算只有一条记录，还不如转成多条记录，很少字段，建了索引的临时表。
比如：查找某个参数表，已经在循环外面查找出该条记录，但该记录有100个字段，要根据条件找出相应的字段。本来以为应该很快，就一条记录，但字段太多，也是要消耗不少时间。于是，将100列转成2个字段，100行的表，时间也得以大幅改善。
on duplicate key update实现(批量)插入或更新操作
一. 背景
- 背景：业务上经常有这样的需求场景，如果之前有这条数据，就做更新；如果没有，就做新增。
- 常用的处理方案：通过主键id或者其他唯一键判断DB中是否有这条数据，再判断调用insert或update语句。这样做逻辑处理起来比较复杂，降低代码效率，而且如果并发量高，可能会存在数据问题。
二. on duplicate key update概述
- 为了应对这种业务场景，MySQL有一种专有语法
（insert into ... on duplicate key update），一条SQL语句实现插入或更新，可单条可批量。 - 使用要点
- 表要求必须有主键或唯一索引才能起效果，否则insert或update无效。
- 该语法是根据主键或唯一键来判断是新增还是更新。
- VALUES() 后面应为需要更新的字段，不需要更新的字段不用罗列。
- 遇到已存在记录（根据唯一键或主键）时，自动更新已有的数据；如果表中有多个唯一键（可以是单列索引或复合索引），则任意一个唯一键冲突时，都会自动更新数据。
- 所有操作均由SQL处理，不需要额外程序代码分析，能够大幅提高程序执行效率。
三. on duplicate key update的使用
创建测试—book科目表
CREATE TABLE `book` (
`id` int NOT NULL AUTO_INCREMENT,
`unique_code` varchar(30) NOT NULL,
`book_name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_code` (`unique_code`) USING BTREE COMMENT 'book unique_code'
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
执行如下命令
insert into book(
unique_code,
book_name
) values (
'zs-001',
'中文'
) on duplicate key update book_name='数学';
成功插入一条数据
再次执行步骤一命令你会发现book_name值改变
Mysql中substring_index函数的使用
一、语法
substring_index（“待截取有用部分的字符串”，“截取数据依据的字符”，截取字符的位置N）
二、案例
SELECT SUBSTRING_INDEX(‘58.33.115.130’, ‘.’, 1); //结果是58
//以第一个句号为分割截取
SELECT SUBSTRING_INDEX(‘58.33.115.130’, ‘.’, -1); //结果是130
//从后面开始算第一个句号
SELECT SUBSTRING_INDEX(‘58.33.115.130’, ‘.’, 2); //结果是58.33
使用PREPARE 动态执行sql语句
PREPARE STMT FROM @sql_str; EXECUTE STMT; DEALLOCATE PREPARE STMT;
1: DROP TABLE IF EXISTS alarm;
2: CREATE TABLE alarm ( id int(11) NOT NULL auto_increment, name varchar(255) default NULL,PRIMARY KEY (id));
3:
4: INSERT INTO alarm (name) values('aa');
5: INSERT INTO alarm (name) values('bb');
6:
7: DROP procedure if exists statalarm;
8: delimiter //
9: create procedure statalarm()
10: begin
11: SET @id = '1,2';
12:
13: SET @sql = 'select * from alarm where id IN (?)';
14: PREPARE stmt FROM @sql;
15: /*
16: 注意 EXECUTE 的最终语句是：select * from alarm where id IN ('1,2');
17: 而不是 select * from alarm where id IN (1,2);
18: 这是因为如果用户变量的值是字符串，在EXECUTE时 会自动的在变量的值前后加上引号
19: */
20: EXECUTE stmt USING @id;
21: DEALLOCATE PREPARE stmt;
22: /*
23: 如果想要 组成select * from alarm where id IN (1,2);可以使用下面的语句
24: */
25: SET @sql = concat('select * from alarm where id IN (',@id,')');
26: PREPARE stmt FROM @sql;
27: EXECUTE stmt ;
28: DEALLOCATE PREPARE stmt;
29:
30: end;//
31: delimiter ;
DECLARE CONTINUE HANDLER FOR NOT FOUND 解释
在mysql的存储过程中经常会看到这句话：DECLARE CONTINUE HANDLER FOR NOT FOUND。
它的含义是：若没有数据返回,程序继续,并将变量IS_FOUND设为0 。
JAVA调用存储过程–MYSQL
业务代码
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 */;
存储过程实例
DELIMITER $$
USE `oso_isp` $$
DROP PROCEDURE IF EXISTS `proc_wh_stock_in_order_ok` $$
CREATE PROCEDURE `proc_wh_stock_in_order_ok` ( IN in_id INT , IN in_user_id INT)
BEGIN
DECLARE var_id INT DEFAULT 0;
DECLARE var_asset_type INT DEFAULT 1;
DECLARE var_quantity INT DEFAULT 0;
DECLARE var_item_id INT DEFAULT 0;
DECLARE var_i INT DEFAULT 0;
DECLARE var_asset_id INT DEFAULT 0;
DECLARE done INT DEFAULT - 1;
/* 声明游标 */
DECLARE myCursor CURSOR FOR SELECT
a.id,
b.asset_type,
a.quantity
FROM
wh_stock_in_order_item a
INNER JOIN wh_asset b ON a.asset_id = b.id
WHERE
a.stock_in_order_id = in_id;
/* 当游标到达尾部时，mysql自动设置done=1 */
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
/* 打开游标 */
OPEN myCursor;
/* 循环开始 */
myLoop :
LOOP
/* 移动游标并赋值 */
FETCH myCursor INTO var_id,
var_asset_type,
var_quantity;
IF done = 1 THEN
UPDATE wh_stock_in_order
SET STATUS = 1
WHERE
id = in_id;
LEAVE myLoop;
END IF;
SELECT id INTO var_asset_id
FROM
wh_asset_inventory
WHERE
stock_in_order_item_id = var_id;
IF var_asset_id > 0 THEN
UPDATE wh_asset_inventory s
JOIN (
SELECT
a.id,
a.asset_id,
c.CODE,
0 ,
b.supplier_id,
b.buyer_id,
a.manufacture_date,
b.purchase_date,
DATE_ADD( a.manufacture_date, INTERVAL c.warranty_period MONTH ) AS end_repair_date,
0,
b.storekeeper_id,
b.Stockroom_id,
a.quantity,
0,
a.id,
a.unit_price,
0,
in_user_id
FROM
wh_stock_in_order_item a
INNER JOIN wh_stock_in_order b ON a.stock_in_order_id = b.id
INNER JOIN wh_asset c ON c.id = a.asset_id
WHERE
a.id = var_id
AND a.is_deleted = 0
) k ON k.id = s.stock_in_order_item_id
SET s.`asset_id` = k.asset_id,
s.`code` = k.CODE,
s.`location_id` = 0,
s.`supplier_id` = k.supplier_id,
s.`buyer_id` = k.buyer_id,
s.`manufacture_date` = k.manufacture_date,
s.`purchase_date` = k.purchase_date,
s.`end_repair_date` = k.end_repair_date,
s.`keeping_department_id` = 0,
s.`storekeeper_id` = k.storekeeper_id,
s.`Stockroom_id` = k.Stockroom_id,
s.`quantity` = k.quantity,
s.`status` = 0,
s.`stock_in_order_item_id` = k.id,
s.unit_price = k.unit_price,
s.`is_deleted` = 0 ,
s.`created_by` = in_user_id
WHERE
s.is_deleted = 0;
ITERATE myLoop;
END IF;
INSERT INTO `wh_asset_inventory` (
`asset_id`,
`code`,
`location_id`,
`supplier_id`,
`buyer_id`,
`manufacture_date`,
`purchase_date`,
`end_repair_date`,
`keeping_department_id`,
`storekeeper_id`,
`Stockroom_id`,
`quantity`,
`status`,
`stock_in_order_item_id`,
unit_price,
`is_deleted`,
`created_by`
) SELECT
a.asset_id,
c.CODE ,
0,
b.supplier_id,
b.buyer_id,
a.manufacture_date,
b.purchase_date,
DATE_ADD( a.manufacture_date, INTERVAL c.warranty_period MONTH ),
0,
b.storekeeper_id,
b.Stockroom_id,
a.quantity,
0 ,
a.id ,
a.unit_price,
0 ,
in_user_id
FROM
wh_stock_in_order_item a
INNER JOIN wh_stock_in_order b ON a.stock_in_order_id = b.id
INNER JOIN wh_asset c ON c.id = a.asset_id
WHERE
a.id = var_id
AND a.is_deleted = 0;
SELECT LAST_INSERT_ID( ) INTO var_item_id;
UPDATE wh_asset a
JOIN ( SELECT asset_id, unit_price FROM wh_stock_in_order_item WHERE id = var_id ) b ON b.asset_id = a.id
SET a.reference_price = b.unit_price;
UPDATE wh_asset_inventory
SET CODE = CONCAT( CODE, '-', id )
WHERE
id = var_item_id;
/* 循环结束 */
END LOOP myLoop;
/* 关闭游标 */
CLOSE myCursor;
END $$
DELIMITER ;
9817
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
