数据库:mysql基础(六.)--存储程序

最新推荐文章于 2022-03-30 19:22:14 发布

XiaodunLP

最新推荐文章于 2022-03-30 19:22:14 发布

阅读量286

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/XiaodunLP/article/details/83034378

版权

MySQL 专栏收录该内容

21 篇文章 0 订阅

订阅专栏

存储程序

什么是存储程序?
- 以前我们是在客户端中发送一系列的sql语句给MySQL服务器端,它再通过存储引擎处理后将结果响应回我们.而存储程序是我们在服务器端保存一段程序,我们如果想使用的话,就直接来调用这段程序,这就是存储程序.
- 总结来说:存储和执行在服务器端的程序.
为什么需要使用存储程序?
- 优点:
  - 执行效率高
  - 简化开发
- 缺点
  - 占用服务器端资源
  - 数据迁移
  - 这个就就简单记记：调试不太好调试；占用服务器端的进程，如果发生一些不太好的事情，可能会对服务器造成影响，崩溃的可能
- 现在的数据库不像以前那样还采用很多的功能了，大部分只是作为存储使用。写存储程序的很少了。

分类

存储过程

执行一组sql语句，可以有输入和输出参数。
创建存储过程：

#存储过程
    #根据员工的姓名查询职位信息。
    #按照以前怎么写呢？
    select job from emp where ename = 'xx';
    #现在我们希望，我给你一个名字，服务器给我查一个，就用到了存储过程。将ename当作一个参数来传入

delimiter //; #设置分割符 --暂时不把；作为结束标志

create procedure sel_jon(name varchar(20))
begin
    select job from emp where ename = name;
end;

调用存储过程

```
#用call调用
call sel_job('smith');
```
上面的存储过程保存在服务器端。

存储过程参数的三种模式：

in：输入，默认模式
out：输出
inout ：输入输出
上面的存储过程sel_job就是默认的in。out就是存储过程执行完毕的数据可以通过参数传出来；inout是可以传进去也可以传出来，不过这个有局限，类型要一致
下面演示一下out:

#根据员工的姓名查询职位信息。
delimiter //;

create procedure sel_job2(name varchar(20),out e_job varchar(20))
begin
  select job into e_job from emp where ename = name;
end;

#将name传进来，通过条件ename=name条件判断查询出满足的结果job值传到e_job中，因为e_job是一个out参数，所以我们就可以拿出来它的值

#调用
call sel_job2('scott',@e_job);
#设置一个临时变量来存储输出来的值 ：@。。。。
#这句话执行完成后，@e_job里就有值了
#通过select语句显示输出，拿到这个值
select @e_job;

下面再看一下inout模式：

#根据员工的姓名查询职位信息。
delimiter //;
create procedure sel_job3(inout name_job varchar(20))
begin
  select job into name_job from emp where ename = name_job;
end;

#调用
#因为只有一个参数，既要作为输入，又要作为输出。所以不能直接传入。所以需要定义一个变量。通过set @..=..;
set @name='scott';
call sel_job3(@name);
select @name;

上面只是写一些简单的，其实存储过程里面是可以写if、循环。。的

存储过程的流程控制

if语法

#存储过程: if 
#例子：输入成绩，进行分级 >80为A  60-80为B  不到60为C

delimiter //;
create procedure score(sc int)
begin 
  #变量声明
	declare v_level varchar(20);
	if sc >= 80 then
	  set v_level = 'A';
	elseif sc >= 60 then 
      set v_level = 'B';
	else
      set v_level = 'C';
	end if;	
    select v_level;	    #得到最后数据
end;

#调用
call score(69);

else后面就没有then了。

循环语句

while..do ...end while

#循环语句  1+2+...+100

delimiter //;
create procedure calc()
begin
    declare i int;    #这里声明变量declare
    declare sum int;
    set i=1;          #这里赋值
    set sum=0;
	while i<=100 do
		set sum=sum+i;
		set i=i+1;
	end while;
	select sum;
end;

#调用
call calc();

loop...end loop循环也很类似

delimiter //;
create procedure calc2()
begin
    declare i int;
	declare sum int;
	set i=1;
	set sum=0;
	lip:loop   #别名在前面
	    set sum=sum+i;
		set i=i+1;
		if i>100 then      #循环的条件在这里
		  leave lip;       #不能直接离开loop,需要别名
		end if;            #离开用leave，if和end if成对
	end loop;
	select sum;
end;

call calc2();

repeat...end repeat循环：和上面两个都相似

delimiter //;
create procedure calc3()
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    #和loop很像，都是死循环，需要条件
	end repeat;        #用的不是if,用的是until,直到i>100结束循环
	select sum;
end;

call calc3();

简单总结一下:

存储过程是有输入输出参数的.参数分为三种模式:in/out/inout;
可以用流程控制语句:if/while ... do ... end while/loop...end loop/repeat...end repeat
创建:procedure 调用call

存储函数

执行一组sql语句，可以有返回值
存储函数其实和存储过程是很类似的,但是有些不同的地方:存储过程是可以独立运行的,但是存储函数必须作为sql语句的一部分来运行.

下面看一下创建语法:

#存储函数...下面举例说明
#根据雇员编号查询名称

delimiter //;
create function func_emp(eno int)     #这一步和存储过程很相似,只有关键字不一样
returns varchar(20)          #因为存储函数必须有返回值,所以这一步定义返回值类型
deterministic                #意思是确定的--如果这个函数多次输入同样的参数值,结果是一样的,否则前面加not
beigin
    declare name varchar(20);    #定义一个变量来存储需要返回的数据,注意要和returns后面的一致
    select ename into name from emp where empno = eno;  #用into将得到的值放到name中
    return name;
end;


#调用
#和存储过程有区别的是,存储函数必须作为sql语句的一部分
select func_emp(7788);    #结果是SCOTT   此时返回的字段名称是:func_emp(7788)

触发器

执行一组sql语句，由事件驱动自动执行
触发器和存储过程,存储函数也有相似性,都是将一组sql语句放在一起来进行执行,但是它是由事件驱动的.自动执行的.所以是不能传递参数的.
这里面的事件主要指三个:插入事件,删除事件,修改事件.
- 例如,对某张表添加了增删改的某个触发器,一旦对这个表进行了相应的增删改操作,那么就会自动执行触发器中的内容.换言之就是触发器会监听设置触发器的事件,并进行响应.

#举个例子:
#删除student表的记录,将删除的记录添加到bak表中
#首先我们需要一张bak表,那么回忆一下如何创建一个和student表结构相同的表呢?
create table bak select * from student where 1=0;    #这是以前的知识,只拿到表的结构

#创建触发器
delimiter //;
create trigger tri_stu        #关键字是trigger,因为触发器不能传参,所以没有参数列表
#但是注意,既然触发器由事件驱动,那么一定要说明由什么事件驱动,和驱动的时机,分别为:
#before 在事件发生前| after ..后    inset插入事件|update修改..|delete删除..
after delete
on student for each row # on 表名:表示为那张表设立触发器   for each row 触发器类型:行级触发器,每删除一行都会触发这个触发器
begin   #事件发生后触发器需要执行的内容定义在这里
    insert into stu_bak values(old.sid,old.sname,old.cid)
    #这里的难点是需要向新表中插入的值是什么,这里有两个关键字 old | new  指代旧的|新的记录
end;

执行:delete from student where sid = 5;这个语句看看表的变化

触发器完整写法可以看下面,了解一下.

#完整的语法模式是这个样子的:
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

#实际操作
create TRIGGER add_t1_t2
after INSERT 
on t1 FOR EACH ROW
BEGIN
	insert t2(name) values('tt');
end;