一、Mysql存储过程概述
存储过程是数据库的一个重要对象（对象还包括，索引，触发器，视图等），可以封装sql语句集，用来完成比较复杂的业务逻辑，并且还可以入参，出参。
存储过程创建时会进行预编译进行保存，当下次调用时不需要再进行编译。
优点：
在生产环境下，可以通过直接修改存储过程的方式修改业务逻辑（或bug），而不用重启服务器。
执行速度快，存储过程经过编译之后会比单独一条一条执行要快。
减少网络传输流量。
方便优化。
缺点：
过程化编程，复杂业务处理的维护成本高。
调试不便
不同数据库之间可移植性差。-- 不同数据库语法不一致！
二、语法结构
一、delimiter
声明结束符；
delimiter //
//：代表的是结束标志，这么做的目的主要是使结束标志与；进行分开，原因是存储过程中可能会有其他的一些操作语句需要用到；，但是存储过程又还没有执行完，所以就需要特别定义一个结束标志。否则存储过程在执行过程中就会认为；是结束标志，然后结束存储过程，可能会导致出错或后面操作无法执行完。
二、 语法体结构
create procedure 名称([proc_parameter[....,.....,.....]])
[characteristic ...] routine_body
end （delimiter 声明的结束标识符）
-- proc_parameter参数部分，可以如下书写：
[ IN | OUT | INOUT ] param_name type
-- type类型可以是MySQL支持的所有类型
-- routine_body(程序体)部分，可以书写合法的SQL语句 BEGIN ... END
栗子:
-- 声明结束符。因为MySQL默认使用‘;’作为结束符，而在存储过程中，会使用‘;’作为一段语句的结束，导致‘;’使用冲突
delimiter $$
create procedure select_demo()
begin
select 'hello';
end $$
-- 调用存储过程 单独调用使用；，如果是接着上面的话，就用$$
call select_demo();
一、变量以及赋值
一、局部变量：用户自定义，在begin/end中有效
声明变量语法：declare var_name type [default var_value];
栗子:declare name varchar(32);
1.使用set进行赋值
delimiter $$
create procedure demo()
begin
declare name varchar(32) default 'tom';
set name = 'bob';
select name;
end $$
2.使用into赋值
delimiter $$
create procedure demo()
begin
declare tp_name varchar(32) default 'tom';
declare tp_age int default 12;
select e.age,e.name into tp_age,tp_name from emp e where e.no = 9527;
select tp_name,tp_age;
end $$
二、用户变量
用户自定义变量，当前会话【连接】有效。
栗子：
delimiter $$
create procedure demo()
begin
declare name varchar(32) default 'tom';
set @name = 'bob';// 直接使用就行
select name;
end $$
三、入参、出参
语法：in | out | inout param_name type
栗子：
-- in 栗子
delimiter $$
create procedure demo(in name varchar(32))
begin
set @name = name;
end $$
-- out栗子
delimiter $$
create procedure demo(in name varchar(32),out age int(11))
begin
select d.age into age from dept d where d.name = name;
// 注意此处，要么表起别名，要么就是入参和字段名不能一致
end $$
--测试
set @dept_no = 100;
call sp_param01('DALLAS',@dept_no);
select @dept_no;
-- INOUT类型
delimiter $$
create procedure demo(inout name varchar(32))
begin
set name = concat('hello',name);
end $$
--测试
set @name = 'world';
call demo(@name);
select @name;
二、流程控制
1、if
语法：
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF
栗子：
-- 需求：入职年限<=3是新手 >3并且<=6老员工 >6元老
-- timestampdiff函数：timestampdiff(unit,exp1,exp2) 取差值exp2-exp1差值，单位是unit
delimiter $$
create procedure demo()
begin
declare result varchar(32);
declare years int;
select timestampdiff(year,e.datetime,now()) into years from emp e where e.no = 9527;
if years >6 then set result = '元老'
else if
years >3 and years <= 3 then set result ='老员工'
else
set result ='新员工'
end if;
select result; // 查询一下结果
end $$
2、case
此语法是不仅可以用在存储过程，查询语句也可以用！
-- 语法一（类比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
栗子：
-- 需求：入职年限<=3是新手 >3并且<=6老员工 >6元老
-- timestampdiff函数：timestampdiff(unit,exp1,exp2) 取差值exp2-exp1差值，单位是unit
delimiter $$
create procedure demo()
begin
declare result varchar(32);
declare years int;
select timestampdiff(year,e.datetime,now()) into years from emp e where e.no = 9527;
case
when years >6 then set result = '元老'
when years >3 and years <= 3 then set result ='老员工'
else
set result ='新员工'
end case;
select result; // 查询一下结果
end $$
3、循环控制
loop：
-- LOOP语法
[begin_label:] LOOP
statement_list
END LOOP [end_label]
栗子：
需要说明，loop是死循环，需要手动退出循环，我们可以使用leave来退出。
可以把leave看成我们java中的break；与之对应的，就有iterate（继续循环）——类比java的continue
-- 需求：循环打印1到10
--leave控制循环退出
delimiter $$
create procedure sp_flow_loop()
begin
declare c_index int default 1;
declare result_str varchar(256) default '1';
cnt:loop
if c_index >= 10
then leave cnt;
end if;
set c_index = c_index + 1;
set result_str = concat(result_str,',',c_index);
end loop cnt;
select result_str;
end$$
--leave + iterate控制循环退出
delimiter $$
create procedure demo()
begin
declare index int(11) default 1;
declare result varchar(32) default '1';
nums:loop
set index = index + 1;
set result = concat(result,',',index);
if index < 10
then
iterate nums;
end if;
leave nums;
end loop nums;
select result;
end $$
repeat ：类似于do------while();
语法结构:
[begin_label:] REPEAT
statement_list
UNTIL search_condition -- 直到…为止，才退出循环
END REPEAT [end_label]
栗子：
delimiter $$
create procedure demo()
begin
declare index int(11) default 1;
declare result varchar(32) default '1';
nums repeat:
index = index + 1;
result = concat(result,',',index);
until index > 10;
end repeat nums;
select result;
end $$
while：相当于java的while(){}语句
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
栗子：
delimiter $$
create procedure demo()
begin
declare index int(11) default 1;
declare result varchar default '1';
while index <10 do
set index = index + 1;
set result = concat(result,',',index);
end while;
select result;
end $$
三、游标
用游标的到一个结果集，然后进行逐行处理
-- 声明语法
DECLARE cursor_name CURSOR FOR select_statement
-- 打开语法
OPEN cursor_name
-- 取值语法
FETCH cursor_name INTO var_name [, var_name] ...
-- 关闭语法
CLOSE cursor_name
栗子：
delimiter $$
create procedure demo(in dept_name vachar(32))
begin
declare e_no int;
declare e_name varchar(32);
declare e_sal decimal(7,2);
declare flag boolean default true;
-- 声明游标
declare emp_curesor cursor for
select e.empno,e.ename,e.sal
from emp e,dept d
where e.deptno = d.deptno and d.dname = dept_name;
--声明句柄，主要是用于进行数据逐行遍历的时候，遍历到最后，没有数据的时候返回报错的一个判断
--这里使用continue(表示：如果数据遍历完了，就退出循环，执行循环外的代码)，如果使用exits(则不会执行循环外的代码),
declare continue handler for NOT FOUND set flae = false;
-- 打开语法
open emp_curesor;
-- 取值
emp_loop:loop
if flag then
select e.empno,e.ename,e.sal
else
leave emp_loop;
end if;
end loop emp_loop;
-- 设置标志，看是否执行到这里
set @end_flag = 'end_flag';
end $$
call demo('RESEARCH');
特别注意：
在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。
四、handler句柄
语法：
DECLARE handler_action HANDLER
FOR condition_value [, condition_value] ...
statement
handler_action: {
CONTINUE
| EXIT
| UNDO
}
condition_value: {
mysql_error_code
| SQLSTATE [VALUE] sqlstate_value
| condition_name
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION
}
mysql数据库报错信息：
CONTINUE: Execution of the current program continues.
EXIT: Execution terminates for the BEGIN ... END compound statement in which the handler is declared. This is true even if the condition occurs in an inner block.
SQLWARNING: Shorthand for the class of SQLSTATE values that begin with '01'.
NOT FOUND: Shorthand for the class of SQLSTATE values that begin with '02'.
SQLEXCEPTION: Shorthand for the class of SQLSTATE values that do not begin with '00', '01', or '02'.
-- 各种写法：
DECLARE exit HANDLER FOR SQLSTATE '42S01' set @res_table = 'EXISTS';
DECLARE continue HANDLER FOR 1050 set @res_table = 'EXISTS';
DECLARE continue HANDLER FOR not found set @res_table = 'EXISTS';
