mysql procedure subquery返回超过1行

jtw3ybtb 于 2021-06-24 发布在 Mysql

关注(0)|答案(1)|浏览(295)

我用mysql写了一个程序，错误信息是mysql procedure subquery返回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 ;

抱歉，我不知道怎么把代码写在blocnote上

mysql subquery

来源：https://stackoverflow.com/questions/49760282/mysql-procedure-subquery-returns-more-than-1-row

1条答案

按热度按时间

hgqdbh6s1#

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

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 ;

赞(0）回复(0）举报 2021-06-24

我来回答

mysql procedure subquery返回超过1行

1条答案

相关问题

热门标签

最新问答