Mysql变量、游标及存储过程的应用-木庄网络博客

本文摘自PHP中文网，作者齐天大圣，侵删。

Mysql自5.0起就支持存储过程，存储过程通俗的讲就是在一段封装过的SQL，但不仅仅只有SQL那么简单，通常还会有变量、条件判断、循环体，游标等。

存储过程的作用

在很多场景中，需要将多个表的数据处理，来产生新的我们需要的数据。这些多个表的数据并不能通过连接等查询方式给出，只能通过判断和循环才能产生。这个时候，就可以利用存储过程来实现。

此外，存储过程还有一些好处，比如性能比较高，还有能减少网络请求。如果不用存储过程来实现的话，使用php来实现就需要调用多次mysql,产生多次请求。

当然，存储过程也不是没有缺点的，它比较哪调式，另外不支持集群。

创建存储过程

创建存储过程语法如下：

CREATE PROCEDURE 过程名(参数) 
BEGIN
 过程体
END

关于参数，设置参数语法为

[IN|OUT|INOUT] 参数名类型

IN 表示该变量只能在过程体内使用
OUT 表示该变量只能在过程体外使用
INOUT 表示在过程体内和体外都能使用

下面，我们来创建一个最简单的存储过程。

CREATE PROCEDURE p1(IN x INT) 
BEGIN
  SELECT x;
END;

变量

在MySQL中变量分为全局变量和局部变量。

全局变量以@开头，无需声明，直接使用即可,如

1	`SET` `@name='gwx';`

局部变量需要先声明，局部变量的初始化方法如下：

1	`DECLARE` `x` `int` `DEFAULT` `0;`

下面我们来完成一个存储过程：根据路程计算车费,3公里内按6远计算，超过的距离按每公里1.2元计算.

-- distance 路程
CREATE PROCEDURE p1(in distance FLOAT)
BEGIN
  DECLARE d_money FLOAT DEFAULT 0; 
    IF distance>3 THEN
       SET d_money=6+(distance-3)*1.2;
    ELSE
      SET d_money=6;
    END IF;
    SELECT d_money;
END;

游标

拿php做比较，游标有点想foreach，每次循环获取一条记录。

定义一个游标：

1	`declare` `游标名` `CURSOR` `FOR` `SELECT` `语句`

开启关闭游标:

open 游标名
close 游标名

取游标数据:

1	`FETCH` `游标名` `INTO` `变量名`

可以这么简单的介绍，大家会有疑惑，不清楚应该如何去使用。下面，来看一个实例，从实例中学习如何使用游标。

用游标完成一个非常简单的功能，将test_cursor表中数字全部累加起来。

CREATE TABLE IF NOT EXISTS test_cursor(
  num1 INT(10) UNSIGNED NOT NULL DEFAULT 0,
  num2 INT(10) UNSIGNED NOT NULL DEFAULT 0
);    
INSERT INTO test_cursor(num1,num2) VALUES(1,1),(2,2),(3,3);
  
CREATE PROCEDURE `test_cursor`()
BEGIN
    DECLARE sum INT(10) DEFAULT 0;
    DECLARE n1,n2 INT(10);
    DECLARE done INT DEFAULT 0;
     
    DECLARE cur CURSOR FOR SELECT num1,num2 FROM test_cursor; 
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; 
     
    OPEN cur; -- 打开游标
    WHILE done=0 DO      
        FETCH cur INTO n1,n2;
         
        IF done=0 THEN -- 注意这里为什么加IF条件，不加的话，最后一个值会被多加一遍         
      SET sum=sum+n1+n2;
        END IF;
    END WHILE;
    CLOSE cur;  -- 关闭游标
     
    SELECT sum;
END