MySQL存储过程快速入门

MySQL存储过程快速入门
本文并不适合于对数据库存储过程一点都不了解的人学习，主要面对从其他数据库（Oracle, SQL Server）迁移过来，或者熟悉MySQL语法，只是第一次开始编写存储过程的朋友.

MySQL的存错过程就是一个符合代码块，存在于数据库服务器之中，可以相互调用，传递参数。假设需要在服务器上执行一个业务逻辑，或者定期执行的事件，一般我们写存储过程来实现。

第一步：如何快熟阅读MySQL的参考手册
看参考手册是学习MySQL的不法二门，可是如果不经指点，直接看5000多页的文档，也是很容易失去信心的。
我们需要理解的是：编写存储过程就是编写一个代码块，那么相关的手册应该在那介绍呢？如果直接找你需要的东西，我感觉有点困难，下面简要介绍一下与编写存储过程相关的章节：
（本文参考的版本是：MySQL 5.7 Reference Manual）
Chapter 21 Stored Programs and Views： 介绍了如何创建存储过程和函数的语法和定义，可是这里并没有多少实际有用的东西。
14.6 Compound-Statement Syntax：这里详细介绍了符合语句的语法，编写存储过程的大部分语法和需要了解的内容都可以在这里找到。控制语句，游标，变量在这个章节中。
14.5 Prepared SQL Statement Syntax：在存储过程中使用动态SQL时需要使用Prepared SQL语句。
掌握基本的存储过程就是看这三节就OK了。

---

第二步 理解编写存储过程中需要用到概念
存储过程主要优势在于语句块，可以写多条SQL，可以用变量，可以用条件判断和循环，下面我们一一介绍MySQL存储过程中是如何使用这些实现存储过程的编写的。
变量(Variables)
在MySQL存储过程中有几种变量，我们需要了解的是两种：
1. 局部变量（Local Variables），这与一般程序语言中的变量没有差异，需要声明类型后使用，可以在控制语句和SELECT INTO, FETCH INTO中使用局部变量。
2. @变量或会话变量（我自己取的名字，专家勿拍）：这个变量可以在MySQL的会话中使用，对于Prepare的Execute等语句中，必需使用@变量，不能使用局部变量（开始写的时候，是很容易出错的，而且MySQL报错也是很难看懂的）
这两种类型的变量在编写存储过程是，都是需要使用的，一般情况下使用@变量就可以了，可是在存储过程间传递参数是，需要使用局部变量的方式传递，所以需要在两钟类型之间切换。SET语句可以实现赋值。比如：

DECLARE a VARCHAR(10);
SET @a = a;
SELECT @a INTO a;

语句控制块
语句控制块比较简单，无非就是IF THEN, ELSE, ELSEIF, END IF实现条件控制。LOOP的控制和一般的语句不同，类型C语言中的goto实现。

DECLARE v_x NUMERIC(10)
SET v_x = 0;
xx_label: LOOP
    SET v_x = v_x + 1;
    IF v_x > 10 THEN
      LEAVE xx_label;
    END IF;
END LOOP;

游标
MySQL的游标语法基本是DECLARE声明游标，OPEN打开游标，FETCH获取游标值，CLOSE关闭游标，需要注意的是检查游标结束的方法的特殊性。直接看代码理解：

-- 代码实例，获取表t_pue_expr的三个值
-- 声明一个游标
DECLARE c_pue_expr CURSOR FOR SELECT roomid, totalpowerexpr, itpowerexpr FROM t_pue_expr;
-- 声明一个游标结束标志的处理器
DECLARE CONTINUE HANDLER FOR NOT FOUND SET pue_expr_done = TRUE;
-- 打开游标
OPEN c_pue_expr;
-- 开始循环
pue_expr_loop: LOOP
  -- 获取游标值
  FETCH c_pue_expr INTO v_deviceid, v_totalpowerexpr, v_itpowerexpr;  
  -- 判断游标是否结束  
  IF pue_expr_done THEN LEAVE pue_expr_loop; END IF;
END LOOP;

动态SQL
动态SQL需要使用Prepare准备SQL语句，Prepare语句中可以使用？代替后期需要运行的参数，如果需要把执行的结果输出，需要使用@变量接收变量，而且只能是单行记录的方式，目前还不支持返回游标。看代码：

-- 查询特定设备，特定指标的记录是否存在
PREPARE st_query_rtdata FROM 'select count(1) into @v_metriccnt from t_actual_data where deviceid= ? and metricid = (select metricid from t_metric where metriccode = ?)';
SET @v_deviceid = 1;
SET @metric_code_pue_value = 'pue.value';
SET @v_metriccnt = 0; -- 动态SQL的返回值
-- 执行动态SQL,可以多次执行
EXECUTE st_query_rtdata USING @v_deviceid, @metric_code_pue_value;
-- 释放动态SQL
DEALLOCATE PREPARE st_query_rtdata;

需要注意的是，动态SQL不能使用局部变量（Local Variables)，所以，如果变量是用局部变量保存的，一定需要使用SET来创建一个@变量，常量也需要使用@变量来保存，输出的变量也使用@变量输出。

---

自此，基本的存储过程介绍到这里，掌握了上述内容，编写一般的存储过程就没有多大的困难了。以上内容是我编写计算一个简单+/-表达式，并计算机房PUE数据用到的技术，也是用MySQL存储过程的处女作，希望和大家共同学习。