什么是存储过程？
MySQL5.0版本开始支持存储过程。
简单地说，存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于java语言中的方法
存储过程就是数据库SQL语言层面的代码封装与复用
有哪些特性？
有输入输出参数，可以声明变量，有if/else，case，while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能
函数的普遍特性：模块化、封装、代码复用
速度快，只有首次执行需要经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤
格式
delimiter 自定义结束符号
create procedure 存储名([in,out,inout] 参数名 数据类型...)
begin
sql语句
end 自定义的结束符号
delimiter;
-- 定义存储过程
delimiter $$
create procedure proc01()
begin
select empno,ename from emp;
end $$
delimiter;
-- 调用存储过程
call proc01();变量定义
局部变量
用户自定义，在begin/end块中有效
语法：声明变量 declare var_name type [default var_value];
delimiter $$
create procedure proc02()
begin
declare var_name01 varchar(20) default 'aaa';-- 声明/定义变量
set var_name01='张三';-- 给变量赋值
select var_name01;-- 输出变量的值
end $$
delimiter;
call proc02();MySQL中还可以使用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();用户变量
用户自定义，当前会话（连接）有效。类比java的成员变量
语法：
@var_name
不需要提前声明，使用即声明
delimiter $$
create procedure proc04()
begin
set @var_name01='北京';
select @var_name01;
end $$
delimiter;
call proc04();
select @var_name01;-- 也可以使用用户变量系统变量
系统变量又分为全局变量与会话变量。
全局变量在MySQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改。
会话变量在每次建立一个新的连接的时候，由MySQL来初始化。MySQL会将当前所有全局变量的值复制一份，来作为会话变量。
也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的。
全局变量与会话变量的区别就在于，对全局变量的修改会影响到整个服务器，但是对会话变量的修改只会影响到当前的会话（也就是当前的数据库连接）。
有些系统变量的值是可以利用语句来动态进行更改的，但是有些系统变量的值却是只读的，对于那些可以更改的系统变量，我们可以利用set语句进行更改。
系统变量-全局变量
由系统提供，在整个数据库有效。
语法：
@@global.var_name
-- 查看全局变量
show global variables;
-- 查看某个全局变量的值
select @@global.auto_increment_increment;
-- 修改全局变量的值
set global sort_buffer_size=40000;
set @@global.sort_buffer_size=33000;
select @@global.sort_buffer_size;系统变量-会话变量
由系统提供，在当前会话（连接）有效。
语法：
@@session.var_name
-- 查看会话变量
show session variables;
-- 查看某会话变量
select @@session.auto_increment_increment;
-- 修改会话变量的值
set session sort_buffer_size=50000;
set @@session.sort_buffer_size=60000;
select @@session.sort_buffer_size;参数传递
存储过程传参-in
in表示传入的参数，可以传入数值或者变量，即使传入变量，并不会更改变量的值，可以内部更改，仅仅作用在函数范围内。
-- 封装有参数的存储过程，传入员工编号，查找员工信息
delimiter $$
create procedure proc06(in param_empno int)
begin
select * from emp where empno=param_empno;
end $$
delimiter;
call proc06(1002);
-- 封装有参数的存储过程，可以通过传入部门名和薪资，查询指定部门，并且薪资大于指定值的员工信息
delimiter $$
create procedure proc07(in param_dname varchar(50),in param_sal decimal(7,2))
begin
select * from dept a,emp b where a.deptno=b.deptno and a.dname=param_dname and b.sal>param_sal;
end $$
delimiter;
call proc07('学工部',20000)存储过程传参-out
out表示从存储过程内部传值给调用者
-- 封装有参数的存储过程，传入员工编号，返回员工名字和薪资
delimiter $$
create procedure proc09(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 proc09(1001,@o_ename,@o_sal);
select @o_ename;
select @o_sal;存储过程传参-inout
inout表示从外部传入的参数经过修改后可以返回的变量，既可以使用传入变量的值也可以修改变量的值（即使函数执行完）
-- 传入一个数字，传出这个数字的10倍值
delimiter $$
create procedure proc10(inout num int)
begin
set num=num*10;
end $$
delimiter;
set @inout_num=2;
call proc10(@inout_num)
select @inout_num;
-- 传入员工名，拼接部门号，传入薪资，求出年薪
delimiter $$
create procedure proc11(inout inout_ename varchar(50),inout inout_sal int)
begin
select 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;流程控制
流程控制-判断
格式：
if语句包含多个条件判断根据结果为true、false执行语句，与编程语言中的if、else if、else语法类似
if search_condition then statement_list_1
[elseif search_condition_2 then statement_list_2]...
[else statement_list_n]
end if
-- 输入学生的成绩，来判断成绩的级别
delimiter $$
create procedure proc_12_if(in score int)
begin
if 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;
set @score=65;
call proc_12_if(@score);
-- 输入员工的名字，判断工资的情况
delimiter $$
create procedure proc_13_if(in in_ename varchar(20))
begin
declare 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('关羽');流程控制-case
case是另一个条件判断语句，类似于编程语言中的switch语法
语法1
case case_value
when when_value then statement_list
[when when_value then statement_list]...
[else statement_list]
end case
delimiter $$
create procedure proc_14_case(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 proc_14_case(1);语法2
case
when search_condition then statement_list
[when search_condition then statement_list]...
[else statement_list]
end case
delimiter $$
create procedure proc_15_case(in score int)
begin
case
when 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;
set @score=65;
call proc_15_case(@score);流程控制-循环
循环是一段在程序中只出现一次，但可能会连续运行多次的代码。
循环中的代码会运行特定的次数，或者是运行到特定条件成立时结束循环。
循环分类：
while
repeat
loop
循环控制：
leave类似于break，跳出，结束当前所在循环
iterate类似于continue，继续，结束本次循环，继续下一次
流程控制-循环-while
格式：
[标签:]while 循环条件 do
循环体;
end while [标签];
-- 向表中添加指定条数的数据
-- while
delimiter $$
create procedure proc16_while(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 proc16_while(10);
-- while+leave
truncate table user;-- 清空数据
delimiter $$
create procedure proc17_while_leave(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;
select '循环结束';
end $$
delimiter;
call proc17_while_leave(10);
-- while+iterate
create table user2(
uid int,
username varchar(50),
password varchar(50)
);
truncate table user2;-- 清空数据
delimiter $$
create procedure proc18_while_iterate(in insertCount int)
begin
declare i int default 0;
label:while i< insertCount do
set i=i+1;
if i=5 then
iterate label;
end if;
insert into user2(uid,username,password) values(i,concat('user-',i),'123456');
end while label;
select '循环结束';
end $$
delimiter;
call proc18_while_iterate(10);流程控制-循环-repeat
格式：
[标签:]repeat
循环体;
until 条件表达式
end repeat[标签]
truncate table user;-- 清空数据
delimiter $$
create procedure proc19_repeat(in insertCount int)
begin
declare i int default 1;
repeat
insert into user(uid,username,password) values(i,concat('user-',i),'123456');
set i=i+1;
until i>insertCount
end repeat;
select '循环结束';
end $$
delimiter;
call proc19_repeat(10);流程控制-循环-loop
格式：
[标签:] loop
循环体;
if 条件表达式 then
leave [标签];
end if;
end loop;
truncate table user;-- 清空数据
delimiter $$
create procedure proc20_loop(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>insertCount
then
leave label;
end if;
end loop label;
end $$
delimiter;
call proc20_loop(10);游标
游标（cursor）是用来存储查询结果集的数据类型，在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、open、fetch和close。
声明
declare cursor_name cursor for select_statement
打开
open cursor_name
取值
fetch cursor_name into var_name[,var_name]...
关闭
close cursor_name
-- 输入一个部门名，查询该部门员工的编号、名字、薪资，将查询的结果集添加游标
delimiter $$
create procedure proc21_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 for
select empno,ename,sal
from dept a,emp b
where a.deptno=b.deptno and a.dname=in_dname;
-- 打开游标
open my_cursor;
-- 通过游标获取值
label:loop
fetch 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 proc21_cursor('销售部');
drop procedure proc21_cursor;异常处理-handler句柄
MySQL存储过程也提供了对异常处理的功能：通过定义handler来完成异常声明的实现。
格式：
declare handler_action handler
for condition_value [,condition_value]...
statement
handler_action:{
continue |exit |undo
}
condition_value:{
mysql_error_code | condition_name | sqlwarning | not found | sqlexception
}特别注意：在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。
delimiter $$
create procedure proc22_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 for
select empno,ename,sal
from dept a,emp b
where a.deptno=b.deptno and a.dname=in_dname;
-- 定义句柄：定义异常的处理方式
/*
1：异常处理完之后程序该怎么执行
continue:继续执行剩余代码
exit:直接终止程序
undo:不支持
2：触发条件
条件码：1329
条件名：
sqlwarning
not found
sqlexception
3：异常触发之后执行什么代码
设置flag的值 0
*/
declare continue handler for 1329 set flag=0;
-- 打开游标
open my_cursor;
-- 通过游标获取值
label:loop
fetch my_cursor into var_empno,var_ename,var_sal;
-- 判读flag，如果flag的值为1，则执行，否则不执行
if flag=1 then
select var_empno,var_ename,var_sal;
else
leave label;
end if;
end loop label;
-- 关闭游标
close my_cursor;
end $$;
delimiter;
call proc22_cursor_handler('销售部');
drop procedure proc22_cursor_handler;存储函数
在MySQL中，创建存储函数使用create function关键字，基本形式如下：
create function func_name([param_name type[,...]])
returns type
[characteristic ...]
begin
routine_body
end;
参数说明：
func_name：存储函数的名称
param_name type：可选项，指定存储函数的参数。type参数用于指定存储函数的参数类型，该类型可以使MySQL数据
returns type：指定返回值的类型。
characteristic：可选项，指定存储函数的特性。
routine_body：SQL代码内容。
-- 允许创建函数权限信任
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);
