MySQL存储过程快速入门

亦凉 2022-07-15 10:41 178阅读 0赞

**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存储过程的处女作，希望和大家共同学习。

[MySQL 5.7 Reference Manual]: http://downloads.mysql.com/docs/refman-5.7-en.pdf