存储过程vs触发器

原创 2007年09月24日 17:23:00
编写存储过程与触发器
       存储过程和触发器是由用户创建的、驻留在服务器的一组Transact SQL查询语句。触发器是系统在特定条件下执行的。存储过程能够改善应用程序的性能。当客户程序需要访问数据时,一般要经过5个步骤才能访问到数据:
1)  查询语句被发送到服务器。
2)  服务器编译SQL代码。
3)  优化产生查询的执行计划。
4)  数据引擎执行查询。
5)  结果发回客户程序。
存储过程是在创建时编译的,当存储过程第一次执行时,SQL Server产生查询执行计划并将其存储进来,以利于将来使用。当通过存储过程发出一个请求时,上述的第2和第3步就没有了,这能大大改善系统的性能。即使在第1步上也能提高性能。因为此时发送到服务器的语句只是一条存储过程的EXECUTE语句,而不是庞大的、复杂的查询。这种特性能降低网络的流量。
除了性能方面的改善外,存储过程还提供了方便地集中维护应用程序的功能。如果将查询嵌入到应用程序中。而又需要对查询进行改变,则应用程序需要重新编译,并重新发布到所有的客户端。而在存储过程中,修改对用户而言是透明的,它只需要在服务器上重新编译存储过程。
存储过程还能提供安全机制,尽管用户可能无权执行存储过程中的命令,但它却可能有权执行存储过程本身。有时候,系统管理员不会给用户以数据修改(UPDATE、INSERT和DELETE)的权力。创建的存储过程却能进行这些操作。当然用户需要拥有执行该存储过程的权力。

建立存储过程
       存储过程可以达到以下目的:
       · 带参数。
       · 返回状态值。
       · 调用其它存储过程。
       · 在远程服务器上执行。
       存储过程在“sysobjects”系统表中有一个表项,其类型为“P”。存储过程的文本存储在“syscomments”系统表中。创建存储过程需要使用Transact SQL命令CREATE PROCEDURE。
       例如:
       USE pubs
       GO
       
       CREATE PROCEDURE ap_GetAuthorsForPublisher
       AS
       SELECT a.au_lname,a.au_fname
       FROM authors a, titleauthor ta, titles t, publishers p
       WHERE a.au_id = ta.Au_id
       AND ta.Title_id = t.title_id
       AND t.pub_id = p.pub_id
       AND p.pub_name = ’New Moon Books’
       GO

       CREATE PROCEDURE语句的语法如下:
       CREATE PROC[DURE] procedure_name [;number]
              [@parameter_name ][OUTPUT] [,_n] ]
              [WITH {RECOMPILE | ENCRYPTION}]
              [FOR REPLICATION]
       AS
       Number是用来对相同名字的过程进行分组的整数。分组是将所有的过程通过drop procedure语句组合到一个分组中。
       @parameter_name指定参数的名称。
       RECOMPILE表示每次执行过程时都要进行编译。
       ENCRYPTION表示过程的文本在“syscomments”表中要加密。
       FOR REPLICATION表示过程不能在提交服务器上执行。

将参数传递给存储过程
       存储过程能够接受参数。
       注意:过程的参数也可以是用户定义的数据类型。

给参数一个缺省值
       用户还可以为存储过程中的参数定义缺省值。当在执行时没有提供所需的参数时,系统就使用缺省值作为参数。如果既没有定义缺省值,又没有在执行时提供参数,则SQL Server就会返回一个错误。在存储过程中定义缺省值,并使用一些逻辑检测是否指定了参数从而采取相应的行动,这是一种很好的习惯。
       例如:
       USE pubs
       GO
       
       CREATE PROCEDURE ap_GetAuthorsForPublisher
       @PublisherName varchar(40) = ‘New Moon Books’
       AS
       SELECT a.au_lname,a.au_fname
       FROM authors a, titleauthor ta, titles t, publishers p
       WHERE a.au_id = ta.Au_id
       AND ta.Title_id = t.title_id
       AND t.pub_id = p.pub_id
       AND p.pub_name = @PublisherName
       GO 
大多关系型数据库系统:如oracle、sybase、DB2等均支持对数据库触发器(data   base   triggers)的使用。触发器是一种特殊类型的存贮过程,它在插入、删除或修改特定表中数据时起作用。触发器通过维持不同表中逻辑上相关数据的一致性,保持数据的相关完整性。  
   
  触发器的主要特点在于,不管何种原因造成数据变化,它均能够自动响应。因此,与存贮过程相类似,在当今数据库系统的建立中,系统开发人员大多使用触发器,极大推进了系统建立的质量。本文以oracle公司的oracle   7数据库系统所支持的触发器为例,就触发器的创建、组成、使用作一介绍。  
   
  1   触发器的创建及使用时机  
   
  当针对某特定表的insert,update或delete语句执行时,用户所定义的过程被隐含执行,这个过程就叫做数据库触发器。触发器可以作为一个单元而执行的sql及pl/sql语句,并可调用存贮过程,但存贮过程与触发器在被执行的方式上完全不同。存贮过程是被用户应用触发器显式调用的,而触发器则是当insert,update或delete语句执行时,由数据库系统点火的(fired),不管与其相连的用户及应用程序。触发器被存贮于数据库中,并与相关联的表分离,由于一般应用预先定义了触发器,相应的数据库管理系统对其进行了伪编译及优化,因而在客户/服务器计算模式下,对减轻网络流量及加快执行速度方面,是一较好的方案。  
   
   
   
  触发器仅可定义于表上,而不能定义于视图上,在大多数情况下,触发器用于如下几个方面:  
   
  1)   自动生成派生列值;2)   禁止非法事务;3)   增强复杂的安全识别;4)   在分布式数据库中增强参照完整性;5)   增强复杂的商业规则;6)   提供透明的事件日志;7)   提供高级审计;8)   维护同步表复制;9)   在表存取上聚合统计。  
   
  开发人员一般是对上述9种情况作组合使用,可归为以下四种典型应用。  
   
  1)   对库中相关表进行连环更新,如:键值的同步更新,数据冗余实现,计算表的同步更新等;  
   
  2)   实现那些破坏完整性操作的拒绝,如:不匹配外键值的插入拒绝;  
   
  3)   实现库定义本身所不能实现的更为复杂的商业规则,如:更新操作的时间限制,更新数据的幅度限制等;  
   
  4)   实现简单的“如果……怎么办”的分析。  
   
  在触发器的使用中,需注意避免级联点火的问题,另一方面,对触发器的过度使用会导致维护困难,还需注意触发器级定义完整性约束的区别。  
   
  2   触发器的组成  
   
  触发器,由触发事件或语句、触发器限制、触发体三部分组成,如下列所示:  
   
   
   
  能导致触发器点火的SQL语句叫做触发事件或语句,它可以是在一表上的INSERT,UPDATE或DELETE语句,也可以是多个DML语句,如:……INSERT   OR   DELETE   OF   INVENTORY……。  
   
  当多个类型DML语句点火触发器时,条件前置可用来检测触发语句的类型,因而,单一的触发器内可据点火触发器主句类型来执行不同的处理。  
   
  触发器限制指明一布尔型(逻辑)表达式,仅当表达式值为真(TRUE)时,触发器才点火,否则触发体将不被执行。  
   
  触发体是一个过程(PL/SQL块),它由一些SQL及PL/SQL语句代码组成,触发语句被执行,且触发器限制为真时,该段代码被执行。  
   
  与存贮过程类似,触发体内含SQL及PL/SQL语句,并可定义PL/SQL语言结构,可指明触发体、执行的次数。  
   
  行触发器对所影响的每行执行一次。如-UPDATE语句涉及到某表的多行更新,则对更新的每一行,触发器点火一次;而语句触发器仅点火一次,不管其语句影响到表的多少行;如-DELETE语句删除了表中的多行,但语句触发器仅点火一次。  
   
  在定义触发器时,还可指明触发时间,即触发体在触发语句之前或之后执行:BEFORE、AFTER。  
   
  在触发器的实际应用中,往往是组合触发时间及触发次数,由这些参数可形成如下表所示的四种类型的触发器。    
   
  类   型  
    含   义  
     
  触发时间  
    触发次数  
     
  BEFORE  
    Statement  
    在执行触发语句前,执行触发体  
     
  AFTER  
    Statement  
    在执行触发语句及申请任何滞后完全性约束之后,执行触发体  
     
  BEFORE  
    ROW  
    在修改所影响的每一行及验证相应完整性约束之前,执行触发体  
     
  AFTER  
    ROW  
    在修改所影响的每一行及可能申请相应完整性约束之后,执行触发体,设触发器实行行级封锁机制。  
     
   
  对一给定表,可以有同一类型的多个触发器。更进一步,对每种类型的DML语句(INSERT,UPDATE或DELETE)亦可建立任意多的前缀类型不同的触发器,针对同一语句的相同类型的触发器,其点火次序是随机的(不能实现程序控制)。  
   
  3   触发器的执行  
   
  定义于数据库的触发器,可由程序控制其使能(ENABLE)或屏蔽(DISABLE)状态。  
   
  单一的-SQL语句可以潜在地点火前述四种类型的触发器,另一方面,触发体内的一语句可能导致一个或多个完整性规则,也可能导致其它的触发器点火(级联触发器),关于多触发点火次序及约束检测,ORACLE采用以下的执行模型。  
   
  1)   执行所有的BEFORE语句级触发器;2)   对遍历所涉及的每一行:a.执行所有的BEFORE行级触发器;b.锁定并修改行,执行完整性约束检测;c.执行所有的AFTER行级触发器。3)   完成滞后完整性的约束检测;4)   执行所有的AFTER语句级触发器。  
版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

存储过程 VS 触发器(转载的)

SQL   Server   中的存储过程与其它编程语言中的过程类似,原因是存储过程可以:   1   接受输入参数并以输出参数的形式将多个值返回至调用过程或批处理 2   包含执行数据库操作(包括调...

触发器调用存储过程

开窗函数,视图,事务,存储过程,索引,触发器,游标

补充:DDL create table TableName ( fid int identity(1,1) primary key not null, ftitle nvarchar(...

触发器与存储过程

  • 2015-03-17 10:43
  • 71KB
  • 下载

java 从零开始,学习笔记之基础入门<SQL_Server_视图_函数_存储过程_触发器等>(二十三)

注释_运算符_函数 注释 --单行注释 /* *多行注释 * * */   --定义局部变量 --declare 局部变量名类型[(长度)] =  默认值; --给局部变量赋值 --   se...

存储过程与触发器

  • 2015-04-17 09:13
  • 596KB
  • 下载

存储过程和触发器的区别

存储过程 是 SQL 语句和可选控制流语句的预编译集合,以一个名称存储并作为一个单元处理。存储过程存储在数据库内,可由应用程序通过一个调用执行,而且允许用户声明变量、有条件执行以及其它强大的编程功能...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)