SQL中游标的使用

来源：互联网发布：北师大网络教育文凭编辑：程序博客网时间：2024/05/16 23:34

declare @studentnum varCHAR(9),@course varchar(10),@achievement tinyint,@classorder tinyint
declare @pstudentnum varchar(9),@allcourse varchar(60)

declare allachievement cursor for
select studentnum,course,achievement,classorder
from tempachievement
where indate=convert(char(10),getdate(),120) and achievement>0
order by studentnum,course

OPEN allachievement

FETCH NEXT FROM allachievement
INTO @studentnum, @course, @achievement, @classorder
set @pstudentnum=@studentnum
set @allcourse=''
WHILE @@FETCH_STATUS = 0
BEGIN
if (@pstudentnum <> @studentnum)
begin
  if (len(@allcourse)>0)
  begin
   insert achievementaddfields(studentnum,addfields)
   values(@pstudentnum,@allcourse)
   --set addfields=@allcourse
   --where studentnum=@pstudentnum
  end
  set @pstudentnum=@studentnum
   --set @allcourse=@course+@achievement+@classorder
  --set @allcourse=''
  set @allcourse=rtrim(@course) + ',' + ltrim(str(@achievement)) + ',' + ltrim(str(@classorder)) + ';'
  FETCH NEXT FROM allachievement
  INTO @studentnum, @course, @achievement, @classorder
end
else
  begin
  set @allcourse=@allcourse + rtrim(@course) + ',' + ltrim(str(@achievement)) + ',' + ltrim(str(@classorder)) + ';'

  FETCH NEXT FROM allachievement
  INTO @studentnum, @course, @achievement, @classorder
  end

end
  if (len(@allcourse)>0)
  begin
   insert achievementaddfields(studentnum,addfields)
   values(@pstudentnum,@allcourse)
   --set addfields=@allcourse
   --where studentnum=@pstudentnum
  end
CLOSE allachievement
DEALLOCATE allachievement