MySQL存储过程
存储过程是数据库的一个重要对象，可以封装SQL，完成一些复杂的业务逻辑。使用存储过程，最主要的特点是执行速度快，其经过编译后会比一条一条单独执行快，也能避免频繁的连接而消耗资源。
语法结构
创建的语法结构
create
[definer = user] -- 用来指定用户的执行权限，默认所有用户可用；
procedure procedure_name([proc_parameter[,...]]) -- procedure_name存储过程的名字；
-- proc_parameter参数，类型有in（入参）、out（出参）、inout（入参和出参）；
body -- body中写程序体，只要是合法的sql即可。以begin开始，以end结束，必须成对出现，通常一对即可。
call 调用存储过程
call procedure_name -- 使用call关键字调用存储过程
实例：创建存储过程并调用
-- 创建存储过程
create procedure proc_test()
begin
select now() from dual; -- 查询当前时间
end;
-- 调用存储过程
call proc_test(); -- 执行结果是当前时间
删除的语法结构
drop procedure procedure_name -- 删除时指定存储过程名称即可，不能加括号。
实例：删除存储过程
drop procedure proc_test;
变量及赋值
局部变量
用户自定义的变量，尽在begin/end块中有效。
-- 使用declare关键字声明变量名和类型，可指定默认值。 [] 表示可选参数
declare var_name type [default var_value];
使用set赋值
给变量赋值，可以使用set和into关键字，对所有变量都适用。
create procedure proc_var01()
begin
declare user_name varchar(50);#直接声明，无默认值
declare age int(11) default 20;#声明的同时指定默认值
set user_name = '张三';#赋值
select user_name,age; #查询
end;
-- 调用存储过程
call proc_var01
若有多个set对其赋值，则最终值是最后一个set的结果。
使用into赋值
create procedure proc_var02()
begin
declare create_time datetime;
select now() into create_time;
select create_time;
end;
-- 调用存储过程
call proc_var02
若在select中使用into给多个变量赋值，则使用逗号分隔即可，但前面的值和后面的变量必须一一对应。
用户变量
用户自定义的变量，在当前会话有效。
@var_name #无需提前声明，使用即声明
赋值
create procedure proc_var03()begin set @create_time = now(); # 并未声明就直接赋值，但变量名前面必须带@符号，表示它是用户变量。 select @create_time;end;-- 调用存储过程 call proc_var03
会话变量与全局变量
会话变量和全局变量都是系统提供的变量。
会话变量：当前会话有效。由于用的不多，在此略。
全局变量：整个MySQL服务有效。由于用的不多，在此略。
入参和出参
in | out | inout param_name type # in（入参）、out（出参）、inout（入参和出参） typ参数的类型
入参
create procedure proc_var04(in name varchar(50))begin select name;end;-- 调用存储过程 传入名字进行查询：call proc_var04('root');
出参
create procedure proc_var05(in phone varchar(11),out phone_suffix varchar(4))begin set phone_suffix = right(phone,4); -- 截取后4位：end;-- 调用存储过程 传入手机号，call proc_var05('15623524651',@suffix);-- 执行结果是4651。需要注意的是，在接收输出参数时，必须使用用户变量，否则接收不到，调用时还会出错。select @suffix;
入参和出参
create procedure proc_var06(inout name varchar(100))begin set name = concat('hello,',name); #将hello拼接用户名end;-- 设置传入的参数set @username='张三';-- 调用存储过程 传入参数用户名call proc_var06(@username);-- 查询用户名select @username;
判断（流程控制）
if
if判断有if、if-else、if-elseif-else等。其条件在判断等于时用一个等号，条件后使用then，且以 if 开头 end if 成对结尾。
仅有if
create procedure proc_var07(in age int)begin declare msg varchar(20) default ''; if age = 20 then -- 满足条件进入then set msg = '年龄为20'; end if; -- 表示if 结束了 select msg;end;-- 调用存储过程 传入参数用户名call proc_var07(20);
if-else
create procedure proc_var08(in age int)begin declare msg varchar(20) default ''; if age = 20 then -- 满足条件进入then set msg = '年龄为20'; else -- 不满足条件进入else set msg = '年龄不为20'; end if; -- 表示if 结束了 select msg;end;-- 调用存储过程 传入参数用户名call proc_var07(21);
if-elseif-else
create procedure proc_var09(in age int)begin declare msg varchar(20) default ''; if age > 20 then -- 满足条件进入then set msg = '年龄大于20'; elseif age < 20 then -- 满足条件进入then set msg = '年龄小于20'; else -- 不满足条件进入else set msg = '年龄等于20'; end if; -- 表示if 结束了 select msg;end;-- 调用存储过程 传入参数用户名call proc_var09(18);
case分支
case的语法有两种
第一种：值匹配
create procedure proc_var10(in season int)begin declare msg varchar(20) default ''; case season -- 值匹配 season为3 就进入3这个分支 when 1 then set msg = 'spring'; when 2 then set msg = 'summer'; when 3 then set msg = 'autumn'; else set msg = 'winter'; end case; select msg;end;-- 调用存储过程 传入3 执行的结果是autumncall proc_var10(3);
第二种：表达式和if一样，哪个满足条件进入哪个
create procedure proc_var10(in age int)begin declare msg varchar(20) default ''; case when age > 20 then set msg = '年龄大于20'; when age < 20 then set msg = '年龄小于20'; else set msg = '年龄等于20'; end case; select msg;end;-- 调用存储过程 传入18 执行的结果是年龄小于20call proc_var10(18);
循环（流程控制）
**leave：**用于结束循环。
iterate：用于结束本次循环，进入下一次循环。
loop循环
[begin_label:] loop statement_listend loop [begin_label]
begin_label是给该循环起个别名，当多个loop嵌套时结束循环需根据别名。另外loop是死循环，必须使用leave结束循环.
实例： 打印1到10：
create procedure proc_var11(in count int )begin declare msg varchar(200) default '1'; declare curr int default 1; cnt:loop -- cnt是loop循环的别名 if curr >= count then leave cnt; -- 当curr>10时，结束cnt循环 end if; set curr = curr + 1; set msg = concat(msg,',',curr); end loop cnt; select msg;end;-- 调用存储过程 传入10 执行的结果是打印1到10call proc_var11(10);
repeat循环
[begin_label:] repeat statement_listuntil conditionend repeat [begin_label]
当until后面的条件符合时结束循环，否则会一直循环。
实例： 打印1到10：
create procedure proc_var12(in count int)begin declare msg varchar(200) default '1'; declare curr int default 1; cnt:repeat set curr = curr + 1; set msg = concat(msg,',',curr); until curr >= count #符合条件结束循环 ，注意until后不能加分号。 end repeat cnt; select msg;end;-- 调用存储过程 传入10 执行的结果是打印1到10call proc_var12(10);
注意：until后不能加分号。
while循环
[begin_label:] while condition do statement_listend while [begin_label]
实例： 打印1到10：
create procedure proc_var13(in count int)begin declare msg varchar(100) default ''; declare curr int default 1; cnt:while curr<=count do -- 符合条件进入循环体，不符合条件退出循环 set msg = concat(msg,',',curr); set curr = curr + 1; end while; select msg;end;-- 调用存储过程 传入10 执行的结果是打印1到10call proc_var13(10);
游标
数据准备
# 创建员工表empCREATE TABLE `emp` ( `id` int(11) NOT NULL, `name` varchar(200) DEFAULT NULL COMMENT '姓名', `dept_no` varchar(32) DEFAULT NULL COMMENT '部门编号', `sal` decimal(10,2) DEFAULT NULL COMMENT '工资', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;# 创建部门表deptCREATE TABLE `dept` ( `dept_no` varchar(32) NOT NULL COMMENT '部门编号', `dept_name` varchar(200) DEFAULT NULL COMMENT '部门名称', PRIMARY KEY (`dept_no`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;#添加数据INSERT INTO `dept`(`dept_no`, `dept_name`) VALUES ('A001', '策划部');INSERT INTO `dept`(`dept_no`, `dept_name`) VALUES ('A002', '开发部');INSERT INTO `dept`(`dept_no`, `dept_name`) VALUES ('A003', '人事部');INSERT INTO `emp`(`id`, `name`, `dept_no`, `sal`) VALUES (1, '张飒', 'A002', 5000.00);INSERT INTO `emp`(`id`, `name`, `dept_no`, `sal`) VALUES (2, '李敏', 'A003', 4500.00);INSERT INTO `emp`(`id`, `name`, `dept_no`, `sal`) VALUES (3, '赵虹', 'A001', 4350.00);INSERT INTO `emp`(`id`, `name`, `dept_no`, `sal`) VALUES (4, '赵敏敏', 'A002', 6530.00);INSERT INTO `emp`(`id`, `name`, `dept_no`, `sal`) VALUES (5, '孙慧', 'A002', 7150.00);
游标语法
游标的作用主要是遍历查询的结果集。故在声明游标时for后面就是select的查询语句。
#声明游标declare cursor_name cursor for select_statement#声明句柄declare continue handler for 1329 set finished = 1;#打开游标open cursor_name #遍历取值 使用fetch遍历，当结果集遍历到最后一条时，它会报错，错误码是1329，错误状态是02000，需要用Handler处理异常fetch cursor_name into var_name ...#使用Handler（句柄）来判断异常进行处理。if finished = 1 then 具体处理#关闭游标，使用完后需关闭close cursor_name
实例： 给开发部的员工涨工资的存储过程：
create procedure proc_var14(in name varchar(200),in add_sal decimal)begin declare user_id int; declare finished int default 0; #声明游标 declare cur_emp cursor for select id from emp where dept_no =(select dept_no from dept where dept_name =name) ; #声明句柄 declare continue handler for 1329 set finished = 1; #打开游标 open cur_emp; emp_loop:loop #遍历取值 fetch cur_emp into user_id; #使用Handler（句柄）来判断异常进行处理。 if finished = 1 then leave emp_loop; #退出循环 else update emp e set sal = e.sal + add_sal where e.id = user_id; #开发部的员工涨工资 end if; end loop emp_loop; #关闭游标，使用完后需关闭 close cur_emp;end;-- 调用存储过程 给开发部的员工1000涨工资call proc_var14('开发部',1000);
声明句柄的注意事项：
除了使用错误码外，还可以使用错误状态，声明如下，二选一即可：
declare continue handler for sqlstate '02000' set finished = 1;