solitaryclown

MySQL存储过程/函数/触发器

2022-01-12
solitaryclown
database

1. MySQL存储过程

1.1. 定义

过程是常规脚本语言中的子程序(如子程序) ，存储在数据库中。对于 MySQL，过程用 MySQL 编写并存储在 MySQL 数据库/服务器中。MySQL 过程有名称、参数列表和 SQL 语句。

1.2. 使用

1.2.1. 创建

delimiter 自定义结束符号
create procedure 名称([in | out | inout]参数...)
begin
    sql语句
end 自定义结束符号
delimiter ;

1.2.2. 调用

call 过程名()

1.2.3. 变量

1.2.3.1. 局部变量

  • 声明：declare 变量名 类型 [default 默认值]
  • 赋值
    • SET 变量名=值
    • select... INTO 变量名
  • 作用域：BEGIN/END块中

1.2.3.2. 用户变量

  • 声明：不需要声明，直接赋值，但变量名必须以’@’开头，如@name
  • 作用域：此次会话。用户定义的变量是特定于会话的。由一个客户端定义的用户变量不能被其他客户端看到或使用。

1.2.3.3. 系统变量

1.2.3.3.1. 全局变量

全局变量是mysql服务器所需要的变量，在服务器启动时会初始化，可以通过my.ini文件修改这些变量的初始化值。

-- 查看所有全局变量
SHOW GLOBAL VARIABLES;

-- 查看某个全局变量
select @@global.变量名;
-- 修改全局变量
SET @@global.变量名=值;
1.2.3.3.2. 会话变量

会话变量是全局变量的一份拷贝，客户端和服务器建立连接时创建这些变量。对会话变量的修改只影响当前会话。

-- 查看所有会话变量
SHOW SESSION VARIABLES;

-- 查看某个会话变量
select @@session.变量名;
-- 修改会话变量
SET @@session.变量名=值;

1.2.4. 参数

1.2.4.1. IN

在创建存储过程时可以在存储过程括号里面声明参数： create procedure proc(IN 变量名 变量类型)...

示例：

-- 存储过程传参
delimiter $$
create procedure proc2(in empno int)
begin
	select *from emp where emp.empno=empno;
end $$
delimiter ;

call proc2(7369);

对于IN类型的参数，变量传入存储过程后，初始值是原来的值，整个存储过程结束后不会改变传入的变量的值，如下：

CREATE procedure p1(in var int)
begin
	set var=10;
end

set @num=1;
call p1(@num);
select @num;

结果：

mysql> select @num;
+------+
| @num |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

1.2.4.2. OUT

OUT用来指明存储过程的传出参数，存储过程并不会返回值，只能在内部为传出参数赋值。

-- out
delimiter $$
create procedure proc3(in empno int,out ename varchar(10))
begin
	select emp.ename into ename FROM emp where emp.empno=empno;
end $$
delimiter ;

call proc3(7369,@ename);
select @ename;

结果：

mysql> select @ename;
+--------+
| @ename |
+--------+
| SMITH  |
+--------+
1 row in set (0.00 sec)

对于OUT类型的参数，变量传入存储过程，会被赋值为null，存储过程对传入的变量值进行的修改，在存储过程结束后可见。如果存储过程没有对变量进行赋值操作，结束后变量的值为Null。

create procedure p2(OUT var int)
begin
	set var=10;
end

set @num=1;
call p2(@num);
select @num;

结果：

mysql> select @num;
+------+
| @num |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

1.2.4.3. INOUT

对于INOUT类型的参数，变量传入存储过程后，初始值就是原来的值，且存储过程对变量的修改在存储过程结束后可见，INOUT相当于IN和OUT类型的结合体。

1.2.5. 删除

删除存储过程：DROP PROCEDURE [IF EXISTS] proc_name;

1.2.6. 流程控制

1.2.6.1. IF-THEN

格式：

IF ... THEN...
    ELSE IF... THEN ...
    ... 
    ELSE ...
END IF

1.2.6.2. 6.2 CASE

CASE ... 
    WHEN ... THEN ...
    WHEN ... THEN ...
    ...
    ELSE ...
END CASE

1.2.6.3. 循环

1.2.6.3.1. while-do

示例：

-- 循环
drop PROCEDURE if EXISTS proc_while;
create procedure proc_while(IN count int)
begin
	declare i int;
	set i:=1;
	myloop:while i<=count 
		do INSERT into db1.user VALUES(i,concat('user-',i),20+i);
		set i:=i+1;
		if(i>5) THEN LEAVE myloop;-- 退出循环，相当于break
		end if;
	end while;
END
1.2.6.3.2. repeat-until
-- 循环repeat-until
drop PROCEDURE if EXISTS proc_repeat;
create procedure proc_repeat(IN count int)
begin
	declare i int;
	set i:=1;
	myrepeat:repeat 
		INSERT into db1.user VALUES(i,concat('user-',i),20+i);
		set i:=i+1;
		until i>count
	end repeat;
END
1.2.6.3.3. loop
loop是死循环。
-- 循环loop
drop PROCEDURE if EXISTS proc_loop;
create procedure proc_loop(IN count int)
begin
	declare i int;
	set i:=1;
	myloop:loop 
		INSERT into db1.user VALUES(i,concat('user-',i),20+i);
		set i:=i+1;
		if i>count THEN LEAVE myloop;
		END if;
	end loop;
END
		
TRUNCATE table db1.user;
call proc_loop(20);

1.2.7. 游标

游标(cursor)是用来遍历关系的东西，以元组（行）为单位，具有以下属性：

  • 只读，不可更新。
  • 不可滚动，只能在一个方向上遍历且不可跳过行。

1.2.7.1. 语法

  • 声明：DECLARE cursor_name CURSOR FOR select_statement
    注意：游标声明必须出现在handler声明之前以及变量和条件声明之后。
  • 打开：OPEN cursor_name
  • 取值：FETCH [[NEXT] FROM] cursor_name INTO var_name [, var_name] ...
  • 关闭：CLOSE cursor_name

1.2.7.2. 示例

-- 游标
DROP PROCEDURE if exists proc_cursor;
create PROCEDURE proc_cursor(in in_dname varchar(10))
BEGIN
	declare var_empno int;
	declare var_ename varchar(10);
	declare var_sal DECIMAL (7,2);
	-- 游标声明
	DECLARE cursor_emp_dept CURSOR FOR 
		(select E.empno,E.ename,E.sal FROM emp E JOIN dept D ON E.deptno=D.deptno WHERE  dname=in_dname);
	-- 打开游标
	OPEN cursor_emp_dept;
	-- 获取值
	cursor_loop:LOOP 
	FETCH cursor_emp_dept INTO var_empno,var_ename,var_sal;
	select var_empno,var_ename,var_sal;
	END LOOP;
	CLOSE cursor_emp_dept;

END

call proc_cursor('SALES');

2. MySQL存储函数

MySQL存储函数和存储过程作用是一样的，都是预编译的SQL语句集合，类似于常规计算机语言中的方法。 但它们也有一些不同点：

  • procedure参数可以是in、out、inout，function参数只能是in
  • procedure没有返回值，function有返回值，且在创建function时要在(var1 type1,...)后使用RETURNS 返回值类型声明返回值。
  • 由于function有返回值，所以它可以作为sql语句的一部分，但procedure不能。

除此之外，它们的创建和使用都是一样的。 示例：

-- 函数示例
DROP FUNCTION IF  EXISTS function_emp;
CREATE FUNCTION function_emp() 
RETURNS INT 
BEGIN
	DECLARE var int DEFAULT 0;
	select count(*) INTO var FROM emp;
	RETURN var;
END

select function_emp();

结果：

mysql> select function_emp();
+----------------+
| function_emp() |
+----------------+
|             14 |
+----------------+
1 row in set (0.00 sec)

3. MySQL触发器

3.1. 作用

数据库触发器是响应数据库中特定表或视图上的某些事件而自动执行的过程代码。触发器主要用于维护数据库中信息的完整性。

3.2. 使用

3.2.1. 创建和删除

CREATE
    [DEFINER = user]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

-- 删除触发器
DROP TRIGGER IF NOT EXISTS trigger_name;

-- 查看所有触发器
SHOW TRIGGERS;

3.2.2. 获取数据

在触发器中可以使用NEW和OLD引用触发器关联的表，其中：

  • NEW.col_name引用将要INSERT的新数据或者UPDATE之后的新数据
  • OLD.col_name引用UPDATE、DELETE之前的旧数据

示例：

-- user插入记录，user_log插入相关id
DROP TRIGGER IF EXISTS trigger_user_insert2;
CREATE TRIGGER trigger_user_insert2 AFTER INSERT 
ON user FOR EACH ROW
BEGIN
	INSERT INTO user_logs VALUES(NULL,now(),CONCAT('新用户注册，用户ID：',NEW.uid));
END

3.3. 注意事项

  1. 触发器不能对本表进行增删改，防止递归。
  2. 对于增删改频繁的表，尽量不要使用触发器，否则会造成增删改效率低下。

上一篇 SQL视图

下一篇 MySQL索引&事务&锁&日志

Comments

Content