数据库第七周【第五章作业存储过程】

 本章的作业题目来自第八章。。。

 T_SQL建立存储过程的标准形式。

Create procedure <procedure_Name> 
As 
Begin 
<SQL Statement> 
End 
Go

对学生-课程数据库编写存储过程， 完成下述功能：

首先建立实验需要使用的离散成绩表，由于之前的Course表中没有离散数学这门课，直接查询会出现错误，所以我们先进行插入，将该门课存入。

create table lisan(
Score char(20),-- 记录离散成绩的分数段
Count int --记录人数
);
insert into Course values ('8','离散数学','2','3');

（1）统计离散数学的成绩分布情况，即按照各分数段统计人数。

----判断是否存在存储过程，如果存在则删除
if(exists (select * from sys.objects where name='Stats'))
	drop procedure Stats
go
create procedure Stats
--定义存储过程Stats
as 
declare  --定义变量
@0to20 int,
@20to40 int,
@40to60 int,
@60to80 int,
@80to100 int,
@cno char(8);

begin

select @cno=Cno
from Course
where Cname='离散数学';

select @0to20 =count(*)
from SC
where Grade<20 and Cno=@cno;

select @20to40 =count(*)
from SC
where Grade<40 and Grade>=20 and Cno=@cno;

select @40to60 =count(*)
from SC
where Grade<60 and Grade>=40 and Cno=@cno;

select @60to80 =count(*)
from SC
where Grade<80 and Grade>=60 and Cno=@cno;

select @80to100 =count(*)
from SC
where Grade>=80 and Cno=@cno;

end;

update lisan set Count=@0to20 where Score='[0,20)';
update lisan set Count=@20to40 where Score='[20,40)';
update lisan set Count=@40to60 where Score='[40,60)';
update lisan set Count=@60to80 where Score='[60,80)';
update lisan set Count=@80to100 where Score='[80,100)';

sqlserver中
EXEC命令有两种用法，一种是执行一个存储过程，另一种是执行一个动态的批处理。

 （2）统计任意一门课的平均成绩。

先建表

create table AvgStudent(
Cno char(4),
Cname char(10),
Score float
);

--存储过程
if(exists(select * from sys.objects where name='AvgScore'))
drop procedure AvgScore
go
create procedure AvgScore
as
declare
@s1 float,
@s2 float,
@s3 float,
@s4 float;

begin

select @s1=avg(Grade)
from SC
where Cno='1';

select @s2=avg(Grade)
from SC
where Cno='2';

select @s3=avg(Grade)
from SC
where Cno='3';

select @s4=avg(Grade)
from SC
where Cno='4';

end;

update AvgScore set Score=@s1 where Cno='1';
update AvgScore set Score=@s2 where Cno='2';
update AvgScore set Score=@s3 where Cno='3';
update AvgScore set Score=@s4 where Cno='4';

（3）将学生选课成绩从百分制改为等级制 (即A、 B、C、D、E)。

下面这个答案可能存在一点问题，，，目前没有找到解决的方法。

if (exists (select * from sys.objects where name='rank'))
	drop procedure rank
go
create procedure rank(@grade smallint,@Cno char(4),@Sno char(9))
as
declare
@A char(5),
@B char(5),
@C char(5),
@D char(5),
@E char(5),
@Cpo char(4),
@Spo char(10);
select @Cpo=Cno from SC where @Cno=Cno;
if @Cpo is null
begin
	print'没有该课程'
	rollback;
	return ;
end;

select @Spo=Sno from SC where @Sno=Sno;
if @Cpo is null
begin
	print'没有该学生'
	rollback;
	return;
end;

update SC set rank='A' where @grade>=90;
update SC set rank='B' where @grade>=80 and @grade<90;
update SC set rank='C' where @grade>=70 and @grade<80;
update SC set rank='D' where @grade>=60 and @grade<70;
update SC set rank='E' where @grade>60;

create table Department(
	Dno char(10) primary key,
	Dname char(10) unique,
	Mname char(10) not null,
	Phone char(11) not null
	);

create table Employee(
	Eno char(15) primary key,
	Ename char(10) not null,
	age int check(age>0 and age<=60),
	duty char(10) not null,
	Salary int,
	Dno char(10),
	foreign key (Dno) references Department(Dno)
	);

心得：

大家的名字都叫SQL，为什么差距那么大呢？

这个作业写的好难啊，找了很久，几乎没有完全可以运行出来的一篇作业。

网上更加流行的还是标准SQL，就连菜鸟教程都默认标准SQL，连T_SQL的影子都没有见到。。。

这次花枯了，不种了