利用存储过程，游标，临时表统计学习成绩

  1 use master
  2 if exists(select * from sysdatabases where name='TT')
  3 drop database TT         ---判读数据库是否存在
  4 go
  5 create database TT
  6 go
  7 use TT
  8 
  9 if exists (select * from sysobjects where name='stuinfor')
 10 drop table stuinfor     ----判读表是否存在
 11 
 12 create table stuinfor
 13 (  
 14    stuid int primary key identity(1,1),
 15    stuname varchar(20)    
 16 )
 17 
 18 if exists (select * from sysobjects where name='student')
 19 drop table student     ----判读表是否存在
 20 create table student
 21 (
 22    id int primary key identity(1,1),
 23    stuid int,   
 24    subject int,
 25    chengji decimal(3,0)
 26 )
 27 go
 28 
 29  
 30 
 31 
 32 if exists (select * from sysobjects where name='proresult')
 33 drop proc proresult      ----判读存储过程是否存在
 34 go
 35 create proc proresult
 36 as
 37 begin
 38   declare @sql nvarchar(200)    
 39   if(object_id('temp_table') is not null)   ---判读是否已经存在临时表名temp_table
 40  begin
 41  drop table temp_table
 42   end
 43   create table temp_table(stuid int primary key, stuname nvarchar(20),chinese decimal(3,0),math decimal(3,0),english decimal(3,0),avgage decimal(3,0), appraise varchar(20)) ---创建临时表
 44   declare cursor_table cursor for          
 45  select stuid,stuname from stuinfor     --定义游标
 46   open cursor_table                    ---打开游标
 47   declare @stuid int,@stuname nvarchar(20)
 48   fetch next from cursor_table into @stuid,@stuname     ---游标移动
 49   while(@@fetch_status=0)           ---判读是否已经移动到最尾端
 50  begin
 51   declare @bid int, @chinese decimal(3,0),  @math decimal(3,0), @english decimal(3,0),@description nvarchar(20),@avgage decimal(3,0)
 52   set @bid=0
 53   while(@bid<3)
 54   begin    
 55    set @bid=@bid+1
 56    print @bid
 57    if(@bid=1)
 58    begin
 59      select @chinese=chengji from student where stuid=@stuid and subject=@bid    
 60    end
 61    else if(@bid=2)
 62    begin
 63     select @math=chengji from student where stuid=@stuid and subject=@bid
 64    end
 65 
 66    else if(@bid=3)
 67    begin
 68     select @english=chengji from student where stuid=@stuid and subject=@bid
 69    end  
 70   end
 71    declare @total decimal(3,0)
 72    set @total=@chinese+@math+@english
 73                  set @avgage=@total/3
 74    -- print @total
 75    if @total/3=0
 76    begin
 77    set @description='未考试'
 78    end
 79    else if @total/3<60
 80    begin
 81    set @description='不及格'
 82    end
 83    else if (@total/3>=60 and @total/3<80)
 84    begin
 85    set @description='良好'
 86    end 
 87    else if (@total/3>=80 and @total/3<90)
 88    begin
 89    set @description='优秀'
 90    end 
 91     else if @total/3>=90
 92    begin
 93    set @description='很好'
 94    end 
 95   print  @description
 96   insert into temp_table(stuid,stuname,chinese,math,english,avgage,appraise) values(@stuid,@stuname,@chinese,@math,@english,@avgage,@description) ---将记录插入到临时表中
 97   fetch next from  cursor_table into @stuid,@stuname        --向下移动
 98  end
 99   close cursor_table         --关闭游标
100   deallocate cursor_table    ---销毁游标
101 
102 
103   set @sql=N'select stuid as 编号, stuname as 姓名,  chinese as 语文, math as 数学, english as 英语 ,avgage as 平均成绩 , appraise as 评价 from temp_table'     
104   exec sp_executesql @sql  ---执行查询临时表
105   drop table temp_table        ---删除临时表
106 end
107 go
108  
109 exec proresult   ---执行存储过程
110 
111