存储过程和处罚期.docx
《存储过程和处罚期.docx》由会员分享，可在线阅读，更多相关《存储过程和处罚期.docx（9页珍藏版）》请在冰豆网上搜索。
存储过程和处罚期
航空大学实验报告
二0一二年五月二十八日
课程名称：
数据库原理实验名称：
存储过程与触发器
班级：
10202225：
仇鑫同组人：
指导教师评定：
签名：
一、实验环境
1．Windows2000或以上版本；
2．SQLServer2000或2005。
二、实验目的
1．掌握存储过程的创建、修改、使用、删除。
2．掌握触发器的创建、修改、使用、删除。
三、实验步骤及参考源代码
1、存储过程实验部分
（1）创建存储过程。
利用createprocedure语句创建存储过程。
例一：
在jxgl数据库中，创建一个名称为Select_S的存储过程，该存储过程，该存储过程的功能是从数据库表S中查询所有女同学的信息，并执行该存储过程。
具体实现的代码如下：
创建：
createprocedureSelect_Sas
select*
froms
wheresex='女'
执行：
executeSelect_S
例二：
定义具有参数的存储过程。
在jxgl数据库中，创建一个名称为InsRecTos的存储过程，该存储过程的功能是向S表中插入一条数据，新纪录的值由参数提供，如果未提供的值给sex时，由参数的默认值代替。
具体实现代码如下：
创建：
createprocedureInsRecToS（snochar（5）,snvarchar（8）,sexchar
（2）='男',ageint,deptvarchar（20））
as
insertintoS
values（sno,sn,sex,age,dept）
执行：
executeInsRecToSsno='S8',sn='罗兵',age=18,dept='信息'
例三：
定义能够返回值的存储过程。
在jxgl数据库中创建一个名称为Query_S的存储过程，该存储过程的功能是从S表中根据学号查询某一学生的和年龄，并返回。
具体实现代码如下：
创建：
createprocedureQuery_S（snochar（5）,snvarchar（8）output,agesmallintoutput）
as
selectsn=sn,age=age
froms
wheresno=sno
（2）执行存储过程。
Query_S存储过程可以通过以下方法执行
declareSNvarchar（8）,AGEsmallint
executeQuery_S'S8',SNoutput,AGEoutput
selectSn,AGE
（3）查看和修改存储过程。
使用alterprocedure命令，修改存储过程InsRecToS，代码如下：
alterprocedureInsRecToS
（snochar（5）,snvarchar（8）,sexchar
（2）='女',agesmallint,deptvarchar（20））
as
insertintos
values（sno,sn,sex,age,dept）
（4）查看、重命名和删除存储过程
例一：
查看数据库表s中存储过程Select_S的源代码
execsp_helptextSelect_S
例二：
将存储过程Select_S改名为Select_Student。
sp_renameSelect_S,Select_Student
例三：
将存储过程Select_Student从数据库中删除。
dropprocedureSelect_Student
（5）在DingBao数据库中创建存储过程C_P_Proc，实现参数化查询顾客订阅信息，查询参数为顾客，要求能查询出参数指定的顾客编号、顾客名、订阅报纸名及订阅份数等信息。
CREATEPROCEDUREC_P_ProcnameVARCHAR（8）
AS
SELECTCUSTOMER.cna,CUSTOMER.cno,PAPER.pna,CP.num
FROMPAPER,CP,CUSTOMER
WHEREcna=nameANDCUSTOMER.cno=CP.cnoAND
PAPER.pno=CP.pno;
（6）执行存储过程C_P_Pro，实现对“涛”、“钱金浩”等不同顾客的订阅信息放入查询。
ExecuteC_P_Procname='涛'
结果如下：
ExecuteC_P_Procname='钱金浩'
结果如下：
（7）删除存储过程C_P_Pro。
DROPPROCEDUREC_P_Proc
2、触发器实验部分
（1）创建触发器
例一：
对s表创建update触发器TR_S_Age_update：
createtriggerTR_S_Age_update
ons
forupdateas
declareiAgeint;
selectiAge=agefrominserted
ifiAge<8oriAge>45
begin
raiserror（'学生年龄应该大于等于，并小于等于',16,1）
rollbacktransaction
end
当对s做update操作时，会自动触发TR_S_Age_update触发器，若入学日期与出生日期年份相差小于8或大于45时，则取消该次修改操作。
例二：
创建一个触发器，当向s表中更新一条记录时，会自动显示s表中的记录：
createtriggerChange_S_Sel
onsfor
insert,update,deleteas
select*froms
（2）触发器的引用（使用）。
类似地对课程c表创建插入触发器TR_C_insert：
createtriggerTR_C_insertonc
forinsertas
declareictint;
selectict=ctfrominserted
ifict<1orict>10
begin
raiserror（'课程学分应该大于等于，小于等于',16,1）
rollbacktransaction
end
当对c表插入一条记录，如insertintoc（cno,,ct）values（‘c8’,’运筹学’,’0.5’），则引发触发器TR_C_insert，取消该记录的插入。
在查询子窗口中，对表s执行修改命令操作时，引发了触发器修改触发器。
还能对表创建delete触发器，如果此表由delete型触发器，则删除记录时触发器将被触发执行。
被删的记录存发在deleted表中，如下是在s表中创建的TR_S_delete触发器：
createtriggerTR_S_deleteons
fordeleteas
declareicountint;
selecticount=count（*）
fromdeleted,sc
wheredeleted.sno=sc.sno
ificount>=1
begin
raiserror（'该学生在表sc中被引用，暂不能被删除！
',16,1）
rollbacktransaction
end
当执行删除命令deletefromswheresno=’s1’时，由于sc表中有对学号为’s1’的学生选课记录，因此删除未能成功。
（3）查看、修改和删除触发器。
<1>修改触发器。
修改触发器TR_S_Age_update：
altertriggerTR_S_Age_update
ons
forupdateas
declareiAgeint;
selectiAge=age
frominserted
ifiAge<8oriAge>45
begin
raiserror（'学生年龄应该大于等于，并小于等于',16,1）
rollbacktransaction
end
<2>使用系统存储过程查看触发器。
例一：
查看已建立Change_S_Sel触发器所涉及的表：
sp_depends'Change_S_Sel'
例二：
查看已建立的Change_S_Sel的命令文本：
sp_helptext'Change_S_sel'
例题三：
查看已建立的Change_S_Sel触发器。
execsp_help'Change_S_Sel'
<3>删除触发器。
删除前面创建的触发器TR_S_Age_update：
droptriggerTR_S_Age_update
（4）在DingBao数据库中针对PAPER创建插入触发器TR_PAPER_I、删除触发器TR_PAPER_D、修改触发器TR_PAPER_U。
具体要求如下。
<1>对PAPER的插入触发器：
插入报纸记录，单价为负值或为空时，设定为10元。
CREATETRIGGERTR_PAPER_IONpaper
FORINSERTAS
DECLAREipprFLOAT;
declareipnoint;
SELECTippr=ppr,ipno=pnofrominserted
begin
ifippr<0oripprisNULL
begin
raiserror（'报纸的单价为空或小于零！
',16,1）
updatepapersetppr=10
wherepaper.pno=ipno
end
end
<2>对PAPER的删除触发器：
要删除的记录，若正在被订阅表CP参照时，级联删除订阅表中相关的订阅记录。
createtriggerTR_Paper_Donpaper
insteadofdeleteas
declareicountint;
selecticount=count（*）fromdeleted,Cpwheredeleted.pno=Cp.pno
ificount>=1
begin
declareipnoint
selectipno=deleted.pno
fromdeleted
deletefromcp
wherepno=ipno
end
<3>对PAPER的修改触发器：
当报纸的单价修改为负值或为空时，提示“输入单价不正确！
”的信息，并取消修改操作。
createtriggerTR_Paper_Uonpaper
forupdateas
declareipprfloat;
selectippr=pprfrominserted
ifippr<0oripprisnull
begin
raiserror（'输入单价不正确!
',16,1）
rollbacktransaction
end
（5）对PAPER表作插入、修改、删除的多种操作，关注并记录3种触发器的触发情况。
insertintopaper（pno,pna,ppr）values（'000006','报','-1'）
insertintopaper（pno,pna,ppr）values（'000007','丰城报','9.5'）
updatepapersetppr=13.5wherepno='000001'
updatepapersetppr=-10.5wherepno='000001'
以上的代码是对上述创建的触发器的测试，测试结果表明创建的三个触发器都正确，都能根据对表的操作做出相应的触发效果。
（6）创建DDL触发器，通过它能阻止对DingBao数据库表结构的修改或表的删除。
createtriggersafety
ondatabasefordrop_table,alter_table
asprint'你必须失效DLL触发器“safety”后，才能删除或修改数据库表！
'
rollback;
（7）创建与使用DDL触发器：
<1>在jxgl数据库中创建DDL触发器，拒绝对库中表的任何创建、修改或删除操作
createtriggersafety
ondatabaseforcreate_table,drop_table,alter_table
asprint'你必须失效DLL触发器“safety”后，才能创建、删除或修改数据库！
'
<2>在jxgl数据库中创建DDL触发器，记录对数据库的任何DDL操作命令道某表中
createtableddl_log（PostTimedatetime,Db_Usernvarchar（100）,Eventnvarchar（100）,TSQLnvarchar（2000））;
createtrigger[log]ondatabaseforDLL_DATABASE_LEVEL_EVENTSas
declaredataXML
setdata=EVENTDATA（）;
insertdll_log（PostTime,DB_User,Event,TSQl）values（getdate（）,convert（nvarchar（100）,current_user）,data.value（'（/event_instance/EventType[1]','nvarchar（100）'）,
data.value（'（/event_instance/TSQLCommand）[1]','nvarchar（2000）'））;
四、实验体会
通过这次的存储过程和触发器的实验，让我学会了并掌握了存储过程和触发器的创建、修改以及删除等一些基本操作。
也了解到了触发器以及存储过程在数据库中的重要应用，以及它们工作的一些基本原理和过程。
升级会员 