sql server 2000存储过程例子

十二12

　　

/****** 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