文章目录
6.MySQL高阶——存储过程
存储过程
存储过程就是数据库SQL语言层面的代码封装与重用。
delimiter 自定义结束符号
create procedure 储存名([ in ,out ,inout ] 参数名 数据类型...)
begin
sql语句...
end 自定义的结束符合
delimiter ;
数据准备
-- 1：创建数据库
create database my_procedure;
use my_procegure;
-- 2：在该数据库下导入sql脚本:procedure_data.sql
-- 3：创建存储过程
delimiter $$
create procedure proc01()
begin
select empno,ename from emp;
end $$
delimiter ;
-- 调用存储过程
call proc01();
变量定义
局部变量
声明变量 declare var_name var_type [default var_value];
实例
### 局部变量 ################
delimiter $$
create procedure proc02()
begin
declare var_name01 varchar(20) default 'aaa'; -- 定义局部变量
set var_name01 = 'zhangsan';-- 修改变量的值
select var_name01;
end $$
delimiter ;
-- 调用存储过程
call proc02();
##或者使用select...into语句为变量赋值
### 局部变量 ################
delimiter $$
create procedure proc03()
begin
declare my_ename varchar(20) ;
select ename into my_ename from emp where empno=1001;
select my_ename;
end $$
delimiter ;
-- 调用存储过程
call proc03();
会话变量-用户变量
@var_name 不需要提前声明，使用即声明
实例：
########### 会话变量 #################
delimiter $$
create procedure proc04()
begin
set @var_name01 = 'ZS';
end $$
delimiter ;
call proc04() ;
-- 调用存储过程
select @var_name01;
全局变量
@@global.var_name
实例：
-- 查看全局变量
show global variables;
-- 查看某全局变量
select @@global.auto_increment_increment;
-- 修改全局变量的值
set global sort_buffer_size = 40000;
会话变量-系统变量
@@session.var_name
实例：
-- 查看会话变量
show session variables;
-- 查看某会话变量
select @@session.auto_increment_increment;
-- 修改会话变量的值
set session sort_buffer_size = 50000;
set @@session.sort_buffer_size = 50000 ;
存出过程传参
存储过程传参-in
in 表示传入的参数， 可以传入数值或者变量，即使传入变量，并不会更改变量的值，可以内部更改，仅仅作用在函数范围内。
实例：
-- 封装有参数的存储过程，传入员工编号，查找员工信息
delimiter $$
create procedure dec_param01(in param_empno varchar(20))
begin
select * from emp where empno = param_empno;
end $$
delimiter ;
call dec_param01('1001');
-- 封装有参数的存储过程，可以通过传入部门名和薪资，查询指定部门，并且薪资大于指定值的员工信息
delimiter $$
create procedure dec_param02(in dname varchar(50),in sal decimal(7,2))
begin
select * from dept a join emp b on a.deptno= b.deptno and b.sal > sal and a.dname = dname;
end $$
delimiter ;
call dec_param02('学工部',20000);
存储过程传参-out
out 表示从存储过程内部传值给调用者
实例：
-- 封装有参数的存储过程，传入员工编号，返回员工名字
delimiter $$
create procedure dec_param03(in in_empno int ,out out_ename varchar(50) )
begin
select ename into out_ename from emp where empno = in_empno;
end $$
delimiter ;
call dec_param03(1001, @o_ename);
select @o_ename;
-- 封装有参数的存储过程，传入员工编号，返回员工名字和薪资
delimiter $$
create procedure dec_param04(in in_empno int ,out out_ename varchar(50) ,out out_sal decimal(7,2))
begin
select ename,sal into out_ename,out_sal from emp where empno = in_empno;
end $$
delimiter ;
call dec_param04(1001, @o_dname,@o_sal);
select @o_dname;
select @o_sal;
存储过程传参-inout
inout 表示从外部传入的参数经过修改后可以返回的变量，既可以使用传入变量的值也可以修改变量的值（即使函数执行完）
实例：
-- 传入员工名，拼接部门号，传入薪资，求出年薪
delimiter $$
create procedure proc05(inout inout_ename varchar(50),inout inout_sal int)
begin
select concat(deptno,'_',inout_ename) into inout_ename from emp where ename = inout_ename;
set inout_sal = inout_sal * 12;
end $$
delimiter ;
set @inout_ename = '关羽';
set @inout_sal = 3000;
call proc05(@inout_ename, @inout_sal) ;
select @inout_ename ;
select @inout_sal ;
流程控制—判断
if判断语句
-- 语法
if search_condition_1 then statement_list_1
[elseif search_condition_2 then statement_list_2] ...
[else statement_list_n]
end if
实例：
-- 输入学生的成绩，来判断成绩的级别
delimiter $$
create procedure proc_06(in score int)
begin
if score < 60 then select '不及格';
elseif score < 80 then select '及格' ;
elseif score < 90 then select '良好';
elseif score <= 100 then select '优秀';
else select '成绩错误';
end if;
end $$
delimiter ;
call proc_06(89)
加强
-- 输入员工的名字，判断工资的情况。
delimiter $$
create procedure proc07(in in_ename varchar(50))
begin
declare result varchar(20);
declare var_sal decimal(7,2);
select sal into var_sal from emp where ename = in_ename;
if var_sal < 10000
then set result = '试用薪资';
elseif var_sal < 30000
then set result = '转正薪资';
else
set result = '元老薪资';
end if;
select result;
end $$
delimiter ;
call proc07('庞统');
case判断
-- 语法一：
case case_value
when when_value then statement_list
[when when_value then statement_list] ...
[else statement_list]
end case
-- 语法二：
case
when search_condition then statement_list
[when search_condition then statement_list] ...
[else statement_list]
end case
实例：
-- 语法一
delimiter $$
create procedure proc07(in pay_type int)
begin
case pay_type
when 1 then select '微信支付' ;
when 2 then select '支付宝支付' ;
when 3 then select '银行卡支付';
else select '其他方式支付';
end case ;
end $$
delimiter ;
call proc07(2);
call proc07(4);
-- 语法二
delimiter $$
create procedure proc08(in score int)
begin
case
when score < 60 then select '不及格';
when score < 80 then select '及格' ;
when score < 90 then select '良好';
when score <= 100 then select '优秀';
else select '成绩错误';
end case;
end $$
delimiter ;
call proc08(88);
流程控制—循环
循环控制
（1）leave类似于break，跳出，结束当前所在的循环
（2）iterate类似于continue，继续，结束本次循环，继续下一次
while循环
【标签:】while 循环条件 do
循环体;
end while【 标签】;
准备数据：
############ 循环条件 ###################
-- 创建测试表
create table user (
uid int primary key,
username varchar ( 50 ),
password varchar ( 50 )
);
实例1：while
-- -------存储过程-while
delimiter $$
create procedure proc09(in insertcount int)
begin
declare i int default 1;
while i<=insertcount do
insert into user(uid,username,`password`) values(i,concat('user-',i),'123456');
set i=i+1;
end while;
end $$
delimiter ;
call proc09(10);
实例2：while + leave
-- -------存储过程-while + leave
truncate table user;
delimiter $$
create procedure proc10(in insertcount int)
begin
declare i int default 1;
label:while i<=insertcount do
insert into user(uid,username,`password`) values(i,concat('user-',i),'123456');
if i=5 then leave label;
end if;
set i=i+1;
end while label;
end $$
delimiter ;
call proc10(10);
实例3：while + iterate
-- -------存储过程-while+iterate
truncate table user;
delimiter $$
create procedure proc11(in insertcount int)
begin
declare i int default 1;
label:while i<=insertcount do
set i=i+1;
if i=5 then iterate label;
end if;
insert into user(uid,username,`password`) values(i,concat('user-',i),'123456');
end while label;
end $$
delimiter ;
call proc11(10);
repeat循环
[标签:]repeat
循环体;
until 条件表达式
end repeat [标签];
实例：
-- -------存储过程-循环控制-repeat
truncate table user;
delimiter $$
create procedure proc12(in insertCount int)
begin
declare i int default 1;
label:repeat
insert into user(uid, username, password) values(i,concat('user-',i),'123456');
set i = i + 1;
until i > insertCount
end repeat label;
select '循环结束';
end $$
delimiter ;
call proc12(100); ##循环到100结束
loop循环
[标签:] loop
循环体;
if 条件表达式 then
leave [标签];
end if;
end loop;
实例：
-- -------存储过程-循环控制-loop
truncate table user;
delimiter $$
create procedure proc13(in insertCount int)
begin
declare i int default 1;
label:loop
insert into user(uid, username, password) values(i,concat('user-',i),'123456');
set i = i + 1;
if i > 5
then
leave label;
end if;
end loop label;
select '循环结束';
end $$
delimiter ;
call proc13(10);
说明：loop相当于死循环，需要结合判断条件来结束进程。
– -------存储过程-循环控制-loop
truncate table user;
delimiter
c
r
e
a
t
e
p
r
o
c
e
d
u
r
e
p
r
o
c
13
(
i
n
i
n
s
e
r
t
C
o
u
n
t
i
n
t
)
b
e
g
i
n
d
e
c
l
a
r
e
i
i
n
t
d
e
f
a
u
l
t
1
;
l
a
b
e
l
:
l
o
o
p
i
n
s
e
r
t
i
n
t
o
u
s
e
r
(
u
i
d
,
u
s
e
r
n
a
m
e
,
p
a
s
s
w
o
r
d
)
v
a
l
u
e
s
(
i
,
c
o
n
c
a
t
(
′
u
s
e
r
−
′
,
i
)
,
′
12345
6
′
)
;
s
e
t
i
=
i
+
1
;
i
f
i
>
5
t
h
e
n
l
e
a
v
e
l
a
b
e
l
;
e
n
d
i
f
;
e
n
d
l
o
o
p
l
a
b
e
l
;
s
e
l
e
c
t
′
循环结
束
′
;
e
n
d
create procedure proc13(in insertCount int) begin declare i int default 1; label:loop insert into user(uid, username, password) values(i,concat('user-',i),'123456'); set i = i + 1; if i > 5 then leave label; end if; end loop label; select '循环结束'; end
createprocedureproc13(ininsertCountint)begindeclareiintdefault1;label:loopinsertintouser(uid,username,password)values(i,concat(′user−′,i),′123456′);seti=i+1;ifi>5thenleavelabel;endif;endlooplabel;select′循环结束′;end
delimiter ;
call proc13(10);
``
说明：loop相当于死循环，需要结合判断条件来结束进程。