SQL语句(进阶部分)

摘自《SQL必知必会》

1. 视图

视图是虚拟的表，与包含数据的表不一样，视图只包含动态检索数据的查询。
视图名需要唯一

比如将下面这条查询语句包装成一个名为 productCustomers 的虚拟表

#创建视图
create view productCustomers as
select cust_name,cust_concat from customers, orders, orderItems 
where customer.cust_id = orders.cust_id
and orderItems.order_num = orders.order_num
and prod_id = 'R1001';


# 通过视图进行查询
select cust_name,cust_concat from productCustomers where pro_id = 'R1001';

为什么使用视图：
1.重用SQL
2.简化复杂SQL操作
3.使用表的一部分而不是整个表
4.保护数据…

2.存储过程

创建存储过程

Oracle

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

这个存储过程有一个名为ListCount的参数，此参数返回一个值，而不是传递一个值给存储过程。OUT 用来指示这种行为。

Oracle 支持 IN 传递值给存储过程、OUT 从存储过程返回值，INOUT即传值也返回值。
存储过程的代码括在BEGIN 和 END 语句中。

上面的语句表示：检索出具有cust_email的 customer ，然后用检索出的行数设置ListCount。

声明一个变量来保存存储过程返回的值
执行存储过程
使用 select 语句显示返回的值

#oracle
var ReturnValue NUMBER
EXEC MailingListCount(:ReturnValue);
Select ReturnValue

SQL Server

SQL Server中局部变量名以@开头

#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;

3. 事务管理

开启和提交事务
SQL Server

begin transaction
...
commit transaction

MySQL

start transaction
...

Oracle

set transaction
...
commit;

回滚

delete from student;
rollback;

保留点

复杂的事务，可能需要部分提交或回退
设置保留点，也就是在某一行设置标志，发生错误回退到某个点之前

创建占位符

#Oracle/MySQL
savepoint delete1;

#SQL Server
save transaction delete1;

回滚到某个保留点

#SQL Server
rollback transaction delete1;
#MYSQL/Oracle
rollback to delete1;

待续…