《SQL必知必会》笔记四：18-21课
四、视图、存储过程、事务处理、游标
4.1 视图
MySQL 从版本 5 起开始支持视图
/*理解视图*/ /*在下例中，检索其他产品（或多个产品）的相同数据，必须修改最后的WHERE 子句。*/ 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 = 'RGAN01'; /*假如可以把整个查询包装成一个名为 ProductCustomers 的虚拟表，则可以如下轻松地检索出相同的数据*/ SELECT cust_name, cust_contact FROM ProductCustomers WHERE prod_id = 'RGAN01';
为什么使用视图？
重用 SQL 语句。
简化复杂的 SQL 操作。在编写查询后，可以方便地重用它而不必知道其基本查询细节。
使用表的一部分而不是整个表。
保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。
更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。
创建视图之后，可以用与表基本相同的方式使用它们。可以对视图执行SELECT 操作，过滤和排序数据，将视图联结到其他视图或表，甚至添加和更新数据。
重要的是，要知道视图仅仅是用来查看存储在别处数据的一种设施。视图本身不包含数据，因此返回的数据是从其他表中检索出来的。
关于视图创建和使用的一些最常见的规则和限制：
与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）
对于可以创建的视图数目没有限制。
创建视图，必须具有足够的访问权限。这些权限通常由数据库管理人员授予。
视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造视图。所允许的嵌套层数在不同的 DBMS 中有所不同（嵌套视图可能会严重降低查询的性能，因此在产品环境中使用之前，应该对其进行全面测试）。
许多 DBMS 禁止在视图查询中使用 ORDER BY 子句。
有些 DBMS 要求对返回的所有列进行命名，如果列是计算字段，则需要使用别名。
视图不能索引，也不能有关联的触发器或默认值。
有些 DBMS 把视图作为只读的查询，这表示可以从视图检索数据，但不能将数据写回底层表。详情请参阅具体的 DBMS 文档。
有些 DBMS 允许创建这样的视图，它不能进行导致行不再属于视图的插入或更新。例如有一个视图，只检索带有电子邮件地址的顾客。如果更新某个顾客，删除他的电子邮件地址，将使该顾客不再属于视图。这是默认行为，而且是允许的，但有的 DBMS 可能会防止这种情况发生。
下面给出几个使用视图的例子：
/*创建视图CREATE VIEW，删除视图使用DROP VIEW*/ CREATE VIEW ProductCustomers 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; /*使用视图*/ SELECT cust_name, cust_contact FROM ProductCustomers WHERE prod_id = 'RGAN01';
/*初始语句*/ SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' AS vend_title FROM Vendors ORDER BY vend_name; /*设为视图*/ CREATE VIEW VendorLocations AS SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' AS vend_title FROM Vendors; /*使用视图*/ SELECT * FROM VendorLocations;
/*用视图过滤没有电子邮件地址的顾客*/ CREATE VIEW CustomerEMailList AS SELECT cust_id, cust_name, cust_email FROM Customers WHERE cust_email IS NOT NULL; /*使用视图*/ SELECT * FROM CustomerEMailList;
总结：
视图为虚拟的表。它们包含的不是数据而是根据需要检索数据的查询。
视图提供了一种封装 SELECT 语句的层次，可用来简化数据处理，重新格式化或保护基础数据。
4.2 存储过程
MySQL 5 已经支持存储过程。
简单来说，存储过程就是为以后使用而保存的一条或多条 SQL 语句。
可将其视为批文件，虽然它们的作用不仅限于批处理。
使用存储过程有三个主要的好处，即简单、安全、高性能。
4.3 事务处理
使用事务处理（transaction processing），通过确保成批的 SQL 操作要么完全执行，要么完全不执行，来维护数据库的完整性。
事务处理用来管理 INSERT、UPDATE 和 DELETE 语句。不能回退 SELECT语句（回退 SELECT 语句也没有必要），也不能回退 CREATE 或 DROP 操作。
事务处理中可以使用这些语句，但进行回退时，这些操作也不撤销。
一些术语解释：
事务（transaction）指一组 SQL 语句；
回退（rollback）指撤销指定 SQL 语句的过程；
提交（commit）指将未存储的 SQL 语句结果写入数据库表；
保留点（savepoint）指事务处理中设置的临时占位符（placeholder），可以对它发布回退（与回退整个事务处理不同）。
管理事务的关键在于将 SQL 语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。
/*SQL 的 ROLLBACK 命令用来回退（撤销）SQL 语句*/ DELETE FROM Orders; ROLLBACK;
/*一个事务的例子*/
/*最后的 COMMIT 语句仅在不出错时写出更改。如果第一条 DELETE 起作用，但第二条失败，则 DELETE 不会提交。*/
BEGIN TRANSACTION DELETE OrderItems WHERE order_num = 12345 DELETE Orders WHERE order_num = 12345 COMMIT TRANSACTION
使用简单的 ROLLBACK 和 COMMIT 语句，就可以写入或撤销整个事务。但是，只对简单的事务才能这样做，复杂的事务可能需要部分提交或回退。
要支持回退部分事务，必须在事务处理块中的合适位置放置占位符。这样，如果需要回退，可以回退到某个占位符。
/*放置占位符*/ SAVE TRANSACTION delete1; /*一个完整的例子*/ BEGIN TRANSACTION INSERT INTO Customers(cust_id, cust_name) VALUES('1000000010', 'Toys Emporium'); SAVE TRANSACTION StartOrder; INSERT INTO Orders(order_num, order_date, cust_id) VALUES(20100,'2001/12/1','1000000010'); IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder; INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, ➥item_price) VALUES(20100, 1, 'BR01', 100, 5.49); IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder; INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, ➥item_price) VALUES(20100, 2, 'BR03', 100, 10.99); IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder; COMMIT TRANSACTION
4.4 游标
情景：SQL 检索操作返回一组称为结果集的行，这组返回的行都是与 SQL 语句相匹配的行（零行或多行）。
简单地使用 SELECT 语句，没有办法得到第一行、下一行或前 10 行。
游标（cursor）是一个存储在 DBMS 服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。
在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。
游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。
游标常见的一些选项和特性如下：
能够标记游标为只读，使数据能读取，但不能更新和删除。
能控制可以执行的定向操作（向前、向后、第一、最后、绝对位置、相对位置等）。
能标记某些列为可编辑的，某些列为不可编辑的。
规定范围，使游标对创建它的特定请求（如存储过程）或对所有请求可访问。
指示 DBMS 对检索出的数据（而不是指出表中活动数据）进行复制，使数据在游标打开和访问期间不变化。
使用游标涉及几个明确的步骤：
在使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的 SELECT 语句和游标选项。
一旦声明，就必须打开游标以供使用。这个过程用前面定义的 SELECT语句把数据实际检索出来。
对于填有数据的游标，根据需要取出（检索）各行。
在结束游标使用时，必须关闭游标，可能的话，释放游标（有赖于具体的 DBMS）。
/*创建游标*/ /*DECLARE 语句用来定义和命名游标，这里为CustCursor*/ DECLARE CustCursor CURSOR FOR SELECT * FROM Customers WHERE cust_email IS NULL； /*定义游标之后，就可以打开它了*/ OPEN CURSOR CustCursor /*CLOSE 语句用来关闭游标*/ CLOSE CustCursor
一旦游标关闭，如果不再次打开，将不能使用。第二次使用它时不需要再声明，只需用 OPEN 打开它即可。
