深入浅出MySQL-开发篇-存储过程和函数

MySQL从5.0版本开始支持存储过程和函数。

12.1什么是存储过程和函数

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

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

12.2存储过程和函数的相关操作

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

12.2.1 创建、修改存储过程或者函数

创建、修改存储过程或者函数的语法：

CREATE PROCEDURE sp_name ([proc_parameter[,...]])

[characteristic ...] routine_body

CREATE FUNCTION sp_name ([func_parameter[,...]])

RETURNS type

[characteristic ...] routine_body

proc_parameter:

[ IN OUT INOUT ] param_name type

func_parameter:

param_name type

type:

Any valid MySQL data type

characteristic:

LANGUAGE SQL

[NOT] DETERMINISTIC

{ CONTAINS SQL NO SQL READS SQL DATA MODIFIES SQL DATA }

SQL SECURITY { DEFINER INVOKER }

COMMENT 'string'

routine_body:

Valid SQL procedure statement or statements

ALTER sp_name [characteristic ...]

characteristic:

{ CONTAINS SQL NO SQL READS SQL DATA MODIFIES SQL DATA }

SQL SECURITY { DEFINER INVOKER }

COMMENT 'string'

调用过程的语法如下：

CALL sp_name([parameter[,...]])

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

下面创建了一个新的过程film_in_stock：

mysql> DELIMITER $$

mysql>

mysql> 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 $$

Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> DELIMITER ;

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

通常我们在执行创建过程和函数之前，都会通过“DELIMITER $$”命令将语句的结束符从“;”修改成其他符号，这里使用的是“$$”，这样在过程和函数中的“;”就不会被MySQL解释成语句的结束而提示错误。在存储过程或者函数创建完毕，通过“DELIMITER ;”命令再将结束符改回成“;”。

可以看到在这个过程中调用了函数inventory_in_stock()，并且这个过程有两个输入参数和一个输出参数。下面可以通过调用这个过程来看看返回的结果。

如果需要检查film_id=2 store_id=2对应的inventory的情况，则首先手工执行过程中的SQL语句，以查看执行的效果：

mysql> SELECT inventory_id

-> FROM inventory

-> WHERE film_id = 2

-> AND store_id = 2

-> AND inventory_in_stock(inventory_id);

+--------------+

inventory_id

+--------------+

10

11

+--------------+

2 rows in set (0.00 sec)

满足条件的记录应该是两条，inventory_id分别是10和11。如果将这个查询封装在存储过程中调用，那么调用过程的执行情况如下：

mysql> CALL film_in_stock(2,2,@a);

+--------------+

inventory_id

+--------------+

10

11

+--------------+

2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select @a;

+------+

@a

+------+

2

+------+

1 row in set (0.00 sec)

可以看到调用存储过程与直接执行SQL的效果是相同的，但是存储过程的好处在于处理逻辑都封装在数据库端，调用者不需要了解中间的处理逻辑，一旦处理逻辑发生变化，只需要修改存储过程即可，而对调用者的程序完全没有影响。

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

下面对characteristic特征值的部分进行简单的说明。

LANGUAGE SQL：说明下面过程的BODY是使用SQL语言编写，这条是系统默认的，为今后MySQL会支持的除SQL外的其他语言支持的存储过程而准备。

[NOT] DETERMINISTIC：DETERMINISTIC确定的，即每次输入一样输出也一样的程序，NOT DETERMINISTIC非确定的，默认是非确定的。当前，这个特征值还没有被优化程序使用。

{ CONTAINS SQL NO SQL READS SQL DATA MODIFIES SQL DATA }：这些特征值提供子程序使用数据的内在信息，这些特征值目前只是提供给服务器，并没有根据这些特征值来约束过程实际使用数据的情况。CONTAINS SQL表示子程序不包含读或写数据的语句。NO SQL表示子程序不包含SQL语句。READS SQL DATA表示子程序包含读数据的语句，但不包含写数据的语句。MODIFIES SQL DATA表示子程序包含写数据的语句。如果这些特征没有明确给定，默认使用的值是CONTAINS SQL。

SQL SECURITY { DEFINER INVOKER }：可以用来指定子程序该用创建子程序者的许可来执行，还是使用调用者的许可来执行。默认值是DEFINER。

COMMENT 'string'：存储过程或者函数的注释信息。

下面的例子对比了SQL SECURITY特征值的不同，使用root用户创建了两个相似的存储过程，分别指定使用创建者的权限执行和调用者的权限执行，然后使用一个普通用户调用这两个存储过程，对比执行的效果：

首先用root用户创建以下两个存储过程film_in_stock_definer和film_in_stock_invoker：

mysql> DELIMITER $$

mysql>

mysql> CREATE PROCEDURE film_in_stock_definer(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)

-> SQL SECURITY DEFINER

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

Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> CREATE PROCEDURE film_in_stock_invoker(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)

-> SQL SECURITY INVOKER

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

Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> DELIMITER ;

给普通用户lisa赋予可以执行存储过程的权限，但是不能查询inventory表：

mysql> GRANT EXECUTE ON sakila.* TO 'lisa'@'localhost';

Query OK, 0 rows affected (0.00 sec)

使用lisa登录后，直接查询inventory表会提示查询被拒绝：

mysql> select count(*) from inventory;

ERROR 1142 (42000): SELECT command denied to user 'lisa'@'localhost' for table 'inventory'

lisa用户分别调用film_in_stock_definer和film_in_stock_invoker：

mysql> CALL film_in_stock_definer(2,2,@a);

+--------------+

inventory_id

+--------------+

10

11

+--------------+

2 rows in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> CALL film_in_stock_invoker(2,2,@a);

ERROR 1142 (42000): SELECT command denied to user 'lisa'@'localhost' for table 'inventory'

从上面的例子可以看出，film_in_stock_definer是以创建者的权限执行的，因为是root用户创建的，所以可以访问inventory表的内容，film_in_stock_invoker是以调用者的权限执行的，lisa用户没有访问inventory表的权限，所以会提示权限不足。

12.2.2 删除存储过程或者函数

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

DROP [IF EXISTS] sp_name

例如，使用DROP语法删除film_in_stock过程：

mysql> DROP PROCEDURE film_in_stock;

Query OK, 0 rows affected (0.00 sec)

12.2.3 查看存储过程或者函数

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

1．查看存储过程或者函数的状态

SHOW STATUS [LIKE 'pattern']

下面演示的是查看过程film_in_stock的信息：

mysql> show procedure status like 'film_in_stock'\G

********************************* 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 sp_name

下面演示的是查看过程film_in_stock的定义：

mysql> show create procedure film_in_stock \G

********************************* 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就可以获得存储过程和函数的包括名称、类型、语法、创建人等信息。

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

mysql> select * from routines where ROUTINE_NAME = 'film_in_stock' \G

********************************* 1. row *********************************

SPECIFIC_NAME: film_in_stock

ROUTINE_CATALOG: NULL

ROUTINE_SCHEMA: sakila

ROUTINE_NAME: film_in_stock

ROUTINE_TYPE: PROCEDURE

DTD_IDENTIFIER: NULL

ROUTINE_BODY: SQL

ROUTINE_DEFINITION: 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

EXTERNAL_NAME: NULL

EXTERNAL_LANGUAGE: NULL

PARAMETER_STYLE: SQL

IS_DETERMINISTIC: NO

SQL_DATA_ACCESS: READS SQL DATA

SQL_PATH: NULL

SECURITY_TYPE: DEFINER

CREATED: 2007-07-06 09:29:00

LAST_ALTERED: 2007-07-06 09:29:00

SQL_MODE:

ROUTINE_COMMENT:

DEFINER: root@localhost

1 row in set (0.00 sec)

12.2.4 变量的使用

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

1．变量的定义

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

定义一个变量的语法如下：

DECLARE var_name[,...] type [DEFAULT value]

例如，定义一个DATE类型的变量，名称是last_month_start：

DECLARE last_month_start DATE;

2．变量的赋值

变量可以直接赋值，或者通过查询赋值。

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

SET var_name = expr [, var_name = expr] ...

给刚才定义的变量last_month_start赋值，具体语法如下：

SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);

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

SELECT col_name[,...] INTO var_name[,...] table_expr

通过查询将结果赋值给变量v_payments：

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

AND payment.customer_id = p_customer_id;

…

RETURN v_rentfees + v_overfees - v_payments;

END $$

12.2.5 定义条件和处理

条件的定义和处理可以用来定义在处理过程中遇到问题时相应的处理步骤。

1．条件的定义

DECLARE condition_name CONDITION FOR condition_value

condition_value:

SQLSTATE [VALUE] sqlstate_value

mysql_error_code

2．条件的处理

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

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

（1）当没有进行条件处理时，执行结果如下：

mysql> select max(actor_id) from actor;

+---------------+

max(actor_id)

+---------------+

200

+---------------+

1 row in set (0.00 sec)

mysql> delimiter $$

mysql>

mysql> 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;

-> $$

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> 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的记录时，会主键重并退出，没有执行到下面其他的语句。

（2）当对主键重的异常进行处理时，执行结果如下：

mysql> delimiter $$

mysql>

mysql> 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;

-> $$

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call actor_insert();

Query OK, 0 rows affected (0.06 sec)

mysql> select @x,@x2;

+------+------+

@x @x2

+------+------+

3 1

+------+------+

1 row in set (0.00 sec)

调用条件处理的过程，再遇到主键重的错误时，会按照定义的处理方式进行处理，由于例子中定义的是CONTINUE，所以会继续执行下面的语句。

handler_type现在还只支持CONTINUE和EXIT两种，CONTINUE表示继续执行下面的语句，EXIT则表示执行终止，UNDO现在还不支持。

condition_value的值可以是通过DECLARE定义的condition_name，可以是SQLSTATE的值或者mysql-error-code的值或者SQLWARNING、NOT FOUND、SQLEXCEPTION，这3个值是3种定义好的错误类别，分别代表不同的含义。

SQLWARNING是对所有以01开头的SQLSTATE代码的速记。

NOT FOUND是对所有以02开头的SQLSTATE代码的速记。

SQLEXCEPTION是对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记。

因此，上面的例子还可以写成以下几种方式：

--捕获mysql-error-code：

DECLARE CONTINUE HANDLER FOR 1062 SET @x2 = 1;

--事先定义condition_name：

DECLARE DuplicateKey CONDITION FOR SQLSTATE '23000';

DECLARE CONTINUE HANDLER FOR DuplicateKey SET @x2 = 1;

--捕获SQLEXCEPTION

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @x2 = 1;

12.2.6 光标的使用

在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH和CLOSE，其语法分别如下。

声明光标：

DECLARE cursor_name CURSOR FOR select_statement

OPEN光标：

OPEN cursor_name

FETCH光标：

FETCH cursor_name INTO var_name [, var_name] ...

CLOSE光标：

CLOSE cursor_name

以下例子是一个简单的使用光标的过程，对payment表按照行进行循环的处理，按照staff_id值的不同累加amount的值，判断循环结束的条件是捕获NOT FOUND的条件，当FETCH光标找不到下一条记录的时候，就会关闭光标然后退出过程。

mysql> delimiter $$

mysql>

mysql> CREATE PROCEDURE payment_stat ()

-> BEGIN

-> DECLARE i_staff_id int;

-> DECLARE d_amount decimal(5,2);

-> DECLARE cur_payment cursor for select staff_id,amount from payment;

-> DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_payment;

->

-> set @x1 = 0;

-> set @x2 = 0;

->

-> OPEN cur_payment;

->

-> REPEAT

-> FETCH cur_payment INTO i_staff_id, d_amount;

-> if i_staff_id = 2 then

-> set @x1 = @x1 + d_amount;

-> else

-> set @x2 = @x2 + d_amount;

-> end if;

-> UNTIL 0 END REPEAT;

->

-> CLOSE cur_payment;

->

-> END;

-> $$

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql>

mysql> call payment_stat();

Query OK, 0 rows affected (0.11 sec)

mysql> select @x1,@x2;

+----------+----------+

@x1 @x2

+----------+----------+

33927.04 33489.47

+----------+----------+

1 row in set (0.00 sec)

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

12.2.7 流程控制

可以使用IF、CASE、LOOP、LEAVE、ITERATE、REPEAT及WHILE语句进行流程的控制，下面将逐一进行说明。

1．IF语句

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

IF search_condition THEN statement_list

[ELSEIF search_condition THEN statement_list] ...

[ELSE statement_list]

END IF

12.2.6小节中使用光标的例子中已经涉及了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

在上文光标的使用例子中，IF语句也可以使用CASE语句来完成：

case

when i_staff_id = 2 then

set @x1 = @x1 + d_amount;

else

set @x2 = @x2 + d_amount;

end case;

或者：

case i_staff_id

when 2 then

set @x1 = @x1 + d_amount;

else

set @x2 = @x2 + d_amount;

end case;

3．LOOP语句

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

[begin_label:] LOOP

statement_list

END LOOP [end_label]

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

4．LEAVE语句

用来从标注的流程构造中退出，通常和BEGIN ... END或者循环一起使用。

下面是一个使用LOOP和LEAVE的简单例子，循环100次向actor表中插入记录，当插入100条记录后，退出循环：

mysql> CREATE PROCEDURE actor_insert ()

-> BEGIN

-> set @x = 0;

-> ins: LOOP

-> set @x = @x + 1;

-> IF @x = 100 then

-> leave ins;

-> END IF;

-> INSERT INTO actor(first_name,last_name) VALUES ('Test','201');

-> END LOOP ins;

-> END;

-> $$

Query OK, 0 rows affected (0.00 sec)

mysql> call actor_insert();

Query OK, 0 rows affected (0.01 sec)

mysql> select count(*) from actor where first_name='Test';

+----------+

count(*)

+----------+

100

+----------+

1 row in set (0.00 sec)

5．ITERATE语句

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

下面的例子使用了ITERATE语句，当@x变量是偶数的时候，不再执行循环中剩下的语句，而直接进行下一轮的循环：

mysql> CREATE PROCEDURE actor_insert ()

-> BEGIN

-> set @x = 0;

-> ins: LOOP

-> set @x = @x + 1;

-> IF @x = 10 then

-> leave ins;

-> ELSEIF mod(@x,2) = 0 then

-> ITERATE ins;

-> END IF;

-> INSERT INTO actor(actor_id,first_name,last_name) VALUES (@x+200, 'Test',@x);

-> END LOOP ins;

-> END;

-> $$

Query OK, 0 rows affected (0.00 sec)

mysql> call actor_insert();

Query OK, 0 rows affected (0.00 sec)

mysql> select actor_id,first_name,last_name from actor where first_name='Test';

+----------+------------+-----------+

actor_id first_name last_name

+----------+------------+-----------+

201 Test 1

203 Test 3

205 Test 5

207 Test 7

209 Test 9

+----------+------------+-----------+

5 rows in set (0.00 sec)

6．REPEAT语句

有条件的循环控制语句，当满足条件的时候退出循环，具体语法如下：

[begin_label:] REPEAT

statement_list

UNTIL search_condition

END REPEAT [end_label]

在“12.2.6光标的使用”小节中的例子就使用REPEAT语句实现光标的循环获得，下面节选的代码就是其中使用REPEAT语句的部分，详细的执行过程请参照12.2.6小节，这里不再赘述。

-> REPEAT

-> FETCH cur_payment INTO i_staff_id, d_amount;

-> if i_staff_id = 2 then

-> set @x1 = @x1 + d_amount;

-> else

-> set @x2 = @x2 + d_amount;

-> end if;

-> UNTIL 0 END REPEAT;

7．WHILE语句

WHILE语句实现的也是有条件的循环控制语句，即当满足条件时执行循环的内容，具体语法如下：

[begin_label:] WHILE search_condition DO

statement_list

END WHILE [end_label]

WHILE循环和REPEAT循环的区别在于：WHILE是满足条件才执行循环，REPEAT是满足条件退出循环；WHILE在首次循环执行之前就判断条件，所以循环最少执行0次，而REPEAT是在首次执行循环之后才判断条件，所以循环最少执行1次。

以下例子用来对比REPEAT和WHILE语句的功能：

mysql> delimiter $$

mysql> CREATE PROCEDURE loop_demo ()

-> BEGIN

-> set @x = 1 , @x1 = 1;

-> REPEAT

-> set @x = @x + 1;

-> until @x > 0 end repeat;

->

-> while @x1 < 0 do

-> set @x1 = @x1 + 1;

-> end while;

-> END;

-> $$

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call loop_demo();

Query OK, 0 rows affected (0.00 sec)

mysql> select @x,@x1;

+------+------+

@x @x1

+------+------+

2 1

+------+------+

1 row in set (0.00 sec)

从判断的条件上看，初始值都是满足退出循环的条件的，但是REPEAT循环仍然执行了一次以后才退出循环的，而WHILE循环则一次都没有执行。

12.2.8 事件调度器

事件调度器是是5.1后新增的功能，可以将数据库按自定义的时间周期触发某种操作，可以理解为时间触发器，类似Linux系统下的任务调度器crontab。

下面是一个最简单的事件调度器：

CREATE EVENT myevent

ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR

DO

UPDATE myschema.mytable SET mycol = mycol + 1;

其中：

l事件名称在 create event 关键字后指定。

l通过ON SCHEDULE子句指定事件在何时执行及执行频次。

l通过DO 子句指定要执行的具体操作或事件。

上述创建的调度事件说明如下：创建myevent调度事件，执行更新操作，起始执行时间为调度器创建时间，后续在起始时间基础上每隔1小时触发一次。

下面通过一个完整的实例来熟悉事件调度器的使用：

1.创建测试表test

mysql> create table test(id1 varchar(10),create_time datetime);

Query OK, 0 rows affected (0.19 sec)

2.创建事件调度器test_event_1，每隔5s向test表插入一条记录

mysql> CREATE EVENT test_event_1

-> ON SCHEDULE

-> EVERY 5 SECOND

-> DO

-> INSERT INTO test.test(id1,create_time)

-> VALUES ('test',now());

Query OK, 0 rows affected (0.05 sec)

3.查看调度器状态

mysql> show events \G;

*************************** 1. row ***************************

Db: test

Name: test_event_1

Definer: root@localhost

Time zone: SYSTEM

Type: RECURRING

Execute at: NULL

Interval value: 5

Interval field: SECOND

Starts: 2013-07-26 14:02:02

Ends: NULL

Status: ENABLED

Originator: 8306

character_set_client: latin1

collation_connection: latin1_swedish_ci

Database Collation: gbk_chinese_ci

1 row in set (0.00 sec)

4.隔几秒后，查看test表，发现并没有数据插入

mysql> select * from test;

Empty set (0.00 sec)

5.查看事件调度器状态，发现默认是关闭的

mysql> show variables like '%scheduler%';

+-----------------+-------+

Variable_name Value

+-----------------+-------+

event_scheduler OFF

+-----------------+-------+

1 row in set (0.01 sec)

6.通过下面命令打开调度器。同时show processlist发现新产生一个后台进程

mysql> SET GLOBAL event_scheduler = 1;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%scheduler%';

+-----------------+-------+

Variable_name Value

+-----------------+-------+

event_scheduler ON

+-----------------+-------+

1 row in set (0.01 sec)

mysql> show processlist \G;

。。。。。。前面省略

*************************** 4. row ***************************

Id: 464905

User: event_scheduler

Host: localhost

db: NULL

Command: Daemon

Time: 1

State: Waiting for next activation

Info: NULL

Rows_sent: 0

Rows_examined: 0

Rows_read: 1

4 rows in set (0.00 sec)

7.隔几秒后，再次查看test表，发现已经有了一些数据，且日期间隔都为5s

mysql> select * from test;

+------+---------------------+

id1 create_time

+------+---------------------+

test 2013-07-29 05:28:48

test 2013-07-29 05:28:53

test 2013-07-29 05:28:58

test 2013-07-29 05:29:03

8.为了防止表变得很大，创建一个新的调度器，每隔一分钟清空一次test表

CREATE EVENT trunc_test

ON SCHEDULE every 1 MINUTE

DO TRUNCATE TABLE test;

隔一段时间后，可以发现，test表中数据会定期清空，这类触发器非常适合去定期清空临时表或者日志表。

9. 如果事件调度器不再使用，可以禁用（disable）或者删除（drop）掉。

--禁用event

mysql> alter event test_event_1 disable;

Query OK, 0 rows affected (0.00 sec)

--删除event

mysql> drop event test_event_1;

Query OK, 0 rows affected (0.05 sec)

对于事件调度器，还有很多选项，比如指定事件开始时间和结束时间，或者指定某个时间执行一次而不是循环执行，详细信息大家可以参考事件调度器的相关帮助，这里不再详述。

最后，我们总结一下事件调度器的优势、适用场景及使用中注意事项。

优势：MySQL事件调度器部署在数据库内部由DBA或专人统一维护和管理，避免将一些数据库相关的定时任务部署在操作系统层，减少操作系统管理员产生误操作的风险，对后续管理维护也非常有益。例如，后续进行数据库迁移时无需再迁移操作系统层的定时任务，数据库迁移本身已经包含了调度事件的迁移。

适用场景：事件调度器适合用于 定期收集统计信息，定期清理历史数据、定期数据库检查（例如：自动监控和恢复Slave失败进程）。

注意事项：

1.在繁忙且要求性能的数据库服务器上要慎重部署和启用调度器。

2.过于复杂的处理更适合用程序实现。

3.开启和关闭事件调度器需要具有超级用户权限。

12.3小结

本章主要介绍了存储过程和函数的创建、修改的方式，存储过程、函数的适用场合，并介绍了存储过程和函数中的变量、条件和处理、光标、流程控制的定义和使用，这些对初学者编写简单的存储过程和函数会有所帮助。虽然使用变量、条件和处理、光标和流程控制可以编写功能强大的存储过程和函数，并进行复杂的逻辑处理，但是由于篇幅问题，本章并没有对这部分内容进行深入，读者如果有兴趣的话，可以查询在线的MySQL文档获得帮助。最后介绍了事件调度器的使用，可以大大方便我们处理一些定时任务。

需要强调的是，存储过程和函数的优势是可以将数据的处理放在数据库服务器上进行，避免将大量的结果集传输给客户端，减少数据的传输，但是在数据库服务器上进行大量的复杂运算也会占用服务器的CPU，造成数据库服务器的压力，所以不要在存储过程和函数中进行大量的复杂运算，应尽量将这些运算操作分摊到应用服务器上执行。