五、存储过程

5.1 认识存储过程

5.1.1 存储过程简介

  • 存储过程 是一组为了完成特定功能的 SQL 语句集合
  • 经编译后保存在数据库中
  • 通过指定存储过程的名字并给出参数的值
  • 可带参数，也可返回结果
  • 可包含数据操纵语句、变量、逻辑控制语句等

5.1.2 MySQL 存储过程的优缺点

存储过程的优点

  • 减少网络流量 存储过程一般存储在数据库服务器上，应用程序不需要发送多个 SQL 语句，而只需要发送存储过程的名称和参数
  • 提升执行速度 在 MySQL 中，对于存储过程只在创建时进行编译，然后将编译好的存储过程放在缓存中。若多次重复调用存储过程，则使用缓存中的编译版本
  • 减少数据库连接次数 执行多条 SQL 语句，应用程序需要多次连接数据库，使用存储过程，应用程序只需要连接一次数据库
  • 安全性高 数据库管理员可以对访问数据库存储过程的应用授予权限，而不提供基础数据表的访问权限
  • 高复用性 存储过程是封装的一个特定的功能块，对于任何应用程序都是可复用的透明的

存储过程的缺点

  • 内存增加 如果使用许多存储过程，则使用这些存储过程的每个连接的内存使用量将显着增加
  • 开发和维护存储过程并不容易
  • 调试存储过程很困难

5.2 使用存储过程

5.2.1 创建和调用存储过程

创建存储过程

  • 使用 CREATE PROCEDURE 语句创建存储过程
  • 创建存储过程 语法
CREATE PROCEDURE 过程名([过程参数[,...]])
	[特性] #可选项，用于设置存储过程的行为
	存储过程体
  • 存储过程的常用特性

特性

说明

LANGUAGE SQL

存储过程语音，默认为 SQL

CONTAINS SQL

NO SQL

READS SQL DATA

MODIFIES SQL DATA

存储过程要做哪类工作

默认为CONTAINS SQL

SQL SECURITY{DEFINER | INVOKER}

用来指定存储过程的执行权限

默认值为 DEFINER

DEFINER 使用创建者的权限执行

INVOKER 使用执行者的权限执行

COMMENT ‘string’

存储过程的注释信息

示例 编写存储过程，输出病人总人数

DROP PROCEDURE IF EXISTS proc_patient_countPatient;
DELIMITER //   #声明分隔符
CREATE PROCEDURE proc_patient_countPatient()
BEGIN  	#过程体开始
SELECT COUNT(*) FROM patient;
END //  #过程体结束
DELIMITER ;   #恢复默认分隔符

1、删除存储过程

  • 在创建存储过程之前，应先判断存储过程是否存在，如果存在，则删除
  • 删除存储过程 语法
DROP PROCEDURE 过程名

2、声明语句分隔符

  • MySQL 中，默认使用 ; 作为分隔符，使用 DELIMITER 关键字可以改变分隔符
  • 创建过程前，首先声明分隔符
  • 将分隔符设置为 $$ 或 // 语法如下
DELIMITER $$
DELIMITER //
  • 如果没有声明分隔符，编译器就会把存储过程当成 SQL 语句处理，会出现报错
  • 存储过程最后，要把分隔符还原 语法如下
DELIMITER ;

3、定义存储过程的参数

  • MySQL 中，存储过程的参数包括 3 种类型
  • IN 输入参数。该参数的值必须在调用存储过程时指定，在存储过程中可以使用该参数，但他不能被返回
  • OUT 输出参数。该参数可以再存储过程中发生改变并可以返回
  • INOUT 输入输出参数.该参数的值在调用存储过程时指定，在存储过程中可以被改变和返回。
  • 定义参数 语法
[IN|OUT|INOUT] 参数名 类型
  • 示例
DELIMITER // #声明分隔符
CREATE PROCEDURE proc_patient_countPatient2(IN patientNum INT)
#省略......
DELIMITER ; #恢复默认分隔符

4、过程体的标识

  • 在定义存储过程的过程体时，需要标识开始和结束。
  • 定义过程体 语法
BEGIN ...... END 分隔符
  • END 后面必须使用 DELIMITER 语句中设置的分隔符为结束

调用存储过程

  • MySQL 中使用 CALL 关键字调用存储过程
  • 调用存储过程 语法
CALL 存储过程名 (参数列表);

创建和调用存储过程 示例

  • 编写存储过程，输出病人总人数
#如果存储过程存在，则删除
DROP PROCEDURE IF EXISTS proc_patient_countPatient;
DELIMITER //  #声明分隔符
CREATE PROCEDURE proc_patient_countPatient()
BEGIN #过程体开始
 SELECT COUNT(*) FROM patient;
END // #过程体结束
DELIMITER ;   #恢复默认分隔符

#调用
CALL roc_patient_countPatient();

5.2.2 存储过程中的变量

  • 类似 Java 与其他编程语言，定义存储过程时可以使用变量
  • 声明变量 语法
DECLARE 变量名[,变量名...] 数据类型 [DEFAULT 值];
  • 示例 声明交易时间变量 trade_time，并设置默认值为 2020-07-10 代码如下
DECLARE trade_time DATE DEFAULT '2020-07-10';
  • 所有局部变量的声明一定要放在存储过程体的开始，否则会提示语法错误
  • 声明变量后，可以给变量赋值
  • 变量赋值 语法
SET 变量名=表达式值[,变量名=表达式...];
  • 示例 设置变量 total 的值为100
set total=100;

5.2.3 MySQL 用户自定义变量

  • MySQL 中变量包括 用户自定义变量 和 系统变量两种
  • MySQL 用户自定义变量又包括 局部变量 和 会话变量
  • 局部变量
  • 一般用于 SQL 的语句块中，如存储过程中的 BEGIN 和 END 语句块中
  • 作用于仅限于定义该变量的语句块内，语句块结束，变量释放
  • 会话变量
  • 也成为用户变量
  • 服务器为每个客户端连接维护的变量，与MySQL客户端是绑定的
  • 可以暂存值，并传递给同一连接中其他SQL语句进行使用
  • 当默认DEFINER = CURRENT_USERMySQL客户端连接退出时，用户变量就会被释放
  • 用户变量创建时，一般以“@”开头，形式为“@变量名”

5.2.4 设置用户权限

  • 存储过程中，可用关键字 DEFINER 赋值语句规定对存储过程访问的安全控制
  • 使用 DEFINER 和 SQL SECURITY 特性控制存储过程的执行 语法
DELIMITER //
CREATE [DEFINER={user|CURRENT_USER}] 
PROCEDURE 存储过程名
[SQL SECURITY {DEFINER|INVOKER|....}]
BEGIN
....
END //
DELIMITER ;
  • DEFINER 指定创建者用户
  • SQL SECURITY 用以指定 定义者 (DFINER) 或 调用者 (INVOKER)

5.2.5 查看存储过程

对创建后的存储过程进行查看

1、查看存储过程的状态

  • 查看以创建的存储过程 语法
SHOW PROCEDURE STATUS
  • 查看指定的数据库中创建的存储过程 语法
SHOW PROCEDURE STATUS WHERE DB='数据库名'
  • 也可以通过 LIKE 关键字匹配存储过程名称 语法如下
SHOW PROCEDURE STATUS LIKE '%patient%';

2、查看存储过程的创建代码

  • 查看存储过程的代码 语法
SHOW CREATE PROCEDURE 存储过程名

5.2.6 修改存储过程

  • 对已创建的存储过程进行修改
  • 只能修改存储过程定义的特性 语法如下
ALTER PROCEDURE 存储过程名 [特性......];
  • ALTER 关键字只能修改存储过程的属性
  • 如果要修改存储过程中过程体的内容，需要先删除该存储过程，然后进行重新创建

5.2.7 删除存储过程

  • 删除已有的存储过程 语法
DROP PROCEDURE 存储过程名

5.3 存储过程的控制语句

MySQL 提供的控制语句包括条件语句、循环语句、迭代语句

5.3.1 条件语句

MySQL 提供了两种控制语句，分别是 IF-ELSEIF-ELSE 条件语句和 CASE 条件语句

1、IF-ELSEIF-ELSE 条件语句

  • IF-ELSEIF-ELSE 语法
IF 条件 THEN 语句列表
	[ELSEIF 条件 THEN 语句列表]
	[ELSE 语句列表]
END IF;
  • 如果 IF 后的条件为真，则执行 THEN 后的语句列表

2、CASE 条件语句

  • MySQL 中的CASE 条件语句有两种写法
  • 第一种 语法
CASE
  WHEN 条件 THEN 语句列表
 [WHEN 条件 THEN 语句列表]
 [ELSE 语句列表]
END CASE;
  • 第二种 语法
CASE 列名
  WHEN 条件 THEN 语句列表
 [WHEN 条件 THEN 语句列表]
 [ELSE 语句列表]
END CASE;
  • 在 CASE 语句中，如果条件为真，则相应的 SQL 语句列表会被执行
  • 如果没有匹配条件，则会在 ELSE 子句里的语句列表被执行
  • CASE 语句的两种写法可以实现相同的功能
  • 如果做等值判断，使用第二种写法更加简洁
  • 因为 CASE 后面有列名，功能上会有一些限制

5.3.2 循环语句

MySQL 语句提供多种循环语句，包括 WHILE 循环语句、LOOP 循环语句 和 REPEAT 循环语句

1、WHILE 循环语句

  • WHILE 循环是最普遍使用的循环语句
  • WHILE 循环 语法
[lable:] WHILE 条件 DO
  语句列表
 END WHILE [lable]
  • 首先判断条件是否成立，若成立，则执行循环体
  • label 及标号，用于区分不同的循环体，可以被省略

2、LOOP 循环

  • LOOP 循环 语法
[lable:] LOOP
	语句列表
END LOOP [lable];
  • LOOP 循环结构不需要判断初始条件，直接执行循环体
  • 直到遇到 LEAVE 语句才退出循环
  • LEAVE 语法
LEAVE lable;

3、REPEAT 循环语句

  • REPEAT 循环 语法
[lable:] REPEAT
 语句列表
UNTLL 条件
END REPEAT [label]
  • REPEAT 循环不需要初始条件就可以直接进入循环体
  • REPEAT 循环有退出条件
  • 每次循环一次后，判断 UNTLL 后的条件是否成立，如果成立，则跳出循环

5.3.3 迭代语句

  • ITERATE 关键字可以嵌入 LOOP、WHILE、REPEAT 程序块中
  • 执行 ITERATE 语句时，跳出本次循环，继续下一次循环
  • ITERATE 语法
ITERATE lable;
  • label 参数表示循环的标志，ITERATE 语句必须跟在循环标志前面