前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL 存储过程

MySQL 存储过程

作者头像
星哥玩云
发布2022-09-15 19:14:32
36.8K0
发布2022-09-15 19:14:32
举报
文章被收录于专栏:开源部署开源部署

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

一、存储过程概述

1.1、什么是存储过程

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

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

1.2、存储过程特点

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

1.3、存储过程优缺点

优点

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

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

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

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

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

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

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

二、存储过程创建

2.1、创建格式

代码语言:javascript
复制
格式:
create procedure 过程名()
begin
......
end;

案例:

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

代码语言:javascript
复制
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、变量

代码语言:javascript
复制
格式:
declare 变量名 变量类型 default 默认值; #声明变量
set 变量名=值; #变量赋值
select 字段名 into 变量名 from 数据库表; #查询表中字段,完成变量赋值
select 变量名; #显示变量

案例:

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

代码语言:javascript
复制
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块之间

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

案例:

查看员工的人数与部门表中的部门数,并找出最高和最低工资(局部变量)

代码语言:javascript
复制
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 秒)

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

代码语言:javascript
复制
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 秒)

三、存储过程参数

代码语言:javascript
复制
格式:
create procedure 过程名([IN|OUT|INOUT] 参数名 参数数据类型 )
begin
......
end;

注意:

in:传入参数

out:传出参数

inout:可以传入也可以传出

3.1、in

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

案例:

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

代码语言:javascript
复制
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,返回员工的姓名。

代码语言:javascript
复制
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和姓名。

代码语言:javascript
复制
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

代码语言:javascript
复制
格式:
if()
then
...
else
...
end if;

案例:

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

代码语言:javascript
复制
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

代码语言:javascript
复制
格式:
if()
then
...
elseif()
then
...
else
...
end if;

案例:

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

代码语言:javascript
复制
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

代码语言:javascript
复制
格式:
case()
when... then...
when... then...
else...
end case;

案例:

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

代码语言:javascript
复制
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

代码语言:javascript
复制
格式:
while(表达式) do 
......  
end while;

案例:

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

代码语言:javascript
复制
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

代码语言:javascript
复制
格式:
repeat
...
until 条件 -- 条件成立,跳出循环
....
end repeat;

案例:

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

代码语言:javascript
复制
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 秒)

六、存储过程游标

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

代码语言:javascript
复制
格式:
declare 游标名 cursor for SQL语句; #声明游标
open 游标名; #打开游标
fetch 游标名 into 变量名; #取出游标的值
close 游标名; #关闭游标

案例:

输出员工表中的id和姓名

代码语言:javascript
复制
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 秒)

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

代码语言:javascript
复制
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、存储过程查看

代码语言:javascript
复制
格式:
show procedure status [like '%字符串%'];

案例:

代码语言:javascript
复制
mysql> show procedure status;
代码语言:javascript
复制
mysql>show procedure status like '%emp%';
image20200221144121604.png
image20200221144121604.png

7.2、存储过程删除

代码语言:javascript
复制
格式:
drop procedure 存储过程名;

案例:

代码语言:javascript
复制
mysql> drop procedure emp_id;
Query OK, 0 rows affected (0.02 秒)

八、自定义函数

8.1、自定义函数创建

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

代码语言:javascript
复制
格式:
create function 函数名(参数) returns 返回类型
begin
.....
return 返回值;
end;

案例:

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

代码语言:javascript
复制
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;
image20200221152753864.png
image20200221152753864.png

注意:

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

代码语言:javascript
复制
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、自定义函数查询
代码语言:javascript
复制
格式:
show function status [like '%字符串%'];

案例:

代码语言:javascript
复制
mysql> show function status;
代码语言:javascript
复制
mysql> show function status like '%getName%';
image20200221154839122.png
image20200221154839122.png
8.2.2、自定义函数删除
代码语言:javascript
复制
格式:
drop function 函数名;

案例:

代码语言:javascript
复制
mysql> drop function getName;
Query OK, 0 rows affected (0.03 秒)

九、触发器

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

9.1、触发器创建

代码语言:javascript
复制
create trigger 触发器名字 触发时间 触发事件 on 表 for each row
begin
    -- 触发器内容主体,每行用分号结尾
end

注意:

触发时间:

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

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

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

触发事件:

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

案例:

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

代码语言:javascript
复制
#创建一个操作表
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、触发器查看
代码语言:javascript
复制
格式:
show triggers [like '%字符串%'];

案例:

代码语言:javascript
复制
mysql> show triggers;
代码语言:javascript
复制
mysql> show triggers like '%emp%';
image20200221161653246.png
image20200221161653246.png
9.2.2、触发器删除
代码语言:javascript
复制
格式:
drop trigger 触发器名;

案例:

代码语言:javascript
复制
mysql> drop trigger emp_insert;
Query OK, 0 rows affected (0.02 秒)

十、事件

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

10.1、事件创建

代码语言:javascript
复制
格式:
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,插入当前日期时间记录

代码语言:javascript
复制
mysql> desc emp_log;
image20200221171724196.png
image20200221171724196.png
代码语言:javascript
复制
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、查看事件
代码语言:javascript
复制
格式:
show events;

案例:

代码语言:javascript
复制
mysql> show events;
image20200221174040539.png
image20200221174040539.png
10.2.2、启用和禁用事件
代码语言:javascript
复制
格式:
alter event 事件名 disable/enable;

禁用事件

代码语言:javascript
复制
mysql> alter event e_insert disable;
Query OK, 0 rows affected (0.01 秒)

mysql> select * from emp_log;
image20200221174401961.png
image20200221174401961.png

启用事件

代码语言:javascript
复制
mysql> alter event e_insert enable;
Query OK, 0 rows affected (0.02 秒)

mysql> select * from emp_log;
image20200221174452171.png
image20200221174452171.png
10.2.3、删除事件
代码语言:javascript
复制
格式:
drop event 事件名;

案例:

代码语言:javascript
复制
mysql> drop event e_insert;
Query OK, 0 rows affected (0.02 秒)

mysql> show events;
空的数据集 (0.01 秒)
本文参与 腾讯云自媒体分享计划,分享自作者个人站点/博客。
如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 一、存储过程概述
    • 1.1、什么是存储过程
      • 1.2、存储过程特点
        • 1.3、存储过程优缺点
        • 二、存储过程创建
          • 2.1、创建格式
            • 2.2、变量
              • 2.3、变量作用域
              • 三、存储过程参数
                • 3.1、in
                  • 3.2、out
                    • 3.3、inout
                    • 四、存储过程条件
                      • 4.1、if…else…end if
                        • 4.2、if…elseif…else…endif
                          • 4.3、case
                          • 五、存储过程循环
                            • 5.1、while
                              • 5.2、repeat
                              • 六、存储过程游标
                              • 七、存储过程操作
                                • 7.1、存储过程查看
                                  • 7.2、存储过程删除
                                  • 八、自定义函数
                                    • 8.1、自定义函数创建
                                      • 8.2、自定义函数操作
                                        • 8.2.1、自定义函数查询
                                        • 8.2.2、自定义函数删除
                                    • 九、触发器
                                      • 9.1、触发器创建
                                        • 9.2、触发器操作
                                          • 9.2.1、触发器查看
                                          • 9.2.2、触发器删除
                                      • 十、事件
                                        • 10.1、事件创建
                                          • 10.2、事件操作
                                            • 10.2.1、查看事件
                                            • 10.2.2、启用和禁用事件
                                            • 10.2.3、删除事件
                                        相关产品与服务
                                        对象存储
                                        对象存储(Cloud Object Storage,COS)是由腾讯云推出的无目录层次结构、无数据格式限制,可容纳海量数据且支持 HTTP/HTTPS 协议访问的分布式存储服务。腾讯云 COS 的存储桶空间无容量上限,无需分区管理,适用于 CDN 数据分发、数据万象处理或大数据计算与分析的数据湖等多种场景。
                                        领券
                                        问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档