第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 beginif @custid<>@prvcustidbegin set @prvcustid=@custid; set @runqty=0;endset @runqty=@runqty+@qty;insert into @Result values(@custid, @ordermonth, @qty, @runqty);fetch next from c into @custid, @ordermonth, @qty;endclose c;select custid, CONVERT(varchar(7), ordermonth, 121) as ordermonth,qty, runqtyfrom @Resultorder by custid, ordermonth;--10.5 临时表--10.5.1 局部临时表--要创建局部临时表,只须要在命名时以单个数字符号(#)作为前缀。--所有三种类型的临时表都是在tempdb数据库中创建的。select YEAR(o.orderdate) as orderyear, SUM(od.qty) as qtyinto dbo.#MyOrderTotalsByYearfrom Sales.Orders as ojoin Sales.OrderDetails as odon od.orderid = o.orderidgroup by YEAR(o.orderdate);select cur.orderyear, cur.qty as curyearqty, prv.qty as prvyearqtyfrom dbo.#MyOrderTotalsByYear as curleft outer join dbo.#MyOrderTotalsByYear as prvon 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 qtyfrom sales.orders as ojoin sales.orderdetails as odon od.orderid = o.orderidgroup by year(o.orderdate);select cur.orderyear, cur.qty as curyearqty, prv.qty as prvyearqtyfrom @MyOrderToTalsYear as curleft outer join @MyOrderToTalsYear as prvon cur.orderyear=prv.orderyear+1order by cur.orderyear;--10.5.4 表类型--通过创建表类型,可以把表的定义保存到数据库中,以后在定义表变量,--存储过程和用户自定义函数的输入参数时,可以将表类型作为表的定义而重用.use TSQLFundamentals2008;if TYPE_ID('dbo.ordertotalsbyyear') is not nulldrop type dbo.ordertotalsbyyear;create type dbo.ordertotalsbyyear as table(orderyear int not null primary key,qty int not null);godeclare @MyOrderTotalsByYear as dbo.ordertotalsbyyear; insert into @MyOrderToTalsYear(orderyear, qty)select YEAR(o.orderdate) as orderyear,SUM(od.qty) as qtyfrom Sales.Orders as ojoin Sales.OrderDetails as odon od.orderid = o.orderidgroup by YEAR(o.orderdate);select orderyear, qty from @MyOrderToTalsYear;