用游标!
一个表中有很多条记录,现在想把里面的数据一条条读取出来处理,应该怎么样做??--例子:
declare tb cursor for select name from sysobjects --定义游标
declare @name varchar(255)
open tb --打开游标
fetch next from tb into @name --取第一条记录
while @@fetch_status=0 --循环,判断取数是否成功
begin --循环,逐条处理记录
print @name --处理语句,这里仅显示
fetch next from tb into @name --继续取下一条
end
close tb --关闭游标
deallocate tb --释放游标资源
一个表中有很多条记录,现在想把里面的数据一条条读取出来处理,应该怎么样做??--例子:
declare tb cursor for select name from sysobjects --定义游标
declare @name varchar(255)
open tb --打开游标
fetch next from tb into @name --取第一条记录
while @@fetch_status=0 --循环,判断取数是否成功
begin --循环,逐条处理记录
print @name --处理语句,这里仅显示
fetch next from tb into @name --继续取下一条
end
close tb --关闭游标
deallocate tb --释放游标资源
(
select max(thetime) thetime,view_plog.pid from view_plog inner join view_problem
on view_problem.pstatus = 7 and view_plog.pid = view_problem.pid group by view_plog.pid ) aon vlog.thetime = a.thetime and vlog.pid = a.pidinner join tpconfigure on vlog.sourceunitid = tpconfigure.unitid
and vlog.ptypeid = tpconfigure.ptype and tpconfigure.status =1 and tpconfigure.ruletype = 1
查出的结果:
thetime expiretime currenttime pid
的结果是多条。那么如果某一条中的currenttime大于expiretime,则需要对和pid有关的别的表进行操作。那么应该怎么做呢?
DECLARE @etime VARCHAR(25)
DECLARE @ctime VARCHAR(25)
DECLARE @id int
DECLARE @unitid int
DECLARE changer CURSOR FOR select vlog.thetime,dateadd(hh,convert(int,substring(content,len('<closehour>')+1,len(content)-len('<closehour>')-len('</closehour>'))),vlog.thetime) expiretime,getdate() currenttime,vlog.pid,vlog.sourceunitidfrom view_plog vlog inner join
(
select max(thetime) thetime,view_plog.pid from view_plog inner join view_problem
on view_problem.pstatus = 7 and view_plog.pid = view_problem.pid group by view_plog.pid ) aon vlog.thetime = a.thetime and vlog.pid = a.pidinner join tpconfigure on vlog.sourceunitid = tpconfigure.unitid
and vlog.ptypeid = tpconfigure.ptype and tpconfigure.status =1 and tpconfigure.ruletype = 1OPEN changerFETCH NEXT FROM changer INTO @ttime, @etime, @ctime, @id , @unitid --取第一条记录while @@fetch_status=0 --循环,判断取数是否成功
begin --循环,逐条处理记录
if @etime<@ctime
begin
update TProblem set pstatus = 10 where pid = + @id
INSERT INTO TPlog ( pid, thetime, logtype, logcontent, psourcestatus, sourceunitid, ptargetstatus, targetunitid ) VALUES ( @id, @ctime , 5, '问题超过确认时限,自动结束' ,7 , + @unitid + , 10, + @unitid + )
end
FETCH NEXT FROM changer INTO @ttime, @etime, @ctime, @id , @unitid --继续取下一条end
close changer --关闭游标
deallocate changer --释放游标资源提示
服务器: 消息 170,级别 15,状态 1,行 30
第 30 行: ',' 附近有语法错误。就是INSERT INTO 那句,可我看了半天也没看出来。为什么呀?
DECLARE @ttime VARCHAR(25)
DECLARE @etime VARCHAR(25)
DECLARE @ctime VARCHAR(25)
DECLARE @id int
DECLARE @unitid intDECLARE changer CURSOR FOR
select vlog.thetime
,dateadd(hh,convert(int,substring(content,len('<closehour>')+1,len(content)-len('<closehour>')-len('</closehour>'))),vlog.thetime) expiretime
,getdate() currenttime
,vlog.pid,vlog.sourceunitid
from view_plog vlog inner join
(
select max(thetime) thetime,view_plog.pid from view_plog inner join view_problem
on view_problem.pstatus = 7 and view_plog.pid = view_problem.pid group by view_plog.pid
) a on vlog.thetime = a.thetime and vlog.pid = a.pid
inner join tpconfigure on vlog.sourceunitid = tpconfigure.unitid
and vlog.ptypeid = tpconfigure.ptype and tpconfigure.status =1 and tpconfigure.ruletype = 1OPEN changerFETCH NEXT FROM changer INTO @ttime, @etime, @ctime, @id , @unitid --取第一条记录while @@fetch_status=0 --循环,判断取数是否成功
begin --循环,逐条处理记录
if @etime<@ctime
begin
update TProblem set pstatus = 10 where pid = + @id
INSERT INTO TPlog ( pid, thetime, logtype, logcontent, psourcestatus, sourceunitid, ptargetstatus, targetunitid )
VALUES ( @id, @ctime , 5, '问题超过确认时限,自动结束' ,7 , @unitid , 10, @unitid )
end
FETCH NEXT FROM changer INTO @ttime, @etime, @ctime, @id , @unitid --继续取下一条end
close changer --关闭游标
deallocate changer --释放游标资源