SQL存储函数、过程、视图、游标、触发器

一.存储过程

create PROCEDURE p1 (in height int)
begin
  declare line varchar(20) default '';
  if height>175 then
    set line='high';
  elseif height=175 then
    set line='mid';
  else
    set line='low';
  end if;
  select line;
end;
--调用：call p1(178);

create PROCEDURE p2 (in height int,out line varchar(20))
begin
  if height>175 then
    set line='high';
  elseif height=175 then
    set line='mid';
  else
    set line='low';
  end if;
end;
--调用：
--call p2(175,@description);
--select @description;
--@变量名表示用户会话变量

--计算从1加到n的值
create procedure p3(in n int)
begin
  declare result int default 0;
  declare h int default 1;
  while h<=n do
    set result = result+h;
    set h = h+1;
  end while;
  select result;
end

二.游标
游标相当于一个容器，存储查询结果集，在存储过程和存储函数中可以使用游标对结果集进行循环处理。游标基本语法如下：

1.DECLARE游标：

DECLARE cursor_name CURSOR FOR select_statement;

2.OPEN游标

OPEN cursor_name;

3.FETCH游标

FETCH cursor_name INTO var_name [,var_name]...

4.CLOSE游标

CLOSE cursor_name;

create procedure p4()
begin
  --声明输出变量
  declare c_id varchar(8);
  declare c_title varchar(50);
  declare c_dept_name varchar(20);
  declare c_credits decimal(2,0);
  --声明游标，其中容器内的内容即为for后面的查询语句所得到的数据集
  declare cursor_a cursor for
	  select * from course where credits=2;
  --打开游标
  open cursor_a;
  --从游标内获取一条数据并将其赋值于变量
  fetch cursor_a into c_id,c_title,c_dept_name,c_credits;
  --输出变量值，若超出集合数目则会报错
  select concat('id',c_id,'title',c_title,'dept_name',c_dept_name,'credits',c_credits);
  --关闭游标
  close cursor_a;
end

create procedure p5()
begin
  declare c_id varchar(8);
  declare c_title varchar(50);
  declare c_dept_name varchar(20);
  declare c_credits decimal(2,0);
  declare has_data int default 1;
  --变量声明必须在声明游标之前
  declare cursor_a cursor for select * from course where credits=3;
  --mysql自带的句柄机制，声明当没有数据时退出程序并将has_data设置为0
  --且其必须置于游标声明语句之下
  declare exit HANDLER for not found set has_data=0;
		
  open cursor_a;

  while has_data!=0 do
    fetch cursor_a into c_id,c_title,c_dept_name,c_credits;
    select concat(c_id,' ',c_title,' ',c_dept_name,' ',c_credits);
  end while;
	
  close cursor_a;
end

三.存储函数

create function f1(c_credit decimal(2,0))
returns int
begin
  declare num int default 0;
  select count(*) into num from course where credits=c_credit;
	return num;
end
--调用：select f1(3);
--由于有返回值因此直接采用select调用即可

四.触发器

语法结构：

create trigger trigger_name
before/after insert/update/delete
on tbl_name
[ for each row ] --加上则代表为行级触发器,mysql只支持行级触发器
begin
  trigger_stmt;  --触发所需完成内容
end;

create trigger t1
after update
on emp
for each row
begin
  --update中new表示新修改后的行，old表示旧的行数据
  insert into emp_logs(id,operate_params) values(new.id,concat(old.id,'->',new.id));
end

五.视图

create view v1 as select * from course;
--则创建出的v1即出现在show tables;指令中，因此可以对其进行与普通表相同的操作
--其与实际表的不同在于视图并不是真的存储数据，而是每次都需要进行计算

结尾：
上述知识来自于B站视频教程，可转自 https://www.bilibili.com/video/BV1UQ4y1P7Xr?p=11 观看视频讲解