MySql学习笔记之——开发常用数据库对象

开发常用数据库对象

1、视图

1.1、什么是视图

视图是一种虚拟存在的表，对于使用视图的用户来说基本上是透明的。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

视图相对于普通的表的优势主要包括以下几项：

• 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集；
• 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现；
• 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改表名，则可以通过修改视图来解决，不会造成对访问者的影响。

理解视图的最好方法是看一个例子：

mysql> SELECT cust_name, cust_contact
    -> FROM customers, orders, orderitems
    -> WHERE customers.cust_id = orders.cust_id
    -> AND orderitems.order_num = orders.order_num
    -> AND prod_id = 'TNT2';
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
2 rows in set (0.00 sec)

此查询用来检索订购了某个特定产品的客户。任何需要这个数据的人都必须理解相关表的结构，并且知道如何创建查询和对表进行联结。为了检索其他产品（或多个产品）的相同数据，必须修改最后的 WHERE 子句。

现在，假如可以把整个查询包装成一个名为 productcustomers 的虚拟表，则可以如下轻松地检索出相同的数据：

--假如可以把整个查询包装成一个名为 productcustomers 的虚拟表
SELECT cust_name, cust_contact
FROM productcustmers
WHERE prod_id = 'TNT2';

---

1.2、视图操作

视图的操作包括修改视图、删除视图，以及查看视图定义。

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

MySQL 视图定义有一些限制，例如，在 FROM 关键字后不能包含子查询，这和其他数据库不同。视图的可更新性和视图中查询的定义有关系，以下类型的视图是不可更新的：

• 包含以下关键字的 SQL 语句：聚集函数（SUM、MIN、MAX、COUNT 等）、DISTINCT、GROUP BY、HAVING、UNION 或者 UNION ALL；
• 常量视图；
• SELECT 中包含子查询；
• JOIN；
• FROM 一个不能更新的视图；
• WHERE 子句的子查询引用了 FROM 子句中的表。

以下视图是不可更新的：

--包含聚合函数
CREATE OR REPLACE VIEW payment_sum AS
SELECT staff_id, sum(amount) 
FROM payment GROUP BY staff_id;

--常量视图
CREATE OR REPLACE VIEW pi AS
SELECT 3.1415926 AS pi;

--SELECT 中包含子查询
CREATE VIEW city_view AS
SELECT (SELECT city FROM city WHERE city_id = 1);

WITH [CASCADED|LOCAL]CHECK OPTION 决定了是否允许更新数据使记录不再满足视图条件。其中：

• LOCAL 只要满足视图的条件就可以更新；
• 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> 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> 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;
+------------+--------+
| payment_id | amount |
+------------+--------+
|          1 |   5.46 |
|          2 | 547.50 |
|          3 | 465.79 |
|          4 | 545.20 |
+------------+--------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM payment_view1 LIMIT 1;
+------------+--------+
| payment_id | amount |
+------------+--------+
|          1 |   5.46 |
+------------+--------+
1 row in set (0.00 sec)

mysql> UPDATE payment_view1 SET amount = 9
    -> WHERE payment_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE payment_view2 SET amount = 10
    -> WHERE payment_id = 1;
ERROR 1369 (HY000): CHECK OPTION failed 'test1.payment_view2'

mysql> SELECT * FROM payment;
+------------+--------+
| payment_id | amount |
+------------+--------+
|          1 |   9.00 |
|          2 | 547.50 |
|          3 | 465.79 |
|          4 | 545.20 |
+------------+--------+
4 rows in set (0.00 sec)

从测试可以看出，payment_view1 是 WITH LOCAL CHECK OPTION 的，所以只要满足本视图的条件就可以更新，但是 payment_view2 是 WITH CASCADED CHECK OPTION 的，必须满足针对该视图的所有视图才可以更新，因为更新后记录不再满足 payment_view 的条件，所以更新操作提示错误退出。

1.3、删除视图

用户可以一次删除一个或者多个视图，前提是必须有这个视图的 DROP 权限。

DROP VIEW [IF EXISTS] view_name [,view_name]... [RESTRICT|CASCADE]

例如，删除 payment_view1 视图：

mysql> DROP VIEW payment_view1;
Query OK, 0 rows affected (0.00 sec)

1.4、查看视图

使用 SHOW TABLES 命令的时候不仅显示表的名字，同时也会显示视图的名字，而不存在单独显示视图的 SHOW VIEWS 命令。同样，在使用 SHOW TABLE STATUS 命令时，不但可以显示表的信息，同时也可以显示视图的信息。

mysql> SHOW TABLES;
+-------------------+
| Tables_in_test1   |
+-------------------+
| Myisam_char       |
| address           |
| ai                |
| autoincre_demo    |
| autoincre_demo2   |
| city              |
| city_memory       |
| country           |
| dept              |
| emp               |
| float_and_decimal |
| latin             |
| num_f             |
| payment           |
| payment_2006      |
| payment_2007      |
| payment_all       |
| payment_view      |		<--视图
| payment_view2     |		<--视图
| pi                |
| salary            |
| staff             |
| staff_list_view   |		<--视图
| t                 |
| t1                |
| t2                |
| t3                |
| t4                |
| t5                |
| tab_memory        |
| test_auto_incre   |
| text              |
| text2             |
| vc                |
+-------------------+
34 rows in set (0.00 sec)

同样，在使用 SHOW TABLES STATUS 命令时，不但可以显示表的信息，同时也可以显示视图信息。

SHOW TABLES STATUS [FROM db_name][LIKE 'pattern']

mysql> SHOW TABLE STATUS LIKE 'payment_view2'\G
*************************** 1. row ***************************
           Name: payment_view2
         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.00 sec)

如果需要查询某个视图的定义，可以使用 SHOW CREATE VIEW 命令进行查看：

mysql> SHOW CREATE VIEW payment_view2 \G
*************************** 1. row ***************************
                View: payment_view2
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `payment_view2` AS select `payment_view`.`payment_id` AS `payment_id`,`payment_view`.`amount` AS `amount` from `payment_view` where (`payment_view`.`amount` > 5) WITH CASCADED CHECK OPTION
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

最后，通过查看系统表 information_schema.views 也可以查看视图的相关信息：

mysql> SELECT * FROM information_schema.views WHERE table_name = 'payment_view2'\G
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: test1
          TABLE_NAME: payment_view2
     VIEW_DEFINITION: select `payment_view`.`payment_id` AS `payment_id`,`payment_view`.`amount` AS `amount` from `test1`.`payment_view` where (`payment_view`.`amount` > 5)
        CHECK_OPTION: CASCADED
        IS_UPDATABLE: YES
             DEFINER: root@localhost
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
1 row in set (0.00 sec)

1.5、案例

1.5.1、利用视图简化复杂的联结

视图最常见的应用之一是隐藏复杂的 SQL，这通常会涉及联结。

--返回已订购了任意产品的所有客户的列表
mysql> CREATE VIEW productconstomers AS
    -> SELECT cust_name, cust_contact, prod_id
    -> FROM customers, orders, orderitems
    -> WHERE customers.cust_id = orders.cust_id
    -> AND orderitems.order_num = orders.order_num;
Query OK, 0 rows affected (0.00 sec)

这里创建一个名为 productconstomers 的视图，它联结三个表，以返回已订购了任意产品的所有客户的列表。如果执行 SELECT * FROM productconstomers，将列出订购了任意产品的客户。

为了检索订购了 TNT2 的客户，可如下进行：

--检索订购了 TNT2 的客户
mysql> SELECT cust_name, cust_contact
    -> FROM productconstomers
    -> WHERE prod_id = 'TNT2';
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
2 rows in set (0.00 sec)

这条语句通过 WHERE 子句从视图中检索特定数据。在 MySQL 处理此查询时，它将指定的 WHERE 子句添加到视图查询中的已有 WHERE 子句中，以便正确地过滤数据。

1.5.2、用视图重新格式化检索出的数据

视图的另一个常见用途是重新格式化检索出的数据。下面的 SELECT 语句在单个组合计算列中返回供应商名和位置：

--在单个组合计算列中返回供应商名和位置
mysql> SELECT CONCAT(RTRIM(vend_name), '(', RTRIM(vend_country), ')')
    -> AS vend_title
    -> FROM vendors
    -> ORDER BY vend_name;
+------------------------+
| vend_title             |
+------------------------+
| ACME(USA)              |
| Anvils R Us(USA)       |
| Furball Inc.(USA)      |
| Jet Set(England)       |
| Jouets Et Ours(France) |
| LT Supplies(USA)       |
+------------------------+
6 rows in set (0.00 sec)

现在，假如经常需要这个格式的结果。不必在每次需要时执行联结，创建一个视图，每次需要时使用它即可。为把此语句转换为视图，可按如下进行：

mysql> CREATE VIEW vendorlocations AS
    -> SELECT CONCAT(RTRIM(vend_name), '(', RTRIM(vend_country), ')')
    -> AS vend_title
    -> FROM vendors
    -> ORDER BY vend_name;
Query OK, 0 rows affected (0.00 sec)

这条语句使用与之前的 SELECT 语句相同的查询创建视图。为了检索出以创建所有邮件标签的数据，可如下进行：

mysql> SELECT *
 -> FROM vendorlocations;
+------------------------+
| vend_title             |
+------------------------+
| ACME(USA)              |
| Anvils R Us(USA)       |
| Furball Inc.(USA)      |
| Jet Set(England)       |
| Jouets Et Ours(France) |
| LT Supplies(USA)       |
+------------------------+
6 rows in set (0.00 sec)

1.5.3、用视图过滤不想要的数据

视图对于应用普通的 WHERE 子句也很有用。例如，可以定义 customeremaillist 视图，它过滤没有电子邮件地址的客户。为此目的，可使用下面的语句：

--过滤没有电子邮件地址的客户
mysql> CREATE VIEW customeremaillist AS
    -> SELECT cust_id, cust_name, cust_email
    -> FROM customers
    -> WHERE cust_email IS NOT NULL;
Query OK, 0 rows affected (0.00 sec)

显然，在发送电子邮件到邮件列表时，需要排除没有电子邮件地址的用户。这里色 WHERE 子句过滤了 cust_email 列中具有 NULL 值的那些行，使他们不被检索出来。

现在，可以像使用其他表一样使用视图 customeremaillist。

mysql> SELECT *
    -> FROM customeremaillist;
+---------+----------------+---------------------+
| cust_id | cust_name      | cust_email          |
+---------+----------------+---------------------+
|   10001 | Coyote Inc.    | ylee@coyote.com     |
|   10003 | Wascals        | rabbit@wascally.com |
|   10004 | Yosemite Place | sam@yosemite.com    |
+---------+----------------+---------------------+
3 rows in set (0.00 sec)

如果从视图检索数据时使用了一条 WHERE 子句，则两组子句（一组在视图中，另一组是传递给视图的）将自动组合。

1.5.4、使用视图与计算字段

视图对于简化计算字段的而是用特别有用。下面的语句检索某个特定订单中的物品，计算每种物品的总价格：

--检索某个特定订单中的物品，计算每种物品的总价格
mysql> SELECT prod_id,
    ->        quantity,
    ->        item_price,
    ->        quantity*item_price AS expanded_price
    -> FROM orderitems
    -> WHERE order_num = 20005;
+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| ANV01   |       10 |       5.99 |          59.90 |
| ANV02   |        3 |       9.99 |          29.97 |
| TNT2    |        5 |      10.00 |          50.00 |
| FB      |        1 |      10.00 |          10.00 |
+---------+----------+------------+----------------+
4 rows in set (0.00 sec)

将其转换为一个视图，如下进行：

mysql> CREATE VIEW orderitemexpanded AS
    -> SELECT prod_id,
    ->        quantity,
    ->        item_price,
    ->        order_num,
    ->        quantity*item_price AS expanded_price
    -> FROM orderitems;
Query OK, 0 rows affected (0.00 sec)

为了检索订单 20005 的详细内容（上面的输出），如下进行：

mysql> SELECT *
    -> FROM orderitemexpanded
    -> WHERE order_num = 20005;
+---------+----------+------------+-----------+----------------+
| prod_id | quantity | item_price | order_num | expanded_price |
+---------+----------+------------+-----------+----------------+
| ANV01   |       10 |       5.99 |     20005 |          59.90 |
| ANV02   |        3 |       9.99 |     20005 |          29.97 |
| TNT2    |        5 |      10.00 |     20005 |          50.00 |
| FB      |        1 |      10.00 |     20005 |          10.00 |
+---------+----------+------------+-----------+----------------+
4 rows in set (0.00 sec)

可以看到，视图非常容易创建，而且很好使用。正确使用，视图可极大地简化复杂的数据处理。

---

2、存储过程和函数

2.1、什么是存储过程和函数

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

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

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

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

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

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

CREATE 
	[DEFINE = {user | CURRENT_USER}]
	PROCEDURE sp_name ([proc_parameter[,...]])
	[characteristic ...] routine_boby

CREATE
	[DEFINE = {user | CURRENT_USER}]
	FUNCTION sp_name ([func_parameter[,...]])
	RETURNS type
	[characteristic ...] routine_boby

proc_parameter:
	[IN | OUT | INOUT] param_name type
	
func_parameter:
	param_name type
	
type:
	Any valid MySQL data type

characteristic:
	COMMENT 'string'
	| LANGUAGE SQL
	| [NOT] DETERMINISTIC
	| {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
	| SQL SECURITY {DEFINER | INVOKER}
	
routine_boby:
	Valid SQL routine statement

调用过程语法如下：

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

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

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

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

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

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

2.4、具体案例

2.4.1、执行存储过程

MySQL 称存储过程的执行为调用，因此 MySQL 执行存储过程的语句为 CALL。CALL 接受存储过程的名字以及需要传递给它的任意参数。请看下面的例子：

CALL productpricing(@pricelow,
				    @pricehigh,
				    @priceaverage);

其中，执行名为 productpricing 的存储过程，它计算并返回产品的最低、最高和平均价格。

存储过程可以显示结果，也可以不显示。

2.4.2、创建存储过程

请看一个例子——一个返回产品平均价格的存储过程。一下是代码：

--一个返回产品平均价格的存储过程
mysql> DELIMITER $$
mysql>
mysql> CREATE PROCEDURE productpricing()
    -> BEGIN
    ->    SELECT avg(prod_price) AS priceaverage
    ->    FROM products;
    -> END
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

此存储过程的名称为 productpricing，用 CREATE PROCEDURE productpricing() 语句定义。如果存储过程接受参数，它们将在 () 中列举出来。此存储过程没有参数，但后跟的 () 仍需要。BEGIN 和 END 语句用来限定存储过程体，过程体本身是一个简单的 SELECT 语句。

那么如何使用这个存储过程呢？如下进行：

mysql> CALL productpricing();
+--------------+
| priceaverage |
+--------------+
|    16.133571 |
+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

CALL productpricing(); 执行刚创建的存储过程并显示返回的结果。因为存储过程实际上是一种函数，所以存储过程名后需要有 () 符号。

2.4.3、删除存储过程

语法如下：

DROP PROCEDURE 存储过程名;

请注意，这个存储过程名后没有 ()。

2.4.4、使用参数

一般，存储过程并不显示结果，而是把结果返回给你指定的变量。

以下是 productpricing 的修改版本：

mysql> DELIMITER //
mysql>
mysql> CREATE PROCEDURE productpricing(
    ->  OUT pl DECIMAL(8, 2),
    ->  OUT ph DECIMAL(8, 2),
    ->  OUT pa DECIMAL(8, 2)
    -> )
    -> BEGIN
    ->    SELECT Min(prod_price)
    ->    INTO pl
    ->    FROM products;
    ->    SELECT Max(prod_price)
    ->    INTO ph
    ->    FROM products;
    ->    SELECT Avg(prod_price)
    ->    INTO pa
    ->    FROM products;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

此存储过程接受 3 个参数：pl 存储产品最低价格，ph 存储产品最高价格，pa 存储产品平均价格。每个参数必须具有指定的类型，这里使用十进制值。关键字 OUT 指出相应的参数用来从存储过程传出一个值（返回给调用者）。MySQL 支持 IN（传递给存储过程）、OUT（从存储过程传出）和 INOUT（对存储过程传入和传出）类型的参数。存储过程的代码位于 BEGIN 和 END 语句内。

为了调用此修改过的存储过程，必须指定 3 个变量名，如下所示：

mysql> CALL productpricing(@pricelow, @pricehigh, @priceaverage);
Query OK, 1 row affected, 1 warning (0.00 sec)

所有 MySQL 变量都必须以 @ 开始。

在调用时，这条语句并不显示任何数据。它返回以后可以显示（或其他处理中使用）的变量。

为了显示检索出的产品平均价格，可如下进行：

mysql> SELECT @priceaverage;
+---------------+
| @priceaverage |
+---------------+
|         16.13 |
+---------------+
1 row in set (0.00 sec)

为了获得 3 个值，可使用以下语句：

mysql> SELECT @pricelow, @pricehigh, @priceaverage;
+-----------+------------+---------------+
| @pricelow | @pricehigh | @priceaverage |
+-----------+------------+---------------+
|      2.50 |      55.00 |         16.13 |
+-----------+------------+---------------+
1 row in set (0.00 sec)

下面是另外一个例子，这次使用 IN 和 OUT 参数。ordertotal 接受订单号并返回该订单的合计：

mysql> DELIMITER //
mysql>
mysql> CREATE PROCEDURE ordertotal(
    ->    IN onumber INT,
    ->    OUT ototal DECIMAL(8, 3)
    -> )
    -> BEGIN
    ->    SELECT Sum(item_price*quantity)
    ->    FROM orderitems
    ->    WHERE order_num = onumber
    ->    INTO ototal;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;

onumber 定义为 IN，因为订单号被传入存储过程。ototal 定义为 OUT，因为要从存储过程返回合计。

为了调用这个存储过程，可使用以下语句：

mysql> CALL ordertotal(20005, @total);
Query OK, 1 row affected (0.00 sec)

为了显示此合计，可如下进行：

mysql> SELECT @total;
+---------+
| @total  |
+---------+
| 149.870 |
+---------+
1 row in set (0.00 sec)

2.4.5、建立智能存储过程

考虑这个场景。你需要获得与之前一样的订单合计，但需要合计增加营业税，不过只针对某些顾客（或许是你所在的州的那些顾客）。那么，你需要做以下几件事情：

• 获得合计；
• 把营业税有条件地添加到合计；
• 返回合计（带或不带税）。

存储过程如下：

mysql> CREATE PROCEDURE ordertotal(
    ->    IN onumber INT,
    ->    IN taxable BOOLEAN,
    ->    OUT ototal DECIMAL(8, 2)
    -> )COMMENT 'Obtain order total, optionally adding tax'
    -> BEGIN
    ->    -- Declare variable for total
    ->    DECLARE total DECIMAL(8, 2);
    ->    -- Declare tax percentage
    ->    DECLARE taxrate INT DEFAULT 6;
    ->
    ->    -- Get the order total
    ->    SELECT Sum(item_price * quantity)
    ->    FROM orderitems
    ->    WHERE order_num = onumber
    ->    INTO total;
    ->
    ->    -- Is this taxable?
    ->    IF (taxable) THEN
    ->       -- Yes, so add taxrate to the total
    ->       SELECT total+(total/100*taxrate) INTO total;
    ->    END IF;
    ->    -- And finally, save to out variable
    ->    SELECT total INTO ototal;
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

这显然是一个更高级，功能更强的存储过程。

mysql> CALL ordertotal(20005, 0, @total);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @total;
+--------+
| @total |
+--------+
| 149.87 |
+--------+
1 row in set (0.00 sec)

mysql> CALL ordertotal(20005, 1, @total);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @total;
+--------+
| @total |
+--------+
| 158.86 |
+--------+
1 row in set (0.00 sec)

2.4.6、检查一个存储过程

为显示用来创建一个存储过程 CREATE 语句，使用 SHOW CREATE PROCEDURE 语句：

mysql> SHOW CREATE PROCEDURE ordertotal \G
*************************** 1. row ***************************
           Procedure: ordertotal
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `ordertotal`(
   IN onumber INT,
 IN taxable BOOLEAN,
 OUT ototal DECIMAL(8, 2)
)
    COMMENT 'Obtain order total, optionally adding tax'
BEGIN

 DECLARE total DECIMAL(8, 2);

 DECLARE taxrate INT DEFAULT 6;


 SELECT Sum(item_price * quantity)
 FROM orderitems
 WHERE order_num = onumber
 INTO total;


 IF (taxable) THEN

      SELECT total+(total/100*taxrate) INTO total;
 END IF;

 SELECT total INTO ototal;
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

为了获得包括何时、由谁创建等详细信息的存储过程，使用 SHOW PROCEDURE STATUS。

---

3、游标

3.1、什么是游标

MySQL 检索操作返回一组称为结果集的行。这组返回的行都是与 SQL 语句相匹配的行（零行或多行）。

有时，需要在检索出来的行中前进或后退一行或几行。这就是使用游标的原因。游标（cursor）是一个存储在 MySQL 服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或作出更改。

只能用于存储过程

MySQL 的游标只能用于存储过程（和函数）。

3.2、使用游标

使用游标涉及几个明确的步骤。

• 在能够使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的 SELECT 语句；
• 一旦声明后，必须打开游标以供使用。这个过程用前面定义的 SELECT 语句把数据实际检索出来；
• 对于填有数据的游标，根据需要取出（检索）各行；
• 在结束游标使用时，必须关闭游标。

在声明游标后，可根据需要频繁地打开和关闭游标。在游标打开后，可根据需要频繁地执行取操作。

3.2.1、创建游标

游标用 DECLARE 语句创建。DECLARE 命名游标，并定义相应的 SELECT 语句，根据需要带 WHERE 和其他子句。例如，下面的语句定义了 ordernumbers 的游标，使用了可以检索所有订单的 SELECT 语句。

-- 定义了 ordernumbers 的游标，使用了可以检索所有订单的 SELECT 语句
CREATE PROCEDURE processorders()
BEGIN
  DECLARE ordernumbers CURSOR
  FOR
  SELECT order_num FROM orders;
END;

--------------------------------------------------------------------

mysql> delimiter //
mysql>
mysql> CREATE PROCEDURE processorders()
    -> BEGIN
    ->    DECLARE ordernumbers CURSOR
    ->    FOR
    ->    SELECT order_num FROM orders;
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

这个存储过程并没有做很多事情，DECLARE 语句用来定义和命名游标，这里为 ordernumbers。存储过程处理完成后，游标就消失（因为它局限于存储过程）。

在定义游标后，可以打开它。

3.2.2、打开和关闭游标

游标使用 OPEN CURSOR 语句打开：

OPEN ordernumbers;

在处理 OPEN 语句是执行查询，存储检索出的数据以供浏览和滚动。

游标处理完成后，应当使用如下语句关闭游标：

CLOSE ordernumbers;

CLOSE 释放游标使用的所有内部内存和资源，因此在每个游标不再需要时都应该关闭。

在一个游标关闭后，如果没有重新打开，则不能使用它。但是，使用声明过的游标不需要再次声明，用 CLOSE 语句打开它就可以。

隐含关闭

如果你不明确关闭游标，MySQL 将会在到达 END 语句时自动关闭。

下面是前面例子的修改版本：

CREATE PROCEDURE processorders()
BEGIN
  -- Delcalre the cursor 
  DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	
	-- Open the cursor
	OPEN ordernumbers;
	
	-- Close the cursor
	CLOSE ordernumbers;
	
END;

--------------------------------------------------------

mysql> delimiter //
mysql>
mysql> CREATE PROCEDURE processorders()
    -> BEGIN
    ->   -- Delcalre the cursor
    ->   DECLARE ordernumbers CURSOR
    -> FOR
    -> SELECT order_num FROM orders;
    ->
    -> -- Open the cursor
    -> OPEN ordernumbers;
    ->
    -> -- Close the cursor
    -> CLOSE ordernumbers;
    ->
    -> END;
    ->
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

这个存储过程声明、打开和关闭一个游标。但对检索出的数据什么也没做。

3.2.3、使用游标数据

在一个游标被打开后，可以使用 FETCH 语句分别访问它的每一行。FETCH 指定检索什么数据（所需的列），检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条 FETCH 语句检索下一行（不重复读取每一行）。

第一个例子从游标中检索单个行（第一行）：

-- 第一个例子从游标中检索单个行（第一行）
CREATE PROCEDURE processorders()
BEGIN

  -- Declare local variables
	DECLARE o INT;

  -- Delcalre the cursor 
  DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	
	-- Open the cursor
	OPEN ordernumbers;
	
	-- Get order number
	FETCH ordernumbers INTO o;
	
	-- Close the cursor
	CLOSE ordernumbers;
	
END;

其中 FETCH 用来检索当前行的 order_num 列（将自动从第一行开始）到一个名为 o 的局部变量的变量中。对检索出的数据不做任何处理。

在下一个例子中，循环检索数据，从第一行到最后一行：

-- 循环检索数据，从第一行到最后一行
CREATE PROCEDURE processorders()
BEGIN

  -- Declare local variables
	DECLARE done boolean DEFAULT 0;
	DECLARE o INT;

  -- Delcalre the cursor 
  DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	
	-- Delcare continue handler
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
	
	-- Open the cursor
	OPEN ordernumbers;
	
	-- Loop through all rows
	REPEAT
	
	  -- Get order number
	  FETCH ordernumbers INTO o;
		
	-- End the loop
	UNTIL done END REPEAT;
	
	-- Close the cursor
	CLOSE ordernumbers;
	
END;

与前边的例子一样，这个例子使用 FETCH 检查当前 order_num 到声明的名为 o 的变量中。但与之前不一样的是，这个例子的 FETCH 是在 REPEAT 中，因此它反复执行到 done 为真（由 UNTIL done END REPEAT; 规定）。为使它起作用，用一个 DEFAULT 0（假，不结束）定义变量 done。那么，done 怎样才能在结束时被设置为真呢？答案是用以下语句：

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

这条语句定义了一个 CONTINUE HANDLER，它是在条件出现时被执行的代码。这里，它指出当 SQLSTATE ‘02000’ 出现时，SET done=1。SQLSTATE ‘02000’ 是一个未找到条件，当 REAPEAT 由于没有更多的行供循环而不能继续时，出现这个条件。

如果调用这个存储过程，它将定义几个变量和一个 CONTINUE HANDLER，定义并打开一个游标，重复读取所有行，然后关闭游标。

如果一切正常，你可以在循环内放入任意需要的处理（在 FETCH 语句之后，循环结束之前）。

为了把这些内容组织起来，下面给出我们的游标存储过程样例的更进一步修改的版本，这次对取出的数据进行某种实际的处理：

-- 最后修改的版本
CREATE PROCEDURE processorders()
BEGIN

  -- Declare local variables
	DECLARE done boolean DEFAULT 0;
	DECLARE o INT;
	DECLARE t DECIMAL(8, 2);

  -- Delcalre the cursor 
  DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	
	-- Delcare continue handler
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
	
	-- Create a table to store the results
	CREATE TABLE IF NOT EXISTS ordertotals
	 (order_num INT, total DECIMAL(8, 2));
	
	-- Open the cursor
	OPEN ordernumbers;
	
	-- Loop through all rows
	REPEAT
	
	  -- Get order number
	  FETCH ordernumbers INTO o;
		
		-- Get the total for this order
		CALL ordertotal(o, 1, t);
		
		-- Insert order and total into ordertotals
		INSERT INTO ordertotals(order_num, total) VALUES(o, t);
		
	-- End the loop
	UNTIL done END REPEAT;
	
	-- Close the cursor
	CLOSE ordernumbers;
	
END;

在这个例子中，我们增加了另一个名为 t 的变量（存储每个订单的合计）。此存储过程还在运行中创建了一个新表（如果它不存在的话），名为 ordertotals。这个表将保存存储过程生成的结果。FETCH 像之前一样取每个 order_num，然后用 CALL 执行另一个存储过程来计算每个订单的带税的合计（结果存储到 t）。最后，用 INSERT 保存每个订单的订单号和合计。

此存储过程不返回数据，但它能够创建和填充另一个表，可以用一条简单的 SELECT 语句查看此表：

mysql> CALL processorders;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM ordertotals;
+-----------+---------+
| order_num | total   |
+-----------+---------+
|     20005 |  158.86 |
|     20009 |   40.78 |
|     20006 |   58.30 |
|     20007 | 1060.00 |
|     20008 |  132.50 |
|     20008 |  132.50 |
|     20005 |  158.86 |
|     20009 |   40.78 |
|     20006 |   58.30 |
|     20007 | 1060.00 |
|     20008 |  132.50 |
|     20008 |  132.50 |
+-----------+---------+
12 rows in set (0.00 sec)

这样，我们就得到了存储过程、游标、逐行处理以及存储过程调用其他存储过程的一个完整的工作样例。

---

4、触发器

4.1、什么是触发器

MySQL 语句在需要时被执行，存储过程也是如此。但是，如果你想要某条语句（或某些语句）在事件发生时自动执行，怎么办呢？例如：

• 每当增加一个顾客到某个数据库表时，都检查其电话号码格式是否正确，州的缩写是否为大写；
• 每当订购一个产品时，都从库存数量中减去订购的数量；
• 无论何时删除一行，都在某个存档表中保留一份副本。

所有的这些例子的共同之处是它们都需要在某个表发生更改时自动处理。这确切地说就是触发器。触发器是 MySQL 响应以下任意语句而自动执行的一条 MySQL 语句（或位于 BEGIN 和 END 之间的一组语句）：

• DELETE；
• INSERT；
• UPDATE。

其他语句不支持触发器。

4.2、创建触发器

在创建触发器时，需要给出 4 条信息：

• 唯一的触发器名；
• 触发器关联的表；
• 触发器应该响应的活动（DELETE、INSERT 或 UPDATE）；
• 触发器何时执行（处理之前或之后）。

触发器用 CREATE TRIGGER 语句创建。下面是一个例子：

-- 创建一个触发器，一个简单的例子
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';

CREATE TRIGGER 用来创建名为 newproduct 的触发器。触发器可在一个操作发生之前或之后执行，这里给出了 AFTER INSERT，所以触发器将在 INSERT 语句成功执行之后执行。这个触发器还指定 FOR EACH ROW，因此代码对每个插入行执行。在这个例子中，文本 Product added 将对每个插入的行显示一次。

为了测试这个触发器，使用 INSERT 语句添加一行或多行到 products 中，你将看到对每个成功的插入，显示 Products added 消息。

仅支持表

只有表才支持触发器，视图不支持（临时表也不支持）。

触发器每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持 6 个触发器（每条 INSERT、UPDATE 和 DELETE 之前或之后）。单一触发器不能与多个事件或多个表关联，所以，如果你需要一个对 INSERT 和 UPDATE 操作执行的触发器，则应该定义两个触发器。

触发器失败

如果 BEFORE 触发器失败，则 MySQL 将不执行请求的操作。此外，如果 BEFORE 触发器或语句本身失败，MySQL 将不执行 AFTER 触发器。

4.3、删除触发器

现在，删除触发器的语法应该很明显了。为了删除一个触发器，可使用 DROP TRIGGER 语句，如下所示：

-- 删除触发器
DROP TRIGGER newproduct;

触发器不能更新或覆盖。为了修改一个触发器，必须先删除它，然后再重新创建。

4.4、使用触发器

来看看每种类型的触发器以及它们之间的差别。

4.4.1、INSERT 触发器

INSERT 触发器在 INSERT 语句执行之前或之后执行。需要知道以下几点：

• 在 INSERT 触发器代码内，可引用一个名为 NEW 的虚拟表，访问被插入的行；
• 在 BEFORE INSERT 触发器中，NEW 中的值也可以被更新（允许更改被插入的值）；
• 对于 AUTO_INCREMENT 列，NEW 在 INSERT 执行之前包含 0，在 INSERT 执行之后包含新的自动生成值。

下面举一个例子（一个实际有用的例子）。AUTO_INCREMENT 列具有 MySQL 自动赋予的值。

-- INSERT 触发器
CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num INTO @arg;

--------------------------------------------------

mysql> CREATE TRIGGER neworder AFTER INSERT ON orders
    -> FOR EACH ROW SELECT NEW.order_num INTO @arg;
Query OK, 0 rows affected (0.00 sec)

此代码创建一个名为 neworder 的触发器，它按照 AFTER INSERT ON orders 执行。在插入一个新的订单到 orders 表时，MySQL 生成一个新订单并保存到 order_num 中。触发器从 NEW.order_num 取得这个值并返回它。此触发器必须按照 AFTER INSERT 执行，因为在 BEFORE INSERT 语句执行之前，新 order_num 还没有生成。对于 orders 的每次插入使用这个触发器将总是返回新的订单号。

为测试这个触发器，试着插入一下新行，如下所示：

INSERT INTO orders(order_date, cust_id)
VALUES(NOW(), 10001);

----------------------------------------------

mysql> INSERT INTO orders(order_date, cust_id)
    -> VALUES(NOW(), 10001);
Query OK, 1 row affected (0.01 sec)

orders 包含 3 个列。order_date 和 cust_id 必须给出，order_num 由 MySQL 自动生成，而现在 order_num 还自动被返回。

BEFORE 或 AFTER？

通常，将 BEFORE 用于数据验证和净化（目的是保证插入表中的数据确实是需要的数据）。本提示也适用于 UPDATE 触发器。

4.4.2、DELETE 触发器

DELETE 触发器在 DELETE 语句执行之前或之后执行。需要知道以下两点：

• 在 DELETE 触发器代码内，你可以引用一个名为 OLD 的虚拟表，访问被删除的行；
• OLD 中的值全都是只读的，不能更新。

下面的例子演示使用 OLD 保存将要被删除的行到一个存档表中：

CREATE TRIGGER deleterorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
  INSERT INTO archive_orders(order_num, order_date, cust_id)
	VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END;

在任意订单被删除前将执行此触发器。它使用一条 INSERT 语句将 OLD 中的值（要被删除的订单）保存到一个名为 archive_orders 的存档表中（为实际使用这个例子，你需要用与 orders 相同的列创建一个名为 archive_orders 的表）。

使用 BEFORE DELETE 触发器的优点（相对于 AFTER DELETE 触发器来说）为，如果由于某种原因，订单不能存档，DELETE 本身将被放弃。

4.4.3、UPDATE 触发器

UPDATE 触发器在 UPDATE 语句执行之前或之后执行。需要知道以下几点：

• 在 UPDATE 触发器代码中，你可以引用一个名为 OLD 的虚拟表访问以前（UPDATE 语句前）的值，引用一个名为 NEW 的虚拟表访问新更新的值；
• 在 BEFORE UPDATE 触发器中，NEW 中的值可能也会被更新（允许更改将要用于 UPDATE 语句中的值）；
• OLD 中的值全都是只读的，不能更新。

下面的例子保证州名缩写总是大写（不管 UPDATE 语句中给出的是大写还是小写）：

-- UPDATE 触发器
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = UPPER(NEW.vend_state);

显然，任何数据净化都需要在 UPDATE 语句之前进行，就像这个例子一样。每次更新一个行时，NEW.vend_state 中的值（将用来更新表行的值）都用 UPPER(NEW.vend_state) 替换。