常用语句（七）procedure

最新推荐文章于 2023-02-08 13:53:16 发布

YuiJUNJUN

最新推荐文章于 2023-02-08 13:53:16 发布

阅读量681

点赞数

分类专栏： T-SQL

本文链接：https://blog.csdn.net/u010221685/article/details/8800139

版权

T-SQL 专栏收录该内容

7 篇文章 1 订阅

订阅专栏

/*
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)
go
truncate table test1Score
go
insert 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 test1Score
go
select 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_exec1
go
create procedure usp_exec1
as
	declare @ssvg int
	select @ssvg=AVG(score) from test1Score where cno=(select cno from course where cname like '%英语%')
	while(@ssvg<80)
	begin
		update test1Score set score=score+2 where cno=(select cno from course where cname like '%英语%') and score<=100-2
		select @ssvg=AVG(score) from test1Score where cno=(select cno from course where cname like '%英语%')
	end
exec usp_exec1
/*
2,创建存储过程usp_exec2完成功能为：
给存储过程一个今年英语考试的分数线，
并在存储过程中打印出今年英语考试的最高分和最低分，
并且打印出没有达到分数线的英语考生姓名和性别和分数
*/
if exists(select * from sysobjects where name='usp_exec2') drop procedure usp_exec2
go
create procedure usp_exec2
	@ysfsx int,
	@zuigaofen int output,
	@zuidifen int output
as
	select @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<@ysfsx
declare @minScore int,@maxScore int
exec usp_exec2 60,@minScore output,@maxScore output
print '英语最高分：'+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_exec3
go
create procedure usp_exec3
	@xuehao int,
	@zuigaofen int output,
	@zuidifen int output
as
	select cno '课程号',score '分数' from test1Score where sno=@xuehao
	select @zuigaofen=MAX(score),@zuidifen=MIN(score) from test1Score where sno=@xuehao
declare @mymax int,@mymin int
exec usp_exec3 20130207,@mymax output,@mymin output
print '最高分：'+cast(@mymax as varchar(3))+',最低分：'+cast(@mymin as varchar(3))
if @mymin<60
	print 'bad score，fiting'