以下代码没有问题:
DELIMITER $$
DROP PROCEDURE IF EXISTS `ITCreatePickTaskSub`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `ITCreatePickTaskSub`()
BEGIN
DECLARE vSKUNO VARCHAR(50);
-- 声明一个标志done, 用来判断游标能否遍历完成
DECLARE done INT DEFAULT 0;
-- 获取手机型号数量游标
DECLARE cur CURSOR FOR SELECT Skuno FROM T_Lockgoods ;
-- 在游标循环到最后会将 done 设置为 1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- 执行查询
OPEN cur;
-- 遍历游标每一行
REPEAT
-- 把一行的信息存放在对应的变量中
FETCH cur INTO vSKUNO;
IF NOT done THEN
INSERT INTO TTaskdetial(Tdid,Skuno)
VALUES(REPLACE(UUID(),"-",""),vSKUNO);
END IF;
UNTIL done END REPEAT;
CLOSE cur;
END$$
DELIMITER ;
但是增加事务后就报错了:
DELIMITER $$
DROP PROCEDURE IF EXISTS `ITCreatePickTaskSub`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `ITCreatePickTaskSub`()
BEGIN
DECLARE vSKUNO VARCHAR(50);
-- 声明一个标志done, 用来判断游标能否遍历完成
DECLARE done INT DEFAULT 0;
-- 事务相关
DECLARE t_error INT DEFAULT 1;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=0; -- 出错处理
-- ******开始事务*********
SET t_error = 1;
START TRANSACTION;
-- 获取手机型号数量游标
DECLARE cur CURSOR FOR SELECT Skuno FROM T_Lockgoods ;
-- 在游标循环到最后会将 done 设置为 1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- 执行查询
OPEN cur;
-- 遍历游标每一行
REPEAT
-- 把一行的信息存放在对应的变量中
FETCH cur INTO vSKUNO;
IF NOT done THEN
INSERT INTO TTaskdetial(Tdid,Skuno)
VALUES(REPLACE(UUID(),"-",""),vSKUNO);
END IF;
UNTIL done END REPEAT;
CLOSE cur;
-- ******结束事务*********
IF t_error=0 THEN
SET res = "FALSE";
ROLLBACK; -- 事务回滚
ELSE
COMMIT; -- 事务提交
END IF;
END$$
DELIMITER ;
错误信息:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near “DECLARE cur CURSOR FOR SELECT Skuno FROM T_Lockgoods ;
— 在游标循环到” at line 15
哪位大牛指点一下,3Q