INSERT INTO在PROCEDURE中不起作用(INSERT INTO do not work in PROCEDURE)

这是我的程序

CREATE PROCEDURE Transakcje() BEGIN DECLARE ile INT DEFAULT 1; DROP TEMPORARY TABLE IF EXISTS temp; CREATE TEMPORARY TABLE temp ( Ilosc int ); WHILE ile <= 11 DO INSERT INTO temp (Ilosc) VALUES (ile); SET ile = ile + 1; END WHILE; INSERT INTO Sprzedaz (KlientId, ProduktNumer, Ilosc , Cena) SELECT k.KlientId, p.ProduktNumer, t.Ilosc, t.Ilosc*p.Cena AS Cena FROM klient AS k, produkt AS P, temp AS t; END // DELIMITER ;

当我运行它看起来都很好但INSERT INTO不起作用。当我单独运行INSERT INTO时，它会按我想要的方式执行操作。我不知道我做错了什么。

here is my PROCEDURE

When I run it all looks okey but INSERT INTO do not work. When i run my INSERT INTO separately it do what i want. I do not know what i do wrong.

最满意答案

将produckt的别名从P更改为p （大写到小写），如下所示：

INSERT INTO Sprzedaz (KlientId, ProduktNumer, Ilosc , Cena) SELECT k.KlientId, p.ProduktNumer, t.Ilosc, t.Ilosc*p.Cena AS Cena FROM klient AS k, produkt AS p, temp AS t;

例：

create table klient (klientid int); insert into klient values (1),(2); create table produkt (produktnumer int, cena int); insert into produkt values (1, 1), (2, 2); create table Sprzedaz(klientid int, produktnumer int, ilosc int, cena int); create table temp (ilosc int); insert into temp values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11); SELECT k.KlientId, p.produktnumer, t.Ilosc, t.Ilosc*p.Cena AS Cena FROM klient AS k, produkt AS P, temp AS t; ERROR 1054 (42S22): Unknown column 'p.produktnumer' in 'field list' SELECT k.KlientId, p.produktnumer, t.Ilosc, t.Ilosc*p.Cena AS Cena FROM klient AS k, produkt AS p, temp AS t; -- getting good results by changing alias from capital P to lower case p INSERT INTO Sprzedaz (KlientId, ProduktNumer, Ilosc , Cena) SELECT k.KlientId, p.ProduktNumer, t.Ilosc, t.Ilosc*p.Cena AS Cena FROM klient AS k, produkt AS p, temp AS t; -- insert works great

通过此更改，您的存储过程应正确插入

Change the alias of produckt from P to p (capital to lower case) like so:

INSERT INTO Sprzedaz (KlientId, ProduktNumer, Ilosc , Cena) SELECT k.KlientId, p.ProduktNumer, t.Ilosc, t.Ilosc*p.Cena AS Cena FROM klient AS k, produkt AS p, temp AS t;

Example:

With this change your stored proc should insert correctly

更多推荐