第10课: 存储过程与函数

PROCEDURE、FUNCTION、参数、流程控制

存储过程基础

存储过程是一组预编译的SQL语句集合，可以接受参数、执行复杂逻辑并返回结果。

创建存储过程

存储过程结构示意图

-- 示例1: 简单存储过程
DELIMITER //
CREATE PROCEDURE GetUserCount()
BEGIN
    SELECT COUNT(*) AS user_count FROM users;
END //
DELIMITER ;

-- 调用存储过程
CALL GetUserCount();

-- 示例2: 带输入参数的存储过程
DELIMITER //
CREATE PROCEDURE GetUsersByAge(IN min_age INT)
BEGIN
    SELECT username, email, age
    FROM users
    WHERE age >= min_age
    ORDER BY age;
END //
DELIMITER ;

-- 调用
CALL GetUsersByAge(18);

-- 示例3: 带输出参数的存储过程
DELIMITER //
CREATE PROCEDURE GetOrderStats(
    IN user_id INT,
    OUT order_count INT,
    OUT total_amount DECIMAL(10,2)
)
BEGIN
    SELECT COUNT(*), COALESCE(SUM(total_amount), 0)
    INTO order_count, total_amount
    FROM orders
    WHERE orders.user_id = user_id;
END //
DELIMITER ;

-- 调用
CALL GetOrderStats(1, @count, @total);
SELECT @count, @total;

流程控制

-- 示例4: IF 条件判断
DELIMITER //
CREATE PROCEDURE CheckStock(IN product_id INT)
BEGIN
    DECLARE stock_count INT;

    SELECT stock INTO stock_count
    FROM products
    WHERE products.product_id = product_id;

    IF stock_count > 100 THEN
        SELECT '库存充足' AS status;
    ELSEIF stock_count > 0 THEN
        SELECT '库存紧张' AS status;
    ELSE
        SELECT '缺货' AS status;
    END IF;
END //
DELIMITER ;

-- 示例5: CASE 语句
DELIMITER //
CREATE PROCEDURE GetUserLevel(IN user_id INT)
BEGIN
    DECLARE total DECIMAL(10,2);

    SELECT COALESCE(SUM(total_amount), 0) INTO total
    FROM orders WHERE orders.user_id = user_id;

    SELECT
        CASE
            WHEN total >= 10000 THEN 'VIP'
            WHEN total >= 5000 THEN '高级会员'
            WHEN total >= 1000 THEN '普通会员'
            ELSE '新用户'
        END AS user_level,
        total AS total_spending;
END //
DELIMITER ;

-- 示例6: WHILE 循环
DELIMITER //
CREATE PROCEDURE GenerateNumbers(IN max_num INT)
BEGIN
    DECLARE counter INT DEFAULT 1;

    CREATE TEMPORARY TABLE IF NOT EXISTS temp_numbers (num INT);

    WHILE counter <= max_num DO
        INSERT INTO temp_numbers VALUES (counter);
        SET counter = counter + 1;
    END WHILE;

    SELECT * FROM temp_numbers;
    DROP TEMPORARY TABLE temp_numbers;
END //
DELIMITER ;

存储函数

存储函数与存储过程类似，但必须返回一个值，可以在SQL语句中直接调用。

-- 示例7: 创建存储函数
DELIMITER //
CREATE FUNCTION CalculateDiscount(price DECIMAL(10,2), discount_rate DECIMAL(3,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN price * (1 - discount_rate);
END //
DELIMITER ;

-- 使用函数
SELECT product_name, price, CalculateDiscount(price, 0.1) AS discounted_price
FROM products;

-- 示例8: 计算用户总消费
DELIMITER //
CREATE FUNCTION GetUserTotalSpending(user_id INT)
RETURNS DECIMAL(10,2)
READS SQL DATA
BEGIN
    DECLARE total DECIMAL(10,2);

    SELECT COALESCE(SUM(total_amount), 0) INTO total
    FROM orders
    WHERE orders.user_id = user_id;

    RETURN total;
END //
DELIMITER ;

-- 使用
SELECT username, GetUserTotalSpending(id) AS total_spending
FROM users;

游标使用

-- 示例9: 使用游标遍历数据
DELIMITER //
CREATE PROCEDURE ProcessOrders()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_order_id INT;
    DECLARE v_total DECIMAL(10,2);

    DECLARE order_cursor CURSOR FOR
        SELECT order_id, total_amount FROM orders WHERE status = 'pending';

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN order_cursor;

    read_loop: LOOP
        FETCH order_cursor INTO v_order_id, v_total;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 处理每个订单
        IF v_total > 1000 THEN
            UPDATE orders SET status = 'vip_processing'
            WHERE order_id = v_order_id;
        END IF;
    END LOOP;

    CLOSE order_cursor;
END //
DELIMITER ;

管理存储过程和函数

-- 查看存储过程
SHOW PROCEDURE STATUS WHERE Db = 'your_database';
SHOW CREATE PROCEDURE GetUserCount;

-- 查看存储函数
SHOW FUNCTION STATUS WHERE Db = 'your_database';
SHOW CREATE FUNCTION CalculateDiscount;

-- 删除存储过程和函数
DROP PROCEDURE IF EXISTS GetUserCount;
DROP FUNCTION IF EXISTS CalculateDiscount;

存储过程最佳实践：

命名规范：使用清晰的命名，如 sp_GetUserInfo
参数验证：检查输入参数的有效性
异常处理：使用 DECLARE HANDLER 处理异常
事务控制：在存储过程中合理使用事务
避免过度使用：复杂业务逻辑建议在应用层处理
性能考虑：避免在存储过程中使用游标处理大量数据

实战场景：订单处理

-- 示例10: 完整的下单存储过程
DELIMITER //
CREATE PROCEDURE CreateOrder(
    IN p_user_id INT,
    IN p_product_id INT,
    IN p_quantity INT,
    OUT p_order_id INT,
    OUT p_message VARCHAR(200)
)
BEGIN
    DECLARE v_stock INT;
    DECLARE v_price DECIMAL(10,2);
    DECLARE v_total DECIMAL(10,2);

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_message = '订单创建失败';
    END;

    START TRANSACTION;

    -- 检查库存
    SELECT stock, price INTO v_stock, v_price
    FROM products
    WHERE product_id = p_product_id FOR UPDATE;

    IF v_stock < p_quantity THEN
        SET p_message = '库存不足';
        ROLLBACK;
    ELSE
        -- 扣减库存
        UPDATE products
        SET stock = stock - p_quantity
        WHERE product_id = p_product_id;

        -- 计算总价
        SET v_total = v_price * p_quantity;

        -- 创建订单
        INSERT INTO orders (user_id, total_amount, status, order_date)
        VALUES (p_user_id, v_total, 'pending', NOW());

        SET p_order_id = LAST_INSERT_ID();

        -- 创建订单明细
        INSERT INTO order_items (order_id, product_id, quantity, price)
        VALUES (p_order_id, p_product_id, p_quantity, v_price);

        COMMIT;
        SET p_message = '订单创建成功';
    END IF;
END //
DELIMITER ;

-- 调用
CALL CreateOrder(1, 100, 2, @order_id, @msg);
SELECT @order_id, @msg;

练习题：

创建一个存储过程，查询指定分类的所有商品
创建一个带输出参数的存储过程，返回用户的订单数量和总金额
创建一个存储函数，计算商品的折扣价格
创建一个存储过程，使用IF判断库存状态（充足/紧张/缺货）
创建一个存储过程，批量更新商品价格（使用游标）
创建一个存储函数，根据总消费金额返回用户等级
查看数据库中所有的存储过程
创建一个完整的退款存储过程（包含事务和异常处理）

项目案例：电商系统核心功能

基于所学的进阶知识，设计并实现电商系统的核心功能，包括订单处理、库存管理、用户管理等。

-- 1. 创建数据库和表结构
CREATE DATABASE ecommerce;
USE ecommerce;

-- 创建用户表
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    address VARCHAR(200),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('active', 'inactive') DEFAULT 'active'
);

-- 创建商品表
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('active', 'inactive') DEFAULT 'active'
);

-- 创建订单表
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    shipping_address VARCHAR(200) NOT NULL,
    payment_method VARCHAR(50) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- 创建订单详情表
CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- 创建库存日志表
CREATE TABLE inventory_logs (
    log_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    type ENUM('in', 'out') NOT NULL,
    reason VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- 2. 添加索引优化性能
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_products_category ON products(category);
CREATE INDEX idx_products_status ON products(status);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

-- 3. 插入示例数据
INSERT INTO users (username, email, password, phone, address) VALUES
('张三', 'zhangsan@example.com', 'password123', '13800138001', '北京市海淀区'),
('李四', 'lisi@example.com', 'password123', '13800138002', '北京市朝阳区'),
('王五', 'wangwu@example.com', 'password123', '13800138003', '北京市西城区');

INSERT INTO products (product_name, category, price, stock, description) VALUES
('iPhone 14', '手机', 5999.00, 100, '苹果智能手机'),
('华为 Mate 50', '手机', 4999.00, 80, '华为智能手机'),
('小米 13', '手机', 3999.00, 120, '小米智能手机'),
('MacBook Pro', '电脑', 9999.00, 50, '苹果笔记本电脑'),
('ThinkPad X1', '电脑', 8999.00, 60, '联想笔记本电脑');

-- 4. 创建存储过程和函数

-- 4.1 创建存储过程：处理订单
DELIMITER //
CREATE PROCEDURE process_order(
    IN p_user_id INT,
    IN p_shipping_address VARCHAR(200),
    IN p_payment_method VARCHAR(50),
    IN p_order_items JSON
)
BEGIN
    DECLARE v_order_id INT;
    DECLARE v_total_amount DECIMAL(10,2) DEFAULT 0;
    DECLARE v_product_id INT;
    DECLARE v_quantity INT;
    DECLARE v_unit_price DECIMAL(10,2);
    DECLARE v_current_stock INT;
    DECLARE v_i INT DEFAULT 0;
    DECLARE v_item_count INT;
    
    -- 开始事务
    START TRANSACTION;
    
    -- 获取订单商品数量
    SET v_item_count = JSON_LENGTH(p_order_items);
    
    -- 检查库存并计算总金额
    WHILE v_i < v_item_count DO
        SET v_product_id = JSON_EXTRACT(p_order_items, CONCAT('$[', v_i, '].product_id'));
        SET v_quantity = JSON_EXTRACT(p_order_items, CONCAT('$[', v_i, '].quantity'));
        
        -- 获取商品价格和库存
        SELECT price, stock INTO v_unit_price, v_current_stock FROM products WHERE product_id = v_product_id FOR UPDATE;
        
        -- 检查库存
        IF v_current_stock < v_quantity THEN
            ROLLBACK;
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = CONCAT('商品库存不足: ', v_product_id);
        END IF;
        
        -- 计算总金额
        SET v_total_amount = v_total_amount + (v_unit_price * v_quantity);
        
        SET v_i = v_i + 1;
    END WHILE;
    
    -- 创建订单
    INSERT INTO orders (user_id, total_amount, shipping_address, payment_method) 
    VALUES (p_user_id, v_total_amount, p_shipping_address, p_payment_method);
    
    SET v_order_id = 1456657041;
    
    -- 重置计数器
    SET v_i = 0;
    
    -- 处理订单商品
    WHILE v_i < v_item_count DO
        SET v_product_id = JSON_EXTRACT(p_order_items, CONCAT('$[', v_i, '].product_id'));
        SET v_quantity = JSON_EXTRACT(p_order_items, CONCAT('$[', v_i, '].quantity'));
        
        -- 获取商品价格
        SELECT price INTO v_unit_price FROM products WHERE product_id = v_product_id;
        
        -- 插入订单详情
        INSERT INTO order_items (order_id, product_id, quantity, unit_price) 
        VALUES (v_order_id, v_product_id, v_quantity, v_unit_price);
        
        -- 更新库存
        UPDATE products SET stock = stock - v_quantity WHERE product_id = v_product_id;
        
        -- 记录库存日志
        INSERT INTO inventory_logs (product_id, quantity, type, reason) 
        VALUES (v_product_id, v_quantity, 'out', CONCAT('订单出库: ', v_order_id));
        
        SET v_i = v_i + 1;
    END WHILE;
    
    -- 提交事务
    COMMIT;
    
    -- 返回订单ID
    SELECT v_order_id AS order_id;
END //
DELIMITER ;

-- 4.2 创建存储过程：查询订单详情
DELIMITER //
CREATE PROCEDURE get_order_details(
    IN p_order_id INT
)
BEGIN
    SELECT
        o.order_id,
        o.user_id,
        u.username,
        o.order_date,
        o.total_amount,
        o.status,
        o.shipping_address,
        o.payment_method,
        oi.order_item_id,
        p.product_id,
        p.product_name,
        oi.quantity,
        oi.unit_price,
        (oi.quantity * oi.unit_price) AS item_total
    FROM orders o
    JOIN users u ON o.user_id = u.user_id
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    WHERE o.order_id = p_order_id;
END //
DELIMITER ;

-- 4.3 创建函数：计算商品折扣价格
DELIMITER //
CREATE FUNCTION calculate_discount(
    p_price DECIMAL(10,2),
    p_discount_percent INT
)
RETURNS DECIMAL(10,2)
BEGIN
    DECLARE v_discount_amount DECIMAL(10,2);
    DECLARE v_final_price DECIMAL(10,2);
    
    SET v_discount_amount = p_price * (p_discount_percent / 100);
    SET v_final_price = p_price - v_discount_amount;
    
    RETURN v_final_price;
END //
DELIMITER ;

-- 4.4 创建触发器：商品入库时记录日志
DELIMITER //
CREATE TRIGGER after_product_stock_update
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    DECLARE v_stock_change INT;
    
    SET v_stock_change = NEW.stock - OLD.stock;
    
    IF v_stock_change > 0 THEN
        INSERT INTO inventory_logs (product_id, quantity, type, reason) 
        VALUES (NEW.product_id, v_stock_change, 'in', '库存更新');
    END IF;
END //
DELIMITER ;

-- 5. 项目实战练习

-- 5.1 使用存储过程创建订单
-- 示例：创建一个包含2个商品的订单
CALL process_order(
    1, 
    '北京市海淀区中关村大街1号', 
    '支付宝', 
    '[{"product_id": 1, "quantity": 1}, {"product_id": 3, "quantity": 2}]'
);

-- 5.2 查询订单详情
CALL get_order_details(1);

-- 5.3 使用函数计算折扣价格
SELECT 
    product_id, 
    product_name, 
    price, 
    calculate_discount(price, 10) AS discount_price
FROM products;

-- 5.4 多表查询：用户订单统计
SELECT
    u.user_id,
    u.username,
    COUNT(o.order_id) AS order_count,
    SUM(o.total_amount) AS total_spending,
    MAX(o.order_date) AS last_order_date
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.username
ORDER BY total_spending DESC;

-- 5.5 聚合查询：商品销售统计
SELECT
    p.product_id,
    p.product_name,
    p.category,
    SUM(oi.quantity) AS total_sold,
    SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name, p.category
ORDER BY total_revenue DESC;

-- 5.6 事务处理：订单状态更新
START TRANSACTION;

-- 更新订单状态
UPDATE orders SET status = 'shipped' WHERE order_id = 1;

-- 记录操作日志（假设存在操作日志表）
-- INSERT INTO operation_logs (user_id, action, target_id, created_at) VALUES (1, 'update_order_status', 1, NOW());

COMMIT;

-- 6. 性能优化

-- 6.1 查看执行计划
EXPLAIN SELECT
    u.user_id,
    u.username,
    COUNT(o.order_id) AS order_count,
    SUM(o.total_amount) AS total_spending
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.username
ORDER BY total_spending DESC;

-- 6.2 添加复合索引
CREATE INDEX idx_orders_user_id_status ON orders(user_id, status);

-- 6.3 分区表（可选）
-- 按日期分区订单表
ALTER TABLE orders PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026)
);

-- 7. 项目扩展

-- 7.1 添加评论系统
CREATE TABLE reviews (
    review_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    user_id INT NOT NULL,
    rating INT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    comment TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- 7.2 添加购物车功能
CREATE TABLE cart (
    cart_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    UNIQUE KEY (user_id, product_id)
);

-- 7.3 创建定期清理过期订单的事件
DELIMITER //
CREATE EVENT cleanup_expired_orders
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 00:00:00'
DO
BEGIN
    -- 清理30天前的pending订单
    UPDATE orders SET status = 'cancelled' 
    WHERE status = 'pending' AND order_date < DATE_SUB(NOW(), INTERVAL 30 DAY);
END //
DELIMITER ;

-- 启用事件调度器
SET GLOBAL event_scheduler = ON;

项目要求：

创建完整的电商系统数据库结构
实现订单处理、库存管理、用户管理等核心功能
使用存储过程处理复杂业务逻辑
使用触发器记录操作日志
使用函数实现业务计算
添加适当的索引优化性能
实现事务处理确保数据一致性
编写定期维护的事件
扩展系统功能，如评论系统、购物车等
编写前端应用程序连接数据库并展示功能

上一课下一课