使用S-C数据库中的S表、C表、SC表创建一个存储过程—sp_CURSOR1。该存储过程的作用是:显示所有的课程信息,如果成绩>=90显示成绩本身;成绩>=80显示良;成绩>=70显示中;成绩>=60显示及格;成绩>=0显示不及格;
if exists(select name from sysobjects where name='sp_CURSOR1' and type='p')
drop procedure sp_CURSOR1
go
create proc sp_CURSOR1
as
declare @g float
declare sp_cur cursor
for select s.sno,sname,cname from s,sc,c
where s.sno=sc.sno and sc.cno=c.cno
open sp_cur
fetch next from sp_cur into @g
while(@@fetch_status=0)
begin
if(@g>=90)
begin
select grade from sc where grade=@g
fetch next from sp_cur into @g
end
else if(@g>=80 and @g<90)
begin
print'良'
fetch next from sp_cur into @g
end
else if(@g>=70 and @g<80)
begin
print'中'
fetch next from sp_cur into @g
end
else if(@g>=60 and @g<70)
begin
print'及格'
fetch next from sp_cur into @g
end
else
begin
print'不及格'
fetch next from sp_cur into @g
end
end
close sp_cur
deallocate sp_curexec sp_CURSOR1 grade
if exists(select name from sysobjects where name='sp_CURSOR1' and type='p')
drop procedure sp_CURSOR1
go
create proc sp_CURSOR1
as
declare @g float
declare sp_cur cursor
for select s.sno,sname,cname from s,sc,c
where s.sno=sc.sno and sc.cno=c.cno
open sp_cur
fetch next from sp_cur into @g
while(@@fetch_status=0)
begin
if(@g>=90)
begin
select grade from sc where grade=@g
fetch next from sp_cur into @g
end
else if(@g>=80 and @g<90)
begin
print'良'
fetch next from sp_cur into @g
end
else if(@g>=70 and @g<80)
begin
print'中'
fetch next from sp_cur into @g
end
else if(@g>=60 and @g<70)
begin
print'及格'
fetch next from sp_cur into @g
end
else
begin
print'不及格'
fetch next from sp_cur into @g
end
end
close sp_cur
deallocate sp_curexec sp_CURSOR1 grade
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货