mysql - INSERT INTO 在 PROCEDURE 中不起作用

Question

这是我的程序

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 时，它会执行我想要的操作。我不知道我做错了什么。

Answer 1

将 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

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