MySql的存储过程

存储过程和函数是在数据库中定义一些SQL语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且，存储过程和函数是在MySQL服务器中存储和执行的。可以减少客户端和服务器端的数据传输。

创建存储过程和存储函数

数据库系统中，为了保证数据的完整性、一致性，同时也为提高其应用性能，大多数据库常采用存储过程和存储函数技术。存储过程和存储函数经常是一组SQL语句的组合，这些语句被当作整体存入MySQL数据库服务器中。用户定义的存储函数不能用于修改全局库状态，但该函数可从查询中被唤醒调用，也可以像存储过程一样通过语句执行。

存储过程

CREATE PROCEDURE sp_name ([proc_parameter[，……]]) [characteristic……] routine_body

sp_name参数是存储过程的名称；

proc_parameter表示存储过程的参数列表,proc_parameter中的参数由3部分组成，它们分别是输入输出类型、参数名称和参数类型。其形式为[IN|OUT|INOUT] param_name type。其中IN表示输入参数；OUT表示输出参数；INOUT表示既可以输入也可以输出；param_name参数是存储过程参数名称；type参数指定存储过程的参数类型，该类型可以为MySQL数据库的任意数据类型;

characteristic参数指定存储过程的特性；

routine_body参数是SQL代码的内容，可以用BEGIN..END来标识SQL代码的开始和结束。

由于存储过程内部语句要以分号结束，所以在定义存储过程前，应将语句结束标志“；”更改为其他字符，
并且应降低该字符在存储过程中出现的概率，更改结束标志可以用关键字“delimiter”定义
delimiter//
create procedure proc_name (in parameter integer)
   begin
      declare variable varchar（20）；
      if parameter=1 then
          set variable='MySQL'；
      else
          set variable='PHP'；
      end if；
     insert into tb (name) values (variable);
   end;
   //

存储函数

CREATE FUNCTION sp_name ([func_parameter[，……]]) RETURNS type [characteristic……] routine_body

func_parameter可以由多个参数组成，其中每个参数均由参数名称和参数类型组成，其结构如下：

param_name参数是存储函数的函数名称；type参数用于指定存储函数的参数类型。该类型可以是MySQL数据库所支持的类型。

delimiter//
create function name_of_student（std_id INT）
    returns varchar（50）
    begin
        return（select name from studentinfo where sid=std_id）；
    end
//  

变量的应用

1.局部变量

局部变量以关键字declare声明，后跟变量名和变量类型，例如：

declare a int

当然在声明局部变量时也可以用关键字default为变量指定默认值，例如：

declare a int default 10

delimiter//
create procedure p1（）
    begin
        declare x char（10）default'outer'；
        begin
            declare x char（10）default'inner'；
            select x；
        end；
        select x；
    end；
//

2.全局变量

MySQL中的会话变量不必声明即可使用，会话变量在整个过程中有效，会话变量名以字符“@”作为起始字符。下述代码为会话变量的使用方法。

设置全局变量的代码如下：
delimiter//
create procedure p2（）
    begin
        set@t=1；
        begin
            set@t=2；
            select@t；
        end；
        select@t；
    end；
//

3.为变量赋值

MySQL中可以使用DECLARE关键字来定义变量。定义变量的基本语法如下：

DECLARE var_name ，…… type [DEFAULT value]

DECLARE是用来声明变量的；var_name参数是设置变量的名称。如果用户需要，也可以同时定义多个变量；type参数用来指定变量的类型；DEFAULT value的作用是指定变量的默认值，不对该参数进行设置时，其默认值为NULL。

MySQL中可以使用SET关键字为变量赋值。SET语句的基本语法如下：

SET var_name1=expr1，var_name2=expr2……

SET关键字是用来为变量赋值；var_name参数是变量的名称；expr参数是赋值表达式。一个SET语句可以同时为多个变量赋值，各个变量的赋值语句之间用“，”隔开。例如：为变量mr_soft赋值，代码如下：

SET mr_soft=10；

另外，MySQL中还可以应用另一种方式为变量赋值。其语法结构如下：

SELECT col_name，…… INTO var_name，…… FROM table_name where condition

其中，col_name参数标识查询的字段名称；var_name参数是变量的名称；table_name参数为指定数据表的名称；condition参数为指定查询条件。

例如：从studentinfo表中查询name为“LeonSK”的记录。将该记录下的tel字段内容赋值给变量customer_tel。其关键代码如下：

SELECT tel INTO customer_tel FROM studentinfo WHERE name='LeonSK'；

说明：上述赋值语句必须存在于创建的存储过程中，且需将赋值语句放置在BEGIN……END之间。若脱离此范围，该变量将不能使用或被赋值。

光标的运用

通过MySQL查询数据库，其结果可能为多条记录。在存储过程和函数中使用光标可以实现逐条读取结果集中的记录。光标使用包括声明光标（DECLARE CURSOR）、打开光标（OPEN CURSOR）、使用光标（FETCH CURSOR）和关闭光标（CLOSE CURSIR）。值得一提的是，光标必须声明在处理程序之前，且声明在变量和条件之后。

1.声明光标

在MySQL中，声明光标仍使用DECLARE关键字，其语法如下：

DECLARE cursor_name CURSOR FOR select_statement

cursor_name是光标的名称，光标名称使用与表名同样的规则；select_statement是一个SELECT语句，返回一行或多行数据。其中，这个语句也可以在存储过程中定义多个光标，但是必须保证每个光标名称的唯一性，即每一个光标必须有自己唯一的名称。

通过上述定义来声明光标info_of_student，代码如下：

DECLARE info_of_student CURSOR FOR

SELECT

sid, name, age, sex, age

FROM studentinfo

WHERE sid=1；

这里SELECT子句中不能包含INTO子句，并且光标只能在存储过程或存储函数中使用。

2.打开光标

在声明光标之后，要从光标中提取数据，必须首先打开光标。在MySQL中，使用OPEN关键字来打开光标。其基本的语法如下：

OPEN cursor_name

其中，cursor_name参数表示光标的名称。在程序中，一个光标可以打开多次。由于可能在用户打开光标后，其他用户或程序正在更新数据表。所以可能会导致用户在每次打开光标后，显示的结果都不同。

打开上面已经声明的光标info_of_student，其代码如下：

OPEN info_of_student

3.使用光标

光标在顺利打开后，可以使用FETCH……INTO语句来读取数据。其语法如下：

FETCH cursor_name INTO var_name1，var_name2.....

其中，cursor_name代表已经打开光标的名称；var_name参数表示将光标中的SELECT语句查询出来的信息存入该参数中。var_name是存放数据的变量名，必须在声明光标前定义好。FETCH……INTO语句与SELECT……INTO语句具有相同的意义。

将已打开的光标info_of_student中SELECT语句查询出来的信息存入tmp_name和tmp_tel中。其中tmp_name和tmp_tel必须在使用前定义。

代码如下：

FETCH info_of_student INTO tmp_name, tmp_tel；

4.关闭光标

光标使用完毕后，要及时关闭，在MySQL中采用CLOSE关键字关闭光标，其语法格式如下：

CLOSE cursor_name

cursor_name参数表示光标名称。下面关闭已打开的光标info_of_student。代码如下：

CLOSE info_of_student

对于已关闭的光标，在其关闭之后则不能使用FETCH来使用光标。光标在使用完毕后一定要关闭。

流程控制语句

在MySQL中，常见的过程式SQL语句可以用在一个存储过程体中。其中包括IF语句、CASE语句、LOOP语句、WHILE语句、ITERATE语句和LEAVE语句，它们可以进行流程控制。

IF语句

IF语句用来进行条件判断，根据不同的条件执行不同的操作。该语句在执行时首先判断IF后的条件是否为真，则执行THEN后的语句，如果为假则继续判断IF语句直到为真为止，当以上都不满足时则执行ELSE语句后的内容。IF语句表示形式如下：

IF condition THEN
……
[ELSE condition THEN]
……
[ELSE]
……
ENDIF

CASE语句

case语句为多分支语句结构，该语句首先从when后的value中查找与case后的value相等的值，如果查找到则执行该分支的内容，否则，执行else后的内容。case语句表示形式如下：

CASE x
WHEN value THEN……
[WHEN value THEN……]
[ELSE……]
END CASE
其中，x参数表示条件判断的变量；WHEN……THEN中的value参数表示变量的取值。

WHILE循环语句

while循环语句执行时首先判断condition条件是否为真，如果是，则执行循环体，否则退出循环。该语句表示形式：

while condition do
……
end while；

LOOP循环语句

该循环没有内置的循环条件，但可以通过leave语句退出循环。loop语句表示形式：

loop
……
end loop

LOOP允许某特定语句或语句群的重复执行，实现一个简单的循环构造，其中中间省略的部分是需要重复执行的语句。在循环内的语句一直重复直至循环被退出，退出循环应用LEAVE语句。

LEAVE语句

LEAVE语句经常和BEGIN……END或循环一起使用，其结构如下：

LEAVE label

label是语句中标注的名字，这个名字是自定义的。加上LEAVE关键字就可以用来退出被标注的循环语句。

delimiter//
    create procedure example_loop（out sum int）
        begin
            declare i int default 1；
            declare s int default 0；
            loop_label：loop
                set s=s+i；
                set i=i+1；
                if i＞100 then
                    leave loop_label；
                end if；
            end loop；
            set sum=s；
        end
//

call example_loop（@s）

select @s

REPEAT循环语句

该语句先执行一次循环体，之后判断condition条件是否为真，则退出循环，否则继续执行循环。repeat语句表示形式：

REPEAT
……
UNTIL condition
END REPEAT

ITERATE语句

它可以出现在LOOP、REPEAT和WHILE语句内，其意为“再次循环”。

ITERATE label

该语句的格式与LEAVE大同小异，区别在于：LEAVE语句是离开一个循环，而ITERATE语句是重新开始一个循环。

注意,与一般程序设计流程控制不同的是：存储过程并不支持FOR循环。

调用存储过程和存储函数

调用存储过程

CALL sp_name（[parameter，……]）；

其中，sp_name是存储过程的名称；parameter是存储过程的参数。

调用存储函数

在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法基本相同，用户自定义的存储函数与MySQL内部函数性质相同。

SELECT function_name（[parameter，……]）；

查看存储过程和函数

创建存储过程和函数以后，用户可以查看存储过程和函数的状态和定义。用户可以通过SHOW STATUS语句查看存储过程和函数状态，也可以通过SHOW CREATE语句来查看存储过程和函数的定义。

在MySQL中可以通过SHOW STATUS语句查看存储过程和函数的状态。其基本语法结构如下：

SHOW {PROCEDURE|FUNCTION} STATUS [LIKE 'pattern']

其中，PROCEDURE参数表示查询存储过程；FUNCTION参数表示查询存储函数；LIKE 'pattern'参数用来匹配存储过程或函数名称。

MySQL中可以通过SHOW CREATE语句来查看存储过程和函数的状态。其语法结果如下：

SHOW CREATE {PROCEDURE|FUNCTION} sp_name；

其中，PROCEDURE参数表示存储过程；FUNCTION参数表示查询存储函数；sp_name参数表示存储过程或函数的名称。

SHOW STATUS语句只能查看存储过程或函数所操作的数据库对象，如存储过程或函数的名称、类型、定义者、修改时间等信息，并不能查询存储过程或函数的具体定义。如果需要查看详细定义，需要使用SHOW CREATE语句。

修改存储过程和函数

修改存储过程和存储函数是指修改已经定义好的存储过程和函数。在MySQL中可通过ALTER PROCEDURE语句来修改存储过程，通过ALTER FUNCTION语句来修改存储函数。

在MySQL中修改存储过程和函数的语句的语法形式如下：

ALTER {PROCEDURE|FUNCTION} sp_name [characteristic……] 
characteristic：{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}|SQL SECURITY{DEFINER|INVOKER}|COMMENT'string'

ALTER FUNCTION name_of_student READS SQL DATA COMMENT'FIND NAME'；

SELECT SPCIFIC_NAME, SQL_DATA_ACCESS, ROUTINE_COMMENT FROM information_schema.Routines WHERE ROUTINE_NAME='name_of_student'；

删除存储过程和函数

删除存储过程和函数指删除数据库中已经存在的存储过程或函数。在MySQL中可以使用DROP PROCEDURE语句来删除存储过程，通过DROP FUNCTION语句来删除存储函数。在删除之前，必须确认该存储过程或函数没有任何依赖关系，否则可能会导致其他与其关联的存储过程无法运行。

删除存储过程和函数的语法如下：

DROP {PROCEDURE|FUNCTION} [IF EXISTS] sp_name

其中，sp_name参数表示存储过程或函数的名称；IF EXISTS是MySQL的扩展，判断存储过程或函数是否存在，以免发生错误。

当返回结果没有提示警告或报错时，则说明存储过程或存储函数已经被顺利删除。

捕获存储过程中的错误

在用户执行某些程序时，可能会产生一些问题，为了增强程序本身处理问题的能力，避免程序因异常而终止运行。我们往往在处理程序执行前，预测程序在执行过程中可能出现或遇到的问题。定义条件和处理程序来提示用户的同时，也为用户提出解决办法。MySQL通过DECLARE关键字来定义条件和处理程序。

定义条件

在MySQL中，应用DECLARE语句定义条件。其语法格式如下：

DECLARE condition_name CONDITION FOR condition_value

condition_value：

SQLSTATE [VALUE] sqlstate_value  |   mysql_error_code

其中，condition_name参数表示条件名称；condition_value参数表示类型：sqlstate_value参数可以表示MySQL的错误。另外也可以应用mysql_error_code来表示错误代码。其中第一种定义方法格式如下：

DECLARE can_not_find CONDITION FOR SQLSTATE '42S02'；

第二种定义格式如下：

DECLARE can_not_find CONDITION FOR 1146

说明:两种表示MySQL错误的方法名称都为can_not_find，不同点只是获取的错误提示码不同。第一种方法设置sqlstate_value值为42S02，第二种方法设置mysql_error_code值为1146。

定义处理程序

在MySQL中可以使用DECLARE关键字来定义处理程序。其语法如下：

DECLARE handler_type HANDLER FOR condition_value，…… sp_statement

handler_type：

CONTINUE|EXIT|UNDO

condition_value：

SQLSTATE [VALUE] sqlstate_value   |   condition_name   |  SQLWARNING  |  NOT FOUND  | SQLEXCEPTION | mysql_error_code

这个语句指定每个可以处理一个或多个条件的处理程序。如果产生一个或多个条件，指定的语句被执行。

handler_type参数指明错误的处理方式，该参数有3个取值。它们分别是：

对于一个CONTINUE处理程序，当前子程序的执行在执行处理程序语句之后继续。

对于EXIT处理程序，当前BEGIN……END复合语句的执行被终止。

UNDO表示遇到错误后撤回之前的操作，MySQL不支持该处理方式。

condition_value参数指定错误类型，该参数有6个取值。

sqlstate_value：表示MySQL的错误。

mysql_error_code：表示错误代码。

condition_name：DECLARE定义的条件名称。

SQL WARNING：表示所有以01开头的sqlstate_value值。

NOT FOUND：表示所有以02开头的sqlstate_value值。

SQLEXCEPITION：表示所有没有被SQL WARNING或NOT FOUND捕获的sqlstate_value值。