在SQL的SP开发中,游标有不可或缺的作用。但是,一些不当的使用游标,会造成严重系统性能的下降,所有呢,很多的人就想找一个方法,即能达到游标的功能,又对性能影响小一些。这就是本文要讲的用循环代替游标。
一般情况下,用游标的目的就是要达到一个循环,可是我们要是以循环来作呢,如何来控制当前的变量值就是关键。
下列代码中用到的表
create table tblUserDetail
(
UserName nvarchar(30) not null,/*用户名*/
UserAge int,/*用户年纪*/
UserDepartment nvarchar(30)/*用户部门*/
)上表中,有若干的数据。
现在呢,要取出各部门的前5句的人名,以年纪从大到小排.
用游标是可以作,
代码如下
declare @Department nvarchar(30)
declare curDepartment cursor for select distinct userDepartment from tblUserDetail
open curDepartment
fetch next from curDepartment into @Department
while @@fetch_status=0
begin
select top 5 username
from tblUserDetail
where userdepartment=@department
order by userage desc
fetch next from curDepartment into @department
end
close curDepartment
deallocate curDepartment
用循环来作呢,就要用到一个临时表
declare @tblDepartment table(
UID int identity(1,1),
Department nvarchar(30))
declare @Department nvarchar(30)
declare @nCount as int
declare @AllCount as int
select @ncount=1
insert @tblDepartment (Department)
select distinct userdepartment from tbluserDetail
select @allCount=@@rowcount
while @ncount<=@allCount
begin
select top 5 username
from tbluserdetail
where userdepartment=(select department from @tbldepartment where uid=@nCount)
order by userage desc
select @ncount=@ncount+1
end
这样,功能是相同,用循环时,不易引起由游标使用不当而造成的死锁,这在对数据量很大的表操作时,效果会很明显.
当然,有一些情况是无法用循环来代替游标的。比如说:一个表中有许多完全相同的记录,要对第一条作更新,第二条不改动,
这时就循环没有办法了。
一般情况下,用游标的目的就是要达到一个循环,可是我们要是以循环来作呢,如何来控制当前的变量值就是关键。
下列代码中用到的表
create table tblUserDetail
(
UserName nvarchar(30) not null,/*用户名*/
UserAge int,/*用户年纪*/
UserDepartment nvarchar(30)/*用户部门*/
)上表中,有若干的数据。
现在呢,要取出各部门的前5句的人名,以年纪从大到小排.
用游标是可以作,
代码如下
declare @Department nvarchar(30)
declare curDepartment cursor for select distinct userDepartment from tblUserDetail
open curDepartment
fetch next from curDepartment into @Department
while @@fetch_status=0
begin
select top 5 username
from tblUserDetail
where userdepartment=@department
order by userage desc
fetch next from curDepartment into @department
end
close curDepartment
deallocate curDepartment
用循环来作呢,就要用到一个临时表
declare @tblDepartment table(
UID int identity(1,1),
Department nvarchar(30))
declare @Department nvarchar(30)
declare @nCount as int
declare @AllCount as int
select @ncount=1
insert @tblDepartment (Department)
select distinct userdepartment from tbluserDetail
select @allCount=@@rowcount
while @ncount<=@allCount
begin
select top 5 username
from tbluserdetail
where userdepartment=(select department from @tbldepartment where uid=@nCount)
order by userage desc
select @ncount=@ncount+1
end
这样,功能是相同,用循环时,不易引起由游标使用不当而造成的死锁,这在对数据量很大的表操作时,效果会很明显.
当然,有一些情况是无法用循环来代替游标的。比如说:一个表中有许多完全相同的记录,要对第一条作更新,第二条不改动,
这时就循环没有办法了。