MySQL存储过程

　简介

　　存储过程就是一条或多条SQL语句的集合，当对数据库进行一系列复杂操作时，存储过程可以将这些复杂的操作封装程一个代码块，可以重复使用，大大减少数据库开发人员的工作量。

　存储过程的创建

　　创建存储过程

　　使用CREATE PROCEDURE语句创建存储过程，基本语法格式如下：

CREATE PROCEDURE sp_name([proc_parameter])
[characteristics...]routine_body

　　上述语法格式中CREATE PROCEDURE：为用来创建存储过程的关键字；sp_name：存储过程的名称；proc_parameter：存储过程的参数列表，该参数列表形式如下：

[IN|OUT|INOUT] param_name type

　　上述参数列表形式中 IN：输入参数；OUT：输出参数；INOUT：即可以输入也可以输出；param_name：参数名称；type：参数类型，可以是MySQL数据库中的任意类型；

　　在创建存储过程语法中，characteristics用于指定存储过程的特性，取值具体说明如下：

• LANGUAGE SQL：说明routine_body部分是由SQL语句组成的，当前系统支持的语言为SQL，SQL是LANGUAGE的唯一值。
• ［NOT］DETERMINISTIC：指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出；NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DE-TERMINISTIC。
• {CONTAINS SQL|NO SQL|READS SQLDATA|MODIFIES SQL DATA}：指明子程序使用SQL语句的限制。CONTAINS SQL表明子程序包含SQL语句，但是不包含读写数据的语句；NOSQL表明子程序不包含SQL语句；READS SQLDATA说明子程序包含读写数据的语句；MODI-FIES SQL DATA表明子程序包含写数据的语句。默认情况下，系统会指定为CONTAINS SQL。
• SQL SECURITY{DEFINER|INVOKER}：指明谁有权限来执行。DEFINER表示只有定义者才能执行。INVOKER表示拥有权限的调用者可以执行。默认情况下，系统指定为DEFINER。
• COMMENT‘string’：注释信息，可以用来描述存储过程。

　　routime_body是SQL代码的内容，可以用BEGIN…END来表示SQL代码的开始和结束。

　　创建存储过程案例：

mysql> DELIMITER //
mysql> CREATE PROCEDURE Proc ()
    -> BEGIN
    -> SELECT * FROM table_name;
    -> END //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;

　　在上述执行过程中，“DELIMITER //”语句的作用是将MySQL的结束符设置为//，因为MySQL默认的语句结束符号为分号“;”，为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符，并以“END //”结束存储过程。存储过程定义完毕后再使用“DELIMITER ;”恢复默认结束符。DELIM-ITER也可以指定其他符号作为结束符。需要格外注意的是，DELIMITER与要设定的结束符之间一定要有一个空格，否则设定无效。

　　变量的使用

　　在编写存储过程时，有时会需要使用变量保存数据处理过程中的值。在MySQL中，变量可以在子程序中声明并使用，这些变量的作用范围是在BEGIN…END程序中。

• 在存储过程中使用DECLARE语句定义变量，具体语法格式如下：

DECLARE var_name[,varname]…date_type[DEFAULT value];

　　上述语法格式中var_name：局部变量的名称；DEFAULT value子句给变量提供一个默认值。该值除了可以被声明为一个常数之外，还可以被指定为一个表达式。如果没有DEFAULT子句，变量的初始值为NULL。

　　接下来定义一个名称为myvariable的变量，类型为INT类型，默认值为100，示例代码如下：

DECLARE myvariable INT DEFAULT 100;

• 使用SET语句为变量赋值，可以改变变量的默认值，语法格式如下：

SET var_name = expr[,var_name = expr]…;

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

　　在存储程序中的SET语句作为预先存在的SET语法的一部分来实现。这允许SET a=x,b=y,…这样的扩展语法。其中不同的变量类型（局域声明变量及全局变量）可以被混合起来。这也允许把局部变量和一些只对系统变量有意义的选项合并起来。

　　接下来声明三个变量，分别为var1、var2、var3，数据类型为INT，使用SET为变量赋值，示例代码如下：

DECLARE var1,var2,var3 INT;
SET var1=10,var2=20;
SET var3=var1+var2;
SET @x=var1+var2;

　　注意:“@x”表示用户变量，使用SET语句为其赋值，用户变量与连接有关，一个客户端定义的变量不能被其他客户端看到或使用。当客户端退出时，该客户连接的所有变量将自动释放。

• 除了可以使用SET语句为变量赋值外，MySQL中还可以通过SELECT…INTO为一个或多个变量赋值，该语句可以把选定的列直接存储到对应位置的变量。使用SELECT…INTO的具体语法格式如下：

SELECT col_name[…] INTO var_name[…] table_expr;

　　在上述语法格式中，col_name表示字段名称；var_name表示定义的变量名称；table_expr表示查询条件表达式，包括表名称和WHERE子句。

　　例：声明变量s_grade和s_gender，通过SELECT…INTO语句查询指定记录并为变量赋值，具体代码如下：

DECLARE s_grade FLOAT;
DECLARE s_gender CHAR(2);
SELECT grade, gender INTO s_grade, s_gender
FROM student WHERE name = 'rose';

　　定义条件和处理程序

　　定义条件是事先定义程序执行过程中遇到的问题，处理程序定义了在遇到这些问题时应当采取的处理方式，并且保证存储过程在遇到警告或错误时能继续执行。

• 定义条件
  在编写存储过程时，定义条件使用DECLARE语句，语法格式如下：
  

  DECLARE condition_name CONDITION FOR [condition_type];
  // condition_type的两种形式: [condition_type]:SQLSTATE[VALUE] sqlstate_value|mysql_error_code

  上述语法格式中，condition_name表示所定义的条件的名称；condition_type表示条件的类型；sqlstate_value和mysql_error_code都可以表示MySQL的错误，sqlstate_value是长度为5的字符串类型错误代码，mysql_error_code为数值类型的错误代码。例如：ERROR1142（42000）中，sql-state_value的值是42000，mysql_error_code的值是1142。
  上述语法格式指定了需要特殊处理的条件。它将一个名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的DECLARE HAN-DLER语句中。

  例：定义“ERROR1148（42000）”错误，名称为command_not_allowed的两种方式：
  

  //方法一：使用sqlstate_value
  DECLARE command_not_allowed CONDITION FOR SQLSTATE'42000';
  //方法二：使用mysql_error_code
  DECLARE command_not_allowed CONDITION FOR 1148;

• 定义处理程序
  定义完条件后，还需要定义针对此条件的处理程序。MySQL中用DECLARE语句定义处理程序，具体语法格式如下：
  

  DECLARE handler_type HANDLER FOR condition_value[,…] sp_statement
  //handler_type:  CONTINUE|EXIT|UNDO
  //condition_value:  |condition_name  |SQLWARNING  |NOT FOUND  |SQLEXCEPTION  |mysql_error_code

  handler_type为错误处理方式，CONTINUE表示遇到错误不处理，继续执行；EXIT表示遇到错误马上退出；UNDO表示遇到错误后撤回之前的操作，MySQL中暂时不支持这样的操作。
  sp_statement表示在遇到定义的错误时，需要执行的存储过程；
  condition_value为错误类型，取值：
  （1）SQLSTATE[VALUE] sqlstate_value包含5个字符的字符串错误值。
  （2）condition_name表示DECLARE CON-DITION定义的错误条件名称。
  （3）SQLWARNING匹配所有以01开头的SQLSTATE错误代码。
  （4）NOT FOUND匹配所有以02开头的SQLSTATE错误代码。
  （5）SQLEXCEPTION匹配所有没有被SQL-WARNING或NOT FOUND捕获的SQLSTATE错误代码。
  （6）mysql_error_code匹配数值类型错误代码。

• 例：定义处理程序几种方式：
  

  //方法一：捕获sqlstate_value
  DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'SET @info='NO_SUCH_TABLE';
  
  //方法二：捕获mysql_error_code
  DECLARE CONTINUE HANDLER FOR 1146 SET @info='NO_SUCH_TABLE';
  
  //方法三：先定义条件，然后调用
  DECLARE no_such_table CONDITION FOR 1146;
  DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info='ERROR';
  
  //方法四：使用SQLWARNING SQL-WARNING捕获所有以01开头的sqlstate_value值，然后执行EXIT操作，并且输出“ERROR”信息。
  DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';
  
  //方法五：使用NOT FOUND NOTFOUND捕获所有以02开头的sqlstate_value值，然后执行EXIT操作，并且输出“NO_SUCH_TA-BLE”信息。
  DECLARE EXIT HANDLER FOR NOT FOUND SET @info='NO_SUCH_TABLE';
  
  //方法六：使用SQLEXCEPTION SQLEXCEPTION捕获所有没有被SQLWARN-ING或NOT FOUND捕获的sqlstate_value值，然后执行EXIT操作，并且输出“ERROR”信息
  DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

　　光标的使用

　　在编写存储过程时，查询语句可能会返回多条记录，如果数据量非常大，则需要使用光标来逐条读取查询结果集中的记录。光标是一种用于轻松处理多行数据的机制。

• 光标的声明
  光标必须声明在声明变量、条件之后，声明处理程序之前。MySQL中使用DECLARE关键字来声明光标，具体语法格式如下：
  

  DECLARE cursor_name CURSOR FOR select_statement;
  

  cursor_name：光标名称；
  select_statement：select语句，用于创建光标的结果集；

  例：声明名为cursor_student的光标
  

  DECLARE cursor_student CURSOR FOR select s_name,s_gender FROM student;

• 光标的使用
  声明完光标就可以使用了，使用前首先要大开光标；MySQL中打开和使用光标，语法格式如下：
  

  OPEN cursor_name
  FETCH cursor_name INTO var_name[,var_name]…

  cursor_name：光标名称；
  var_name：将光标中的SELECT语句查询出来的信息存入该参数中，需要注意的是，var_name必须在声明光标之前就定义好。

  例：使用名称为cursor_student的光标。将查询出来的信息存入s_name和s_gender中
  

  FETCH cursor_student INTO s_name, s_gender;

• 光标的关闭
  使用完光标要将光标关闭；语法格式如下：
  

  CLOSE cursor_name

  值得一提的是，如果没有明确地关闭光标，它会在其声明的复合语句的末尾被关闭。

　　流程控制的使用　

　　在编写存储过程时还有一个非常重要的部分——流程控制。MySQL中的流程控制语句包括：IF语句、CASE语句、LOOP语句、WHILE语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句。

　　每个流程中可能包含一个单独语句，也可能是使用BEGIN…END构造的复合语句，可以嵌套。

• IF语句
  IF实现了一个基本的条件构造。用法：
  

  IF val IS NULL
      THEN SELECT 'val is NULL';
      ELSE SELECT 'val is not NULL';
  END IF;

  需要注意的是，MySQL中还有一个IF()函数，它不同于这里描述的IF语句。IF语句都需要使用END IF来结束，不可省略。

• CASE语句
  CASE是另一种条件判断的语句，该语句有两种格式，第一种如下：
  

  CASE case_expr
      WHEN when_value THEN statement_list
      [WHEN when_value THEN statement_list]…
      [ELSE statement_list]
  END CASE

  例：

  CASE val
      WHEN 1 THEN SELECT 'val is 1';
      WHEN 2 THEN SELECT 'val is 2';
      ELSE SELECT 'val is not 1 or 2';
  END CASE;

  第二种如下：

  CASE
    WHEN expr_condition THEN statement_list
    [WHEN expr_condition THEN statement_list]
    [ELSE statement_list]
  END CASE;

  需要注意的是，这里说的用在存储过程里的CASE语句与“控制流程函数”里描述的SQL CASE表达式中的CASE语句有些不同。存储过程里的CASE语句不能有ELSE NULL子句，并且用ENDCASE替代END来终止。

• LOOP语句
  LOOP循环语句是用来重复执行某些语句，直到跳出循环语句。语法格式如下：
  

  [loop_label:]LOOP
      statement_list
  END LOOP [loop_label]

  loop_label：指LOOP语句的标注名称，可省略。
  statement_list：需要循环执行的语句。

  例：使用LOOP语句进行循环操作
  

  DECLARE id INT DEFAULT 0;
  add_loop:LOOP
  SET id=id+1;
      IF id>=10 THEN  LEAVE add_loop;
      END IF;
  END LOOP add_loop;

  当id值小于10时，循环重复执行；当id值大于或者等于10时，使用LEAVE语句退出循环。

• LEAVE语句
  LEAVE语句用于退出任何被标注的流程控制构造，如LOOP例子中，当不满足循环条件时，需要使用LEAVE语句退出循环。基本语法如下：
  

  LEAVE label

  label表示循环的标志。通常LEAVE语句与BEGIN。。。END、循环语句一起使用。 

• ITERATE语句
  ITERATE即再次循环，用于将执行顺序转到语句段的开头处。基本语法如下：
  

  ITERATE lable

  lable表示循环的标志。需要注意的是，ITERATE 语句只能出现在LOOP、REPEAT和WHILE语句内。

  例：演示了ITERATE语句在LOOP语句内的使用
  

  CREATE PROCEDURE doiterate()
  BEGIN 
  DECLARE p1 INT DEFAULT 0;
  my_loop:LOOP
     SET p1=p1+1;
     IF p1<10 THEN ITERATE my_loop;
     ELSEIF p1>20 THEN LEAVE my_loop;
     END IF;
     SELECT 'p1 is between 10 and 20';
  END LOOP my_loop;
  END

• REPEAT语句
  REPEAT语句用于创建一个带有条件判断的循环过程，每次执行后，会对条件进行判断，如果为真，则循环结束；否则重复执行循环中的语句。基本语法如下：
  

  [repeat_lable:] REPEAT
     statement_list
  UNTIL expr_condition
  END REPEAT[repeat_lable]

  repeat_lable指REPEAT语句的标注名称（可选）；REPEAT语句内的语句或语句群被重复，直至expr_condition为真。

  例：演示使用REPEAT语句执行循环过程
  

  DECLARE id INT DEFAULT 0;
  REPEAT
  SET id=id+1;
  UNTIL id>=10;
  END REPEAT;

• WHILE语句
  WHILE语句用于创建一个带有条件判断的循环过程，与REPEAT不同的是在语句执行时，先对指定的表达式进行判断，如果为真，则执行循环内的语句，否则退出循环。基本语法如下：
  

  [while_lable:] WHILE expr_condition DO
       Statement_list
  END WHILE [while_lable]

  while_lable指WHILE语句的标注名称；
  expr_condition为进行判断的表达式，如果为真，WHILE语句内的语句或语句群被执行，直至expr_condition为假，退出循环。

  例：示使用WHILE语句进行循环操作
  

  DECLARE i INT DEFAULT 0;
  WHILE i<10 DO
  SET i=i+1;
  END WHILE;

　存储过程的使用

　　调用存储过程

　　存储过程必须用CALL语句调用，如果调用其他数据库的存储过程，需要指定数据库名。调用语法格式如下：

CALL sp_name([parameter[,…]])

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

　　例：定义一个名为CountProc1的存储过程，然后调用这个存储过程，具体操作如下：

1. 定义存储过程：
   

   mysql> DELIMITER //
   mysql> CREATE PROCEDURE CountProc1(IN s_gender VARCHAR(50),OUT num INT)
       -> BEGIN
       -> SELECT COUNT(*) INTO num FROM student WHERE gender= s_gender;
       -> END//
   Query OK, 0 rows affected (0.13 sec)
   mysql> DELIMITER;

2. 调用存储过程：
   

   mysql> CALL CountProc1("女",@num);
   Query OK, 1 row affected (0.17 sec)

3. 查看返回结果
   

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

　　查看存储过程

1. SHOW STATUS语句
   基本语法结构：
   

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

   上述语法格式中，PROCEDURE和FUNCTION分别表示查看存储过程和函数，LIKE语句表示匹配的名称。

   示例：获取数据库中所有名称以C开头的存储过程的信息。
   

   SHOW PROCEDURE STATUS LIKE'C%'\G

2. SHOW CREATE语句
   基本语法结构：
   

   SHOW CREATE{PROCEDURE|FUNCTION} sp_name

   示例：

   SHOW CREATE PROCEDURE chapter06.CountProc1\G

3. 从information_schema.Routines表中查看存储过程的信息
   在MySQL中存储过程和函数的信息存储在information_schema数据库下的Routines表中。可以通过查询该表的记录来查询存储过程的信息，查询语句如下：

   SELECT * FROM  information_schema.RoutinesWHERE ROUTINE_NAME='CountProc1' AND ROUTINE_TYPE='PROCEDURE'\G

   需要注意的是，ROUTINE_NAME：指定存储过程的名称；ROUTINE_TYPE：指定存储程序的类型。

　　修改存储过程

　　使用ALTER语句修改存储过程的特性，其基本语法格式如下：

ALTER {PROCEDURE|FUNCTION} sp_name[characteristic…]

　　上述语法格式中，sp_name：存储过程或函数的名称；characteristic表示要修改存储过程的哪个部分，characteristic的取值具体如下：

1. CONTAINS SQL表示子程序包含SQL语句，但不包含读或写数据的语句；
2. NO SQL表示子程序中不包含SQL语句；
3. READS SQL DATA表示子程序中包含读数据的语句；
4. MODIFIES SQL DATA表示子程序中包含写数据的语句；
5. SQL SECURITY{DEFINER|INVOKER}指明谁有权限来执行；（DEFINER：只有定义者自己才能够执行；INVOKER：调用者可以执行）
6. COMMENT‘string’表示注释信息。

　　目前，MySQL还不提供对已存在的存储过程代码的修改，如果一定要修改存储过程代码，必须先将存储过程删除之后，再重新编写代码，或创建一个新的存储过程。

　　删除存储过程

　　使用DROP语句删除存储过程，其基本语法格式如下：

DROP{ PROCEDURE|FUNCTION }[IF EXISTS] sp_name

　　示例：删除存储过程CountProc1

DROP PROCEDURE CountProc1;

 　综合案例－存储过程应用

1. 创建一个stu表
   表结构：
   
   表数据：
   
   建表及初始化数据脚本：
   

   CREATE TABLE stu(id INT,name VARCHAR(50),class VARCHAR(50));
   INSERT INTO stu VALUE (1,'Lucy','class1'),(2,'Tom','class1'),(3,'Rose','class2');

2. 创建一个存储过程
   创建一个存储过程addcount能够获取表stu中的记录数和id的和，代码格式如下：
   

   CREATE PROCEDURE addcount(out count INT)
   BEGIN
   DECLARE itmp INT;
   DECLARE cur_id CURSOR FOR SELECT id FROM stu;
   DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_id;
   SELECT count(*) INTO count FROM stu;
   SET @sum=0;
   OPEN cur_id;
   REPEAT
   FETCH cur_id INTO itmp;
   IF itmp<10
   THEN SET @sum=@sum+itmp;
   END IF;
   UNTIL 0 END REPEAT;
   CLOSE cur_id;
   END;

   上述存储过程用到了变量的声明、光标、流程控制。SQL语句执行情况如下：

   mysql> DELIMITER //
   mysql> CREATE PROCEDURE addcount(out count INT)
       -> BEGIN
       -> DECLARE itmp INT;
       -> DECLARE cur_id CURSOR FOR SELECT id FROM stu;
       -> DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_id;
       -> SELECT count(*) INTO count FROM stu;
       -> SET @sum=0;
       -> OPEN cur_id;
       -> REPEAT
       -> FETCH cur_id INTO itmp;
       -> IF itmp<10
       -> THEN SET @sum=@sum+itmp;
       -> END IF;
       -> UNTIL 0 END REPEAT;
       -> CLOSE cur_id;
       -> END //
   Query OK, 0 rows affected (0.00 sec)
   mysql> CALL addcount(@count) //
   Query OK, 0 rows affected (0.00 sec)
   mysql> SELECT @count,@sum //
   +--------+------+
   | @count | @sum |
   +--------+------+
   |    3   |    6 |
   +--------+------+
   1 row in set (0.00 sec)
   mysql> DELIMITER;

   从调用存储过程的结果可以看出，stu表中共有三条数据，id之和为6。这个存储过程创建了一个cur_id的光标，使用这个光标来获取每条记录的id，使用REPEAT循环语句来实现所有id号相加。