深入浅出mysql_存储过程

十二. 存储过程和函数

12.1 什么是存储过程和函数


是什么


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


存储过程和函数的区别


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


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


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


下面对 characteristic 特征值的部分进行简单的说明。
1 LANGUAGE SQL：说明下面过程的 BODY 是使用 SQL 语言编写，这条是系统默认的，为今后 MySQL 会支持的除 SQL 外的其他语言支持的存储过程而准备。
2 [NOT] DETERMINISTIC：DETERMINISTIC 确定的，即每次输入一样输出也一样的程序，NOT DETERMINISTIC 非确定的，默认是非确定的。当前，这个特征值还没有被优化程序使用。
3 { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：这些特征值提供子程序使用数据的内在信息，这些特征值目前只是提供给服务器，并没有根据这些特征值来约束过程实际使用数据的情况。CONTAINS SQL 表示子程序不包含读或写数据的语句。NO SQL 表示子程序不包含 SQL 语句。READS SQL DATA 表示子程序包含读数据的语句，但不包含写数据的语句。MODIFIES SQL DATA 表示子程序包含写数据的语句。如果这些特征没有明确给定，默认使用的值是 CONTAINS SQL。
4 SQL SECURITY { DEFINER | INVOKER }：可以用来指定子程序该用创建子程序者的许可来执行，还是使用调用者的许可来执行。默认值是 DEFINER。
5 COMMENT 'string'：存储过程或者函数的注释信息。


范例


创建了一个新的过程 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 ;


调用上例子过程的执行情况如下
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)


修改


语法


ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]


characteristic:
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'


调用


语法


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


12.2.2 删除存储过程或者函数


一次只能删除一个存储过程或者函数，删除存储过程或者函数需要有该过程或者函数的ALTER ROUTINE 权限


语法


DROP {PROCEDURE | FUNCTION} [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 {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']


范例


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 {PROCEDURE | FUNCTION} sp_name


范例


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 了解存储过程和函数的信息了解存储过程和函数的信息


范例


mysql> select * from routines where ROUTINE_NAME = 'film_in_stock' \G
*************************** 1. row ***************************
SPECIFIC_NAME: film_in_stock
ROUTINE_CATALOG: NULL
ROUTINE_SCHEMA: sakila......k(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 变量的使用


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] ...
SELECT col_name[,...] INTO var_name[,...] table_expr


范例


给刚才定义的变量 last_month_start 赋值，具体语法如下：
SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);


通过查询将结果赋值给变量 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 <= p_effective_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


范例


1）当没有进行条件处理时，执行结果如下：
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，所以会继续执行下面的语句。


12.2.6 光标的使用


在存储过程和函数中可以使用光标对结果集进行循环的处理。


声明光标


语法


DECLARE cursor_name CURSOR FOR select_statement


OPEN 光标：


语法


OPEN cursor_name


FETCH 光标：


语法


FETCH cursor_name INTO var_name [, var_name] ...


CLOSE 光标：


语法


CLOSE cursor_name


范例


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)


12.2.7 流程控制


1 ．IF  语句


语法


IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END 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 CAS


范例


在上文光标的使用例子中，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]


范例


子主题 1


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  语句


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


范例


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)


6 ．REPEAT  语句


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


语法


[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]


范例


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


范例


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)


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