mysql中的视图、函数以及存储过程

摘自书本

第11章视图

MySQL 从 5.0.1 版本开始提供视图功能，本章将对 MySQL 中的视图进行介绍。
 
 

11.1 什么是视图

 
视图（View）是一种虚拟存在的表，对于使用视图的用户来说基本上是透明的。视图并
不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时
动态生成的。
视图相对于普通的表的优势主要包括以下几项。
 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，
对用户来说已经是过滤好的复合条件的结果集。
 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能
限制到某个行某个列，但是通过视图就可以简单的实现。
 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加
列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问
者的影响。
 

11.2 视图操作

 
视图的操作包括创建或者修改视图、删除视图，以及查看视图定义。
11.2.1 创建或者修改视图
创建视图需要有 CREATE VIEW 的权限，并且对于查询涉及的列有 SELECT 权限。如果使用
CREATE OR REPLACE 或者 ALTER 修改视图，那么还需要该视图的 DROP 权限。
创建视图的语法为：
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
修改视图的语法为：
ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
例如,要创建了视图 staff_list_view,可以使用以下命令：
mysql> CREATE OR REPLACE VIEW staff_list_view AS
-> SELECT s.staff_id,s.first_name,s.last_name,a.address
-> FROM staff AS s,address AS a
-> where s.address_id = a.address_id ;
Query OK, 0 rows affected (0.00 sec)
MySQL 视图的定义有一些限制，例如，在 FROM 关键字后面不能包含子查询，这和其他数
据库是不同的，如果视图是从其他数据库迁移过来的，那么可能需要因此做一些改动，可以
将子查询的内容先定义成一个视图，然后对该视图再创建视图就可以实现类似的功能了。
 
 
视图的可更新性和视图中查询的定义有关系，以下类型的视图是不可更新的。
 包含以下关键字的 SQL 语句：聚合函数（SUM、MIN、MAX、COUNT 等）、DISTINCT、GROUP
BY、HAVING、UNION 或者 UNION ALL。
 常量视图。
 SELECT 中包含子查询。
 JION。
 FROM 一个不能更新的视图。
 WHERE 字句的子查询引用了 FROM 字句中的表。
例如，以下的视图都是不可更新的：
--包含聚合函数
mysql> create or replace view payment_sum as
 -> select staff_id,sum(amount) from payment group by staff_id;
Query OK, 0 rows affected (0.00 sec)
--常量视图
mysql> create or replace view pi as select 3.1415926 as pi;
Query OK, 0 rows affected (0.00 sec)
--select 中包含子查询
mysql> create view city_view as
 -> select (select city from city where city_id = 1) ;
Query OK, 0 rows affected (0.00 sec)

 

WITH [CASCADED | LOCAL] CHECK OPTION 决定了是否允许更新数据使记录不再满足视图的条
件。这个选项与 Oracle 数据库中的选项是类似的，其中：
 LOCAL 是只要满足本视图的条件就可以更新；
 CASCADED 则是必须满足所有针对该视图的所有视图的条件才可以更新。
如果没有明确是 LOCAL 还是 CASCADED，则默认是 CASCADED。
例如，对 payment 表创建两层视图，并进行更新操作：
mysql> create or replace view payment_view as
 -> select payment_id,amount from payment 
 -> where amount < 10 WITH CHECK OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql> 
mysql> create or replace view payment_view1 as
 -> select payment_id,amount from payment_view 
 -> where amount > 5 WITH LOCAL CHECK OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql> 
mysql> create or replace view payment_view2 as
 -> select payment_id,amount from payment_view 

 -> where amount > 5 WITH CASCADED CHECK OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from payment_view1 limit 1;
+------------+--------+ 
 ?
| payment_id | amount |
+------------+--------+
| 3 | 5.99 |
+------------+--------+
1 row in set (0.00 sec)
mysql> update payment_view1 set amount=10 
 -> where payment_id = 3; 
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update payment_view2 set amount=10
 -> where payment_id = 3;
ERROR 1369 (HY000): CHECK OPTION failed 'sakila.payment_view2'
从测试结果可以看出，payment_view1 是 WITH LOCAL CHECK OPTION 的，所以只要满足本视
图的条件就可以更新，但是 payment_view2 是 WITH CASCADED CHECK OPTION 的，必须满足
针对该视图的所有视图才可以更新，因为更新后记录不再满足 payment_view 的条件，所以
更新操作提示错误退出。
 
 

11.2.2 删除视图

用户可以一次删除一个或者多个视图，前提是必须有该视图的 DROP 权限。
DROP VIEW [IF EXISTS] view_name [, view_name] ...[RESTRICT | CASCADE]
 
例如，删除 staff_list 视图：
mysql> drop view staff_list;
Query OK, 0 rows affected (0.00 sec)

 

 
11.2.3 查看视图
从 MySQL 5.1 版本开始，使用 SHOW TABLES 命令的时候不仅显示表的名字，同时也会显示
视图的名字，而不存在单独显示视图的 SHOW VIEWS 命令。
mysql> use sakila
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_sakila |
+----------------------------+
……

| staff |
| staff_list |
| store |
+----------------------------+
26 rows in set (0.00 sec)
同样，在使用 SHOW TABLE STATUS 命令的时候，不但可以显示表的信息，同时也可以显示视
图的信息。所以，可以通过下面的命令显示视图的信息：
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']
 
下面演示的是查看 staff_list 视图信息的操作：
mysql> show table status like 'staff_list' \G
*************************** 1. row ***************************
 Name: staff_list
 Engine: NULL
 Version: NULL
 Row_format: NULL
 Rows: NULL
Avg_row_length: NULL
 Data_length: NULL
Max_data_length: NULL
 Index_length: NULL
 Data_free: NULL
Auto_increment: NULL
 Create_time: NULL
 Update_time: NULL
 Check_time: NULL
 Collation: NULL
 Checksum: NULL
Create_options: NULL
 Comment: VIEW
1 row in set (0.01 sec)
如果需要查询某个视图的定义，可以使用 SHOW CREATE VIEW 命令进行查看：
mysql> show create view staff_list \G
*************************** 1. row ***************************
 View: staff_list
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER 
VIEW `staff_list` AS select `s`.`staff_id` AS `ID`,concat(`s`.`first_name`,_utf8' 
',`s`.`last_name`) AS `name`,`a`.`address` AS `address`,`a`.`postal_code` AS `zip 
code`,`a`.`phone` AS `phone`,`city`.`city` AS `city`,`country`.`country` AS 
`country`,`s`.`store_id` AS `SID` from (((`staff` `s` join `address` `a` on((`s`.`address_id` 
= `a`.`address_id`))) join `city` on((`a`.`city_id` = `city`.`city_id`))) join `country` 
on((`city`.`country_id` = `country`.`country_id`)))
1 row in set (0.00 sec)
最后，通过查看系统表 information_schema.views 也可以查看视图的相关信息：
mysql> select * from views where table_name = 'staff_list' \G
*************************** 1. row ***************************
 TABLE_CATALOG: NULL
 TABLE_SCHEMA: sakila
 TABLE_NAME: staff_list
VIEW_DEFINITION: select `s`.`staff_id` AS `ID`,concat(`s`.`first_name`,_utf8' 
',`s`.`last_name`) AS `name`,`a`.`address` AS `address`,`a`.`postal_code` AS `zip 
code`,`a`.`phone` AS `phone`,`sakila`.`city`.`city` AS `city`,`sakila`.`country`.`country` AS 
`country`,`s`.`store_id` AS `SID` from (((`sakila`.`staff` `s` join `sakila`.`address` `a` 
on((`s`.`address_id` = `a`.`address_id`))) join `sakila`.`city` on((`a`.`city_id` = 
`sakila`.`city`.`city_id`))) join `sakila`.`country` on((`sakila`.`city`.`country_id` = 
`sakila`.`country`.`country_id`)))
 CHECK_OPTION: NONE
 IS_UPDATABLE: YES
 DEFINER: root@localhost
 SECURITY_TYPE: DEFINER
1 row in set (0.00 sec)
View Code

11.3 小结

 
本章主要介绍了 MySQL 提供的视图创建、维护等相关语法。如果从不支持视图的旧版本升
级到提供视图功能的新版本后，要想使用视图，则需要升级授权表，使之包含与视图有关的
权限。相关的升级步骤，可以参见 27.4 节 MySQL 升级内容。
 

第12章存储过程和函数

 
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 {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[,...]])

 

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 {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 循环则一次都没有执行。
 
 
posted @ 2020-12-02 16:24  wq9  阅读(432)  评论(0)    收藏  举报