mysql定义函数获取信息

转载

岁月如歌甚好 2024-07-11 19:13:46

文章标签 mysql定义函数获取信息 mysql 数据库 sql 处理程序 文章分类 MySQL 数据库

文章目录

💒 定义处理异常

🚏 定义条件与处理程序

🚀 案例分析

🚬 案例分析1：
🚬 案例分析2：

🚄 定义条件

🚬 举例1：
🚬 举例2：

🚒 定义处理程序

🚬 定义处理程序的几种方式：

🚤 案例解决

🚬 举例：

😹 作者: gh-xiaohe
😻 gh-xiaohe的博客 😽 觉得博主文章写的不错的话，希望大家三连（✌关注，✌点赞，✌评论），多多支持一下！！！

💒 定义处理异常

🚏 定义条件与处理程序

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

说明：定义条件和处理程序在存储过程、存储函数中都是支持的。

🚀 案例分析

🚬 案例分析1：

#错误代码： 1364
#Field 'email' doesn't have a default value # email 有约束 禁止为null
INSERT INTO employees(last_name)
VALUES('Tom');

🚬 案例分析2：

创建一个名称为“UpdateDataNoCondition”的存储过程。代码如下：

DELIMITER //

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 //

DELIMITER ;

#调用存储过程
#错误代码： 1048
#Column 'email' cannot be null
CALL UpdateDataNoCondition();

SELECT @x; # 结果是 1 出现错误之后程序就不向下执行了

mysql定义函数获取信息_sql

可以看到，此时@x变量的值为1。结合创建存储过程的SQL语句代码可以得出：在存储过程中未定义条件和处理程序，且当存储过程中执行的SQL语句报错时，MySQL数据库会抛出错误，并退出当前SQL逻辑，不再向下继续执行。

🚄 定义条件

定义条件就是给MySQL中的错误码命名，这有助于存储的程序代码更清晰。它将一个错误名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的DECLARE HANDLER语句中。

定义条件使用DECLARE语句，语法格式如下：

DECLARE 错误名称 CONDITION FOR 错误码（或错误条件）

错误码的说明：(两种个错误码)

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

MySQL_error_code是数值类型错误代码。
sqlstate_value是长度为5的字符串类型错误代码。

例如，在ERROR 1418 (HY000)中，1418是MySQL_error_code，'HY000’是sqlstate_value。
例如，在ERROR 1142（42000）中，1142是MySQL_error_code，'42000’是sqlstate_value。

🚬 举例1：

定义“Field_Not_Be_NULL”错误名与MySQL中违反非空约束的错误类型是“ERROR 1048 (23000)”对应。

#举例1：定义“Field_Not_Be_NULL”错误名与MySQL中违反非空约束的错误类型是“ERROR 1048 (23000)”对应。
#方式1：使用MySQL_error_code
DECLARE Field_Not_Be_NULL CONDITION FOR 1048;

#方式2：使用sqlstate_value
DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE '23000'; # SQLSTATE

🚬 举例2：

定义"ERROR 1148(42000)"错误，名称为command_not_allowed。

#举例2：定义"ERROR 1148(42000)"错误，名称为command_not_allowed。
#方式1：使用MySQL_error_code
DECLARE command_not_allowed CONDITION FOR 1148;

#方式2：使用sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000'; # SQLSTATE

🚒 定义处理程序

可以为SQL执行过程中发生的某种类型的错误定义特殊的处理程序。定义处理程序时，使用DECLARE语句的语法如下：

DECLARE 处理方式 HANDLER FOR 错误类型 处理语句

出现这个的 错误类型 处理的方式 处理的语句

处理方式：处理方式有3个取值：CONTINUE、EXIT、UNDO。

CONTINUE：表示遇到错误不处理，继续执行。
EXIT：表示遇到错误马上退出。
UNDO：表示遇到错误后撤回之前的操作。MySQL中暂时不支持这样的操作。 MySQL不支持

错误类型（即条件）可以有如下取值：

SQLSTATE '字符串错误码'：表示长度为5的sqlstate_value类型的错误代码；
MySQL_error_code：匹配数值类型错误代码；
错误名称：表示DECLARE … CONDITION定义的错误条件名称。

-----------------分为两波-----------------------
SQLWARNING：匹配所有以01开头的SQLSTATE错误代码；
NOT FOUND：匹配所有以02开头的SQLSTATE错误代码；
SQLEXCEPTION：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码；

处理语句：如果出现上述条件之一，则采用对应的处理方式，并执行指定的处理语句。语句可以是像“SET 变量 = 值”这样的简单语句，也可以是使用BEGIN ... END编写的复合语句。

🚬 定义处理程序的几种方式：

#格式：DECLARE 处理方式 HANDLER FOR 错误类型 处理语句

#举例：
#方法1：捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';

#方法2：捕获mysql_error_value
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';

#方法3：先定义条件，再调用
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR no_such_table SET @info = 'NO_SUCH_TABLE';

#方法4：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';

#方法5：使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';

#方法6：使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';

🚤 案例解决

在存储过程中，定义处理程序，捕获sqlstate_value值，当遇到MySQL_error_code值为1048时，执行CONTINUE操作，并且将@proc_value的值设置为-1。

DROP PROCEDURE UpdateDataNoCondition;

#重新定义存储过程，体现错误的处理程序
DELIMITER //

CREATE PROCEDURE UpdateDataNoCondition()
	BEGIN
		#声明处理程序
		#处理方式1：
		DECLARE CONTINUE HANDLER FOR 1048 SET @prc_value = -1;
		#处理方式2：
		#DECLARE CONTINUE HANDLER FOR sqlstate '23000' SET @prc_value = -1;
		
		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 //

DELIMITER ;

#调用存储过程：
CALL UpdateDataNoCondition();

#查看变量：
SELECT @x,@prc_value; # 处理异常的方式 继续往下执行

mysql定义函数获取信息_sql_02

🚬 举例：

创建一个名称为“InsertDataWithCondition”的存储过程，代码如下。

在存储过程中，定义处理程序，捕获sqlstate_value值，当遇到sqlstate_value值为23000时，执行EXIT操作，并且将@proc_value的值设置为-1。

#准备工作
CREATE TABLE departments
AS
SELECT * FROM atguigudb.`departments`;

ALTER TABLE departments
ADD CONSTRAINT uk_dept_name UNIQUE(department_id);

#创建一个名称为“InsertDataWithCondition”的存储过程

#① 准备工作
CREATE TABLE departments
AS
SELECT * FROM atguigudb.`departments`;

DESC departments;

ALTER TABLE departments
ADD CONSTRAINT uk_dept_id  UNIQUE(department_id); #uk_dept_id  应该改写成

#② 定义存储过程：
DELIMITER //

CREATE PROCEDURE InsertDataWithCondition()
	BEGIN		
		SET @x = 1;
		INSERT INTO departments(department_name) VALUES('测试');
		SET @x = 2;
		INSERT INTO departments(department_name) VALUES('测试'); # 违反唯一性约束出错
		SET @x = 3;
	END //

DELIMITER ;

#③ 调用
# 错误：ERROR 1062 (23000): Duplicate entry '0' for key 'departments.uk_dept_id'
CALL InsertDataWithCondition(); # 只能数值类型 建议使用命令行  还可以查看到数值类型 更清晰一些

SELECT @x;  #2

#④ 删除此存储过程
DROP PROCEDURE IF EXISTS InsertDataWithCondition;

#⑤ 重新定义存储过程（考虑到错误的处理程序）

DELIMITER //

CREATE PROCEDURE InsertDataWithCondition()
	BEGIN		
		
		#处理程序
		#方式1：
		#declare exit handler for 1062 set @pro_value = -1;
		#方式2：
		#declare exit handler for sqlstate '23000' set @pro_value = -1;
		#方式3：
		#定义条件
		DECLARE duplicate_entry CONDITION FOR 1062;
		DECLARE EXIT HANDLER FOR duplicate_entry SET @pro_value = -1;
		
		SET @x = 1;
		INSERT INTO departments(department_name) VALUES('测试');
		SET @x = 2;
		INSERT INTO departments(department_name) VALUES('测试');
		SET @x = 3;
	END //

DELIMITER ;

#调用
CALL InsertDataWithCondition();

SELECT @x,@pro_value; # 为什么是 1 应为之前 已经往表中插入过数据，id = 1  所以上来就会 重复id 报错

mysql定义函数获取信息_数据库_03