九、MySQL存储过程和函数

dsqzhaoyue

　　存储过程就是一条或者多条SQL语句的集合，可视为批文件，但是其作用不仅限于批处理。
　　9.1、创建存储过程和函数
　　存储程序可以分为存储过程和函数，MySQL中创建存储过程和函数使用的语句分别是CREATE PROCEDURE和CREATE FUNCTION。使用CALL语句来调用存储过程，只能用输出变量返回值。函数可以从语句外调用( 即通过引用函数名)，也能返回标量值。存储过程也能调用其他存储过程。
　　创建存储过程
　　创建存储过程，需要使用CREATE PROCEDURE语句，基本语法为：
CREATE PROCEDURE sp_name ([proc_parameter])　　
    [characteristic ...] routine_body
　　CREATE PROCEDURE：创建存储过程的关键字
sp_name：存储过程的名称　　proc_parameter：参数列表，列表形式为 [IN|OUT|INOUT] param_name type
　　IN表示输入参数；OUT表示输出参数；INOUT表示既可输入又可输出；
　　param_name参数名称；type参数的类型，该类型可以是MySQL中的任意类型
characteristic：指定存储过程的特性　　LANGUAGE SQL：说明routine_body部分是由SQL语句组成，SQL是LANGUAGE特性的唯一值
　　[NOT] DETERMINISTIC：指明存储过程执行的结果是否确定，DETERMINISTIC表示结果确定，每次执行存储过程时，相同输入得到相同输出；NOT DETERMINISTIC表示不确定。
　　{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使用SQL语句的限制。CONTAINS SQL表明子程序包含SQL语句，但是不包含读写数据的语句；NO SQL表明子程序不包含SQL语句；READS SQL DATA表明子程序包含读数据的语句；MODIFIES SQL DATA表明子程序包含写数据的语句。
　　SQL SECURITY { DEFINER | INVOKER }：指明谁有权限执行。DEFINER表示只有定义者才能执行；INVOKER表示拥有权限的调用者可以执行。
　　COMMENT 'string'：注释信息。
routine_body：SQL代码的内容，可以用BEGIN...END来表示代码的开始和结束。　　创建查看fruits表的存储过程
mysql> DELIMITER //　　

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

　　
mysql> DELIMITER ;
　　创建名称为CountProc的存储过程
mysql> DELIMITER //　　

　　
mysql>  CREATE PROCEDURE CountProc(OUT param1 INT)
　　
    ->   BEGIN
　　
    ->   SELECT COUNT(*) INTO param1 FROM fruits;
　　
    ->   END //
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
mysql> DELIMITER ;
　　'DELIMITER //'作用是将MySQL的结束符设置为//，当使用DELIMITER命令时，应该避免使用反斜杠（’\’）字符，因为反斜线是MySQL的转义字符。
　　
　　创建存储函数
　　创建存储函数，需要使用CREATE FUNCTION语句，基本语法为：
CREATE FUNCTION func_name ([func_parameter])　　
  RETURNS type
　　
  [characteristic ...] routine_body
CREATE FUNCTION：创建存储函数的关键字 func_name表示存储函数的名称func_parameter存储函数的参数列表，形式为 [IN|OUT|INOUT] param_name type　　IN表示输入参数；OUT表示输出参数；INOUT表示既可输入又可输出；
　　param_name参数名称；type参数的类型，该类型可以是MySQL中的任意类型
RETURNS type表示函数返回数据类型characteristic指定存储函数的特性，取值和与创建存储过程相同　　创建存储函数，名称为NameByZip，该函数返回SELECT语句的查询结果，数值类型为字符串型
mysql> DELIMITER //　　

　　
mysql>  CREATE FUNCTION NameByZip()
　　
    ->  RETURNS CHAR(50)
　　
    ->  RETURN   (SELECT s_name FROM suppliers WHERE s_call= '48075');
　　
    ->  //
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
mysql> DELIMITER ;
　　变量的使用
　　在存储过程中使用DECLARE语句定义变量，语法格式为：
DECLARE var_name[,varname]... date_type [DEFAULT value];　　定义名称为myparam的变量，类型为INT类型，默认值为100
DECLARE  myparam  INT  DEFAULT 100;　　定义变量后，为变量赋值可以改变变量的默认信息，MySQL使用SET语句为变量赋值，语法格式为：
SET var_name = expr [,var_name=expr]...　　声明3个变量，分别为var1、var2和var3，数据类型为INT，使用SET为变量赋值
DECLARE var1, var2, var3 INT;　　
SET var1 = 10, var2 = 20;
　　
SET var3 = var1 + var2;
　　MySQLhankeyishiyong SELECT...INTO为一个或多个变量赋值，语法为：
SELECT col_name[,...] INTO var_name[,...] table_expr;　　声明变量fruitname和fruitprice，通过SELECT ... INTO语句查询指定记录并为变量赋值
DECLARE fruitname CHAR(50);　　
DECLARE fruitprice DECIMAL(8,2);
　　

　　
SELECT f_name,f_price INTO fruitname, fruitprice
　　
FROM fruits WHERE f_id ='a1';
　　定义条件和处理程序
　　特定条件需要特定处理。这些条件可以联系到错误，以及子程序的一般流程控制。定义条件是事先定义程序执行过程中遇到的问题，处理程序定义了在遇到这些问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。使用DECLARE关键字来定义条件和处理程序。定义条件的语法格式为：
DECLARE condition_name CONDITION FOR [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为数值类型错误代码
　　定义"ERROR 1148(42000)"错误，名称为command_not_allowed
//方法一：使用sqlstate_value　　
DECLARE  command_not_allowed CONDITION FOR SQLSTATE '42000';
　　

　　
//方法二：使用mysql_error_code
　　
DECLARE  command_not_allowed CONDITION  FOR  1148
　　
　　定义处理程序时，使用DECLARE语句的语法为：
DECLARE handler_action HANDLER FOR condition_value statement　　

　　
handler_action:
　　
    CONTINUE| EXIT| UNDO
　　

　　
condition_value:
　　
    mysql_error_code
　　
  | SQLSTATE [VALUE] sqlstate_value
　　
  | condition_name
　　
  | SQLWARNING
　　
  | NOT FOUND
　　
  | SQLEXCEPTION
　　handler_action:处理错误方式，参数有3个取值：CONTINUE，EXIT，UNDO。
　　CONTINUE表示遇到错误不处理，继续执行
　　EXIT遇到错误马上退出
　　UNDO遇到错误后撤回之前的操作
condition_value表示错误类型　　SQLSTATE [VALUE] sqlstate_value：包含5个字符的字符串错误值
　　condition_name：DECLARE CONDITION定义的错误条件名称
　　SQLWARNING：匹配所有01开头的SQLSTATE错误代码
　　NOT FOUND：匹配所有02开头的SQLSTATE错误代码
　　SQLEXCEPTION：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码
　　mysql_error_code：匹配数值类型错误代码
statement参数为程序语句段，表示遇到定义的错误时，需要执行的存储过程或函数　　定义处理程序的几种方式
//方法一：捕获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=' NO_SUCH_TABLE ';
　　

　　
//方法四：使用SQLWARNING
　　
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';
　　

　　
//方法五：使用NOT FOUND
　　
DECLARE EXIT HANDLER FOR NOT FOUND SET @info=' NO_SUCH_TABLE ';
　　

　　
//方法六：使用SQLEXCEPTION
　　
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';
　　定义条件和处理程序
mysql> CREATE TABLE test.t (s1 int,primary key (s1));　　
Query OK, 0 rows affected (0.05 sec)
　　

　　
mysql> DELIMITER //
　　

　　
mysql> CREATE PROCEDURE handlerdemo ()
　　
    ->       BEGIN
　　
    ->        DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
　　
    ->        SET @x = 1;
　　
    ->        INSERT INTO test.t VALUES (1);
　　
    ->        SET @x = 2;
　　
    ->        INSERT INTO test.t VALUES (1);
　　
    ->        SET @x = 3;
　　
    ->      END;
　　
    ->      //
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
mysql>  DELIMITER ;
　　

　　
mysql> CALL handlerdemo();         /*调用存储过程*/
　　
Query OK, 0 rows affected, 1 warning (0.02 sec)
　　

　　
mysql> SELECT @x;                  /*查看调用过程结果*/
　　
+------+
　　
| @x   |
　　
+------+
　　
|    3 |
　　
+------+
　　
1 row in set (0.00 sec)
　　'@var_name'表示用户变量，使用SET语句为其赋值，用户与连接有关，一个客户端定义的变量不能被其他客户端看到或使用。客户端退出时，该客户端连接的所有变量自动释放。
　　光标的使用
　　查询语句可能返回多条记录，如果数据非常大，需要在存储过程和存储函数中使用光标来逐条读取查询结果集中的记录。光标必须在声明处理程序之前被声明，并且变量和条件还必须在声明光标或处理程序之前声明。MySQL中光标只能在存储过程和函数中使用。
　　MySQL中使用DECLARE关键字声明光标，语法基本形式为：
DECLARE cursor_name CURSOR FOR select_statement　　cursor_name表示光标名称
　　select_statement表示SELECT语句的内容
　　返回一个用户创建光标的结果集
　　声明名称为cursor_fruit的光标
DECLARE cursor_fruit CURSOR FOR SELECT f_name, f_price FROM fruits ;　　打开光标的语法为：
OPEN cursor_name [光标名称]　　打开名称为cursor_fruit的光标
OPEN cursor_fruit　　使用光标的语法为：
FETCH cursor_name INTO var_name [,var_name] ...[参数名称]　　使用名称为cursor_fruit的光标。将查询出来的数据存入fruit_name和fruit_price这两个变量
FETCH  cursor_fruit INTO fruit_name, fruit_price ;　　关闭名称为cursor_fruit的光标
CLOSE  cursor_fruit;　　
　　流程控制的使用
　　流程控制语句用来控制条件语句的执行。MySQL终于来控制流程的于具有IF、CASE、LOOP、LEAVE、ITERATE、REPEAT和WHERE语句。

• 　　IF
  

　　IF语句包含多个条件判断，根据判断的结果为TRUE或FALSE执行相应的语句，语法格式为：
IF search_condition THEN statement_list　　
    [ELSEIF search_condition THEN statement_list] ...
　　
    [ELSE statement_list]
　　
END IF
　　IF语句的示例
IF val IS NULL　　
  THEN SELECT 'val is NULL';
　　
  ELSE SELECT 'val is not NULL';
　　
END IF;

• 　　CASE
  

　　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流程控制语句第1种格式，判断val值等于1、等于2，或者两者都不等
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;
　　
当val值为1时，输出字符串“val is 1”；当val值为2时，输出字符串“val is 2”；否则输出字符
　　
串“val is not 1 or 2”。
　　使用CASE流程控制语句的第2种格式，判断val是否为空、小于0、大于0或者等于0
CASE　　
  WHEN val IS NULL THEN SELECT 'val is NULL';
　　
  WHEN val < 0 THEN SELECT 'val is less than 0';
　　
  WHEN val > 0 THEN SELECT 'val is greater than 0';
　　
  ELSE SELECT 'val is 0';
　　
END CASE;
　　
当val值为空，输出字符串“val is NULL”；当val值小于0时，输出字符串“val is less than 0”；
　　
当val值大于0时，输出字符串“val is greater than 0”；否则输出字符串“val is 0”。

• 　　LOOP
  

　　LOOP循环语句用来重复执行某些语句，与IF和CASE相比，LOOP只是创建一个循环的过程，并不进行条件判断。LOOP内的语句一直重复只i系那个知道循环被退出，跳出循环过程使用LEAVE子句，LOOP语法格式为：
[begin_label:] LOOP　　
    statement_list
　　
END LOOP [end_label]
　　使用LOOP语句进行循环操作，id值小于等于10之前，将重复执行循环过程
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;

• 　　LEAVE
  

　　LEAVE语句用来退出任何被标注的流程控制构造，其语法格式为：
LEAVE label　　使用LEAVE语句退出循环
add_num: LOOP　　
SET @count=@count+1;
　　
IF @count=50 THEN LEAVE add_num ;
　　
END LOOP add_num ;

• 　　ITERATE
  

　　ITERATE语句将执行顺序转到语句段开头处，语法格式为：
ITERATE label　　ITERATE只可以出现在LOOP、REPEAT、WHERE语句内。
　　ITERATE语句示例
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语句创建一个带条件判断的循环过程，每次语句执行完毕之后，会对条件表达式进行判断，如果为真，则结束循环，其语法格式为：
[begin_label:] REPEAT　　
    statement_list
　　
UNTIL search_condition
　　
END REPEAT [end_label]
　　REPEAT语句示例，id值小于等于10之前，将重复执行循环过程
DECLARE id INT DEFAULT 0;　　
REPEAT
　　
SET id = id + 1;
　　
UNTIL  id >= 10
　　
END REPEAT;

• 　　WHILE
  

　　WHILE语句创建一个带条件判断的循环过程，与REPEAT不同，WHERE在执行语句时，先对指定表达式进行判断，如果为真，则执行循环的语句，其语法格式为：
[begin_label:] WHILE search_condition DO　　
    statement_list
　　
END WHILE [end_label]
　　WHILE语句示例，id值小于等于10之前，将重复执行循环过程
DECLARE i INT DEFAULT 0;　　
WHILE i < 10 DO
　　
SET i = i + 1;
　　
END WHILE;
　　9.2、调用存储过程和函数
　　调用存储过程
　　存储过程通过CALL语句进行调用，语法格式为：
CALL sp_name([parameter[,...]])　　定义名为CountProc1的存储过程，然后调用这个存储过程
mysql> DELIMITER //　　
mysql> CREATE PROCEDURE CountProc1 (IN sid INT, OUT num INT)
　　
    -> BEGIN
　　
    -> SELECT COUNT(*) INTO num FROM fruits WHERE s_id = sid;
　　
    -> END //
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
mysql> DELIMITER ;
　　
mysql> CALL CountProc1 (101, @num);
　　
Query OK, 1 row affected (0.00 sec)
　　

　　
mysql> select @num;
　　
+------+
　　
| @num |
　　
+------+
　　
|    3 |
　　
+------+
　　
1 row in set (0.02 sec)
　　调用存储函数
　　定义存储函数CountProc2，然后调用这个函数
mysql> DELIMITER //　　
mysql> CREATE FUNCTION  CountProc2 (sid INT)
　　
    -> RETURNS INT
　　
    -> BEGIN
　　
    -> RETURN (SELECT COUNT(*) FROM fruits WHERE s_id = sid);
　　
    -> END //
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
mysql> DELIMITER ;
　　

　　
mysql> SELECT CountProc2(101);
　　
+--------------------+
　　
| Countproc(101) |
　　
+--------------------+
　　
|             3 |
　　
+-------------------+
　　9.3、查看存储过程和函数
　　MySQL存储了存储过程和函数的状态信息，用户可以使用SHOW STATUS语句或SHOW CREATE语句查看，也可以直接从系统information_schema数据库中查看。
　　使用SHOW STATUS语句可以查看存储过程和函数状态，基本语法为：
SHOW [PROCEDURE | FUNCTION] STATUS [LIKE 'pattern']　　SHOW STATUS语句示例
mysql> SHOW PROCEDURE STATUS LIKE 'C%'\G　　
*************************** 1. row ***************************
　　
                  Db: test
　　
                Name: CountProc
　　
                Type: PROCEDURE
　　
             Definer: root@localhost
　　
            Modified: 2017-08-04 11:32:08
　　
             Created: 2017-08-04 11:32:08
　　
       Security_type: DEFINER
　　
             Comment:
　　
character_set_client: utf8
　　
collation_connection: utf8_general_ci
　　
  Database Collation: utf8_general_ci
　　
1 row in set (0.00 sec)
　　使用SHOW CREATE语句可以查看存储过程和函数状态，基本语法为：
SHOW CREATE [PROCEDURE | FUNCTION] sp_name　　SHOW CREATE语句示例
mysql> SHOW CREATE PROCEDURE test.CountProc \G　　
*************************** 1. row ***************************
　　
           Procedure: CountProc
　　
            sql_mode:
　　
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `CountProc`(OUT param1 INT)
　　
BEGIN
　　
  SELECT COUNT(*) INTO param1 FROM fruits;
　　
  END
　　
character_set_client: utf8
　　
collation_connection: utf8_general_ci
　　
  Database Collation: utf8_general_ci
　　
1 row in set (0.00 sec)
　　MySQL中存储过程和函数的信息存储在information_schema数据库下的Routines表中，可以通过查询该表的记录来查询存储过程和函数，其基本语法为：
SELECT * FROM information_schema.Routines　　
  WHERE ROUTINE_NAME= 'sp_name';
　　从Routines表中查询名称为CountProc的存储过程的信息
mysql> SELECT * FROM information_schema.Routines　　
    -> WHERE ROUTINE_NAME='CountProc'  AND  ROUTINE_TYPE = 'PROCEDURE' \G
　　
*************************** 1. row ***************************
　　
           SPECIFIC_NAME: CountProc
　　
         ROUTINE_CATALOG: def
　　
          ROUTINE_SCHEMA: test
　　
            ROUTINE_NAME: CountProc
　　
            ROUTINE_TYPE: PROCEDURE
　　
               DATA_TYPE:
　　
CHARACTER_MAXIMUM_LENGTH: NULL
　　
  CHARACTER_OCTET_LENGTH: NULL
　　
       NUMERIC_PRECISION: NULL
　　
           NUMERIC_SCALE: NULL
　　
      CHARACTER_SET_NAME: NULL
　　
          COLLATION_NAME: NULL
　　
          DTD_IDENTIFIER: NULL
　　
            ROUTINE_BODY: SQL
　　
      ROUTINE_DEFINITION: BEGIN
　　
  SELECT COUNT(*) INTO param1 FROM fruits;
　　
  END
　　
           EXTERNAL_NAME: NULL
　　
       EXTERNAL_LANGUAGE: NULL
　　
         PARAMETER_STYLE: SQL
　　
        IS_DETERMINISTIC: NO
　　
         SQL_DATA_ACCESS: CONTAINS SQL
　　
                SQL_PATH: NULL
　　
           SECURITY_TYPE: DEFINER
　　
                 CREATED: 2017-08-04 11:32:08
　　
            LAST_ALTERED: 2017-08-04 11:32:08
　　
                SQL_MODE:
　　
         ROUTINE_COMMENT:
　　
                 DEFINER: root@localhost
　　
    CHARACTER_SET_CLIENT: utf8
　　
    COLLATION_CONNECTION: utf8_general_ci
　　
      DATABASE_COLLATION: utf8_general_ci
　　
1 row in set (0.00 sec)
　　9.4、修改存储过程和函数
　　使用ALTER语句可以修改存储过程或函数的特性，基本语法为：
ALTER [PROCEDURE | FUNCTION ] sp_name [characteristic ...]　　修改存储过程CountProc的定义。将读写权限改为MODIFIES SQL DATA，并指明调用者可以执行
mysql> ALTER  PROCEDURE  CountProc　　
    ->      MODIFIES SQL DATA
　　
    ->      SQL SECURITY INVOKER ;
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
mysql> SELECT SPECIFIC_NAME,SQL_DATA_ACCESS,SECURITY_TYPE
　　
    ->       FROM information_schema.Routines
　　
    ->      WHERE ROUTINE_NAME='CountProc' AND ROUTINE_TYPE='PROCEDURE';
　　
+---------------+-------------------+---------------+
　　
| SPECIFIC_NAME | SQL_DATA_ACCESS   | SECURITY_TYPE |
　　
+---------------+-------------------+---------------+
　　
| CountProc     | MODIFIES SQL DATA | INVOKER       |
　　
+---------------+-------------------+---------------+
　　
1 row in set (0.00 sec)
　　9.5、删除存储过程和函数
　　删除存储过程和函数，可以使用DROP语句，其语法格式为：
DROP[PROCEDURE | FUNCTION ] [IF EXISTS] sp_name　　删除存储过程和存储函数
mysql> DROP PROCEDURE CountProc;　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
mysql> DROP FUNCTION CountProc;
　　
Query OK, 0 rows affected (0.00 sec)