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

一、概念

共同：

区别：

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

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 命令将结束符号修改为特定字符。

语法格式如下：

代码语言：javascript复制

DELIMITER $$

格式说明：

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

举例说明：

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

代码语言：javascript复制

DELIMITER ??

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

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

代码语言：javascript复制

DELIMITER ;

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

代码语言：javascript复制

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：表示在创建存储过程时指定的参数列表。其列表形式如下：

代码语言：javascript复制

[ IN | OUT | INOUT ] param_name type

各项说明如下：

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

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

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

·param_name：当前存储过程中参数的名称；

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

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

代码语言：javascript复制

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 商品表

代码语言：javascript复制

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 中创建存储过程方式：

代码语言：javascript复制

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

界面如下：可以设计函数，创建函数，删除函数，运行函数等操作。

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

代码语言：javascript复制

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

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

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

代码语言：javascript复制

DELIMITER ;

五、创建存储函数

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

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

代码语言：javascript复制

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 的函数。

代码语言：javascript复制

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

如下图所示：

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

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

1）右键新建函数

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

3）指定参数名

最后保存即可。

结语：

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

存储云数据库SQLServer sql 数据库

0 人点赞

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

一、概念

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

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

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

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

1．具有良好的封装性

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

3．让SQL具备处理能力

4．减少网络交互

5．能够提高系统性能

6．降低数据出错的概率

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

8．保证数据的安全性

四、存储过程的创建

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

2、参数详细说明

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

五、创建存储函数

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

2．参数详细说明

3．创建函数的简单示例

结语：

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