存储过程与函数基础

一、存储过程基础

1.1 存储过程概念

1.1.1 什么是存储过程

存储过程：一组预编译的SQL语句，存储在数据库中，可以重复调用。

东巴文理解：

存储过程 = 菜谱
菜谱记录了做菜的步骤
存储过程记录了SQL的执行步骤
按照菜谱做菜，按照存储过程执行SQL

1.1.2 存储过程的优缺点

优点：

• 减少网络传输
• 提高执行效率
• 代码复用
• 安全性高

缺点：

• 难以调试
• 可移植性差
• 占用数据库资源

1.2 创建存储过程

1.2.1 基本语法

语法格式：

CREATE PROCEDURE 存储过程名([参数])
BEGIN
    SQL语句;
END;

示例：

-- 修改分隔符
DELIMITER //

-- 创建存储过程
CREATE PROCEDURE get_all_employees()
BEGIN
    SELECT * FROM employees;
END //

-- 恢复分隔符
DELIMITER ;

1.2.2 调用存储过程

语法格式：

CALL 存储过程名([参数]);

示例：

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

1.3 参数类型

1.3.1 IN参数

特点：输入参数，只读

示例：

DELIMITER //

CREATE PROCEDURE get_employee_by_id(IN emp_id INT)
BEGIN
    SELECT * FROM employees WHERE id = emp_id;
END //

DELIMITER ;

-- 调用
CALL get_employee_by_id(1);

1.3.2 OUT参数

特点：输出参数，可写

示例：

DELIMITER //

CREATE PROCEDURE get_employee_count(OUT emp_count INT)
BEGIN
    SELECT COUNT(*) INTO emp_count FROM employees;
END //

DELIMITER ;

-- 调用
CALL get_employee_count(@count);
SELECT @count;

1.3.3 INOUT参数

特点：输入输出参数，可读可写

示例：

DELIMITER //

CREATE PROCEDURE double_value(INOUT num INT)
BEGIN
    SET num = num * 2;
END //

DELIMITER ;

-- 调用
SET @num = 10;
CALL double_value(@num);
SELECT @num;  -- 输出：20

1.4 删除存储过程

语法格式：

DROP PROCEDURE [IF EXISTS] 存储过程名;

示例：

-- 删除存储过程
DROP PROCEDURE IF EXISTS get_all_employees;

1.5 查看存储过程

1.5.1 查看存储过程列表

-- 查看指定数据库的存储过程
SELECT ROUTINE_NAME, ROUTINE_TYPE 
FROM information_schema.ROUTINES 
WHERE ROUTINE_SCHEMA = 'database_name';

1.5.2 查看存储过程定义

-- 查看存储过程定义
SHOW CREATE PROCEDURE get_all_employees;

二、变量

2.1 局部变量

2.1.1 声明变量

语法格式：

DECLARE 变量名 数据类型 [DEFAULT 默认值];

示例：

DELIMITER //

CREATE PROCEDURE test_variable()
BEGIN
    -- 声明变量
    DECLARE name VARCHAR(50) DEFAULT '张三';
    DECLARE age INT DEFAULT 25;
    
    SELECT name, age;
END //

DELIMITER ;

-- 调用
CALL test_variable();

2.1.2 变量赋值

使用SET：

DELIMITER //

CREATE PROCEDURE test_set()
BEGIN
    DECLARE name VARCHAR(50);
    SET name = '李四';
    SELECT name;
END //

DELIMITER ;

使用SELECT INTO：

DELIMITER //

CREATE PROCEDURE test_select_into(IN emp_id INT)
BEGIN
    DECLARE emp_name VARCHAR(50);
    DECLARE emp_salary DECIMAL(10, 2);
    
    SELECT name, salary INTO emp_name, emp_salary
    FROM employees WHERE id = emp_id;
    
    SELECT emp_name, emp_salary;
END //

DELIMITER ;

2.2 用户变量

2.2.1 定义用户变量

语法格式：

SET @变量名 = 值;
-- 或
SET @变量名 := 值;

示例：

-- 定义用户变量
SET @name = '张三';
SET @age := 25;

-- 使用用户变量
SELECT @name, @age;

-- 在SQL中使用
SELECT * FROM employees WHERE name = @name;

2.3 系统变量

2.3.1 查看系统变量

-- 查看所有系统变量
SHOW VARIABLES;

-- 查看特定系统变量
SHOW VARIABLES LIKE 'max_connections';

2.3.2 设置系统变量

会话级别：

SET SESSION max_connections = 200;
-- 或
SET @@session.max_connections = 200;

全局级别：

SET GLOBAL max_connections = 200;
-- 或
SET @@global.max_connections = 200;

三、流程控制

3.1 IF语句

3.1.1 基本语法

语法格式：

IF 条件 THEN
    语句;
ELSEIF 条件 THEN
    语句;
ELSE
    语句;
END IF;

示例：

DELIMITER //

CREATE PROCEDURE get_grade(IN score DECIMAL(5, 2), OUT grade CHAR(1))
BEGIN
    IF score >= 90 THEN
        SET grade = 'A';
    ELSEIF score >= 80 THEN
        SET grade = 'B';
    ELSEIF score >= 70 THEN
        SET grade = 'C';
    ELSEIF score >= 60 THEN
        SET grade = 'D';
    ELSE
        SET grade = 'E';
    END IF;
END //

DELIMITER ;

-- 调用
CALL get_grade(85, @grade);
SELECT @grade;  -- 输出：B

3.2 CASE语句

3.2.1 基本语法

语法格式：

CASE 
    WHEN 条件1 THEN 语句1
    WHEN 条件2 THEN 语句2
    ELSE 语句3
END CASE;

示例：

DELIMITER //

CREATE PROCEDURE get_season(IN month INT, OUT season VARCHAR(10))
BEGIN
    CASE 
        WHEN month IN (3, 4, 5) THEN
            SET season = '春季';
        WHEN month IN (6, 7, 8) THEN
            SET season = '夏季';
        WHEN month IN (9, 10, 11) THEN
            SET season = '秋季';
        WHEN month IN (12, 1, 2) THEN
            SET season = '冬季';
        ELSE
            SET season = '无效月份';
    END CASE;
END //

DELIMITER ;

-- 调用
CALL get_season(5, @season);
SELECT @season;  -- 输出：春季

3.3 LOOP循环

3.3.1 基本语法

语法格式：

[标签:] LOOP
    语句;
    IF 退出条件 THEN
        LEAVE 标签;
    END IF;
END LOOP [标签];

示例：

DELIMITER //

CREATE PROCEDURE test_loop(OUT result INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET result = 0;
    
    add_loop: LOOP
        SET i = i + 1;
        SET result = result + i;
        
        IF i >= 10 THEN
            LEAVE add_loop;
        END IF;
    END LOOP add_loop;
END //

DELIMITER ;

-- 调用
CALL test_loop(@result);
SELECT @result;  -- 输出：55

3.4 WHILE循环

3.4.1 基本语法

语法格式：

[标签:] WHILE 条件 DO
    语句;
END WHILE [标签];

示例：

DELIMITER //

CREATE PROCEDURE test_while(OUT result INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET result = 0;
    
    WHILE i < 10 DO
        SET i = i + 1;
        SET result = result + i;
    END WHILE;
END //

DELIMITER ;

-- 调用
CALL test_while(@result);
SELECT @result;  -- 输出：55

3.5 REPEAT循环

3.5.1 基本语法

语法格式：

[标签:] REPEAT
    语句;
UNTIL 条件 END REPEAT [标签];

示例：

DELIMITER //

CREATE PROCEDURE test_repeat(OUT result INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET result = 0;
    
    REPEAT
        SET i = i + 1;
        SET result = result + i;
    UNTIL i >= 10 END REPEAT;
END //

DELIMITER ;

-- 调用
CALL test_repeat(@result);
SELECT @result;  -- 输出：55

3.6 ITERATE语句

3.6.1 基本语法

ITERATE：跳过当前循环的剩余语句，进入下一次循环。

示例：

DELIMITER //

CREATE PROCEDURE test_iterate(OUT result INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET result = 0;
    
    add_loop: LOOP
        SET i = i + 1;
        
        -- 跳过偶数
        IF i % 2 = 0 THEN
            ITERATE add_loop;
        END IF;
        
        SET result = result + i;
        
        IF i >= 10 THEN
            LEAVE add_loop;
        END IF;
    END LOOP add_loop;
END //

DELIMITER ;

-- 调用
CALL test_iterate(@result);
SELECT @result;  -- 输出：25（1+3+5+7+9）

四、游标

4.1 游标概念

4.1.1 什么是游标

游标：用于遍历查询结果集的指针。

东巴文理解：

游标 = 书签
看书时用书签标记位置
游标标记结果集的当前位置
可以逐行读取数据

4.2 游标操作

4.2.1 声明游标

语法格式：

DECLARE 游标名 CURSOR FOR SELECT语句;

4.2.2 打开游标

语法格式：

OPEN 游标名;

4.2.3 获取数据

语法格式：

FETCH 游标名 INTO 变量列表;

4.2.4 关闭游标

语法格式：

CLOSE 游标名;

4.3 游标示例

4.3.1 基本示例

DELIMITER //

CREATE PROCEDURE process_employees()
BEGIN
    -- 声明变量
    DECLARE done INT DEFAULT FALSE;
    DECLARE emp_id INT;
    DECLARE emp_name VARCHAR(50);
    DECLARE emp_salary DECIMAL(10, 2);
    
    -- 声明游标
    DECLARE emp_cursor CURSOR FOR 
        SELECT id, name, salary FROM employees;
    
    -- 声明结束处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 创建临时表存储结果
    CREATE TEMPORARY TABLE IF NOT EXISTS emp_results (
        id INT,
        name VARCHAR(50),
        salary DECIMAL(10, 2),
        salary_level VARCHAR(10)
    );
    
    -- 清空临时表
    TRUNCATE TABLE emp_results;
    
    -- 打开游标
    OPEN emp_cursor;
    
    -- 循环读取数据
    read_loop: LOOP
        FETCH emp_cursor INTO emp_id, emp_name, emp_salary;
        
        -- 判断是否结束
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 处理数据
        IF emp_salary >= 15000 THEN
            INSERT INTO emp_results VALUES (emp_id, emp_name, emp_salary, '高');
        ELSEIF emp_salary >= 10000 THEN
            INSERT INTO emp_results VALUES (emp_id, emp_name, emp_salary, '中');
        ELSE
            INSERT INTO emp_results VALUES (emp_id, emp_name, emp_salary, '低');
        END IF;
    END LOOP;
    
    -- 关闭游标
    CLOSE emp_cursor;
    
    -- 返回结果
    SELECT * FROM emp_results;
    
    -- 删除临时表
    DROP TEMPORARY TABLE IF EXISTS emp_results;
END //

DELIMITER ;

-- 调用
CALL process_employees();

五、存储函数

5.1 存储函数概念

5.1.1 什么是存储函数

存储函数：返回单个值的存储过程。

与存储过程的区别：

5.2 创建存储函数

5.2.1 基本语法

语法格式：

CREATE FUNCTION 函数名([参数])
RETURNS 返回类型
[DETERMINISTIC|NOT DETERMINISTIC]
BEGIN
    SQL语句;
    RETURN 返回值;
END;

参数说明：

• DETERMINISTIC：相同输入总是返回相同输出
• NOT DETERMINISTIC：相同输入可能返回不同输出

示例：

DELIMITER //

CREATE FUNCTION get_employee_name(emp_id INT)
RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
    DECLARE emp_name VARCHAR(50);
    SELECT name INTO emp_name FROM employees WHERE id = emp_id;
    RETURN emp_name;
END //

DELIMITER ;

-- 调用
SELECT get_employee_name(1);

5.3 存储函数示例

5.3.1 计算函数

DELIMITER //

CREATE FUNCTION calculate_tax(salary DECIMAL(10, 2))
RETURNS DECIMAL(10, 2)
DETERMINISTIC
BEGIN
    DECLARE tax DECIMAL(10, 2);
    
    IF salary <= 5000 THEN
        SET tax = 0;
    ELSEIF salary <= 10000 THEN
        SET tax = (salary - 5000) * 0.1;
    ELSEIF salary <= 20000 THEN
        SET tax = 500 + (salary - 10000) * 0.2;
    ELSE
        SET tax = 2500 + (salary - 20000) * 0.3;
    END IF;
    
    RETURN tax;
END //

DELIMITER ;

-- 调用
SELECT name, salary, calculate_tax(salary) AS tax
FROM employees;

5.3.2 字符串函数

DELIMITER //

CREATE FUNCTION mask_phone(phone VARCHAR(20))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    RETURN CONCAT(
        LEFT(phone, 3),
        '****',
        RIGHT(phone, 4)
    );
END //

DELIMITER ;

-- 调用
SELECT name, mask_phone(phone) AS masked_phone
FROM employees;

5.4 删除存储函数

语法格式：

DROP FUNCTION [IF EXISTS] 函数名;

示例：

-- 删除存储函数
DROP FUNCTION IF EXISTS get_employee_name;

5.5 查看存储函数

5.5.1 查看存储函数列表

-- 查看指定数据库的存储函数
SELECT ROUTINE_NAME, ROUTINE_TYPE 
FROM information_schema.ROUTINES 
WHERE ROUTINE_SCHEMA = 'database_name' AND ROUTINE_TYPE = 'FUNCTION';

5.5.2 查看存储函数定义

-- 查看存储函数定义
SHOW CREATE FUNCTION get_employee_name;

六、错误处理

6.1 错误处理程序

6.1.1 基本语法

语法格式：

DECLARE 处理类型 HANDLER FOR 错误类型 处理语句;

处理类型：

• CONTINUE：继续执行
• EXIT：退出

错误类型：

• SQLSTATE值
• SQL错误码
• SQLWARNING
• NOT FOUND
• SQLEXCEPTION

6.1.2 错误处理示例

示例1：处理NOT FOUND

DELIMITER //

CREATE PROCEDURE test_handler1()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE emp_name VARCHAR(50);
    
    DECLARE emp_cursor CURSOR FOR SELECT name FROM employees;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN emp_cursor;
    
    read_loop: LOOP
        FETCH emp_cursor INTO emp_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SELECT emp_name;
    END LOOP;
    
    CLOSE emp_cursor;
END //

DELIMITER ;

示例2：处理SQLEXCEPTION

DELIMITER //

CREATE PROCEDURE test_handler2()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT '事务已回滚' AS message;
    END;
    
    START TRANSACTION;
    
    INSERT INTO employees (id, name) VALUES (1, '张三');
    INSERT INTO employees (id, name) VALUES (1, '李四');  -- 主键冲突
    
    COMMIT;
END //

DELIMITER ;

6.2 自定义错误

6.2.1 SIGNAL语句

语法格式：

SIGNAL SQLSTATE '状态码'
SET MESSAGE_TEXT = '错误信息';

示例：

DELIMITER //

CREATE PROCEDURE withdraw(IN acc_id INT, IN amount DECIMAL(10, 2))
BEGIN
    DECLARE balance DECIMAL(10, 2);
    
    SELECT balance INTO balance FROM accounts WHERE id = acc_id;
    
    IF balance < amount THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '余额不足';
    END IF;
    
    UPDATE accounts SET balance = balance - amount WHERE id = acc_id;
END //

DELIMITER ;

七、实战案例

7.1 批量插入数据

DELIMITER //

CREATE PROCEDURE batch_insert(IN num INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    
    WHILE i <= num DO
        INSERT INTO employees (name, salary, dept_id)
        VALUES (CONCAT('员工', i), RAND() * 10000 + 5000, (i % 4) + 1);
        SET i = i + 1;
    END WHILE;
    
    SELECT CONCAT('成功插入', num, '条数据') AS message;
END //

DELIMITER ;

-- 调用
CALL batch_insert(100);

7.2 分页查询

DELIMITER //

CREATE PROCEDURE page_query(
    IN table_name VARCHAR(50),
    IN page_num INT,
    IN page_size INT
)
BEGIN
    DECLARE offset_val INT;
    SET offset_val = (page_num - 1) * page_size;
    
    SET @sql = CONCAT('SELECT * FROM ', table_name, ' LIMIT ', offset_val, ', ', page_size);
    
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

-- 调用
CALL page_query('employees', 2, 10);

7.3 数据统计

DELIMITER //

CREATE PROCEDURE dept_statistics()
BEGIN
    SELECT 
        d.dept_name,
        COUNT(e.id) AS emp_count,
        IFNULL(AVG(e.salary), 0) AS avg_salary,
        IFNULL(MAX(e.salary), 0) AS max_salary,
        IFNULL(MIN(e.salary), 0) AS min_salary
    FROM departments d
    LEFT JOIN employees e ON d.id = e.dept_id
    GROUP BY d.id, d.dept_name;
END //

DELIMITER ;

-- 调用
CALL dept_statistics();

八、本章小结

8.1 核心要点

✅ 存储过程是一组预编译的SQL语句 ✅ 参数类型包括IN、OUT、INOUT ✅ 流程控制包括IF、CASE、LOOP、WHILE、REPEAT ✅ 游标用于遍历结果集 ✅ 存储函数返回单个值

8.2 验证清单

完成本章学习后，请确认您能够：

• 创建和调用存储过程
• 使用各种参数类型
• 使用流程控制语句
• 使用游标遍历结果集
• 创建和调用存储函数