sql server 2000存储过程例子-优快云博客

本文介绍了一个用于学生成绩统计的SQL存储过程。该过程能够根据指定班级和学期，获取学生的成绩信息，并计算平均成绩。同时，它还考虑了成绩的有效性和作弊情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

/****** Object: Stored Procedure dbo.ChengJi    Script Date: 2005-11-10 11:28:53 ******/

CREATE PROCEDURE dbo.ChengJi (@fClass_No varchar(20),@fTermNo varchar (10),@n int)
AS
create table #temp
(
    fStu_Num varchar (20),
   fStu_Name varchar (20),
   fSex       varchar(2),
   fScores   varchar (8000),
   fAverage varchar(10),
   fNote       varchar(1) default '0'
)
--从学籍表T_xjStudent 中取得指定班级的所有学生存入#temp 中，以备后面使用
declare @StuNum varchar (20),@StuName varchar(20),@Sex varchar(2),@Scores varchar(8000),@fCNames varchar(200)
declare stu_cursor cursor for select distinct fStudentNum ,fName,fSex from T_xjStudent where fClassNum =@fClass_No-- and substring(fStudentNum,1,4)='1999'
Open stu_cursor
   fetch next from stu_cursor into @StuNum,@StuName,@Sex
while @@fetch_status = 0
begin
   Insert into #temp (fStu_Num,fStu_Name,fSex,fScores) values(@StuNum,@StuName,@Sex,'')
   fetch next from stu_cursor into @StuNum,@StuName,@Sex
end
close stu_cursor
deallocate stu_cursor

--选择出一个学籍班的某一学期所有教学计划安排
declare @fCourseNum varchar (20),@fCourseName varchar(20)
declare @fScores varchar (8000)
declare Cou_cursor cursor for
   select   distinct PE.fCourse_No As 课程班号,CL.fCName_China As 课程
   from    T_jxPersonalElective PE ,T_xjStudent S ,T_jxCourseLibrary CL
   where S.fStudentNum =PE.fStudentNum and S.fClassNum = @fClass_No and PE.fTermNo =@fTermNo and CL.fCourse_Num=Substring(PE.fCourse_No,2,4)
       /*select TI.fCourse_No As 课程班号,CL.fCName_China As 课程
       from T_jxTeachImplement TI,T_jxCourseLibrary CL
       Where TI.fClass_No=@fClass_No and CL.fCourse_Num =substring(TI.fCourse_No,2,4) and TI.fTermNo =@fTermNo */ --TI.fCourse_No'11313201'
Open Cou_cursor
   fetch next from Cou_cursor into @fCourseNum,@fCourseName
   --set @fCNames = @fCNames +@fCourseName
while @@fetch_status = 0
begin

--选出每一个学籍班的所有学生一学期一门课程的成绩
   declare @SNum varchar(20),@SName varchar (20),@SS varchar(10),@fNote varchar(1),@EC varchar(10)
   declare Score_cursor cursor for
       select     S.fStudentNum As 学号,S.fName As 姓名,ISNULL(PE.fScore1,0) As 成绩 ,EC.fCheatName2 As 考试情况
       from       T_xjStudent S,T_jxPersonalElective PE ,T_jxExamCheat EC
       where    S.fStudentNum = PE.fStudentNum and S.fClassNum =@fClass_No and PE.fCourse_No = @fCourseNum and PE.fTermNo = @fTermNo
           and EC.fCheat_No = PE.fCheat -- @fCourseNum'11313201'
       Order By S.fStudentNum,PE.fScore1
   Open Score_cursor
       fetch next from Score_cursor into @SNum,@SName,@SS,@EC
   while @@fetch_status = 0
   begin
       set @Scores = (select fScores from #temp where fStu_Num = @SNum and fStu_Name = @SName)
       set @fNote = (select fNote from #temp where fStu_Num = @SNum and fStu_Name = @SName)
       if(cast(@SS As int)>=60 and @fNote='0' and @SS!='0') -- @SS!='' and @SS!='NULL' and
       Update #temp set fScores =( @Scores+ @SS+'         '),fNote = '0' where fStu_Num = @SNum and fStu_Name = @SName
       else
       if(@SS ='' or @SS = 'NULL' or @SS='0')
       Update #temp set fScores =( @Scores+ @EC +'         ') ,fNote = '1' where fStu_Num = @SNum and fStu_Name = @SName--
       else
       Update #temp set fScores =( @Scores+ @SS +'         ') ,fNote = '1' where fStu_Num = @SNum and fStu_Name = @SName
       fetch next from Score_cursor into @SNum,@SName,@SS,@EC
   end
   close Score_cursor
   deallocate Score_cursor

   fetch next from Cou_cursor into @fCourseNum,@fCourseName
end
close Cou_cursor
deallocate Cou_cursor

--计算平均成绩
declare @fStudentNum varchar(20),@fName varchar(20),@Ave varchar (10)
declare Ave_cursor cursor for
   select S.fStudentNum As 学号,S.fName As 姓名,avg(PE.fScore1) As 平均成绩
   from       T_xjStudent S,T_jxPersonalElective PE
   where    S.fStudentNum = PE.fStudentNum and S.fClassNum =@fClass_No and PE.fTermNo = @fTermNo    -- and PE.fScore2 ='' and PE.fScore3 =''                                                                         --and PE.fCourse_No = '11313201'
   group by S.fStudentNum,S.fName

Open   Ave_cursor
   fetch next from Ave_cursor into @fStudentNum,@fName ,@Ave
while @@fetch_status = 0
begin
   Update #temp set fAverage = @Ave where fStu_Num = @fStudentNum and fStu_Name = @fName
   fetch next from Ave_cursor into @fStudentNum,@fName ,@Ave
end
close Ave_cursor
deallocate Ave_cursor
if(@n !='' and @n !=0)
begin
set ROWCOUNT @n
select   fStu_Num As 学号,fStu_Name As 姓名,fSex As 性别,fScores As 成绩 ,substring(fAverage,1,5) As 平均成绩 from #temp Where fNote ='0'
Order by substring(fAverage,1,5) DESC
set ROWCOUNT 0
end
else
select   fStu_Num As 学号,fStu_Name As 姓名,fScores As 成绩 ,substring(fAverage,1,5) As 平均成绩 from #temp

GO