Mysql存储过程if语法

Question

我创建一个存储过程，它应该从“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;

Answer 1

在 IF 语句中使用 alt1、alt2、alt2、alt4。不是@alt1、@alt2、@alt3、@alt4。声明变量时不要使用@。