mysql存储过程

什么是存储过程

Stored Procedure

是一组为了完成特定功能的SQL语句集合

经编译后保存在数据库中

通过指定存储过程的名字并给出参数的值

MySQL5.0版本开始支持存储过程，使数据库引擎更加灵活和强大

可带参数，也可返回结果

可包含数据操纵语句、变量、逻辑控制语句等

存储过程包含了：

单个SQL语句

SQL语句块

SQL语句与逻辑控制语句

存储过程的优缺点

优点

减少网络流量

提升执行速度

减少数据库连接次数

安全性高

复用性高

缺点

可移植性差

在实际应用开发中，要根据业务需求决定是否使用存储过程，对于应用中特别复杂的数据处理，可以选用存储过程来进行实现

例如：复杂的报表设计，涉及多条件多表的联合查询等

创建存储过程

语法：

CREATE procedure 过程名 ([过程参数[,...]])

[特性]

存储过程体

常用特性

特性	说明
LANGUAGE SQL	表示存储过程语言，默认SQL
{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}	表示存储过程要做的工作类别默认值为CONTAINS SQL
SQL SECURITY { DEFINER | INVOKER }	指定存储过程的执行权限默认值是DEFINERDEFINDER：使用创建者的权限INVOKER：用执行者的权限
COMMENT 'string'	存储过程的注释信息

声明语句分隔符

使用DELIMITER关键字将分隔符设置为"$$"或"//"

还原为默认分隔符

DELIMITER;

如果没有声明分隔符，编译器会将其视为普通SQL语句进行处理，编译过程会报错

正确用法：首先用DELIMITER关键字声明当前段的分隔符，最后要将分隔符还原为默认字符

过程体的标识

定义存储过程的过程体时，需要标识开始和结束

语法：

BEGIN

//...

END

定义存储过程的参数

[IN | OUT | INOUT] 参数名 数据类型

IN: 指输入参数

该参数的值必须在调用存储过程时指定，存储过程中可以使用该参数，但它不能被返回

OUT: 指输出参数

该参数可以在存储过程中发生改变，并可以返回

INOUT：指输入输出参数

该参数的值在调用存储过程时指定

在存储过程中可以被改变和返回

如果需要定义多个参数，需要使用","进行分隔

调用存储过程

调用执行存储过程

语法：

CALL 存储过程名 [参数1,参数2,...]

存储过程调用类似java中的方法调用

存储过程中的变量

语法：

DECLART 变量名[,变量名...] 数据类型[DEFAULT 值];

给变量进行赋值

SET 变量名 = 表达式值[,变量名=表达式...];

定义存储过程时，所有局部变量的声明一定要放在存储过程体的开始；否则，会提示语法错误

MySQL变量

系统变量

指MySQL全局变量，以@@开头，形式为@@变量名

自定义变量

局部变量

一般用于SQL的语句块中，如：存储过程中的BEGIN和END语句块 ​ 作用域仅限于定义该变量的语句块内 ​ 生命周期也仅限于该存储过程的调用期间 ​ 在存储过程执行到END时，局部变量就会被释放

会话变量

是服务器为每个客户端连接维护的变量，与MySQL客户端是绑定的 ​ 也称作用户变量 ​ 可以暂存值，并传递给同一连接中其他SQL语句进行使用 ​ 当MySQL客户端连接退出时，用户变量就会被释放 ​ 用户变量创建时，一般以“@”开头，形式为“@变量名”

用户变量的生命周期在MySQL连接关闭后结束

用户变量在存储过程间传递

用户变量不仅可以在存储过程内和MySQL客户端中设置，还可以在不同存储过程间传递值

Navicat中创建、调用存储过程

Navicat提供了良好的开发环境，比MySQL命令行操作更加便捷

创建和调用存储过程的基本步骤

1.创建存储过程 右键点击选定的数据库下的“函数”节点，在弹出的下拉菜单中选择“新建函数” 在右侧区域会自动创建存储过程模板，并在其中编写存储过程代码

2.运行存储过程 点击“保存”按钮，存储过程将自动保存在选定的数据库“函数”节点下 点击“运行”按钮调用存储过程 根据存储过程的定义，在弹出的对话框中输入设定的用户参数值 点击“确定”按钮，执行存储过程，并输出结果

Navicat中，编写存储过程时不需使用DELIMITER声明新的分隔符

设置用户执行存储过程的权限

通过DEFINER和SQL SECURITY特性控制存储过程的执行权限

语法：

CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE 存储过程名 [SQL SECURITY { DEFINER | INVOKER } | …] BEGIN … END

DEFINER

默认DEFINER = CURRENT_USER ​ 检查 'user_name'@'host_name' 的权限

INVOKER

执行存储过程时，会检查调用者的权限

如果省略SQL SECURITY特性，则使用DEFINER属性指定调用者，且调用者必须具有EXECUTE权限，必须在mysql.user表中 如果将SQL SECURITY特性指定为INVOKER，则DEFINER属性无效

查看存储过程状态

语法：SHOW PROCEDURE STATUS;

查看存储过程的创建代码

语法：SHOW CREATE PROCEDURE 存储过程名；

修改存储过程

使用ALTER PROCEDURE语句修改创建存储过程时定义的特性

语法：ALTER PROCEDURE 存储过程名 [特性……] ;

删除存储过程

使用DROP PROCEDURE语句删除已创建的存储过程

语法：DROP PROCEDURE 存储过程名；

创建存储过程前，可以使用IF EXISTS语句检查其是否已存在 如果不存在，再进行创建

存储过程的控制语句

与Java语言的流程控制语句类似，MySQL提供的控制语句

条件语句

IF-ELSEIF-ELSE条件语句 CASE条件语句

循环语句

WHILE循环 LOOP循环 REPEAT循环

迭代语句

IF-ELSEIF-ELSE条件语句

语法：

IF 条件 then 语句列表

ELSE 语句列表

END IF；

CASE条件语句

语法：

CASE WHEN 条件 THEN 语句列表 [WHEN 条件 THEN 语句列表] [ELSE 语句列表] END CASE;

WHILE循环语句

语法：

[label:] WHILE 条件 DO 语句列表 END WHILE [label]

label为标号，用于区分不同的循环，可省略 用在begin、repeat、while 或者loop 语句前

LOOP循环语句

不需判断初始条件，直接执行循环体

[label:]LOOP

语句列表

END LOOP[label]

遇到LEAVE语句，退出循环

LEAVE label

REPEAT循环语句

先执行循环操作再判断循环条件

语法：

[label:] REPEAT 语句列表 UNTIL 条件 END REPEAT [label]

与LOOP循环语句相比较

相同点：不需要初始条件直接进入循环体 不同点：REPEAT语句可以设置退出条件

迭代语句

从当前代码处返回到程序块开始位置，重新执行

语法：

ITERATE label;

ITERATE关键字可以嵌入到LOOP、WHILE和REPEAT程序块中