常用语句（七）procedure

/*1,创建存储过程usp_exec1完成功能为：如果考英语学员的平均分数不到80分则给每个考英语的学生增加2分，直到平均分达到80分为止，但不能给超过98分的学员增加分数*//*create table test1Score(scno int identity(1,1) not null primary key,sno int,cno int,score int)gotruncate table test1Scoregoinsert into test1Score(sno,cno,score) values('20130202','1','91');insert into test1Score(sno,cno,score) values('20130202','2','59');insert into test1Score(sno,cno,score) values('20130204','1','62');insert into test1Score(sno,cno,score) values('20130207','1','23');insert into test1Score(sno,cno,score) values('20130207','2','64');select * from test1Scoregoselect avg(score) from test1Score where cno=(select cno from course where cname like '%英语%')go*/if exists(select * from sysobjects where name='usp_exec1') drop procedure usp_exec1gocreate procedure usp_exec1asdeclare @ssvg intselect @ssvg=AVG(score) from test1Score where cno=(select cno from course where cname like '%英语%')while(@ssvg<80)beginupdate test1Score set score=score+2 where cno=(select cno from course where cname like '%英语%') and score<=100-2select @ssvg=AVG(score) from test1Score where cno=(select cno from course where cname like '%英语%')endexec usp_exec1/*2,创建存储过程usp_exec2完成功能为：给存储过程一个今年英语考试的分数线，并在存储过程中打印出今年英语考试的最高分和最低分，并且打印出没有达到分数线的英语考生姓名和性别和分数*/if exists(select * from sysobjects where name='usp_exec2') drop procedure usp_exec2gocreate procedure usp_exec2@ysfsx int,@zuigaofen int output,@zuidifen int outputasselect @zuigaofen=MAX(score),@zuidifen=MIN(score) from test1score where cno=(select cno from course where cname like '%英语%')select sname 姓名,sex 性别,score 分数 from student st inner join test1Score sc on st.sno=sc.sno where score<@ysfsxdeclare @minScore int,@maxScore intexec usp_exec2 60,@minScore output,@maxScore outputprint '英语最高分：'+cast(@minScore as varchar(3)) + '最低分：'+cast(@maxScore as varchar(3))/*3,创建存储过程usp_exec3完成功能为：给存储过程输入某个学生的学号，存储过程接收后打印出该学员所参加考试的课程号以及分数，并且从存储过程输出来该学员的所有考试科目中最高分和最低分，在执行存储过程时用自定义的@mymax,@mymin来接收输出来的参数，并判断如果@mymin小于60分，打印出‘bad score，fiting’*/if exists(select * from sysobjects where name='usp_exec3') drop procedure usp_exec3gocreate procedure usp_exec3@xuehao int,@zuigaofen int output,@zuidifen int outputasselect cno '课程号',score '分数' from test1Score where sno=@xuehaoselect @zuigaofen=MAX(score),@zuidifen=MIN(score) from test1Score where sno=@xuehaodeclare @mymax int,@mymin intexec usp_exec3 20130207,@mymax output,@mymin outputprint '最高分：'+cast(@mymax as varchar(3))+',最低分：'+cast(@mymin as varchar(3))if @mymin<60print 'bad score，fiting'