[转]mysql 5.6 存储过程+事务+游标+错误异常抛出+日志写入
转自:http://www.wolonge.com/post/detail/118249
DELIMITER $$ USE `ecstore`$$ DROP PROCEDURE IF EXISTS `proc_add_warranty_card`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_add_warranty_card`() BEGIN -- 获取异常信息 DECLARE v_sql1 VARCHAR(500); DECLARE v_sql2 VARCHAR(500); #定义变量 DECLARE w_warranty_id BIGINT(20) DEFAULT 1; DECLARE w_orderid BIGINT(20); DECLARE w_ordertime INT(10); DECLARE w_member_id MEDIUMINT(8); #定义游标遍历时,作为判断是否遍历完全部记录的标记 DECLARE done1 INTEGER DEFAULT 0; DECLARE data_err INTEGER DEFAULT 0; DECLARE log_err INTEGER DEFAULT 0; #定义保修卡主表为C_WARRANTY DECLARE C_WARRANTY CURSOR FOR SELECT orde.order_id, orde.createtime, orde.member_id FROM `sdb_b2c_orders` AS orde WHERE orde.ship_status='1' AND orde.status IN ('active','finish') AND (orde.warranty_id IS NULL); #声明当游标遍历完全部记录后将标志变量置成某个值 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1=1; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; GET DIAGNOSTICS CONDITION 1 v_sql1 = RETURNED_SQLSTATE,v_sql2= MESSAGE_TEXT; INSERT INTO `sdb_b2c_warranty_log` (`order_id`,`createtime`,`msg_text`) VALUES (w_orderid,UNIX_TIMESTAMP(CURDATE()),CONCAT(v_sql1,':',v_sql2)); SET log_err=1; END; #手动提交事务 SET autocommit=0; OPEN C_WARRANTY; #取出每条记录并赋值给相关变量,注意顺序 FETCH C_WARRANTY INTO w_orderid, w_ordertime, w_member_id; SET w_warranty_id=CONCAT(DATE_FORMAT(NOW(), '%Y%m%d'),LPAD((w_warranty_id), 5, '0')); #循环语句的关键词 REPEAT -- 启动事务 START TRANSACTION; #保修卡主表添加 INSERT INTO `sdb_b2c_warranty` (`warranty_id`,`order_id`,`ordertime`,`member_id`,`warranty_card_status`,`createtime`) VALUES (w_warranty_id,w_orderid,w_ordertime,w_member_id,'1',UNIX_TIMESTAMP(CURDATE())); IF log_err=0 THEN #生成明细 INSERT INTO `sdb_b2c_warranty_detail`(warranty_id,item_id,order_id, obj_id,product_id,goods_id,type_id,bn,pn,`name`,nums,sendnum,addon,item_type) SELECT w_warranty_id,ite.item_id,ite.`order_id`,ite.obj_id,ite.product_id, ite.goods_id,ite.type_id,ite.bn,pro.store_place,ite.name,ite.nums, ite.sendnum,ite.addon,ite.item_type FROM`sdb_b2c_order_items` AS ite LEFT JOIN `sdb_b2c_products` AS pro ON pro.product_id=ite.product_id WHERE ite.order_id=w_orderid; END IF; #回写订单表保修卡号 IF log_err=0 THEN UPDATE `sdb_b2c_orders` SET `warranty_id`=w_warranty_id WHERE `order_id`= w_orderid; END IF; COMMIT; SET log_err=0; SET done1=0; #取出每条记录并赋值给相关变量,注意顺序 FETCH C_WARRANTY INTO w_orderid, w_ordertime, w_member_id; SET w_warranty_id =w_warranty_id+1; #循环语句结束 UNTIL done1 END REPEAT; #关闭游标 CLOSE C_WARRANTY; BEGIN #如果是退货,则把保修卡状态改成无效 DECLARE card_order_id BIGINT(20); -- 获取异常信息 DECLARE v_sql1 VARCHAR(500); DECLARE v_sql2 VARCHAR(500); DECLARE card_warranty_id BIGINT(20); #标记循环结束 DECLARE done2 INTEGER DEFAULT 0; DECLARE C_UPDATE_CARD_STATUS CURSOR FOR SELECT war.`order_id`,war.`warranty_id` FROM `sdb_b2c_orders` AS orde JOIN `sdb_b2c_warranty` AS war ON orde.`order_id`=war.`order_id` WHERE orde.ship_status='4'; #声明当游标遍历完全部记录后将标志变量置成某个值 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2= 1; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 v_sql1 = RETURNED_SQLSTATE,v_sql2= MESSAGE_TEXT; INSERT INTO `sdb_b2c_warranty_log` (`order_id`,`createtime`,`msg_text`) VALUES (w_orderid,UNIX_TIMESTAMP(CURDATE()),CONCAT(v_sql1,':',v_sql2)); END; #打开明细游标 OPEN C_UPDATE_CARD_STATUS; FETCH C_UPDATE_CARD_STATUS INTO card_order_id,card_warranty_id; REPEAT UPDATE sdb_b2c_warranty SET warranty_card_status='0',invalid_reason='0' WHERE warranty_card_status='1' AND `order_id`=card_order_id; SET done2=0; #取出每条记录并赋值给相关变量,注意顺序 FETCH C_UPDATE_CARD_STATUS INTO card_order_id,card_warranty_id; #循环语句结束 UNTIL done2 END REPEAT; CLOSE C_UPDATE_CARD_STATUS; END; END$$ DELIMITER ;