mysql触发器能不能使用游标 ? DROP TRIGGER IF EXISTS p_ticket_plan_insert;
DELIMITER $$CREATE
TRIGGER `tuniu`.`p_ticket_plan_insert` AFTER INSERT
ON `tuniu`.`p_ticket_plan`
FOR EACH ROW BEGIN
DECLARE $done INT DEFAULT 0;
DECLARE $a_ tinyint(1);
DECLARE $b_ tinyint(1);
DECLARE $cat_id int(11);
SELECT INTO $b_ FROM p_ticket where id = new.ticket_id;
DECLARE $cur1 CURSOR FOR SELECT ,destination_cat FROM p_ticket_cat WHERE p_ticket_id = new.ticket_id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET $done = 1;
OPEN $cur1;
REPEAT
FETCH $cur1 INTO $a_ ,$cat_id;
IF NOT $done THEN
INSERT INTO package_calendar(plan_date,flag,,plan_id,cat_id)
VALUES (new.plan_date,1,0,new.id,$cat_id);
END IF;
UNTIL $done END REPEAT;
CLOSE $cur1; END$$DELIMITER ;错误码: 1064
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 $cur1 CURSOR FOR SELECT ,destination_cat FROM p_ticket_cat WHERE p_t' at line 14
创建游标的那行报错 难道不支持?
有没其他什么方案解决?
DELIMITER $$CREATE
TRIGGER `tuniu`.`p_ticket_plan_insert` AFTER INSERT
ON `tuniu`.`p_ticket_plan`
FOR EACH ROW BEGIN
DECLARE $done INT DEFAULT 0;
DECLARE $a_ tinyint(1);
DECLARE $b_ tinyint(1);
DECLARE $cat_id int(11);
SELECT INTO $b_ FROM p_ticket where id = new.ticket_id;
DECLARE $cur1 CURSOR FOR SELECT ,destination_cat FROM p_ticket_cat WHERE p_ticket_id = new.ticket_id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET $done = 1;
OPEN $cur1;
REPEAT
FETCH $cur1 INTO $a_ ,$cat_id;
IF NOT $done THEN
INSERT INTO package_calendar(plan_date,flag,,plan_id,cat_id)
VALUES (new.plan_date,1,0,new.id,$cat_id);
END IF;
UNTIL $done END REPEAT;
CLOSE $cur1; END$$DELIMITER ;错误码: 1064
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 $cur1 CURSOR FOR SELECT ,destination_cat FROM p_ticket_cat WHERE p_t' at line 14
创建游标的那行报错 难道不支持?
有没其他什么方案解决?
DELIMITER $$CREATE
TRIGGER `tuniu`.`p_ticket_plan_insert` AFTER INSERT
ON `tuniu`.`p_ticket_plan`
FOR EACH ROW BEGIN
DECLARE $done INT DEFAULT 0;
DECLARE $a_ TINYINT(1);
DECLARE $b_ TINYINT(1);
DECLARE $cat_id INT(11);
DECLARE $cur1 CURSOR FOR SELECT ,destination_cat FROM p_ticket_cat WHERE p_ticket_id = new.ticket_id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET $done = 1;
SELECT INTO $b_ FROM p_ticket WHERE id = new.ticket_id;
OPEN $cur1;
REPEAT
FETCH $cur1 INTO $a_ ,$cat_id;
IF NOT $done THEN
INSERT INTO package_calendar(plan_date,flag,,plan_id,cat_id)
VALUES (new.plan_date,1,0,new.id,$cat_id);
END IF;
UNTIL $done END REPEAT;
CLOSE $cur1; END$$DELIMITER ;
TRIGGER `tuniu`.`p_ticket_plan_insert` AFTER INSERT
ON `tuniu`.`p_ticket_plan`
FOR EACH ROW BEGIN
DECLARE $done INT DEFAULT 0;
DECLARE $a_ tinyint(1);
DECLARE $b_ tinyint(1);
DECLARE $cat_id int(11);
SELECT INTO $b_ FROM p_ticket where id = new.ticket_id;
DECLARE $cur1 CURSOR FOR SELECT ,destination_cat FROM p_ticket_cat WHERE p_ticket_id = new.ticket_id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET $done = 1;
OPEN $cur1;
20.2.11.1.声明光标20.2.11.2. 光标OPEN语句20.2.11.3. 光标FETCH语句20.2.11.4. 光标CLOSE语句简单光标在存储程序和函数内被支持。语法如同在嵌入的SQL中。光标当前是不敏感的,只读的及不滚动的。不敏感意为服务器可以活不可以复制它的结果表。 光标必须在声明处理程序之前被声明,并且变量和条件必须在声明光标或处理程序之前被声明。例如:CREATE PROCEDURE curdemo()