一：介绍
（1）定义
MySQL 5.0 版本开始支持存储过程；
简单来说，存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法；
存储过程就是数据库SQL语言层面的代码封装与重用；
（2）特性
有输入输出参数，可以声明变量，有if/else,case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能；
函数的普遍特性：模块化，封装，代码复用；
速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤；
二：格式
delimiter $$
create procedure proc01()
beginselect empno,ename from emp;
end $$
delimiter ;-- 调用存储过程
call proc01();三：变量定义
（1） 局部变量
use mydb1;
delimiter $$
create procedure proc02()
begindeclare var_name01 varchar(20) default'aaa';-- 声明/定义变量(默认值)set var_name01 = 'zhangsan';-- 给变量赋值select var_name01;-- 输出变量值
end $$ -- select var_name01;在此处无法使用，局部变量只在局部（begin和end之内）可以使用
delimiter ;-- 调用存储过程
call proc02();
（2）局部变量其他赋值方式
delimiter $$
create procedure proc03()
begindeclare my_ename varchar(20) ;set ename into my_ename from emp where empno = 1001;select my_ename;
end $$ delimiter ;-- 调用存储过程
call proc03();（3）用户变量
delimiter $$
create procedure proc04()
beginset @var_nam01 = 'beijing';select @var_nam01;
end $$ delimiter ;call proc04(); select @var_nam01;-- 在外部也可以使用(4)系统变量
<1>介绍
系统变量又分为全局变量与会话变量；
全局变量在MYSQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改；
会话变量在每次建立一个新的连接的时候，由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份，来做为会话变量；
也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的；
全局变量与会话变量的区别就在于，对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话（也就是当前的数据库连接）；
有些系统变量的值是可以利用语句来动态进行修改的，但是有些系统的值却是只读的，对于那些可以更改的系统变量，我们可以利用set语句进行更改
<2>全局变量
<3>会话变量
四：参数传递
(1)in
in表示传入的参数，可以传入数值或者变量，即使传入变量，并不会更改变量的值，可以内部更改，仅仅作用在函数范围内
-- 封装有参数的存储过程，传入员工编号，查找员工信息
delimiter $$
create procedure proc06(in empno int)
beginselect * from emp where emp.empno = empno;
end $$ delimiter ;call proc06(1001);
call proc06(1002); -- 封装有参数的存储过程，可以通过传入部门名和薪资，查询指定部门，并且薪资大于指定值的员工信息
delimiter $$
create procedure proc07(in param_dname varchar(20),in param_sal decimal(7,2))
beginselect * from dept a,emp b where a.depton = b.depton and a.dname = param_dname and b.sal > param_sal;
end $$ delimiter ;call proc07('学工部',20000);
call proc07('销售部',10000);
(2)out
out表示从存储过程内部传值给调用者
-- 封装有参数的存储过程，传入员工编号，传出员工信息
delimiter $$
create procedure proc08(in in_empno int,out out_ename varchar(50))
beginselect ename into out_ename from emp where empno = in_empno;
end $$ delimiter ;call proc08(1001,@o_ename);
select @o_ename;-- 封装有参数的存储过程，传入员工编号，返回员工名字和薪资
delimiter $$
create procedure proc09(in in_empno int,out out_ename varchar(50),out out_sal decimal(7,2))
beginselect ename,sal into out_ename,out_sal from emp where empno = in_empno;
end $$ delimiter ;call proc09(1001,@o_ename,@o_sal);
select @o_ename;
select @o_sal;(3)inout
inout表示从外部传入的参数经过修改后可以返回的变量，既可以使用传入变量的值也可以修改变量的值（即使函数执行完）
-- 传入一个数字，传出这个数字的10倍
delimiter $$
create procedure proc10(inout num int)
begin set num = num*10;
end $$
delimiter ;set @inout_num = 3;
call proc10(@inout_num);select @inout_num;-- 传入员工名，拼接部门号，传入薪资，求出年薪
-- 关羽--------> 30_关羽
delimiter $$
create procedure proc11(inout inout_ename varchar(50),inout inout_sal int)
beginselect concat_ws('_',deptno,ename) into inout_ename from emp where emp.ename = inout_ename;set inout_sal = inout_sal*12;
end $$delimiter ;set @inout_ename = '关羽';
set @inout_sal = 3000;call proc11(@inout_ename,@inout_sal);select @inout_ename;
select @inout_sal五：流程控制
（1）if判断
if语句包含多个条件判断，根据结果为true,false执行语句，与编程语言中的if,else,else if 语法类似，其语法格式如下
-- 案例一：判断成绩级别
delimiter $$
create procedure proc_12_if(in score int)
beginif score<60 then select '不及格';elseif score>=60 and score <80 then select '及格';elseif score>=80 and score <90 then select '良好';elseif score>=90 and score <=100 then select '优秀';else select '成绩错误';end if;
end $$
delimiter ;call proc_12_if(64);-- 案例二：输入员工名字，判断工资情况
/* sal<10000:试用薪资sal>=10000 and sal<20000:转正薪资sal>=20000:元老薪资
*/
delimiter $$
create procedure proc_13_if(in ename varchar(20))
begindeclare var_sal decimal(7,2);declare result varchar(20);select sal into var_sal from emp where ename = in_ename;if var_sal < 10000 then set result = '试用薪资';elseif var_sal < 20000 then set result = '转正薪资';else set result = '元老薪资';end if;
select result;
end $$
delimiter ;call proc_13_if('关羽');
(2)case
case是另一个条件判断的语句，类似于编程语言中的switch语法
-- 格式一
-- 支付方式判断
/* 1 微信支付2 支付宝支付3 银行卡支付4 其他方式支付
*/
use mydb1;
delimiter $$
create procedure proc_14_case(in pay_type int)
begincase pay_typewhen 1 then select '微信支付';when 2 then select '支付宝支付';when 3 then select '银行卡支付';else select '其他方式支付';end case;
end $$
delimiter ;call proc_14_case(2);-- 格式二
delimiter $$
create procedure proc_15_case(in score int)
begincasewhen score<60 then select '不及格';when score>=60 and score <80 then select '及格';when score>=80 and score <90 then select '良好';when score>=90 and score <=100 then select '优秀';else select '成绩错误';end case;
end $$
delimiter ;call proc_15_case(64);（3）循环
<1>while
-- 创建测试表
create table user(uid int primary key,username varchar(50),password varchar(50)
);-- 需求：向表中添加指定条数的数据-- 存储过程-循环-while
delimiter $$
create procedure proc_16_while(in insertcount int)
begin declare i int default 1;label:while i<=insertcount doinsert into user(uid,username,password) values (i,concat('user_',i),'123456');set i = i+1;end while label;
end $$
delimiter ;call proc_16_while(10);-- 存储过程-循环-while+leave
truncate table user;
-- 清空表
delimiter $$
create procedure proc_17_while_leave(in insertcount int)
begin declare i int default 1;label:while i<=insertcount doinsert into user(uid,username,password) values (i,concat('user_',i),'123456');if i=5 thenleave label;end if;set i = i+1;end while label;
end $$
delimiter ;call proc_17_while_leave(10);-- 存储过程-循环-while+iterate
truncate table user;
-- 清空表
delimiter $$
create procedure proc_17_while_iterate(in insertcount int)
begin declare i int default 0;label:while i<insertcount doset i = i+1;if i=5 theniterate label;end if;insert into user(uid,username,password) values (i,concat('user_',i),'123456');end while label;select '循环结束';
end $$
delimiter ;call proc_17_while_iterate(10);
<2>repeat
-- 存储过程-循环-repeat
truncate table user;
delimiter $$
create procedure proc_18_repeat(in insertcount int)
begin declare i int default 1;label:repeatinsert into user(uid,username,password) values (i,concat('user_',i),'123456');set i = i+1;until i>insertcountend repeat label;select '循环结束';
end $$
delimiter ;call proc_18_repeat(10);<3>loop
-- 存储过程-循环-loop
truncate table user;delimiter $$
create procedure proc_19_loop(in insertcount int)
begindeclare i int default 1;label:loopinsert into user(uid,username,password) values (i,concat('user_',i),'123456');set i = i+1;if i>insertcount thenleave label;end if;end loop label;
end $$
delimiter ;call proc_19_loop(10);(4)游标
游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用光标对结果进行循环的处理。光标的使用包括光标的声明,open,fetch,close。
-- 需求：输入一个部门名，查询该部门员工的编号，名字，薪资，将查询结果输出
delimiter $$
create procedure proc19_cursor(in in_dname varchar(50))
begin-- 定义局部变量declare var_empno int;declare var_ename varchar(50);declare var_sal decimal(7,2);-- 声明游标declare my_cursor cursor forselect empno,ename,salfrom dept a,emp bwhere a.deptno = b.deptno and a.dname = in_dname;-- 打开游标open my_cursor;-- 通过游标获取值(fetch一次，显示出一个值)label:loopfetch my_cursor into var_empno,var_ename,var_sal;select var_empno,var_ename,var_sal;end loop label;-- 关闭游标close my_cursor;
end $$
delimiter ;call proc19_cursor();
(5)异常处理
-- 需求：输入一个部门名，查询该部门员工的编号，名字，薪资，将查询结果输出
delimiter $$
create procedure proc21_cursor_handler(in in_dname varchar(50))
begin-- 定义局部变量declare var_empno int;declare var_ename varchar(50);declare var_sal decimal(7,2);-- 定义标记值declare flag int default 1;-- 声明游标declare my_cursor cursor forselect empno,ename,salfrom dept a,emp bwhere a.deptno = b.deptno and a.dname = in_dname;-- 定义句柄：定义异常的处理方式/*1：异常处理完之后程序该怎么执行continue：继续执行剩余代码exit：直接终止程序undo：不支持2：触发条件条件码：1329条件名：sqlwarningnot foundsqlexception3：异常触发后执行什么代码设置flag的值------>0 */declare continue handler for 1329 set flag = 0;-- 打开游标open my_cursor;-- 通过游标获取值(fetch一次，显示出一个值)label:loopfetch my_cursor into var_empno,var_ename,var_sal;-- 判断flag，如果flag的值为1，则执行，否则不执行if flag = 1 then select var_empno,var_ename,var_sal;elseleave label;end if;end loop label;-- 关闭游标close my_cursor;
end $$
delimiter ;call proc21_cursor_handler('销售部');
六：MySQL的存储函数
-- 允许创建函数权限信任
set global log_bin_trust_function_creators = TRUE;
-- 创建存储函数--没有参数
drop function if exists myfunc1_emp;
delimiter $$
create function myfunc1_emp() returns int
begin-- 定义局部变量declare cnt int default 0;select count(*) into cnt from emp;return cnt;
end $$
delimiter ;-- 调用存储函数
select myfunc1_emp();-- 创建存储函数--有输入参数
-- 需求：传入一个员工编号，返回员工的名字
drop function if exists myfunc2_emp;
delimiter $$
create function myfunc2_emp(in_empno int)returns varchar(50)
begin declare out_ename varchar(50);select ename into out_ename from emp where empno = in_empno;return out_ename;
end $$
delimiter ;-- 调用存储函数
select myfunc2_emp(1008);