---
title: "16-存储过程与函数"
created: 2026-01-08
tags:
  - 项目筑基
---

# 存储过程与函数

## **第十一章：存储过程与函数**

> 💡 存储过程和函数是预编译的SQL代码块，存储在数据库服务器中，可以重复调用

### **11.1 存储过程概述**

#### **11.1.1 什么是存储过程**

**定义**：存储过程是一组预编译的SQL语句，存储在数据库服务器中，可以通过名称调用执行。

![[image-8d96700f.png]]

#### **11.1.2 存储过程的优点**

| **优点** | **说明** |
| --- | --- |
| **减少网络流量** | 只传输过程名和参数，不传输大量SQL |
| **提高执行效率** | 预编译，执行计划被缓存 |
| **代码复用** | 一次编写，多次调用 |
| **安全性** | 可以限制用户只能通过存储过程访问数据 |
| **便于维护** | 修改存储过程不影响应用程序 |

#### **11.1.3 存储过程分类**

| **类型** | **说明** |
| --- | --- |
| **系统存储过程** | MySQL内置的存储过程 |
| **用户自定义存储过程** | 用户创建的存储过程 |

### **11.2 创建存储过程**

#### **11.2.1 基本语法**

```sql
DELIMITER //
CREATE PROCEDURE 存储过程名([参数列表])
BEGIN
    -- SQL语句
END //
DELIMITER ;
```

> 💡 **DELIMITER 说明**
>
> - MySQL默认以`;`作为语句结束符
> - 存储过程内部包含多条SQL语句，也用`;`结束
> - 需要临时更改结束符为`//`，避免冲突
> - 定义完成后再改回`;`

#### **11.2.2 无参数的存储过程**

```sql
DELIMITER //
CREATE PROCEDURE 查询所有学生()
BEGIN
    SELECT * FROM 学生;
END //
DELIMITER ;

-- 调用
CALL 查询所有学生();
```

#### **11.2.3 带输入参数的存储过程**

```sql
DELIMITER //
CREATE PROCEDURE 按姓名查询学生(IN p_name VARCHAR(20))
BEGIN
    SELECT * FROM 学生 WHERE 姓名 = p_name;
END //
DELIMITER ;

-- 调用
CALL 按姓名查询学生('张三');

-- 使用变量调用
SET @name = '李四';
CALL 按姓名查询学生(@name);
```

#### **11.2.4 带输出参数的存储过程**

```sql
DELIMITER //
CREATE PROCEDURE 统计学生人数(OUT p_count INT)
BEGIN
    SELECT COUNT(*) INTO p_count FROM 学生;
END //
DELIMITER ;

-- 调用
CALL 统计学生人数(@total);
SELECT @total AS 学生总数;
```

#### **11.2.5 带输入和输出参数的存储过程**

```sql
DELIMITER //
CREATE PROCEDURE 统计某班级人数(
    IN p_class VARCHAR(30),
    OUT p_count INT
)
BEGIN
    SELECT COUNT(*) INTO p_count
    FROM 学生
    WHERE 班级 = p_class;
END //
DELIMITER ;

-- 调用
CALL 统计某班级人数('22软件1班', @count);
SELECT @count AS 班级人数;
```

#### **11.2.6 INOUT 参数**

```sql
DELIMITER //
CREATE PROCEDURE 增加金额(INOUT p_amount DECIMAL(10,2))
BEGIN
    SET p_amount = p_amount * 1.1;  -- 增加10%
END //
DELIMITER ;

-- 调用
SET @money = 1000;
CALL 增加金额(@money);
SELECT @money;  -- 1100
```

#### **11.2.7 参数类型对比**

| **参数类型** | **说明** | **是否需要传入** | **是否返回值** |
| --- | --- | --- | --- |
| `IN` | 输入参数 | ✓ | ✗ |
| `OUT` | 输出参数 | ✗（可选） | ✓ |
| `INOUT` | 输入输出参数 | ✓ | ✓ |

### **11.3 存储过程实例**

#### **11.3.1 查询学生选修的课程**

```sql
DELIMITER //
CREATE PROCEDURE 查询学生选课(IN p_name VARCHAR(20))
BEGIN
    SELECT 课程.课程名称
    FROM 课程
    INNER JOIN 选课成绩 ON 选课成绩.课程编号 = 课程.课程编号
    INNER JOIN 学生 ON 选课成绩.学号 = 学生.学号
    WHERE 学生.姓名 = p_name;
END //
DELIMITER ;

-- 调用
CALL 查询学生选课('张三');
```

#### **11.3.2 统计学生选课数量**

```sql
DELIMITER //
CREATE PROCEDURE 查询选课数(
    IN p_name VARCHAR(20),
    OUT p_count INT
)
BEGIN
    SELECT COUNT(*) INTO p_count
    FROM 选课成绩
    WHERE 学号 = (
        SELECT 学号 FROM 学生 WHERE 姓名 = p_name
    );
END //
DELIMITER ;

-- 调用
CALL 查询选课数('张三', @count);
SELECT @count AS 选课门数;
```

#### **11.3.3 更新数据的存储过程**

```sql
DELIMITER //
CREATE PROCEDURE 更新学生性别(
    IN p_name VARCHAR(20),
    IN p_gender CHAR(2)
)
BEGIN
    UPDATE 学生
    SET 性别 = p_gender
    WHERE 姓名 = p_name;

    -- 返回受影响的行数
    SELECT ROW_COUNT() AS 影响行数;
END //
DELIMITER ;

-- 调用
CALL 更新学生性别('张三', '女');
```

#### **11.3.4 带条件判断的存储过程**

```sql
DELIMITER //
CREATE PROCEDURE 成绩评级(
    IN p_score INT,
    OUT p_grade VARCHAR(10)
)
BEGIN
    IF p_score >= 90 THEN
        SET p_grade = '优秀';
    ELSEIF p_score >= 80 THEN
        SET p_grade = '良好';
    ELSEIF p_score >= 60 THEN
        SET p_grade = '及格';
    ELSE
        SET p_grade = '不及格';
    END IF;
END //
DELIMITER ;

-- 调用
CALL 成绩评级(85, @grade);
SELECT @grade;  -- '良好'
```

#### **11.3.5 带循环的存储过程**

```sql
DELIMITER //
CREATE PROCEDURE 批量插入测试数据(IN p_count INT)
BEGIN
    DECLARE i INT DEFAULT 1;

    -- 开启事务
    START TRANSACTION;

    WHILE i <= p_count DO
        INSERT INTO 测试表(名称) VALUES (CONCAT('测试', i));
        SET i = i + 1;
    END WHILE;

    -- 提交事务
    COMMIT;

    SELECT CONCAT('成功插入', p_count, '条数据') AS 结果;
END //
DELIMITER ;

-- 调用
CALL 批量插入测试数据(100);
```

### **11.4 查看存储过程**

```sql
-- 查看所有存储过程
SHOW PROCEDURE STATUS;

-- 查看当前数据库的存储过程
SHOW PROCEDURE STATUS WHERE Db = '数据库名';

-- 查看存储过程的创建语句
SHOW CREATE PROCEDURE 存储过程名;

-- 从information_schema查询
SELECT ROUTINE_NAME, ROUTINE_TYPE
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = '数据库名'
  AND ROUTINE_TYPE = 'PROCEDURE';
```

### **11.5 修改和删除存储过程**

#### **11.5.1 修改存储过程**

```sql
-- MySQL不支持直接修改存储过程的内容
-- 只能修改存储过程的特性

-- 修改存储过程特性
ALTER PROCEDURE 存储过程名
COMMENT '注释内容';

-- 要修改内容，需要先删除再重新创建
DROP PROCEDURE IF EXISTS 存储过程名;
CREATE PROCEDURE 存储过程名...
```

#### **11.5.2 删除存储过程**

```sql
-- 删除存储过程
DROP PROCEDURE 存储过程名;

-- 安全删除
DROP PROCEDURE IF EXISTS 存储过程名;

-- 删除多个存储过程（需要分开写）
DROP PROCEDURE IF EXISTS 存储过程1;
DROP PROCEDURE IF EXISTS 存储过程2;
```

### **11.6 存储函数**

#### **11.6.1 函数与存储过程的区别**

| **特性** | **存储过程** | **存储函数** |
| --- | --- | --- |
| 返回值 | 可以有多个OUT参数 | **必须有且只有一个返回值** |
| 调用方式 | `CALL` | 在SQL语句中像函数一样调用 |
| 是否能在SELECT中使用 | ✗ | ✓ |
| 是否能执行DML | ✓ | 不推荐（应该是只读的） |

#### **11.6.2 创建函数**

```sql
DELIMITER //
CREATE FUNCTION 函数名(参数列表)
RETURNS 返回类型
[DETERMINISTIC | NOT DETERMINISTIC]
BEGIN
    -- 函数体
    RETURN 返回值;
END //
DELIMITER ;
```

> 💡 **DETERMINISTIC说明**
>
> - `DETERMINISTIC`：相同输入总是产生相同输出
> - `NOT DETERMINISTIC`：相同输入可能产生不同输出（如使用RAND()）
> - MySQL 8.0默认要求指定其中之一

#### **11.6.3 函数示例**

```sql
-- 计算年龄的函数
DELIMITER //
CREATE FUNCTION get_age(birthday DATE)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, birthday, CURDATE());
END //
DELIMITER ;

-- 使用函数
SELECT 姓名, 出生日期, get_age(出生日期) AS 年龄 FROM 学生;
SELECT get_age('2000-06-15');
```

```sql
-- 成绩等级函数
DELIMITER //
CREATE FUNCTION grade_level(score INT)
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
    DECLARE level VARCHAR(10);

    IF score >= 90 THEN
        SET level = '优秀';
    ELSEIF score >= 80 THEN
        SET level = '良好';
    ELSEIF score >= 60 THEN
        SET level = '及格';
    ELSE
        SET level = '不及格';
    END IF;

    RETURN level;
END //
DELIMITER ;

-- 使用函数
SELECT 学号, 课程编号, 成绩, grade_level(成绩) AS 等级
FROM 选课成绩;
```

#### **11.6.4 查看和删除函数**

```sql
-- 查看函数
SHOW FUNCTION STATUS WHERE Db = '数据库名';
SHOW CREATE FUNCTION 函数名;

-- 删除函数
DROP FUNCTION IF EXISTS 函数名;
```

### **11.7 快速参考**

```sql
-- 创建存储过程
DELIMITER //
CREATE PROCEDURE 名称([IN|OUT|INOUT 参数名 类型, ...])
BEGIN
    -- SQL语句
END //
DELIMITER ;

-- 调用存储过程
CALL 存储过程名(参数);

-- 创建函数
DELIMITER //
CREATE FUNCTION 名称(参数列表) RETURNS 类型
DETERMINISTIC
BEGIN
    RETURN 值;
END //
DELIMITER ;

-- 查看
SHOW PROCEDURE STATUS;
SHOW CREATE PROCEDURE 名称;
SHOW FUNCTION STATUS;
SHOW CREATE FUNCTION 名称;

-- 删除
DROP PROCEDURE IF EXISTS 名称;
DROP FUNCTION IF EXISTS 名称;
```

---
⬅️ [[15-MySQL 编程基础|MySQL 编程基础]] 🏠 [[00-数据库|00-数据库]] ➡️ [[17-用户管理与权限控制|用户管理与权限控制]]