SQL必知必会(五) 使用视图、使用储存过程、管理事务处理、使用游标、高级SQL特性

十八、使用视图

视图

SELECT prod_name, vend_name, prod_name, prod_price, quantity
FROM OrderItems, Vendors, Products
WHERE Vendors.vend_id = Products.vend_id
AND OrderItems.prod_id = Products.prod_id
AND order_num = 20007;

将上面查询包装成一个名为ProductCustomers的虚拟表，则可以很轻松地检索出相同的数据

SELECT prod_name, vend_name, prod_name, prod_price, quantity
FROM ProductCustomers
WHERE order_num = 20007;

为什么使用视图

1. 简化复杂的SQL操作
2. 使用表的一部分而不是整张表
3. 保护数据
4. 更改数据格式和表示。（可返回与底层表格式不同的数据）

创建视图

CREATE VIEW ProductCustomers AS
SELECT prod_name, vend_name, prod_name, prod_price, quantity
FROM OrderItems, Vendors, Products
WHERE Vendors.vend_id = Products.vend_id
AND OrderItems.prod_id = Products.prod_id

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

SELECT RTRIM(vend_name) + '(' + RTRIM(vend_country) + ')'
FROM Vendors
ORDER BY vend_name;

如果经常需要上面这个格式的结果，可转换成视图

CREATE VIEW VendorLocations AS
SELECT RTRIM(vend_name) + '(' + RTRIM(vend_country) + ')'
FROM Vendors

用视图过滤不想要的数据

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 OrderItems
WHERE order_num = 20008;

十九、使用储存过程

储存过程就是为以后使用而保存的一条或多条AQL语句，功能类似为批文件。

执行储存过程

EXECUTE AddNewProduct(  'JTS01',
                        'Stuffed Eiffel Tower'
                        6.49
                        'This is a description');

这里执行一个名为AddNewProduct的储存过程，将一个新产品添加到Product表中。

创建储存过程

CREATE PROCEDURE MailingListCount
AS
DECLARE @cnt INTEGER  --@cnt局部变量
SELECT @cnt = COUNT(*)
FROM Customers
WHERE NOT cust_email IS NULL;
RETURN @cnt;

--调用
DECLARE @ReturnValue INT
EXECUTE @ReturnValue = MailingListCount
SELECT @ReturnValue;

CREATE PROCEDURE NewOrder @cust_id CHAR(10)
DECLARE @order_num INTEGER
SELECT @order_num = MAX(order_num)
FROM Orders
SELECT @order_num = @order_num + 1
INSERT INTO Orders(order_num,order_date,cust_id)
VALUES(@order_num,GETDATE(),@cust_id)
RETURN @order_num;

改储存过程在Orders表中创建了一个新订单，它只有一个参数cust_id。
首先声明一个局部变量order_num储存订单号，接着检索最大订单号并加1，然后将新订单号、当前系统时间、顾客ID插入到加1那行。最后返回订单号。

二十、管理事务处理

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

• 事务
• 回退
• 提交
• 保留点

控制事务处理

-- SQL Server版本
BEGIN TRANSACTION
...
COMMIT TRANSACTION

-- MySQL 版本
START TRANSACTION

--Oracle版本
SET TRANSACTION

使用ROLLBACK 回退

DELETE FROM Orders;
ROLLBACK;

使用 COMMIT

BEGIN TRANSACTION
DELETE OrderIetms WHERE order_num = 12345;
DELETE Orders WHERE order_num = 12345;
COMMIT TRANSACTION

使用保留点

-- oracle、MySQL版本
SAVEPOINT delete1;
ROLLBACK TO delete1;

-- SQL SERVER版本
SAVE TRANSACTION delete1;
ROLLBACK TRANSACTION delete1;

二十一、使用游标

有时，需要在检索出来的行中前进或后退一行或多行，这就是游标的作用

使用游标

-- MySQL、SQL Server版本
DECLARE CustCursor CURSOR
FOR
SELECT * FROM Customers
WHERE cust_email IS NULL

--oracle版本
DECLARE CURSOR CustCursor
IS
SELECT * FROM Customers
WHERE cust_email IS NULL

OPEN CURSOR CustCursor

使用OPEN CURSOR打开游标，FETCH访问游标数据

DECLARE TYPE CustCursors IS REF CURSOR
    RETURN Customers%ROWTPYE
DECLARE CustRecord Customer%ROWTYPE
BEGIN 
    OPEN CustRecord;
    LOOP
    FETCH CustCursor INTO CustRecord;
    EXIT WHEN CustCursor%NOTFOUND; --在取不出更多行时退出
    ...
    END LOOP;
    CLOSE CustCursor;
END;

二十二、高级SQL特性

约束

主键

CREATE TABLE Vendors
(
  vend_id      char(10) NOT NULL PRIMARY KEY ,
  vend_name    char(50) NOT NULL ,
  vend_address char(50) NULL ,
  vend_city    char(50) NULL ,
  vend_state   char(5)  NULL ,
  vend_zip     char(10) NULL ,
  vend_country char(50) NULL 
);

-- 也可在表创建后更新
ALTER TABLE Vendors 
ADD PRIMARY KEY (vend_id);

外键

外键是表中的一列，其值必须列在另一个表的主键中。

CREATE TABLE Orders
(
  order_num  int      NOT NULL PRIMARY KEY,
  order_date datetime NOT NULL ,
  cust_id    char(10) NOT NULL REFERENCES Customers(cust_id)
);

-- 也可在表创建后更新
ALTER TABLE Orders 
ADD CONSTRAINT 
FOREIGN KEY (cust_id) REFERENCES Customers (cust_id);

唯一约束

用来保证一列中数据是唯一的，如社会号。 但可以修改和更新

检查约束

• 检查最大最小值
• 指定范围
• 只允许特定的值

CREATE TABLE OrderItems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL CHECK (quantity > 0),
  item_price decimal(8,2) NOT NULL 
);

ADD CONSTRAINT CHECK (gender LIKE '[MF]')

索引

• 索引用来排序数据以加快搜索和排序操作的速度
• 可以在一列或者多个列定义索引
• 索引改善了搜索的性能，但降低了数据插入修改和删除的性能，因为在执行这些操作时要动态更新索引

CREATE INDEX prod_name_id
ON Products (prod_name)

索引必须唯一命名

触发器

触发器是特殊的储存过程，可以与INSERT、UPDATE、DELETE相关联

触发器常见用途

• 保证数据一致 。 例如将州名大写
• 基于某个表的变动在其他表上执行活动。 例如每当更改时写入日志表
• 进行额外的验证并根据需要回退数据
• 计算计算列的值或更新时间戳

-- SQL Server版本
CREATE TRIGGER customer_state
ON Customers
FOR INSERT , UPDATE
AS
UPDATE Customers
SET cust_state = Upper(cust_state)
WHERE Customers.cust_id = inserted.cust_id;

--oracle版本
CREATE TRIGGER customer_state
ON Customers
AFTER INSERT OR UPDATE
FOR EACH NOW
BEGIN
UPDATE Customers
SET cust_state = Upper(cust_state)
WHERE Customers.cust_id = :OLD.cust_id;
END;

约束比触发器更快，在可能的时候，尽量使用约束

数据库安全

• GRANT
• REVOKE