mysql - MySQL的存储过程，如果语法

我创建了一个存储过程，该过程应该从“ alt1”，“ alt2”，“ alt3” alt4”列中获取值，并使用每个值创建一个新行。
此新值必须具有增量ID。
但是，如果条件从未满足，尽管我确定可以肯定有很多alt1，alt2，alt3，alt4不为空。

编辑：
在数据库上有很多alt * NOT NULL，但是在存储的变量中Alt *是ALL NULL。

先感谢您

DELIMITER $$

DROP PROCEDURE IF EXISTS smonta_alias $$
CREATE PROCEDURE smonta_alias ( )
BEGIN
 DECLARE done INT DEFAULT FALSE;
 DECLARE tname VARCHAR(255);
 DECLARE nuovo_id INT DEFAULT 5000;
 DECLARE alt1 VARCHAR(255);
 DECLARE alt2 VARCHAR(255);
 DECLARE alt3 VARCHAR(255);
 DECLARE alt4 VARCHAR(255);
 DECLARE id INT;
 DECLARE idq INT;

 DECLARE cur1 CURSOR FOR SELECT `alt1` AS alt1, `alt2` AS alt2, `alt3` AS alt3, `alt4` AS alt4, `id` AS id, `id_quota` AS idq FROM `squadra` s WHERE
                                         `s`.alt1 IS NOT NULL OR
                                         `s`.alt2 IS NOT NULL OR
                                         `s`.alt3 IS NOT NULL OR
                                         `s`.alt4 IS NOT NULL;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

 OPEN cur1;
 tables_loop: LOOP
   FETCH cur1 INTO alt1, alt2, alt3, alt4, id, idq;
   IF done THEN
     LEAVE tables_loop;
   END IF;
   IF @alt1 IS NOT NULL THEN
    SET @s = CONCAT('INSERT INTO `squadra` (id, id_quota, nome_squadra) VALUES (?,?,?)');
    SELECT @s;
    PREPARE stmtd FROM @s;
    EXECUTE stmtd USING @nuovo_id, @idq, @alt1;
    SET @nuovo_id = @nuovo_id + 1;
   END IF;

   IF @alt2 IS NOT NULL THEN
    SET @s = CONCAT('INSERT INTO `squadra` (id, id_quota, nome_squadra) VALUES (?,?,?)');
    SELECT @s;
    PREPARE stmtd FROM @s;
    EXECUTE stmtd USING @nuovo_id, @idq, @alt2;
    SET @nuovo_id = @nuovo_id + 1;
   END IF;

   IF @alt3 IS NOT NULL THEN
    SET @s = CONCAT('INSERT INTO `squadra` (id, id_quota, nome_squadra) VALUES (?,?,?)');
    SELECT @s;
    PREPARE stmtd FROM @s;
    EXECUTE stmtd USING @nuovo_id, @idq, @alt3;
    SET @nuovo_id = @nuovo_id + 1;
   END IF;

   IF @alt4 IS NOT NULL THEN
    SET @s = CONCAT('INSERT INTO `squadra` (id, id_quota, nome_squadra) VALUES (?,?,?)');
    SELECT @s;
    PREPARE stmtd FROM @s;
    EXECUTE stmtd USING @nuovo_id, @idq, @alt4;
    SET @nuovo_id = @nuovo_id + 1;
   END IF;

 END LOOP;
END $$

DELIMITER ;

CALL smonta_alias;

最佳答案

在IF语句中，使用alt1, alt2, alt2, alt4。不是@alt1, @alt2, @alt3, @alt4。
声明变量时，请勿使用@。