MySQL 存储过程

MySQL 存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升

一、存储过程概述

1.1、什么是存储过程

存储过程是数据库中的一个重要对象。

存储过程是在数据库系统中，一组为了完成特定功能的SQL 语句集。存储过程是存储在数据库中，一次编译后，到处运行。不需要再次编译，用户通过指定存储过程的名字并传递参数（如果该存储过程带有参数）来执行。

1.2、存储过程特点

用来完成较复杂业务
比较灵活，易修改，好编写，可编程性强
编写好的存储过程可重复使用

1.3、存储过程优缺点

优点

存储过程在创建的时候直接编译，sql语句每次使用都要编译，效率高。

存储过程可以被重复使用。

存储过程只连接一次数据库，sql语句在访问多张表时，连接多次数据库。

存储的程序是安全的。存储过程的应用程序授予适当的权限。
缺点
在那里创建的存储过程，就只能在那里使用，可移植性差。

开发存储过程时，标准不定好的话，后期维护麻烦。

没有具体的编辑器，开发和调试都不方便。

太复杂的业务逻辑，存储过程也解决不了。

二、存储过程创建

2.1、创建格式

格式：
create procedure 过程名()
begin
......
end;

案例：

查看员工与部门表中的全信息

create procedure dept_emp()
begin
	select * from dept;
	select * from emp;
end;

mysql> call dept_emp();
+----+-----------+
| id | name      |
+----+-----------+
| 1  | 研发部 |
| 2  | 渠道部 |
| 3  | 教务部 |
| 4  | 执行部 |
+----+-----------+
4 行于数据集 (0.02 秒)

+----+--------+--------+--------+------------+---------+
| id | name   | gender | salary | join_date  | dept_id |
+----+--------+--------+--------+------------+---------+
| 1  | 张三 | 男    | 7200   | 2013-02-24 | 1       |
| 2  | 李四 | 男    | 3600   | 2010-12-02 | 2       |
| 3  | 王五 | 男    | 9000   | 2008-08-08 | 2       |
| 4  | 赵六 | 女    | 5000   | 2015-10-07 | 3       |
| 5  | 吴七 | 女    | 4500   | 2011-03-14 | 1       |
| 6  | 王一 | 男    | 8768   | 2013-12-05 | NULL    |
| 7  | 王二 | 女    | NULL   | NULL       | NULL    |
+----+--------+--------+--------+------------+---------+
7 行于数据集 (0.05 秒)

Query OK, 0 rows affected (0.05 秒)

2.2、变量

格式：
declare 变量名 变量类型 default 默认值; #声明变量
set 变量名=值; #变量赋值
select 字段名 into 变量名 from 数据库表; #查询表中字段，完成变量赋值
select 变量名; #显示变量

案例：

查看员工表中id=1的员工的姓名

create procedure emp_name()
begin
	declare ename varchar(20) default '';
	select name into ename from emp where id=1;
	select ename;
end;

mysql> call emp_name();
+--------+
| ename  |
+--------+
| 张三 |
+--------+
1 行于数据集 (0.01 秒)

Query OK, 0 rows affected (0.01 秒)

2.3、变量作用域

存储过程中变量是有作用域的，作用范围在begin和end块之间，end结束变量的作用范围即结束。

变量可分为：

局部变量： begin和end块之间

全局变量： 放在所有代码块之前；传参变量是全局的，可以在多个块之间起作用

案例：

查看员工的人数与部门表中的部门数，并找出最高和最低工资（局部变量）

create procedure dept_or_emp()
begin
	begin
		declare e_n int default 0;
		declare d_n int default 0;
		select count(*) into e_n from emp;
		select count(*) into d_n from dept;
		select e_n,d_n;
	end;
	begin
		declare max_s double default 0;
		declare min_s double default 0;
		select max(salary) into max_s from emp;
		select min(salary) into min_s from emp;
		select max_s,min_s;
	end;
end;

mysql> call dept_or_emp();
+------+------+
| e_n  | d_n  |
+------+------+
| 7    | 4    |
+------+------+
1 行于数据集 (0.26 秒)

+-------+-------+
| max_s | min_s |
+-------+-------+
| 9000  | 3600  |
+-------+-------+
1 行于数据集 (0.26 秒)

Query OK, 0 rows affected (0.26 秒)

查看员工的人数与部门表中的部门数，并找出最高和最低工资（全局变量）

create procedure dept_or_emp1()
begin
	declare e_n int default 0;
	declare d_n int default 0;
	declare max_s double default 0;
	declare min_s double default 0;
	begin
		select count(*) into e_n from emp;
		select count(*) into d_n from dept;
	end;
	begin

		select max(salary) into max_s from emp;
		select min(salary) into min_s from emp;
	end;
	select e_n,d_n,max_s,min_s;
end;

mysql> call dept_or_emp1();
+------+------+
| e_n  | d_n  |
+------+------+
| 7    | 4    |
+------+------+
1 行于数据集 (0.22 秒)

+-------+-------+
| max_s | min_s |
+-------+-------+
| 9000  | 3600  |
+-------+-------+
1 行于数据集 (0.23 秒)

Query OK, 0 rows affected (0.23 秒)

三、存储过程参数

格式：
create procedure 过程名([IN|OUT|INOUT] 参数名 参数数据类型 )
begin
......
end;

注意：

in：传入参数

out：传出参数

inout：可以传入也可以传出

3.1、in

表示该参数的值必须在调用存储过程事指定，如果不显示指定为in,那么默认就是in类型。

案例：

根据传入的id查看员工的姓名。

create procedure emp_id(eid int)
begin
	declare ename varchar(20) default '';
	select name into ename from emp where id=eid;
	select ename;
end;

mysql> call emp_id(1);
+--------+
| ename  |
+--------+
| 张三 |
+--------+
1 行于数据集 (0.01 秒)

Query OK, 0 rows affected (0.01 秒)

mysql> call emp_id(2);
+--------+
| ename  |
+--------+
| 李四 |
+--------+
1 行于数据集 (0.01 秒)

Query OK, 0 rows affected (0.02 秒)

mysql> call emp_id(9);
+-------+
| ename |
+-------+
|       |
+-------+
1 行于数据集 (0.01 秒)

Query OK, 0 rows affected (0.01 秒)

3.2、out

out参数也需要指定，但必须是变量，不能是常量。

案例：

根据传入的id，返回员工的姓名。

create procedure emp_id1(eid int,out ename varchar(20))
begin
	select name into ename from emp where id=eid;
end;

mysql> set @ename='';
Query OK, 0 rows affected (0.02 秒)

mysql> call emp_id1(3,@ename);
Query OK, 1 rows affected, 1 warnings (0.02 秒)

mysql> select @ename;
+--------+
| @ename |
+--------+
| 王五 |
+--------+
1 行于数据集 (0.02 秒)

3.3、inout

如果既需要传入，同时又需要传出，则可以使用INOUT类型参数

案例：

根据传入的id，返回员工的id和姓名。

create procedure emp_id2(inout eid int,out ename varchar(20))
begin
	select id,name into eid,ename from emp where id=eid;
end;

mysql> set @eid=3;
Query OK, 0 rows affected (0.01 秒)

mysql> set @ename='';
Query OK, 0 rows affected (0.01 秒)

mysql> call emp_id2(@eid,@ename);
Query OK, 1 rows affected (0.01 秒)

mysql> select @eid,@ename;
+------+--------+
| @eid | @ename |
+------+--------+
| 3    | 王五 |
+------+--------+
1 行于数据集 (0.01 秒)

四、存储过程条件

4.1、if…else…end if

格式：
if()
then
...
else
...
end if;

案例：

输入一个id，判断他是否是偶数，偶数打印对应的姓名，奇数打印id

create procedure emp_if_id(eid int)
begin
	declare ename varchar(20) default '';
	if(eid%2=0)
	then
		select name into ename from emp where id=eid;
		select ename;
  else
  	select eid;
  end if;
end;

mysql> call emp_if_id(2);
+--------+
| ename  |
+--------+
| 李四 |
+--------+
1 行于数据集 (0.02 秒)

Query OK, 0 rows affected (0.02 秒)

mysql> call emp_if_id(1);
+------+
| eid  |
+------+
| 1    |
+------+
1 行于数据集 (0.01 秒)

Query OK, 0 rows affected (0.01 秒)

4.2、if…elseif…else…endif

格式：
if()
then
...
elseif()
then
...
else
...
end if;

案例：

给id为1，2，3的员工加薪1000元，其他员工不变

create procedure emp_if_salary(eid int)
begin
	declare esalary double default 0;
	if(eid=1)
	then
		update emp set salary=salary+1000 where id=eid;
  elseif(eid=2)
  then
  	update emp set salary=salary+1000 where id=eid;
  elseif(eid=3)
  then
  	update emp set salary=salary+1000 where id=eid;	
  else
  	update emp set salary=salary where id=eid;	
  end if;
  select salary into esalary from emp where id=eid;
  select esalary;
end;

mysql> call emp_if_salary(1);
+---------+
| esalary |
+---------+
| 8200    |
+---------+
1 行于数据集 (0.03 秒)

Query OK, 0 rows affected (0.05 秒)

mysql> call emp_if_salary(3);
+---------+
| esalary |
+---------+
| 10000   |
+---------+
1 行于数据集 (0.02 秒)

Query OK, 0 rows affected (0.02 秒)

mysql> call emp_if_salary(9);
+---------+
| esalary |
+---------+
| 0       |
+---------+
1 行于数据集 (0.02 秒)

Query OK, 0 rows affected (0.02 秒)

4.3、case

格式：
case()
when... then...
when... then...
else...
end case;

案例：

给id为1，2，3的员工加薪1000元，其他员工不变

create procedure emp_case_salary(eid int)
begin
	declare esalary double default 0;
	case (eid)
	when 1 then update emp set salary=salary+1000 where id=eid;
  when 2 then update emp set salary=salary+1000 where id=eid;
  when 3 then update emp set salary=salary+1000 where id=eid;	
  else
  	update emp set salary=salary where id=eid;	
  end case;
  select salary into esalary from emp where id=eid;
  select esalary;
end;

mysql> call emp_case_salary(3);
+---------+
| esalary |
+---------+
| 12000   |
+---------+
1 行于数据集 (0.02 秒)

Query OK, 0 rows affected (0.02 秒)

五、存储过程循环

5.1、while

格式：
while(表达式) do 
......  
end while;

案例：

通过id查询出员工表中的前5个员工的姓名

create procedure emp_view()
begin
	declare eid int default 1;
	declare ename varchar(20) default '';
	while(eid<=5) do
		select name into ename from emp where id=eid;	
		select ename;
		set eid=eid+1;
	end while;	
end;

mysql> call emp_view();
+--------+
| ename  |
+--------+
| 张三 |
+--------+
1 行于数据集 (0.01 秒)

+--------+
| ename  |
+--------+
| 李四 |
+--------+
1 行于数据集 (0.01 秒)

+--------+
| ename  |
+--------+
| 王五 |
+--------+
1 行于数据集 (0.02 秒)

+--------+
| ename  |
+--------+
| 赵六 |
+--------+
1 行于数据集 (0.02 秒)

+--------+
| ename  |
+--------+
| 吴七 |
+--------+
1 行于数据集 (0.03 秒)

Query OK, 0 rows affected (0.03 秒)

5.2、repeat

格式：
repeat
...
until 条件 -- 条件成立，跳出循环
....
end repeat;

案例：

通过id查询出员工表中的前5个员工的姓名

create procedure emp_view1()
begin
	declare eid int default 1;
	declare ename varchar(20) default '';
	repeat
		select name into ename from emp where id=eid;		
		select ename;
		set eid=eid+1;
	until eid>5
	end repeat;	
end;

mysql> call emp_view1();
+--------+
| ename  |
+--------+
| 张三 |
+--------+
1 行于数据集 (0.02 秒)

+--------+
| ename  |
+--------+
| 李四 |
+--------+
1 行于数据集 (0.03 秒)

+--------+
| ename  |
+--------+
| 王五 |
+--------+
1 行于数据集 (0.05 秒)

+--------+
| ename  |
+--------+
| 赵六 |
+--------+
1 行于数据集 (0.06 秒)

+--------+
| ename  |
+--------+
| 吴七 |
+--------+
1 行于数据集 (0.06 秒)

Query OK, 0 rows affected (0.06 秒)

六、存储过程游标

游标是保存查询结果的临时区域

格式：
declare 游标名 cursor for SQL语句; #声明游标
open 游标名; #打开游标
fetch 游标名 into 变量名; #取出游标的值
close 游标名; #关闭游标

案例：

输出员工表中的id和姓名

create procedure emp_all_view()
begin
	declare eid int default 1;
	declare ename varchar(20) default '';
	declare c_emp cursor for select id,name from emp;
	open c_emp;
	fetch c_emp into eid,ename;
	select eid,ename;
	close c_emp;
end;

mysql> call emp_all_view();
+------+--------+
| eid  | ename  |
+------+--------+
| 1    | 张三 |
+------+--------+
1 行于数据集 (0.03 秒)

Query OK, 0 rows affected (0.03 秒)

这样我们只取出了一条信息，这个时候我们需要循环？

create procedure emp_all_view1()
begin
	declare eid int default 1;
	declare ename varchar(20) default '';
	declare c_emp cursor for select id,name from emp;
	
	open c_emp;
	loop
		fetch c_emp into eid,ename;
		select eid,ename;
	end loop;
	close c_emp;
end;

mysql> call emp_all_view1();
+------+--------+
| eid  | ename  |
+------+--------+
| 1    | 张三 |
+------+--------+
1 行于数据集 (0.01 秒)

+------+--------+
| eid  | ename  |
+------+--------+
| 2    | 李四 |
+------+--------+
1 行于数据集 (0.02 秒)

+------+--------+
| eid  | ename  |
+------+--------+
| 3    | 王五 |
+------+--------+
1 行于数据集 (0.03 秒)

+------+--------+
| eid  | ename  |
+------+--------+
| 4    | 赵六 |
+------+--------+
1 行于数据集 (0.03 秒)

+------+--------+
| eid  | ename  |
+------+--------+
| 5    | 吴七 |
+------+--------+
1 行于数据集 (0.03 秒)

+------+--------+
| eid  | ename  |
+------+--------+
| 6    | 王一 |
+------+--------+
1 行于数据集 (0.04 秒)

+------+--------+
| eid  | ename  |
+------+--------+
| 7    | 王二 |
+------+--------+
1 行于数据集 (0.05 秒)

No data - zero rows fetched, selected, or processed

七、存储过程操作

7.1、存储过程查看

格式：
show procedure status [like '%字符串%'];

案例：

mysql> show procedure status;

mysql>show procedure status like '%emp%';

7.2、存储过程删除

格式：
drop procedure 存储过程名;

案例：

mysql> drop procedure emp_id;
Query OK, 0 rows affected (0.02 秒)

八、自定义函数

8.1、自定义函数创建

函数与存储过程最大的区别是函数必须有返回值，否则会报错

格式：
create function 函数名(参数) returns 返回类型
begin
.....
return 返回值;
end;

案例：

通过输入的id获取员工的姓名

create function getName(eid int) returns varchar(20)
begin
	declare ename varchar(20) default '';
	select name into ename from emp where id=eid;
	return ename;
end;

注意：

这是我们开启了bin-log, 我们就必须指定我们的函数指定一个参数deterministic 不确定的
no sql 没有SQL语句，当然也不会修改数据
reads sql data 只是读取数据，当然也不会修改数据
modifies sql data 要修改数据
contains sql 包含了SQL语句

create function getName(eid int) returns varchar(20) reads sql data
begin
	declare ename varchar(20) default '';
	select name into ename from emp where id=eid;
	return ename;
end;

mysql> select getName(1);
+------------+
| getName(1) |
+------------+
| 张三     |
+------------+
1 行于数据集 (0.02 秒)

8.2、自定义函数操作

8.2.1、自定义函数查询

格式：
show function status [like '%字符串%'];

案例：

mysql> show function status；

mysql> show function status like '%getName%';

8.2.2、自定义函数删除

格式：
drop function 函数名;

案例：

mysql> drop function getName;
Query OK, 0 rows affected (0.03 秒)

九、触发器

触发器与函数、存储过程一样，触发器是一种对象，它能根据对表的操作时间，触发一些动作，这些动作可以是insert,update,delete等操作。

9.1、触发器创建

create trigger 触发器名字 触发时间 触发事件 on 表 for each row
begin
    -- 触发器内容主体，每行用分号结尾
end

注意：

触发时间：

当 SQL 指令发生时，会令行中数据发生变化，而每张表中对应的行有两种状态：数据操作前和操作后

before：表中数据发生改变前的状态

after：表中数据发生改变后的状态

触发事件：

触发器是针对数据发送改变才会被触发，对应的操作只有insert、update、delete

案例：

向员工表中插入数据时，记录插入的id，动作，时间

#创建一个操作表
create table emp_log(
	id int primary key auto_increment,
  eid int,
  eaction varchar(20),
  etime datetime
);

mysql> select * from emp_log;
空的数据集 (0.01 秒)

#创建触发器
create trigger emp_insert after insert on emp for each row
begin
	insert into emp_log values(null,NEW.id,'insert',now());
end;

mysql> insert into emp(id,name,gender)values(8,'王三','男');
Query OK, 1 rows affected (0.01 秒)

mysql> select * from emp_log;
+----+------+---------+---------------------+
| id | eid  | eaction | etime               |
+----+------+---------+---------------------+
| 1  | 8    | insert  | 2020-02-21 03:12:44 |
+----+------+---------+---------------------+
1 行于数据集 (0.02 秒)

9.2、触发器操作

9.2.1、触发器查看

格式：
show triggers [like '%字符串%'];

案例：

mysql> show triggers;

mysql> show triggers like '%emp%';

9.2.2、触发器删除

格式：
drop trigger 触发器名;

案例：

mysql> drop trigger emp_insert;
Query OK, 0 rows affected (0.02 秒)

十、事件

事件取代了原先只能由操作系统的计划任务来执行的工作，而且MySQL的事件调度器可以精确到每秒钟执行一个任务，而操作系统的计划任务只能精确到每分钟执行一次。

10.1、事件创建

格式：
create event[IF NOT EXISTS] event_name -- 创建事件
on schedule 时间和频率 -- on schedule 什么时候来执行
[on completion [NOT] preserve] -- 调度计划执行完成后是否还保留
[enable | disable] -- 是否开启事件，默认开启
[comment '事件描述'] -- 事件的注释
do event_body;-- 需要执行的SQL

注意：

单次计划任务示例
在2019年2月1日4点执行一次 on schedule at ‘2019-02-01 04:00:00’

重复计划执行
on schedule every 1 second 每秒执行一次
on schedule every 1 minute 每分钟执行一次
on schedule every 1 day 没天执行一次

指定时间范围的重复计划任务
每天在20:00:00执行一次 on schedule every 1 day starts ‘2019-02-01 20:00:00’

案例：

每5秒向emp_log,插入当前日期时间记录

mysql> desc emp_log;

create event e_insert on schedule every 5 second on completion preserve
enable
comment '每5秒插入一次'
do
begin
	insert into emp_log values(null,1,'insert1',now());
end;
#do call 存储过程 
#do select 函数名

10.2、事件操作

10.2.1、查看事件

格式：
show events;

案例：

mysql> show events;

10.2.2、启用和禁用事件

格式：
alter event 事件名 disable/enable;

禁用事件

mysql> alter event e_insert disable;
Query OK, 0 rows affected (0.01 秒)

mysql> select * from emp_log;

启用事件

mysql> alter event e_insert enable;
Query OK, 0 rows affected (0.02 秒)

mysql> select * from emp_log;

10.2.3、删除事件

格式：
drop event 事件名;

案例：

mysql> drop event e_insert;
Query OK, 0 rows affected (0.02 秒)

mysql> show events;
空的数据集 (0.01 秒)