SQL中游标的使用

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