MySQL存储过程入门了解

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.htmlhttps://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差值，单位是unitselect 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_listEND 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_listUNTIL search_condition	-- 直到…为止，才退出循环END REPEAT [end_label]

-- 需求：循环打印1到10delimiter $$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_listEND WHILE [end_label]

-- 需求：循环打印1到10delimiter $$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 statementsITERATE 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()BEGINdeclare 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:loopfetch 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] ...    statementhandler_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等领域相关技术干货，期待与您相遇！