我有以下存储过程
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文档中,我可以运行SELECT id, data INTO @x, @y FROM test.t1 LIMIT 1;
。FETCH
的使用有问题吗?或者问题出在哪里?
FETCH
(从光标中获取下一条记录并前进)与SELECT ... INTO
(仅适用于返回单个记录的查询)不同。
FETCH
只能取入本地变量(没有@
前缀,但在存储程序开始时必须是DECLARE
d),而SELECT ... INTO
也可以取入用户定义的变量(确实有@
前缀,但不是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
语句之间存在种族风险),而且是一种非常笨拙的方法,本应简单得多。