存储过程和触发器讲解.docx
- 文档编号:18240679
- 上传时间:2023-08-14
- 格式:DOCX
- 页数:13
- 大小:203.57KB
存储过程和触发器讲解.docx
《存储过程和触发器讲解.docx》由会员分享,可在线阅读,更多相关《存储过程和触发器讲解.docx(13页珍藏版)》请在冰点文库上搜索。
存储过程和触发器讲解
存储过程和触发器
存储过程和触发器是SQLServer的数据库对象。
使用存储过程可以提高应用程序的效率。
触发器可以大大增强应用程序的健壮性、数据库的可恢复性和可管理性。
一、存储过程
存储过程是一组T-SQL语句,它们只需编译一次,以后即可多次执行。
存储过程是在SQLServer中定义的子过程,是数据库对象之一。
存储过程可以执行范围很宽的各种操作与业务功能。
比如可以插入、更新或删除表中的数据。
通过传递参数值,存储过程可以判断是选择数据还是执行其他操作。
由于存储过程可以接受输入参数并以输出参数的格式向调用过程或批处理返回多个值;存储过程是包含用于在数据库中执行操作(包括调用其他过程)的编程语句。
此外存储过程可以向调用过程或批处理返回状态值,以指明成功或失败(以及失败的原因)。
故SQLServer中的存储过程与其他语言中的过程(有时也称:
函数)类似。
可以使用T-SQLEXECUTE语句来运行存储过程。
存储过程作为SQLServer数据库系统中很重要的概念之一,合理的使用存储过程,可以有效地提高程序的性能;并且将商业逻辑封装在数据库系统中的存储过程中,可以大大提高整个软件系统的维护性。
当商业逻辑发生了改变的时候,不再需要修改并编译客户端的应用程序以及重新分发它们到为数众多的用户手中,只需要修改位于服务器端的实现相应商业逻辑的存储过程即可。
使用SQLServer创建应用程序时,T-SQL编程语言是应用程序和SQLServer数据库之间的主要编程接口。
使用T-SQL程序时,可用两种方法存储和执行程序;一种是将程序存储在本地,然后创建向SQLServer发送命令并处理结果的应用程序;另一种是将程序作为存储过程存储在SQLServer中,然后创建执行过程并处理结果的应用程序。
在SQLServer中使用存储过程而不使用存储在客户端计算机本地的T-SQL程序,原因在于存储过程具有以下的好处:
✓存储过程已在服务器注册。
✓存储过程具有安全特性(例如权限)和所有权链接,以及可以附加到它们的证书。
✓存储过程可以强制应用程序的安全性。
✓存储过程允许模块化程序设计。
存储过程被创建后可以多次调用。
✓存储过程是命名代码,允许延迟绑定。
✓存储过程可以减少网络流量。
对于同一个针对数据库对象的操作,如果这一操作所涉及到的T-SQL语句被组织成一个存储过程,那么当在客户间计算机上调用该存储过程时,网络中传送的只是调用存储过程的语句,从而减少网络负担。
一)存储过程类型
在SQLServer2005中有多种可用的存储过程。
主要有以下3种:
系统存储过程、用户定义的存储过程和扩展存储过程。
1.系统存储过程
SQLServer2005中许多管理活动都是通过一种特殊的存储过程执行的,这种存储过程被称为系统存储过程。
系统存储过程存储在源数据库中,并且带有sp_前缀。
从逻辑意义上讲,系统存储过程出现在每个系统定义数据库和用户定义数据库的sys构架中。
在SQLServer2005中,可将GRANT、DENY和REVOKE权限应用于系统存储过程。
SQLServer支持在SQLServer和外部程序之间提供一个接口以实现各种维护活动的系统存储过程。
这些扩展存储程序使用xp_前缀。
在“MicrosoftSQLServerManagementStudio”中可以查看系统存储过程。
查看步骤为:
打开“MicrosoftSQLServerManagementStudio”后,在“对象资源管理器”展开“数据库”选项,打开master系统数据库,打开“可编程的”目录下的“存储过程”目录,就可以看到“系统存储过程目录”,该目录有所有系统存储过程的列表。
如下图所示:
2.用户定义的存储过程
用户定义的存储过程是指由用户创建并能完成某一特定功能的存储过程,是主要的存储过程类型,用户自定义的存储过程封装了可重用代码的模块或例程。
存储过程可以接受输入参数、向客户端返回表格或标量结果和消息、调用数据定义语言(DDL)和数据操作语言(DML)语句,然后返回输出参数。
在SQLServer2005中,存储过程有两种类型:
T-SQL和CLR。
其中T-SQL存储过程是指保存着T-SQL语句的集合,可以接受和返回用户提供的参数。
CLR存储过程是指.NETFramework公共语言运行时(CLR)方法的引用。
3.扩展存储过程
扩展存储过程允许使用编程语言(例如C语言)创建自己的外部例程。
扩展存储过程是指SQLServer的实例可以动态加载和运行的DLL。
扩展存储过程直接在SQLServer的实例的地址空间中运行,可以使用SQLServer扩展存储过程API完成编程。
二)存储过程的设计
1.存储过程的设计规则
2.限定存储过程内的名称
3.存储过程的内部命名规范化
4.加密过程定义
三)存储过程的实现
1.创建存储过程的前提
2.创建存储过程
1)使用SQLServerManagementStudio创建存储过程
2)用T-SQL语句创建存储过程
【例1】创建一个名为“产品情况”的存储过程
USE商品信息
GO
CREATEPROCEDURE产品情况
AS
SELECT产品名称,价格
FROM产品表
GO
3.执行存储过程
Execute产品情况
【例2】在“商品信息”数据库下创建一个名为“通过编号查询产品情况”的存储过程。
USE商品信息
GO
CREATEPROCEDURE[dbo].[通过产品编号查询产品情况]
@SnoVARCHAR(6)
AS
BEGIN
SELECT产品名称,价格
FROM产品表
WHERE产品编号=@Sno
END
GO
该存储过程的执行方式为:
Execute通过产品编号查询产品情况'100010'
或
Execute通过产品编号查询产品情况@Sno='100010'
4.存储过程的修改与删除
【例3】修改在“商品信息”数据库下的名为“通过编号查询产品情况”的存储过程,使得能够查找到所有产品编号比给定编号大的所有产品信息。
ALTERPROCEDURE[dbo].[通过产品编号查询产品情况]
@PnoVARCHAR(6)
AS
BEGIN
SELECT产品名称,价格
FROM产品表
WHERE产品编号>@Pno
END
GO
执行语句为:
Execute通过产品编号查询产品情况@Sno='100004'
【例4】使用DROP语句删除“通过编号查询产品情况”存储过程
DROPPROCEDURE产品情况
DROPPROCEDURE通过产品编号查询产品情况
二、触发器
触发器是一种特殊的存储过程,它在执行语言事件时自动生效。
触发器通过强制执行一定的业务规则,保持数据完整性、检查数据有效性、实现数据库管理任务和一些附加的功能。
约束和触发器是SQLServer2005提供的来强制执行业务规则和数据完整性的两种主要机制。
触发器的主要作用是实现由主键和外键所不能保证的复杂的参照完整性和数据一致性。
触发器是数据库中比较高级的应用,灵活使用触发器可以大大增强应用程序的健壮性、数据库的可恢复性和数据库的可管理性。
同时可以使开发人员和数据库管理员实现一些复杂的功能,简化开发步骤,降低开发成本。
增加开发效率,提高数据库的可靠性。
触发器是一种特殊的存储过程,除了具有存储过程的特点外,它还另外含有以下特点:
1)自动执行
触发器是自动执行的,可以在一定条件下触发。
2)对相关表实现级联更改
触发器可以同步数据库的相关表,进行级联更改。
3)触发器可以实现更复杂的安全检查
触发器可以实现比CHECK更复杂的业务规则,还可以引用其他表中的列。
4)触发器可以实现数据库的管理任务
如DDL触发器,在DDL语句执行后触发,可以实现一些统一的数据库管理策略。
一)触发器类型
SQLServer2005包括两大类触发器:
DML触发器和DDL触发器。
1.DML触发器
DML触发器是在执行数据操作语言事件时被调用的触发器,其中数据操作语言事件包括:
INSERT、UPDATE和DELETE语句。
DML触发器用于在数据被修改时强制执行业务规则,以及扩展MicrosoftSQLServer2005约束、默认值和规则的完整性检查逻辑。
触发器中可以包含复杂的Transact-SQL语句触发器整体被看作一个事物,可以回滚。
约束和DML触发器在特殊情况下各有优点。
DML触发器的主要优点在于它们可以包含使用T-SQL代码的复杂处理逻辑。
因此,DML支持约束的所有功能;但DML触发器对于给定的并不一定是最好的方法。
实体完整性必须在最低级别上通过索引进行强制,这些索引是PRIMARYKEY和UNIQUE约束的一部分,或者是独立于约束而创建的。
域完整性应通过CHECK约束进行强制,而引用完整性(RI)则通过FOREIGNKEY约束进行强制,假设这些约束的功能满足应用程序的功能要求。
当约束支持的功能无法满足应用程序的功能要求时,DML触发器将变得非常有用。
DML触发器在以下方面非常有用:
(1)DML触发器可通过数据库中的相关表实现级联更改。
不过,通过级联引用完整性约束可以更有效地进行这些更改。
(2)DML触发器可以防止恶意或错误的INSERT、UPDATE以及DELETE操作,并强制执行比CHECK约束定义的限制更为复杂的其他限制。
(3)DML触发器可以评估修改前后表的状态,并根据该差异采取措施。
(4)一个表中的多个同类DML触发器(INSERT、UPDATE或DELETE)允许采取多个不同的操作来响应同一个修改语句。
在SQLServer2005中,可以创建AFTER触发器、INSTEADOF触发器和CLR触发器。
(1)AFTER触发器。
在执行INSERT、UPDATE或DELETE语句操作之后执行AFTER触发器。
指定AFTER与指定FOR相同,它是SQLServer早期版本中唯一可用的选项。
AFTER触发器只能在表上指定。
(2)INSTEADOF触发器。
执行INSTEADOF触发器代替通常的触发动作。
还可为一个或多个基表的视图定义INSTEADOF触发器,而这些触发器能够扩展视图可支持的更新类型。
(3)CLR触发器。
CLR触发器可以是AFTER触发器还可以是DDL触发器。
CLR触发器将执行在托管代码(在.NETFramework中创建并在SQLServer中上载的程序集的成员)中编写的方法,而不用执行T-SQL存储过程。
2.DDL触发器
DDL触发器是SQLServer2005的新增功能,与DML触发器类似,DDL触发器将激发存储过程以响应事件。
与DML不同的是,它们不会响应针对表或视图的UPDATE、INSERT或DELETE语句而激发。
它响应的触发事件是由数据定义语言引起的事件,包括:
CREATE、ALTER和DROP语句,DDL触发器用于执行数据库管理任务,如调解和审计数据库运行。
DDL触发器只能在触发事件发生后才能调用执行,即它只能是AFTER类型的。
如果要执行以下操作,可使用DDL触发器:
(1)要防止对数据库进行某些更改
(2)希望数据库中发生某种情况以响应数据库架构中的更改
(3)要记录数据库架构中的更改或事件
仅在运行触发DDL触发器的DDL语句后,DDL触发器才会激发。
DDL触发器无法作为INSTEADOF触发器使用。
二)触发器的设计与实现
(1)创建触发器
【例1】创建一个触发器,使得对于“商品信息”数据库,如果在“产品表”中添加数据,则将向客户端显示一条“数据插入成功”的信息。
USE商品信息
GO
CREATETRIGGER产品表_trigger1
ON产品表
FORINSERT
AS
PRINT'数据插入成功'
GO
USE商品信息
GO
INSERTINTO产品表
VALUES('100015','IPHONE6',5600,20)
GO
【例2】创建一个触发器,使得对于“商品信息”数据库,如果在“产品表”中删除数据,则将向客户端显示一条“数据删除成功”的信息。
USE商品信息
GO
CREATETRIGGER产品表_trigger2
ON产品表
FORDELETE
AS
PRINT'数据删除成功'
GO
USE商品信息
GO
DELETEFROM产品表
WHERE产品编号='100013'
(2)删除触发器
【例3】删除“产品表_triger2”触发器
USE商品信息
GO
DROPTRIGGER产品表_trigger2
(3)修改触发器
【例4】修改“产品表_triger1”触发器,使得对于商品信息数据库,如果在产品表中添加或者更新数据,则将向客户端显示一条信息“操作成功”的信息。
USE商品信息
GO
ALTERTRIGGER产品表_trigger1
ON产品表
FORINSERT,UPDATE
AS
PRINT'操作成功'
GO
USE商品信息
GO
INSERTINTO产品表VALUES('100013','MP4',650,50)
USE商品信息
GO
UPDATE产品表
SET价格=600
WHERE产品编号='100013'
三、事务
由于触发器是对其中的某张表进行添加、删除、修改等操作可能引起另一张或多张表中数据的数据的变化,为了保持数据完整性和一致性,触发器和事务是一个强有力的结合。
1.什么是事务
事务是一个不可分隔的逻辑单元,在数据库系统上执行并发操作时事务是最小的控制单元来使用。
它包含的所有数据库操作命令作为一个整体一起提交或撤销,即这一组数据库操作命令要要么都执行,要么都不执行。
2.事务的4个属性
(1)原子性(Atomicity)。
事务中的所有元素作为一个整体提交或回滚,事务的各元素是不可分的,事务是一个完整的操作。
(2)一致性(Consistency)。
事务完成时,数据必须是一致的,也就是说,在事务开始之前,数据存储中的数据处于一致状态,保证数据的无损。
(3)隔离性(Isolation)。
对数据进行修改的多个事务是彼此隔离的。
这表明事务必须是独立的,不应该以任何方式干预或影响其他事务。
(4)持久性(Durability)。
事务完成之后,它对于系统的影响是永久的,即使出现系统故障,该修改也将一直保留,真实地修改了数据库。
3.事务的语句
通常在程序中用BEGINTRANSACTION命令来标识一个事务的开始,用COMMITTRANSACTION命令标识事务的结束。
这两个命令之间的所有语句被视为一体,只有执行到COMMITTRANSACTION命令时,事务中对数据库的更新操作才算确认。
和BEGIN……END命令类似,这两个命令也可以进行嵌套,即事务可以嵌套执行。
这两个命令的语法如下:
开始事务:
BEGINTRANSCTION
提交事务:
COMMITTRANSACTION
【例5】定义一个事务,使得对于“商品信息”数据库,如果在“产品销售表”中增加一条记录,表明某产品已销售出一定的数量,则“产品表”中该产品编号对应的记录的库存量应减掉已经销售的数量。
USE商品信息
GO
BEGINTRANSACTION
INSERTINTO产品销售表VALUES('100014','000002','2007-03-01',5,5000.0)
UPDATE产品表SET库存量=库存量-5WHERE产品表.产品编号='100014'
COMMITTRANSACTION
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 存储 过程 触发器 讲解