《数据库系统》上机题实验四TSQL查询存储过程触发器完整性.docx
《《数据库系统》上机题实验四TSQL查询存储过程触发器完整性.docx》由会员分享，可在线阅读，更多相关《《数据库系统》上机题实验四TSQL查询存储过程触发器完整性.docx（11页珍藏版）》请在冰豆网上搜索。
《数据库系统》上机题实验四TSQL查询存储过程触发器完整性
实验四：
T-SQL查询、存储过程、触发器、完整性
姓名：
张浩学号：
专业：
信息管理与信息系统
班级：
1
课程名称：
数据库系统概论课程代码：
实验时间：
2010年12月24日，2010年12月18日
实验地点：
12号楼406室
1.实验特点:
类型：
验证类别：
专业基础计划学时：
4学时每组人数：
1人
2.实验目的与要求：
实验目的：
1）熟练掌握局部变量、游标的定义和使用
2）熟练掌握存储过程、触发器的创建和使用
3）掌握数据库完整性约束的定义和使用
基本要求：
1）掌握上述过程定义的一般格式
3.主要仪器设备:
计算机+SQLServer2000
第一部分：
T-SQL程序设计
（1）.如果3号课程的平均成绩在80分以上，则输出“3号课程成绩良好”，否则输出“3号成绩一般”if（selectavg（grade）fromscwherecno='3'）>80
begin
print'3号成绩良好'
end
else
begin
print'3号成绩一般'
end
（2）计算并输出95003号学生的平均成绩，若无该生信息，则显示“该生未选课”,提示信息.declare@messagevarchar（200）
ifexists（selectsnofromscwheresc.sno='95003'）
beginselectavg（grade）fromscwheresc.sno='95003'end
elsebeginset@message='该生未选课'
print@messageend
（3）.如果有成绩在90分以上的学生,则显示他的学号,课程和成绩,否则显示“没有学生的课程成绩在90分以上”提示信息declare@msgvarchar（200）
ifexists（selectgradefromscwheresc.grade>=90）
selectsno,cno,gradefromscwheresc.grade>=90
else
set@msg='没有学生的成绩在90以上'
print@msg
（4）.利用游标逐行显示student表中的记录。
declare@cursorvarcursor
set@cursorvar=cursorscrolldynamicfor
select*fromstudent
open@cursorvar
fetchnextfrom@cursorvar
while@@fetch_status=0
beginfetchnextfrom@cursorvarendclose@cursorvardeallocate@cursorvar
（5）.用自定义函数计算全体男生的平均年龄
CREATEFUNCTIONaverage（@cnumchar
（2））RETURNSintas
begindeclare@averint
select@aver=（selectavg（sage）fromstudentwheressex=@cnumgroupbyssex）
return@aver
end
go
declare@aver1int,@cnum1char
（2）
set@cnum1='男'
select@aver1=dbo.average（@cnum1）
select@aver1as'全体男生的平均年龄'
（6）.显示course表中课程名的前2个字符。
selectleft（cname,2）
fromcourse
orderbycno
（7）.在一列中显示student中各元组的学号中的年级，列名显示为“年级”；另一列中显示学号中的学生序列号，列名显示为“序号”。
SELECTSUBSTRING（sno,1,2）'年级',SUBSTRING（sno,3,LEN（sno）-1）'序号'
FROMstudent
ORDERBYsno
（8）.在选课表中显示学号、课程号，并根据成绩：
0-59显示“不合格”；60-79显示“合格”；80-89显示“良好”；90-100显示“优秀。
”selectsno,cno,grade=
case
whengrade>=0andgrade<60then'不合格'
whengrade>=60andgrade<80then'合格'
whengrade>=80andgrade<90then'良好'
whengrade>=90andgrade<=100then'优秀'
end
fromsc
第二部分：
存储过程
（1）创建一个为worker表添加职工记录的存储过程Addworker
CREATEprocAddworker@职工号char（4）,
@名姓char（8）,
@性别char
（2）,
@出生日期datetime,
@党员否char
（2）,
@参加工作datetime,
@部门号char（4）
asinsertintoworker
values（@职工号,@名姓,@性别,@出生日期,@党员否,@参加工作,@部门号）;
（2）创建一个存储过程Delworker删除worker表中指定职工号的记录
createprocDelworker@zhigonghaochar（4）asdeletefromworkerwhere职工号=@zhigonghao;
go
（3）显示存储过程Delworker的定义信息。
（4）删除存储过程Addworker和Delworker。
不要截图
dropprocedureAddworker
dropprocedureDelworker
（5）创建并执行以下存储过程：
a.从数据库表中查询，返回学生学号、姓名、课程名、成绩
createprocs_back
as
selectstudent.sno,sname,cname,grade
fromstudent,sc,course
wherestudent.sno=sc.snoando=o
go
execs_back
b.从数据库表中查询指定学号的学生学号，姓名、班级，该存储过程接受与传递参数，精确匹配的值
createprocr_back
@achar（10）
asselectsno,snamefromstudentwheresno=@a
go
execr_back'95003'
第三部分：
触发器
（1）在表depart上创建一个触发器depart_update,当更改部门号时同步更改worker表中对应的部门号createtriggerdepart_update
ondepart
forupdate
asupdateworker
set部门号=（select部门号frominserted）
whereworker.部门号=（select部门号fromdeleted）
。
（2）在表worker上创建一个触发器worker_delete,当删除职工记录时同步删除salary表中对应的职工记录。
createtriggerworker_delete
onworkerfordelete
asdeletefromsalary
where职工号=（select职工号fromdeleted）
（3）删除触发器depart_update
droptriggerdepart_update
（4）删除触发器worker_delete
droptriggerworker_delete
（5）在数据库中创建一个触发器，向选课表添加一条纪录时，检查该纪录的学号在学生表中是否存在，检查该纪录的课程号在课程表中是否存在，若其中有一项为否，则拒绝添加操作，并显示“违反数据一致性”提示信息。
例如：
向选课表中添加一条记录（'95007','2',99），说明95009不在student表中
createtriggersc_insertonscforinsertas
declare@xuehaochar（10）,@kechenghaochar（10）,@chengjismallint,@a,@b
select@xuehao=sno,@kechenghao=cnofrominserted
set@a=selectsnofromstudentwheresno=@xuehao
set@b=selectcnofromcoursewherecno=@kechenghao
ifexists（@aand@b）
beign
insertintoscvalues（@xuehao,@kechenghao,@chengji）
end
rollbacktransactionprint'违反数据一致性'
insertintoscvalues（'95007','2',99）
第四部分：
数据库完整性
1、实施worker表的“性别”字段默认值为“男”的约束
altertableworker
addconstraintcon1default'男'for性别
go
例如：
insertworker（职工号）values（111）
select*fromworker
2实施salary表的“工资”字段值在0~9999的约束
createtablesalary
（职工号char（4）,
姓名char（8）,
日期datetime,
工资decimalnotnullcheck（工资>=0and工资<=9999）,
primarykey（职工号,日期））
3实施depart表的“部门号”字段值唯一的非聚集索引的约束
createtabledepart
（部门号char（4）unique,
部门名char（10））
Go
4、为worker表建立外键“部门号”，参考表depart的“部门号”列。
Createtableworker
（职工号char（4）primarykey,
姓名char（8）,
性别char
（2）,
出生日期datetime,
党员否char
（2）,
参加工作datetime,
部门号char（4）,
Foreignkey（部门号）referencesdepart（部门号））
5、建立一个规则sex：
@性别=’男’OR@性别=’女’,将其绑定到worker表的“性别”列上。
createruleworker_rule
as@性别like'男'or@性别like'女'
go
execsp_bindrule'worker_rule','worker.性别'
Go
6、删除1小题所建立的约束
Altertableworkerdropconstraintcon1
7、删除2小题所建立的约束。
Altertablesalarydrop工资check
8、删除3小题所建立的约束
Altertabledepartdropunique
9、删除4小题所建立的约束
Altertableworkerdropforeignkey
10.解除5小题所建立的绑定并删除规则sex
ifexists（selectnamefromsysobjects
wherename='worker_rule'andtype='R'）
begin
execsp_unbindrule'worker.性别'
dropruleworker_rule
升级会员 