MySQL存储过程错误: unknown system variable

Question

我对 MySQL 5.5 中的存储过程有疑问。这是该过程必须在其上运行的表:

Table `diba`:
----------------------------------------------------
Column   | Type    | Null | Default | Links to
----------------------------------------------------
ParentID | int(11) | No   |         | articoli -> id         
ChildID  | int(11) | No   |         | articoli -> id
Quantity | int(11) | No   |
lvl      | int(11) | No   | 0            

这是程序本身:

DELIMITER //
CREATE PROCEDURE RenumberLevels()
DETERMINISTIC
BEGIN
DECLARE lvl_counter INT;
SET lvl_counter = 1;
UPDATE `diba` SET `diba`.`lvl` = CASE WHEN `diba`.`ParentID` IS NULL THEN 1 ELSE 0 END;
WHILE EXISTS (SELECT * FROM `diba` WHERE `diba`.`lvl` = 0) DO
UPDATE `diba` SET `diba`.`lvl` = lvl_counter +1 WHERE (SELECT `D2`.`lvl` FROM `diba` AS D2
WHERE D2.ChildID = `diba`.`ParentID`) > 0 AND `diba`.`lvl` = 0;
SET `diba`.`lvl` = lvl_counter + 1;
END WHILE;
END//
DELIMITER ;

产生的错误是:#1193 - 未知的系统变量“lvl”

这个过程有什么问题？

Answer 1

这是 create procedure 语句的正确版本:

DELIMITER $$
CREATE PROCEDURE `RenumberLevels`()
DETERMINISTIC
BEGIN
  DECLARE lvl_counter INT;
  SET lvl_counter = 1;
  UPDATE `diba` SET `diba`.`lvl` = CASE WHEN `diba`.`ParentID` IS NULL THEN 1 ELSE 0 END;
  WHILE EXISTS (SELECT * FROM diba WHERE lvl = 0) DO
    CREATE TABLE dibatemp AS
      SELECT D2.lvl FROM diba AS D2 LEFT JOIN diba ON diba.ParentID=D2.ChildID;
    UPDATE diba
      SET lvl = lvl_counter +1 WHERE lvl IN (SELECT lvl FROM dibatemp) > 0 AND lvl = 0;
    DROP TABLE dibatemp;
    SET lvl_counter = lvl_counter + 1;
  END WHILE;
END$$
DELIMITER ;