存储过程函数与触发器操作答案.docx
《存储过程函数与触发器操作答案.docx》由会员分享，可在线阅读，更多相关《存储过程函数与触发器操作答案.docx（15页珍藏版）》请在冰豆网上搜索。
存储过程函数与触发器操作答案
《存储过程、函数与触发器操作》实验
一、实验目的与要求
1、掌握存储过程的使用。
2、掌握函数的使用。
3、掌握触发器操作。
二、实验平台
1、操作系统：
WindowsXP或Windows2003
2、数据库管理系统：
SQLServer2005
三、实验内容
一、存储过程
1、在“教务管理系统”数据库中创建一个名为ProcStudentInfo的存储过程，它返回学生的学号、姓名、性别、班级编号、年级和籍贯信息。
CREATEPROCEDUREProc_StudentInfo
AS
SELECT学号,姓名,性别,班级编号,年级,籍贯FROM学生信息
2、用EXECUTE执行Proc_StudentInfo存储过程。
EXECUTEProc_StudentInfo
3、创建一个存储过程，用于返回“教务管理系统”数据库上某个班级中所有学生的信息。
CREATEPROCEDUREProc_GetClassStudent1
@ClassIDvarchar（14）
AS
SELECT学号,姓名,性别,班级编号,年级,籍贯FROM学生信息WHERE班级编号=@ClassID
4、创建一个存储过程，用于返回“教务管理系统”数据库上某个班级中所有学生的信息，默认班级编号为'20031340000102'。
CREATEPROCEDUREProc_GetClassStudent2
@ClassIDvarchar（14）='20031340000102'
AS
SELECT学号,姓名,性别,班级编号,年级,籍贯FROM学生信息WHERE班级编号=@ClassID
EXECUTEProc_GetClassStudent2'20031340000103'
8、创建一个返回执行代码为100的存储过程。
CREATEPROCEDUREProc_GetClassStudent4
@ClassIDvarchar（14）
AS
SELECT学号,姓名,性别,班级编号,年级,籍贯
FROM学生信息
WHERE班级编号=@ClassID
RETURN100
9、执行存储过程Proc_GetClassStudent2和Proc_GetClassStudent4，并定义两个个变量存储执行返回代码。
DECLARE@return1int,@return2int
EXEC@return1=Proc_GetClassStudent2'20031340000102'
EXEC@return2=Proc_GetClassStudent4'20031340000102'
SELECT@return1
SELECT@return2
10、在TeachingManageSYS中创建一个名为GetStudentScore的存储过程，用于实现：
由执行该存储过程时提供的学生的Sno（学号）查询其每门课程的Score（成绩），并返回学生的Sno（学号）、Sname（姓名）、Ssex（性别）、Cname（课程的课程名）
（使用“学生成绩查询”存储过程，查询学号为“010101”和“010201”的学生的成绩。
）
createprocedureGetStudentScore
@StudentIDvarchar（14）
as
selectStudentInfo.Sno学号,Sname姓名,Ssex性别,Cname课程名,Score成绩
fromStudentInfo,CourseInfo,StudentCourse
whereStudentCourse.Sno=StudentInfo.SnoandStudentCourse.Cno=CourseInfo.Cno
andStudentInfo.Sno=@StudentID
execGetStudentScore'010101'
execGetStudentScore'010201'
11、在TeachingManageSYS中创建一个名为GetTeacherOrStudentInfo的存储过程，用于实现：
由执行该存储过程时提供的查询类别（“教师”或“学生”，默认值为“学生”）和学生姓名或教师姓名，查询学生或教师信息。
（使用教师姓名“邵军”和学生姓名“付佳燕”分别执行存储过程）
createprocedureGetTeacherOrStudentInfo
@姓名char（10）,@classchar（4）
as
if（@class='教师'）
select*fromTeacherInfowhereTname=@姓名
else
select*fromStudentInfowhereSname=@姓名
execGetTeacherOrStudentInfo'邵军','教师'
execGetTeacherOrStudentInfo'付佳燕','学生'
12、在commoditysell中创建一个名为GetFactProd的存储过程，用于实现：
由执行该存储过程时提供的厂家的名称，查询其生产的产品信息，返回产品的ProdID、ProdName、UnitPrice和StockAmount。
（使用“四川绵阳”和“上海黄埔”两个厂家的名称执行存储过程）
createprocGetFactProd
@FactAddrchar（20）
as
selectProdID,ProdName,UnitPrice,StockAmount
from
ProductInfo
whereFactAddr=@FactAddr
execGetFactProd'四川绵阳'
execGetFactProd'上海黄埔'
13、在commoditysell中创建一个名为SellProdAmount的存储过程，用于实现：
由执行该存储过程时提供的商场的名称，查询其销售的产品数量，并用输出参数输出销量数。
（使用“双桥子商场”和“十陵商场”两个商场名称执行程存储过程）
createprocedureSellProdAmount
@EmpNamechar（20）
as
selectEmporiumInfo.EmpName,sum（SellAmout）as销售量
fromEmporiumSell,EmporiumInfo
whereEmporiumSell.EmpID=EmporiumInfo.EmpID
andEmpName=@EmpName
groupbyEmporiumInfo.EmpName
execSellProdAmount'双桥子商场'
execSellProdAmount'十陵商场'
14、在教务管理系统中创建一个名为GetStudentScoreInfo的存储过程，用于实现：
由执行该存储过程时提供的学生姓名，使用输出参数输出学生成绩的总分、最高分和最低分。
（使用“冬云”和“张宇宏”两个学生姓名执行存储过程）
createprocedureGetStudentScoreInfo
@studentnamechar（8）
as
selectsum（成绩表.成绩）as总分,max（成绩表.成绩）as最高分,min（成绩表.成绩）as最低分
from成绩表,学生信息
where成绩表.学号=学生信息.学号and学生信息.姓名=@studentname
groupby成绩表.学号
execGetStudentScoreInfo@studentname='朱志'
execGetStudentScoreInfo@studentname='张宇宏'
二、函数练习
1、TeachingManageSYS创建一个内嵌表值函数chengji1，实现根据姓名查询该学生所有课程的成绩。
CREATEFUNCTIONchengji1（@xname_inchar（10））
RETURNSTABLE
AS
RETURN（selectCname,ScorefromStudentCourse,StudentInfo,CourseInfo
WHEREStudentInfo.Sno=StudentCourse.SnoAND
CourseInfo.Cno=StudentCourse.CnoANDSname=@xname_in）
select*fromchengji1（'魏士斌'）
2、TeachingManageSYS创建一个内嵌表值函数TeacherCourse，实现根据输入的Tno（教师编号）该教师的姓名和所授课程的课程号。
CREATEFUNCTIONTeacherCourse
（@numberchar（25））
RETURNSTABLE
AS
RETURN
（SELECTTname
FROMTeacherInfoWHERETno=@number
UNIONSELECTCno
FROMTeach
WHERETno=@number
）
3、TeachingManageSYS创建一个多语句表值函数，实现查询某一课程的考试成绩。
CREATEFUNCTIONall_score（@cname_inchar（10））
RETURNS@all_score_tabtable（Snochar（6）primarykey,Snamechar（10）notnull,Ssexchar
（2）,Scorereal）
AS
BEGIN
INSERT@all_score_tab
SELECTa.Sno,a.Sname,a.Ssex,b.Score
FROMStudentINFOa,StudentCourseb,CourseINFOc
WHEREa.Sno=b.SnoANDb.Cno=c.CnoANDc.Cname=@cname_in
RETURN
END
select*fromall_score（'数据库原理'）
三、触发器练习
1、设计一个简单的AFTERINSERT触发器，这个触发器的作用是：
在插入一条记录的时候，发出“又添加了一个学生的成绩”的友好提示。
CREATETRIGGERscore_insert
ON成绩表
AFTERINSERT
AS
BEGIN
PRINT'又添加了一个学生的成绩'
END
GO
insertinto成绩表（学号,课程编号,成绩）values（'200130000146',51,87）
2、设计一个简单的AFTERUPDATE触发器，这个触发器的作用是：
在修改一条记录的时候，发出“又修改了一个学生的成绩”的友好提示。
CREATETRIGGERscore_update
ON成绩表
AFTERUPDATE
AS
BEGIN
PRINT'又修改了一个学生的成绩'
END
update成绩表set成绩=93where学号='200130000146'and课程编号=51
3、设计一个简单的AFTERDELETE触发器，这个触发器的作用是：
在删除一条记录的时候，发出“又删除了一个学生的成绩”的友好提示。
CREATETRIGGERscore_delete
ON成绩表
AFTERDELETE
AS
BEGIN
PRINT'又删除了一个学生的成绩'
END
delete成绩表where学号='200130000146'and课程编号=51
5、在score表上创建一个insteadofinsert触发器，实现：
当向表score插入记录时检查分数的合理性，如果不合理就不进行插入操作，否则允许。
createtriggerscore_insert_instead
on成绩表
insteadofinsert
as
begin
setnocounton
declare@scoreint
select@score=成绩frominserted
if（@score<0or@score>100）
print'分数不合理'
else
insertinto成绩表（学号,课程编号,成绩）
select学号,课程编号,成绩frominserted
end
insertinto成绩表（学号,课程编号,成绩）values（'200130000146',51,87）
6、在score表上创建一个insteadofdelete触发器，实现：
当从表score删除记录时检查各课程的成绩是否为空，如果为空就不允许进行删除操作，否则允许。
createtriggerscore_delete_instead
on成绩表
insteadofdelete
as
begin
setnocounton
declare@idint,@scoreint
select@id=编号,@score=成绩fromdeleted
if（@score>=0）
print'成绩正常，不能删除该记录'
else
deletefrom成绩表where编号=@id
end
deletefrom成绩表where编号=1020
7、在score表上创建一个insteadofupdate触发器，实现：
当从表score更新记录时检查当前用户是否是dbo，如果不是dbo就不允许进行更新操作，否则允许。
createtriggerscore_update_instead
on成绩表
insteadofupdate
as
begin
setnocounton
declare@score1int,@score2int,@current_uservarchar（10）
select@score1=成绩frominserted
select@score2=成绩fromdeleted
if（current_user!
='dbo1'）
print'不是dbo用户不能修改学生姓名信息'
else
printcurrent_user
update成绩表set成绩=@score1where成绩=@score2
end
update成绩表set成绩=93where学号='200130000146'and课程编号=51
8、在视图v_score创建一个insteadofinsert触发器，实现：
从视图v_score插入记录时，提示‘从视图v_score插入一条记录’。
createviewv_score
as
select学号,课程编号,成绩
from成绩表
createtriggerv_score_insert
onv_score
insteadofinsert
as
begin
insertinto成绩表（学号,课程编号,成绩）select*frominserted
print'从视图v_score插入了一条记录'
end
insertintov_scorevalues（'200130000146',51,97）
9、建立一个DDL触发器，用于保护数据库中的数据表不被修改，不被删除。
CREATETRIGGER禁止对数据表操作
ONDATABASE
FORDROP_TABLE,ALTER_TABLE
AS
PRINT'对不起，您不能对数据表进行操作'
ROLLBACK
ALTERTable成绩表
ADDCONSTRAINTScore_CHECK
Check
（
课程编号>=0and课程编号<=100
）
10、建立一个DDL触发器，用于保护当前SQLServer服务器里所有数据库不能被删除。
CREATETRIGGER不允许删除数据库
ONallserver
FORDROP_DATABASE
AS
PRINT'对不起，您不能删除数据库'
ROLLBACK
dropdatabaseTeachingManageSYS
11、给score表创建一个afterinsert触发器，实现：
当向score插入记录时，提示‘向score表插入了内容为：
…的记录’，也就是把插入表中的记录信息显示出来。
CREATETRIGGER显示_Insert
ON成绩表
AFTERINSERT
AS
BEGIN
Declare@编号int,@学号varchar（14）,@课程编号int,@成绩int
select@编号=编号,@学号=学号,@课程编号=课程编号,@成绩=成绩frominserted
print'插入的记录为：
'+CAST（@编号ASvarchar（16））+'，'+@学号+'，'+CAST（@课程编号ASvarchar（16））+'，'+CAST（@成绩ASvarchar（6））
END
insertinto成绩表（学号,课程编号,成绩）values（'200130000146',51,87）
12、给score表创建一个afterupdate触发器，实现：
更新score表时，提示‘把score表内容为：
…的记录更新为：
…’，也就是把更新表中的记录信息显示出来。
CREATETRIGGER显示_update
ON成绩表
AFTERupdate
AS
Declare@编号int,@学号varchar（14）,@课程编号int,@成绩int,@学号varchar（14）,@课程编号int,@成绩int
select@编号=编号,@学号=学号,@课程编号=课程编号,@成绩=成绩fromdeleted
select@学号=学号,@课程编号=课程编号,@成绩=成绩frominserted
print'编号为：
'+CAST（@编号ASvarchar（16））
print'修改前的记录为：
'+@学号+'，'+CAST（@课程编号ASvarchar（16））+'，'+CAST（@成绩ASvarchar（6））
print'修改后的记录为：
'+@学号+'，'+CAST（@课程编号ASvarchar（16））+'，'+CAST（@成绩ASvarchar（6））
update成绩表set成绩=100where编号=1025
13、给score表创建一个afterdelete触发器，实现：
当向score删除记录时，提示‘从score表删除了内容为：
…的记录’，也就是把删除的记录信息显示出来。
CREATETRIGGER显示_delete
ON成绩表
AFTERdelete
AS
Declare@编号int,@学号varchar（14）,@课程编号int,@成绩int
select@编号=编号,@学号=学号,@课程编号=课程编号,@成绩=成绩fromdeleted
print'删除的记录为：
'+CAST（@编号ASvarchar（16））+'，'+@学号+'，'+CAST（@课程编号ASvarchar（16））+'，'+CAST（@成绩ASvarchar（6））
deletefrom成绩表where编号=1041
14、commoditysell库中给sell表创建一个afterinsert触发器，实现：
当向sell插入一条记录时，修改stock表中的stock_amount（库存数量），值等于stock_amount-sell_amount，还有修改时间modified_date，并显示相应的提示信息：
“库存量还剩余stock_amount-sell_amount”，当（stock_amount-sell_amount）<0时，不允许进行插入操作，即操作回滚，并显示相应的提示信息：
“库存量不足，只有stock_amount”。
附stock、sell表信息
stock（
prod_idchar（10）,
prod_namevarchar（10）,
unit_priceint,
stock_amountint,
modified_datedatetime
）
sell（
order_idintidentity（1,1）,
prod_idchar（10）,
sell_amountint,
sell_datedatetime,
Salervarchar（10）
）
createtable
stock（
prod_idchar（10）primarykey,
prod_namevarchar（10）,
unit_priceint,
stock_amountint,
modified_datedatetime
）
createtable
sell（
order_idintidentity（1,1）primarykey,
prod_idchar（10）foreignkeyreferencesstock（prod_id）,
sell_amountint,
sell_datedatetime,
Salervarchar（10）
）
insertintostockvalues（'10001','三星手机',2999,10,'2013-4-10'）
CREATETRIGGERsell_Insert
ONsell
AFTERINSERT
AS
Declare@prod_idint,@sell_amountint,@stock_amountint
select@prod_id=prod_id,@sell_amount=sell_amountfrominserted
select@stock_amount=stock_amount-@sell_amountfromstock
--@stock_amount=6
if（@stock_amount<0）
begin
ROLLBACKtransaction
print'库存量还剩余，只有'+CAST（@stock_amount+2ASvarchar（6））
end
else
begin
print'库存量还剩余'+CAST（@stock_amountASvarchar（6））
updatestocksetstock_amount=@stock_amountwhereprod_id=@prod_id
end
insertintosellvalues（'10001',2,'2013-4-11','101'）
15、在sell表中创建一个insteadofinsert触发器，实现14题的功能。
16、在stock表中创建一个afterdelete触发器，实现：
当删除stock表中记录时，如果记录的stock_amount不为零，
升级会员 