【开发篇】MySQL的存储过程和函数的操作

  MySQL 从 5.0 版本开始支持存储过程和函数。
  存储过程和函数是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

  存储过程和函数的区别在于函数必须有返回值，而存储过程没有，存储过程的参数可以使用IN、OUT、INOUT 类型，而函数的参数只能是 IN 类型的。如果有函数从其他类型的数据库迁移到 MySQL，那么就可能因此需要将函数改造成存储过程。

  MySQL 的存储过程和函数中允许包含 DDL 语句，也允许在存储过程中执行提交（Commit，即确认之前的修改）或者回滚（Rollback，即放弃之前的修改），但是存储过程和函数中不允许执行 LOAD DATA INFILE 语句。此外，存储过程和函数中可以调用其他的过程或者函数。

  在对存储过程或函数进行操作时，需要首先确认用户是否具有相应的权限。例如，创建存储过程或者函数需要 CREATE ROUTINE 权限，修改或者删除存储过程或者函数需要 ALTER ROUTINE 权限，执行存储过程或者函数需要 EXECUTE 权限。

1、创建

存储过程创建语法：

delimiter //
create procedure 储存名（[in，out，inout]参数名 数据类形……）
begin
  sql语句
end
//
delimiter；

函数创建语法：

delimiter //
CREATE FUNCTION sp_name ([in]参数名 数据类形……])
RETURNS type
begin
  sql语句
end
//
delimiter；

-- 示例

DELIMITER //

CREATE PROCEDURE film_in_stock(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
READS SQL DATA
BEGIN
	SELECT inventory_id
	FROM inventory
	WHERE film_id = p_film_id
	AND store_id = p_store_id
	AND inventory_in_stock(inventory_id);
	
	SELECT FOUND_ROWS() INTO p_film_count;
END 
//
DELIMITER ;

  上面是在使用的样例数据库中创建的一个过程，该过程用来检查 film_id 和 store_id 对应的 inventory 是否满足要求，并且返回满足要求的 inventory_id 以及满足要求的记录数。

代码说明：

• “DELIMITER //” 将语句的结束符从 “;” 变成 “//” ，也可以是你想要的任何符号，这样的目的是为了存储过程中的 “;” 不被当成结束语句而提示错误。
• READS SQL DATA ：表示子程序包含读数据的语句，但不包含写数据的语句。mysql创建存储过程的时候还有三个这样的数据存取限制的参数，分别是CONTAINS SQL（表示子程序不包含读或写数据的语句），NO SQL（子程序不包含SQL语句），MODIFIES SQL DATA（表示子程序包含写数据的语句），默认的是CONTAINS SQL。，这些特征值目前只是提供给服务器，并没有根据这些特征值来约束过程实际使用数据的情况。
• 存储过程的参数：支持三种，in，out，inout ，三种参数的说明：in参数：默认参数形式，传入参数，该参数的值必须在调用存储过程事指定。out参数：传出参数，不能用于传入参数值，调用存储过程时，out参数也需要指定，但必须是变量，不能是常量。inout参数：可变变量，调用时可传入值，在调用过程中，可修改其值，同时也可返回值，调用时传入的是变量，而不是常量；
• inventory_in_stock：这是存储过程调用其他的函数（或者存储过程）。

  另外，和视图的创建语法稍有不同，存储过程和函数的 CREATE 语法不支持使用 CREATE OR REPLACE 对存储过程和函数进行修改，如果需要对已有的存储过程或者函数进行修改，需要执行 ALTER 语法。

2、调用

函数和存储过程的调用方式一致，如下：
CALL sp_name([parameter[,…]])

-- 调用上面创建的存储过程

 CALL film_in_stock(2,2,@a)
 +--------------+
| inventory_id |
+--------------+
| 10 |
| 11 |
+--------------+
2 rows in set (0.00 sec)

-- 存储过程的输出型变量
 select @a;
 +------+
| @a |
+------+
| 2 |
+------+
1 row in set (0.00 sec)

3、查看

  存储过程或者函数创建后，用户可能需要查看存储过程或者函数的状态或者定义等信息，便于了解存储过程或者函数的基本情况。下面将介绍如何查看存储过程或函数相关信息。

1.状态查看

语法：
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE ‘pattern’]

-- 示例：
show procedure status like 'film_in_stock;

*************************** 1. row ***************************
 Db: sakila
 Name: film_in_stock
 Type: PROCEDURE
 Definer: root@localhost
 Modified: 2007-07-06 09:29:00
 Created: 2007-07-06 09:29:00
Security_type: DEFINER
 Comment: 
1 row in set (0.00 sec)

2.定义查看

语法：
SHOW CREATE {PROCEDURE | FUNCTION} sp_name

-- 示例：

show create procedure film_in_stock;

*************************** 1. row ***************************
 Procedure: film_in_stock
 sql_mode: 
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `film_in_stock`(IN p_film_id 
INT, IN p_store_id INT, OUT p_film_count INT)
 READS SQL DATA
BEGIN
 SELECT inventory_id
 FROM inventory
 WHERE film_id = p_film_id
 AND store_id = p_store_id
 AND inventory_in_stock(inventory_id);
 SELECT FOUND_ROWS() INTO p_film_count;
END
1 row in set (0.00 sec)
 

3.信息查看

  除了以上两种方法，我们还可以查看系统表来了解存储过程和函数的相关信息，通过查看information_schema. Routines 就可以获得存储过程和函数的包括名称、类型、语法、创建人等信息。

-- 例如，通过查看 information_schema. Routines 得到过程 film_in_stock 的定义：

select * from routines where ROUTINE_NAME = 'film_in_stock';

4、删除

  一次只能删除一个存储过程或者函数，删除存储过程或者函数需要有该过程或者函数的ALTER ROUTINE 权限，具体语法如下：

删除存储过程或者函数的语法：
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

-- 示例：删除存储过程film_in_stock

DROP PROCEDURE film_in_stock;

5、变量的使用

  存储过程和函数中可以使用变量，而且在 MySQL 5.1 版本之后，变量是不区分大小写的。

1.变量的定义

  通过 DECLARE 可以定义一个局部变量，该变量的作用范围只能在 BEGIN…END 块中，可以用在嵌套的块中。变量的定义必须写在复合语句的开头，并且在任何其他语句的前面。可以一次声明多个相同类型的变量。如果需要，可以使用 DEFAULT 赋默认值。

语法：
DECLARE var_name[,…] type [DEFAULT value]

-- 示例1：定义date类型的变量last_month_start 

DECLARE last_month_start DATE;

-- 示例2：一次定义多个同类型变量,并指定默认值

DECLARE count_id,count_name int DEFAULT 0;

2.变量的赋值

  变量可以直接赋值，或者通过查询赋值。直接赋值使用 SET，可以赋常量或者赋表达式，具体语法如下：

语法：
SET var_name = expr [, var_name = expr] …

-- 示例：
SET last_month_start = '1999-12-31';
SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);

  也可以通过查询将结果赋给变量，这要求查询返回的结果必须只有一行，具体语法如下：

语法：
SELECT col_name[,…] INTO var_name[,…] table_expr

DELIMITER //
CREATE FUNCTION get_customer_balance(p_customer_id INT, p_effective_date DATETIME) 
RETURNS DECIMAL(5,2)
DETERMINISTIC
READS SQL DATA
BEGIN
	 DECLARE v_payments DECIMAL(5,2); #SUM OF PAYMENTS MADE PREVIOUSLY
	 
	 SELECT IFNULL(SUM(payment.amount),0) INTO v_payments
	 FROM payment
	 WHERE payment.payment_date <= p_effective_date
	 AND payment.customer_id = p_customer_id;

	 RETURN v_rentfees + v_overfees - v_payments;
END 
//

DELIMITER ;

3.MySQL中的其他变量

  上面讲解的变量叫局部变量，也就是说，只能在函数或者存储过程中使用。而MySQL还有两个两个变量。分别是用户变量和全局变量。

用户变量：

语法：
@var_name    不需要提前声明，使用即声明

  它的使用范围也可以算是在函数或者存储过程中，但有个问题是滥用用户变量会导致程序难以理解及管理

全局变量：

语法：
@@global.var_name

  由系统系统,在整个数据库中有效。

6、条件和处理

  在程序的运行过程中可能会遇到问题，此时我们可以通过定义条件和处理程序来事先定义这些问题。定义条件是指事先定义程序执行过程中遇到的问题，处理程序定义了在遇到这些问题时应当采取的处理方式和解决办法，保证存储过程和函数在遇到警告或错误时能继续执行，从而增强程序处理问题的能力，避免程序出现异常被停止执行，相当于python中的try…except。

1.条件的定义

语法：
 
DECLARE condition_name CONDITION FOR condition_value
 
condition_value:
SQLSTATE [VALUE] sqlstate_value| mysql_error_code

参数说明：
 
cond_name：条件名称
condition_value：条件类型，用于定义MySQL的错误，SQLSTATE是长度为5的字符串类型的错误代码；mysql_error_code是数值类型的错误代码。
例如 ERROR 1142（42000）
sql_state_value的值是’42000’，mysql_error_code的值是1142。

--示例：对错误ERROR 1148(42000)，定义条件如下：

-- 使用SQLSTATE
DECLARE cond_error CONDITION FOR SQLSTATE '42000';
-- 使用mysql_error_code
DECLARE cond_error CONDITION FOR 1148;

2.条件的处理

条件处理的语法：
DECLARE handler_type HANDLER FOR condition_value[,…] sp_statement
 
 
1、handler_type：指定错误处理方式
  CONTINUE：遇到错误不处理，继续执行
  EXIT：遇到错误立即退出
   UNDO：遇到错误撤回之前的操作
 
2、condition_value：表示错误类型
   SQLSTATE [VALUE] sqlstate_value：包含5个字符的字符串错误值
   condition_name：定义条件的名称，就是前面定义的条件的名称
   SQLWARNING：匹配所有以01开头的SQLSTATE错误代码
   NOT FOUND：匹配所有以02开头的SQLSTATE错误代码
   SQL EXCEPTION：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码
   mysql_error_code：包含5个字符的字符串错误值
 
3、sp_statement：程序语句段，表示在遇到定义的错误时执行的存储过程或函数。

  下面将通过两个例子来说明：在向 actor 表中插入记录时，如果没有进行条件的处理，那么在主键重的时候会抛出异常并退出，如果对条件进行了处理，那么就不会再抛出异常。顺便理解前面讲解的各种东西的使用。

-- 当不设置条件处理时
delimiter $$

CREATE PROCEDURE actor_insert ()
BEGIN
	SET @x = 1;
	INSERT INTO actor(actor_id,first_name,last_name) VALUES (201,'Test','201');
	SET @x = 2;
	INSERT INTO actor(actor_id,first_name,last_name) VALUES (1,'Test','1');
	SET @x = 3;
END
$$
delimiter ;

--调用上面定义的存储过程
call actor_insert();

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

--通过定义的用户变量检查问题出在哪里
mysql> select @x;
+------+
| @x |
+------+
| 2 |
+------+
1 row in set (0.00 sec)

  从上面的例子可以看出，执行到插入 actor_id=1 的记录时，会主键重复并退出，没有执行到下面其他的语句。

-- 设置条件处理
delimiter $$

CREATE PROCEDURE actor_insert ()
BEGIN
	declare continue HANDLER FOR SQLSTATE '23000' set @x2 = 1;
	SET @x = 1;
	INSERT INTO actor(actor_id,first_name,last_name) VALUES (201,'Test','201');
	SET @x = 2;
	INSERT INTO actor(actor_id,first_name,last_name) VALUES (1,'Test','1');
	SET @x = 3;
END
$$
delimiter ;

--调用上面定义的存储过程
call actor_insert();
Query OK, 0 rows affected (0.06 sec)

--通过定义的用户变量检查判断存储过程执行的情况
select @x,@x2;
+------+------+
| @x | @x2 |
+------+------+
| 3 | 1 |
+------+------+
1 row in set (0.00 sec)

  从上面的例子的用户变量检查可以看出，虽然执行到插入 actor_id=1 的记录时，主键重复出现异常，但由于定义了条件处理（例子中时continue ，遇到错误继续执行），所以 用户变量@x,@x2的值分别是3和1。

7、光标的使用

  查询语句时，可能查询出多条记录，在存储过程和函数中使用光标来逐条读取查询结果集中的记录。有些书上将光标称为游标。光标的使用包括声明光标（DECLARE）、打开光标（OPEN）、使用光标（FETCH）和关闭光标（CLOSE）。光标必须声明在处理程序之前，并且声明在变量和条件之后。光标可以类比为数组内部中的循环指针。其语法分别如下。

1、声明光标：
DECLARE cursor_name CURSOR FOR select_statement；

参数说明：
cursor_name ：光标的名称
select_statement：表示SELECT语句的内容，返回一个用于创建光标的结果集
 
示例：
DECLARE cur_student CURSOR FOR SELECT id, name FROM student;

 
2、OPEN 光标：
OPEN cursor_name
 
3、FETCH 光标：
FETCH cursor_name INTO var_name [, var_name] …

参数说明：
cursor_name ：光标的名称
var_name ：var_name参数表示将光标中的SELECT语句查询出来的信息存入该参数中。var_name必须在声明光标之前就定义好。

 
4、CLOSE 光标：关闭之后就不能使用FETCH来使用光标了。
CLOSE cursor_name

光标使用示例：
 现有表，形如：

-- 创建存储过程
delimiter //

create Procedure student_info() -- 无参存储过程
READS SQL DATA
begin
	declare stu_id int;
	declare stu_name varchar(200);

	declare cur_student cursor for select id,name from students; -- 定义游标
	declare exit handler for not found  close  cur_student;-- 定义条件处理：找不到时关闭游标退出
	
	set @id_ji = '';
	set @id_ou = '';
	set @name_ji = '';
	set @name_ou = '';

	open cur_student;--打开游标
	REPEAT
	    FETCH cur_student INTO stu_id,stu_name;-- 使用游标
	    IF stu_id%2=1 THEN
		    SET  @id_ji = @id_ji + stu_id;
			SET  @name_ji =CONCAT(@name_ji,' ',stu_name);
	    ELSE
		    SET  @id_ou = @id_ou + stu_id;
			SET  @name_ou =CONCAT(@name_ou,' ',stu_name);
	    END IF;
	UNTIL 0 END REPEAT;
	
CLOSE cur_student;-- 关闭游标
		END 
		//
DELIMITER ;	

-- 调用
CALL student_info();

-- 结果查看
SELECT @id_ji,@id_ou,@name_ji,@name_ou;

 结果输出，形如：

注意：
  变量、条件、条件处理、光标都是通过 DECLARE 定义的，它们之间是有先后顺序的要求的。变量和条件必须在最前面声明，然后才能是光标的声明，最后才可以是条件处理的声明。

8、流程控制

  流程控制语句用于根据条件控制语句的执行。MySQL中用于构造控制流程的语句有 IF 语句、CASE 语句、LOOP 语句、WHILE 语句、LEAVE 语句、ITERATE 语句、REPEAT 语句。

1.if语句

  IF 实现条件判断，满足不同的条件执行不同的语句列表，具体语法如下：

语法：
IF search_condition THEN statement_list
  [ELSEIF search_condition THEN statement_list] …
  [ELSE statement_list]
END IF

  IF 实现了一个基本的条件构造。如果 expr_condition 求值为真 (TRUE), 相应的 SQL 语句列表被执行; 如果没有 expr_condition 匹配，则 ELSE 子句里的语句列表被执行。statement_list 可以包括一个或多个语句。

  MySQL中还有一个 IF() 函数，它不同于 IF 语句。

2.case语句

  CASE 实现比 IF 更复杂一些的条件构造，具体语法如下：

语法：
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语句实现

case
	when stu_id%2=1 THEN
		    SET  @id_ji = @id_ji + stu_id;
			SET  @name_ji =CONCAT(@name_ji,' ',stu_name);
	ELSE
		SET  @id_ou = @id_ou + stu_id;
		SET  @name_ou =CONCAT(@name_ou,' ',stu_name);
END case;

--或者
case mod(stu_id,2)
	when 1 THEN
	    SET  @id_ji = @id_ji + stu_id;
		SET  @name_ji =CONCAT(@name_ji,' ',stu_name);
	ELSE
		SET  @id_ou = @id_ou + stu_id;
		SET  @name_ou =CONCAT(@name_ou,' ',stu_name);
END case;

3.loop语句

  LOOP 实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用 LEAVE 语句实现，具体语法如下：

语法：
[begin_label:] LOOP
  statement_list
END LOOP [end_label]

示例：

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;

  如果不在 statement_list 中增加退出循环的语句，那么 LOOP 语句可以用来实现简单的死循环。

4.leave语句

  leave语句用来从标注的流程构造中退出，通常和 BEGIN … END 或者循环一起使用。语法如下：

语法：其中label 参数表示循环的标志
LEAVE lable

  示例见loop循环示例。

5.iterate语句

  ITERATE 语句必须用在循环中，作用是跳过当前循环的剩下的语句，直接进入下一轮循环。类似python中break。

示例：

delimiter //

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
//

delimiter ;

6.repeat语句

  REPEAT 语句创建一个带条件判断的循环过程，每次语句执行完毕之后，会对条件表达式进行判断，如果表达式为真，则循环结束 ; 否则重复执行循环中的语句。 REPEAT 语句的基本格式如下:

语法：
[begin_label:] REPEAT
  statement_list
UNTIL search_condition
END REPEAT [end_label]

示例：

-- 例子1：
REPEAT
	    FETCH cur_student INTO stu_id,stu_name;-- 使用游标
	    IF stu_id%2=1 THEN
		    SET  @id_ji = @id_ji + stu_id;
			SET  @name_ji =CONCAT(@name_ji,' ',stu_name);
	    ELSE
		    SET  @id_ou = @id_ou + stu_id;
			SET  @name_ou =CONCAT(@name_ou,' ',stu_name);
	    END IF;
	UNTIL 0 
END REPEAT;

--例子2 id 值小于10, 将重复执行循环过程
DECLARE id INT DEFAULT 0;
REPEAT
	SET id = id + 1;
UNTIL  id >= 10
END REPEAT;

7.while语句

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

语法：
[begin_label:] WHILE search_condition DO
  statement_list
END WHILE [end_label]

示例: id 值小于10, 将重复执行循环过程

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