第18课 使用视图
视图
what？视图是虚拟的表，只包含使用时动态检索数据的查询
why？ 重用SQL语句 简化复杂的SQL操作 使用表的一部分而不是整个表 保护数据 更改数据格式和表示
how？
- 视图必须唯一命名
- 对于可以创建的视图数目没有限制
- 创建视图必须具有足够的访问权限
- 视图可以嵌套
- 许多DBMS禁止在视图查询中使用ORDER BY子句
- 有点要求对返回的所有列进行命名
- 视图不能索引，也不能有关联的触发器或默认值
-有的DBMS把视图作为只读的查询，表示可以从视图检索数据，但不能将数据写回到底层表 - 有些DBMS允许创建这样的视图，它不能进行导致行不再属于视图的插入或更新
创建视图
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;
创建可重用的视图
# 检索订购了产品RGAN01的顾客
SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id='RGAN01';
- 用视图重新格式化检索出的数据
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;
- 使用视图与计算字段
CREATE VIEW OrderItemsExpanded AS
SELECT order_num, prod_id, quantity, item_price,
quantity * item_price AS expanded_price
FROM OrderItems;
SELECT *
FROM OrderItemsExpanded
WHERE order_num=20008;
第19课 使用存储过程
what? 存储过程就是为以后使用而保存的一条或多条SQL语句
可将其视为批文件，虽然他们的作用不仅限于批处理
why?
- 通过把处理封装在一个易用的单元中，简化复杂的操作
- 由于不要求反复建立一系列处理步骤，因而保证了数据的一致性
- 简化对变动的管理- 安全性
- 存储过程通常以编译过的形式存储，所以DBMS处理命令所需的工作量少，提高了性能
缺陷：不同DBMS中存储过程语法有所不同;比编写基本SQL语句复杂
how：
创建存储过程
- 检索具有邮件地址的顾客
Oracle版本 创建
#OUT指示此参数从存储过程返回一个值而不是传递一个值给存储过程
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
执行
var ReturnValue NUMBER
EXEC MailingListCount(:ReturnValue);
SELECT ReturnValue;
SQL Server版本 创建
CREATE PROCEDURE MailingListCount
AS
DECLARE @cnt INTEGER
SELECT @cnt=COUNT(*)
FROM Customers
WHERE NOT cust_email IS NULL;
RETURN @cnt;
调用：
#声明一个变量来保存存储过程返回的任何值
DECLARE @ReturnValue INT
EXECUTE @ReturnValue=MailingListCount;
SELECT @ReturnValue; #SELECT语句显示返回的值
- 在Orders表中插入一个新订单
SQL Server版本1 创建
CREATE PROCEDURE NewOrder @cust_id CHAR(10)
AS
-- Declare variable for order number
DECLARE @order_num INTEGER
-- Get current highest order number
SELECT @order_num=MAX(order_num)
FROM Orders
-- Determine next order number
SELECT @order_num=@order_num+1
-- Insert new order
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(@order_num, GETDATE(), @cust_id)
-- Return order number
RETURN @order_num;
SQL Server版本2 创建，由DBMS生成订单号，SQL Server称这些自动增量的列为标识字段。DBMS对日期使用默认值（GETDATE()函数）
CREATE PROCEDURE NewOrder @cust_id CHAR(10)
AS
INSERT INTO Orders(cust_id)
VALUES(@cust_id)
-- SQL Sever中可在全局变量@@IDENTITY中得到自动生成的ID
SELECT order_num=@@IDENTITY;
