九、MySQL存储过程和函数

 存储过程就是一条或者多条SQL语句的集合，可视为批文件，但是其作用不仅限于批处理。

9.1、创建存储过程和函数

  存储程序可以分为存储过程和函数，MySQL中创建存储过程和函数使用的语句分别是CREATE PROCEDURE和CREATE FUNCTION。使用CALL语句来调用存储过程，只能用输出变量返回值。函数可以从语句外调用( 即通过引用函数名)，也能返回标量值。存储过程也能调用其他存储过程。

创建存储过程

  创建存储过程，需要使用CREATE PROCEDURE语句，基本语法为：

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

创建查看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

创建存储函数，名称为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

定义"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

定义处理程序的几种方式

//方法一：捕获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_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)