定制网站开发平台_义乌便宜自适应网站建设厂家_整合营销策划方案_小程序开发教程全集免费

🌈你好呀！我是 山顶风景独好
🎈欢迎踏入我的博客世界，能与您在此邂逅，真是缘分使然！😊
🌸愿您在此停留的每一刻，都沐浴在轻松愉悦的氛围中。
📖这里不仅有丰富的知识和趣味横生的内容等您来探索，更是一个自由交流的平台，期待您留下独特的思考与见解。🌟
🚀让我们一起踏上这段探索与成长的旅程，携手挖掘更多可能，共同进步！💪✨

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

一、存储过程概述

1.1、什么是存储过程

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

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

1.2、存储过程特点

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

1.3、存储过程优缺点

优点

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

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

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

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

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

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

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

二、存储过程创建

2.1、创建格式

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

案例：

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

create procedure dept_emp()
beginselect * 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()
begindeclare 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()
beginbegindeclare 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;begindeclare 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()
begindeclare e_n int default 0;declare d_n int default 0;declare max_s double default 0;declare min_s double default 0;beginselect count(*) into e_n from emp;select count(*) into d_n from dept;end;beginselect 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)
begindeclare 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))
beginselect 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))
beginselect 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)
begindeclare ename varchar(20) default '';if(eid%2=0)thenselect name into ename from emp where id=eid;select ename;elseselect 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)
begindeclare esalary double default 0;if(eid=1)thenupdate emp set salary=salary+1000 where id=eid;elseif(eid=2)thenupdate emp set salary=salary+1000 where id=eid;elseif(eid=3)thenupdate emp set salary=salary+1000 where id=eid;	elseupdate 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)
begindeclare 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;	elseupdate 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()
begindeclare eid int default 1;declare ename varchar(20) default '';while(eid<=5) doselect 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()
begindeclare eid int default 1;declare ename varchar(20) default '';repeatselect name into ename from emp where id=eid;		select ename;set eid=eid+1;until eid>5end 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()
begindeclare 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()
begindeclare eid int default 1;declare ename varchar(20) default '';declare c_emp cursor for select id,name from emp;open c_emp;loopfetch 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)
begindeclare 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
begindeclare 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
begininsert 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

注意：

单次计划任务示例
在2024年6月11日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 ‘2024-06-10 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
begininsert 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 秒)

---

✨ 这就是今天要分享给大家的全部内容了，我们下期再见！😊
🏠 我在CSDN等你哦！我的主页😍