查询插入数据时引用依赖数据列表

Question

我正在学习MySql数据库。我有两张这样的桌子ServiceTable (ServiceID (PK), . .,费)TransactionTable (TransactionID (PK), ServiceID(颗),…,费)我写了一个produce来插入数据到TransactionTable:

CREATE DEFINER = 'root'@'localhost'
PROCEDURE bbet.Proc_transactiontable_CreateTransaction(IN userID varchar(36),
IN serviceID varchar(36),
...,
IN amountMoney float
)
BEGIN
SELECT @fee = s.Fee FROM servicetypetable s WHERE s.ServiceID = serviceID;
INSERT INTO transactiontable (TransactionID, ServiceID,..., Fee, TotalMoney,  ModifiedDate)
VALUES (UUID(), serviceID, amountMoney,..., @fee, amountMoney * (1 - @fee), NOW());
END

我想从ServiceTable的Fee列中引用数据，但在这种情况下@fee get null。我如何从ServiceTable的Fee栏中引用produce中的数据?

Answer 1

CREATE DEFINER = 'root'@'localhost'
PROCEDURE bbet.Proc_transactiontable_CreateTransaction(
IN userID varchar(36),
IN serviceID varchar(36),
...,
IN amountMoney float
)
INSERT INTO transactiontable (TransactionID, ServiceID,..., Fee, TotalMoney,  ModifiedDate)
SELECT UUID(), serviceID, amountMoney,..., s.Fee, amountMoney * (1 - s.Fee), NOW()
FROM servicetypetable s 
WHERE s.ServiceID = serviceID;

p。我不建议您干扰局部变量名和列名。例如，使用IN in_serviceID varchar(36)..