mysql 存储过程

mysql存储过程：事先经过编译并且存储在数据库中的一段SQL语句集合。调用存储过程可以减少数据库和应用程序间传输的网络性能消耗

目录

1、创建存储过程和调用

1.1、无参存储过程

1.2、有参存储过程

1.3、mysql变量

1.3.1、系统变量

1.3.2、用户自定义变量

1.3.3、局部变量

2、存储过程中条件判断 if 的使用

3、存储过程中case的使用

4、存储过程中循环的使用

4.1、while 循环

4.2、repeat 循环

4.3、loop 循环

5、存储过程-游标 CURSOR

---

1、创建存储过程和调用

创建存储过程语句

CREATE PROCEDURE 存储过程名称([参数])

BEGIN

内容

END;

调用存储过程使用CALL

调用存储过程语句

CALL 存储过程名称([参数])

1.1、无参存储过程

示例

创建无参存储过程，查看数据库版本

CREATE PROCEDURE p_version()BEGINSELECT VERSION();END;

注意：笔者这里使用的是 navicat 和 mysql5.7.25 版本，如果读者使用的是 mysql 命令行客户端，需要使用 DELIMITER $$  定义结束符，因为上边语句中的 "；" 会被mysql 命令行客户端识别为结束符，因此在有"；"的函数体中，需要读者额外定义结束符，如下面代码

DELIMITER $$CREATE PROCEDURE p_version()BEGINSELECT VERSION();END $$

后面的代码笔者都是直接在navicat上执行，因此不再重复说明  DELIMITER $$  定义结束符的事

调用

CALL p_version();

1.2、有参存储过程

声明存储过程参数需要3个关键字

IN

OUT

INOUT

IN：表示输入的参数

OUT：表示输出的参数

INOUT：即可以表示输入的参数，也可以表示输出的参数

示例sql

CREATE PROCEDURE p_name(IN userid int, OUT username varchar(20))BEGINSELECT name INTO username from user where id = userid;END;

数据库中有张user表，有1条数据，上面存储过程传入id，返回user的name字段

上面sql中的 name INTO username，是将user表的name赋值给存储过程的 username，这个INTO后面会详细说明，这里先看运行效果

调用存储过程

CALL p_name(1, @username);SELECT @username;

 @username是一个变量，用来接收存储过程输出的值，再通过SELECT @username;显示出来

运行效果

1.3、mysql变量

mysql变量有系统变量、用户自定义变量、局部变量

1.3.1、系统变量

系统变量：是mysql提供的，用户不能定义，分全局变量（GLOBAL）、会话变量（SESSION）

系统变量是2个@表示，即@@

查看所有系统变量（GLOBAL）

SHOW GLOBAL VARIABLES;

查看所有会话变量（SESSION）

SHOW SESSION VARIABLES;

查看某一系统变量（GLOBAL）

SELECT @@GLOBAL.变量名

查看某一会话变量（SESSION）

SELECT @@SESSION.变量名

设置系统变量（GLOBAL）

SET GLOBAL 变量名 = 值

设置会话变量（SESSION）

SET SESSION 变量名 = 值

注意：这里设置的系统变量（GLOBAL）或会话变量（SESSION），当mysql服务器重启后都会失效；如果想长期有效需要在mysql配置文件中设置

示例

SHOW GLOBAL VARIABLES;SHOW SESSION VARIABLES;SELECT @@GLOBAL.autocommit;SELECT @@SESSION.autocommit;SET SESSION autocommit = 0;

运行效果

1.3.2、用户自定义变量

用户自定义变量：是用户自己定义的变量，不用提前声明，在使用的时候直接 @变量名 即可，作用域是当前连接，用1个@表示

赋值，有4种方式

SET @变量名 = 值

SET @变量名 := 值

SELECT @变量名 := 值

SELECT 字段名 INTO @变量名 FROM 表名

使用用户自定义变量

SELECT @变量名

示例

SET @demo_name = '雾失楼台';SET @demo_age := 18;SELECT @demo_gender := '女';SELECT name INTO @demo_username FROM user;SELECT @demo_name;SELECT @demo_age,@demo_gender,@demo_username;

运行效果

1.3.3、局部变量

局部变量：在局部生效的变量，使用之前需要用 DECLARE 关键字先声明，可作为存储过程内的局部变量和输入参数，作用范围是局部变量声明的存储过程的BEGIN END间

声明

DECLARE 变量名 变量类型

变量类型就是数据库字段的类型，int、varchar、date等

赋值，有3种方式

SET 变量名 = 值

SET 变量名 := 值

SELECT 字段名 INTO 变量名 FROM 表名

使用局部变量

SELECT 变量名

示例

CREATE PROCEDURE p_localvar()BEGINDECLARE demo_name VARCHAR(20);SELECT name into demo_name from user;SELECT demo_name;END;CALL p_localvar();CREATE PROCEDURE p_localvar2()BEGINDECLARE demo_name VARCHAR(20);SET demo_name = '尊前谈笑人依旧';SELECT demo_name;END;CALL p_localvar2();CREATE PROCEDURE p_localvar3()BEGINDECLARE demo_name VARCHAR(20);SET demo_name := '雨打梨花深闭门，忘了青春，误了青春';SELECT demo_name;END;CALL p_localvar3();

运行效果

2、存储过程中条件判断 if 的使用

存储过程中可以写 if 条件判断

语法格式

IF 条件1 THEN

内容

ELSEIF 条件2 THEN

内容

ELSE 

内容

END IF;

示例

CREATE PROCEDURE p_judging_age(IN age int)BEGIN    DECLARE content VARCHAR(20);    IF age