存储过程和处罚期

1、例一：在jxgl数据库中，创建一个名称为Select_S的存储过程，该存储过南昌航空大学实验报告二0 一二年五月二十八日课程名称：数据库原理实验名称:存储过程与触发器班级:10202225 姓名： 仇鑫同组人:签名:指导教师评定:、实验环境1. Windows2000或以上版本;2. SQLServer2000 或 2005。二、实验目的1 .掌握存储过程的创建、修改、使用、删除。2 .掌握触发器的创建、修改、使用、删除。三、实验步骤及参考源代码1、存储过程实验部分(1 )创建存储过程。程，该存储过程的功能是从数据库表S中查询所有女同学的信息，并执行该存储过程。具体实现的代码如下: 仓y建：c

2、reate procedure Select_S as select * from s where sex = '女'执行：execute Select_S例二：定义具有参数的存储过程。在jxgl数据库中，创建一个名称为S表中插入一条数据， 新纪录的值由参数提供，如果未提供的值给sex时，由参数的默认值代替。InsRecTos的存储过程，该存储过程的功能是向具体实现代码如下:仓J建：create procedure InsRecToS (sno char(5),sn varchar (8),sex char (2)='男',age int ,dept varch

3、ar (20) as in sert in to S values (sno ,sn ,sex ,age ,dept )执行: execute In sRecToSs no ='S8',s n ='罗 兵',age = 18,de pt ='信息'例三：定义能够返回值的存储过程。在jxgl数据库中创建一个名称为Query_S的存储过程，该存储过程的功能是从S表中根据学号查询某一学生的姓名和年龄，并返回。具体实现代码如下:仓J建：create procedure Query_S (sno char(5),sn varchar (8) out put

4、 ,age smalli nt out put ) as select sn = sn,age = age from s where sno = sno(2 )执行存储过程。Query_S存储过程可以通过以下方法执行declare SN varchar (8),AGE smallint execute Query_S 'S8' ,SN out put ,AGE out put select Sn,AGE(3 )查看和修改存储过程。使用alter procedure 命令，修改存储过程InsRecToS，代码如下:alter procedure InsRecToS (sno ch

5、ar(5),sn varchar (8),sex char (2)='女',age smallint ,dept varchar (20) as insert into s values (sno ,sn ,sex ,age ,dept )(4)查看、重命名和删除存储过程例一：查看数据库表s中存储过程Select_S的源代码exec sp_helptext Select_S例二：将存储过程Select_S改名为Select_Student 。sp_rename Select_S ,Select_Student例三：将存储过程Select Student 从数据库中删除。drop

6、 procedure Select_Student(5) 在DingBao数据库中创建存储过程 C_P_Proc ,实现参数化查询顾客订阅信息，查询参数为顾客姓名， 要求能查询出参数指定的顾客编号、顾客 名、订阅报纸名及订阅份数等信息。CREATE PROCEDURE C_P_Proc name VARCHAR (8)ASSELECT CUSTOMER .cna,CUSTOMER .cno,PAPER.pna ,CP.numFROM PAPER,CP,CUSTOMERWHERE cna=n ame AND CUSTOMER .c no = CP.cno ANDPAP ER. pno =CP.p

7、no ;口 U DingE白&国Sa表Ed视圏E 3闫义伺曰立可編程性0辛和过H0之就存删程E 圍 dbo.C_P_Proc(6) 执行存储过程 C_P_Pro ,实现对“李涛”、“钱金浩”等不同顾客的订 阅信息放入查询。Execute C_P_Proc name ='李涛'结果如下:Execute C_P_Proc name ='钱金浩' 结果如下:(7) 删除存储过程C_P_Pro。DROP PROCEDURE C P Proc2、触发器实验部分(1) 创建触发器例一：对 s 表创建 up date 触发器 TR_S_Age_u pdate : cr

8、eate trigger TR_S_Age_ up date on s for up date asdeclare iAge int ;select iAge =age from insertedif iAge <8 or iAge >45beg inraiserror （'学生年龄应该大于等于，并小于等于,16,1）rollback transactionend当对s做up date操作时，会自动触发 TR_S_Age_u pdate 触发器，若入学日期与出生日期年份相差小于8或大于45时，则取消该次修改操作。例二：创建一个触发器，当向s表中更新一条记录时， 会自动显示s

9、表中的记录:create triggerCha nge_S_Selon s forinsert ,update,delete asselect * from（2）触发器的引用（使用）。类似地对课程c表创建插入触发器 TR_C_insert : create trigger TR_C_insert on c for insert asdeclare ict int;select ict = ct from insertedif ict < 1 or ict > 10beginraiserror （'课程学分应该大于等于，小于等于,16,1）rollback transacti

10、onend当对 c 表插入一条记录，女0insert into c(cno,cn,ct)values('c8' /运筹学,'0.5 '),则引发触发器 TR_C_insert，取消该记录的插入。在查询子窗口中，对表s执行修改命令操作时，引发了触发器修改触发器。还能对表创建delete触发器，如果此表由delete型触发器，则删除记录时触发器将被触发执行。被删的记录存发在deleted 表中，如下是在 s表中创建的TR_S_delete触发器: create trigger TR_S_delete on s for delete asdeclare icount

11、int ;select icount = count (*)from deleted ,scwhere deleted .sno = sc.snoif icount >= 1beginraiserror ('该学生在表sc中被引用，暂不能被删除！ ,16,1) rollback transactionend当执行删除命令 delete from s where sno= ' s1 '时，由于sc表中有对学 号为s1'的学生选课记录，因此删除未能成功。(3) 查看、修改和删除触发器。<1>修改触发器。修改触发器 TR_S_Age_update :

12、 alter trigger TR_S_Age_ up date on sfor up date as declare iAge int;select iAge =age from inserted if iAge < 8 or iAge > 45 beg inraiserror （'学生年龄应该大于等于，并小于等于,16,1）rollback transaction end<2>使用系统存储过程查看触发器。例一：查看已建立Change_S_SeI触发器所涉及的表:sp_depends 'Cha nge_S_Se l'例二：查看已建立的Cha n

13、ge_S_Sel的命令文本:sp_help text 'Cha nge_S_se l'例题三：查看已建立的Change_S_Sel触发器。exec sp_help 'Change_S_Sel'<3>删除触发器。删除前面创建的触发器 TR_S_Age_u pdate : drop trigger TR_S_Age_ up date(4) 在DingBao 数据库中针对 PAPER创建插入触发器 TR_PAPER_I、删除触发器TR_PAPER_D、修改触发器TR_PAPER_U。具体要求如下。<1>对PAPER的插入触发器：插入报纸记录，单

14、价为负值或为空时，设定为10元。ON paper=pno from insertedCREATE TRIGGER TR PAPER IFOR INSERT ASDECLARE ippr FLOAT ;declare ipno int ;SELECT ippr = ppr ,ipnobeginif ippr <0 or ippr is NULLbegi nraiserror ('报纸的单价为空或小于零!',16,1)up date paper set ppr =10where paper .pno =ipnoendend<2>对PAPER的删除触发器：要删除的记

15、录,若正在被订阅表CP参照时，级联删除订阅表中相关的订阅记录。create trigger TR_Paper_D on paperin stead of delete asdeclare icount int ;select icount = count (*) from deleted ,Cp wheredeleted .pno = Cp .pnoif icount >= 1begindeclare ipno intselect ipno = deleted .pnofrom deleteddelete from cpwhere pno = ipnoend<3>对PAPER

16、的修改触发器：当报纸的单价修改为负值或为空时，提示“输 入单价不正确！ ”的信息，并取消修改操作。create trigger TR_Paper_U on paper for up date asdeclare ippr float ;select ippr = ppr from insertedif ippr <0 or ippr is nullbegi nraiserror （'输入单价不正确！' ,16,1）rollbacktran sacti onendFi IJ DinjBao壬LJ数据库关靠E1®0B-二奏 二j乗緬表 3 dbft ep|±

17、;(+J dU .pap er口龍涼rii._raper_D f7l rK_FAFEE_I SI ni._rapeT_U(5 )对PAPER表作插入、修改、删除的多种操作,关注并记录3种触发江西报','-1')丰城报,95')器的触发情况。in sert into paper(pno,pna,pp r)values('000006','in sert into paper(pno,pna,pp r)values('000007','up date paper set pp r=13.5 where pno='

18、;000001'up date paper set pp r=-10.5 where pno='000001'以上的代码是对上述创建的触发器的测试，测试结果表明创建的三个触发器 都正确，都能根据对表的操作做出相应的触发效果。(6)创建DDL触发器，通过它能阻止对DingBao数据库表结构的修改或表的删除。create trigger safety on database for drop_table ,alter_tableas print '你必须失效DLL触发器“ safety ”后，才能删除或修改数据库表！rollback ;(7)创建与使用 DDL触发器:<1>在jxgl数据库中创建 DDL触发器，拒绝对库中表的任何创建、修改或 删除操作 create trigger safety on database for create_table ,drop_table ,alter_tableas print '你必须失效DLL触发器“ safety ”后，才能创建、删除或修改数据库