mysql 5.6 游标_[转]mysql 5.6 存储过程+事务+游标+错误异常抛出+日志写入

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_WARRANTYDECLARE C_WARRANTY CURSOR FOR

SELECTorde.order_id,

orde.createtime,

orde.member_idFROM `sdb_b2c_orders` ASordeWHERE 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 FORSQLEXCEPTIONBEGIN

ROLLBACK;

GET DIAGNOSTICS CONDITION1 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;OPENC_WARRANTY;

#取出每条记录并赋值给相关变量，注意顺序FETCH C_WARRANTY INTOw_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)SELECTw_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_typeFROM`sdb_b2c_order_items` ASiteLEFT JOIN `sdb_b2c_products` AS pro ON pro.product_id=ite.product_idWHERE 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 INTOw_orderid, w_ordertime, w_member_id;SET w_warranty_id =w_warranty_id+1;

#循环语句结束

UNTIL done1ENDREPEAT;

#关闭游标CLOSEC_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

SELECTwar.`order_id`,war.`warranty_id`FROM `sdb_b2c_orders` ASordeJOIN `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 FORSQLEXCEPTIONBEGINGET DIAGNOSTICS CONDITION1 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;

#打开明细游标OPENC_UPDATE_CARD_STATUS;FETCH C_UPDATE_CARD_STATUS INTOcard_order_id,card_warranty_id;

REPEATUPDATE 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 INTOcard_order_id,card_warranty_id;

#循环语句结束

UNTIL done2ENDREPEAT;CLOSEC_UPDATE_CARD_STATUS;END;END$$

DELIMITER ;