浅谈 MySQL 存储过程与函数

Java.慈祥

已于 2024-05-10 13:33:38 修改

阅读量1.1k

点赞数 1

分类专栏： # Mysql 文章标签： mysql sql 存储过程函数

于 2022-05-09 00:21:56 首次发布

WSM

本文链接：https://blog.csdn.net/qq_45542380/article/details/124657553

版权

Mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

本篇学习于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' 存储过程的注释信息;

一般用于描述，注释当前存储过程的作用.

BEGIN … END 注意事项⚠:

存储过程体中，有多条SQL 语句，如果：当前存储过程只有一条SQL 则可以省略 BEGIN END

BEGIN
	DECLARE 变量名 变量类型;
    SET 变量名 = '值';
    SELECT 列 INTO 变量名 ...
	# 中间可以包含很多条SQL语句, 每句SQL需要以 ; 分号来作为结束符
END

	DECLARE				#可选,[中翻译: 声明] 用来声明变量,位置定义于Begin-end 之间
	SET					#赋值语句,可以用于对变量进行赋值.
	SELECT ... INTO		#把从数据表中查询的结果存放到变量中,也就是为变量赋值;

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');

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

定义条件：

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

处理程序：

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

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

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

比如：

案例分析：

员工表字段：

在这里插入图片描述

创建一个存储过程，修改员工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 就是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

在这里插入图片描述

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

Java.慈祥

关注

1
点赞
踩
7

收藏

觉得还不错? 一键收藏
打赏
0
评论
浅谈 MySQL 存储过程与函数

本篇学习于B站尚硅谷康师傅的Mysql2022 yyds👍Mysql 存储过程🔃 与函数📊:存储过程和存储函数说实话本人工作中不经常使用，康师傅也说阿里开发准则不建议使用存储过程/函数… 但这并不是咱不会的理由🙃很久以前学习过，也听说了，存储过程是mysql5.0新增的大致就像编程语言的方法/函数一样, 将复杂的sql 逻辑封装在一起, 使用的时候调用一下即可.应用程序无需关注内部的逻辑，只需要传入对应的参数, 就可以返回想要的结果. 要知道很多数据库本身也是有很多方便的函数
复制链接

扫一扫