存储过程和处罚期.docx
- 文档编号:16472479
- 上传时间:2023-07-13
- 格式:DOCX
- 页数:13
- 大小:196.52KB
存储过程和处罚期.docx
《存储过程和处罚期.docx》由会员分享,可在线阅读,更多相关《存储过程和处罚期.docx(13页珍藏版)》请在冰点文库上搜索。
存储过程和处罚期
南昌航空大学实验报告
二0一二年五月二十八日
课程名称:
数据库原理实验名称:
存储过程与触发器
班级:
姓名:
仇鑫同组人:
指导教师评定:
签名:
一、实验环境
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)
执行:
executeInsRecToS@sno='S8',@sn='罗兵',@age=18,@dept='信息'
例三:
定义能够返回值的存储过程。
在jxgl数据库中创建一个名称为Query_S的存储过程,该存储过程的功能是从S表中根据学号查询某一学生的姓名和年龄,并返回。
具体实现代码如下:
创建:
createprocedureQuery_S(@snochar(5),@snvarchar(8)output,@agesmallintoutput)
as
select@sn=sn,@age=age
froms
wheresno=@sno
(2)执行存储过程。
Query_S存储过程可以通过以下方法执行
declare@SNvarchar(8),@AGEsmallint
executeQuery_S'S8',@SNoutput,@AGEoutput
select@Sn,@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_Proc@nameVARCHAR(8)
AS
SELECT,,,
FROMPAPER,CP,CUSTOMER
WHEREcna=@nameAND=AND
=;
(6)执行存储过程C_P_Pro,实现对“李涛”、“钱金浩”等不同顾客的订阅信息放入查询。
ExecuteC_P_Proc@name='李涛'
结果如下:
ExecuteC_P_Proc@name='钱金浩'
结果如下:
(7)删除存储过程C_P_Pro。
DROPPROCEDUREC_P_Proc
2、触发器实验部分
(1)创建触发器
例一:
对s表创建update触发器TR_S_Age_update:
createtriggerTR_S_Age_update
ons
forupdateas
declare@iAgeint;
select@iAge=agefrominserted
if@iAge<8or@iAge>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
declare@ictint;
select@ict=ctfrominserted
if@ict<1or@ict>10
begin
raiserror('课程学分应该大于等于,小于等于',16,1)
rollbacktransaction
end
当对c表插入一条记录,如insertintoc(cno,cn,ct)values(‘c8’,’运筹学’,’’),则引发触发器TR_C_insert,取消该记录的插入。
在查询子窗口中,对表s执行修改命令操作时,引发了触发器修改触发器。
还能对表创建delete触发器,如果此表由delete型触发器,则删除记录时触发器将被触发执行。
被删的记录存发在deleted表中,如下是在s表中创建的TR_S_delete触发器:
createtriggerTR_S_deleteons
fordeleteas
declare@icountint;
select@icount=count(*)
fromdeleted,sc
where=
if@icount>=1
begin
raiserror('该学生在表sc中被引用,暂不能被删除!
',16,1)
rollbacktransaction
end
当执行删除命令deletefromswheresno=’s1’时,由于sc表中有对学号为’s1’的学生选课记录,因此删除未能成功。
(3)查看、修改和删除触发器。
<1>修改触发器。
修改触发器TR_S_Age_update:
altertriggerTR_S_Age_update
ons
forupdateas
declare@iAgeint;
select@iAge=age
frominserted
if@iAge<8or@iAge>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
DECLARE@ipprFLOAT;
declare@ipnoint;
SELECT@ippr=ppr,@ipno=pnofrominserted
begin
if@ippr<0or@ipprisNULL
begin
raiserror('报纸的单价为空或小于零!
',16,1)
updatepapersetppr=10
where=@ipno
end
end
<2>对PAPER的删除触发器:
要删除的记录,若正在被订阅表CP参照时,级联删除订阅表中相关的订阅记录。
createtriggerTR_Paper_Donpaper
insteadofdeleteas
declare@icountint;
select@icount=count(*)fromdeleted,Cpwhere=
if@icount>=1
begin
declare@ipnoint
select@ipno=
fromdeleted
deletefromcp
wherepno=@ipno
end
<3>对PAPER的修改触发器:
当报纸的单价修改为负值或为空时,提示“输入单价不正确!
”的信息,并取消修改操作。
createtriggerTR_Paper_Uonpaper
forupdateas
declare@ipprfloat;
select@ippr=pprfrominserted
if@ippr<0or@ipprisnull
begin
raiserror('输入单价不正确!
',16,1)
rollbacktransaction
end
(5)对PAPER表作插入、修改、删除的多种操作,关注并记录3种触发器的触发情况。
insertintopaper(pno,pna,ppr)values('000006','江西报','-1')
insertintopaper(pno,pna,ppr)values('000007','丰城报','')
updatepapersetppr=wherepno='000001'
updatepapersetppr=wherepno='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
declare@dataXML
set@data=EVENTDATA();
insert('(/event_instance/EventType[1]','nvarchar(100)'('(/event_instance/TSQLCommand)[1]','nvarchar(2000)'));
四、实验体会
通过这次的存储过程和触发器的实验,让我学会了并掌握了存储过程和触发器的创建、修改以及删除等一些基本操作。
也了解到了触发器以及存储过程在数据库中的重要应用,以及它们工作的一些基本原理和过程。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 存储 过程 处罚