存储过程、函数与触发器操作答案

《存储过程、函数与触发器操作》实验

一、实验目的与要求

1、掌握存储过程的使用。

2、掌握函数的使用。

3、掌握触发器操作。

三、实验内容

一、存储过程

1、在“教务管理系统”数据库中创建一个名为ProcStudentlnfo的

存储过程，它返回学生的学号、姓名、性别、班级编号、年级和籍贯

信息、。

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)=120031340000102,

AS

SELECT学号，姓名，性别，班级编号，年级，籍贯FROM学生信息WHERE班级编号=@门855工口

1

EXECUTEProc_GetClassStudent2'20031340000103,

5、创建一个返回执行代码为100的存储过程。

CREATEPROCEDUREProc_GetClassStudent4

@ClassIDvarchar（14）

AS

BEGIN

SELECT学号，姓名，性别，班级编号，年级，籍贯

FROM学生信息

WHERE班级编号=尤12$$工D

RETURN100

END

6执行存储过程Proc_GetClassStudent2和

Proc_GetClassStudent4,并定义两个个变量存储执行返回代码。

DECLAREQreturnlint,@return2int

EXEC0returnl=Proc_GetClassStudent2'200313400001021

EXEC@return2=Proc_GetClassStudent4*20031340000102,

SELECT@returnl

SELECT@return2

7、在TeachingManageSYS中创I建一个名为GetStud㊀ntScore

的存储过程，用于实现：由执行该存储过程时提供的学生的Sn。（学

号）查询其每门课程的Score（成绩），并返回学生的Sn。（学号）、

Sname（姓名）、Ssex（性别）、Cname（课程的课程名）

（使用“学生成绩查询”存储过程，查询学号为"010101"和

“010201”的学生的成绩。）

createprocedureGetStudentscore

©Student工Dvarchar（14）

as

selectStudentinfo.Sno学号，Sname姓名，Ssex性别，Cname课程名，Score成绩

fromStudentInfo,CourseInfo,Studentcourse

whereStudentcourse.Sno=StudentInfo.Snoand

Studentcourse.Cno=CourseInfo.Cno

andStudentInfo.Sno=@StudentID

execGetStudentScore'010101,

execGetStudentScore,010201,

2

8、在TeachingManageSYS中创建一个名为

GetTeacherOrStudentInfo的存储过程，用于实现：由执行该存

储过程时提供的查询类别（“教师”或“学生”，默认值为“学生”）

和学生姓名或教师姓名，查询学生或教师信息。

（使用教师姓名“邵军”和学生姓名“付佳燕”分别执行存储过程）

createprocedureGetTeacherOrStudentlnfo

@姓名char（10）,@classchar（4）

as

if（@class=*教师'）

select*fromTeacherinfowhereTname=@姓名

else

select*fLurnSLudenLInfuwhexeSriaiii已=@姓名

execGetTeacherOrStudentlnfo，邵军一教师，

execGetTeacherOrStudentlnfo，付佳燕'J学生，

9、在commoditysell中创建一个名为GetFactProd的存储过程，

用于实现：由执行该存储过程时提供的厂家的名称，查询其生产的产

品信息，返回产品的ProdID>ProdName、UnitPrice和

StockAmounto

（使用“四川绵阳”和“上海黄埔”两个厂家的名称执行存储过程）

createprocGetFactProd

QFactAddrchar（20）

as

selectProdID,ProdName,UnitPrice,StockAmount

from

ProductInfo

whereFactAddr=@FactAddr

execGetFactProd'四川绵阳,

execGetFactProd'上海黄埔'

10>在commoditysell中创建一个名为SellProdAmount的存储过

程，用于实现：由执行该存储过程时提供的商场的名称，查询其销售

3

的产品数量，并用输出参数输出销量数。

(使用“双桥子商场”和“十陵商场”两个商场名称执行程存储过

程)

createprocedureSellProdAmount

©Enamechar(20),@Samoutintoutput

as

select@Samout=sum(SellAnout)

fromEmporiumSella,Emporiuminfob

wherea.EmpID=b.EmpIDandEmpName=@Ename

groupbya.EmpID

declare@xchar(20)

execSellProdAmount，双桥子商场I@xoutput

selecL销量数

--print@x

declare@ychar(20)

execSellProdAmount'十陵商场output

selectey销量数

--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'冬云I©sumoutput,@maxoutput,@minoutput

select@sum总分，@max最高分，@min最低分

4

declare@sumlint,@maxlint,Qminlint

execGetstudentscoreinfo'朱志',@sum：Loutput,emaxloutput,@minloutput

select@suml总分，@maxl最高分，@minl最低分

二、函数练习

1、TeachingManageSYS创建一个内嵌表值函数chengjil,实现根据姓

名查询该学生所有课程的成绩。

CREATEFUNCTIONchengjil(Gxname_inchar(10))

RETURNSTABLE

AS

RETURN(selectCname,ScorefromStudentCourse,Studentinfo,Courseinfo

WHEREStudentinfo.Sno=StudentCourse.SnoAND

CourseInfo.Cno=StudentCourse.CnoANDSname=@xname_in)

select*fromchengjil(1魏士斌，)

2、TeachingManageSYS创建一个内嵌表值函数TeacherCourse,实现

根据输入的Tno(教师编号)该教师的姓名和所授课程的课程号。

CREATEFUNCTIONTeacherCourse

(@numberchar(25))

RETURNSTABLE

AS

RETURN

(SELECTTname

FROMTeacherinfoWHERETno=@number

UNIONSELECTCno

FROMTeach

WHERETno=@number

)

3、TeachingManageSYS创建一个多语句表值函数，实现查询某一课

程的考试成绩。

CREATEFUNCTIONall_scoreJ@cname_inchar(10))

RETURNS@all_score_tabtable(Snochar(6)primarykey,Snamechar(10)not

null,Ssexchar(2),Scorereal)

AS

BEGIN

INSERT@all_score_tab

SELECTa.Sno,a.Sname,a.Ssex,b.Score

5

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触发器，这个触发器的作用是:

在修改一条记录的时候，发出“又修改了一个学生的成绩”的友好提

zj\O

CREATETRIGGERscore_update

ON成绩表

AFTERUPDATE

AS

BEGIN

PRINT，又修改了一个学生的成绩，

END

update成绩表set成绩=93where学号=，2001300001461and课程编号=51

3、设计一个简单的AFTERDELETE触发器，这个触发器的作用是:

在删除一条记录的时候，发出“又删除了一个学生的成绩”的友好提

Zj\o

6

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(,200130000L46,,51,87)

5、在成绩表上创建一个insteadofdelete触发器，实现：当从表成绩

表删除记录时检查各课程的成绩是否为空，如果为空就不允许进行删

除操作，否则允许。

createtriggerscore_delete_instead

on成绩表

insteadofdelete

as

begin

setnocounton

declare@idint,@scoreint

7

select@id=编号，@score=成绩fromdeleted

if(@score>=0)

print，成绩正常，不能删除该记录，

else

deletefrom成绩表where编号=@id

end

deletefrom成绩表where编号=1020

6、在成绩表上创建一个insteadof叩date触发器，实现：当从表成绩

表更新记录时检查当前用户是否是dbo,如果不是dbo就不允许进行

更新操作，否则允许。

createtriggerscore_update_instead

on成绩表

insteadofupdate

as

begin

setnocounton

declare@scorelint,@score2int,@current_uservarchar(10)

select@scorel=成绩frominserted

select@score2=成绩fromdeleted

if(current_user!=*dbo')

print，不是db。用户不能修改学生姓名信息，

else

printcurrent_user

update成绩表set成绩=@scorelwhere成绩=@score2

end

update成绩表set成绩where学号200130000146，and课程编号-51

7、在视图v_score创建一个insteadofinsert触发器,实现：从视图

v_score插入记录时，提示'从视图v_score插入一条记录

createviewv_score

as

select学号，课程编号，成绩

from成绩表

createtriggerv_score_insert

onv_score

insteadofinsert

as

8

begin

insertinto成绩表（学号，课程编号，成绩）select*frominserted

print，从视图v_score插入了一条记录，

end

insertintov_scorevalues（1200130000146,,51,97）

8、建立一个DDL触发器，用于保护数据库中的数据表不被修改，不

被删除。

CREATETRIGGER禁止对数据表操作

ONDATABASE

FORDROP_TABLE,ALTER]ABLEAFTER

AS

PRINT，对不起，您不能对数据表进行操作，

ROLLBACK

ALTERTable成绩表

ADDCONSTRAINTScore_CHECK

Check

（

课程编号＞=0and课程编号＜=100

）

9、建立一个DDL触发器，用于保护当前SQLServer服务器里所有

数据库不能被删除。

CREATETRIGGER不允许删除数据库

ONallserver

FORDROP_DATABASE

AS

PRINT，对不起，您不能删除数据库，

ROLLBACK

dropdatabaseTeachingManageSYS

10、给score表创建一个afterinsert触发器，实现：当向成绩表插入

记录时，提示'向成绩表插入了内容为：…的记录'，也就是把插入

表中的记录信息显示出来。

CREATETRIGGER显示—工nsert

ON成绩表

AFTERINSERT

9

AS

BEGIN

Declare©编号int,@学号va:char(14),@课程编号int,@成绩int

select@编号=编号，©学号=学号，@课程编号=课程编号，@成绩=成绩frominserted

print，插入的记录为：，+CAST(@编号ASvarchar(16))+，，，+@学号+，，,+CAST(0

课程编号ASvarchar(16))+,,'+CAST(©成绩ASvarchar(6))

END

insertinto成绩表(学号，课程编号，成绩)values('200130000146151,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，修改前的记录为：，+G学号+3，+CAST(©课程编号ASvarchar(16))+,,

'+CAST(@成绩ASvarchar(6))

print，修改后的记录为：'+G学号+\，+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，删除的记录为：7CAST(©编号ASvarchar(16))+、,+@学号+If+CAST(@

课程编号ASvarchar(16))+\，+CAST(@成绩ASvarchar(6))

10

deletefrom成绩表where编号=1041

13、commoditysell库中给sell表创建一个afterinserl触发器，实现:

当向sell插入一条记录时，修改stock表中的stock_amount(库存数量),

值等于stock_amount-sell_amount,还有修改时间modified_date,并显

示相应的提示信息：“库存量还剩余stock_amount-sell_amount当

(stock_amount-sell_amount)<0Bj",不允许进行插入操作，即操作回滚,

并显示相应的提示信息：“库存量不足，只有stock_amount”。

附stocksell表信息

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,

11

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

pi.inL'库存量还剩余，*+CAST(GsLuuk_dniouiiL+2ASvaxulid£(6))

end

else

begin

print'库存量还剩余，+CAST(@stock_amountASvarchar(6))

updatestocksetstock_amount=@stock_amountwhereprod_id=@prod_id

end

insertintosellvalues('100012,'2013-4-11',^Ol1)

14、在sell表中创建一个insteadofinsert触发器，实现14题

的功能。

15、在stock表中创建一个afterdelete触发器,实现：当删除stock

表中记录时,如果记录的stock_amount不为零,则记录不能删除，

即操作回滚，并显示提示信息：“库存量不为零，记录不能删除”，否

则显示提示信息："记录删除了二

CREATETRIGGERscore_delete2

ON成绩表

AFTERDELETE

AS

declare@scoreint