MySQL中级：存储过程和游标
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 (])
routine_body
CREATE FUNCTION sp_name (])
RETURNS type
routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic:
LANGUAGE SQL
| 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:
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
DROP PROCEDURE和DROP FUNCTION
DROP {PROCEDURE | FUNCTION} 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 这个语句是一个MySQL的扩展。它返回子程序的特征，如数据库，名字，类型，创建者及创建和修改日期。如果没有指定样式，根据你使用的语句，所有存储过程和所有存储函数的信息都被列出。
CALL语句
CALL sp_name(]) MySQL称存储过程的执行为调用，因此使用CALL语句。CALL语句调用一个先前用CREATE PROCEDURE创建的存储过程。CALL接受存储过程的名字以及需要传递给它的任意参数。存储过程名称后面必须加括号，哪怕该存储过程没有参数传递。
使用参数
Mysql支持IN(传递给存储过程)，OUT(从存储过程传出)和INOUT(对存储过程传入和传出)类型参数。
[*] IN：这是个默认的类型，也就是如果参数没指定类型的话，那么默认就是IN类型了，这种参数主要是传递进去的值，这个值提供给存储过程使用，另外存储过程对其做的改变不会对传入的参数发生作用。
[*] OUT：这个主要是存储过程要传递出去的值，也就是存储过程给你将它改变，并且传回去给调用它的程序。
[*] INOUT：则把上面两个的特点合在一起了，即可以传递值给存储过程使用，同时存储过程也可以改变这个值在传给调用它的程序。
Create procedure|function([ var_name var_type...])
参数的数据类型：存储过程的参数允许的数据类型与表中使用的数据类型相同。但是，记录集是不允许的类型，因此，不能通过一个参数返回多个行和列。
同时也要注意，参数的名字不要和数据库表中的列名相同。
DECLARE语句
DECLARE语句被用来把不同项目局域到一个 子程序：局部变量，条件 和 处理程序 及游标。SIGNAL和RESIGNAL语句当前还不被支持。
DECLARE有几个限制：
[*] DECLARE仅被用在BEGIN ... END复合语句里，并且必须在复合语句的开头，在任何其它语句之前。
[*] 游标必须在声明处理程序之前被声明，并且变量和条件必须在声明游标或处理程序之前被声明。
存储程序中的变量
DECLARE局部变量
DECLARE var_name[,...] type 这个语句被用来声明局部变量。要给变量提供一个默认值，请包含一个DEFAULT子句。值可以被指定为一个表达式，不需要为一个常数。如果没有DEFAULT子句，初始值为NULL。
type只能是MySql支持的那些基本数据类型；
局部变量的作用范围在它被声明的BEGIN ... END块内。它可以被用在嵌套的块中，除了那些用相同名字 声明变量的块。
-- 声明一个变量
DECLAREx 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 为变量赋值：
SETx = 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。
DECLARECONTINUE HANDLER FOR NOT FOUNDSETno_more_products = 1;
/* when "not found" occur,just continue,这个是个条件处理,针对NOT FOUND的条件*/
流程控制语句
IF, CASE, LOOP, WHILE, ITERATE, 及 LEAVE 结构被完全实现。
有过编程经验的同学，都会很熟悉这些东西，与编程语言的控制语句非常相似。
IF语句
IF search_condition THEN statement_list
...
END IF
IF实现了一个基本的条件结构。如果search_condition求值为真，相应的SQL语句列表被执行。如果没有search_condition匹配，在ELSE子句里的语句列表被执行。statement_list可以包括一个或多个语句。
CASE语句
CASE case_value
WHEN when_value THEN statement_list
...
END CASE
Or:
CASE
WHEN search_condition THEN statement_list
...
END CASE
存储过程的CASE语句实现一个复杂的条件结构。如果search_condition 求值为真，相应的SQL被执行。如果没有搜索条件匹配，在ELSE子句里的语句被执行。与SHELL中的case语句作用类似。
LOOP语句
LOOP
statement_list
END LOOP
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语句
REPEAT
statement_list
UNTIL search_condition
END REPEAT
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语句
WHILE search_condition DO
statement_list
END WHILE
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
页:
[1]