mysql存储过程调用自定义的函数

DELIMITER //

CREATE FUNCTION calculate_discount(amount DECIMAL(10, 2))
RETURNS DECIMAL(10, 2)
DETERMINISTIC
BEGIN
    DECLARE discount DECIMAL(10, 2);
    IF amount > 1000 THEN
        SET discount = amount * 0.1;
    ELSE
        SET discount = 0;
    END IF;
    RETURN discount;
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE apply_discount(IN order_id INT)
BEGIN
    DECLARE total_amount DECIMAL(10, 2);
    DECLARE discount DECIMAL(10, 2);
    SELECT amount INTO total_amount FROM orders WHERE id = order_id;
    SET discount = calculate_discount(total_amount);
    UPDATE orders SET discounted_amount = total_amount - discount WHERE id = order_id;
END //

DELIMITER ;

-- 检查函数是否存在
SHOW FUNCTION STATUS LIKE 'calculate_discount';

-- 检查存储过程是否存在
SHOW PROCEDURE STATUS LIKE 'apply_discount';

-- 授权用户执行存储过程和访问表
GRANT EXECUTE ON PROCEDURE apply_discount TO 'user'@'localhost';
GRANT SELECT, UPDATE ON orders TO 'user'@'localhost';