第10章 可编程对象(3)

--10.3 游标
--可以用游标来处理查询返回的结果集中的各行,以指定的顺序一次只处理一行.

--游标的缺陷:
--使用游标严重违背了关系模型,关系模型要求按照集合来考虑问题.
--游标逐行对记录进行操作会带来一定的开销.
--使用游标需要为解决方案的物理操作编写很多代码,换句话说,得写很多代码来描述如何处理数据.
set nocount on;
use TSQLFundamentals2008;

declare @Result as Table
(custid int,
ordermonth datetime,
qty int,
runqty int,
primary key(custid, ordermonth))

declare 
@custid as int,
@prvcustid as int,
@ordermonth as datetime,
@qty as int,
@runqty as int;

declare c cursor FAST_FORWARD /*read only, forward only*/ FOR
 select custid, ordermonth, qty
 from Sales.CustOrders
 order by custid, ordermonth;
 
 open c
 
fetch next from c into @custid, @ordermonth, @qty;
 
 set @prvcustid=@custid;
 set @runqty=0;
 
 while @@FETCH_STATUS=0
 begin
	if @custid<>@prvcustid
	begin
	 set @prvcustid=@custid;
	 set @runqty=0;
	end
	set @runqty=@runqty+@qty;
	insert into @Result values(@custid, @ordermonth, @qty, @runqty);
	
	fetch next from c into @custid, @ordermonth, @qty;
end

close c;

select custid, CONVERT(varchar(7), ordermonth, 121) as ordermonth,
qty, runqty
from @Result
order by custid, ordermonth;

--10.5 临时表
--10.5.1 局部临时表
--要创建局部临时表,只须要在命名时以单个数字符号(#)作为前缀。
--所有三种类型的临时表都是在tempdb数据库中创建的。
select YEAR(o.orderdate) as orderyear, SUM(od.qty) as qty
into dbo.#MyOrderTotalsByYear
from Sales.Orders as o
join Sales.OrderDetails as od
on od.orderid = o.orderid
group by YEAR(o.orderdate);

select cur.orderyear, cur.qty as curyearqty, prv.qty as prvyearqty
from dbo.#MyOrderTotalsByYear as cur
left outer join dbo.#MyOrderTotalsByYear as prv
on cur.orderyear=prv.orderyear+1;

--10.5.2 全局临时表
--要创建全局临时表,只需要在命名时用两个数字符号(##)作为前缀。
--访问全局临时表不需要任何特殊的权限，所有人都可以获取完整的DDL和DML访问。
create table dbo.##Globals
(id sysname not null primary key,
val sql_variant not null);

insert into dbo.##Globals(id, val) values(N'i', CAST(10 as int));

select val from dbo.##Globals where id = N'i';

--只要创建全局临时表的会话断开了数据库的联接,而且也没有其他活动引用全局临时表时,SQL Servr就会自动删除它.

--10.5.3 表变量
--声明表变量的方式和生命其他变量类似,使用的都是declare语句.
--和局部临时表类似,表变量也只对创建它的会话可见,但允许访问的范围更有限,它只对当前批处理可见.
--从性能上考虑,对于少量的数据(只有几行)，使用表变量更有意义，否则，应该使用临时表。
declare @MyOrderToTalsYear as table 
(orderyear int not null primary key,
qty int not null);

insert into @MyOrderToTalsYear(orderyear, qty)
select year(o.orderdate) as orderyear,
sum(od.qty) as qty
from sales.orders as o
join sales.orderdetails as od
on od.orderid = o.orderid
group by year(o.orderdate);

select cur.orderyear, cur.qty as curyearqty, prv.qty as prvyearqty
from @MyOrderToTalsYear as cur
left outer join @MyOrderToTalsYear as prv
on cur.orderyear=prv.orderyear+1
order by cur.orderyear;

--10.5.4 表类型
--通过创建表类型,可以把表的定义保存到数据库中,以后在定义表变量,
--存储过程和用户自定义函数的输入参数时,可以将表类型作为表的定义而重用.

use TSQLFundamentals2008;
if TYPE_ID('dbo.ordertotalsbyyear') is not null
drop type dbo.ordertotalsbyyear;

create type dbo.ordertotalsbyyear as table
(orderyear int not null primary key,
qty int not null);
go

declare @MyOrderTotalsByYear as dbo.ordertotalsbyyear; 

insert into @MyOrderToTalsYear(orderyear, qty)
select YEAR(o.orderdate) as orderyear,
SUM(od.qty) as qty
from Sales.Orders as o
join Sales.OrderDetails as od
on od.orderid = o.orderid
group by YEAR(o.orderdate);

select orderyear, qty from @MyOrderToTalsYear;