0.环境说明：
mysql版本：5.7
1.使用说明
存储过程是数据库的一个重要的对象，可以封装SQL语句集，可以用来完成一些较复杂的业务逻辑，并且可以入参出参(类似于java中的方法的书写)。
创建时会预先编译后保存，用户后续的调用都不需要再次编译。
// 把editUser类比成一个存储过程 public void editUser(User user,String username){ String a = "nihao"; user.setUsername(username); } main(){ User user = new User(); editUser(user,"张三"); user.getUseranme(); //java基础还记得不 } 大家可能会思考，用sql处理业务逻辑还要重新学，我用java来处理逻辑（比如循环判断、循环查询等）不行吗？那么，为什么还要用存储过程处理业务逻辑呢？
优点： 在生产环境下，可以通过直接修改存储过程的方式修改业务逻辑（或bug），而不用重启服务器。 执行速度快，存储过程经过编译之后会比单独一条一条执行要快。 减少网络传输流量。 方便优化。 缺点： 过程化编程，复杂业务处理的维护成本高。 调试不便 不同数据库之间可移植性差。-- 不同数据库语法不一致！ 2.准备：
数据库参阅资料中的sql脚本；
delimiter $$ --声明结束符
3.语法
-- 官方参考网址 https://dev.mysql.com/doc/refman/5.6/en/sql-statements.html https://dev.mysql.com/doc/refman/5.6/en/sql-compound-statements.html 3.0 语法结构
CREATE [DEFINER = user] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body -- proc_parameter参数部分，可以如下书写： [ IN | OUT | INOUT ] param_name type -- type类型可以是MySQL支持的所有类型 -- routine_body(程序体)部分，可以书写合法的SQL语句 BEGIN ... END 简单演示：
-- 声明结束符。因为MySQL默认使用‘;’作为结束符，而在存储过程中，会使用‘;’作为一段语句的结束，导致‘;’使用冲突 delimiter $$ CREATE PROCEDURE hello_procedure () BEGIN SELECT 'hello procedure'; END $$ call hello_procedure(); 3.1 变量及赋值
类比一下java中的局部变量和成员变量的声明和使用 局部变量：
用户自定义，在begin/end块中有效
语法： 声明变量 declare var_name type [default var_value]; 举例：declare nickname varchar(32); -- set赋值 create procedure sp_var01() begin declare nickname varchar(32) default 'unkown'; set nickname = 'ZS'; -- set nickname := 'SF'; select nickname; end$$ -- into赋值 delimiter $$ create procedure sp_var_into() begin declare emp_name varchar(32) default 'unkown' ; declare emp_no int default 0; select e.empno,e.ename into emp_no,emp_name from emp e where e.empno = 7839; select emp_no,emp_name; end$$ 用户变量：
用户自定义，当前会话（连接）有效。类比java的成员变量
语法： @var_name 不需要提前声明，使用即声明 -- 赋值 delimiter $$ create procedure sp_var02() begin set @nickname = 'ZS'; -- set nickname := 'SF'; end$$ call sp_var02() $$ select @nickname$$ --可以看到结果 会话变量：
由系统提供，当前会话（连接）有效
语法： @@session.var_name show session variables; -- 查看会话变量 select @@session.unique_checks; -- 查看某会话变量 set @@session.unique_checks = 0; --修改会话变量 全局变量：
由系统提供，整个mysql服务器有效
语法： @@global.var_name 举例
-- 查看全局变量中变量名有char的记录 show global variables like '%char%'; -- 查看全局变量character_set_client的值 select @@global.character_set_client; 3.2 入参出参
-- 语法 in | out | inout param_name type 举例
-- IN类型演示 delimiter $$ create procedure sp_param01(in age int) begin set @user_age = age; end$$ call sp_param01(10) $$ select @user_age$$ -- OUT类型，只负责输出！ -- 需求：输出传入的地址字符串对应的部门编号。 delimiter $$ create procedure sp_param02(in loc varchar(64),out dept_no int(11)) begin select d.deptno into dept_no from dept d where d.loc = loc; --此处强调，要么表起别名，要么入参名不与字段名一致 end$$ delimiter ; --测试 set @dept_no = 100; call sp_param01('DALLAS',@dept_no); select @dept_no; -- INOUT类型 delimiter $$ create procedure sp_param03(inout name varchar) begin set name = concat('hello' ,name); end$$ delimiter ; set @user_name = '小明'; call sp_param03(@user_name); select @user_name; 3.3 流程控制-判断
官网说明 https://dev.mysql.com/doc/refman/5.6/en/flow-control-statements.html if
-- 语法 IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] ... [ELSE statement_list] END IF 举例：
-- 前置知识点：timestampdiff(unit,exp1,exp2) 取差值exp2-exp1差值，单位是unit select timestampdiff(year,e.hiredate,now()) from emp e where e.empno = '7499'; -- 需求：入职年限<=38是新手 >38并且<=40老员工 >40元老 delimiter $$ create procedure sp_hire_if() begin declare result varchar(32); if timestampdiff(year,'2001-01-01',now()) > 40 then set result = '元老'; elseif timestampdiff(year,'2001-01-01',now()) > 38 then set result = '老员工'; else set result = '新手'; end if; select result; end$$ delimiter ; 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 举例：
-- 需求：入职年限年龄<=38是新手 >38并 <=40老员工 >40元老 delimiter $$ create procedure sp_hire_case() begin declare result varchar(32); declare message varchar(64); case when timestampdiff(year,'2001-01-01',now()) > 40 then set result = '元老'; set message = '老爷爷'; when timestampdiff(year,'2001-01-01',now()) > 38 then set result = '老员工'; set message = '油腻中年人'; else set result = '新手'; set message = '萌新'; end case; select result; end$$ delimiter ; 3.4 流程控制-循环
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$$ -- iterate + leave控制循环 delimiter $$ create procedure sp_flow_loop02() begin declare c_index int default 1; declare result_str varchar(256) default '1'; cnt:loop set c_index = c_index + 1; set result_str = concat(result_str,',',c_index); if c_index < 10 then iterate cnt; end if; -- 下面这句话能否执行到？什么时候执行到？ 当c_index < 10为false时执行 leave cnt; end loop cnt; select result_str; end$$ repeat
[begin_label:] REPEAT statement_list UNTIL search_condition -- 直到…为止，才退出循环 END REPEAT [end_label] -- 需求：循环打印1到10 delimiter $$ create procedure sp_flow_repeat() begin declare c_index int default 1; -- 收集结果字符串 declare result_str varchar(256) default '1'; count_lab:repeat set c_index = c_index + 1; set result_str = concat(result_str,',',c_index); until c_index >= 10 end repeat count_lab; select result_str; end$$ while
类比java的while(){} [begin_label:] WHILE search_condition DO statement_list END WHILE [end_label] -- 需求：循环打印1到10 delimiter $$ create procedure sp_flow_while() begin declare c_index int default 1; -- 收集结果字符串 declare result_str varchar(256) default '1'; while c_index < 10 do set c_index = c_index + 1; set result_str = concat(result_str,',',c_index); end while; select result_str; end$$ 3.5 流程控制-退出、继续循环
leave
类比java的breake -- 退出 LEAVE can be used within BEGIN ... END or loop constructs (LOOP, REPEAT, WHILE). LEAVE label iterate
类比java的continue -- 继续循环 ITERATE can appear only within LOOP, REPEAT, and WHILE statements ITERATE label 3.6 游标
用游标得到某一个结果集，逐行处理数据。
类比jdbc的ResultSet -- 声明语法 DECLARE cursor_name CURSOR FOR select_statement -- 打开语法 OPEN cursor_name -- 取值语法 FETCH cursor_name INTO var_name [, var_name] ... -- 关闭语法 CLOSE cursor_name -- 需求：按照部门名称查询员工，通过select查看员工的编号、姓名、薪资。（注意，此处仅仅演示游标用法） delimiter $$ create procedure sp_create_table02(in dept_name varchar(32)) begin declare e_no int; declare e_name varchar(32); declare e_sal decimal(7,2); declare lp_flag boolean default true; declare emp_cursor cursor for select e.empno,e.ename,e.sal from emp e,dept d where e.deptno = d.deptno and d.dname = dept_name; -- handler 句柄 declare continue handler for NOT FOUND set lp_flag = false; open emp_cursor; emp_loop:loop fetch emp_cursor into e_no,e_name,e_sal; if lp_flag then select e_no,e_name,e_sal; else leave emp_loop; end if; end loop emp_loop; set @end_falg = 'exit_flag'; close emp_cursor; end$$ call sp_create_table02('RESEARCH'); DROP PROCEDURE if EXISTS sp_update_create_time; delimiter $$ create procedure sp_update_create_time() BEGIN declare value_id bigint(20); declare lp_flag boolean default true; declare update_create_time_cursor cursor for SELECT commodity_discnt_item_value_id FROM td_co_discnt_item_value WHERE create_time IS NULL; declare continue handler for NOT FOUND set lp_flag = false; open update_create_time_cursor; update_create_time_loop:loop fetch update_create_time_cursor into value_id; if lp_flag then UPDATE td_co_discnt_item_value SET create_time =now() WHERE commodity_discnt_item_value_id =value_id; else leave update_create_time_loop; end if; end loop update_create_time_loop; set @end_falg = 'exit_flag'; close update_create_time_cursor; end$$ call sp_update_create_time(); 说明：以上存储过程目的为更新表中create_time为空的数据设置为当前时间
特别注意：
在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。
3.7 存储过程中的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 } 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'; 4.练习
——大家注意，存储过程的业务过程在java代码中一般也可以实现，我们下面的需求是为了练习存储过程
4.1 利用存储过程更新数据
为某部门(需指定)的人员涨薪100;如果是公司总裁，则不涨薪。 delimiter // create procedure high_sal(in dept_name varchar(32)) begin declare e_no int; declare e_name varchar(32); declare e_sal decimal(7,2); declare lp_flag boolean default true; declare emp_cursor cursor for select e.empno,e.ename,e.sal from emp e,dept d where e.deptno = d.deptno and d.dname = dept_name; -- handler 句柄 declare continue handler for NOT FOUND set lp_flag = false; open emp_cursor; emp_loop:loop fetch emp_cursor into e_no,e_name,e_sal; if lp_flag then if e_name = 'king' then iterate emp_loop; else update emp e set e.sal = e.sal + 100 where e.empno = e_no; end if; else leave emp_loop; end if; end loop emp_loop; set @end_falg = 'exit_flag'; close emp_cursor; end // call high_sal('ACCOUNTING'); 结语
欢迎关注微信公众号『码仔zonE』，专注于分享Java、云计算相关内容，包括SpringBoot、SpringCloud、微服务、Docker、Kubernetes、Python等领域相关技术干货，期待与您相遇！
