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
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 ;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:
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 greatWith this change your stored proc should insert correctly
更多推荐
发布评论