mysql - 触发器声明内的局部变量存在语法错误

Question

我有以下存储过程

CREATE PROCEDURE `update_ordenes`()
BEGIN
    DECLARE record CURSOR FOR 
        SELECT ordenes.idorden, ordenes.idafiliado 
            FROM ordenes, afiliados 
            WHERE afiliados.idafiliado = ordenes.idafiliado;

    OPEN record;
    REPEAT
        FETCH record INTO @id_orden, @id_afil_viejo;

        INSERT INTO afil2(nombre, apellido, documento) 
            (SELECT nombre, apellido, documento
                FROM afiliados 
                WHERE idafiliado = @id_afil_viejo);

        SET @last_id = (SELECT id FROM afil2 ORDER BY id DESC LIMIT 1);

        UPDATE ordenes 
        SET afil2 = @last_id,
            ordenes.idafiliado = NULL
        WHERE ordenes.idafiliado = @id_afil_viejo
              AND ordenes.idorden = @orden_id;
    UNTIL done END REPEAT;
END

由于某种原因，我在 FETCH record INTO @id_orden, @id_afil_viejo; 行收到语法错误

在 mysql documentation我可以运行SELECT id, data INTO @x, @y FROM test.t1 LIMIT 1; 。是使用FETCH的问题吗？ ？或者有什么问题？

Answer 1

FETCH (从光标处抓取下一条记录并前进)与 SELECT ... INTO 不同。 (仅适用于返回单个记录的查询)。

FETCH 只能获取 local variables (没有 @ 前缀，但在存储程序的开头必须是 DECLARE d)，而 SELECT ... INTO 也可以获取 user-defined variables (确实有一个 @ 前缀并且不是 DECLARE d)。

因此，您必须DECLARE一个局部变量，您的记录将FETCH写入其中:

CREATE PROCEDURE `update_ordenes`()
BEGIN

    DECLARE id_orden BIGINT UNSIGNED DEFAULT 5;  -- as appropriate
    DECLARE id_afiv_viejo VARCHAR(15) NOT NULL;  -- as appropriate

    DECLARE record CURSOR FOR 
        SELECT ordenes.idorden, ordenes.idafiliado 
            FROM ordenes, afiliados 
            WHERE afiliados.idafiliado = ordenes.idafiliado;

    OPEN record;
    REPEAT
        FETCH record INTO id_orden, id_afil_viejo;

        INSERT INTO afil2(nombre, apellido, documento) 
            (SELECT nombre, apellido, documento
                FROM afiliados 
                WHERE idafiliado = id_afil_viejo);

        SET @last_id = (SELECT id FROM afil2 ORDER BY id DESC LIMIT 1);

        UPDATE ordenes 
        SET afil2 = @last_id,
            ordenes.idafiliado = NULL
        WHERE ordenes.idafiliado = id_afil_viejo
              AND ordenes.idorden = id_orden;
    UNTIL done END REPEAT;
END

总而言之，您的存储过程非常不安全(第一个 INSERT 语句和后面的 SET 语句之间存在竞争危险)并且做某事的方式极其笨拙这应该更简单。