存储过程和触发器(数据库实验5)
实验5：存储过程和触发器
云南大学软件学院实验报告课程：数据库原理与实用技术实验学期：2014-2015学年第二学期任课教师：薛岗、朱艳萍专业：学号：姓名：成绩：实验5 存储过程和触发器一、实验目的（1）理解存储过程的概念、了解存储过程的类型（2）掌握创建存储过程的方法（3）掌握执行存储过程的方法（4）理解触发器的功能及工作原理。
（5）掌握创建、更改、删除触发器的方法。
二、实验内容1、使用不带参数的存储过程（1）创建一个存储过程，查询person表中所有不重复的职称。
（2）执行存储过程。
2、带输入参数的存储过程（1）创建一个存储过程，按照姓名查询person表中的员工信息。
（2）执行存储过程，查询名为黎明的员工数据3、带输入/输出参数的存储过程（1）创建一个存储过程，使其能够根据员工姓名，查询员工工资。
（考虑到员工不存在时给出提示信息）（2）执行存储过程4、使用触发器（1）创建一个触发器trig_update，返回对person进行更新操作后，被更新的记录条数（2）执行触发器（3）修改触发器trig_update，除返回被更新的记录条数外，再返回学生的所有基本信息5、使用触发器的两个特殊表：插入表（inserted）和删除表（deleted）。
（1）在person上创建触发器ins_del_sample，在插入、删除或更新操作后，分别从inserted表和deleted表中查询员工所有信息。
（请同学们在做删除操作时，注意备份）（2）执行触发器。
思考执行插入、删除和更新操作后返回的表有什么区别？6、使用系统存储过程查看触发器（与存储过程的使用类似）（1）显示触发器trig_update的一般信息（2）显示触发器trig_update的源代码（3）显示person上所有的依赖关系（4）显示触发器trig_update所引用的对象。
实验五 触发器及存储过程的使用
实验五触发器与存储过程
【实验目的】：①掌握触发器的使用
②掌握存储过程的使用
【实验内容】：相关命令写在作业本上。
1、建立银行业务数据库bankdb，其中，帐户信息表(bank)存放帐户的信息，交易信息表(transInfo)存放每次的交易信息。
（1）当向交易信息表(transInfo)中插入一条交易信息时，自动更新对应帐户的余额。
（2）当删除交易信息表时，要求自动备份被删除的数据到表backupTable中。
（3）跟踪用户的交易，交易金额超过20000元，则取消交易，并给出错误提示。
2、在学生成绩数据库中，利用存储过程，查询每门考试的平均分，若平均分大于85分，显示“优秀”，否则显示“较差”，并查询这门课中未通过考试的学生名单。
3、有程序员工资表prowage(id int,panme char(10),wage int)，其中id是程
wage是工资。
创建一个存储过程，对程序员的工资进行分析，如果有百分之五十的人薪水不到2000，给所有人加薪，每次加100，再进行分析，直到有一半以上的人大于2000元为止。
存储过程执行完后，最终加了多少钱？每个人的工资为多少？
思考：如何修改上题的命令创建存储过程，要求查询程序员平均工资在4500元，如果不到，则每个程序员每次加200元，直到所有程序员平均工资达到4500元为止。
【实验答案】：1、（1）命令为：
(2)命令为：
(3)命令为：
2、命令为：
3、命令为：。
实验5 存储过程,函数,触发器操作
实验5 存储过程、函数、触发器操作
已知一个已经建好的数据库factory,现在该数据库上存在三个表
1.职工表worker,其结构为 (职工号 numnber ,姓名 char(8), 性别 char(2) ,
出生日期 date, 党员否 char, 参加工作时间 date , 部门号 number ),其中职工号为主键
2.部门表 department 结构为 ( 部门号 number , 部门名称 varchar(20) ) ,
其中部门号为主键. 通常的部门信息有人事部,市场部,财务处等等
3.职工工资表 salary 其结构为 ( 职工号 number ,姓名 char(10) , 日期
date , 工资 number(10,2) ) . 其中职工号和日期为关键字
在以上的数据库上完成如下操作：
1.创建一个为worker表添加职工记录的存储过程addworker
2.创建一个为给定职工号,查询职工信息及部门名称的存储过程query_worker
3.创建一个为给定职工号,删除worker表中记录的存储过程delete_worker
4.显示存储过程
5.创建一个函数，完成给定职工号返回职工所在部门的名称的函数
6.创建一个函数，完成给定部门号返回该部门的最大年龄的函数
7.在表department上创建一个触发器deaprt_update，当更改部门号时同步更
改worker表中对应的部门号
8.在表worker上创建一个触发器worker_delete，当删除职工记录同步删除对
应职工的工资记录
9.删除两个刚刚建立触发器
以上内容请书写实验报告
余下时间请完成数据库的预备试验。
存储过程和触发器(实验报告)
WITH ENCRYPTION AS
SELECT*
FROM student_info
WHERE性别='男'
EXEC stu_en
DROP PROCEDURE stu_en
4．使用grade表。
（1）创建一个存储过程stu_g_r，当输入一个学生的学号时，通过返回输出参数获取该学生各门课程的平均成绩。
CREATE PROC stu_g_r @stu_no varchar(8)=NULL,
@stu_score real OUTPUT
AS
SELECT@stu_score=AVG(分数)
FROM grade
WHERE (学号=@stu_no)
（2）执行存储过程stu_g_r，输入学号0002。
DECLARE @score real
WHERE (a.姓名=@stu_name)
EXEC stu_g_p ‘刘卫平’
sp_helptext stu_g_p
3．使用student_info表。
（1）创建一个加密的存储过程stu_en，查询所有男学生的信息。
（2）执行存储过程stu_en，查看返回学生的情况。
（3）使用Transact-SQL语句DROP PROCEDURE删除存储过程stu_en。
（3）掌握通过SQL Server管理平台和Transact-SQL语句Alter procedure修改存储过程的方法；
（4）掌握通过SQL Server管理平台和Transact-SQL语句Drop procedure删除存储过程的方法；
（5）掌握通过SQL Server管理平台和Transact-SQL语句Create trigger创建触发器的方法和步骤；
实验五触发器与存储过程的设计-西安理工大学
实验五触发器与存储过程的设计-西安理工大学实验五触发器与存储过程的设计实验目的1、掌握创建存储过程的方法和步骤。
2、掌握存储过程的使用方法。
3、掌握创建触发器的方法和步骤。
4、掌握触发器的使用方法。
实验内容、要求以教学管理系统为例，有Student、SC和Course表，根据数据库的完整性要求，自己设计一个存储过程和触发器，当删除SC表的某条记录时，在屏幕上给出提示，当删除该条记录后，若没有学生选择此门课时，要求将Course表中对应的记录删除。
实验环境Microsoft Word, SQL Server 2000环境。
实验过程一、存储过程的设计1、有关概念存储过程是由SQL语句及控制流语句组成的集合。
调用一个存储过程，可以一次性地执行过程中的所有语句。
从这一点来说，它类似于程序。
存储过程由用户建立，它作为数据库的一个成分，存在于数据库中。
存储过程类似VFP中的过程（函数、子程序），它可以接受参数，也可以返回参数。
存储过程可以被客户端、其他存储过程或触发器调用。
以SP_为前缀的存储过程是SQL提供的系统存储过程; 以XP_为前缀的存储过程是扩展的存储过程; 关联到表上的存储过程称为触发式存储过程。
2、设计存储过程使用SQL语句创建存储过程（或使用企业管理器创建存储过程）。
3、执行存储过程对存储在服务器上的存储过程，使用EXECUTE命令执行它。
4、操作存储过程查看、修改、删除存储过程。
二、触发器的设计触发器是一种特殊的存储过程, 用它来控制关联的表。
1、设计一个触发器使用SQL语句创建触发器（使用企业管理器创建触发器）。
2、查看、修改和删除触发器实验六图书管理系统设计及SQL编程实验目的初步掌握数据库系统的开发过程实验内容1.根据数据库设计的基本思想，设计出图书管理系统的概念结构（用E-R图表示）。
2.根据E-R图进行数据库的逻辑设计。
3.在逻辑设计的基础上，进行数据库的物理设计。
4.给出各设计阶段的完整文档（E-R图、逻辑模型、数据字典）。
实验五触发器和存储过程
实验五：触发器和存储过程一、实验目的：理解触发器和存储过程的含义，掌握用SQL语句实现触发器和存储过程的编写，并初步掌握什么情况下使用事务。
二、预习要求：存储过程和触发器的相关概念，事务的相关概念，编写相应的SQL语句。
三、实验内容：（一）为S表的删除操作定义一个触发器，在删除一个供应商记录时，将这个供应商的所有供应情况从spj表中删除。
（二）有一个小型的图书管理数据库，包含的表为：bookstore（bookid，bookname，bookauthor，purchasedate，state）；--图书库存表borrowcard（cardid，ownername）；--借书证表borrowlog（cardid，bookid，borrowdate，returndate）；--借书记录表写一个存储过程，实现借书操作，要求有事务处理。
（1）读者借书，要先设置书籍不在库标志state（借出），然后增加借书记录，在同一事务中完成。
（2）要求在事务执行过程中引入错误触发事件，以此体会事务的错误保护机制和事务编程的作用。
（三）完成情况（附上设计的SQL语句）。
建立数据库：create database bookstoruse bookstorcreate table bookstore(bookid int,bookname char(20),bookauthor char(20),purchasedate char(20),stat char(6),primary key(bookid),Check(stat in('在库','不在库')),);create table borrowcard(cardid int primary key,ownername char(20),);create table borrowlog(cardid int,bookid int,borrowdate char(20),returndate char(20),primary key(cardid,bookid),foreign key(cardid)references borrowcard(cardid),foreign key(bookid)references bookstore(bookid),);存储过程：create procedure [dbo].[jieshu](@cardid int,@bookid int,@borrowdate char(20),@returndate char(20))asbegin transactioninsertinto borrowlogvalues(@cardid ,@bookid ,@borrowdate ,@returndate )if exists(select * from bookstore,borrowcard where bookid=@bookid and stat='在库'and cardid=@cardid)beginupdate bookstoreset stat='不在库'where bookid=@bookidcommit transactionendelsebeginif not exists(select*from bookstore where bookid=@bookid)print'不存在该书'if not exists(select*from borrowcard where cardid=@cardid)print'没有此用户'rollback transactionEnd查询语句：exec jieshu 1002062,1,'20121103','2012124'结果：触发器:create trigger storon borrowlogafter insertasif(new.borrowdate>new.returndate)print'失败，还书时间不应早于结束时间'beginrollback transactionend四、实验方法和步骤：（一）在查询分析器中编写实现上述功能的触发器，参考Create Trigger语法。
存储过程及触发器实验报告
数据库技术与应用实验报告七班级：机械因材学号： 16 姓名：高永吉一：实验名称：存储过程及触发器二，实验目的：⑴ 使用系统常用的存储过程；⑵ 掌握存储过程的创建及应用(3) 理解触发器的概念；(4) 掌握触发器的创建及应用。
三．实验内容、过程和结果：存储过程1创建一个存储过程，查看学号为1（根据实际情况取）的学生的信息，包括该学生的学号，班级编号，姓名。
（提示：查询涉及到表Student）2执行1中创建的存储过程。
3使用输入参数创建题1中的存储过程。
题1中所创建的存储过程只能学号为1的学生信息进行查看，要想对其他学生进行查看，需要进行参数传递。
4执行3中创建的存储过程，（1）按位置传递参数；（2）通过参数名传递参数；5触发器1）在课程表Course上创建一个触发器，该触发器被操作DELETE所触发，且要求触发触发器的DELETE语句在执行被取消。
2）在表Student中建立插入触发器, 插入一条记录时，若年龄>100或者年龄<=0,拒绝插入记录并显示：“年龄不符合规定，无法插入此记录！”；3）创建一个触发器，如果在Student表中添加或更改数据，向客户端显示一条消息“你正在插入或修改学生表的数据”，要求触发触发器的DELETE、UPDATE语句被执行。
4 ）为Course表创建一个名称为my_trig的触发器，当用户成功删除该表中的一条或多条记录时，触发器自动删除Student表中与之有关的记录。
5 ）使用系统存储过程查看创建的触发器。
图一：创建一个存储过程，查看Tno为1（根据实际情况取）的教师的信息，包括该教师的姓名，sal图二执行1中创建的存储过程。
图三使用输入参数创建题1中的存储过程。
图四执行3中创建的存储过程，（按位置传递参数）图五执行3中创建的存储过程通过参数名传递参数；图六使用系统存储过程查看3中创建的存储过程图七删除3中创建的存储过程。
图八在Teacher上创建一个触发器，该触发器被操作DELETE所触发，且要求触发触发器的DELETE语句在执行被取消。
实验五 存储过程和触发器的使用
实验五存储过程和触发器的使用【目的要求】1、了解存储过程的基本概念和类型。
2、了解创建存储过程的T-SQL语句的基本语法。
3、了解查看、执行、修改和删除存储过程的T-SQL命令的用法。
4、了解触发器的基本概念和类型。
5、了解创建触发器的T-SQL语句的基本语法。
6、了解查看、修改和删除存储过程的T-SQL命令的用法。
【实验内容】内容一：存储过程的使用一、数据需求分析存储过程是一种数据库对象，为了实现某个特定任务，将一组预编译的SQL语句以一个存储单元的形式存储在服务器上，供用户调用，自动完成需要预先执行的任务。
存储过程在第一次执行时进行编译，然后将编译好的代码保存在高速缓存中便于以后调用，提高了代码的执行效率。
二、内容要点分析1、SQL SERVER支持五种类型的存储过程：系统存储过程、本地存储过程、临时存储过程、远程存储过程和扩展存储过程。
其中，系统存储过程是由系统提供的存储过程，可以作为命令执行各种操作。
系统存储过程定义在系统数据库master中，其前缀是sp_。
本地存储过程是指在用户数据库中创建的存储过程，这种存储过程完成特定数据库操作任务，不能以sp_为前缀。
2、只能在当前数据库中创建存储过程。
3、创建存储过程时，应指定所有输入参数和向调用过程或批处理返回的输出参数、执行数据库操作的编程语句和返回至调用过程或批处理以表明成功或失败的状态值。
4、创建存储过程的T-SQL语句CREATE PROC[EDURE] 存储过程名称[{ @参数名称数据类型 }] [,…n][WITH{ RECOMPILE|ENCRYPTION }]ASSQL语句序列说明：（1）RECOMPILE表明每次运行该过程时，将其重新编译。
（2）ENCRYPTION表示 SQL SERVER 加密SYSCOMMENTS表中包含CREATE PROCEDURE语句文本的条目。
注：必须将CREATE PROCEDURE语句放在单个批处理中。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
数据库基础与实践实验报告实验五存储过程和触发器班级：惠普测试142学号：**********姓名：***日期：2016-11-141 实验目的：1)掌握SQL进行存储过程创建和调用的方法；2)掌握SQL进行触发器定义的方法，理解触发器的工作原理；3)掌握触发器禁用和重新启用的方法。
2 实验平台：操作系统：Windows xp。
实验环境：SQL Server 2000以上版本。
3 实验内容与步骤利用实验一创建的sch_id数据库完成下列实验内容。
1.创建存储过程JSXX_PROC，调用该存储过程时可显示各任课教师姓名及其所教课程名称。
存储过程定义代码：CREATE PROCEDURE JSXX_PROCASSELECT tn 教师姓名,cn 所教课程FROM T,TC,C WHERE T.tno=TC.tno AND o=o存储过程执行语句与执行结果截图：EXECUTE JSXX_PROC2.创建存储过程XM_PROC，该存储过程可根据输入参数（学生姓名）查询并显示该学生的学号、所学课程名称和成绩；如果没有该姓名学生，则提示“无该姓名的同学”。
存储过程定义代码：CREATE PROCEDURE XM_PROC @sname VARCHAR(100)ASBEGINIF EXISTS(SELECT NULL FROM S WHERE sn=@sname)SELECT S.sno 学号,cn 课程,score 成绩FROM S,SC,C WHERE o=o AND SC.sno=S.sno ANDS.sn=@snameELSEPRINT'无该姓名的同学。
'END运行截图：3.创建存储过程XBNL_PROC，该存储过程可根据输入参数（专业名词，默认值为计算机专业），统计并显示该专业各年龄段男、女生人数。
如果没有该专业，则显示“无此专业”。
存储过程定义代码：CREATE PROCEDURE XBNL_PROC@departName VARCHAR(30)='计算机',@begin INT,@end INTASDECLARE @numOfBoys INTDECLARE @numOfGirls INTDECLARE @d# VARCHAR(3)DECLARE @result VARCHAR(50)BEGINSELECT @d# = dno FROM D WHERE dn=@departNameIF @d# IS NOT NULLBEGINSELECT @numOfBoys =COUNT(sno)FROM S WHERE age BETWEEN @begin AND @end AND dno=@d# AND sex='男'SELECT@numOfGirls =COUNT(sno)FROM S WHERE age BETWEEN@begin AND@end AND dno=@d# AND sex='女'SET @result = @departName+'专业年龄在'+CAST(@begin AS VARCHAR(3))+'-'+CAST(@end AS VARCHAR(3))+'之间的男生有'+CAST(@numOfBoys AS VARCHAR(3))+'人，'+'女生有'+CAST(@numOfGirls AS VARCHAR(3))+'人'ENDELSESET @result='无此专业。
'PRINT @resultEND执行结果：4.执行XM_PROC存储过程，查询“贾慧”同学的学号、所学课程名称和成绩。
存储过程调用语句及其执行结果截图：EXEC XM_PROC'贾慧'5.如果学生表中无“贾慧”同学，则另查询一位学生表中出现过的学生姓名；如果学生表中有“贾慧”同学，则另查询一位学生表中没有的学生姓名。
存储过程调用语句及其执行结果截图：EXEC XM_PROC'张明'6.执行XBNL_PROC存储过程，查询计算机专业各年龄的男、女生人数。
（要求计算机专业必须有至少3个年龄段的男女学生）存储过程调用语句及其执行结果截图：EXEC XBNL_PROC@begin=21,@end=25 --默认：计算机EXEC XBNL_PROC'计算机',18,25EXEC XBNL_PROC'计算机',23,25EXEC XBNL_PROC'计算机',20,217.执行XBNL_PROC存储过程，输入一个专业表中没有的专业。
存储过程调用语句及其执行结果截图：EXEC XBNL_PROC'音乐',18,258.分别查看XBNL_PROC存储过程的一般信息。
执行语句及结果截图：EXEC sp_help XM_PROC9.删除XM_PROC存储过程。
执行语句及结果截图：DROP PROC XM_PROC10.创建班级表B(bno,bn,num)，三个属性分别表示班号，班名，人数；在学生S表中增加一个属性班号。
表创建语句：--创建班级表：CREATE TABLE B(bno VARCHAR(3)PRIMARY KEY,bn VARCHAR(20)UNIQUE NOT NULL,num INT)--S表添加班级属性：ALTER TABLE S ADD bno VARCHAR(3)执行结果截图：11.创建触发器T_insertS，当向S表添加记录时，验证学生的班级号是否出现在B表中，如果不是则撤销S表添加记录的操作；，如果学生的班号在班级表中则自动修改相应班级的人数字段值。
触发器创建语句：CREATE TRIGGER T_insertSON SFOR INSERTASBEGINDECLARE @bno VARCHAR(3)DECLARE @sno VARCHAR(10)SELECT @bno=bno FROM insertedSELECT @sno=sno FROM insertedIF EXISTS(SELECT NULL FROM B WHERE bno=@bno)BEGINUPDATE B SET num=num+1 WHERE bno=@bnoPRINT'添加成功！'ENDELSEBEGINDELETE FROM S WHERE sno=@snoPRINT'添加失败！'ENDEND向S表中插入一位学生，其班号不在B表中。
测试触发器的功能截图：插入记录前查询：SELECT*FROM S WHERE sn='翠翠'插入记录：INSERT INTO S VALUES('S19','翠翠','女',18,'D2',3)插入后查询：SELECT*FROM S WHERE sn='翠翠'向S表中插入一位学生，其班号在B表出现过。
测试触发器的功能截图：插入前查询：SELECT*FROM S WHERE sn='翠翠'插入记录：INSERT INTO S VALUES('S19','翠翠','女',18,'D2',1)插入后查询：SELECT*FROM S WHERE sn='翠翠'禁用T_insertS触发器语句；ALTER TABLE S DISABLE TRIGGER T_insertS向S表中插入一位学生，其班号不在B表中。
测试触发器的功能截图：INSERT INTO S VALUES('S20','翠花','女',18,'D2',3)SELECT*FROM S WHERE sn='翠花'启用T_insertS触发器语句；ALTER TABLE S ENABLE TRIGGER T_insertS向S表中插入一位学生，其班号不在B表中。
测试触发器的功能截图：INSERT INTO S VALUES('S21','翠花儿','女',19,'D2',3)SELECT*FROM S WHERE sn='翠花儿'12.创建触发器T_updateS，当修改S表一位同学的班级字段值时，验证学生的班级号是否出现在B表中，如果不是则撤销对S表中该条记录的修改操作，如果修改后的班号在班级表中则自动修改相应班级的人数字段值。
触发器创建语句：CREATE TRIGGER T_updateSON SFOR UPDATEASBEGINDECLARE @b# VARCHAR(3)DECLARE @b#old VARCHAR(3)DECLARE @numOld INTSELECT @b#=bno FROM insertedSELECT @b#old=bno FROM deletedSELECT @numOld=num FROM B WHERE bno=@b#oldIF EXISTS(SELECT NULL FROM B WHERE bno=@b#)BEGINIF (@numOld IS NOT NULL)BEGINUPDATE B SET num=num+1 WHERE bno=@b#UPDATE B SET num=num-1 WHERE bno=@b#oldENDELSEUPDATE B SET num=num+1 WHERE bno=@b#PRINT'更新成功!'ENDELSEBEGINUPDATE S SET bno=@b#old WHERE bno=@b#PRINT'更新失败!'ENDEND修改S表中一位学生的班级号，修改后的班号不在B表中。
测试触发器的功能截图：UPDATE S SET bno='88'WHERE sno='S1'修改S表中一位学生的班级号，修改后的班号在B表中。
测试触发器的功能截图：SELECT sno AS'学号',bno AS'班级编号'FROM S WHERE sno='S18'SELECT bno '班级编号',num AS'班级人数'FROM BUPDATE S SET bno='8'WHERE sno='S18'SELECT sno AS'学号',bno AS'班级编号'FROM S WHERE sno='S18'SELECT bno '班级编号',num AS'班级人数'FROM B4 深入思考与讨论1）请按自己的理解，说明一下触发器的工作原理。