mysql过程子查询返回超过1行

Question

我用MYSQL写了一个过程，错误信息是mysql过程子查询返回超过1行，我不知道是否有人能帮助我

DELIMITER $$
CREATE DEFINER=`root`@`localhost` 
PROCEDURE `vente`(IN `Lieu` VARCHAR(5), 
                  IN `Objet` INT, IN `Quantite` INT(3), 
                  IN `jour` INT(3), IN `Id_Mois` INT(3), 
                  IN `Annee` INT(3), IN `artisan` INT(3), 
                  IN `artisan2` VARCHAR(5), IN `deco` VARCHAR(5), 
                  IN `deco2` VARCHAR(5), IN `pouvoir` VARCHAR(5), 
                  IN `Oo` VARCHAR(5), IN `Oa` VARCHAR(5), 
                  IN `of` VARCHAR(5))
BEGIN

SET @Numventee =  (SELECT ID_vente FROM commande ORDER BY ID_vente DESC LIMIT 1 ) ;
SET @Numvente = @Numventee +1 ;

INSERT INTO commande (ID_Lieu,ID_Objet,Quantite_objet,Jour,id_Mois,Annee, id_pouvoir)
SELECT Lieu,Objet,Quantite,Jour,Id_Mois,Annee, pouvoir
LIMIT 1;

INSERT INTO realiser (ID_Vente,ID_Artisan)
SELECT @Numvente,Artisan
ORDER BY ID_vente LIMIT 1;

INSERT INTO realiser (ID_vente,ID_artisan)
SELECT @Numvente,artisan2 
Where  artisan2 <> 'NULL' LIMIT 1; 

INSERT INTO composer (ID_Vente,ID_Deco)
SELECT @Numvente,Deco 
Where  Deco <> 'NULL' LIMIT 1;

INSERT INTO composer (ID_vente,ID_deco)
SELECT @Numvente,Deco2 
Where  Deco2 <> 'NULL' LIMIT 1;

INSERT INTO couter (ID_Vente,ID_Monnaie,Quantite_Monnaie)
SELECT @Numvente,1,Oo 
Where  Oo <> 'NULL' LIMIT 1;

INSERT INTO couter (ID_vente,ID_Monnaie,Quantite_Monnaie)
SELECT @Numvente,2,Oa 
Where  Oa <> 'NULL' LIMIT 1;

INSERT INTO couter (ID_vente,ID_Monnaie,Quantite_Monnaie)
SELECT @Numvente,3,Of 
Where  Of <> 'NULL' LIMIT 1;


END$$
DELIMITER ;

对不起，我不知道如何把代码放在区块笔记上

Answer 1

您应该使用LAST_INSERT_ID()来获取行的新的自动增量ID。使用IF语句，而不是使用所有这些SELECT语句来对插入进行条件化。

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `vente`(IN `Lieu` VARCHAR(5), IN `Objet` INT, IN `Quantite` INT(3), IN `jour` INT(3), IN `Id_Mois` INT(3), IN `Annee` INT(3), IN `artisan` INT(3), IN `artisan2` VARCHAR(5), IN `deco` VARCHAR(5), IN `deco2` VARCHAR(5), IN `pouvoir` VARCHAR(5), IN `Oo` VARCHAR(5), IN `Oa` VARCHAR(5), IN `of` VARCHAR(5))
BEGIN

INSERT INTO commande (ID_Lieu,ID_Objet,Quantite_objet,Jour,id_Mois,Annee, id_pouvoir)
VALUES (Lieu,Objet,Quantite,Jour,Id_Mois,Annee, pouvoir);

SET @Numvente = LAST_INSERT_ID();

INSERT INTO realiser (ID_Vente,ID_Artisan)
VALUES (@Numvente,Artisan);

IF artisan2 <> 'NULL'
THEN
    INSERT INTO realiser (ID_vente,ID_artisan)
    VALUES (@Numvente,artisan2); 
END IF;

IF Deco <> 'NULL'
THEN
    INSERT INTO composer (ID_Vente,ID_Deco)
    VALUES (@Numvente,Deco);
END IF;

ID Deco2 <> 'NULL'
THEN
    INSERT INTO composer (ID_vente,ID_deco)
    VALUES (@Numvente,Deco2);
END IF;

IF Oo <> 'NULL'
THEN
    INSERT INTO couter (ID_Vente,ID_Monnaie,Quantite_Monnaie)
    VALUES (@Numvente,1,Oo);
END IF;

IF Oa <> 'NULL'
THEN
    INSERT INTO couter (ID_vente,ID_Monnaie,Quantite_Monnaie)
    VALUES (@Numvente,2,Oa);
END IF;

IF Of <> 'NULL'
THEN
    INSERT INTO couter (ID_vente,ID_Monnaie,Quantite_Monnaie)
    VALUES (@Numvente,3,Of);
END IF;

END$$
DELIMITER ;