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.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

1586956047152

[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等领域相关技术干货,期待与您相遇!

商匡云商
Logo
注册新帐户
对比商品
  • 合计 (0)
对比
0
购物车