存储过程

创建存储过程

delimiter $$
create [DEFINER=`用户名`] procedure sp_name( in|out|inout 字段 约束)
begin:
  ...
end $$
delimiter ;

存储过程参数类型

• in 输入参数：表示输入值，可以是形参或变量

  mysql> delimiter $$
  mysql> create procedure in_param(in p_in int)
      -> begin
      -> 　　select p_in;   # 1
      -> 　　set p_in=2;
      ->    select P_in;    # 2
      -> end$$
  mysql> delimiter ;
  mysql> set @p_in=1;
  mysql> call in_param(@p_in);
  # @p_in=1
  

• out 输出参数：表示输出值，必须传入变量

  mysql> delimiter //
  mysql> create procedure out_param(out p_out int)
      ->   begin
      ->     select p_out;     # 1
      ->     set p_out=2;
      ->     select p_out;    # 2
      ->   end
      -> //
  mysql> delimiter ;
  mysql> set @p_out=1;
  mysql> call out_param(@p_out);
  # @p_in=2
  

• inout 输入输出参数：即表示传入值也表示输出值，必须是变量

  mysql> delimiter $$
  mysql> create procedure inout_param(inout p_inout int)
      ->   begin
      ->     select p_inout;  # 1
      ->     set p_inout=2;
      ->     select p_inout;    # 2
      ->   end
      -> $$
  mysql> delimiter ;
  mysql> set @p_inout=1;
  mysql> call inout_param(@p_inout);
  # @p_in=2
  

声明语句结束符

在存储过程内不能使用默认结束符。

delimiter $$
delimiter ;

变量定义

declare l_int int unsigned default 3000;

变量赋值

set @p_int=3;
select  'hello word' into @x

用户变量一般以@开头

每一个语句块都可以添加一个标签

label1: BEGIN
　　label2: BEGIN
　　　　label3: BEGIN
　　　　　　statements; 
　　　　END label3 ;
　　END label2;
END label1

执行存储过程

call sp_name(参数)

如果是 in 类型的参数，只需要传入形参即可，直接传值；

如果是 out或inout，需要传入变量，返回值通过变量获取。

查询存储过程

查询存储过程列表

show procedure status where db='hq_db';

select * from mysql.proc where db='hq_db';

查询存储过程的详细内容

show create procedure 数据库.存储过程;
show create procedure hq_db.clear_shopee_rate_data;

CREATE DEFINER=`by15161458383`@`%` PROCEDURE `clear_shopee_rate_data`()
BEGIN
  # 每月1号清理激活率表中的数据，因为数据积攒很大
  DELETE from hq_db.t_shopee_activation_rate_detailed WHERE RefreshTime<=DATE_SUB(CURDATE(), INTERVAL 3 MONTH);
	optimize table hq_db.t_shopee_activation_rate_detailed;

	DELETE from hq_db.t_shopee_activation_rate WHERE RefreshTime<=DATE_SUB(CURDATE(), INTERVAL 3 MONTH);
	optimize table hq_db.t_shopee_activation_rate;

	# Shopee 删除草稿箱已放到回收站的SPU
	DELETE FROM hq_db.t_shopee_publish_draft WHERE is_published = 4 and create_time< DATE_SUB(CURDATE(), INTERVAL 1 MONTH);

END

删除存储过程

drop procedure pd_name;

控制语句

1、if-then-else 条件语句

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc2(IN parameter int)  
     -> begin 
     -> declare var int;  
     -> set var=parameter+1;  
     -> if var=0 then 
     -> insert into t values(17);  
     -> end if;  
     
     -> if parameter=0 then 
     -> update t set s1=s1+1;  
     -> else 
     -> update t set s1=s1+2;  
     -> end if;  
     -> end;  
     -> //  
mysql > DELIMITER ;

2、case语句

case
    when var=0 then
        insert into t values(30);
    when var>0 then
    when var<0 then
    else
end case

3、where -- end where 循环语句

while 条件 do
    --循环体
endwhile

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc4()  
     -> begin 
     -> declare var int;  
     -> set var=0;  
     -> while var<6 do  
     -> insert into t values(var);  
     -> set var=var+1;  
     -> end while;  
     -> end;  
     -> //  
mysql > DELIMITER ;

4、repeat -- end repeat 循环语句

repeat
    --循环体
until 循环条件  
end repeat;

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc5 ()  
     -> begin   
     -> declare v int;  
     -> set v=0;  
     -> repeat  
     -> insert into t values(v);  
     -> set v=v+1;  
     -> until v>=5  
     -> end repeat;  
     -> end;  
     -> //  
mysql > DELIMITER ;

5、loop -- endloop 循环语句

leave 用于离开循环

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc6 ()  
     -> begin 
     -> declare v int;  
     -> set v=0;  
     -> LOOP_LABLE:loop  
     -> insert into t values(v);  
     -> set v=v+1;  
     -> if v >=5 then 
     -> leave LOOP_LABLE;  # 离开loop循环
     -> end if;  
     -> end loop;  
     -> end;  
     -> //  
mysql > DELIMITER ;

6、lables 标号

用于 begin 、 repeat while 、loop 语句前，可以用于跳出循环。

7、lterate 迭代

跳出本次循环，执行下一次循环

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc10 ()  
     -> begin 
     -> declare v int;  
     -> set v=0;  
     -> LOOP_LABLE:loop  
     -> if v=3 then   
     -> set v=v+1;  
     -> ITERATE LOOP_LABLE;  
     -> end if;  
     -> insert into t values(v);  
     -> set v=v+1;  
     -> if v>=5 then 
     -> leave LOOP_LABLE;  
     -> end if;  
     -> end loop;  
     -> end;  
     -> //  
mysql > DELIMITER ;