mysql> CALL film_in_stock_definer(2,2,@a);+--------------+
| inventory_id |
+--------------+
| 10 |
| 11 |
+--------------+
2 rows in set (0.03sec)
Query OK,0 rows affected (0.03sec)
mysql> CALL film_in_stock_invoker(2,2,@a);
ERROR1142 (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 {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']
下面演示的是查看过程 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
153Linux
公社
www.linuxidc.com
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
下面演示的是查看过程 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
154Linux
公社
www.linuxidc.com
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 赋值，具体语法如下：
155Linux
公社
www.linuxidc.com
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 <= 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:
156Linux
公社
www.linuxidc.com
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)当对主键重的异常进行处理时，执行结果如下：
157Linux
公社
www.linuxidc.com
158
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;Linux
公社
www.linuxidc.com
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 ;
159Linux
公社
www.linuxidc.com
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] ...
160Linux
公社
www.linuxidc.com
[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;
163Linux
公社
www.linuxidc.com
164
->
-> 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 循环则一次都没有执行。
扫一扫
440
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
