实验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、PLSQL创建储存过程编译出错不会给出错误提示，导致调用时提示储存过程处于无效状态。

解决方案：使用SQLPLUS，不过SQLPLUS只会提示编译错误，不会提示具体原因，还可以使用Navicat工具，Navicat会给出更加详细的错误原因。

2、创建储存过程时，设置变量参数类型时，指定了字符长度导致创建失败。

解决方案：直接设置变量数据类型，不设置其字符长度。

3、使用TO_DATE(SYSDATE,‘YYYY/MM/DD’)获取当前日期作为借阅日期导致调用借书储存过程失败，提示参数类型错误。

解决方案：因为TO_DATE()函数是将字符类型转换成日期类型，而SYSDATE本来就是日期类型，所以导致调用失败，使用TO_DATE(TO_CHAR(SYSDATE,‘YYYY/MM/DD’),‘YYYY/MM/DD ’)将SYSDATE转换成字符类型再转换成日期类型。

4、使用DBMS_OUTPUT.PUT_LINE()函数输出提示，没有反应。

解决方案：在SQLPLUS中需要先使用SET SERVEROUTPUT ON;打开输出模式才能看见输出，而在PLSQL中输出的内容在另一个Output窗口中，而不是没有反应。

5、创建自动递增借阅流水号的触发器时，使用NEW关键字改变借阅流水号，导致创建触发器失败，解决方案：使用NEW关键字时，需要在前面加一个“:”号，如“:NEW.借阅流水号”。

6、调用修改后的借书储存过程时，发送错误，提示违反唯一约束条件以及COMMIT;不能再触发器中使用。

解决方案：删除在触发器中的COMMIT;，然后删除序列“借阅流水号序列”，重新创建序列“借阅流水号序列”，并且设置初始值为8，因为借阅表中已经有借阅流水号1到7的数据了，然后创建序列时未指定初始值，序列默认从1开始，导致违反唯一约束条件，从而导致调用储存过程失败。

四、实验心得体会通过本次实验，学会了储存过程以及触发器和序列的使用方法，对存储过程有了一个直观的认识，对触发器的工作原理和作用有了更加深刻的认识，使用触发器可以在修改数据前后规范数据，使数据规范化和标准化。

数据库基础与实践实验报告实验五存储过程和触发器班级：惠普测试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='无此专业。

实验报告课程名称：数据库原理与应用上机实验名称：存储过程和触发器专业班级：计算机科学与技术1103 指导教师：卫凡学生姓名：贾梦洁学期：2013-2014学年第一学期实验报告课程名称数据库原理与应用实验名称存储过程和触发器姓名贾梦洁学号 201107010330专业班级计1103实验日期2013年12月5日成绩指导教师卫凡一、实验目的1.加深和巩固对存储过程和触发器概念的理解。

2. 掌握触发器的简单应用。

3. 掌握存储过程的简单应用。

二、实验环境硬件环境：PC机软件环境：操作系统为Microsoft Windows 2000或以上版本。

数据库管理系统为Microsoft SQL Server 2000标准版或企业版。

三、实验内容1. 熟悉运用SQL Server企业管理器和查询分析器进行存储过程的创建和删除。

2. 熟悉运用SQL Server企业管理器和查询分析器进行触发器的创建和删除。

四、实验步骤1.建立存储过程class_info ,当执行该过程时，只要给出学生的姓名，就能查到他们的班级名称。

使用存储过程class_info查找学生“张强”的信息。

2.删除存储过程 class_info3.使用触发器实现S,SC表的级联删除删除前：删除后：4.在数据库中创建一个触发器，当向S表中插入一条记录时，检查该记录的学号在S表中是否存在，如果有则不允许插入。

5.创建基于学生表的插入触发器,当向学生表插入一条记录时,返回一条信息:“欢迎新同学”。

6.为S表创建触发器s_insert,当向S表中插入数据时,要求学号必须以"2002"开头，否则取消插入操作。

五、实验总结这次的实验总体来说较前两次简单，因为很多题目都是书上的例题。

不过通过这次实验，也把上课没有认真听讲的部分给补上了。

至少让我不要在见到这些题目的时候觉得陌生，我觉得对我的数据库期末考试是有一定帮助的。

虽然数据库对我来说真的好苦手，但是我相信只要努力了总能做到的。

实验五存储过程和触发器（计科）实验五：触发器和存储过程一、实验目的：理解触发器和存储过程的含义，掌握用SQL语句实现触发器和存储过程的编写，并初步掌握什么情况下使用事务。

二、预习要求：存储过程和触发器的相关概念，事务的相关概念，编写相应的SQL语句。

三、实验内容：有一个小型的图书管理数据库，包含的表为：bookstore（bookid，bookname，bookauthor，purchasedate，state）；--图书库存表borrowcard（cardid，ownername）；--借书证表borrowlog（cardid，bookid，borrowdate，returndate）；--借书记录表写一个存储过程，实现借书操作，要求有事务处理。

（1）读者借书，要先设置书籍不在库标志state（借出），然后增加借书记录，在同一事务中完成。

（2）要求在事务执行过程中引入错误触发事件，以此体会事务的错误保护机制和事务编程的作用。

（3）要求用触发器实现表的完整性控制。

四、完成情况（附上设计的SQL语句）。

------触发器和存储过程------触发器和存储过程CREATE DATABASE BOOK1gouse BOOK1CREATE TABLE BOOKSTORE(Bookid nvarchar(10),Bookname nvarchar(10),Bookauthor nvarchar(10),purchasedate datetime,state Nvarchar(10),primary key(Bookid))Create table Borrowcard(Cardid int,ownernamenvarchar(10),primarykey(Cardid))Create table Borrowlog(Cardid int,Bookid nvarchar(10),borrowdate datetime,returndate datetime ,primary key(Cardid,Bookid))insert into Borrowcard values(12,'wyb')insert into Borrowcard values(123,'wyb')insert into Borrowcard values(1,'wyb')insert into Bookstore values(1,'数据库','王珊','2012-04-23','存在') insert into Bookstore values(2,'数据结构','珊','2012-11-23','存在') insert into Bookstore values(3,'数据结构','珊','2011-1-23','存在') insert into Bookstore values(11,'数据库','王珊','2009-10-23','存在') insert into Bookstore values(12,'数据结构','珊','2001-11-23','存在') insert into Bookstore values(13,'数据结构','珊','2013-12-23','存在')----借书存储过程create proc borrow1@bookid nvarchar(10),@cardid intasbegin transactioninsert into Borrowlog(Cardid,Bookid,borrowdate,returndate) values(@cardid,@bookid,getdate(),null)if exists(select*from bookstore,Borrowcard where bookid=@bookid and state='存在'and cardid=@cardid) beginupdate bookstore set state='不存在'where bookid=@bookid commit transactionendelsebeginif exists(select*from bookstore where bookid=@bookid and state='不存在')Print'不存在该书'if not exists(select*from borrowcard,bookstore where cardid=@cardid) Print'没有此用户'rollback transactionEndexec borrow1'3',12exec borrow1'3',123exec borrow1'2',123drop proc borrow1----删除存储过程Create trigger insert_borrowlogon Borrowlog after insertasdeclare@borrowdate datetime,@returndate datetimeselect@borrowdate=borrowdate,@returndate=returndate from insertedif(@borrowdate>@returndate)print'借书时间不可晚于还书时间'rollbackselect*from bookstoreinsert into Borrowlog values(12,'1','2013-06-06','2012-05-5')五、思考题：如何通过系统的设置实现类似的功能，而不需触发器？答：通过定义存储过程或者设置外键约束等方法。

实验五存储过程和触发器的使用【目的要求】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．通过对常用系统存储过程的使用，了解存储过程的类型；通过创建和执行存储过程，了解存储过程的基本概念，掌握使用企业管理器及查询分析器执行T-SQL语句创建存储过程。

2．通过创建触发器，了解触发器的基本概念，理解触发器的功能，掌握使用企业管理器及查询分析器执行T-SQL语句创建触发器。

二、实验要求
1．实验前做好上机实验的准备，针对实验内容，认真复习与本次实验有关的知识，完成实验内容的预习准备工作；
2．能认真独立完成实验内容；
3．实验后做好实验总结，根据实验情况完成总结报告。

三、实验学时
2学时
四、实验内容
1、使用sp_helptext查看byroyalty存储过程的文本，该存储过程在数据库pubs中。

2、在实验二创建的factory数据库中执行以下操作：
（1）创建一个为worker表添加职工记录的存储过程addworker。

执行并验证存储过程的正确性。

最后删除该存储过程。

（2）在depart表上创建一个触发器depart_update，当更改部门号时同步更改worker 表中对应的部门号。

执行并验证触发器的正确性。

最后删除该触发器。

（3）在worker表上创建一个触发器worker_delete，当删除职工记录时同步删除salary表中对应职工的工资记录。

执行并验证触发器的正确性。

最后删除该触发器。

实验5：存储过程、触发器和视图第五周实验可编程对象（视图、存储过程和触发器）一．实验目的1．了解视图、存储过程和触发器的基本概念和使用方法。

2．学会用两种方法创建和维护视图、存储过程和触发器等数据库对象：一是在SQL Server Management Studio通过可视化操作实现，一是在查询窗口执行相关T-SQL语句实现。

二．实验环境●SQL Server Management Studio●BookStore数据库提示1：到“课程辅助材料”中下载BookStore数据库，在SQL Server中附加。

三．实验内容说明：标记为▲的是必做题目，其他为选作题目。

首先需要附加BookStore数据库。

1▲．创建视图。

（1）创建视图V_BookSell，使其包含图书销量情况。

要求显示图书代码（BookCode）、图书名称（BookName）、作者（Author）、出版社名称（Publisher）以及数量（Amount）。

（2）创建视图V_CustomerBookOrderDetail。

要求显示订单号（OrderCode）、客户名（Name）、客户等级（VIPClass）、书名（BookName）、单价（Price）、数量（Amount）、折扣（Discount）以及总价（TotalPrice=Price*Amount*Discount）。

（3）创建视图V_CustomerVIPABTotalOrder，汇总客户订单信息。

使其包含用户等级为“A”和“B”、且不姓“郭”和“刘”的客户订单信息，要求显示客户姓名（Name）以及所订图书总金额，并按所订图书总金额降序排列。

2．创建存储过程（1）▲创建存储过程proc_SearchBook，查询指定书名的图书信息。

（2）创建存储过程proc_FuzzySearchBook，实现按书名（全名或部分书名）模糊查询图书信息。

（3）创建存储过程proc_SearchCustomerMoney，查询指定客户在某一年之前的购书总金额（已知客户号和年份，输出总金额）。

实验五存储过程和触发器的定义和使用实验内容：1.在学生成绩数据库中建立表，并相应的导入数据create database学生成绩use学生成绩create table student(学号char(6)not null,姓名char(8)not null,性别bit not null,出生日期smalldatetime,专业名char(10),所在系char(10),联系电话char(11)null)create table course(课程号char(3)not null,课程名char(20)not null,教师char(10),开课学期tinyint,学时tinyint,学分tinyint not null)create table sc(学号char(6)not null,课程号char(3)not null,成绩smallint)2.（1）全局变量（2）if语句①②（3）循环语句①declare@i int,@sum int,@count intset@i=1set@sum=0set@count=0while (@i<100)beginif(@i%3=0)beginset@sum=@sum+@iset@count=@count+1endset@i=@i+1endprint'总和为:'+convert(varchar(10),@sum)print'个数为:'+convert(varchar(10),@count)②declare@i1int,@s1intset@i1=1set@s1=0beg:if(@i1<=5)beginset@s1=@s1+@i1set@i1=@i1+1goto begendprint@s1（4）waitfor 语句①waitfor delay'00:00:05'select*from studentgo②waitfor time'10:20'exec update_all_stats（5）case语句①select学号,性别=case性别when'1'then'男'when'0'then'女'endfrom studentgo②select学号,成绩=casewhen成绩IS NULL then'未考'when成绩<60 then'不及格'when成绩>=60 and成绩<70 then'及格'when成绩>=70 and成绩<90then'良好'when成绩>=90 then'优秀'endfrom sc3．存储过程（1）create proc proc1asselect学号,课程号from scwhere成绩between 60 and 80goexec proc1（2）①create proc proc21(@学号char(10))asdelete成绩from scwhere学号=@学号go②create proc proc22(@学号char(6),@课程号char(3),@成绩smallint)asinsert into scvalues(@学号,@课程号,@成绩)go（3）create proc proc3(@学号char(6),@课程号char(3),@成绩smallint output)asselect@成绩=成绩from scwhere学号=@学号and课程号=@课程号godeclare@成绩smallintexec proc3'020101','101',@成绩output select'成绩'=@成绩（4）create proc proc_t1(@专业名称char(10))asselect专业名,student.学号,姓名,course.课程号,course.课程名,成绩,学分from student,sc,coursewhere专业名=@专业名称and student.学号=sc.学号and sc.课程号=course.课程号goexec proc_t1'信息管理'go（5）alter proc proc_t1(@专业名称char(10))asselect专业名,student.学号,姓名,course.课程号,course.课程名,成绩,学分from student,sc,coursewhere专业名=@专业名称and student.学号=sc.学号and sc.课程号=course.课程号and student.性别=1goexec proc_t1'信息管理'go（6）drop proc proc_t1（7）create proc proc7(@课程名char(10))asbeginselect课程名,sum(case when成绩between 0 and 59 then 1 else 0 end)as'0-60分', sum(case when成绩between 60 and 79 then 1 else 0 end)as'60-79分', sum(case when成绩between 80 and 89 then 1 else 0 end)as'80-89分', sum(case when成绩between 90 and 100 then 1 else 0 end)as'90-100分' from sc,coursewhere课程名=@课程名and sc.课程号=course.课程号group by课程名endgoexec proc7'英语'go4.（1）create trigger tri_sc_insert on scafter insertasbegindeclare@sno char(6)select@sno=inserted.学号from insertedif not exists(select学号from student where学号=@sno) delete scwhere sc.学号=@snoendgo（2）create trigger tri_sc_upd on scafter updateasif UPDATE(成绩)beginprint'修改失败!!'raiserror ('不能修改SC表的成绩',16,10)rollback transactionendgo③create trigger trigger_1on Coursefor updateasif update(课程号)begin update SCset课程号=(select课程号from inserted)from SC,deletedwhere SC.课程号=deleted.课程号end④create trigger trigger_2on Studentfor deleteasbegindelete SCfrom SC,deletedwhere SC.学号=deleted.学号endgo⑤create trigger trigger_3on Studentfor updateasprint'学生表被修改了！'go⑥alter trigger trigger_3on Studentfor updateasdeclare@kch char(6)select@kch=学号from deletedprint'学生表中学号为'+@kch+'的记录被修改了' go⑦drop trigger trigger_3三、思考题1.存储过程的类型有哪些？分别有什么特征？类型：系统存储过程、扩展存储过程、用户定义存储过程特征：（1）系统存储过程：由系统创建、管理和使用；主要存储在master数据库，以sp_ 为前缀；用户只能对其调用，不能修改或删除；通过系统存储过程能够得到系统信息或为数据库系统管理员管理SQL Server提供支持。

实验五触发器、存储过程操作实验本实验需要2学时。

请大家先根据“触发器.doc”文档完成相关操作，再进行本次实验。

介绍完“存储过程”后，需上交本次实验报告。

一、实验目的（1）掌握SQL Server中的触发器的使用方法；（2）掌握存储过程的操作方法。

二、实验内容1. 创建、查看、修改和删除触发器。

2. 创建、查看、修改和删除存储过程。

三、实验方法1. 触发器的操作（1）建立触发器方法一：使用企业管理器首先，打开企业管理器，定位数据库并找到要创建触发器的表；然后，右击该表名，在弹出的快捷菜单中选择“设计表”，在打开的“设计表”窗口中单击按钮，打开如图1所示窗口。

图1 触发器属性窗口最后，在触发器属性窗口中输入触发器的内容，并单击“确定”按钮。

方法二：使用CREATE TRIGGER语句语法：CREATE TRIGGER <触发器名>ON { <表名> | <视图名>}{ FOR | INSTEAD OF | AFTER}{ [UPDATE] [,] [INSERT] [,] [DELETE]}AS<SQL 语句块>（2）查看触发器方法一：使用企业管理器方法二：使用T-SQL语句- sp_help <触发器名>- sp_helptext <触发器名>- sp_depends <触发器名>（3）修改触发器方法一：使用企业管理器方法二：使用ALTER TRIGGER语句语法：ALTER TRIGGER <触发器名>ON { <表名> | <视图名>}{ FOR | INSTEAD OF | AFTER}{ [UPDATE] [,] [INSERT] [,] [DELETE]} AS<SQL 语句块>（4）删除触发器方法一：使用企业管理器方法二：使用DROP TRIGGER语句语法为：DROP TRIGGER <触发器名>2. 存储过程的操作（1）建立存储过程方法一：使用建立存储过程向导方法二：使用企业管理器方法三：使用SQL语句（CREATE PROCEDURE）语法：CREATE PROC[EDURE] <存储过程名>[{@<参数名> <数据类型>}]AS {<ＳＱＬ语句> | <语句块> }（2）查看存储过程方法一：使用企业管理器方法二：使用SQL语句（系统存储过程）- sp_help <存储过程名>- sp_helptext <存储过程名>- sp_depends <存储过程名>（3）修改存储过程方法一：使用企业管理器方法二：使用SQL语句（ALTER PROCEDURE）语法为：ALTER PROC[EDURE] <存储过程名>[{@<参数名> <数据类型>}]AS {<ＳＱＬ语句>| <语句块> }（4）删除存储过程方法一：使用企业管理器方法二：使用DROP PROCEDURE语句语法为：DROP PROCEDURE <存储过程名>四、实验内容1、在学生表student上建立一个DELETE类型的触发器tr_delete，触发动作是显示信息“已删除学生表中的数据”。