存储过程函数与触发器操作答案.docx
《存储过程函数与触发器操作答案.docx》由会员分享,可在线阅读,更多相关《存储过程函数与触发器操作答案.docx(15页珍藏版)》请在冰豆网上搜索。
![存储过程函数与触发器操作答案.docx](https://file1.bdocx.com/fileroot1/2022-12/11/e1061528-b759-4b9e-a48c-b0c97ca3f8fb/e1061528-b759-4b9e-a48c-b0c97ca3f8fb1.gif)
存储过程函数与触发器操作答案
《存储过程、函数与触发器操作》实验
一、实验目的与要求
1、掌握存储过程的使用。
2、掌握函数的使用。
3、掌握触发器操作。
三、实验内容
一、存储过程
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'
5、创建一个返回执行代码为100的存储过程。
CREATEPROCEDUREProc_GetClassStudent4
@ClassIDvarchar(14)
AS
BEGIN
SELECT学号,姓名,性别,班级编号,年级,籍贯
FROM学生信息
WHERE班级编号=@ClassID
RETURN100
END
6、执行存储过程Proc_GetClassStudent2和Proc_GetClassStudent4,并定义两个个变量存储执行返回代码。
DECLARE@return1int,@return2int
EXEC@return1=Proc_GetClassStudent2'20031340000102'
EXEC@return2=Proc_GetClassStudent4'20031340000102'
SELECT@return1
SELECT@return2
7、在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'
8、在TeachingManageSYS中创建一个名为GetTeacherOrStudentInfo的存储过程,用于实现:
由执行该存储过程时提供的查询类别(“教师”或“学生”,默认值为“学生”)和学生姓名或教师姓名,查询学生或教师信息。
(使用教师姓名“邵军”和学生姓名“付佳燕”分别执行存储过程)
createprocedureGetTeacherOrStudentInfo
@姓名char(10),@classchar(4)
as
if(@class='教师')
select*fromTeacherInfowhereTname=@姓名
else
select*fromStudentInfowhereSname=@姓名
execGetTeacherOrStudentInfo'邵军','教师'
execGetTeacherOrStudentInfo'付佳燕','学生'
9、在commoditysell中创建一个名为GetFactProd的存储过程,用于实现:
由执行该存储过程时提供的厂家的名称,查询其生产的产品信息,返回产品的ProdID、ProdName、UnitPrice和StockAmount。
(使用“四川绵阳”和“上海黄埔”两个厂家的名称执行存储过程)
createprocGetFactProd
@FactAddrchar(20)
as
selectProdID,ProdName,UnitPrice,StockAmount
from
ProductInfo
whereFactAddr=@FactAddr
execGetFactProd'四川绵阳'
execGetFactProd'上海黄埔'
10、在commoditysell中创建一个名为SellProdAmount的存储过程,用于实现:
由执行该存储过程时提供的商场的名称,查询其销售的产品数量,并用输出参数输出销量数。
(使用“双桥子商场”和“十陵商场”两个商场名称执行程存储过程)
createprocedureSellProdAmount
@Enamechar(20),@Samoutintoutput
as
select@Samout=sum(SellAmout)
fromEmporiumSella,EmporiumInfob
wherea.EmpID=b.EmpIDandEmpName=@Ename
groupbya.EmpID
declare@xchar(20)
execSellProdAmount'双桥子商场',@xoutput
select@x销量数
--print@x
declare@ychar(20)
execSellProdAmount'十陵商场',@youtput
select@y销量数
--print@y
11、在教务管理系统中创建一个名为GetStudentScoreInfo的存储过程,用于实现:
由执行该存储过程时提供的学生姓名,使用输出参数输出学生成绩的总分、最高分和最低分。
(使用“冬云”和“张宇宏”两个学生姓名执行存储过程)
createprocGetStudentScoreInfo
@namevarchar(8),
@sumintoutput,
@maxintoutput,
@minintoutput
as
select@sum=sum(成绩),@max=max(成绩),@min=min(成绩)from成绩表,学生信息
where成绩表.学号=学生信息.学号and姓名=@name
groupby成绩表.学号
declare@sumint,@maxint,@minint
execGetStudentScoreInfo'冬云',@sumoutput,@maxoutput,@minoutput
select@sum总分,@max最高分,@min最低分
declare@sum1int,@max1int,@min1int
execGetStudentScoreInfo'朱志',@sum1output,@max1output,@min1output
select@sum1总分,@max1最高分,@min1最低分
二、函数练习
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
4、在成绩表上创建一个insteadofinsert触发器,实现:
当向表成绩表插入记录时检查分数的合理性,如果不合理就不进行插入操作,否则允许。
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)
5、在成绩表上创建一个insteadofdelete触发器,实现:
当从表成绩表删除记录时检查各课程的成绩是否为空,如果为空就不允许进行删除操作,否则允许。
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
6、在成绩表上创建一个insteadofupdate触发器,实现:
当从表成绩表更新记录时检查当前用户是否是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!
='dbo')
print'不是dbo用户不能修改学生姓名信息'
else
printcurrent_user
update成绩表set成绩=@score1where成绩=@score2
end
update成绩表set成绩=93where学号='200130000146'and课程编号=51
7、在视图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)
8、建立一个DDL触发器,用于保护数据库中的数据表不被修改,不被删除。
CREATETRIGGER禁止对数据表操作
ONDATABASE
FORDROP_TABLE,ALTER_TABLEAFTER
AS
PRINT'对不起,您不能对数据表进行操作'
ROLLBACK
ALTERTable成绩表
ADDCONSTRAINTScore_CHECK
Check
(
课程编号>=0and课程编号<=100
)
9、建立一个DDL触发器,用于保护当前SQLServer服务器里所有数据库不能被删除。
CREATETRIGGER不允许删除数据库
ONallserver
FORDROP_DATABASE
AS
PRINT'对不起,您不能删除数据库'
ROLLBACK
dropdatabaseTeachingManageSYS
10、给score表创建一个afterinsert触发器,实现:
当向成绩表插入记录时,提示‘向成绩表插入了内容为:
…的记录’,也就是把插入表中的记录信息显示出来。
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)
11、给成绩表创建一个afterupdate触发器,实现:
更新成绩表时,提示‘把成绩表内容为:
…的记录更新为:
…’,也就是把更新表中的记录信息显示出来。
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
12、给成绩表创建一个afterdelete触发器,实现:
当向成绩表删除记录时,提示‘从成绩表删除了内容为:
…的记录’,也就是把删除的记录信息显示出来。
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
13、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_amountwhere