2022-08-09

储存函数触发器

#函数function

mysql中内置了很多函数

mysql8.0不支持创建函数的语法的

解决方案:

在my.ini或者my.cnf文件下添加：

[mysqld]

log-bin-trust-function-creators=1

最后mysql服务器重启

创建语法

函数体中一定有return语句
只要遇到varchar类型,必须要指定参数的长度

语法部分

-- 重新定制mysql的结束符号,sql语句的结束符号默认的是分号
delimiter $$
         
create function 函数名([变量名 数据类型(长度)]) returns 返回类型
begin
	-- -- 函数体
	return 结果;
end $$
         
delimiter ;

练习

传入俩个整数,返回俩个整数的相加的结果

delimiter //
create function dy_add(a int,b int) returns int
begin
	return a + b;
end //
delimiter ;

-- 调用函数
select dy_add(50,10);

练习

写一个函数可以实现日期转成指定格式的字符串xxxx年xx月xx日

date_format(date,pattern)

drop function dy_format;

delimiter //
create function dy_format(dt date) returns varchar(20)
begin
	return date_format(dt,'%Y年%m月%d日');
end //
delimiter ;

select dy_format(now());

语句语法

while..do..end while

求出1~x之间的总和

drop function x_add;

delimiter //
create function x_add(x int) returns int
begin
  -- 循环变量因子,局部变量
	declare i int default 1;
	-- 定义一个变量,用来保存总的和
	declare sums int default 0;
	while i<=x do
		-- 对已经声明过的变量进行赋值操作
		set sums = sums + i;
		set i = i + 1;
	end while;
	return sums;
end //
delimiter ;

select x_add(100);

分支语句if … then…end if

  -- 求出1~x之间奇数的总和
drop function ji_add;
  delimiter //
create function ji_add(x int) returns int
begin
	declare i int default 1;
	declare sums int default 0;
	while i<=x do
		if i%2!=0 then
			set sums = sums+i;
		end if;
		set i = i + 1;
	end while;
	return sums;
end //
delimiter ;

1	select ji_add(100);

全局变量

求出1~x之间的数字之和,但是不包括5的倍数

drop function my_add;
delimiter //
create function my_add(x int) returns int
begin
	-- 定义一个局部变量
	declare i int default 1;
	-- 定义一个全局变量,用来存储总和
	-- 全局变量  @变量名
	-- 系统的全局变量 @@tx_isolation
	set @sums = 0;
	
	-- 对语句片段进行一个命名 - 类似于java中的continue语句
	-- 命名是任意的
	success:while i<=x do
	  -- mysql中判断是否相等,=
		if i%5=0 then
			set i = i + 1;
			-- continue - 跳过本轮循环,继续下一轮循环
			iterate success;
		end if;
		set @sums = @sums + i;
		set i = i + 1;
	end while;
	return @sums;
end //
delimiter ;

select my_add(100);

-- 全局变量,在函数体外调用
select @sums;

存储过程

定义:为了完成一些特定的工程,提前将sql语句预编译好,存储在在mysql-server端.并且只会编译一次.后面直接调用

存储过程的时候,是不需要再次对sql语句进行编译了,提高性能.

存储过程可以做到标准的组件编程[把sql封装好,形成一个组件]

sql执行的过程

mysql-client 客户端编写sql语句 mysql> select * from s_emp;

将这个客户端的sql发送到mysql-server端,需要对这条sql进行编译[检测语法]以及解析.

mysql-server将解析之后的结果返回给mysql-client;

在没有使用存储过程之前,只要将sql语句发送到mysql-server端,每次都是需要对sql进行编译的.比较浪费时间.

语法

-- 删除存储过程
drop procedure 存储过程名;

-- 创建存储过程
delimiter //
create procedure 存储过程名([in|out] 变量名 数据类型)
begin
	-- 过程体
end //
delimiter ;

体验

-- 把s_emp表中的员工的平均薪资预编译好在mysql-server端存储
delimiter //
create procedure pro_sal()
begin
	select avg(salary) from s_emp;
end //
delimiter ;

call pro_sal();

in - 接受参数

drop procedure in_pro;
delimiter //
create procedure in_pro(in a int)
begin
	-- 输出System.out.println(a)
	-- 变量a是否有输出
	select a;
	
	-- set @i = 900,但是in来修饰的
	set a = 900;
end //
delimiter ;

-- 直接接受一个字面量
call in_pro(10);
-- 接受一个全局变量
set @a=10;
call in_pro(@a);
-- 10
select @a;

out - 返回结果

drop procedure out_pro;
delimiter //
create procedure out_pro(out a int)
begin
	-- 输出System.out.println(a)
	-- out修饰的 - 不能够接受外面传进来的参数
	select a;
	
	-- 给a重新赋值 - set @i = 800
	set a = 800;
end //
delimiter ;

-- 注意点:如果参数使用out,那么此处是不允许直接使用字面量进行传参
call out_pro(100);
ERROR 1414 (42000): OUT or INOUT argument 1 for routine dy.out_pro is not a variable or NEW pseudo-variable in BEFORE trigger

-- 只能使用全局变量进行传参
set @i = 100;
call out_pro(@i);
-- 发现a为null
+------+
| a    |
+------+
| NULL |
+------+

select @i;
+------+
| @i   |
+------+
|  800 |
+------+

练习 - 封装单个结果集

方式一

根据员工的id来返回员工的名称,薪资

和表结合一起使用的话,数据类型和表统一

drop procedure find_pro;
delimiter //
create procedure find_pro(in eid int(7),out fname varchar(20),out sal float(11,2))
begin
	select first_name into fname from s_emp where id=eid;
	select salary into sal from s_emp where id=eid;
end //
delimiter ;

call find_pro(1,@fname,@sal);
select @fname;
select @sal;

方式二

统一赋值

drop procedure find_pro;
delimiter //
create procedure find_pro(in eid int(7),out fname varchar(20),out sal float(11,2))
begin
	select first_name,salary into fname,sal from s_emp where id=eid;
end //
delimiter ;

call find_pro(1,@fname,@sal);
select @fname;
select @sal;

练习 - 封装多个结果集

传统的做法 - 已经被弃用了 - 性能比较低 - 游标

定义第三张表来存储多个结果集

-- 构建临时表 - 结果集
create table emp_copy as select first_name,salary from s_emp where 1=2;

drop procedure find_pro;
delimiter //
create procedure find_pro(in eid int(7))
begin
	insert into emp_copy(first_name,salary) select first_name,salary from s_emp where id>eid;
end //
delimiter ;

call find_pro(20);
select * from emp_copy;

练习 - 带事务

转账功能 - 要么同时成功,要么同时失败

drop procedure transfer_pro;
delimiter //
create procedure transfer_pro(in sid int(7),in tid int(7),in money double(10,2),in st int(7))
begin
	-- 定义一个局部变量,用来展示是否转账成功
	declare msg varchar(20) default '';

-- 手动开启失败
start transaction;

-- 第一条sql
update t_acc set balance = balance - money where id = sid;

-- 故意搞事情
if st=1 then
	set msg = 'sorry,转账失败!';
	-- 事务回滚
	rollback;
else
 -- 第二条sql
 update t_acc set balance = balance + money where id = tid;
 set msg = 'good,转账成功!';
  -- 手动提交事务
		commit;
end if;
select msg;
end //
delimiter ;

-- 转账成功
call transfer_pro(1,2,1000,2);

-- 转账失败
call transfer_pro(1,2,1000,1);

语句使用

条件分支语句

if 条件表达式 then elseif … then … else…end if;

drop procedure if_pro;
delimiter //
create procedure if_pro(in a int)
begin
	declare msg varchar(20) default '';
	if a>=90 then
		set msg = '优秀';
	elseif a>=80 then
		set msg = '良好';
	elseif a>=60 then
		set msg = '中等';
	else
		set msg='不及格';
	end if;
	select msg;
end //
delimiter ;

1	call if_pro(85);

case .. when 固定的值 then .. else .. end case

drop procedure case_pro;
delimiter //
create procedure case_pro(in a int)
begin
	declare msg varchar(20) default '';
	case a
		when 1 then
			set msg = '1';
		when 2 then
			set msg = '2';
		else
			set msg = '3';
	end case;
	select msg;
end //
delimiter ;

1	call case_pro(2);

循环语句

while … do .. end while

drop procedure while_pro;
delimiter //
create procedure while_pro(in x int,out result int) 
begin
	-- 定义一个局部变量  int i = 1
	declare i int default 1;
	-- 保存最终的总和
	declare sums int default 0;
	-- 循环
	while i<=x do
		-- sums自增1
		set sums = sums + i;
		-- i应该要自增1
		set i = i + 1;
	end while;
	-- select sums;
	set result = sums;
end //

delimiter ;

1 2	call while_pro(100,@result); select @result;

loop .. end loop - 类似于java中的while(true)

-- 1~x
drop procedure loop_pro;
delimiter //
create procedure loop_pro(in x int)
begin
	declare i int default 1;
	declare sums int default 0;
	
	success:loop
		if i>x then
			-- 打破循环的语句
			-- iterate success;  类似于continue
			
			-- 类似于break
			leave success;
		end if;
		set sums = sums + i;
		set i = i + 1;
	end loop;
	select sums;
end //
delimiter ;

1	call loop_pro(100);

repeat … until … end repeat

drop procedure repeat_pro;
delimiter //
create procedure repeat_pro(in x int)
begin
  -- 但是先进入到这个循环体中先执行一遍,然后再进行判断
	repeat
		set x = x + 1;
		select x;
	-- 如果x>0,循环停止了
	until x>0
	end repeat;
end //
delimiter ;

1	call repeat_pro(1);

存储过程和函数的区别

定义函数的时候,在函数的签名上必须要指定returns返回类型,定义存储过程的时候不需要使用returns来指定返回类型

函数体中必须要有return语句来返回函数的执行结果,但是存储过程中可以没有return语句

调用函数使用select,调用存储过程使用call

存储过程更加侧重于sql的封装以及sql的预编译,提高效率和安全和sql的复用性

存储过程必须要使用in来接受参数,使用out来返回结果

触发器

在myql中,当我们执行一些操作的时候,比如DML操作(触发器触发的事件),一旦事件被触发,那么

就会执行一段程序.触发器本质上就是一个特殊的存储过程.

语法

-- 删除触发器
drop trigger 触发器名称;
delimiter $$
-- 创建触发器
create trigger 触发器名
触发时机(after,before) 触发事件(insert,delete,update) on 触发器事件所在的表名
for each row
-- 触发器需要执行的逻辑.
begin
end
$$
delimiter ;

练习

1	create table t_acc_copy as select * from t_acc where 1=2;

删除t_acc表中的任意一条数据之后,会在t_acc_copy表中插入一条.

drop trigger acc_tri;
delimiter //
create trigger acc_tri
after delete on t_acc
for each row
begin
	insert into t_acc_copy values(old.id,old.balance);
end //
delimiter ;

mysql> delete from t_acc where id=1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_acc_copy;
+------+---------+
| id   | balance |
+------+---------+
|    1 | 1000.00 |
+------+---------+
1 row in set (0.00 sec)

级联删除 - 删除t_customer中的客户信息之前需要级联删除该客户的订单信息

drop trigger acc_tri;
delimiter //
create trigger cus_tri
before delete on t_customer
for each row
begin
	delete from t_ord where customer_id=old.id;
end //
delimiter ;

delete from t_customer where id=2;

oracle中支持自检约束check

drop table cks;
create table cks(
	id int(7) primary key auto_increment,
  age int(7)
);

insert into cks values(1,30);
insert into cks values(2,30);

auto_increment 主键自增长的策略,默认值是从1开始,步长为1.  主键列不需要设置值
insert into cks(age) values(20);
insert into cks(age) values(30);
delete from cks where id=2;
insert into cks(age) values(40);
select * from cks;

-- auto_increment - 主键列数据类型不能是varchar
-- auto_increment到达最大值

mysql用触发器来实现自检约束

-- 插入age只能在(0,18]区间,否则报错.
drop trigger cks_tri;
delimiter //
create trigger cks_tri
before insert on cks
for each row
begin
	if new.age<0 or new.age>18 then
		signal sqlstate '42000' set message_text='age必须在0~18区间';
	end if;
end //
delimiter ;

insert into cks(age) values(200);

ape_li