MySQL中级：存储过程和游标

unijun

　　MySQL5添加了对存储过程和函数以及游标的支持，本文内容适用于MySQL5及以后的版本。
　　什么是存储过程？
　　一个存储过程是可以被存储在服务器中的一套SQL语句。一旦它被存储了，客户端不需要再重新发布单独的语句，而是可以引用存储过程来替代。
　　为什么要使用存储过程？

• 　　通过把处理封装在易于使用的单元中，简化复杂的操作，并可以多次调用。
  
• 　　由于不要求反复建立一系列处理步骤，这保证了数据的完整性。
  
• 　　简化对变动的管理。如果表名、列名或者业务逻辑发生变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。
  
• 　　安全性。
  
• 　　提高性能。因为只有较少的信息需要在服务器和客户算之间传送。代价是增加数据库服务器系统的负荷，因为更多的工作在服务器这边完成，更少的在客户端（应用程序）那边完成上。如果许多客户端机器（比如网页服务器）只由一个或少数几个数据库服务器提供服务，可以考虑一下存储程序。
  
• 　　存储过程重用性比较高，并且是透明的，因为保存在数据库里面所以对任何应用来说都可以使用。新的应用只需要调用相应的存储过程就可以得到相应的数据服务。
  

　　不足之处

• 　　存储过程会使得数据库占用的系统资源加大(cpu、memory)，数据库毕竟主要用来做数据存取的，并不进行复杂的业务逻辑操作。
  
• 　　因为存储过程依旧是sql，所以没办法像编程语言那样写出复杂业务逻辑对应的存储过程。
  
• 　　存储过程不容易进行调试。
  
• 　　存储过程书写及维护难度都比较大。
  

　　存储过程的语法
　　存储过程和函数是用CREATE PROCEDURE和CREATE FUNCTION语句创建的子程序。一个子程序要么是一个过程，要么是一个函数。使用CALL语句来调用过程，过程只能用输出变量传回值。就像别其它函数调用一样，函数可以被从语句外调用（即通过引用函数名），函数能返回标量值。 存储子程序也可以调用其它存储子程序。
　　在MySQL 5.1中，一个存储子程序或函数与特定的数据库相联系。这里有几个意思：

• 　　当一个子程序被调用时，一个隐含的USE db_name 被执行（当子程序终止时停止执行）。存储子程序内的USE语句时不允许的。
  
• 　　你可以使用数据库名限定子程序名。这可以被用来引用一个不在当前数据库中的子程序。比如，要引用一个与test数据库关联的存储过程p或函数f，你可以说CALL test.p()或test.f()。
  
• 　　数据库移除的时候，与它关联的所有存储子程序也都被移除。
  

　　存储过程是绑定在某个数据库上的。
　　MySQL 支持非常有用的扩展，即它允许在存储程序中使用常规的SELECT语句（那就是说，不使用光标或 局部变量）。这个一个查询的结果包被简单地直接送到客户端。多SELECT语句生成多个结果包，所以客户端必须使用支持多结果包的MySQL客户端库。这意味这客户端必须 使用至少MySQL 4.1以来的近期版本上的客户端库。
　　CREATE PROCEDURE和CREATE FUNCTION
CREATE PROCEDURE sp_name ([proc_parameter[,...]])　　
    [characteristic ...] routine_body
　　

　　
CREATE FUNCTION sp_name ([func_parameter[,...]])
　　
    RETURNS type
　　
    [characteristic ...] routine_body
　　

　　
    proc_parameter:
　　
    [ IN | OUT | INOUT ] param_name type
　　

　　
    func_parameter:
　　
    param_name type
　　

　　
type:
　　
    Any valid MySQL data type
　　

　　
characteristic:
　　
    LANGUAGE SQL
　　
  | [NOT] DETERMINISTIC
　　
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
　　
  | SQL SECURITY { DEFINER | INVOKER }
　　
  | COMMENT 'string'
　　

　　
routine_body:
　　
    Valid SQL procedure statement or statements
　　默认地，子程序与当前数据库关联。要明确地把子程序与一个给定数据库关联起来，可以在创建子程序的时候指定其名字为db_name.sp_name。
　　如果子程序名和内建的SQL函数名一样，定义子程序时，你需要在这个名字和随后括号中间插入一个空格，否则发生语法错误。当你随后调用子程序的时候也要插入。为此，即使有可能出现这种情况，我们还是建议最好避免给你自己的 存储子程序取与存在的SQL函数一样的名字。
　　由括号包围的参数列必须总是存在。如果没有参数，也该使用一个空参数列()。每个参数 默认都是一个IN参数。要指定为其它参数，可在参数名之前使用关键词 OUT或INOUT
　　注意: 指定参数为IN, OUT, 或INOUT 只对PROCEDURE是合法的。（FUNCTION参数总是被认为是IN参数）
　　RETURNS子句只能对FUNCTION做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。
　　一个简单的例子：
mysql> delimiter //　　
mysql> CREATE PROCEDURE getAll()    --创建存储过程
　　
    -> BEGIN
　　
    ->     SELECT prod_id, prod_name, prod_price FROM products;
　　
    -> END
　　
    -> //
　　
Query OK, 0 rows affected (0.15 sec)
　　

　　
mysql> delimiter ;
　　

　　
mysql> CALL getAll();                -- 调用存储过程
　　再来一个：
mysql> delimiter //　　

　　
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)    -- 创建存储过程
　　
    -> BEGIN
　　
    ->   SELECT COUNT(*) INTO param1 FROM t;
　　
    -> END //
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
mysql> delimiter ;
　　

　　
mysql> CALL simpleproc(@a);                            -- 调用存储过程
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
mysql> SELECT @a;
　　
+------+
　　
| @a   |
　　
+------+
　　
| 3    |
　　
+------+
　　
1 row in set (0.00 sec)
　　MySQL命令行客户端的分隔符：
　　默认的MySQL语句分隔符为 ; 。MySQL命令行程序也使用 ; 作为语句分隔符。如果命令行程序要解释存储过程自身内的 ; 字符，则它们最终不会成为存储过程的成分，这会导致存储过程的SQL出现语法错误。
　　所以，DELIMITER // 告诉命令行程序使用//作为新的语句结束分隔符，这样，存储过程体内的 ; 仍然保持不变，并且正确的传递给数据库引擎。最后，为恢复为原来的语句分隔符，使用DELIMITER ;
　　当使用delimiter命令时，你应该避免使用反斜杠(‘\’)字符，因为那是MySQL的转义字符。
　　ALTER PROCEDURE和ALTER FUNCTION
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]　　

　　
characteristic:
　　
    { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
　　
  | SQL SECURITY { DEFINER | INVOKER }
　　
  | COMMENT 'string'
　　DROP PROCEDURE和DROP FUNCTION
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name　　存储过程在创建之后，被保存在服务器上以供使用，直至被删除。
　　这个语句被用来移除一个存储过程或函数。即从服务器移除一个制定的子程序。在MySQL 5.1中，你必须有ALTER ROUTINE权限才可用此子程序。这个权限被自动授予子程序的创建者。
　　IF EXISTS 子句是一个MySQL的扩展。如果程序或函数不存在，它防止发生错误。产生一个可以用SHOW WARNINGS查看的警告。
　　注意：没有使用后面的()括号， 只给出存储过程名。不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程
　　SHOW CREATE PROCEDURE和SHOW CREATE FUNCTION
SHOW CREATE {PROCEDURE | FUNCTION} sp_name　　这个语句是一个MySQL的扩展。类似于SHOW CREATE TABLE，它返回一个可用来重新创建已命名 子程序的确切字符串。
　　SHOW PROCEDURE STATUS和SHOW FUNCTION STATUS
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']　　这个语句是一个MySQL的扩展。它返回子程序的特征，如数据库，名字，类型，创建者及创建和修改日期。如果没有指定样式，根据你使用的语句，所有存储过程和所有存储函数的信息都被列出。
CALL语句
CALL sp_name([parameter[,...]])　　MySQL称存储过程的执行为调用，因此使用CALL语句。CALL语句调用一个先前用CREATE PROCEDURE创建的存储过程。CALL接受存储过程的名字以及需要传递给它的任意参数。存储过程名称后面必须加括号，哪怕该存储过程没有参数传递。
　　使用参数
　　Mysql支持IN(传递给存储过程)，OUT(从存储过程传出)和INOUT(对存储过程传入和传出)类型参数。

• 　　IN：这是个默认的类型，也就是如果参数没指定类型的话，那么默认就是IN类型了，这种参数主要是传递进去的值，这个值提供给存储过程使用，另外存储过程对其做的改变不会对传入的参数发生作用。
  
• 　　OUT：这个主要是存储过程要传递出去的值，也就是存储过程给你将它改变，并且传回去给调用它的程序。
  
• 　　INOUT：则把上面两个的特点合在一起了，即可以传递值给存储过程使用，同时存储过程也可以改变这个值在传给调用它的程序。
  

Create procedure|function([[IN | OUT | INOUT ] var_name var_type...])

　　参数的数据类型：存储过程的参数允许的数据类型与表中使用的数据类型相同。但是，记录集是不允许的类型，因此，不能通过一个参数返回多个行和列。
同时也要注意，参数的名字不要和数据库表中的列名相同。
DECLARE语句
　　DECLARE语句被用来把不同项目局域到一个 子程序：局部变量，条件 和 处理程序 及游标。SIGNAL和RESIGNAL语句当前还不被支持。
　　DECLARE有几个限制：

• 　　DECLARE仅被用在BEGIN ... END复合语句里，并且必须在复合语句的开头，在任何其它语句之前。
  
• 　　游标必须在声明处理程序之前被声明，并且变量和条件必须在声明游标或处理程序之前被声明。
  

存储程序中的变量
DECLARE局部变量
DECLARE var_name[,...] type [DEFAULT value]　　这个语句被用来声明局部变量。要给变量提供一个默认值，请包含一个DEFAULT子句。值可以被指定为一个表达式，不需要为一个常数。如果没有DEFAULT子句，初始值为NULL。
　　type只能是MySql支持的那些基本数据类型；
　　局部变量的作用范围在它被声明的BEGIN ... END块内。它可以被用在嵌套的块中，除了那些用相同名字 声明变量的块。
-- 声明一个变量　　
DECLARE  x INT DEFAULT 0
　　

　　
-- 多个相同类型的变量
　　
DECLARE x,y VARCHAR(50) DEFAULT 'hello'
变量SET语句
SET var_name = expr [, var_name = expr] ...　　在存储过程中的SET语句是一般SET语句的扩展版本。被引用变量可能是子程序内声明的变量，或者是全局服务器变量。
　　在存储过程中的SET语句作为预先存在的SET语法的一部分来实现。这允许SET a=x, b=y, ...这样的扩展语法。
　　我们通过set 为变量赋值：
SET  x = 99;SELECT ... INTO语句
SELECT col_name[,...] INTO var_name[,...] table_expr　　这个SELECT语法把选定的列直接存储到变量。因此，只有单一的行可以被取回。
SELECT id,data INTO x,y FROM test.t1 LIMIT 1;　　注意，用户变量名在MySQL 5.1中是对大小写不敏感的。
　　重要: SQL变量名不能和列名一样。如果SELECT ... INTO这样的SQL语句包含一个对列的引用，并包含一个与列相同名字的 局部变量，MySQL当前把引用解释为一个变量的名字，而不是列名。
　　MySQL变量以及作用域
　　变量分为用户变量和系统变量以及局部变量

• 　　会话变量(session variable)，也叫做用户定义的变量(user defined variable)。这种变量要在变量名称前面加上“@”符号，用户变量名一般以@开头，叫做会话变量，代表整个会话过程他都是有作用的。这种变量用途比较广，因为只要在一个会话内(就是某个应用的一个连接过程中)，这个变量可以在被调用的存储过程或者代码之间共享数据。
  

• 　　局部变量：在BEGIN .. END语句块中，使用DECLARE语句声明，作用域为DECLARE开始到END结束
  
• 　　系统变量：MySQL服务器相关的配置
  

　　会话变量：在整个会话连接中有效
mysql> SELECT 'hello' INTO @var1;　　
Query OK, 1 row affected (0.00 sec)
　　

　　
mysql> SELECT @var1;
　　
+-------+
　　
| @var1 |
　　
+-------+
　　
| hello |
　　
+-------+
　　
1 row in set (0.00 sec)
　　

　　
mysql> SET @var2 = 'hello';
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
mysql> SELECT @var2;
　　
+-------+
　　
| @var2 |
　　
+-------+
　　
| hello |
　　
+-------+
　　
1 row in set (0.00 sec)
　　局部变量与会话变量的作用域
mysql> SET @session_var = 0;                -- 声明会话变量　　

　　
mysql> DELIMITER //
　　
mysql> CREATE PROCEDURE varTest()
　　
-> BEGIN
　　
->     DECLARE local_var INT DEFAULT 0;     -- 声明局部变量
　　
->     SET local_var = local_var + 1;       -- 递增局部变量
　　
->     SET @session_var = @session_var + 1; -- 递增会话变量
　　
->     SELECT local_var, @session_var;
　　
-> END //
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
mysql> DELIMITER ;
　　
mysql> CALL varTest();                    -- 调用varTest(), 第一次
　　
+-----------+--------------+
　　
| local_var | @session_var |
　　
+-----------+--------------+
　　
|         1 |            1 |
　　
+-----------+--------------+
　　
1 row in set (0.00 sec)
　　

　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
mysql> CALL varTest();                    -- 调用varTest(), 第二次
　　
+-----------+--------------+
　　
| local_var | @session_var |
　　
+-----------+--------------+
　　
|         1 |            2 |
　　
+-----------+--------------+
　　
1 row in set (0.00 sec)
　　

　　
Query OK, 0 rows affected (0.00 sec)
　　我们看到，@session_var会话变量一直递增的。
　　游标
　　Mysql检索操作返回一组称为结果集的行。
　　有时，需要在检索出来的行中前进或者后退一行或多行。这就是使用游标的原因。游标（cursor）是一个存储在mysql服务器上的数据库查询，它不是一条SELECT语句，而是被SELECT语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。
　　不像多数的DBMS，MySQL游标仅在存储程序和函数内被支持。语法如同在嵌入的SQL中。游标当前是不敏感的，只读的及不滚动的。不敏感意为服务器可以活不可以复制它的结果表。
　　游标的作用就是用于对查询数据库所返回的记录进行遍历，以便进行相应的操作；游标有下面这些属性：

• 　　游标是只读的，也就是不能更新它；
  
• 　　游标是不能滚动的，也就是只能在一个方向上进行遍历，不能在记录之间随意进退，不能跳过某些记录；
  
• 　　避免在已经打开游标的表上更新数据。
  

　　游标必须在声明处理程序之前被声明，并且变量和条件必须在声明光标或处理程序之前被声明。
　　使用游标的步骤：

CREATE PROCEDURE curdemo()          -- 创建存储过程　　
BEGIN
　　
  DECLARE done INT DEFAULT 0;       -- 声明变量
　　
  DECLARE a CHAR(16);
　　
  DECLARE b,c INT;
　　

　　
  -- 声明游标
　　
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
　　
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
　　
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
　　

　　
  -- 打开游标
　　
  OPEN cur1;
　　
  OPEN cur2;
　　

　　
  REPEAT
　　
    FETCH cur1 INTO a, b;
　　
    FETCH cur2 INTO c;
　　
    IF NOT done THEN
　　
       IF b < c THEN
　　
          INSERT INTO test.t3 VALUES (a,b);
　　
       ELSE
　　
          INSERT INTO test.t3 VALUES (a,c);
　　
       END IF;
　　
    END IF;
　　
  UNTIL done END REPEAT;
　　

　　
  -- 关闭游标
　　
  CLOSE cur1;
　　
  CLOSE cur2;
　　
END
　　声明游标
DECLARE cursor_name CURSOR FOR select_statement　　这个语句声明一个游标。也可以在子程序中定义多个游标，但是一个块中的每一个游标必须有唯一的名字。也就是定义一个SELECT语句。
　　SELECT语句不能有INTO子句。
　　
　　游标OPEN语句
OPEN cursor_name　　这个语句打开先前声明的游标。
　　
　　游标FETCH语句
FETCH cursor_name INTO var_name [, var_name] ...　　这个语句用指定的打开游标读取下一行（如果有下一行的话），并且前进游标指针。
　　
　　游标CLOSE语句
CLOSE cursor_name　　这个语句关闭先前打开的游标。
　　如果未被明确地关闭，游标在它被声明的复合语句的末尾被关闭，也就是在到达END语句时自动关闭它。
　　在使用游标时需要注意的是，使用定义一个针对NOT FOUND的条件处理函数(condition handler)来避免出现“no data to fetch”这样的错误，条件处理函数就是当某种条件产生时所执行的代码，这里但我们游标指到记录的末尾时，便达到NOT FOUND这样条件，这个时候我们希望继续进行后面的操作，所以我们会在下面的代码中看到一个CONTINUE。
DECLARE  CONTINUE HANDLER FOR NOT FOUND  SET  no_more_products = 1;　　
/* when "not found" occur,just continue,这个是个条件处理,针对NOT FOUND的条件*/
流程控制语句
　　IF, CASE, LOOP, WHILE, ITERATE, 及 LEAVE 结构被完全实现。
　　有过编程经验的同学，都会很熟悉这些东西，与编程语言的控制语句非常相似。
　　IF语句
IF search_condition THEN statement_list　　
    [ELSEIF search_condition THEN statement_list] ...
　　
    [ELSE statement_list]
　　
END IF
　　IF实现了一个基本的条件结构。如果search_condition求值为真，相应的SQL语句列表被执行。如果没有search_condition匹配，在ELSE子句里的语句列表被执行。statement_list可以包括一个或多个语句。
　　CASE语句
CASE case_value　　
    WHEN when_value THEN statement_list
　　
    [WHEN when_value THEN statement_list] ...
　　
    [ELSE statement_list]
　　
END CASE
　　
Or:
　　
CASE
　　
    WHEN search_condition THEN statement_list
　　
    [WHEN search_condition THEN statement_list] ...
　　
    [ELSE statement_list]
　　
END CASE
　　存储过程的CASE语句实现一个复杂的条件结构。如果search_condition 求值为真，相应的SQL被执行。如果没有搜索条件匹配，在ELSE子句里的语句被执行。与SHELL中的case语句作用类似。
　　LOOP语句
[begin_label:] LOOP　　
    statement_list
　　
END LOOP [end_label]
　　LOOP允许某特定语句或语句群的重复执行，实现一个简单的循环结构。在循环内的语句一直重复直循环被退出，退出通常伴随着一个LEAVE 语句。
　　LOOP语句可以被标注。除非begin_label存在，否则end_label不能被给出，并且如果两者都出现，它们必须是同样的。
　　LEAVE语句（类比： break）
LEAVE label　　这个语句被用来退出任何被标注的流程控制结构。它和BEGIN ... END或循环一起被使用。
　　ITERATE语句(类比： continue)
ITERATE label　　ITERATE只可以出现在LOOP, REPEAT, 和WHILE语句内。ITERATE意思为：“再次循环。”
　　例如：
CREATE PROCEDURE doiterate(p1 INT)　　
BEGIN
　　
  label1: LOOP
　　
    SET p1 = p1 + 1;
　　
    IF p1 < 10 THEN ITERATE label1; END IF;
　　
    LEAVE label1;
　　
  END LOOP label1;
　　
  SET @x = p1;
　　
END
　　
　　REPEAT语句
[begin_label:] REPEAT　　
    statement_list
　　
UNTIL search_condition
　　
END REPEAT [end_label]
　　REPEAT语句内的语句或语句群被重复，直至search_condition 为真。
　　REPEAT 语句可以被标注。 除非begin_label也存在，end_label才能被用，如果两者都存在，它们必须是一样的。
　　例如：
mysql> delimiter //　　

　　
mysql> CREATE PROCEDURE dorepeat(p1 INT)
　　
    -> BEGIN
　　
    ->   SET @x = 0;
　　
    ->   REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
　　
    -> END
　　
    -> //
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
mysql> CALL dorepeat(1000)//
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
mysql> SELECT @x//
　　
+------+
　　
| @x   |
　　
+------+
　　
| 1001 |
　　
+------+
　　
1 row in set (0.00 sec)
　　WHILE语句
[begin_label:] WHILE search_condition DO　　
    statement_list
　　
END WHILE [end_label]
　　WHILE语句内的语句或语句群被重复，直至search_condition 为真。
　　WHILE语句可以被标注。 除非begin_label也存在，end_label才能被用，如果两者都存在，它们必须是一样的。
　　例如：
CREATE PROCEDURE dowhile()　　
BEGIN
　　
  DECLARE v1 INT DEFAULT 5;
　　

　　
  WHILE v1 > 0 DO
　　
    ...
　　
    SET v1 = v1 - 1;
　　
  END WHILE;
　　
END
　　本文主要参见官方文档
　　http://dev.mysql.com/doc/refman/5.1/zh/stored-procedures.html
　　http://blog.csdn.net/rdarda/article/details/7881648