存储过程概念

MySQL 5.0 版本开始支持存储过程。
简单的说，存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法，类似Python中的函数；
存储过就是数据库 SQL 语言层面的代码封装与重用

入门案例

语法:

delimiter 自定义结束符号
create procedure 储存名([ in ,out ,inout ] 参数名 数据类型...)
beginsql语句...
end 自定义的结束符合
delimiter ;
​
-- 调用存储过程
call 储存名();

案例:

delimiter $$
create procedure proc01()
beginselect empno,ename from emp; 
end  $$
delimiter ;
​
-- 调用存储过程
call proc01();

驱动调整

变量定义

局部变量

变量可以在存储过程中临时存储查询的结果.最后汇总计算成为功能需要的结果.

案例:

-- todo 在存储过程中定义变量-----------------------------------
delimiter $$
create procedure proc02()
begin-- todo 定义变量declare name varchar(20) default 'tom';-- todo 修改变量的值set name = 'jerry';select name ;
end $$
delimiter ;
​
call proc02();
​
----------------------------------------------------------------
delimiter $$
create procedure proc03()
begin-- todo 定义变量declare var_name varchar(20) default 'tom';declare var_sal double;-- todo 使用select ... into 变量名  -- 修改变量的值select ename into var_name from emp where empno = 1001;select sal into var_sal from emp where empno = 1001;select var_name , var_sal;
end $$
delimiter ;
​
call proc03();

用户会话变量

用户会话变量可以在当次连接的全局生效.不同的存储过程都可以使用该变量.
注意: 用户连接断开后用户变量会消失.

-- todo 用户会话变量----------------------------------------
delimiter $$
create procedure proc04()
begin-- todo 使用用户会话变量set @var_name = 'hello';select @var_name;
end $$
delimiter ;
​
call proc04();
​
-- todo 在任意的地方直接查询用户变量.
select @var_name; -- todo 用户变量的生命周期高于局部变量
# select var_sal; -- todo 局部变量只能在存储过程中使用.

系统变量

系统变量是mysql服务器自己定义的变量.用户设置mysql服务器的功能.
​
会话变量在每次建立一个新的连接的时候，由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份。来做为会话变量。
也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的。
​
修改会话变量 : (本次用户连接的会话生效)----
修改全局变量 : (整个服务器生效)-----------

-- todo 系统变量--全局变量(整个服务器)------------
-- 查看全局变量
show global variables;
-- 查看某全局变量
select @@global.auto_increment_increment;
-- 修改全局变量的值
set global sort_buffer_size = 40000;
select @@global.sort_buffer_size;
select @@global.autocommit;-- 1表示自动提交
​
​
-- todo 系统变量--会话变量(本次用户连接的会话)----
-- 查看会话变量
show session variables;
-- 查看某会话变量
select @@session.auto_increment_increment;
-- 修改会话变量的值
set session sort_buffer_size = 50000;
set @@session.sort_buffer_size = 50000 ;
​
select @@session.sort_buffer_size;
select @@global.sort_buffer_size;

带参数存储过程

参数可以提高存储过程的灵活性和扩展性.
in:    输入入参
out:   输出参数
inout: 输入输出参数

in 参数

-- todo  带有 in 参数 的存储过程
-- 封装有参数的存储过程，传入员工编号，查找员工信息
delimiter $$
create procedure proc06(in in_empno varchar(20)) -- todo 定义参数
beginselect * from emp where empno = in_empno;-- todo使用参数
end $$
delimiter ;
​
call proc06('1001');-- todo 传递参数
call proc06('1002');
​
​
-- 封装有参数的存储过程，可以通过传入部门名和薪资，查询指定部门，并且薪资大于指定值的员工信息
delimiter $$
create procedure proc07(in in_deptno int,in in_sal decimal(7,2))
beginselect * from emp where deptno = in_deptno and sal > in_sal;
end $$
delimiter ;
​
call proc07(10,15000);

out参数

-- todo  带有 out 参数 的存储过程
-- 封装有参数的存储过程，传入员工编号，返回员工名字
delimiter $$
create procedure proc08(in in_empno int,out out_ename varchar(20))
beginselect ename into out_ename from emp where empno =  in_empno;
end $$
delimiter ;
​
call proc08(1001,@outname);
-- 检查 我们的 out参数中的值
select @outname;
​
-- 封装有参数的存储过程，传入员工编号，返回员工名字和薪资
​
delimiter $$
create procedure proc09(in in_empno int,out out_ename varchar(20),out out_sal double)
beginselect ename,sal into out_ename,out_sal from emp where empno =  in_empno;select '查询成功';
end $$
delimiter ;
​
call proc09(1001,@outname,@outsal);
select @outname,@outsal;

inout参数

-- 传入员工名，拼接部门号，传入薪资，求出年薪
delimiter $$
create procedure proc10(inout inout_ename varchar(20),inout inout_sal int)
beginselect concat_ws('_',deptno,inout_ename) into inout_ename from emp where ename = inout_ename;-- 查询部门并且拼接set inout_sal = inout_sal * 12; -- 计算年薪
end $$
delimiter ;
​
-- todo 给参数赋值
set @my_name = '甘宁';
set @imy_sal = 8000;
​
-- 调用存储过程
call proc10(@my_name,@imy_sal);
​
-- 查询参数值
select @my_name, @imy_sal ;

if判断结构

语法:

if search_condition_1 then statement_list_1[elseif search_condition_2 then statement_list_2] ...[else statement_list_n]
end if
​

案例:

-- todo if 判断
​
-- 输入学生的成绩，来判断成绩的级别：
delimiter $$
create procedure proc11(in in_score int)
begindeclare result varchar(20);if in_score < 60 then set result='不及格';elseif in_score < 80 then set result='及格';elseif in_score < 90 then set result='良好';elseif in_score <= 100 then set result='优秀';else set result='成绩错误';end if;select result;
end $$
delimiter ;
​
call proc11(90);
​
​
-- 输入员工的名字，判断工资的情况。
​
delimiter $$
create procedure proc12(in in_name varchar(20))
begindeclare var_sal int ;-- 定义局部变量(生命短暂-存储过程结束就挂)declare var_res varchar(20);select sal into var_sal from emp where ename = in_name;if var_sal < 10000 then set var_res = '试用薪资';elseif var_sal <30000 then set var_res = '转正薪资';else set var_res = '元老薪资';end if;if var_sal is null then set var_res = '没有薪资';end if;select in_name,var_sal,var_res;
end $$
delimiter ;
​
call proc12('甘宁');
call proc12('');

case when 结构

-- todo case when 判断结构
-- 语法一（类比java的switch）：
# 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
-- 语法一（类比java的switch）：
# case case_value
#     when when_value then statement_list
#     [when when_value then statement_list] ...
#     [else statement_list]
# end case
​
-- todo 需求:定义存储过程,传入季节,返回该季节对应的活动.
delimiter $$
create procedure proc13(in in_season varchar(20))
begincase in_seasonwhen '春季' then select '踏青....' as res;when '夏季' then select '漂流....' as res;when '秋季' then select '烧烤....' as res;when '冬季' then select '冬眠....' as res;else select '欢迎来到另一个世界....' as res;end case;
end $$
delimiter ;
​
call proc13('秋季');
​
​
-- 语法二：
# case
#     when search_condition then statement_list
#     [when search_condition then statement_list] ...
#     [else statement_list]
# end case
​
-- 根据用户名查询用户的入职日期.入职是2000年的薪资涨1000,入职是2001年的薪资涨500,其它涨200
​
delimiter $$
create procedure proc14(in in_name varchar(20))
begin-- 定义变量记录薪资declare var_sal int;declare var_year date;-- 把查询到的某个员工的薪资赋值给 var_sal ,入职日期赋值给var_yearselect sal,hiredate into var_sal , var_year from emp where ename = in_name;casewhen year(var_year) = 2000 then set var_sal = var_sal + 1000;when year(var_year) = 2001 then set var_sal = var_sal + 500;else set var_sal = var_sal + 200;end case ;select in_name,var_sal;
end $$
delimiter ;
​
call proc14('甘宁');
call proc14('张飞');
call proc14('诸葛亮');

while循环结构

-- 创建测试表
create table user (uid int primary key,username varchar ( 50 ),password varchar ( 50 )
);
​
-- 定义存储过程.实现可以制造任意条测试数据.
delimiter $$
create procedure proc15(in in_count int)
begin-- 使用循环,让插入数据的代码走多次.declare i int default 1;-- todo (1)定义循环初始变量while i <= in_count do -- todo (2)判断循环变量是否满足循环条件insert into user(uid,username,`password`) values(i,concat('user-',i),'123456');-- todo (3)循环体set i = i+1;-- todo (4)循环变量自增end while ;
end $$
delimiter ;
truncate user;
call proc15(10000);