本篇学习于B站尚硅谷 康师傅的Mysql2022 yyds👍
Mysql 存储过程🔃 与 函数📊:
存储过程 和 存储函数说实话本人工作中不经常使用，康师傅也说 阿里开发准则 不建议使用存储过程/函数... 但这并不是咱不会的理由🙃
-
很久以前学习过，也听说了，
存储过程是mysql5.0新增的大致就像编程语言的 方法/函数一样,将复杂的sql 逻辑封装在一起, 使用的时候调用一下即可.应用程序无需关注内部的逻辑，只需要传入对应的参数, 就可以返回想要的结果. 要知道很多数据库 本身也是有很多方便的
函数()： IF() Count() ... -
🆗 话不多说让我们开始学习吧！📚
存储过程🔃：
概述：
我们之前所学习的 MySQL 语句都是针对一个表或几个表的单条 SQL 语句，但是在数据库的实际操作中，经常会有需要多条 SQL 语句处理多个表才能完成的操作。
例如： 张三工资是否大于 所属部门的平均工资 先知道张三部门，计算平均工资，是否大于
存储过程是一组为了完成特定功能的 SQL 语句集合
- 减少网络传输量（客户端不需要把所有的 SQL 语句通过网络发给服务器）
- 减少了 SQL 语句暴露在 网上的风险，也提高了数据查询的安全性
- 简化操作，提高了sql语句的重用性，减少了开发程序员的压力
- 减少操作过程中的失误，提高效率
和视图、函数的对比：
-
它和视图有着同样的优点，清晰、安全，还可以减少网络传输量
不同的是
试图是虚拟表不会对底层数据表直接操作，而存储过程是程序化sql 可以对 直接对底层表操作 -
存储过程和函数 类似，使用时候直接调用即可...
相对于函数有一些细节存储过程 和 函数
调用方式不一样有无返回值参数类型...
创建存储过程
语法分析：
# 因为存储过程中,为了区分多条SQL每个SQL需要使用 ;分号作为结束符号
# 而 Mysql ;分号是几乎所有sql语言的结束语 BEGIN --- END 中的分号会导致声明存储过程的语法结束,报错;
# 所以：需要使用 DELTMITER 来改变MYSQL的结束符号 (这里的// 并不是固定,而是一个不会造成其它影响的一个特殊符号 可以随意更改Mysql的结束符号
DELIMITER //
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
DECLARE
#存储过程体
END //
# 因为 DELIMITER 更改了Mysql的结束符所以执行: END // 结束存储过程声明
DELIMITER ;
# 为了存储过程中的多SQL ; 分号结束符不会导致存储过程声明的中断.
# DELTMITER 改变了Mysql的结束符, 当存储过程声明结束,为了不影响正常使用,建议将Mysql默认结束符 ; 更改回去,避免造成其它影响
类似于Java 的函数定义语法： 修饰符 返回类型 方法名(参数类型 参数名,....){ 方法体; } 学过编程语言的这里应该都不是难事.
存储过程名：
- 故名思意就是这个 存储过程的名字，方便调用执行：
CALL 数据库.存储过程名如果USE 数据库已经选中可以忽略数据库.
(IN|OUT|INOUT 参数名 参数类型,...) 存储过程的参数列表：
IN OUT INOUT 表示的是不同参数的，作用：
-
IN表示当前参数为入参可以理解为，带入存储过程中，SQL 执行需要的参数, 如果不指定默认就是
IN类型 -
OUT表示当前参数为出参调用这个函数的，客户端/应用程序，可以在执行完毕之后读取这个参数，相当于 存储过程的返回值..
一般用来存储一些 sql执行的值. -
INOUT当前参数既可以表示 出参 也可以表示 入参，有两种参数共有的特点.
参数名 参数类型
- 见名之意了， 就相当于Java 函数的：
形参名形参类型：可以是Mysql任意类型
当然参数列表也可以什么都没有，就是无参无返回值...就像Java的无参方法...
[characteristics ...] 可选
表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下：
LANGUAGE SQL
[NOT] DETERMINISTIC
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
SQL SECURITY { DEFINER | INVOKER }
COMMENT 'string'
-
LANGUAGE SQL
说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL
-
[NOT] DETERMINISTIC 指明存储过程执行的结果是否确定
DETERMINISTIC表示结果是确定的 每次执行存储过程时，相同的输入会得到相同的输出NOT DETERMINISTIC表示结果是不确定的 相同的输入可能得到不同的输出, 不指定，默认为NOT DETERMINISTIC -
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } 子程序SQL限制
多选一NO SQL表示当前存储过程的子程序中不包含任何SQL语句READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句CONTAINS SQL表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句 系统默认指定. -
SQL SECURITY { DEFINER | INVOKER } 表示存储过程的使用权限
SQL SECURITY DEFINER表示当前存储过程，只有创建者|定义者 才可以使用它 默认值SQL SECURITY INVOKER表示当前存储过程，有调用存储过程的权限用户可以使用它. -
COMMENT 'String'存储过程的 注释信息;一般用于描述，注释当前存储过程的作用.
DELIMITER
因为MySQL默认的语句结束符号为分号 ;
为了避免与存储过程中SQL语句结束符相冲突，需要使用 DELIMITER改变Mysql的默认结束符.
使用DELIMITER命令时，注意避免使用反斜杠 `` 字符，因为反斜线是MySQL的转义字符. 如果你使用的可视化工具,例如 Navicat 会自动的设置 DELIMITER 为其它符号，可以省略这个步骤...
调用存储过程：CALL
ok 上面咱们了解了，存储过程的声明... 之后就可以去手动创建一个存储过程了...
在实际开发过程中，存储过程是：声明定义在数据库中的，开发者只需要知道这个存储过程是干啥的 需要传递什么参数... 之后就可以像调用方法一样调用它：
调用语法：
CALL 存储过程名(实例参数)
- 注意，如果执行的存储过程属于其它的数据库, 需要指定数据库的名称
CALL 数据库名.存储过程名 - 或者
use 数据库切换数据库
调用案例：
#假设有一个存储过程 selall() 无参存储过程
因为本身就是当前库直接调用
CALL selall();
当然也可以使用 库.存储过程名
CALL 库.selall();
#假设调用 IN模式的入参存储过程 selall(IN ID int) 参数ID根据ID查询数据
方式1: 直接传参
CALL selall(1); # 传入参数 1
方式2：set 声明用户变量,作为参数传递
SET @id = 1; # SET MySQL设值定义变量的语法...注意: 这里的参数类型 且 SET 声明属性时候必须给值或 NULL;
CALL selall(@id);
#假设调用 OUT模式的出参存储过程 selCount(OUT zs int); 获取表的总记录数
方式1: set 声明用户变量,作为出参进入存储过程将返回值带出来..
CALL selCount(@zs);
SELECT @zs; # SET定义的用户变量通过 "SELECT 变量名" 可以直接查看数据库中定义的变量/系统变量
准备工作：
首先要准备一些表.... 当然可以不用和我一样...自由发挥
入门实例：
上面咱们了解了存储过程的 声明 调用 马上进入实战吧! 💪
#创建一个存储过程 selall() 无参存储过程,查看员工表
# 为了确保存储过程中多个SQL可以区分结束,更改Mysql默认的sql结束符; Navicat等工具默认处理了可以省略...可能会报错.
DELIMITER //
CREATE PROCEDURE selall()
BEGIN
-- 查询所有的员工
SELECT * FROM employees;
END
DELIMITER ;
# 避免对其他SQL造成影响,存储过程之后要将 ; 分号重新定义结束符;
# 调用: 方式一
CAll selall();
# 调用: 方式二
CAll atguigudb.selall();
# Navicat 本身对存储过程做了处理,所以不需要通过 DELIMITER 做处理;
# Procedure 存储过程并不能像Java一样可以方法重载...同名的方法无参/有参不可以出现"同名存储过程函数"
# 根据ID查看员工信息
CREATE PROCEDURE selId(IN ID INt)
BEGIN
-- SELECT * FROM employees WHERE employee_id = 100;
SELECT * FROM employees WHERE employee_id = ID;
END
#调用: 方式一
CALL selId(100);
#调用: 方式二
CALL selId('100');
#调用: 方式三,通过创建变量方式进行入参赋值...
SET @eid = 100
CALL selId(@eid);
SELECT @eid;
# 可以通过 SELECT 变量名 查看变量值...
# 这时候一定会有朋友想如果:
# 入参IN 为变量时在存储过程中 SELECT ... INTO ... 赋值变量IN类型 是否在外面可以获取到重新赋值的参数. "本人亲测不可以哦~"
# 根据ID 查看员工信息并返回员工姓名...
CREATE PROCEDURE selIdName(IN eid INT,OUT ename VARCHAR(10))
BEGIN
-- 如果查询INTO赋值时，查询结果列数要与接收函数的个数相同...'一个结果列对于一个OUT 两个列就是两个OUT'
-- 当然两个OUT 是可以由,两个SQL返回一个列返回的,不和SQL个数影响和SQL结果列数有关系..
SELECT CONCAT(last_name,first_name) INTO ename FROM employees
WHERE employee_id = eid;
END
# 调用: 方式一
-- SET @ename = NULL; 可有可无
CALL selIdName(100,@ename);
-- 查看当前返回的用户名
SELECT @ename
# 注意: SET @xx=NULL; 必须给值或NULL而且用户自定义会话变量,变量名必须是 @xx @开头Mysql的规范;
# INOUT类型参数：根据用户ID获取员工名
#创建存储过程一个INOUT出入参
CREATE PROCEDURE selIDName(INOUT IdName VARCHAR(20))
BEGIN
#根据传入的参数id 返回参数重新赋值用户名
SELECT CONCAT(emp.first_name,emp.last_name) INTO IdName FROM employees emp
WHERE emp.employee_id = IdName;
END
#调用: 方式一
-- 创建变量并赋要查询的ID 全局变量必须 @ 开头;
SET @IdName = 100; -- 赋值 '100'|100 都一样Mysql数值和字符类型内容匹配会自动转换.
CALL selIDName(@IdName);
SELECT @IdName; -- 查看结果,返回员工的姓名;
表名作为参数进行传递：
无论是存储过程 还是 存储函数都不支持将表面作为参数直接传输。 如果 FORM ’变量名‘ 后面直接跟着参数运行时候会将 变量名作为表面去查询 需要特殊处理.
#常规操作
-- 根据参数表名动态查询结果集
CREATE PROCEDURE seltab(IN tabname VARCHAR(20))
BEGIN
SELECT * FROM tabname;
END
#调用方法发现报错: 找不到表名发现sql将变量名作为表名进行查询了
CALL seltab('employees');
#结论：存储过程/函数不能直接将表名进行参数传递.
#特殊操作解决：将要执行的SQL通过CONCAT拼接好之后通过数据库预编译形式进行执行SQL
CREATE PROCEDURE seltab2(IN tabname VARCHAR(20))
BEGIN
-- 通过拼接sql形式进行特殊操作
SET @seltabs = CONCAT('SELECT * FROM ',tabname); -- 因为SQL是拼接的注意之间的空格哦!
PREPARE seltabs from @seltabs; -- PREPARE 预编译SQL语法字符
EXECUTE seltabs; -- 执行SQL脚本
END
#调用方法: 成功！
CALL seltab2('employees');
CALL seltab2('departments');
- 通过
PREPAREEXECUTE预编译执行形式进行动态操作.
ok, 到这里存储过程的大致相比已经有了一定的了解, 这里只是一些简单的例子，实际开发中会有很多更加复杂的案例需要自己发掘.
存储函数✨：
前面我们已经学习了存储过程了，Mysql中不仅有存储过程还有存储函数，二者大致相同但又有不同存储函数语法相对比较严格
创建存储函数：
-- 存储函数 和 存储过程声明语法大致也相同,
CREATE FUNCTION 函数名([IN] 参数名 参数类型)
RETURNS 返回值类型
[characteristics...]
BEGIN
-- 函数体
-- 函数体中存在 RETURN 语句
END
-
参数列表：
存储函数 声明比较严格，参数只能是
IN入参默认也是IN 所以可以不用声明参数 IN 更加符合编程语言的规范. -
RETURNS
表示存储函数的 返回类型, 存储函数只能有一个返回值. 且
必须有一个返回值. -
[characteristics...]
和存储过程一样，声明对函数内部的声明约束.
-
BEGIN...END
和存储过程一样包含方法体，如果只有一条语句，也可以省略.
Select调用存储过程：
存储函数 其实就是和Mysql 本身内置函数基本类似，区别在于是 用户自己定义的.
# Mysql调用系统函数
SELECT COUNT(1);
SELECT CONCAT('Hello',' ','world'); -- 调用系统自带函数成立！
# Mysql调用用户自定义函数
SELECT 自定义函数名(参数列表);
SELECT 数据库.自定义函数名(参数列表); -- 没有 USR 数据库 的需要指定数据库.
入门案例：
# 创建无参存储函数,获取员工表的员工数量
CREATE FUNCTION empCount()
RETURNS INT
# Mysql8版本自定义函数设置,预防报错.
DETERMINISTIC CONTAINS SQL
BEGIN
# 存储函数不想存储过程 SQL返回结果集必须被变量接受。
-- SELECT * FROM employees; 不能直接执行查询sql返回结果集
-- DECLARE emp CURSOR FOR SELECT * FROM employees; 需要声明变量或游标进行接受
RETURN (SELECT COUNT(1) AS emps FROM employees); -- RETURN 返回的值是sql需要括号() 包括一下;
END
#调用存储过程
SELECT empCount();
SELECT atguigudb.empCount();
# 创建存储函数,根据员工id 查询员工姓名;
CREATE FUNCTION selempid(empid INT)
RETURNS VARCHAR(25)
DETERMINISTIC CONTAINS SQL
BEGIN
-- 声明局部变量 并INTO赋值 RETURN返回出去;
DECLARE empname VARCHAR(25);
SELECT CONCAT(first_name,last_name) INTO empname FROM employees WHERE employee_id = empid;
RETURN empname;
END
#调用存储函数
SELECT selempid(100);
SELECT selempid(101);
注意事项：
存储过程和存储函数区别😶🌫️：
存储过程：
- 通过
CALL 存储过程名调用 - 参数类型支持
IN|OUT|INOUT正因如此支持多个返回值，同时也可以无返回值 - 执行方法体中支持
调用存储函数功能强大支持：创建表 删除表 事务操作这些都是存储函数不具备的
存储函数：
- 通过
SELECT 存储函数()调用 - 参数类型仅支持
IN默认也是如此存储函数必须有返回值 且唯一 - 存储函数中不可以调用存储过程 不支持直接返回结果集查看，但存储函数定义完成之后可以在其他sql语句中调用，存储过程不支持.
创建存储函数中报错：
you might want to use the less safe log_bin_trust_function_creators variable 好像是Mysql版本函数默认设置问题：
-
方式一 局部定义函数设置，仅当前函数定义有效.
定义函数时候加上特定的
函数特性：[NOT] DETERMINISTIC和{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} -
方式二 全局定义，后面的所有自定义函数都是这样的
Mysql执行： SET GLOBAL log_bin_trust_function_creators = 1; 更改系统变量，
但是如果Mysql服务重启就会失效，建议直接配置在配置文件中 My.ini
存储过程和函数的查看、修改、删除
因为存储过程 和 存储函数的大部分语法都很相似就放在一起学习总结了：
查看：
MySQL存储了存储过程和函数的状态信息，
用户可以使用SHOW STATUS语句或SHOW CREATE语句来查 看，也可直接从系统的information_schema数据库中查询。当前可以通过工具直接查看。
#查看存储过程和函数的创建信息
SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名
#查看存储过程和函数的状态信息
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
-- 这个语句返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。
-- [LIKE 'pattern']：匹配存储过程或函数的名称，可以省略。当省略不写时，会列出MySQL数据库中存在的所有存储过程或函数的信息
#从information_schema.Routines表中查看存储过程和函数的信息
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='存储过程或函数的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];
-- 注意: 如果在MySQL数据库中存在存储过程和函数名称相同的情况，
-- 最好指定ROUTINE_TYPE查询条件来指明查询的是存储过程还是函数
修改
修改存储过程或函数，不影响存储过程或函数功能，只是修改相关特性。使用ALTER语句实现
ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [characteristic ...]
举例：
修改存储过程CountProc的定义。将读写权限改为MODIFIES SQL DATA，并指明调用者可以执行
ALTER PROCEDURE CountProc
MODIFIES SQL DATA
SQL SECURITY INVOKER ;
修改只能修改定义声明结构，不能更改 BEGIN---END影响功能。
删除
删除存储过程和函数，可以使用DROP语句，其语法结构如下
DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名
#IF EXISTS：如果程序或函数不存储，它可以防止发生错误，产生一个用SHOW WARNINGS查看的警告
存储过程高级✨：
变量：
在Mysql 存储过程|函数，中可以使用变量来存储查询 计算中间的结果数据.... 变量又分为 系统变量 用户自定义变量
系统变量
系统变量由数据库的系统定义，属于 服务器 层面
- 这些系统变量定义了当前Mysql 服务实例的属性，特征。
- 一般来说这些值要源于：
编译Mysql时候的参数Mysql配置文件my.ini也可以通过访问官网来进行查看
系统变量分为全局系统变量global 会话系统变量session
全局系统变量
全局系统变量一般被 global关键字修饰说明 有时候也简称为：全局变量 local变量
全局系统变量对应的是Mysql服务 对所有的会话都会产生影响，但不能跨重启，重启之后都会回归默认值.
会话系统变量
会话系统变量一般被 session 关键字修饰，如果没有被任何修饰则默认会话级别
每一个Mysql 客户机成功链接都是对应着一次会话，而会话变量就是针对于这个客户端会话进行影响...
- 会话系统变量仅针对于当前会话（连接）有效。会话期间，当前会话对某个会话系统变量值的修 改，不会影响其他会话同一个会话系统变量的值
- 多个客户端，链接同一个Mysql服务资源，A客户端更改本地的会话系统变量配置，不会影响到B客户端的变量.
- 这些系统变量的初始值，一般都是
全局系统变量赋的值
在MySQL中有些系统变量只能是全局的，例如 max_connections 用于限制服务器的最大连接数；
有些系统变量作用域既可以是全局又可以是会话，例如 character_set_client 用于设置客户端的字符集；
有些系统变量的作用域只能是当前会话，例如 pseudo_thread_id 用于标记当前会话的 MySQL 连接 ID
系统变量🤖：
查看系统变量
#查看所有全局变量
SHOW GLOBAL VARIABLES;
#查看所有会话变量
SHOW SESSION VARIABLES;
或
SHOW VARIABLES; -- 如果不指定则默认查看的是session 会话级别的信息;
条件查看满足条件的系统变量
#查看满足条件的全局变量
SHOW GLOBAL VARIABLES LIKE '%标识符%';
-- 案例
SHOW GLOBAL VARIABLES LIKE 'admin_%';
#查看满足条件的会话变量
SHOW SESSION VARIABLES LIKE '%标识符%';
查看指定的系统变量
Mysql 编码规范,查看系统变量需要 @@ "两个@符号"进行修饰,
#查看指定的全局系统变量 @@GLOBAL 用于标记全局系统变量
SELECT @@global.变量名;
#查看指定的会话系统变量 @@SESSION 用于标记会话系统变量
SELECT @@session.变量名;
或
SELECT @@变量名;
修改系统变量的值
有些时候，数据库管理员需要修改系统变量的默认值，以便修改当前会话或者MySQL服务实例的属性、特征
#方式1：修改MySQL 配置文件 ，继而修改MySQL系统变量的值 (该方法需要重启MySQL服务;
#方式2：在MySQL服务运行期间，使用“set”命令重新设置系统变量的值 (该方法重启之后会失效;
#为某个系统变量赋值
#方式1：
SET @@global.变量名=变量值;
#方式2：
SET GLOBAL 变量名=变量值;
#为某个会话变量赋值
#方式1：
SET @@session.变量名=变量值;
#方式2：
SET SESSION 变量名=变量值;
用户自定义变量
用户变量是用户自己定义的：
-
MySQL 编码规范，
MySQL 中的用户变量以 一个“@” 开头根据作用 范围不同，又分为会话用户变量和局部变量 -
会话用户变量： 作用域和会话变量一样，只对 当前连接 会话有效
局部变量： 只在 BEGIN 和 END 语句块中有效。局部变量只能在 存储过程和函数 中使用
会话用户变量的定义: 必须赋值 且 @ 符号开头
#变量的定义方式有很多种,这里只需要掌握简单的几种即可,其他的了解就行
#不指定是不是Mysql版本原因,我看有的人赋值不需要 @ 而Mysql8好像必须需要”@ 单个符号声明“Mysql的编码规格;
#方式一 =等号赋值
SELECT @WSM; -- 查看@WSM变量值,即使不存在变量也不会报错,直接返回NULL 存在就返回值;
SET @WSM = 123;
#方式二 :=赋值
SELECT @WSM;
SET @WSM := 1234; -- 如果变量值定义错误,更改也是只需要重新赋值即可,SET 定义的变量没有数据类型,可以随意赋值;
#方式三 通过SQL查询赋值
SELECT @WSM;
SET @WSM = (SELECT count(1) FROM employees);
或 SELECT @wsm := count(1)+1 FROM employees;
或 SET @WSM := (SELECT count(1) FROM employees);
-- 这种方式比较霸道不方便读取,需要注意返回的结果集不能是多个,多个需要使用游标进行存储;
#方式四 SELECT 列 INTO 变量名 [FORM ...]
SELECT count(1) INTO @wsm FROM employees;
-- 个人觉得常用而且方便读取，如果多个变量需要赋值：SELECT 列1,列2 INTO 变量1,变量2
-- INTO 前后的列表个数需要对应,进行赋值,不然会报错需要进行注意;
局部变量的定义：
局部变量定义使用 DECLARE 关键字来定义:
作用域：仅仅在定义它的 BEGIN ... END 中有效
#声明一个存储过程: 通过局部变量 给 用户会话变量赋值,并查看:
#验证: 用户会话变量作用域同一会话有效,局部变量只在BEGIN...END内有效果;
-- 删除存储过程
DROP PROCEDURE setval;
-- 创建存储过程
CREATE PROCEDURE setval()
BEGIN
-- 定义局部变量
-- DECLARE 变量名 类型 [default 值]; 如果没有DEFAULT子句，初始值为NULL
DECLARE val INT DEFAULT 123;
-- 如果需要修改 DECLARE 修饰的局部变量,也是直接使用 SET 就可以进修改;
-- SET val = 321;
-- SET val := 321;
SET @setval = val;
SELECT @setval;
END
-- 调用存储过程
CALL setval();
-- 查看用户会话变量
SELECT @setval;
SET 和 DECLARE 区别：
| 作用域 | 定义位置 | 语法： | |
|---|---|---|---|
| SET | 当前会话 | 会话的任何地方 | 加@符号，不用指定类型 |
| DEClARE | 定义它的BEGIN END中 | BEGIN END的第一句话, 值可以通过 SET 变量名 修改 |
一般不用加@,需要指定类型,有默认值 |
异常处理：
Mysql存储过程和存储函数都是支持 定义条件与处理程序
定义条件：
- 事先定义程序执行过程中可能遇到的问题
处理程序：
- 定义了在遇到问题时应当采取的处理方 式，并且保证存储过程或函数在遇到警告或错误时能继续执行
增强存储程序处理问题的能 力，避免程序异常停止运行
生活中会有很事情，可能会打断你本想好的计划，今天本想学习奈何游戏太好玩了我去玩游戏了.
程序也是如此，我们定义了一组存储过程，想着执行但是执行过程中报错了，我们不能简单的确定程序运行到哪里...
比如：
案例分析：
员工表字段：
创建一个存储过程，修改员工Abel 的邮箱，因为员工表的邮箱是非空的所以如果赋值NULL 会直接报错💥
#创建一个存储过程
CREATE PROCEDURE UpdateDataNoCondition()
BEGIN
SET @x = 1;
UPDATE employees SET email = NULL WHERE last_name = 'Abel';
SET @x = 2;
UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
SET @x = 3;
END
#调用存储过程
CALL UpdateDataNoCondition();
#这个时候会发现存储过程报错了。
#好在我们在每一句后面都给 @x 进行了赋值;
SELECT @X; -- 查看 @x 的值就可以知道大概存储过程在哪里执行报错...
-- 且当存储过程中执行的SQL语句报错时，MySQL数据库会抛出错误，并退出当前SQL逻辑，不再向下继续执行
工具调用和 控制台服务直接调用报错，但控制台报错信息更加多，可视化工具对错误信息进行封装
定义条件：
上面我们看到存储过程报错，我们并不能直接获取到报错的位置信息，而且Mysql返回的错误码 并不很方便我们来查看.
定义条件就是给MySQL中的错误码命名，这有助于存储的程序代码更清晰。
它将一个 错误名字 和 指定的 错误条件 关联起来这个名字可以随后被用在定义处理程序的 DECLARE HANDLER 语句中
DECLARE 错误名称 CONDITION FOR 错误码（或错误条件）
Mysql错误码：
MySQL_error_code 和 sqlstate_value 都可以表示MySQL的错误
- MySQL_error_code是数值类型错误代码
- sqlstate_value是长度为5的字符串类型错误代码
- mysql错误码有两种，例如上面cmd错误：
MySQL_error_code 就是1046sqlstate_value 就是 3D000
定义条件案例：
将上面的错误码重命名 NULL
#使用Mysql_error_code 进行定义命名
DECLARE NULL CONDITION FOR 1046 -- 在出现错误码1046就是这样的NUll
#使用sqlstate_valeu 进行定义命名
DECLARE NULL CONDITION FOR SQLSTATE 3D000
处理程序：
定义处理程序就相当于是，编程语言中的 try-catch 当存储过程中出现某些异常就执行什么样的操作.
DECLARE 处理方式 HANDLER FOR 错误类型 处理语句
处理方式： 处理方式有3个取值：CONTINUE、EXIT、UNDO
CONTINUE：表示遇到错误不处理，继续执行EXIT：表示遇到错误马上退出UNDO：表示遇到错误后撤回之前的操作。MySQL中暂时不支持这样的操作
错误类型：（即条件）可以有如下取值
SQLSTATE'字符串错误码' ：表示长度为5的sqlstate_value类型的错误代码MySQL_error_code：匹配数值类型错误代码；错误名称：表示DECLARE ... CONDITION定义的错误条件名称，其实上面的也是服务于这里的，方便查看，不然直接看错误码确实比较头疼不方便记忆SQLWARNING：匹配所有以01开头的SQLSTATE错误代码；NOT FOUND：匹配所有以02开头的SQLSTATE错误代码；SQLEXCEPTION：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码；
处理语句：
- 相当于
try-catch中的catch 当出现错误执行的方法... - 语句可以是 像
SET 变量 = 值这样的简单语句，也可以是使用BEGIN ... END编写的复合语句
定义条件+处理程序 完成异常处理：
在存储过程中，定义处理程序，捕获sqlstate_value值，
当遇到MySQL_error_code值为1048时，执行 CONTINUE操作，并且将@proc_value的值设置为-1
#创建存储过程
CREATE PROCEDURE UpdateDataNoCondition()
BEGIN
#如果不使用异常码进行捕获异常也可以使用 定义条件进行捕获异常
-- 方式一
-- 声明异常条件名
-- DECLARE duplicate_entry CONDITION FOR SQLSTATE '3D000'; -- 先当于就是异常码1048
-- 定义处理程序,错误类型捕获是 声明的 错误名称;
-- DECLARE CONTINUE HANDLER FOR duplicate_entry SET @proc_value = -1;
-- 方式二
#定义处理程序
DECLARE CONTINUE HANDLER FOR 1048 SET @proc_value = -1; -- 如果报错就给 @proc_value 赋值,对象有值说明报错;
SET @x = 1;
UPDATE employees SET email = NULL WHERE last_name = 'Abel';
SET @x = 2;
UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
SET @x = 3;
END
#调用存储过程
CALL UpdateDataWithCondition();
#查看 @x 和 @proc_value 值判断存储过程是否执行异常,且运行到第几行报错;
SELECT @x,@proc_value;
这里介绍了存储过程运行时候，如果出现错误如何进行捕获并处理...下面让我们了解存储过程的流程控制
流程控制：
解决复杂问题不可能通过一个 SQL 语句完成，我们需要执行多个 SQL 操作
流程控制语句的作用就是控 制存储过程中 SQL 语句的执行顺序，是我们完成复杂操作必不可少的一部分 针对于MySQL的流程控制语句主要有 3类
- 条件判断语句 ：IF 语句和 CASE 语句
- 循环语句 ：LOOP、WHILE 和 REPEAT 语句
- 跳转语句 ：ITERATE 和 LEAVE 语句
分支结构 IF
IF 语句的语法结构是：
#就相当于是Java的 IF-ELSE IF—ELSE
IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2] -- ELSEIF 相当于Java的ELSE IF 可选;
[ELSEIF 表达式3 THEN 操作3]
[ELSE 操作N] -- 可选表示没有任何匹配条件时候执行;
END IF -- 表示IF分支结构的结束符,注意别忘了 结尾;分号
分支结构 CASE
CASE 语句的语法结构是： 不仅仅可用于存储过程和存储函数
#情况一：类似于switch
CASE 表达式
WHEN 值1 THEN 结果1或语句1(如果是语句，需要加分号;)
WHEN 值2 THEN 结果2或语句2(如果是语句，需要加分号;)
...
-- 相当于switch 中的Default
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）
#情况二：类似于多重if
CASE
WHEN 条件1 THEN 结果1或语句1(如果是语句，需要加分号)
WHEN 条件2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）
分支结构 CASE 和 IF 案例：
个人觉得 IF CASE 都差不多，个人觉得CASE 更加结构化清晰方便查看，这里举例查看：
#传入参数 张三考试成绩 =100满分 >80优秀 >60及格 <60不及格
#IF实现:
DROP PROCEDURE testScoreIF;
CREATE PROCEDURE testScoreIF(IN score INT)
BEGIN
IF score=100 THEN SET @eval = '满分';
ELSEIF score>80 THEN SET @eval = '优秀';
ELSEIF score>60 THEN SET @eval = '及格';
ELSE SET @eval = '不及格';
END IF;
-- 成绩评价
SELECT @eval;
END
CALL testScoreIF(100); -- 满分
CALL testScoreIF(81); -- 优秀
CALL testScoreIF(80); -- 及格
CALL testScoreIF(50); -- 不及格
#CASE方式二实现:
#方式一实现太过麻烦,CASE 支持场景更加灵活呢~
CREATE PROCEDURE testScoreCASE1(IN score INT)
BEGIN
CASE
WHEN score=100 THEN SET @eval = '满分';
WHEN score>80 THEN SET @eval = '优秀';
WHEN score>60 THEN SET @eval = '及格';
ELSE SET @eval = '不及格';
-- 在存储过程|函数中需要指定结束的语句,SELECT中也支持使用CASE 就可以直接以END结尾;
END CASE;
-- 成绩评价
SELECT @eval;
END
CALL testScoreCASE1(100); -- 满分
CALL testScoreCASE1(81); -- 优秀
CALL testScoreCASE1(80); -- 及格
CALL testScoreCASE1(50); -- 不及格
🆗 分支结构大致如此多加练习呀~
🔁循环结构之LOOP
LOOP循环语句用来重复执行某些语句，LOOP内的语句一直重复执行直到使用LEAVE子句跳出循环过程； 无论如果都会执行一次 没有条件需要手动 LEAVE 不然会陷入死循环
-- [] 可选表示定义LOOP的别名,多重循环用于指定循环结束的位置;
-- 执行时,会进入循环直到碰到 LEAVE 跳出循环,因此无论如果都会执行一次！不是根据条件进行跳出循环,注意LEAVE 避免死循环;
[循环名:] LOOP
循环执行的语句l;
END LOOP [循环名]
测试：循环计算10以内整数和
CREATE PROCEDURE TenCount()
BEGIN
-- 初始化数值
DECLARE shu INT DEFAULT 0;
DECLARE tenCount INT DEFAULT 0;
-- 声明循环label循环标识;
label:LOOP
-- SET修改初始化的数值,MySQL不能向编程语言直接 shu = shu+1;
SET shu = shu+1;
SET tenCount = tenCount+shu;
-- 如果shu等于10跳出循环 LEAVE指定跳出的循环循;
IF shu=10 THEN
LEAVE label;
END IF;
END LOOP label;
-- 查询10以内整数合
SELECT tenCount;
END;
CALL TenCount(); -- 结构55 成功!
🔁循环结构之WHILE
WHILE语句创建一个带条件判断的循环过程：
WHILE在执行语句执行时，首先对指定的 表达式进行判断，表达式成立，就执行循环，否则退出循环.
#基本语法
[while_label:] WHILE 循环条件 DO
BEGIN
循环体;
END;
END WHILE [while_label;
-- [while_label] 可选,给循环起一个别名,一个存储过程中多个循环,可以通过 LEAVE结束指定循环;
-- 如果循环体只有一行可以省略 BEGIN...END
测试：循环计算10以内整数和
#创建存储过程
CREATE PROCEDURE WTen()
BEGIN
#存储过程首行声明变量
DECLARE shu INT DEFAULT 0;
DECLARE tenCount INT DEFAULT 0;
#开始WHILE 循环 shu小于10 每次循环都+1;
WHILE shu<10 DO
#WHILE循环体结束;
BEGIN
#重新赋值;
SET shu = shu+1;
SET tenCount = tenCount+shu;
END;
END WHILE;
#查询结果最终值
SELECT tenCount;
END;
#调用存储过程
CALL wTen();
🔁循环结构之REPEAT
REPEAT语句创建一个带条件判断的循环过程
与WHILE循环不同的是，REPEAT 循环首先会执行一次循环- 然后在 UNTIL 中进行表达式的判断，如果满足条件就退出
- 如果条件不满足，则会 就继续执行循环，直到满足退出条件为止
#基本语法
[repeat_label:] REPEAT
循环体的语句;
UNTIL 结束循环的条件表达式 -- UNTIL 表达式 后面需要添加; 结尾哦~
END REPEAT [repeat_label]
测试：循环计算10以内整数和
#创建存储过程
CREATE PROCEDURE RTen()
BEGIN
#定义局部变量
DECLARE shu INT DEFAULT 0;
DECLARE tenCount INT DEFAULT 0;
#REPEAT 循环,首先执行一次循环,在根据是否满足 UNTIL 条件是否退出循环;
REPEAT
SET shu = shu+1;
SET tenCount = tenCount+shu;
UNTIL shu=10
END REPEAT;
SELECT tenCount;
END;
#调用存储过程
call RTen;
跳转语句之LEAVE | ITERATE语句
LEAVE语句：
- 可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，表示跳出循环或者跳出程序体的操作. 可以把 LEAVE 理解为
break - LEAVE 可以用来退出
BEGIN,,,END循环结构的标记。
#基本语法：
LEAVE 标记名;
ITERATE 语句：
只能用在循环语句（LOOP、REPEAT和WHILE语句）内，表示重新开始循环，将执行顺序 转到语句段开头处，可以把 ITERATE 理解为continue- 意思为 “本次循环结束,进入下一次循环”
#基本语法：
ITERATE 标记名;
游标：
什么是游标：
游标个人觉得概念比较抽象：
官方的定义是：
- 游标是一种类似于指针一样的东西，我们sql 查询时候一次可以查询到很多数据集，我们没办法轻松的找到其中的某一条元素进行操作。
- 游标，提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录 进行定位，并对指向的记录中的数据进行操作的数据结构。
游标让 SQL 这种面向集合的语言有了面向过程开发的能力
个人理解：
- 就可以把游标当作一个可以临时存储数据集的一个东西，
类似于集合，可以进行循环遍历进行操作每一行或指定条件行记录
使用游标的步骤：
游标必须在声明处理程序之前被声明，并且变量和条件还必须在声明游标或处理程序之前被声明
一般需要经历四个步骤。不同的 DBMS 中，使用游标的语法可能略有不同。
声明游标：
在MySQL中，使用DECLARE关键字来声明游标，其语法的基本形式如下：
#这个语法适用于 MySQL，SQL Server，DB2 和 MariaDB
DECLARE cursor_name CURSOR FOR SELECT语句;
-- cursor_name 自定义,理解为创建的游标名;
-- 上面说可以把游标理解为是一个结果集,这里的 SELECT语句 就是结果集的查询SQL
#如果是用 Oracle 或者 PostgreSQL，需要写成
DECLARE cursor_name CURSOR IS SELECT语句;
打开游标：
当我们定义好游标之后，如果想要使用游标，必须先打开游标
打开游标的时候 SELECT 语句的查询结 果集就会送到游标工作区，为后面游标的 逐条读取 结果集中的记录做准备。
OPEN 游标名;
使用游标：
游标必须在声明处理程序之前被声明，并且变量和条件还必须在声明游标或处理程序之前被声明
游标是一个结果集，一个SQL结果集可以有很多列，游标可以对每一行进行操作，将没一行的列，赋值给变量上进行操作...
#语法如下:
FETCH 游标名 INTO 变量1,变量2,[变量x]...
-- 这句的作用是使用这个游标来读取当前行,并且将数据保存到 var_name 这个变量中，游标指针指到下一行
-- 如果游标读取的数据行有多个列名，则在 INTO 关键字后面赋值给多个变量名即可.
注意⚠️：
- 变量，必须在声明游标之前就定义好
游标的查询结果集中的字段数，必须跟 INTO 后面的变量数一致否则，在存储过程执行的时 候，MySQL 会提示错误
关闭游标：
有 OPEN 就会有 CLOSE，也就是打开和关闭游标
当我们使用完游标后需要关闭掉该游标。因为游标会占用系统资源
如果不及时关闭，游标会一直保持到存储过程结束，影响系统运行的效率。
CLOSE 游标名;
关闭游标之后，我们就不能再检索查询结果中的数据行，如果需要检索只能再次打开游标。
游标案例
公司年会总结，之后发现 90部门 和 100部门业绩突出，给两个部门分别没人涨工资500 1000
CREATE PROCEDURE raises()
BEGIN
-- 定义游标需要存储的变量 （需要在游标之前定义
DECLARE done INT DEFAULT 0; -- 为了方便跳出循环实现定义一个用于判断跳出方法的变量
DECLARE empid INT DEFAULT 0;
DECLARE dmpid INT DEFAULT 0;
-- 定义游标
-- 因为我们这里只需要 员工工号 和 部门就可以了多了也没必要还麻烦,每个列都要对应一个变量;
DECLARE emps CURSOR FOR SELECT employee_id,department_id FROM employees WHERE department_id IS NOT NULL;
-- Mysql游标存在问题会报错需要进行处理: No data - zero rows fetched, selected, or processed
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
-- 打开游标
OPEN emps;
-- 开始 使用游标 定义一个循环,每次取出游标的员工和部门 90 100 部门员工根据工号加公司;
out_loop: LOOP
BEGIN
-- 使用游标;
FETCH emps INTO empid,dmpid;
-- 如果done=1 Mysql的 0是false 1是true 这个大家应该都知道吧退出循环;
IF done THEN
LEAVE out_loop;
END IF;
-- 判断部门是不是 90 或 100
CASE dmpid
WHEN 90 THEN
UPDATE employees SET salary = salary+500 WHERE employee_id = empid;
WHEN 100 THEN
UPDATE employees SET salary = salary+1000 WHERE employee_id = empid;
ELSE
UPDATE employees SET salary = salary WHERE employee_id = empid;
END CASE;
END;
END LOOP;
-- 别忘记关闭游标,不然消耗系统资源;
CLOSE emps;
-- 最后重新查看一下部门的工资问题
SELECT * FROM employees
WHERE department_id IN (90,100);
END;
涨薪后：一不小心执行多了~~
总结：
游标是 MySQL 的一个重要的功能，为 逐条读取 结果集中的数据 提供了完美的解决方案
- 游标可以在存储程序中使用，效率高，程序也更加简洁
但同时也会带来一些性能问题，比如在使用游标的过程中，会对数据行进行 加锁
- 这样在业务并发量大 的时候，不仅会影响业务之间的效率，还会
消耗系统资源 - 造成内存不足，这是因为游标是在内存中进行的处理。
建议：养成用完之后就关闭的习惯，这样才能提高系统的整体效率
关于Mysql游标报错：
No data - zero rows fetched, selected, or processed
- 网上，关于这个错误说的很多的，好像是 游标循环的时候不知道自己已经执行完毕了，FETCH没有退出循环
- 光标必须在声明处理程序之前被声明，并且变量和条件必须在声明光标或处理程序之前被声明
- 本人更新数据时发现利用游标更新数据时出错，但是数据还是可以更新的。
#加入这么一句,声明游标的指针位置,如果指针执行完毕了就给 done 赋值1
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
#我们也可以使用 done 来判断游标已经执行完毕退出循环;
-
这句话就是当有游标执行结束就会，将 done 设置为0 ，如果需要同时使用多个游标
注意： 判断两游标的值，一般来说游标走完了就只能给值赋值NULL 如果值为NULL 就需要把
SET done = 0重新刷回去！反正就是，根据需求操作，如果有游标执行完毕，注意刷新done 以防止其他游标收到影响;
