存储过程概念
MySQL 5.0 版本开始支持存储过程。 简单的说，存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法，类似Python中的函数； 存储过就是数据库 SQL 语言层面的代码封装与重用
入门案例
delimiter 自定义结束符号
create procedure 储存名([ in ,out ,inout ] 参数名 数据类型...)
begin
sql语句...
end 自定义的结束符合
delimiter ;
-- 调用存储过程
call 储存名();案例:
delimiter $$
create procedure proc01()
begin
select 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 定义参数
begin
select * 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))
begin
select * 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))
begin
select 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)
begin
select 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)
begin
select 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)
begin
declare 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))
begin
declare 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))
begin
case in_season
when '春季' 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_year
select sal,hiredate into var_sal , var_year from emp where ename = in_name;
case
when 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);