MYSQL中的触发器、存储函数存储过程

jrians

于 2024-09-09 18:46:12 发布

阅读量888

点赞数 13

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_54842049/article/details/142038636

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

1. 触发器

特点：

某个事件发生后会自动触发定义的操作。触发器

类似于监听器。
外键的级联删除不会触发触发器。

1.1 创建

CREATE TRIGGER 触发器名
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名 FOR EACH ROW 
执行的语句块;

1.2 删除

DROP TRIGGER IF EXISTS 触发器名;

1.3 查看

SHOW TRIGERS;
SHOW CREATE TRIGGER 触发器名;

2. 存储函数和存储过程

2.1 变量

2.1.1 系统变量

定义：mysql服务器的变量。

分类

全局系统变量
会话系统变量¹

查看

SHOW GLOBAL VARIABLES [like 条件] ;#全局
SHOW SESSION VARIABLES [like 条件];#会话
SELECT @@变量名;#查看指定变量：先在会话系统变量中查，再在全局查

修改

my.ini文件中修改

SET @@GLOBAL.变量名 = value;#修改全局变量
SET @@SESSION.变量名 = value;#修改会话变量

全局变量持久化修改

set persist  变量名 = value;#下次启动仍是修改后的值。注意是全局变量。

2.1.2 自定义变量

定义：用户定义的变量。

分类：²

会话用户变量
局部变量

2.1.2.1 会话用户变量

定义

@变量名#初始值为null

赋值³

set 语句

SET @变量名 = value;
SET @变量名 := value;

select 语句

SELECT 2 INTO @ad;
SELECT @ad := 2;

使用

@变量名#如：select @a

2.1.2.2 局部变量

定义：定义在存储变量和存储函数中的变量称为局部变量。

DECLARE 变量名 INT [DEFAULT value];

如：

DELIMITER //
CREATE FUNCTION TEX()
RETURNS INT
DETERMINISTIC
BEGIN
	DECLARE a INT DEFAULT(1);
	RETURN (a);
END //
DELIMITER ;

使用

变量名

赋值

set 变量名 = value;

2.2 异常

特点：只能在函数和过程中使用。

2.2.1 定义条件

给MySQL错误码命名。

DECLARE 条件名 CONDITION FOR sql错误码;#法1
DECLARE 条件名 CONDITION FOR SQLSTATE sqlstate值;#法2

2.2.2 定义处理程序

特点：一定要在所有定义之后。且不能再open和close之间。

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

处理方式	说明
CONTINUE	继续
EXIT	退出

错误类型	说明
SQLSTATE ‘错误码’
MYSQL_ERROR_CODE
定义条件名
SQLWARNING	01开头SQLSTATE错误码
NOTFOUND	02开头SQLSTATE错误码
SQLEXCEPTION	除了01，02开头外的剩余SQLSTATE错误码

处理语句⁴

BEGIN END 语句⁵
SET语句
SQL语句

2.2.3 SIGNAL 语句

了解即可。

特点：signal语句主要是发生了错误后，产生提示信息，功能没有处理程序强大。


SIGNAL 错误类型 [SET 语句]#set语句用于给要提示的信息赋值

信息类型	信息说明
CLASS_ORIGIN:	一个字符串，表示发生错误的主要类别的原始标识。
SUBCLASS_ORIGIN	一个字符串，表示错误的次要类别的原始标识。
MESSAGE_TEXT:	用于描述错误的自定义消息文本，这通常用来给出向最终用户显示的错误信息。
MYSQL_ERRNO	MySQL特有的错误编号，与内部错误代码相对应的无符号小整数（SMALLINT UNSIGNED）。
CONSTRAINT_CATALOG	发生违反约束错误时，约束所属的目录的名称。
CONSTRAINT_SCHEMA	发生违反约束错误时，该约束所在的数据库的名称。
CONSTRAINT_NAME	当涉及到约束违规时，触发错误的具体约束的名称。
CATALOG_NAME	出错时相关联的目录名称。
SCHEMA_NAME	错误发生时相关的数据库架构（或称为“数据库”）的名称。
TABLE_NAME	当错误与特定表相关时，该表的名称。
COLUMN_NAME	若错误与某个特定的列相关，则为该列的名称。
CURSOR_NAME	如果错误和游标操作相关，表示那个游标的名称。

2.3 流程控制

只能在过程和函数中使用，条件只能是变量。

2.3.1 分支语句

2.3.1.1 if语句

IF 条件 THEN 语句 ;
ELSEIF 条件 THEN 语句;
ELSE 语句;
END IF;

3.1.2 case语句

同DQL中的单行函数。

2.3.2 循环结构

关键语句：

LEAVE 循环标签名;#跳出循环begin and也可以，表示跳出语句体。

iterate 循环标签名;#跳过本次循环

2.3.2.1 loop 语句

循环标签名:LOOP
	循环语句;#要包括跳出语句
END LOOP 循环标签名;

2.3.2.2 while 语句

[循环标签名:]while 循环条件 do
	循环语句;
end while [循环标签名];

2.3.2.3 REPEAT 语句

[循环标签名:] repeat 
	循环语句;
untill 循环条件
end repeat [循环标签名];

2.4 游标

定义：游标指向一个表中的某一行。相当于指针。

特点：

游标会占用系统资源。会加锁。
变量再其定义之前定义
只能在函数或过程中使用

2.4.1 声明

DELCARE 游标名 CURSOR FOR DQL语句;#指向第一行

2.4.2 打开游标

OPEN 游标名;

2.4.3 使用游标

FETCH 游标名 INTO 变量1，变量2，....;#取值后移动到下一行

2.4.4 关闭游标⁶

CLOSE 游标名;

2.4.5 结束判断

select中记录一下总数，之后num–即可。

SQL FOUND_ROWS() into num;#FOUND_ROWS()返回最后一次查询的元组数

2.5 存储函数

特点：

为了获取某个值
sql语句的聚合
非return语句中不允许使用select语句

创建

DELIMITER //
CREATE FUNCTION 函数名(参数 参数类型，..)
RETURNS 返回值类型
DETERMINISTIC#返回值确定，如随机数不确定
COMMENT ‘内容’#评论
BEGIN
	RETURN (SQL语句);
END //
DELIMITER ;

使用

函数名(参数)

查看信息

SHOW CREATE FUNCTION email_by_id;#查看创建信息
SHOW FUNCTION STATUS;#查看状态

删除

DROP FUNCTION 函数名;

2.6 存储过程

特点：

多条sql语句的聚合。其经过预编译后存储到计算机中。存储过程不建议使用。
为了执行特定的过程

3.1.9.3.1 无参无返回值

创建

delimiter // #设置分隔符，避免和存储过程中的sql语句重合
CREATE PROCEDURE a()
DETERMINISTIC#返回值确定，如随机数不确定
COMMENT ‘内容’#评论
BEGIN
	sql语句
END //
delimiter ; #恢复分隔符

引用

CALL a();

查看信息

SHOW CREATE PROCEDURE a;#查看创建信息
SHOW PROCEDURE STATUS;#查看状态

删除

DROP PROCEDURE 过程名;

3.1.9.3.2 有返回值

创建

DELIMITER //
CREATE PROCEDURE a2(OUT 变量名1 类型，OUT 变量名2 类型,..)
BEGIN
SELECT 值1,值2,... into 变量名2,...#查询结果赋给变量
END //
DELIMITER ;

调用

CALL a2(@var1);#调用存储过程
SELECT @var1;#查询变量值

3.1.9.3.2 有参数

创建

DELIMITER //
CREATE PROCEDURE a4(IN var INT)
BEGIN
SELECT 1 = var;
END //
DELIMITER ;

调用

CALL a4(2);

注：一个参数是返回参数，也是传入参数，那么用INOUT 修饰

会话是由客户端与服务器建立的通信产生的。会话系统变量仅对当前会话起作用，会话结束会失效。全局系统变量对全部会话起作用，关闭mysql服务会失效。二者有交集。 ↩︎
会话变量只在会话存在时有效。局部变量只在begin and中有效，只能使用在存储过程和存储函数中。 ↩︎
:=为赋值运算符，=为比较运算符。:=在set和update语句中和=是等价的。其他语句中:=对于非局部变量仍是赋值运算符。但是，对于局部变量不是。因为其他语句中，会将局部变量当作列来处理。 ↩︎
注意函数中非return位置不能使用select语句。 ↩︎
BEGIN ... END 语句用于定义一组 SQL 语句的复合语句块 ↩︎
open和close之间不能定义局部变量和处理过程了。 ↩︎