存储过程在应该返回任何值时不返回任何值(MySQL)

Question

我有一个存储过程，我希望它返回一个值(最后一个插入(。它返回的只是 0。 我的问题是下面的代码有什么问题？

CREATE PROCEDURE my_stored_procedure(
IN inCartId int
)
BEGIN
DECLARE orderId INT;
-- Insert a new record into orders and obtain the new order ID
INSERT INTO orders (created_on) VALUES (NOW());
-- Obtain the new Order ID
SELECT LAST_INSERT_ID() INTO orderId;
-- Insert order details in order_detail table
INSERT INTO order_detail (
order_id, product_id, attributes, product_name, quantity, unit_cost
)
SELECT
orderId,
p.id,
sc.attributes,
p.name,
sc.quantity,
COALESCE( NULLIF( p.discounted_price, 0 ), p.price ) AS unit_cost
FROM
shopping_cart sc
INNER JOIN products p ON sc.product_id = p.id
WHERE
sc.cart_id = inCartId
AND
sc.buy_now;
-- Save the order's total amount
UPDATE
orders
SET
total_amount = (
SELECT
SUM( unit_cost * quantity )
FROM
order_detail
WHERE
order_id = orderId
)
WHERE
id = orderId;
-- Clear the shopping cart
CALL shopping_cart_empty( inCartId );
END

Answer 1

• 在大多数数据库系统中，如MySQL/MariaDB和MS SQL Server，存储过程不像函数那样具有"返回值"。

• 相反，存储过程通过输出参数将数据返回给调用方。

  • 在MySQL中，FUNCTION确实返回一个值，_{但FUNCTION与存储过程不同，解释差异最好在单独的问题中完成}。

• (在Microsoft SQL Server和Sybase中，存储过程都返回一个int"返回代码"值，但是此值指示状态(例如可执行程序如何具有退出代码(，并且不用于从数据库返回数据。

• 要在MySQL和MariaDB中使用输出参数，请使用OUT关键字而不是IN关键字，然后分配给该参数。

在您的情况下，请将局部变量orderId替换为我在存储过程参数列表中添加的OUT newOrderId参数。无需进行其他更改。

CREATE PROCEDURE my_stored_procedure(
IN inCartId int,
OUT newOrderId int
)
BEGIN
-- Insert a new record into orders and obtain the new order ID
INSERT INTO orders (created_on) VALUES (NOW());
-- Obtain the new Order ID
SELECT LAST_INSERT_ID() INTO newOrderId;
-- Insert order details in order_detail table
INSERT INTO order_detail (
order_id, product_id, attributes, product_name, quantity, unit_cost
)
SELECT
orderId,
p.id,
sc.attributes,
p.name,
sc.quantity,
COALESCE( NULLIF( p.discounted_price, 0 ), p.price ) AS unit_cost
FROM
shopping_cart sc
INNER JOIN products p ON sc.product_id = p.id
WHERE
sc.cart_id = inCartId
AND
sc.buy_now;
-- Save the order's total amount
UPDATE
orders
SET
total_amount = (
SELECT
SUM( unit_cost * quantity )
FROM
order_detail
WHERE
order_id = orderId
)
WHERE
id = orderId;
-- Clear the shopping cart
CALL shopping_cart_empty(inCartId);
END