mysql整理10-存储程序（存储过程，存储函数，触发器）

目录

存储程序的优缺点
存储程序的分类
     i.存储过程procedure
          if语法
          循环语法:while 、 loop 、repeat
     ii.存储函数(函数)function
          函数和存储过程区别
     iii.触发器trigger
          触发器注意事项:

存储程序是运行于服务器端程序。

存储程序的优缺点

优点

i.简化开发
ii.执行效率较高

缺点

i.程序保存在服务器端,占用服务器资源
ii.数据迁移
iii.调试编写程序不方便

存储程序的分类

存储程序分为三类：存储过程，函数和触发器

i.存储过程procedure

因为mysql一遇到分号，它就要自动执行。
这种情况下，就可以使用delimiter，把delimiter后面换成其它符号，如//或$$

#创建

delimiter //;
create procedure sel_emp()
begin
	 select dname,ename from emp,dept where dept.deptno=emp.deptno;
end;

#调用

call sel_emp();

参数的模式:
In:传入模式 默认
Out:传出模式
Inout:传入传出模式

#根据员工姓名查询员工职位

delimiter //;
create procedure findJob(inout name_job varchar(20))
begin
  select job into name_job from emp where ename = name_job;
end;

#设置参数值
set @name_job='smith';
#调用
call findJob(@name_job);

select @name_job;

if语法:

delimiter //;
create procedure score_level(score int)
begin
  #变量声明
  declare v_level varchar(20);
  if score >= 80 then
		#变量赋值
		set v_level='A';
  elseif score>=60 then
	    set v_level='B';
  else
   	set v_level='C';
  end if;	
	select v_level;	
end;

循环(1+2+…+100)

MySQL三种循环 while 、 loop 、repeat

循环方式一：while循环

delimiter //;
create procedure calc()
begin
  declare i int;
  declare sum int;
  set i=1;
  set sum=0;

# while循环	
while i<=100 do
    set sum = sum + i;
	set i = i + 1;
end while;

  select sum;
end;

# 调用
call calc();

循环方式二：loop循环：

delimiter //;
create procedure calc1()
begin
    declare i int;
	declare sum int;
	set i=1;
	set sum=0;
	
	# loop循环
	lip:loop
	  set sum = sum + i;
	  set i = i +1;
	  		# 嵌套if
			if i>100 then
			  leave lip;
			end if;
	end loop;
	
  select sum;
end;

# 调用
call calc1();

循环方式三：repeat循环：

delimiter //;
create procedure calc2()
begin
  declare i int;
	declare sum int;
	set i=1;
	set sum=0;
	
	repeat
	  set sum = sum + i;
		set  i = i + 1;
	  until i > 100
	end repeat;
	
  select sum;
end;

call calc2();

ii.存储函数(函数)function

存储在服务器端,有返回值,函数作为sql一部分使用。

#根据用户编号查询姓名

delimiter //;

create function findNameByNo(eno int) returns varchar(20) 
DETERMINISTIC 
begin
  declare v_name varchar(20);
  select ename into v_name from emp where empno = eno;
  return v_name;
end;

# 调用方式
select findNameByNo(7788);

函数和存储过程区别:

 关键字不同
 存储过程三种参数模式实现数据输入输出;函数有返回值返回数据；
 存储过程可以作为独立个体执行,函数只能作为sql的一部分执行。

iii.触发器trigger

存储程序，存储在服务器端。
由事件(增删改)调用,不能传参。

CREATE
    [DEFINER = { user | CURRENT_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

demo:

delimiter //;
create trigger tri_user
after delete
on userinfo for each row
begin
	# old  new
   insert into user_bak values(old.uid,old.uname,old.password);
end;

触发器注意事项:

	不要添加过多触发器,降低效率
    存储程序中不能使用事务控制。