如何编写SQL存储过程按给定百分比更新指定productLine的Saleprice

现有代码问题 #

你当前的写法有两个明显错误：

• 变量赋值逻辑不成立：如果指定productLine下存在多条产品记录，SELECT Saleprice FROM temp_table WHERE productLine = @line会返回多个值，无法直接赋值给标量变量@newprice，即便该产品线只有一条产品，这种写法也会强制把所有同产品线的售价都统一为同一个计算后的值，不符合不同产品原售价不同、按各自价格比例调价的需求。
• UPDATE语法错误：缺少标准SQL要求的SET关键字，直接写字段赋值会触发语法报错。

正确实现逻辑 #

不需要单独查询赋值，直接在UPDATE语句中完成售价计算即可，既能避免额外查询的性能损耗，也能适配同产品线多产品的调价需求。
下面分别给出主流数据库的存储过程实现：

MySQL 版本 #

DELIMITER //
CREATE PROCEDURE UpdateProductLinePrice(
    IN p_productLine VARCHAR(50), -- 要调价的产品线
    IN p_percentage DECIMAL(5,2) -- 调价百分比，比如涨价10%就传1.1，降价20%传0.8
)
BEGIN
    UPDATE temp_table
    SET Saleprice = Saleprice * p_percentage
    WHERE productLine = p_productLine;
END //
DELIMITER ;

-- 调用示例：给产品线'Classic Cars'涨价15%
CALL UpdateProductLinePrice('Classic Cars', 1.15);

SQL Server 版本 #

CREATE PROCEDURE UpdateProductLinePrice
    @line VARCHAR(50), -- 要调价的产品线
    @percentage DECIMAL(5,2) -- 调价百分比
AS
BEGIN
    SET NOCOUNT ON; -- 避免返回影响行数的多余信息，可按需开启
    UPDATE temp_table
    SET Saleprice = Saleprice * @percentage
    WHERE productLine = @line;
END
GO

-- 调用示例：给产品线'Motorcycles'降价10%
EXEC UpdateProductLinePrice @line = 'Motorcycles', @percentage = 0.9;

补充说明 #

如果需要调价前做参数校验，比如百分比不能小于0、产品线必须存在，可以在UPDATE前加判断逻辑，以SQL Server版本为例：

IF @percentage <= 0
BEGIN
    RAISERROR('调价百分比不能小于等于0', 16, 1);
    RETURN;
END
IF NOT EXISTS (SELECT 1 FROM temp_table WHERE productLine = @line)
BEGIN
    RAISERROR('指定的产品线不存在', 16, 1);
    RETURN;
END

内容的提问来源于stack exchange，提问作者Nhung Nguyen