sql

编写存储过程访问数据库

对学生-课程数据库，编写存储过程，完成下面功能：
1、统计离散数学的成绩分布情况，即按照分数段统计人数；
create procedure count_sno
  @g1 smallint,@g2 smallint      --输入分数段
as
  declare   @cunt int
  select @cunt=count(*)  from sc
  where grade between @g1 and @g2 
        and  cno=(select cno from course  where cname='离散数学')
  select  @g1 成绩1,'---',@g2 成绩2, @cunt 人数
--执行存储过程     
exec count_sno 80,100




--带有输出参数的存储过程：
create procedure count_sno
  @g1 smallint,@g2 smallint,      --输入分数段
  @cunt int output                --输出参数
as
  select @cunt=count(*) from sc
  where grade between @g1 and @g2 
        and  cno=(select cno from course  where cname='离散数学')
  select  @g1 成绩1,'---',@g2 成绩2, @cunt 人数
--执行存储过程
declare @s int   
exec count_sno 80,100,@s output
select @s as 选修人数            -- print '选修人数='+cast(@s as char)




2、统计任意一门课的平均成绩；


create procedure avg_grade
  @cno char(2)          --输入课程号
as
  select cno 课号,avg(grade) 平均成绩 from sc
  where cno=@cno
  group by cno


--执行存储过程    
exec avg_grade '3'


或
create procedure avg_grade
  @cname char(20)          --输入课程名：
as
  select cno 课号,avg(grade) 平均成绩 from sc
  where cno=(select cno from course where cname=@cname)             
  group by cno


--执行存储过程    
exec avg_grade '数据库'


3、将学生选课成绩从百分制改为等级制（即A、B、C、D、E）。
要求一：在SC表中添加一列dj，通过存储过程p_dj01将成绩从百分制转化为等级制填充dj列，输出SC表信息。


--创建存储过程p_dj01
create proc p_dj01
as
 begin
  if not exists(select * from syscolumns where id=(select id from sysobjects  where name='sc') and name='dj')
      alter table sc add dj char(2)
  update sc 
  set dj=case
           when grade>=90  THEN  'A'
           when grade>=80 and grade<90 THEN  'B'
           when grade>=70 and grade<80 THEN  'C'
           when grade>=60 and grade<70 THEN  'D'
        else  'E'
    END
   select * from sc
end


go
--执行存储过程    
 exec p_dj01


方法二：通过存储过程p_dj02按指定学号将学生选课的百分制成绩转化为
相应等级输出。


--若存储过程p_dj02存在，则删除。


if exists(select * from sysobjects where name='p_dj02')
     drop procedure p_dj02


--创建存储过程p_dj02


create proc p_dj02
   @stuno char(9)       --输入学号
as
  declare @dj char(1) , @g  smallint 
  /*定义游标*/
  DECLARE cur CURSOR FOR 
     SELECT grade  FROM sc WHERE sno=@stuno
 /*打开游标*/
  OPEN cur
  fetch next from cur into @g
  while @@FETCH_STATUS=0
    begin
      set @dj=case
           when @g>=90  THEN  'A'
           when @g>=80 and @g<90 THEN  'B'
           when @g>=70 and @g<80 THEN  'C'
           when @g>=60 and @g<70 THEN  'D'
           else  'E'
       end
      print @stuno+'   成绩：'+cast(@g as char)+'等级：'+@dj
     fetch next from cur into @g
   end
  close  cur
go
  
--执行存储过程:   
p_dj02 '200215121'


p_dj02 '200215123'