数据库原理及应用---第9章 存储过程和触发器
《数据库原理与应用》实验存储过程和触发器（部分答案）
实验6存储过程和触发器1．实验目的（1）掌握通过SQL Server管理平台和Transact-SQL语句CREATE PROCEDURE创建存储过程的方法和步骤。
（2）掌握使用Transact-SQL语句EXECUTE执行存储过程的方法。
（3）掌握通过SQL Server管理平台和Transact-SQL语句ALTER PROCEDURE修改存储过程的方法。
（4）掌握通过SQL Server管理平台和Transact-SQL语句DROP PROCEDURE删除存储过程的方法。
（5）掌握通过SQL Server管理平台和Transact-SQL语句CREATE TRIGGER创建触发器的方法和步骤。
（6）掌握引发触发器的方法。
（7）掌握使用SQL Server管理平台或Transact-SQL语句修改和删除触发器。
（8）掌握事务、命名事务的创建方法，了解不同类型的事务的处理情况。
2．实验内容及步骤请先附加studentsdb数据库，然后完成以下实验。
（1）在查询设计器中输入以下代码，创建一个利用流控制语句的存储过程letters_print，该存储过程能够显示个小写字母。
语句：CREATE PROCEDURE letters_printASDECLARE@count intSET@count=0WHILE@count<26BEGINPRINT CHAR(ASCII('a')+@count)SET@count=@count+1ENDexec letters_print（2）输入以下代码，创建存储过程stu_info，执行时通过输入姓名，可以查询该姓名对应的学生的各科成绩。
语句：create proc stu_info@name char(10)asbeginSELECT姓名,g.课程编号,分数FROM dbo.student_info s JOIN grade gON s.学号=g.学号WHERE s.姓名=@nameEndexec stu_info'马东'（3）使用系统存储过程sp_rename将存储过程stu_grade更名为stu_g。
数据库设计中的存储过程与触发器
数据库设计中的存储过程与触发器数据库设计在软件开发中起着至关重要的作用，它决定着数据的结构、存储和访问方式。
在数据库设计中，存储过程和触发器是两个常用的技术，它们能够帮助我们更好地实现数据管理和处理的目标。
本文将重点探讨数据库设计中的存储过程与触发器，并介绍它们的作用、使用场景和优缺点。
一、存储过程存储过程是一组预编译的SQL语句集合，可以在数据库服务器上进行执行。
它们通常用于实现复杂的数据库操作、数据验证和业务逻辑。
存储过程可以被视为一种封装了的数据库操作，通过调用存储过程，我们可以实现统一的数据处理逻辑，提高代码的复用性和可维护性。
在数据库设计中，存储过程可以起到以下几个作用：1. 数据的一致性和完整性控制：存储过程可以通过在执行之前进行数据验证、规范化和自动修复，保证数据的一致性和完整性。
2. 事务管理：存储过程可以帮助我们实现复杂的事务处理，通过定义事务的边界、控制事务的提交和回滚，确保数据的一致性和可靠性。
3. 数据库性能优化：存储过程可以通过预编译和优化查询语句等技术手段，提高数据库的访问效率和响应速度。
存储过程的使用场景多样，例如在用户注册时进行用户名的唯一性验证，在订单提交时计算订单总价等。
然而，存储过程并非适合所有情况，它也存在一些缺点，如可移植性差、调试困难等。
因此，在使用存储过程时应权衡其利弊，并根据具体需求做出选择。
二、触发器触发器是与数据库表相关联的一种特殊对象，它在表上的插入、更新或删除操作时被自动触发执行。
触发器可以用于实现诸多数据库功能，如数据验证、派生数据和审计跟踪等。
触发器的主要作用如下：1. 数据验证和约束：触发器可以在插入、更新或删除数据之前进行数据验证和约束，确保数据的完整性和一致性。
2. 派生数据：通过触发器，可以自动计算和派生某些数据，避免手动计算和维护数据的复杂性。
3. 审计跟踪：触发器可以在数据操作时记录相关的变更信息，以实现审计和追踪功能，用于安全和合规需求。
数据库第9章 存储过程和触发器简明教程PPT课件
9.1 存储过程
9.1.2 存储过程的类型
2. 本地存储过程 本地存储过程又称为“用户自定义存储过程”，它是创建在用户自 己数据库中的存储过程，这种存储过程是用户创建的普通存储过程，没 有前缀“sp_”。在SQL Server 2008中，用户存储过程可以使用T-SQL语 言编写，也可以使用CLR方式编写。在本书中，主要介绍使用T-SQL语言 编写的本地存储过程。 3.临时存储过程 临时存储过程是一种特殊的本地存储过程。如果在本地存储过程前 面有一个“#”，这种存储过程称为局部临时存储过程，只能在一个用户 会话中使用；如果在本地存储过程前面有一个“##”，这种存储过程称 为全局临时存储过程，可以在所有用户会话中使用。
9.2 创建存储过程
9.2.1 使用T-SQL创建存储过程
举例 创建存储过程“p_insert_major”向“Major”专业类别表插入记
录 USE Student GO CREATE PROCEDURE p_insert_major /*创建存储过程*/ @id int,@name varchar(20) /*声明变量分别指定要插入数据的两 个列*/ AS BEGIN INSERT INTO Major VALUES(@id,@name) /*插入的语句*/ END GO
第9章 存储过程与触发器
tab_1的数据例如： 张三 男 1978 的数据例如： 的数据例如 2002 tab_2的数据例如： 张三 男 销售科 科 的数据例如： 的数据例如 长 问题： 问题： 要改tab_1中张三的性别为“女”的话，那 中张三的性别为“ 的话， 要改 中张三的性别为 的性别也该改为“ 该如何处理？ 么tab_2的性别也该改为“女。该如何处理？ 的性别也该改为 原理是： 中某人的性别发生变更后， 原理是：当tab_1中某人的性别发生变更后， 中某人的性别发生变更后 数据库自动将tab_2的性别进行同步修改 数据库自动将 的性别进行同步修改
执行存储过程
例2：编写一个存储过程 ：编写一个存储过程Getunitprice，要求 ， 数据库中针对products表，依 在northwind数据库中针对 数据库中针对 表 据产品编号查询产品单价。 据产品编号查询产品单价。要求删除已经存 在的Getunitprice存储过程，并且通过语句 存储过程， 在的 存储过程 调用新创建的存储过程。 调用新创建的存储过程。
四 带Output参数的存储过程 参数的存储过程
创建存储过程 CREATE PROCEDURE MathTutor @m1 smallint, @m2 smallint, @result smallint OUTPUT AS SET @result = @m1* @m2 GO DECLARE @answer smallint EXECUTE MathTutor 5,6, @answer OUTPUT SELECT 'The result is: ', @answer 结果 The result is: 30
第9 章 存储过程与触发器
本章内容
存储过程 触发器
存储过程
第9章存储过程和触发器
过程中执行，但这种类型的存储过程不能在订阅服务器上执行。
不能与WITH RECOMPILE同时使用。
第9章 事务、存储过程和触发器
比如，我们创建一个最简单的存储过程: CREATE PROCedure getSTU_IS AS SELECT * FROM Student where sdept=„IS‟
第9章 事务、存储过程和触发器
用CREATE PROCEDURE创建存储过程的语法 形式如下：
CREATE PROC [ EDURE ] 存储过程名 [；版本号] [ ({ @参数名 数据类型 } [VARYING] [=default] [OUTPUT] [，…])] [WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION] [FOR REPLICATION] AS SQL语句
第9章 事务、存储过程和触发器
5. 事务处理语句
SQL Server中有关事务的处理语句有：
命令名 作用 格式 BEGIN 说明一个事务开始 BEGIN TRANsaction [<事务名>] TRANSACTION 说明一个事务结束，它的作 COMMIT COMMIT TRANsaction [<事务名 用是提交或确认事务已经完 TRANSACTION >] 成 用于在事务中设置一个保存 SAVE 点，目的是在撤消事务时可 SAVE TRANsaction <保存点> TRANSACTION 以只撤消部分事务，以提高 系统的效率
数据库中的存储过程与触发器
数据库中的存储过程与触发器数据库是一个用于存储和管理大量数据的集合，而存储过程和触发器作为数据库中的两种重要对象，在实际的数据库应用中发挥着重要的作用。
本文将详细介绍数据库中的存储过程和触发器的定义、作用以及使用方式，并对它们在实际应用中的优势进行探讨。
存储过程是一组预编译的SQL语句集合，这些语句经过编译并且存储在数据库中，以便后续的重用。
存储过程可以接受参数，并且通过执行一系列SQL语句来实现复杂的操作。
存储过程的主要作用包括提高数据库的性能、减少网络流量、实现封装和重用性。
首先，存储过程可以提高数据库的性能。
当执行一组SQL语句时，存储过程会将这些语句一次性发送给数据库服务器，并且在服务器上进行预编译和优化。
相比于每次发送单独的SQL语句，存储过程能够减少网络往返的时间，提高执行效率。
其次，存储过程能够减少网络流量。
由于存储过程的执行过程在数据库服务器上完成，它只需要将执行结果返回给客户端，而不需要将整个SQL语句和数据传输回客户端。
这样不仅减少了网络传输的数据量，还减少了网络请求的次数，有效降低了网络流量。
此外，存储过程实现了封装和重用性的特点。
通过将一系列SQL语句封装在一个存储过程中，可以减少代码的重复性，提高代码的可维护性。
同时，存储过程可以在不同的应用程序中被调用，实现了代码的重用性，提高了开发效率。
在实际应用中，存储过程常用于完成复杂的业务逻辑。
例如，在某电商网站的订单系统中，存储过程可以用于完成下单流程的各个环节，包括生成订单、更新库存、计算订单总价等。
通过使用存储过程，可以确保这些操作的原子性，避免了在应用层面上进行多个SQL语句的事务管理。
另一个重要的数据库对象是触发器。
触发器是数据库中的一类特殊对象，它与表相关联，并且在特定的事件发生时自动执行一些操作。
触发器的主要作用包括数据完整性的维护、业务规则的实施以及数据审计等。
首先，触发器能够维护数据的完整性。
通过在数据操作之前或之后触发相应的操作，触发器可以保证数据库中的数据满足特定的约束条件。
第09章 数据库的高级应用——存储过程和触发器_第3稿
第九章数据库的高级使用2——存储过程和触发器在SQL Server数据库管理系统中，存储过程具有很重要的作用，存储过程是T-SQL语句的集合，它提供了一种高效、便捷和安全的访问数据库的方法，经常被用来查询和更新数据。
而触发器就其本质而言也是一种存储过程。
它只是在满足一定条件时就可以触发完成预制好的各种动作，可以帮助我们更好地维护数据库中数据的完整性，实现对数据的管理。
在本章我们要介绍存储过程与触发器的概念、特点和作用，介绍创建和管理存储过程与触发器的方法与技巧。
9.1 任务的提出一天，晓灵又来到了郝老师的办公室。
晓灵：“郝老师，《晓灵学生管理系统》的数据库基本设计完成了。
在这个过程中得到了您的大力支持！太感谢您了！”郝老师：“这没什么，应该做的。
”晓灵：“郝老师，还有个问题得请教一下。
就是在使用《晓灵学生管理系统》的过程中，对于那些不太熟悉T-SQL语句的用户，我们应该如何帮助他们使用系统的查询功能。
再有针对用户的误操作，我们能不能让SQL Server数据库系统实现自动纠错？”郝老师：“我明白你的意思。
也就是说，对于那些不太会使用T-SQL语句的用户，通过使用某种帮助也可以完成相应的任务。
”晓灵：“太对了，我就是想说这些。
那我应该如何解决这个问题呢？”郝老师：“解决这个问题的实质就是使用存储过程和触发器。
当然使用存储过程和触发器并不是仅能完成上述功能。
为了能够更好的说明存储过程和触发器的方法及原理，我们来举例说明。
”下面首先看一个存储过程应用的实例。
在介绍存储过程之后，再看触发器的应用。
【任务9.1】在《晓灵学生管理系统》中，需要提供对学生成绩进行查询的功能。
在查询时，需要提供欲查询的学生姓名和课程名称。
我们知道如果掌握了T-SQL语句中的SELECT语句，要实现这一功能并不难。
但问题是，并不是所有的人都会使用SELECT语句。
要想让每一个系统用户都能实现查询，必须要提出一个新的解决方案。
分析：要想解决这个问题，我们可以把欲执行的T-SQL语句做成一个相对固定的语句组，用户想查询学生的成绩只要执行这个T-SQL语句组就可以了。
存储过程与触发器
存储过程与触发器存储过程和触发器是关系型数据库中非常重要的概念和工具。
存储过程是一组预编译的SQL语句集合，经编译后存储在数据库中，可以被反复调用执行。
而触发器是数据库中一种特殊的对象，它与表相关联，当特定的事务操作（如INSERT、UPDATE、DELETE）在关联的表上执行时，触发器会自动执行相应的操作。
在本文中，将详细介绍存储过程和触发器的应用场景和使用方法。
存储过程的优势主要体现在以下几个方面：1.提高性能：存储过程可以减少网络传输的开销，将数据库操作逻辑集中在数据库服务器上执行，减少了网络延迟时间。
此外，存储过程可以预先编译和优化，提高了执行效率。
2.简化开发：存储过程可以将常用的业务逻辑封装在一起，减少了代码的重复编写。
开发人员只需调用存储过程，而不必重复编写相同的SQL语句。
3.减少安全风险：存储过程可以对数据库操作进行权限控制，通过给用户分配不同的执行权限，提高了数据库的安全性。
4.数据库事务管理：存储过程可以将一系列数据库操作封装在一个事务中，保证了数据的一致性和完整性。
触发器的主要优势在于：1.强制数据完整性：触发器可以通过在特定操作之前或之后执行代码，强制执行特定的条件和限制，确保数据库中的数据始终保持一致性和完整性。
2.隐藏复杂逻辑：触发器可以将复杂的业务逻辑隐藏在数据库中，使应用程序的逻辑更加简洁和清晰。
3.自动化处理：触发器可以自动执行一些操作，如更新相关表的数据，发送电子邮件等，减少了人工操作的需要，提高了工作效率。
下面以一个具体的例子来说明存储过程和触发器的使用。
假设有一个订单管理系统，包括订单表和订单明细表。
当插入一个订单时，触发器会自动计算订单的总金额，并更新到订单表中的"total_amount"字段中。
首先创建一个计算订单总金额的存储过程：CREATE PROCEDURE calculateTotalAmountASBEGINFROM order_detailsUPDATE ordersEND然后创建一个触发器，当插入新的订单明细时，自动调用存储过程计算订单的总金额：CREATE TRIGGER updateTotalAmountON order_detailsAFTERINSERTASBEGINFROM inserted;END通过以上的存储过程和触发器，当插入新的订单明细时，触发器会自动调用存储过程计算订单的总金额，并更新到订单表中。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
执行存储过程： EXEC borrowed_book3
数据库原理及应用
9.1.3存储过程的参数
• 例：利用输出参数计算阶乘。 USE Library IF EXISTS(SELECT name FROM sysobjects WHERE name='factorial' AND type='P') DROP PROCEDURE factorial GO CREATE PROCEDURE factorial 调用存储过程： @in float, --输入形式参数 @out float OUTPUT --输出形式参数 DECLARE @ou float AS EXEC factorial 5,@ou OUT --实参表 DECLARE @i int PRINT @ou DECLARE @s float SET @i=1 SET @s=1 WHILE @i<=@in BEGIN SET @s=@s*@i SET @i=@i+1 END SET @out=@s --给输出参数赋值
客户：sp（1，2） Sp（x,y）
数据库原理及应用
9.1.1存储过程的基本知识
使用存储过程而不使用存储在客户端计算 机本地的 T-SQL 程序的优点包括： 允许标准组件式编程，增强重用性和共享 性 能够实现较快的执行速度 能够减少网络流量 可被作为一种安全机制来充分利用
• • • •
数据库原理及应用
数据库原理及应用
9.2.2创建DML触发器
例7：在表borrow中添加借阅信息记录时，得 到该书的应还日期。 说明：在表borrow中增加一个应还日期 SReturnDate。 USE Library IF EXISTS (SELECT name FROM sysobjects WHERE name ='T_return_date' AND type='TR') DROP TRIGGER T_return_date CREATE TRIGGER T_return_date --创建触 发器 ON Borrow --基于表borrow After INSERT --插入操作 AS --查询插入记录INSERTED中读者的类型 DECLARE @type int,@dzbh char(10),@tsbh char(15) SET @dzbh=(SELECT RID FROM inserted) SET @tsbh=(SELECT BID FROM inserted) SELECT @type= TypeID FROM reader WHERE RID=(SELECT RID FROM inserted)-副本 /*把Borrow表中的应还日期改为 当前日期加上各类读者的借阅期限*/ UPDATE Borrow SET SReturnDate=getdate()+ CASE WHEN @type=1 THEN 90 WHEN @type=2 THEN 60 WHEN @type=3 THEN 30 END WHERE RID=@dzbh and BID=@tsbh
数据库原理及应用
9.2.1触发器的基本知识
• SQL Server 2005 的新增功能。当服务器或数据 库中发生数据定义语言（ DDL ）事件时将调用这 些触发器。但与 DML 触发器不同的是，它们不会 为 响 应 针 对 表 或 视 图 的 UPDATE 、 INSERT 或 DELETE 语句而激发，相反，它们会为响应多种 数据定义语言（ DDL ）语句而激发。这些语句主 要是以 CREATE 、 ALTER 和 DROP 开头的语句。 DDL 触发器可用于管理任务，例如审核和控制数 据库操作。
应用： USE Library INSERT INTO borrow(RID,BID) values('2000186010','TP85-08')
数据库原理及应用
9.2.2创建DML触发器
• 例：在数据库Library中，当读者还 书时，实际上要修改表brorrow中相 应记录还期列的值，请计算出是否 过期。 USE Library IF EXISTS(SELECT name FROM sysobjects WHERE name='T_fine_js' AND type='TR') DROP TRIGGER T_fine_js GO CREATE TRIGGER T_fine_js ON borrow After UPDATE AS DECLARE @days int,@dzbh char(10),@tsbh char(15) SET @dzbh=(select RID from inserted) SET @tsbh=(select BID from inserted) SELECT @days=DATEDIFF(day, ReturnDate, SReturnDate) --DATEDIFF函数返回两个日期之差， 单位为DAY FROM borrow WHERE RID=@dzbh and BID=@tsbh IF @days>0 PRINT '没有过期！' ELSE PRINT '过期 应用： '+convert(char(6),@days)+' 天' USE Library GO UPDATE borrow SET ReturnDate='2007-12-12' WHERE RID='2000186010‘ and BID='TP85-08' GO
数据库原理及应用
第9章 存储过程和触发器
• 9.1存储过程 • 9.2触发器
数据库原理及应用
9.1存储过程
• 9.1.1存储过程的基本知识 • 9.1.2创建用户存储过程 • 9.1.3存储过程的参数
数据库原理及应用
9.1.1存储过程的基本知识
• 存储过程（Stored Procedure）是一组编译 好存储在服务器上的完成特定功能T-SQL 代码，是某数据库的对象。客户端应用程 序可以通过指定存储过程的名字并给出参 数（如果该存储过程带有参数）来执行存 储过程。
数据库原理及应用
数据库原理及应用
An Introduction to Database System
数据库原理及应用
数据库系统概论
An Introduction to Database System
第9章 存储过程和触发器
数据库原理及应用
教学目标：
• 掌握存储过程和触发器的基本概念， • 学会编写简单的存储过程和触发器， • 对存储过程和触发器的实际应用有较好的 理解。
数据库原理及应用
9.2触发器
• • • • • • 9.2.1触发器的基本知识 9.2.2创建DML触发器 9.2.3创建DDL触发器 9.2.4修改触发器 9.2.5删除触发器 9.2.6查看触发器
数据库原理及应用
9.2.1触发器的基本知识
• 触发器是特殊的存储过程，基于一个表创 建，主要作用就是实现由主键和外键所不 能保证的复杂的参照完整性和数据一致性。 • 当触发器所保护的数据发生变化 （update,insert,delete）后，自动运行以 保证数据的完整性和正确性。通俗的说： 通过一个动作（update,insert,delete）调 用一个存储过程（触发器）。
直接传值： EXEC borrowed_book2 '程鹏' --实参表
数据库原理及应用
9.1.3存储过程的参数
• 例：使用默认参数 USE Library GO CREATE PROCEDURE borrowed_book3 @name varchar(10)=NULL --默认参数 AS IF @name IS NULL SELECT r.RID,r.Rname,b.BID,k.Bname,b.LendDate FROM reader r INNER JOIN borrow b ON r.RID=b.RID INNER JOIN book k ON b.BID=k.BID ELSE SELECT r.RID,r.Rname,b.BID,k.Bname,b.LendDate FROM reader r INNER JOIN borrow b ON r.RID=b.RID INNER JOIN book k ON b.BID=k.BID WHERE Rname=@name GO
数据库原理及应用
9.2.2创建DML触发器
语法格式： CREATE TRIGGER 触发器 ON 表名 FOR[update,insert,delete ] AS SQL语句
数据库原理及应用
9.2.2创建DML触发器
例：创建基于表reader ，DELETE操作的触发器。 USE Library GO IF EXISTS(SELECT name FROM sysobjects WHERE name='reader_d' AND type='TR') DROP TRIGGER reader_d --如果已经存在触发器reader_d则删除 GO CREATE TRIGGER reader_d --创建触发器 ON reader --基于表 应用： FOR DELETE --删除事件 USE Library AS GO PRINT '数据被删除!' --执行显示输出 DELETE reader where Rname='aaa' GO
数据库原理及应用
9.1.2创建用户存储过程
• 格式：
CREATE PROC 过程名 @形参名 类型 @变参名 类型 OUTPUT AS SQL语句
数据库原理及Байду номын сангаас用
9.1.2创建用户存储过程