Mysql 存储过程和存储函数的初步认知

我不是女神ヾ 2023-09-26 11:31 2阅读 0赞

**目录**

一、概念

共同：

区别：

二、为什么要用存储过程和存储函数？

1、存储过程和函数不仅能够简化开发人员开发应用程序的工作量，

2、而且对于存储过程和函数中SQL语句的变动，无须修改上层应用程序的代码，这也大大简化了后期对于应用程序维护的复杂度。

三、存储过程和存储函数优点

1．具有良好的封装性

2．应用程序与SQL逻辑分离

3．让SQL具备处理能力

4．减少网络交互

5．能够提高系统性能

6．降低数据出错的概率

7．保证数据的一致性和完整性

8．保证数据的安全性

四、存储过程的创建

1、创建存储过程的语法说明

2、参数详细说明

3、 创建存储过程的简单示例

五、创建存储函数

1．创建存储函数的语法说明

2．参数详细说明

3．创建函数的简单示例

结语：

--------------------

## 一、概念 ##

在MySQL数据库中，存储程序可以分为**存储过程**和**存储函数**。

> ### 共同： ###

1.存储过程和存储函数都是一系列SQL语句的集合，这些SQL语句被封装到一起组成一个存储过程或者存储函数保存到数据库中。

2.应用程序调用存储过程只需要通过 CALL 关键字并指定存储过程的名称和参数即可；

同样，应用程序调用存储函数只需要通过 SELECT 关键字并指定存储函数的名称和参数即可。

> ### 区别： ###

**1.存储函数有且只有一个返回值，而存储过程可以有多个返回值，也可以没有返回值。**

2.**存储函数只能有输入参数**，而且不能带in, 而存储过程可以有多个in,out,inout参数。

3.存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑**，而函数有很多限制，如不能在函数中使用insert,update,delete,create等语句；**

4.存储函数只完成查询的工作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较强。

**5.存储过程可以调用存储函数、但函数不能调用存储过程。**

## 二、为什么要用存储过程和存储函数？ ##

### 1、存储过程和函数不仅能够**简化开发人员开发应用程序的工作量**， ###

### 2、而且对于存储过程和函数中SQL语句的变动，无须修改上层应用程序的代码，这也大大简**化了后期对于应用程序维护的复杂度**。 ###

**存储过程**是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。当以后需要数据库提供与已定义好的存储过程的功能相同的服务时，只需调用“CALL存储过程名字”即可自动完成。

存储函数和存储过程的目的一样，只是存储函数有返回值。

存储过程是数据库中的一个重要功能，存储过程可以用来转换数据、数据迁移、制作报表，它类似于编程语言，一次执行成功，就可以随时被调用，完成指定的功能操作。

## 三、存储过程和存储函数优点 ##

在实际项目开发过程中，使用存储过程和函数能够为项目开发和维护带来诸多好处，现就存储过程和函数的典型优点总结如下：

### 1．具有良好的封装性 ###

存储过程和函数将一系列的SQL语句进行封装，经过编译后保存到MySQL数据库中，可以供应用程序反复调用，而无须关注SQL逻辑的实现细节。

### 2．应用程序与SQL逻辑分离 ###

存储过程和函数中的SQL语句发生变动时，在一定程度上无须修改上层应用程序的业务逻辑，大大简化了应用程序开发和维护的复杂度。

### 3．让SQL具备处理能力 ###

存储过程和函数支持流程控制处理，能够增强SQL语句的灵活性，而且使用流程控制能够完成复杂的逻辑判断和相关的运算处理。

### 4．减少网络交互 ###

单独编写SQL语句在应用程序中处理业务逻辑时，需要通过SQL语句反复从数据库中查询数据并进行逻辑处理。每次查询数据时，都会在应用程序和数据库之间产生数据交互，增加了不必要的网络流量。使用存储过程和函数时，将SQL逻辑封装在一起并保存到数据库中，应用程序调用存储过程和函数，在应用程序和函数之间只需要产生一次数据交互即可，大大减少了不必要的网络带宽流量。

### 5．能够提高系统性能 ###

由于存储过程和函数是经过编译后保存到MySQL数据库中的，首次执行存储过程和函数后，存储过程和函数会被保存到相关的内存区域中。反复调用存储过程和函数时，只需要从对应的内存区域中执行存储过程和函数即可，大大提高了系统处理业务的效率和性能。

### 6．降低数据出错的概率 ###

在实际的系统开发过程中，业务逻辑处理的步骤越多，出错的概率往往越大。存储过程和函数统一封装SQL逻辑，对外提供统一的调用入口，能够大大降低数据出错的概率。

### 7．保证数据的一致性和完整性 ###

通过降低数据出错的概率，能够保证数据的一致性和完整性。

### 8．保证数据的安全性 ###

存储过程提高安全性的一个方案就是把它作为中间组件，在实际的系统开发过程中，需要对数据库划分严格的权限。部分人员不能直接访问数据表，但是可以为其赋予存储过程和函数的访问权限，使其通过存储过程和函数来操作数据表中的数据，从而提升数据库中数据的安全性。

## 四、存储过程的创建 ##

### 1、创建存储过程的语法说明 ###

CREATE PROCEDURE <过程名> ( \[过程参数\[,…\] \] ) <过程体>

存储过程主要包含：*过程名*，*过程参数*，*过程体*。

1）过程名

存储过程的名称，默认在当前数据库中创建。

若需要在特定数据库中创建存储过程，则要在名称前面加上数据库的名称，db\_name.sp\_name。

注意：*名称应当尽量避免选取与 MySQL 内置函数相同的名称，否则会发生错误*。

2）过程参数

存储过程的参数列表。其中，<参数名>为参数名，<类型>为参数的类型（可以是任何有效的 MySQL 数据类型）。当有多个参数时，参数列表中彼此间用**逗号**分隔。存储过程可以没有参数（此时存储过程的名称后仍需加上一对括号），也可以有 1 个或多个参数。

MySQL 存储过程支持三种类型的参数，即输入参数、输出参数和输入/输出参数，分别用 **IN**、**OUT** 和 **INOUT** 三个关键字标识。其中，输入参数可以传递给一个存储过程，输出参数用于存储过程需要返回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数。

注意：*参数的取名不要与数据表的列名相同，否则尽管不会返回出错信息，但是存储过程的 SQL 语句会将参数名看作列名，从而引发不可预知的结果*。

3）过程体

存储过程的主体部分，也称为存储过程体，包含在过程调用的时候必须执行的 SQL 语句。

这个部分以关键字 **BEGIN** 开始，以关键字 **END** 结束。

若存储过程体中只有一条 SQL 语句，则可以省略 BEGIN-END 标志。

> DELIMITER 命令

在存储过程的创建中，经常会用到一个十分重要的 MySQL 命令，即 DELIMITER 命令，特别是对于通过命令行的方式来操作 MySQL 数据库的使用者，更是要学会使用该命令。

在 Mysql 中，服务器默认将分号作为 SQL 语句的结束符号，但是这在存储过程中显然是不可行的。这个时候就需要我们用 DELIMITER 命令将结束符号修改为特定字符。

语法格式如下：

DELIMITER $$

格式说明：

*  $$ 是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“?”或两个“￥”等。
 *  当使用 DELIMITER 命令时，应该避免使用反斜杠“\\”字符，因为它是 MySQL 的转义字符。

举例说明：

例如在 MySQL 命令行客户端设置 两个 ?? 作为SQL 语句结束符 。

DELIMITER ??

成功执行这条 SQL 语句后，任何命令、语句或程序的结束标志就换为两个问号“??”了。

若希望换回默认的分号“;”作为结束标志，则在 MySQL 命令行客户端输入下列语句即可：

DELIMITER ;

接下来是创建存储过程更详细的说明和介绍：

--------------------

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
          [characteristic ...] routine_body

--------------------

语法格式说明：

·CREATE PROCEDURE：创建存储过程必须使用的关键字；

·sp\_name：创建存储过程时指定的存储过程名称；

·proc\_parameter：创建存储过程时指定的参数列表，参数列表可以省略；

·characteristic：创建存储过程时指定的对存储过程的约束；

·routine\_body：存储过程的SQL执行体，使用BEGIN…END来封装存储过程需要执行的SQL语句。

### 2、参数详细说明 ###

这里我们着重学习了解，参数列表 proc\_parameter 和 约束条件 characteristic 这两个参数。

（1）proc\_parameter：表示在创建存储过程时指定的参数列表。其列表形式如下：

[ IN | OUT | INOUT ] param_name type

各项说明如下：

·IN：当前参数为输入参数，也就是表示入参；

·OUT：当前参数为输出参数，也就是表示出参；

·INOUT：当前参数即可以为输入参数，也可以为输出参数，也就是即可以表示入参，也可以表示出参；

·param\_name：当前存储过程中参数的名称；

·type：当前存储过程中参数的类型，此类型可以是MySQL数据库中支持的任意数据类型。

--------------------

（2）characteristic：表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下：

LANGUAGE SQL
        | [NOT] DETERMINISTIC
        | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
        | SQL SECURITY { DEFINER | INVOKER }
        | COMMENT 'string'

各项说明如下：

·LANGUAGE SQL：存储过程的SQL执行体部分（存储过程语法格式中的routine\_body部分）是由SQL语句组成的。

*  ·\[NOT\] DETERMINISTIC：执行当前存储过程后，得出的结果数据是否确定。
 *  其中，DETERMINISTIC表示执行当前存储过程后得出的结果数据是确定的，即对于当前存储过程来说，每次输入相同的数据时，都会得到相同的输出结果。
 *  NOT DETERMINISTIC表示执行当前存储过程后，得出的结果数据是不确定的，即对于当前存储过程来说，每次输入相同的数据时，得出的输出结果可能不同。
 *  如果没有设置执行值，则MySQL 默认为 NOT DETERMINISTIC。

·\{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA\}：存储过程中的子程序使用SQL语句的约束限制。

*  其中，CONTAINS SQL 表示当前存储过程的子程序包含 SQL 语句，但是并不包含读写数据的 SQL 语句；
 *  NO SQL表示当前存储过程的子程序中不包含任何SQL语句；
 *  READS SQL DATA 表示当前存储过程的子程序中包含读数据的 SQL 语句；
 *  MODIFIES SQL DATA 表示当前存储过程的子程序中包含写数据的 SQL 语句。
 *  如果没有设置相关的值，则 MySQL默认指定值为 CONTAINS SQL。

·SQL SECURITY \{DEFINER | INVOKER\}：执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程。

*  DEFINER 表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；
 *  INVOKER 表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。
 *  如果没有设置相关的值，则MySQL默认指定值为 DEFINER。

·COMMENT 'string'：表示当前存储过程的注释信息，解释说明当前存储过程的含义。

注意：*在MySQL的存储过程中允许包含DDL的SQL语句，允许执行Commit（提交）操作，也允许执行Rollback（回滚）操作，但是不允许执行 LOAD DATA INFILE 语句。在当前存储过程中，可以调用其他存储过程或者函数*。

### 3、 创建存储过程的简单示例 ###

1）我先创建一张 t\_goods 商品表

CREATE TABLE `t_goods` (
      `id` int NOT NULL AUTO_INCREMENT COMMENT '主键ID',
      `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '商品名称',
      `price` decimal(18,2) DEFAULT NULL COMMENT '商品价格',
      `create_time` datetime DEFAULT NULL COMMENT '创建时间',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='商品表';

2）我们创建一个存储过程名称为 **SelectAllData** 的存储过程，这个存储过程比较简单，就是返回*t\_goods* 表中的所有数据。

**navicat 中创建存储过程方式：**

CREATE DEFINER=`root`@`%` PROCEDURE `SelectAllData`()
    BEGIN
     SELECT * FROM t_goods;
     END

界面如下：可以设计函数，创建函数，删除函数，运行函数等操作。![c56842492e284a00b12f38bb2e137e1a.png][]

--------------------

**命令行中创建存储过程方式：**

DELIMITER $$
    CREATE PROCEDURE SelectAllData() BEGIN SELECT * FROM t_goods; END $$

命令行运行完可看到如下内容

![c154bb9d12a640c6856657a779cbc625.png][]

切换 mysql 结束语句符号为分号

DELIMITER ;

## 五、创建存储函数 ##

### 1．创建存储函数的语法说明 ###

在MySQL数据库中创建存储函数时需要使用 **CREATE FUNCTION** 语句。创建存储函数的语法格式如下：

CREATE FUNCTION func_name ([func_parameter[,...]])
          RETURNS type
          [characteristic ...] routine_body

语法格式说明：

·CREATE FUNCTION：创建函数必须使用的关键字；

·func\_name：创建函数时指定的函数名称；

·func\_parameter：创建函数时指定的参数列表，参数列表可以省略；

·RETURNS type：创建函数时指定的返回数据类型；

·characteristic：创建函数时指定的对函数的约束；

·routine\_body：函数的SQL执行体。

### 2．参数详细说明 ###

（1）对于参数列表而言，存储过程的参数类型可以是IN、OUT和INOUT类型，而存储函数的参数类型只能是 IN 类型。

（2）创建函数时对characteristic参数的说明与创建存储过程时对characteristic参数的说明相同，笔者不再赘述。

### 3．创建函数的简单示例 ###

接下来我们创建一个名为 **SelectNameById** 的函数。这个函数比较简单，就是返回 *t\_goods* 数据表中 id 为2 的名称信息。

在 MySQL 命令行中创建名为 SelectNameById 的函数。

DELIMITER ??
    CREATE FUNCTION SelectNameById() RETURNS varchar(255) RETURN (SELECT name FROM t_goods WHERE id = 2); ??
    DELIMITER ;

如下图所示：

![f9d51ef9c548415d8e570fbb06e7f5a9.png][]

此时，名为 SelectNameById 的函数创建成功。

![de7aaa05cd474c52a8f0e512de11bc39.png][]

注：navicat 中创建存储过程和函数都是在同一个菜单下进行的。

1）右键新建函数

![7decce6c85904ba18aee2fd582e8c602.png][]

2）打开函数向导，这里可以看到有过程和函数两个类型

![0719ab2d65bd4be7b1fe9e52ba374697.png][]

3）指定参数名

![97e0dd0adda7494ebaa93a3bb4b693a7.png][]

最后保存即可。

## 结语： ##

本节主要给大家介绍 Mysql 存储过程和存储函数的初步认知。也了解如何创建存储过程和存储函数。下一节，为大家详细介绍存储过程的创建，删除，和修改等等详细的内容。后续还会介绍存储过程和存储函数在实际项目中的实战应用。

[c56842492e284a00b12f38bb2e137e1a.png]: https://img-blog.csdnimg.cn/c56842492e284a00b12f38bb2e137e1a.png
[c154bb9d12a640c6856657a779cbc625.png]: https://img-blog.csdnimg.cn/c154bb9d12a640c6856657a779cbc625.png
[f9d51ef9c548415d8e570fbb06e7f5a9.png]: https://img-blog.csdnimg.cn/f9d51ef9c548415d8e570fbb06e7f5a9.png
[de7aaa05cd474c52a8f0e512de11bc39.png]: https://img-blog.csdnimg.cn/de7aaa05cd474c52a8f0e512de11bc39.png
[7decce6c85904ba18aee2fd582e8c602.png]: https://img-blog.csdnimg.cn/7decce6c85904ba18aee2fd582e8c602.png
[0719ab2d65bd4be7b1fe9e52ba374697.png]: https://img-blog.csdnimg.cn/0719ab2d65bd4be7b1fe9e52ba374697.png
[97e0dd0adda7494ebaa93a3bb4b693a7.png]: https://img-blog.csdnimg.cn/97e0dd0adda7494ebaa93a3bb4b693a7.png