存储过程（Stored Procedure）介绍

左手的ㄟ右手 2022-05-22 01:07 230阅读 0赞

本文主要介绍数据库中存储过程（Stored Procedure）的相关知识，同时通过用法示例介绍存储过程的使用方法。

# 1 概述 #

## 1.1 What ##

**存储过程**是大型数据库系统中，一组为了完成特定功能的 **SQL 语句集**，这些 SQL 语句集存储在数据库中，经过第一次编译后，后续调用不需要再次编译，用户通过存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

存储过程是数据库的一个重要对象。

## 1.2 Why ##

使用存储过程的原因（也就是使用存储过程的优点）如下：

*  当对数据库进行复杂操作时（如对多个表进行增删改查），可将这些复杂操作通过存储过程封装起来，与数据库提供的事务处理结合起来使用；
 *  存储过程只在创造时进行编译，以后每次执行存储过程都不需重新编译；而一般SQL语句每执行一次就需要编译一次，所以使用存储过程可提高数据库执行速度；
 *  存储过程有助于减少应用程序和数据库服务器之间的流量。使用存储过程时，应用程序不必再向数据库服务器发送多个冗长的 SQL 语句，而只需发送存储过程的名称（和参数）即可；
 *  存储过程对任何应用程序都是**可重用的**和**透明的**，且存储过程可以重复使用。存储过程将数据库接口暴露给所有应用程序，开发人员无需重复开发存储过程中已支持的功能，这可以减少数据库开发人员的工作量；
 *  存储过程是安全的。参数化的存储过程可以防止 SQL 注入式攻击，同时，数据库管理员可以为访问数据库中存储过程的应用程序授予适当的权限，而不针对基础数据库表放开任何权限。

## 1.3 How ##

当前，几大数据库厂商提供的编写存储过程的工具没有统一，虽然它们针对存储过程的编写风格有些相似，但由于没有统一的标准，因此各家的开发调试过程也不一样。

本文在下一节中会针对不同的数据库，分别介绍其存储过程的使用方法。

# 2 用法示例 #

## 2.1 MySQL ##

### 2.1.1 一个简单的存储过程示例 ###

DELIMITER //
    CREATE PROCEDURE GetOccupation(OUT s TEXT)
        BEGIN
            SELECT occupation into s FROM roles WHERE role_id = 1;
        END //
    DELIMITER ;

针对上述示例，进行以下说明：

*  命令“DELIMITER //”，与存储过程语法无关。DELIMITER 语句将标准分隔符分号“;”更改为双反斜杠“//”。更改分隔符的原因，在于想将存储过程的内容作为一个整体传递给数据库服务器，而不让 mysql 工具单独解释每个 SQL 语句（在上面的示例中，假如不更改分隔符，那么 SELECT 语句就会单独执行，如果存储过程中存在多个类似的语句，那么每条语句都会被单独执行，这显然不是我们想要的）。在 END 关键字之后，使用分隔符“//”来表示存储过程的内容到此结束。最后的 DELIMITER 命令将分隔符更改回分号“;”；
 *  使用“CREATE PROCEDURE”语句创建一个新的存储过程。在该语句后指定待创建的存储过程的名称。在上面的示例中，存储过程的名称为“GetOccupation”，其后携带着该存储过程的参数信息（即 TEXT 类型的出参“s”）；
 *  BEGIN 和 END 之间的部分称为存储过程的主体。将声明性 SQL 语句放在主体之中以处理业务逻辑。在上面的存储过程中，使用了一个简单的 SELECT 语句来查询 roles 表中的 occupation 字段的数据。

### 2.1.2 创建存储过程 ###

有多种方式可以将存储过程录入数据库（即创建存储过程），在此我们通过 MySQL 客户端命令行工具，录入前面的存储过程示例。过程如下：

![70][]

说明：也可以通过将存储过程内容写入一个文件，直接将该文件内容导入数据库中；或者通过 GUI 工具创建存储过程。

### 2.1.3 查询存储过程信息 ###

通过 MySQL 客户端命令行工具，查询存储过程信息，命令如下：

SHOW PROCEDURE STATUS;

在存储过程创建完成后，执行查询命令得到的结果如下：

![70 1][]

另外，可以通过如下命令查询存储过程的**详细内容**，如下：

SHOW CREATE PROCEDURE PROCEDURE_NAME

对于前面创建的存储过程，查询结果如下：

![70 2][]

说明：“\\G”的作用是将查询的结果旋转90度变成纵向显示。

### 2.1.4 调用存储过程 ###

通过 MySQL 客户端命令行工具，调用指定的存储过程，命令格式如下：

CALL PROCEDURE_NAME[(参数信息)];

其中，PROCEDURE\_NAME 为存储过程名称，同时，需要根据存储过程的参数设置情况，附带相应的参数信息。

此处调用前面创建的存储过程 GetOccupation，信息如下：

![70 3][]

上述结果显示：将参数“out\_info”作为存储过程 GetOccupation 的出参，在调用该存储过程后，获得了相应的查询结果。

### 2.1.5 删除存储过程 ###

通过 MySQL 客户端命令行工具，删除指定的存储过程，命令格式如下：

DROP PROCEDURE PROCEDURE_NAME;

在本例中，删除存储过程的信息如下：

![70 4][]

上述结果显示：使用 DROP 命令删除存储过程 GetOccupation 后，再次查询存储过程信息时，已经查询不到该存储过程信息了，说明删除存储过程操作执行成功了。

[70]: /images/20220522/05e8ec88cb914f29bc0e266666f4c67c.png
[70 1]: /images/20220522/9fb7298ec6de4334b156a7e8e86c32ca.png
[70 2]: /images/20220522/1605e17fb18247e099d84564c227b1a0.png
[70 3]: /images/20220522/0850bb82c9424c5fa495bc31519b0f58.png
[70 4]: /images/20220522/5ed6b2b87a374ca3a091061ab443c672.png