存储过程概念与SQL函数
存储过程概念与SQL函数
生活类比
想象你去银行办理业务：
- 存储过程就像银行的"标准业务流程"——柜员按照预定义的步骤执行操作，你只需要说"我要存款"，整个流程就会自动完成。
- 函数就像计算器上的一个功能键——输入参数，返回结果，可以嵌套在更大的计算中使用。
核心概念
什么是存储过程
存储过程（Stored Procedure）是预编译并存储在数据库中的SQL语句集合，可以：
- 接受输入参数
- 执行复杂的业务逻辑
- 返回结果集或状态码
- 提高性能（预编译、减少网络传输）
💡 SQLite 不支持存储过程，以下示例展示概念和 MySQL/PostgreSQL 语法。SQLite 用户可以使用自定义函数替代。
MySQL 存储过程语法
SQL
-- MySQL 存储过程基本结构
DELIMITER //
CREATE PROCEDURE procedure_name (参数列表)
BEGIN
-- 变量声明
-- 业务逻辑
-- 返回结果
END //
DELIMITER ;
PostgreSQL 存储过程语法
SQL
-- PostgreSQL 存储过程基本结构
CREATE OR REPLACE FUNCTION procedure_name(参数列表)
RETURNS 返回类型 AS $$
DECLARE
-- 变量声明
BEGIN
-- 业务逻辑
-- RETURN 语句
END;
$$ LANGUAGE plpgsql;
SQLite 自定义函数
SQLite 不支持存储过程，但支持通过编程语言创建用户自定义函数：
SQL
-- SQLite 本身不支持 CREATE FUNCTION
-- 需要通过 Python、C 等编程语言注册自定义函数
-- Python 示例（使用 sqlite3 模块）
import sqlite3
def calculate_bonus(salary, rate):
return salary * rate
conn = sqlite3.connect('company.db')
conn.create_function('calculate_bonus', 2, calculate_bonus)
-- 注册后可以在 SQL 中使用
SELECT name, salary, calculate_bonus(salary, 0.1) AS bonus
FROM employees;
流程控制语句
IF/ELSE 条件判断
SQL
-- MySQL 示例
DELIMITER //
CREATE PROCEDURE check_salary(IN emp_id INT)
BEGIN
DECLARE emp_salary DECIMAL(10,2);
SELECT salary INTO emp_salary
FROM employees WHERE id = emp_id;
IF emp_salary > 10000 THEN
SELECT '高薪员工' AS status;
ELSEIF emp_salary > 5000 THEN
SELECT '中等薪资' AS status;
ELSE
SELECT '低薪员工' AS status;
END IF;
END //
DELIMITER ;
WHILE 循环
SQL
-- MySQL 示例：计算1到N的和
DELIMITER //
CREATE PROCEDURE calculate_sum(IN n INT, OUT total INT)
BEGIN
DECLARE i INT DEFAULT 1;
SET total = 0;
WHILE i <= n DO
SET total = total + i;
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
LOOP 循环
SQL
-- MySQL 示例：LOOP 与 LEAVE
DELIMITER //
CREATE PROCEDURE process_employees()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE emp_name VARCHAR(100);
DECLARE cur CURSOR FOR SELECT name FROM employees;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO emp_name;
IF done THEN
LEAVE read_loop;
END IF;
-- 处理每条记录
SELECT CONCAT('Processing: ', emp_name);
END LOOP;
CLOSE cur;
END //
DELIMITER ;
游标（CURSOR）概念
游标用于逐行处理查询结果集：
SQL
-- 游标的基本使用流程
-- 1. 声明游标
DECLARE cur CURSOR FOR SELECT column FROM table;
-- 2. 打开游标
OPEN cur;
-- 3. 获取数据
FETCH cur INTO variable;
-- 4. 处理数据（通常在循环中）
-- 5. 关闭游标
CLOSE cur;
存储函数（Stored Function）
存储函数与存储过程类似，但有重要区别：
SQL
-- MySQL 存储函数
DELIMITER //
CREATE FUNCTION get_annual_salary(monthly_salary DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
RETURN monthly_salary * 12;
END //
DELIMITER ;
-- 使用函数
SELECT name, get_annual_salary(salary) AS annual_salary
FROM employees;
存储过程 vs 函数的区别
| 特性 | 存储过程 | 存储函数 |
|---|---|---|
| 调用方式 | CALL 语句 | 在 SQL 中直接调用 |
| 返回值 | 可返回多个结果集 | 必须返回单个值 |
| 使用场景 | 复杂业务逻辑 | 计算和转换 |
| 事务控制 | 可以管理事务 | 不能管理事务 |
| SQL 语句中 | 不能在 SELECT 中使用 | 可以在 SELECT 中使用 |
基本语法
创建存储过程（MySQL）
SQL
-- 创建带参数的存储过程
DELIMITER //
CREATE PROCEDURE get_employees_by_dept(
IN dept_name VARCHAR(50),
IN min_salary DECIMAL(10,2)
)
BEGIN
SELECT e.name, e.salary, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.department_name = dept_name
AND e.salary >= min_salary
ORDER BY e.salary DESC;
END //
DELIMITER ;
-- 调用存储过程
CALL get_employees_by_dept('技术部', 8000);
创建存储函数（PostgreSQL）
SQL
-- 创建计算奖金的函数
CREATE OR REPLACE FUNCTION calculate_bonus(
base_salary NUMERIC,
performance_rating INTEGER
)
RETURNS NUMERIC AS $$
DECLARE
bonus_rate NUMERIC;
BEGIN
CASE performance_rating
WHEN 1 THEN bonus_rate := 0.10;
WHEN 2 THEN bonus_rate := 0.15;
WHEN 3 THEN bonus_rate := 0.20;
WHEN 4 THEN bonus_rate := 0.25;
WHEN 5 THEN bonus_rate := 0.30;
ELSE bonus_rate := 0.05;
END CASE;
RETURN base_salary * bonus_rate;
END;
$$ LANGUAGE plpgsql;
-- 使用函数
SELECT
name,
salary,
performance_rating,
calculate_bonus(salary, performance_rating) AS bonus
FROM employees;
💡 提示：存储过程适合封装复杂的业务逻辑，而函数适合可复用的计算。在设计时应根据实际需求选择合适的方式。
示例
示例：创建员工入职处理存储过程
创建一个存储过程，处理新员工入职流程，包括分配工号、设置初始薪资、记录入职信息。
SQL
-- MySQL 版本
DELIMITER //
CREATE PROCEDURE hire_employee(
IN p_name VARCHAR(100),
IN p_department_id INT,
IN p_position VARCHAR(50),
IN p_salary DECIMAL(10,2),
OUT p_employee_id INT,
OUT p_message VARCHAR(200)
)
BEGIN
DECLARE dept_exists INT DEFAULT 0;
DECLARE max_id INT DEFAULT 0;
-- 检查部门是否存在
SELECT COUNT(*) INTO dept_exists
FROM departments WHERE id = p_department_id;
IF dept_exists = 0 THEN
SET p_message = '错误：指定的部门不存在';
SET p_employee_id = -1;
ELSE
-- 获取最大工号
SELECT COALESCE(MAX(id), 0) INTO max_id FROM employees;
SET p_employee_id = max_id + 1;
-- 插入新员工记录
INSERT INTO employees (id, name, department_id, position, salary, hire_date)
VALUES (p_employee_id, p_name, p_department_id, p_position, p_salary, CURDATE());
SET p_message = CONCAT('成功：员工 ', p_name, ' 已入职，工号为 ', p_employee_id);
END IF;
END //
DELIMITER ;
-- 调用示例
CALL hire_employee('张三', 1, '初级工程师', 8000.00, @new_id, @msg);
SELECT @new_id AS employee_id, @msg AS message;
示例：PostgreSQL 版本的员工薪资调整函数
SQL
-- PostgreSQL 版本
CREATE OR REPLACE FUNCTION adjust_salary(
p_employee_id INTEGER,
p_adjustment_type VARCHAR(10), -- 'RAISE' 或 'REDUCE'
p_percentage NUMERIC
)
RETURNS TABLE(
employee_name VARCHAR,
old_salary NUMERIC,
new_salary NUMERIC,
adjustment_amount NUMERIC
) AS $$
DECLARE
v_old_salary NUMERIC;
v_new_salary NUMERIC;
v_name VARCHAR;
BEGIN
-- 获取当前薪资
SELECT name, salary INTO v_name, v_old_salary
FROM employees WHERE id = p_employee_id;
IF NOT FOUND THEN
RAISE EXCEPTION '员工ID % 不存在', p_employee_id;
END IF;
-- 计算新薪资
IF p_adjustment_type = 'RAISE' THEN
v_new_salary := v_old_salary * (1 + p_percentage / 100);
ELSE
v_new_salary := v_old_salary * (1 - p_percentage / 100);
END IF;
-- 更新数据库
UPDATE employees SET salary = v_new_salary WHERE id = p_employee_id;
-- 返回结果
employee_name := v_name;
old_salary := v_old_salary;
new_salary := v_new_salary;
adjustment_amount := v_new_salary - v_old_salary;
RETURN NEXT;
END;
$$ LANGUAGE plpgsql;
-- 使用示例
SELECT * FROM adjust_salary(1, 'RAISE', 10);
应用场景
场景1：批量数据处理
SQL
-- MySQL：批量更新订单状态
DELIMITER //
CREATE PROCEDURE update_order_status()
BEGIN
DECLARE affected_rows INT DEFAULT 0;
-- 开始事务
START TRANSACTION;
-- 更新超过30天未付款的订单为已取消
UPDATE orders
SET status = 'cancelled',
updated_at = NOW()
WHERE status = 'pending'
AND created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
SET affected_rows = ROW_COUNT();
-- 记录处理日志
INSERT INTO process_log (process_name, affected_rows, executed_at)
VALUES ('update_order_status', affected_rows, NOW());
-- 提交事务
COMMIT;
SELECT CONCAT('已取消 ', affected_rows, ' 个过期订单') AS result;
END //
DELIMITER ;
场景2：复杂报表生成
SQL
-- PostgreSQL：生成部门薪资统计报表
CREATE OR REPLACE FUNCTION generate_salary_report()
RETURNS TABLE(
department_name VARCHAR,
employee_count BIGINT,
avg_salary NUMERIC,
min_salary NUMERIC,
max_salary NUMERIC,
total_salary NUMERIC
) AS $$
BEGIN
RETURN QUERY
SELECT
d.department_name,
COUNT(e.id) AS employee_count,
ROUND(AVG(e.salary), 2) AS avg_salary,
MIN(e.salary) AS min_salary,
MAX(e.salary) AS max_salary,
SUM(e.salary) AS total_salary
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.department_name
ORDER BY total_salary DESC;
END;
$$ LANGUAGE plpgsql;
-- 使用
SELECT * FROM generate_salary_report();
❓ 常见问题
Q：SQLite 完全不支持存储过程怎么办？ A： SQLite 设计为轻量级嵌入式数据库，不支持存储过程。可以使用编程语言（Python、Java等）实现业务逻辑，或使用视图（VIEW）封装复杂查询。
Q：存储过程和视图有什么区别？ A： 视图是虚拟表，主要用于简化查询；存储过程是程序，可以包含复杂的业务逻辑、流程控制和数据修改操作。
Q：什么时候应该使用存储过程？ A： 当需要封装复杂的业务逻辑、减少网络传输、提高性能、统一数据访问接口时使用。简单的查询使用视图更合适。
Q：存储过程如何调试？ A： MySQL 可以使用 SELECT 语句输出中间结果；PostgreSQL 可以使用 RAISE NOTICE 输出调试信息；也可以使用数据库管理工具的调试功能。
📖 小节
本课我们学习了：
- 存储过程的概念和作用
- MySQL 和 PostgreSQL 的存储过程语法
- SQLite 的自定义函数替代方案
- 流程控制语句（IF/ELSE、WHILE、LOOP）
- 游标的概念和使用方法
- 存储函数与存储过程的区别
- 实际业务场景中的应用
📝 作业
-
基础练习：创建一个存储过程，接受部门ID作为参数，返回该部门薪资最高的前3名员工信息。
-
进阶练习：创建一个函数，根据员工的工作年限（hire_date到当前日期）计算工龄工资补贴：
- 1-3年：基本工资的5%
- 3-5年：基本工资的10%
- 5年以上：基本工资的15%
-
思考题：比较在应用层代码和数据库存储过程中处理业务逻辑的优缺点。
