likes
comments
collection
share

浅谈 Mysql 存储过程🔃 与 函数📊:

作者站长头像
站长
· 阅读数 11

本篇学习于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 变量名" 可以直接查看数据库中定义的变量/系统变量

准备工作：

首先要准备一些表.... 当然可以不用和我一样...自由发挥

浅谈 Mysql 存储过程🔃 与 函数📊:

入门实例：

上面咱们了解了存储过程的 声明 调用 马上进入实战吧! 💪

 #创建一个存储过程 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');
  • 通过 PREPARE EXECUTE 预编译执行形式进行动态操作.

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存储过程和存储函数都是支持 定义条件与处理程序

定义条件：

  • 事先定义程序执行过程中可能遇到的问题

处理程序：

  • 定义了在遇到问题时应当采取的处理方 式，并且保证存储过程或函数在遇到警告或错误时能继续执行 增强存储程序处理问题的能 力，避免程序异常停止运行

生活中会有很事情，可能会打断你本想好的计划，今天本想学习奈何游戏太好玩了我去玩游戏了.

程序也是如此，我们定义了一组存储过程，想着执行但是执行过程中报错了，我们不能简单的确定程序运行到哪里...

比如：

案例分析：

员工表字段：

浅谈 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返回的错误码 并不很方便我们来查看.

定义条件就是给MySQL中的错误码命名，这有助于存储的程序代码更清晰。

  • 它将一个 错误名字 和 指定的 错误条件 关联起来 这个名字可以随后被用在定义处理程序的 DECLARE HANDLER 语句中
 DECLARE 错误名称 CONDITION FOR 错误码（或错误条件）

Mysql错误码：

MySQL_error_code 和 sqlstate_value 都可以表示MySQL的错误

  • MySQL_error_code是数值类型错误代码
  • sqlstate_value是长度为5的字符串类型错误代码
  • mysql错误码有两种，例如上面cmd错误： MySQL_error_code 就是1046 sqlstate_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

浅谈 Mysql 存储过程🔃 与 函数📊:

 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 的一个重要的功能，为 逐条读取 结果集中的数据 提供了完美的解决方案

  • 游标可以在存储程序中使用，效率高，程序也更加简洁

但同时也会带来一些性能问题，比如在使用游标的过程中，会对数据行进行 加锁

  • 这样在业务并发量大 的时候，不仅会影响业务之间的效率，还会 消耗系统资源
  • 造成内存不足，这是因为游标是在内存中进行的处理。

建议：养成用完之后就关闭的习惯，这样才能提高系统的整体效率

关于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 以防止其他游标收到影响;

转载自:https://juejin.cn/post/7341642966790160419
评论
请登录