MySQL存储过程

        存储程序和函数是用CREATE PROCEDURE和CREATE FUNCTION语句创建的子程序。一个子程序要么是一个程序要么是一个函数。使用CALL语句来调用 程序，程序只能用输出变量传回值。就像其它函数调用一样，函数可以从语句外被调用（即通过引用函数名），函数能返回标量值。 存储子程序也可以调用其它存储子程序。

        在MySQL 5.1中，一个存储子程序或函数与特定的数据库相联系

1、当一个子程序被调用时，一个隐含的USE db_name 被执行（当子程序终止时停止执行）。存储子程序内的USE语句是不允许的

2、你可以使用数据库名限定子程序名。这可以被用来引用一个不在当前数据库中的子程序。比如，要引用一个与test数据库关联的存储程序p或函数f，你可以说CALL test.p()或test.f()。

3、 数据库移除的时候，与它关联的所有存储子程序也都被移除

        MySQL 支持非常有用的扩展，即它允许在存储程序中使用常规的SELECT语句（也就是说，不使用光标或局部变量）。这样一个查询的结果被简单地直接送到客户端。多SELECT语句生成多个结果包，所以客户端必须使用支持多结果包的MySQL客户端库。这意味着客户端必须使用至少MySQL 4.1以来的近期版本上的客户端库。

本文主要介绍MySQL的存储过程。

一、创建存储过程

CREATE PROCEDURE sp_name ([proc_parameter[,...]])

 [characteristic ...] routine_body

  proc_parameter:

    [ IN | OUT | INOUT ] param_name 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参数）

        下面是一个使用OUT参数的简单的存储程序的例子。该例子在程序被定义的时候，用mysql客户端delimiter命令来把语句定界符从 ;变为//。这就允许用在程序体中的;定界符被传递到服务器而不是被mysql自己来解释。

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 ;

注意：当使用delimiter命令时，你应该避免使用反斜杠(‘\’)字符，因为那是MySQL的 转义字符。

二、修改存储过程

ALTER {PROCEDURE} sp_name [characteristic ...]

characteristic:

    { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

  | SQL SECURITY { DEFINER | INVOKER }

  | COMMENT 'string'

三、删除存储过程

DROP {PROCEDURE} [IF EXISTS] sp_name

移除一个存储程序或函数

四、查看存储过程创建语句

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

返回一个可用来重新创建已命名子程序的确切字符串

五、查看存储过程状态信息

SHOW {PROCEDURE} STATUS [LIKE 'pattern']

返回子程序的特征，如数据库，名字，类型，创建者及创建和修改日期

六、执行存储过程

CALL sp_name([parameter[,...]])

CALL语句调用一个先前用CREATE PROCEDURE创建的程序。

CALL语句可以用 声明为OUT或的INOUT参数的参数给它的调用者传回值

七、BEGIN ... END复合语句

[begin_label:] BEGIN

    [statement_list]

END [end_label]

存储子程序可以使用BEGIN ... END复合语句来包含多个语句。statement_list 代表一个或多个语句的列表。statement_list之内每个语句都必须用分号（；）来结尾

使用多重语句需要客户端能发送包含语句定界符;的查询字符串。这个符号在命令行客户端被用delimiter命令来处理。改变查询结尾定界符;（比如改变为//）使得; 可被用在子程序体中。

八、DECLARE语句

DECLARE语句被用来把不同项目局域到一个子程序：局部变量

DECLARE仅被用在BEGIN ... END复合语句里，并且必须在复合语句的开头，在任何其它语句之前。

光标必须在声明处理程序之前被声明，并且变量和条件必须在声明光标或处理程序之前被声明。

九、存储过程中的变量

1. DECLARE局部变量

DECLARE var_name[,...] type [DEFAULT value]

这个语句被用来声明局部变量。如果需要给变量提供一个默认值，可以包含一个DEFAULT子句。值可以被指定为一个表达式，不需要为一个常数。如果没有DEFAULT子句，初始值为NULL。

局部变量的作用范围在它被声明的BEGIN ... END块内。它可以被用在嵌套的块中，除了那些用相同名字 声明变量的块。

2. 变量SET语句

SET var_name = expr [, var_name = expr] ...

在存储程序中的SET语句是一般SET语句的扩展版本。被参考变量可能是子程序内声明的变量，或者是全局服务器变量。

在存储程序中的SET语句作为预先存在的SET语法的一部分来实现。允许SET a=x, b=y, ...这样的扩展语法。其中不同的变量类型（局域声明变量及全局和集体变量）可以被混合起来。这也允许把局部变量和一些只对系统变量有意义的选项合并起来。在那种情况下，此选项被识别，但是被忽略了。

3. SELECT ... INTO语句

SELECT col_name[,...] INTO var_name[,...] table_expr

这个SELECT语法把选定的列直接存储到变量。因此，只有单一的行可以被取回。

注意：SQL变量名不能和列名一样，用户变量名在MySQL 5.1中是对大小写不敏感的

CREATE PROCEDURE sp1 (x VARCHAR(5))

  BEGIN

    DECLARE xname VARCHAR(5) DEFAULT 'bob';

    DECLARE newname VARCHAR(5);

    DECLARE xid INT;

    SELECT xname,id INTO newname,xid 

      FROM table WHERE xname = xname;

    SELECT newname;

  END;

当这个程序被调用的时候，无论table.xname列的值是什么，变量newname将返回值‘bob’。

十、光标

简单光标在存储程序和函数内被支持。语法如同在嵌入的SQL中。光标当前是不敏感的，只读的及不滚动的。不敏感意为服务器可以活不可以复制它的结果表。

光标必须在声明处理程序之前被声明，并且变量和条件必须在声明光标或处理程序之前被声明

1.声明光标

DECLARE cursor_name CURSOR FOR select_statement

这个语句声明一个光标。也可以在子程序中定义多个光标，但是一个块中的每一个光标必须有唯一的名字。

此处的SELECT语句不能有INTO子句。

2. 光标OPEN语句

OPEN cursor_name

这个语句打开先前声明的光标。

3. 光标FETCH语句

FETCH cursor_name INTO var_name [, var_name] ...

这个语句用指定的打开光标读取下一行（如果有下一行的话），并且前进光标指针。

4. 光标CLOSE语句

CLOSE cursor_name​​​​​​​

这个语句关闭先前打开的光标。

如果未被明确地关闭，光标在它被声明的复合语句的末尾被关闭

十一、流程控制构造

1. 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可以包括一个或多个语句。

请注意，也有一个IF() 函数，它不同于这里描述的IF语句

2. 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子句里的语句被执行。

CASE语句不能有ELSE NULL子句，并且用END CASE替代END来终止

3. LOOP语句

[begin_label:] LOOP

    statement_list

END LOOP [end_label]

LOOP允许某特定语句或语句群的重复执行，实现一个简单的循环构造。在循环内的语句一直重复直循环被退出，退出通常伴随着一个LEAVE 语句。

LOOP语句可以被标注。除非begin_label存在，否则end_label不能被给出，并且如果两者都出现，它们必须是同样的。

4. LEAVE语句

LEAVE label

这个语句被用来退出任何被标注的流程控制构造。它和BEGIN ... END或循环一起被使用。

5. ITERATE语句

ITERATE label

ITERATE只可以出现在LOOP, REPEAT, 和WHILE语句内。ITERATE意思为：“再次循环。”

6. REPEAT语句

[begin_label:] REPEAT

    statement_list

UNTIL search_condition

END REPEAT [end_label]

REPEAT语句内的语句或语句群被重复，直至search_condition 为真。

REPEAT 语句可以被标注。 除非begin_label也存在，end_label才能被用，如果两者都存在，它们必须是一样的

7. WHILE语句

[begin_label:] WHILE search_condition DO

    statement_list

END WHILE [end_label]

WHILE语句内的语句或语句群被重复，直至search_condition 为真。

WHILE语句可以被标注。 除非begin_label也存在，end_label才能被用，如果两者都存在，它们必须是一样的。