存储过程和处罚期.docx
- 文档编号:5544921
- 上传时间:2022-12-19
- 格式:DOCX
- 页数:9
- 大小:77.81KB
存储过程和处罚期.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)'));
四、实验体会
通过这次的存储过程和触发器的实验,让我学会了并掌握了存储过程和触发器的创建、修改以及删除等一些基本操作。
也了解到了触发器以及存储过程在数据库中的重要应用,以及它们工作的一些基本原理和过程。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 存储 过程 处罚
![提示](https://static.bdocx.com/images/bang_tan.gif)