mysql - 我不明白为什么下面的代码出现语法错误。谁能帮我？

Question

 DELIMITER ##
 create trigger tra_Price after update on assets_cdn_charge for each row
 begin
 declare res int;
 declare ids int;
 declare idq int;
 declare idt int;
 set res = (select price from assets_cdn_charge where price = new.price);
 set ids = (select id from assets_cdn_charge where price = new.price);
 DECLARE cur CURSOR FOR SELECT id FROM assets_cdn_composite WHERE cdn_charge_id = ids;
 open cur;
 ins_loop:LOOP
 fetch cur into idq;
 declare curs cursor for select id from assets_cdn_traffic where domain_name_id = idq;
 open curs;
 ins1_loop:LOOP
 fetch curs into idt;
 update assets_cdn_traffic set cost = traffic * res where domain_namd_id = idt;
 end LOOP;
 close curs;
 end LOOP;
 close cur;
 END; ##

当我运行此代码时，我收到此错误:

ERROR 1064 (42000): 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 id FROM assets_cdn_composite WHERE cdn_charge_id =' at line 9

Answer 1

可能是因为:

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

...因此该行可能会移动到该点上方的两个 set 语句之前。

我相信，如果修复了该错误，您将会遇到其他麻烦，因为我不知道您希望该查询检索什么，因为 idq 是在该点之前声明的，但是没有设置任何值？

=====

更新:

下面是之前评论中关于完全消除光标的可能性的示例。试试这个:

BEGIN
    UPDATE assets_cdn_traffic
        JOIN assets_cdn_composite ON assets_cdn_traffic.domain_name_id = assets_cdn_composite.cdn_charge_id
        JOIN assets_cdn_charge ON assets_cdn_charge.id = assets_cdn_composite.cdn_charge_id
    SET cost = traffic * NEW.price
    WHERE assets_cdn_charge.id = NEW.id
END

但是，我会在触发器中使用之前单独尝试更新查询，以确保它按预期工作。将 NEW.price 和 NEW.id 替换为测试值以验证处理情况。