MySQL存储过程

什么是存储过程

MySQL 5.0 版本开始支持存储过程。

存储过程：用户定义的一系列SQL语句的集合。

SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

存储过程定义的一个实例：

 优缺点

优点

存储过程可封装，并隐藏复杂的商业逻辑。存储过程可以回传值，并可以接受参数。存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。存储过程可以用在数据检验，强制实行商业逻辑等。存储过程是SQL语句的集合，使用存储过程可以减少应用程序和数据库的交互次数，提高数据处理效率。

缺点

存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。存储过程的性能调校与撰写，受限于各种数据库系统。

存储过程和MySQL函数的区别

存储过程涉及表或者其他对象，而函数通常不涉及特定的用户表。存储过程功能复杂强大，可以执行包括修改表等一系列数据库操作；而函数实现的功能针对性比较强；对于存储过程来说可以返回参数，如记录集，而函数只能返回值或者表对象。函数只能返回一个变量；而存储过程可以返回多个。存储过程的参数可以有IN,OUT,INOUT三种类型，而函数只能有IN类； IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）OUT 输出参数：表示过程向调用者传出值可以返回多个值（传出值只能是变量）INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）存储过程可以使用非确定函数，而不允许用户在函数定义非确定函数；存储过程一般作为一个独立的部分来执行，而函数可以作为查询语句的一部分来调用（SELECT调用）；

存储过程的使用

1、创建存储过程

创建存储过程，使用如下的语法：

DELIMITER $

CREATE   PROCEDURE   [存储过程名]  （存储过程所需的参数）

BEGIN

——SQL语句

END$

示例：

2、调用存储过程

调用存储过程的语法如下：

call procedure_name();

示例：

返回的结果为：

3、查看存储过程

——查看数据库 “db_name” 中所有的存储过程

SELECT  NAME  FROM  mysql.proc WHERE db = "db_name";

——查看存储过程的状态信息

SHOW   PROCEDURE   STATUS ;

——查询某个存储过程的定义

SHOW  CREATE  PROCEDURE  db_name.proc_name ;

4、删除存储过程

DROP  PROCEDURE   IF   EXISTS  procedure_name;

5、语法

存储过程是可以进行编程的，可以编程意味着可以使用变量、表达式、控制结构，来完成比较复杂的功能。

5.1 变量

DECLARE

在存储过程中，使用DECLARE关键字声明局部变量，该变量的作用范围是BEGIN ... END块中。

示例：

调用如下：

SET

直接赋值使用SET，可以赋值常量或者表达式。

示例：

调用如下：

5.2 if条件判断

语法格式如下：

IF  condition_list   THEN  statement_list;

      [ELSEIF  condition_list   THEN  statement_list]

      [ELSE]

END IF;

示例：

调用如下：

5.3 传递参数

IN ——输入参数（调用存储过程的时候输入，默认）

示例：

调用如下：

OUT——输出参数（调用存储过程的时候输出）

示例：

调用如下：

在MySQL中，变量名称之前添加@，表示该变量为用户会话变量，只在此次会话过程中有效；

变量名称之前如果有@@，表示该变量为系统变量，例如@@global.sort_buffer_size。

5.4 CASE结构

示例：

调用如下：

5.5 循环结构

while循环

调用如下：

REPEAT循环（满足条件则退出循环，否则一直循环）

注意：while循环结构中，满足条件才会执行循环；

而在repeat循环结构中，满足条件会退出循环。

repeat的结构如下：

REPEAT

        statement_list

        UNTIL   【退出条件】 END REPEAT；

示例：

 

调用如下：

LOOP循环结构，需要配合LEAVE语句一起使用

LOOP

statement_list(这里需要实现leave语句，否则会死循环)

END LOOP;

示例：

调用如下：

5.6 游标/光标

游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对查询结果集进行遍历。游标的使用包括：游标的声明、OPEN、FETCH和CLOSE，语法分别如下：

声明游标

DECLARE cursor_name  CURSOR  FOR   select_statement ;

OPEN游标

OPEN cursor_name ; 

FETCH游标

FETCH cursor_name  INTO  var_name ...

CLOSE游标

CLOSE  cursor_name ;

存储函数

存储函数可以认为是一个有返回值的存储过程。

示例：

调用如下：

参考资料

1、https://hillzhang1999.gitee.io/2020/05/29/shu-ju-ku-fu-xi-ji-yu-mysql/#toc-heading-118

2、https://www.bilibili.com/video/BV1UQ4y1P7Xr?p=27