第九部分 视图和存储过程
视图是虚拟的表。与包含数据的表不一样，视图只包含使用动态检索数据的查询，并不包含表中应该有的任何列或数据，即包含的不是数据。视图提供了一种MySQL的SELECT语句层次的封装，可用来简化数据处理以及重新格式化基础数据或保护基础数据。
为什么使用视图？使用视图的作用主要有以下几个理由
- 重用SQL语句；
- 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节；
- 使用表的组成部分而不是整个表；
- 保护数据，可以给用户授予表的特定部分的访问权限而不是整个表的访问权限；
- 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。
- 视图的规则和限制
- 视图必须唯一命名，即不能给视图取与别的视图或表相同的名字；
- 对于可以创建的视图数目没有限制；
- 为了创建视图，必须具有足够的访问权限。这些权限通常由数据库管理人员授予；
- 视图可以嵌套，即可以利用其他视图中检索数据的查询来构造一个视图；
- ORDER BY可以用在视图中，但如果从该视图检索数据的SELECT中也含有ORDER BY，那么该视图中的ORDER BY将被覆盖；
- 视图不能索引，也不能有关联的触发器或默认值；
- 视图可以和表一起使用。
1.使用视图
- 视图用CREATE VIEW语句来创建；
- 使用SHOW CREATE VIEW viewname;来查看创建视图的语句；
- 用DROP删除视图，即DROP VIEW viewname；
更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第2条更新语句会创建一个视图；如果要更新的视图存在，则第2条更新语句会替换原有视图。
示例1：创建一个名为productcustomers的视图，返回已订购了任意产品的所有客户的列表
SQL语句：CREATE VIEW productcustomers AS SELECT cust_name, cust_contact FROM customers, orders, orderitems WHERE customers.cust_id = orders.cust_id AND orderitems.order_num=orders.order_num;
示例2：检索订购某个特定产品的客户
SQL语句：SELECT cust_name, cust_contact FROM productcustomers WHERE prod_id = 'TNT2';
示例3：检索某个特定订单中的物品，计算每种物品的总价格
SQL语句：CREATE VIEW expandprice AS SELECT prod_id, quantity, item_price, quantity*item_price AS expand_price FROM orderitems;
SQL语句：SELECT * FROM expandprice WHERE order_num = 2005;
(1) 用视图重新格式化检索出的数据
示例：单个组合计算列中返回供应商名和位置
SQL语句：CREATE VIEW vendorlocations AS SELECT Contac(RTrim(vend_name)), '(', RTrim(vend_country), ')') AS vend_title FROM vendor ORDER BY vend_name;
SQL语句：SELECT * FROM vendorlocations;
(2) 用视图过滤数据
示例：过滤没有电子邮件地址的客户
SQL语句：CREATE VIEW customeremaillist AS SELECT cust_id, cust_name, cust_email FROM customers WHERE cust_email IS NOT NULL;
SQL语句：SELECT * FROM customeremaillist；
2.更新视图
通常，视图是可更新的，即可以对其使用INSERT、UPDATE、DELETE操作。更新一个视图将更新其基表，因为视图本身没有数据。若对视图增加或删除行，实际上是对其基表增加或删除行。但是，并非所有视图均可以更新。基本上说，如果MySQL不能正确地确定被更新的基数据，则不允许更新。更具体地说，以下几种操作，均不能进行视图的更新
- 分组（使用GROUP BY和HAVING);
- 联结；
- 子查询；
- 并；
- 聚集函数（Min()、Count()、Sum()等）；
- DISTINCE;
- 导出（计算）列。
存储过程
简言之，存储过程是为以后的使用而保存的一条或多条MySQL语句的集合。可以将其视为批文件。那么，为什么要使用它们呢？理由主要有以下几点
- 通过把处理封装在容易使用的单元中，简化复杂的操作。
- 简化对变动的管理。如果表名、列名或业务逻辑有变化，只需要更改存储过程的代码，使用人员不需要知道这些变化。
- 提高性能。因为使用存储过程比使用单独的SQL语句要快。
换句话说，使用存储过程有3个主要的好处，即简单、安全、高性能。
(1) 创建存储过程
示例1：返回产品平均价格的存储过程（无参数）
SQL语句：CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage
FROM products;
END;
示例2：返回产品价格的存储过程（有参数）
SQL语句：CREATE PROCEDURE productpricing(
OUT pl DECIMAL(8,2),
OUT ph DECIMAL(8,2),
OUT pa DECIMAL(8,2)
)
BEGIN
SELECT Min(prod_price) INTO p1 FROM products;
SELECT Max(prod_price) INTO ph FROM products;
SELECT Avg(prod_price) INTO pa FROM products;
END;
此存储过程接受3个参数：pl存储产品最低价格，ph存储产品最高价格，pa存储产品平均价格。每个参数必须具有指定的类型，这里使用十进制。关键字OUT指出相应的参数用来从存储过程传出一个值，即返回给调用者。MySQL支持IN（传递给存储过程）、OUT（从存储过程传出）和INOUT(对存储过程传入和传出)类型的参数。
示例3：使用IN和OUT参数，ordertotal接受订单号并返回该订单的合计
SQL语句：CREATE PROCEDURE ordertotal(
IN onumber INT,
OUT ototal DECIMAL(8,2)
)
BEGIN
SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = onumber INTO ototal;
END;
其中，onumber定义为IN，因为订单号被传入存储过程。ototal定义为OUT，因为要从存储过程中返回合计。
示例4：考虑这样的场景。你需要获得与以前一样的订单合计，但需要对合计增加营业税，不过只针对某些顾客。那么，需要做下面几件事情：获得合计、把营业税有条件地添加到合并、返回合计。
SQL语句：CREATE PROCEDURE ordertotal(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
)COMMENT 'Obtain order total, optionally adding tax'
BEGIN
DECLARE tax 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;
(2) 执行（调用）存储过程
示例：计算并返回产品的最低、最高和平均价格
SQL语句：CALL productpricing(@pricelow,@pricehigh,@priceaverage)
SQL语句：CALL productpriceing();
SQL语句：SELECT @pricelow,@pricehigh,@priceaverage;
SQL语句：CALL ordertotal(2005,@total);
必须给ordertotal传递两个参数，第一个参数为订单号，第二个参数为包含计算出来的合计的变量名。
SQL语句：CALL ordertotal(2005,0,@total);SELECT @total;
示例：显示订单2005和@total包含的值合计
SQL语句：SELECT @total;
(3) 删除存储过程
示例：删除存储过程productpricing，注意没有存储过程名后面的()
SQL语句：DROP PROCEDURE productpricing IF EXISTS；
(4) 检查存储过程
示例1：显示用来创建一个存储过程的CREATE语句
SQL语句：SHOW CREATE PROCEDURE ordertotal;
SQL语句：SHOW PROCEDURE STATUS LIKE 'ordertotal';
