mysql 存储过程小记

语法

• 创建存储过程

CREATE
    [DEFINER = { user | CURRENT_USER }]
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
 
proc_parameter:
    [ IN | OUT | INOUT ] param_name type
 
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
 
routine_body:
　　Valid SQL routine statement
 
[begin_label:] BEGIN
　　[statement_list]
　　　　……
END [end_label]

下面先看一个存储过程的例子：

DROP PROCEDURE IF EXISTS `SP_day_history_value_summary`;
delimiter ;;
CREATE DEFINER=`Qi`@`%` PROCEDURE `SP_day_history_value_summary`(IN calcdate_offset int)
    COMMENT '用户计算'
BEGIN

    DECLARE _uid varchar(100);
    DECLARE _value decimal(20, 3);

    BEGIN
      DECLARE _stop_flag INT DEFAULT 0;
      DECLARE _Cursor CURSOR FOR
        select u.uid                                                                                 as `uid`,
               sum(admob_value / 100.0) + sum(facebook_value / 100.0) + sum(bat_value / 100.0) + sum(mobvista_value / 100.0) +
               sum(applovin_value / 100.0) +
               sum(fyber_coins * fyber_ratio / 10000.0) + sum(pollfish_coins * pollfish_ratio / 10000.0) as `value`
        from users_active u
               inner join (select uid from users_active where act_time = date(ADDDATE(NOW(), INTERVAL calcdate_offset DAY))) a
                 on a.uid = u.uid
        group by u.uid;
      DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _stop_flag = 1;

      OPEN _Cursor;
      FETCH _Cursor
      INTO _uid, _value;

      WHILE _stop_flag <> 1 DO
        update users set value = _value where uid = _uid;

        FETCH _Cursor
        INTO _uid, _value;
      END WHILE;
      CLOSE _Cursor;
    END;


    CALL SP_LogCallDebug('end.');

  END;
;;
delimiter ;

我们先来一步一步讲解这个例子：

• delimiter ;;　　# 将语句的结束符号从分号;临时改为两个;;(可以是自定义)
• DEFINER=Qi`@`% # 用户为Qi，%代表允许来自任何ip的连接
• (IN calcdate_offset int) IN代表传入参数calcdate_offset
• COMMENT '用户计算' 注释
• DECLARE 声明变量
• 声明一个游标: declare 游标名称 CURSOR for table;(这里的table可以是你查询出来的任意集合)
• DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _stop_flag = 1; SELECT INTO 语句或 INSERT 语句的子查询的结果为空表。在搜索的 UPDATE 或 DELETE 语句内标识的行数为零。也就是在 FETCH 语句中引用的游标位置处于结果表最后一行之后，设置_stop_flag =1.
• 打开定义的游标:open 游标名称;
• 获得下一行数据:FETCH 游标名称 into field_1, field_2;
• 需要执行的语句
• 释放游标:CLOSE 游标名称;

传参IN OUT INOUT

• IN输入参数：表示调用者向过程传入值（传入值可以是字面量或变量），该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
• OUT输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量），该值可在存储过程内部被改变，并可返回
• INOUT输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量），调用时指定，并且可被改变和返回

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

mysql> set @p_in=1;

mysql> call in_param(@p_in);
+------+
| p_in |
+------+
|    1 |
+------+

+------+
| P_in |
+------+
|    2 |
+------+

mysql> select @p_in;
+-------+
| @p_in |
+-------+
|     1 |
+-------+

以上可以看出，p_in在存储过程中被修改，但并不影响@p_id的值，因为前者为局部变量、后者为全局变量。
。

变量

• 用户变量名一般以@开头，只对当前用户使用的客户端生效
• 在MySQL存储过程中，可以使用SELECT …INTO语句对变量进行赋值，如：

create procedure getMsg  
()  
Begin 
declare v_title varchar(30);  
declare v_content varchar(100);  
select title,content into v_title,v_content from news where artId=333;  
End  

• mysql declare和set定义变量，除了一个不加@和一个加@这个区别之外，还有以下区别：

1. declare用来定义局部变量
2. @用来定义会话变量