— 存储过程创建策略
— 1. 封装复杂的业务逻辑
— 2. 提高代码的重用性
— 3. 减少网络传输

— 函数创建策略
— 1. 封装简单的计算逻辑
— 2. 用于SQL语句中
— 3. 提高代码的可读性

— 更新存储过程
DELIMITER //
CREATE OR REPLACE PROCEDURE fgedu_get_users()
BEGIN
SELECT * FROM fgedu_users;
END //
DELIMITER ;

— 优化存储过程
— 1. 减少存储过程中的SQL语句数量
— 2. 使用适当的索引
— 3. 避免使用游标

— 监控存储过程执行情况
— 启用慢查询日志
SET GLOBAL slow_query_log = ‘ON’;
SET GLOBAL slow_query_log_file = ‘/mysql/data/fgedu-slow.log’;
SET GLOBAL long_query_time = 1;

— 创建存储过程
DELIMITER //
CREATE PROCEDURE fgedu_get_users(IN p_limit INT)
BEGIN
SELECT * FROM fgedu_users LIMIT p_limit;
END //
DELIMITER ;

— 调用存储过程
CALL fgedu_get_users(10);

— 创建带输出参数的存储过程
DELIMITER //
CREATE PROCEDURE fgedu_get_user_count(OUT p_count INT)
BEGIN
SELECT COUNT(*) INTO p_count FROM fgedu_users;
END //
DELIMITER ;

— 调用带输出参数的存储过程
SET @count = 0;
CALL fgedu_get_user_count(@count);
SELECT @count;

— 创建函数
DELIMITER //
CREATE FUNCTION fgedu_calculate_total(p_price DECIMAL(10,2), p_quantity INT)
RETURNS DECIMAL(10,2)
BEGIN
RETURN p_price * p_quantity;
END //
DELIMITER ;

— 使用函数
SELECT fgedu_calculate_total(100.00, 5) AS total;

— 创建复杂函数
DELIMITER //
CREATE FUNCTION fgedu_format_date(p_date DATE)
RETURNS VARCHAR(20)
BEGIN
RETURN DATE_FORMAT(p_date, ‘%Y-%m-%d’);
END //
DELIMITER ;

— 使用函数
SELECT fgedu_format_date(NOW()) AS formatted_date;

— 原存储过程（未优化）
DELIMITER //
CREATE PROCEDURE fgedu_get_orders()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE o_id INT;
DECLARE o_customer_id INT;
DECLARE cur CURSOR FOR SELECT id, customer_id FROM fgedu_orders;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;

read_loop:
LOOP
FETCH cur INTO o_id, o_customer_id;
IF done THEN
LEAVE read_loop;
END IF;

— 处理每个订单
SELECT * FROM fgedu_customers WHERE id = o_customer_id;
END LOOP;

CLOSE cur;
END //
DELIMITER ;

— 优化后存储过程
DELIMITER //
CREATE PROCEDURE fgedu_get_orders()
BEGIN
SELECT o.*, c.*
FROM fgedu_orders o
JOIN fgedu_customers c ON o.customer_id = c.id;
END //
DELIMITER ;

— 测试优化效果
CALL fgedu_get_orders();

— 创建存储过程：处理订单
DELIMITER //
CREATE PROCEDURE fgedu_process_order(
IN p_order_no VARCHAR(20),
IN p_customer_id INT,
IN p_amount DECIMAL(10,2),
OUT p_order_id INT
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT ‘Error processing order’ AS message;
END;

START TRANSACTION;

— 插入订单
INSERT INTO fgedu_orders (order_no, customer_id, amount, status, created_at)
VALUES (p_order_no, p_customer_id, p_amount, ‘PAID’, NOW());

— 获取订单ID
SET p_order_id = LAST_INSERT_ID();

— 更新库存
UPDATE fgedu_products SET stock = stock – 1 WHERE id = 1;

COMMIT;

SELECT ‘Order processed successfully’ AS message;
END //
DELIMITER ;

— 调用存储过程
SET @order_id = 0;
CALL fgedu_process_order(‘ORD1001’, 1, 100.00, @order_id);
SELECT @order_id;

— 创建函数：计算折扣
DELIMITER //
CREATE FUNCTION fgedu_calculate_discount(p_amount DECIMAL(10,2))
RETURNS DECIMAL(10,2)
BEGIN
DECLARE discount DECIMAL(10,2);

IF p_amount >= 1000 THEN
SET discount = p_amount * 0.1;
ELSEIF p_amount >= 500 THEN
SET discount = p_amount * 0.05;
ELSE
SET discount = 0;
END IF;

RETURN discount;
END //
DELIMITER ;

— 使用函数
SELECT
order_no,
amount,
fgedu_calculate_discount(amount) AS discount,
amount – fgedu_calculate_discount(amount) AS final_amount
FROM fgedu_orders;

— 优化前：使用游标和多次查询
DELIMITER //
CREATE PROCEDURE fgedu_get_sales_report()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE p_id INT;
DECLARE p_name VARCHAR(100);
DECLARE cur CURSOR FOR SELECT id, name FROM fgedu_products;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

CREATE TEMPORARY TABLE temp_report (
product_id INT,
product_name VARCHAR(100),
total_sales DECIMAL(10,2)
);

OPEN cur;

read_loop:
LOOP
FETCH cur INTO p_id, p_name;
IF done THEN
LEAVE read_loop;
END IF;

— 计算每个产品的销售额
INSERT INTO temp_report (product_id, product_name, total_sales)
SELECT p_id, p_name, SUM(amount)
FROM fgedu_sales
WHERE product_id = p_id;
END LOOP;

CLOSE cur;

— 返回结果
SELECT * FROM temp_report;
DROP TEMPORARY TABLE temp_report;
END //
DELIMITER ;

— 优化后：使用单次查询
DELIMITER //
CREATE PROCEDURE fgedu_get_sales_report()
BEGIN
SELECT
p.id AS product_id,
p.name AS product_name,
SUM(s.amount) AS total_sales
FROM fgedu_products p
LEFT JOIN fgedu_sales s ON p.id = s.product_id
GROUP BY p.id, p.name;
END //
DELIMITER ;

— 测试优化效果
CALL fgedu_get_sales_report();