实验五存储过程和触发器地定义和使用.docx
- 文档编号:28114927
- 上传时间:2023-07-08
- 格式:DOCX
- 页数:19
- 大小:584.84KB
实验五存储过程和触发器地定义和使用.docx
《实验五存储过程和触发器地定义和使用.docx》由会员分享,可在线阅读,更多相关《实验五存储过程和触发器地定义和使用.docx(19页珍藏版)》请在冰豆网上搜索。
实验五存储过程和触发器地定义和使用
实验五存储过程和触发器的定义和使用
一、实验目的
1、掌握局部变量、全局变量、流程控制语句的使用方法
2、了解存储过程的类型和作用,并掌握使用对象资源管理器和Transact-SQL语句创建
存储过程的方法及使用方法。
3、理解触发器的特点和作用,并掌握使用Transact-SQL语言创建触发器的方法
二、实验容
1.在学生成绩库中中有如下各表:
学生表(Student)
学号
性别
出生日期
专业
所在系
联系
020101
颖
0
1980-7-20
计算机应用
计算机
88297147
020102
方露露
0
1981-1-15
信息管理
计算机
88297147
020103
俞奇军
1
1980-2-20
信息管理
计算机
88297151
020104
国强
1
1980-11-7
信息管理
计算机
88297151
020105
薛冰
1
1980-7-29
水利工程
水利系
88297152
020201
秦盈飞
0
1981-3-10
电子商务
经济系
88297161
020202
董含静
0
1980-9-25
电子商务
经济系
88297062
020203
伟
1
1980-8-7
电子商务
经济系
88297171
020204
新江
1
1980-7-20
房建
水利系
88297171
createdatabase学生成绩数据库
createtableStudent
(
学号Char(6)notnull,
Char(8)notnull,
性别Bitnotnull,
出生日期smalldatetime,
专业Char(10),
所在系Char(10),
联系Char(11)null
)
课程表(Course)
课程号
课程名
教师
开课学期
学时
学分
101
计算机原理
红
2
45
3
102
计算方法
王颐
3
45
3
103
操作系统
徐格
2
60
4
104
数据库原理及应用
应对刚
3
75
5
105
网络基础
吴江江
4
45
3
106
高等数学
中文
1
90
6
107
英语
刚
1
90
6
108
VB程序设计
红韦
3
70
5
createtableCourse
(
课程号Char(3)notnull,
课程名Char(20)notnull,
教师Char(10),
开课学期Tinyint,
学时Tinyint,
学分Tinyintnotnull,
)
学生选课成绩表(SC)
学号
课程号
成绩
020101
101
85
020101
102
87
020101
103
88
020102
101
58
020102
102
63
020104
107
76
020202
103
55
020202
107
80
020203
103
57
020204
103
71
createtableSC
(
学号Char(6)notnull,
课程号Char(3)notnull,
成绩Smallint,
)
对三个表格分别导入,截图如下:
2、T-SQL语句中流程控制语句的使用
(1)全局变量的使用。
显示到当前日期和时间为止试图登录SQLServer的次数。
selectgetdate()as'当前的日期和时间',
connectionsas'试图登陆的次数'
(2)IF语句的使用。
①在Student表中,若存在学号“020205”的学生,则显示该学生的信息,否则插入该学生的记录(020205,萍,0,1983-7-20,电子商务,经济系,88297171)
Ifexists(select*fromStudentwhere学号='020205')
select*fromStudentwhere学号='020205'
Else
insertintoStudentvalues('020205','萍','0','1983-7-20','电子商务','经济系','88297171')
②查询颖有没有选课,若选了课,则统计其平均成绩,若没有选课,则输出“颖没有选课”
Ifexists(selectfromStudent,SCwhere='颖'andStudent.学号=SC.学号)
selectavg(成绩)fromSC,StudentwhereStudent.='颖'andStudent.学号=SC.学号
Else
print'颖没有选课!
'
(3)循环语句的使用。
①用WHILE语句编程计算1-100之间所有能被3整除的数的个数及总和。
DECLARESSMALLINT,ISMALLINT,NUMSSMALLINT
SETS=0
SETI=1
SETNUMS=0
WHILE(I<=100)
BEGIN
IF(I%3=0)
BEGIN
SETS=S+I
SETNUMS=NUMS+1
END
SETI=I+1
END
PRINTS
PRINTNUMS
②利用GOTO语句求出从1加到5的总和。
DECLARESSMALLINT,ISMALLINT
SETI=1
SETS=0
BEG:
IF(I<=5)
BEGIN
SETS=S+I
SETI=I+1
GOTOBEG
END
PRINTS
(4)WAITFOR语句的使用。
①等待5秒后执行查询Student学生信息
waitfordelay'00:
00:
05'
select*
fromStudent
②等到晚上10:
20执行存储过程update_all_stats。
BEGIN
WAITFORTIME'10:
20'
EXECUTEupdate_all_stats
END
(5)CASE语句的使用。
①查询Student的SNO,SEX,如果SEX为“1”则输出“男”,如果为“0”输出“女”。
SELECT学号,
性别=
CASE性别
WHEN'1'THEN'男'
WHEN'0'THEN'女'
END
FROMStudent
②从SC表中查询所有同学选课成绩情况,凡成绩为空者输出“未考”、小于60分输出“不
及格”、60分至70分输出“及格”、70分至90分输出“良好”、大于或等于90分时输出“优秀”。
SELECT学号,课程号,
成绩=
CASE
WHEN成绩ISNULLTHEN'未考'
WHEN成绩<60THEN'不及格'
WHEN成绩>=60AND成绩<70THEN'及格'
WHEN成绩>=70AND成绩<90THEN'良好'
WHEN成绩>=90THEN'优秀'
END
FROMSC
3、存储过程的的使用。
在上面学生成绩库中完成如下操作:
(1)创建如下不带参数的简单存储过程:
查询成绩在60至80分之间的学生的学号和课程号。
createprocedureSCList
as
select学号,课程号
fromSC
where成绩>60and成绩<80
(2)分别创建如下的带输入参数的存储过程:
①根据用户输入的学号,删除该学生选课成绩记录;
createprocedurestudentqk1
(学号char(10))
as
delete成绩
fromSC
where学号=学号
②向SC表中插入数据。
createproceduresccharu
(学号char(10),课程号char(10),成绩int)
as
insertintoSCvalues(学号,课程号,成绩)
(3)创建如下的带输出参数的存储过程,并写出一个执行该过程的例子:
根据用户输入的学号,课程号,输出其成绩。
createproceduregrade
(学号char(10),课程号char(10)output,成绩intoutput)
as
select成绩=成绩
fromSC
where学号=学号and课程号=课程号
declare成绩int
executegrade'020101','101',成绩output
select'成绩'=成绩
(4)创建存储过程proc_t1,要现如下功能:
输入专业名称,产生该专业学生的选课情况列表,其中包括专业、学号、、课程号、课程名、成绩、学分等。
并调用此存储过程,显示“信息管理”专业学生的选课情况列表。
createprocedureproc_t1(专业char(10))
as
selectStudent.学号,Student.专业,Student.,
Course.课程号,Course.课程名,Course.学分,成绩
fromStudent,Course,SC
whereStudent.学号=SC.学号andCourse.课程号=SC.课程号andStudent.专业=专业
execproc_t1'信息管理'
(5)对学生成绩库中已创建的存储过程proc_t1进行修改,要现如下功能:
输入专业名称,产生该专业所有男生的选课情况列表,其中包括专业、学号、、课程号、课程名、成绩、学分等。
并调用修改后的存储过程,显示“信息管理”专业男生的选课情况列表。
alterprocedureproc_t1(专业char(10))
as
selectStudent.学号,Student.专业,Student.,
Course.课程号,Course.课程名,Course.学分,成绩
fromStudent,Course,SC
whereStudent.学号=SC.学号andCourse.课程号=SC.课程号andStudent.专业=专业and性别=1
execproc_t1'信息管理'
(6)删除学生成绩库中的存储过程proc_t2。
dropprocedureproc_t1
(7)编写存储过程,要现如下功能:
输入课程名称,产生该课程各分数段及其相应人数的成绩分布情况统计。
createprocedurescqk(课程名char(10))
as
begin
select课程名,
sum(casewhen成绩between0and59then1else0end)as'不及格',
sum(casewhen成绩between60and69then1else0end)as'60-69',
sum(casewhen成绩between70and89then1else0end)as'70-89',
sum(casewhen成绩between90and100then1else0end)as'90-100'
fromSC,Course
whereSC.课程号=Course.课程号and课程名=课程名
groupby课程名
end
execscqk'计算机原理'
4、触发器的使用。
在上面学生成绩库中完成如下操作:
(1)DML触发器的使用
①创建一个INSERT触发器tri_sc_insert,当向sc表中添加数据时,如果添加的数据与Student表中的数据不匹配(没有对应的学号),则将此数据删除。
createtriggertri_sc_insertonSC
forinsert
as
begin
declarebhchar(6)
selectbh=inserted.学号
frominserted
ifnotexists(select学号fromStudentwhereStudent.学号=bh)
deleteSCwhere学号=bh
end
②创建一个UPDATE触发器tri_sc_upd,用来防止用户修改SC表的成绩。
createtriggertri_sc_updonSC
forupdate
as
ifupdate(成绩)
begin
print'修改失败。
。
。
。
'
raiserror('不能修改SC表的成绩',16,10)
rollbacktransaction
end
③创建一个级联修改触发器trigger_1,即当修改学生课程表Course中的某门课的课程号时,对应学生的选课表SC中的课程号也作修改
createtriggertrigger_1onCourse
forupdate
as
ifupdate(课程号)
beginupdateSC
set课程号=(select课程号frominserted)
fromSC,deleted
whereSC.课程号=deleted.课程号
end
④建立一个级联删除触发器trigger_2,实现,即当删除表Student中的记录时,
自动删除表sc中对应选课记录。
createtriggertrigger_2onStudent
fordelete
as
begin
deleteSC
fromSC,deleted
whereSC.学号=deleted.学号
end
⑤创建触发器trigger_3,实现当修改学生表中的数据时,显示提示信息“学生表被修改了”。
createtriggertrigger_3onStudent
forupdate
as
print'学生表被修改了!
'
⑥修改已创建的触发器trigger_3,实现当修改学生表中的数据时,显示提示信息“学生表中XXX号学生记录被修改了”。
altertriggertrigger_3onStudent
forupdate
as
declarekchchar(6)
selectkch=学号
fromdeleted
print'学生表中学号为'+kch+'的记录被修改了'
⑦删除学生表上的触发器trigger_3。
droptriggertrigger_3
(2)DDL触发器的使用(选作)
①使用DDL触发器来防止在数据库中创建表。
创建一个safety_1触发器,禁止用户在学生成绩库数据库创建表。
并运行CREATETABLEnewtable(IDint)语句验证。
②用DDL触发器来防止在数据库中修改、删除表。
创建一个safety_2触发器,禁止用户在学生成绩库数据库中修改、删除表。
并运行DROPTABLESC语句验证
③删除触发器safety_1和safety_2
三、思考题
1、存储过程的类型有哪些?
分别有什么特征?
。
2、如何创建一个存储过程?
试述存储过程在程序设计的作用。
3、假设必须修改数据库中的一个存储过程,同时有几个用户被授予执行这个存储过程的权限,请问执行哪个语句可以实现修改,但又不影响现有的权限。
4、列出本实验你遇到的问题及解决办法
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 实验 存储 过程 触发器 定义 使用