06 视图、存储过程和事务

06 视图、存储过程和事务

标签：SQL、数据库

1.创建视图

  视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。即，是动态的，只可查询的。
  最基本的，使用CREATE VIEW语句来创建。使用DROP VIEW viewname来删除。如果想要重命名视图的话，必须先删除这个视图，然后重新创建。

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;

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

2.拼接视图

CREATE VIEW VendorLocations 
AS SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')' 
AS vend_title 
FROM Vendors;

3.EXECUTE——存储过程

  首先，我们看下我们将要用到的Products表，是什么样的。

  通过把处理封装在一个易用的单元中，可以简化复杂的操作（如前面例子所述）。
由于不要求反复建立一系列处理步骤，因而保 证了数据的一致性。如果所有开发人员和应用 程序都使用同一存储过程，则所使用的代码都 是相同的。

①存储过程的执行远比编写要频繁得多，因此我们先介绍存储过程的执行。执行存储过程的 SQL语句很简单，即EXECUTE。

EXECUTE AddNewProduct('JTS01',
					  'Stuffed Eiffel Tower', 
					  6.49, 
					  'Plush stuffed toy with the text La ➥Tour Eiffel in red white and blue' );

  分析：这里执行一个名为AddNewProduct的存储过 程，将一个新产品添加到Products表中。AddNewProduct有四个参数，分别是：供应商ID（Vendors表的主键）、产品名、价格和 描述。这4个参数匹配存储过程中4个预期变 量（定义为存储过程自身的组成部分）。此存储过程将新行添加到Products表，并将传入的属性赋给相应的列。
  我们注意到，在Products表中还有另一个需 要值的列prod_id列，它是这个表的主键。 为什么这个值不作为属性传递给存储过程？ 要保证恰当地生成此ID，最好是使生成此ID 的过程自动化（而不是依赖于最终用户的输入）。

②创建存储过程

CREATE PROCEDURE MailingListCount ( ListCount OUT INTEGER ) 
IS v_rows INTEGER; 
BEGIN 
	SELECT COUNT(*) INTO v_rows 
	FROM Customers 
	WHERE NOT cust_email IS NULL; 
	ListCount := v_rows; 
END;

分析：
  (1)这个存储过程有一个名为ListCount的参数。此参数从存储过程返回一个值而不是传 递一个值给存储过程。关键字OUT用来指示这 种行为。
  (2)Oracle支持IN（传递值给存储过程）、OUT（从存储过程返回值，如这里）、INOUT（既传递值给存储过程也从存 储过程传回值）类型的参数。存储过程的代码括在BEGIN和END语句中，这里执行一条简 单的SELECT语句，它检索具有邮件地址的顾客。然后用检索出的行数设 置ListCount（要传递的输出参数）。

  执行这个存储过程：

var ReturnValue NUMBER 
EXEC MailingListCount(:ReturnValue); 
SELECT ReturnValue;

4.事务处理

  使用事务处理（transaction processing），通过 确保成批的SQL操作要么完全执行，要么完全 不执行，来维护数据库的完整性。

下面是关于事务处理需要知道的几个术语：
• 事务（transaction）指一组SQL语句。
• 回退（rollback）指撤销指定SQL语句的过程。
• 提交（commit）指将未存储的SQL语句结果 写入数据库表。
• 保留点（savepoint）指事务处理中设置的临 时占位符（placeholder），可以对它发布回退 （与回退整个事务处理不同）。

  不同DBMS用来实现事务处理的语法有所不 同。在使用事务处理时请参阅相应的DBMS 文档。

  如在SQL Server中，标识如下：

BEGIN TRANSACTION
... 
COMMIT TRANSACTION

  Oracle使用的语法：

SET TRANSACTION
...

①使用ROLLBACK
  SQL的ROLLBACK命令用来回退（撤销）SQL 语句，请看下面的语句：

DELETE FROM Orders; 
ROLLBACK

  在此例子中，执行DELETE操作，然后 用ROLLBACK语句撤销。虽然这不是最有用的
例子，但它的确能够说明，在事务处理块 中，DELETE操作（与INSERT和UPDATE操作 一样）并不是最终的结果。

②使用COMMIT
  一般的SQL语句都是针对数据库表直接执行 和编写的。这就是所谓的隐式提交（implicit commit），即提交（写或保存）操作是自动 进行的。
  在事务处理块中，提交不会隐式进行。不 过，不同DBMS的做法有所不同。有的DBMS 按隐式提交处理事务端，有的则不这样。
  下面是 一个SQL Server的例子：

BEGIN TRANSACTION
DELETE OrderItems WHERE order_num = 12345
DELETE Orders WHERE order_num = 12345 
COMMIT TRANSACTION

  Oracle中的写法：

SET TRANSACTION 
DELETE OrderItems WHERE order_num = 12345; 
DELETE Orders WHERE order_num = 12345; 
COMMIT;

  在这个\例子中，从系统中完全删除订单12345。因为涉及更新两个数据库 表Orders和OrderItems，所以使用事务处理块来保证订单不被部分删除。最后的COMMIT语句仅在不出错时写出更改。如果第一条DELETE起作用，但第二条失败， 则DELETE不会提交。
  

③使用保留点
  要支持回退部分事务，必须在事务处理块中 的合适位置放置占位符。这样，如果需要回 退，可以回退到某个占位符。在SQL中，这些占位符称为保留点。

①在Oracle中，建立保留点，语句为：

SAVEPOINT delete1

返回保留点为：

ROLLBACK TO delete1;

②在SQL Server中，建立保留点，语句为：

SAVE TRANSACTION delete1;

返回保留点为：

ROLLBACK TRANSACTION delete1;

5.主键

  关于主键、约束什么的，可以参考其他的博客：https://blog.csdn.net/weirdowang/article/details/83181346