SQL学习笔记6 视图 存储过程 事务处理

视图 VIEW

视图是虚拟的表，其只包含使用时动态检索数据的查询。视图本身不包含数据，返回的数据都是从其他表中检索出来的，一旦这些表的数据发生变化，对应的视图也将返回变化的数据。
（Access不支持视图，SQLite只支持视图只读）

其包括以下用途：

• 简化复杂的联结
• 重新格式化检索出的数据
• 过滤不想要的数据
• 创建包含计算字段的视图

正常检索数据，当需要检索订购了某种产品的顾客时，需要理解这些表的结果，并修改WHERE子句，显得很繁琐

SELECT cust_name，cust_contact，prod_id
FROM customers，orders，orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.cust_id.order_num;
AND prod_id = '001';

创建视图

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.cust_id.order_num;

检索视图

SELECT cust_name，cust_contact
FROM productcustomers 
WHERE prod_id = '001';

• 创建视图以后，以后有类似的查询，都可以直接检索视图，简化了查询步骤
• 与表一样，视图必须唯一命名
• 视图可以嵌套，即查询时利用其它视图来构造视图，但严重消耗性能
• 许多DBMS禁止在视图中使用ORDER BY子句
• 有些DBMS要求对返回的所有列命名
• 删除视图，可以使用DROP VIEW viewname

存储过程

存储过程就是为以后使用而保存的一条或多条SQL语句，经编译后存储在数据库，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。可将其视为批文件。但其他们的作用不仅限于批处理。个人理解有点像自定义的函数
Access和SQLite不支持存储过程

执行存储过程

EXECUTE addnewproduct('001','zjw','6.49');

• EXECUTE接受存储过程名和需要传递给它的任何参数

创建存储过程
自动创建订单（SQL server）

-- 接受一个参数，顾客id
CREATE PROCEDURE NewOrder @cust_id CHAR(10)
AS
-- 定义变量
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;

• 只适用于SQL server
• DECLARE用于声明变量，SQL server中所有局部变量都以@开头
• 不同的DBMS，实现存储过程的语法实现不一

事务处理 TRANSACTION

使用事务处理（Transaction processing），通过确保成批的SQL操作，要么完全执行、要么不执行，保证不包含不完整的操作结果。
事务管理用来管理INSERT， UPDATE ，DELETE，不能回退SELECT， CREATE ，DROP

• 事务：一组SQL语句
• 回退（rollback）：撤销指定SQL语句的过程
• 提交（commit）：将未存储的SQL语句结果写入数据表
• 保留点（savepoint）：指事务处理中设置的临时占位符，可以对它发布回退

SQLserver

BEGIN TRANSACTION
....
COMMIT TRANSACTION

MariaDB 和 MySQL

START TRANSACTION
...

Oracle

SET TRANSACTION
...

PostgreSQL

BEGIN
...

• 多数实现没有明确标识事务处理在何处结束，通常COMMIT用于保存更改，ROLLBACK用于撤销

使用ROLLBACK

DELETE FROM orders；
ROLLBACK；

使用COMMIT

BEGIN TRANSACTION
DELETE orderitems WHERE order_num = 12345
DELETE orders WHERE order_num = 12345
COMMIT TRANSACTION

• 任意一条语句出错，都不会提交更改

使用保留点
保留点可设置多个，使SQL语句能灵活的回退

SAVEPOINT delete1
...
ROLLBACK TO delete1

• 适用于MariaDB、MySQL、Oracle

SAVE TRANSACTION delete1
...
ROLLBACK TRANSACTION delete1

• 适用于SQL server