存储过程与触发器

存储过程：

     为了完成特定功能的SQL 语句集，存储在数据库中经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。

基本术语：

1、系统存储过程

   sp_开头,进行系统各项设定，取得信息、相关管理工作

2、本地存储过程

   由用户创建完成的某一特定功能存储过程（默认）

3、临时存储过程

    本地临时存储过程：以#作为名称第一个字符，该存储过程将成为一个存放在tempdb数据库中的本地临时存储过程，只有创建它的用户才能执行它；

   全局临时存储过程，##开始，存储在tempdb数据库中的全局临时存储过程，全局临时存储过程一旦创建，以后连接到服务器的用户都可执行它。

4、远程存储过程

    SQLServer2005中，Remote Stored Procedures位于远程服务器上的存储过程，可以使用分布式查询和EXECUTE命令执行

5、扩展存储过程

    ExtendedStored Proedures用户可以使用外部程序语言编写的存储过程，而且扩展存储过程的名称通常以xp_开头

语法：

创建：

Createprocedure sp_name

@[参数名][类型]，@[参数名][类型]

as

begin

……

End

简写成：

Create  proc   sp_name

@[参数名][类型]，@[参数名][类型]

as

begin

……

end

注：“sp_name”为需要创建的存储过程的名字，该名字不可以阿拉伯数字开头

调用：

exec  sp_name[参数名]

删除：

drop  procedure sp_name

注意：

   不能再一个存储过程中删除另一个存储过程，只能调用

其他命令：

1、show procedure status:

    显示数据库中所有存储过程基本信息，包括所属数据库、存储过程名称、创建时间等

2、show create  procedure  sp_name

    显示某一个mysql存储过程详细信息

3、exec sp_helptext sp_name

    sp_helptext：显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的文本

优点：

1、增强SQL语言的功能和灵活性

     可完成复杂判断和运算

     可用流控制语句（if、switch、while等）编写

2、保证数据安全性和完整

   使相关的动作一起发生，从而可维护数据库的完整性

   使得没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全

3、提高执行速度

    运行前，数据库已对其进行了语法和句法分析，并给出优化执行方案

4、降低网络通信量

   把体现企业规则的运算程序放入数据库服务器中

   集中控制、当企业规则变化时在服务器中改变存储过程即可

缺点：

1、调试麻烦

    但是用PL/SQL Developer（是一个集成开发环境，专门面向Oracle数据库存储程序单元的开发）调试很方便

2、移植问题：

         如果系统过多的使用了存储过程，那系统的业务逻辑过于依赖数据库，这样就会给系统额外的增加一层数据库中的业务逻辑层，如果开发的时候用的sql server，后来发现数据量过大，需要提高性能移植到oracle或者mysql，这样就会很麻烦，相当于把存储过程重写一遍，这是不能忍受的。

3、重新编译问题：

    数据库端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程将需要重新编译（不过也可以设置成运行时刻自动编译）

4、代码可复用性差

    面向对象的思维在存储过程这毫无用武之地，两个很相似的功能在也需要两个存储过程，因为他们是互相独立的，可以互相调用，但是不能进行继承等面向对象的操作，这也就增加了代码量。

     如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，用户想维护该系统很难很难、代价是空前的，维护起来更麻烦。

触发器：

   特殊类型的存储过程，通过事件进行触发被执行，

   与表紧密相连，可看做表格定义的一部分，不能被显示调用（存储过程可通过存储过程名字进行直接调用）

   当对某一表进行诸如update、insert、delete等操作时，如果表中有相应操作类型的触发器，Server会自动执行触发器定义的SQL语句，从而确保对数据的处理必须符合由这些SQL语句所定义的规则。

功能：

强化约束：实现比check语句更为复制的约束

实现由主键和外键所不能保证的复杂参数完整性和数据的一致性

跟踪变化：侦测数据库内的操作，不容许数据库中未经许可的指定更新和变化

级联运行：侦测数据库内的操作，自动级联影响整个数据库各项内容。例如某个表上的触发器中包含有对另外一个表的数据操作,如删除更新插入而该操作又导致该表上触发器被触发。

相关内容：

    为响应数据库更新，触发器可以调用一个或多个存储过程，也可以通过外部过程的调用而在DBMS（数据库管理系统：操纵和管理数据库的大型软件，用于建立、使用和维护数据库）本身之外进行操作。

    触发器可以解决高级形式的业务规则或复杂行为限制以及实现定制记录等一些方面的问题。触发器能够找出某一表在数据修改前后状态发生的差异，并根据这种差异执行一定的处理。此外一个表的同一类型(Insert、 Update、 Delete)的多个触发器能够对同一种数据操作采取多种不同的处理。

   触发器性能比较低，运行时，系统处理的大部分时间花费在参照其他表的这一处理上，这些表既不在内存中也不在数据库设备上，而删除表和插入表总是位于内存中，触发器所参照的其他表的位置决定了操作要花费的时间长短。

种类：

AFTER触发器：

     要求只有执行某一操作（Insert、Update、Delete）后，触发器才能被触发，只能在表上定义

INSTEAD OF触发器

    用来代替通常的触发动作，不执行其定义的操作（insert、update、delete），仅执行触发器自身，系统不是直接对表执行这些操作，而是把操作内容交给触发器，让触发器检查所进行的操作是否正确。如正确才进行相应的操作。因此，INSTEAD OF 触发器的动作要早于表的约束处理。既可在表上定义也可在视图上定义，但在作为级联引用完整性约束目标的表上限制应用。

    简单的地说即：对表执行了 INSERT、UPDATE 或 DELETE 语句操作之后再执行的操作。比如对某个表中的数据进行了更新操作后，要求立即对相关的表进行指定的操作，这时就可以采用 AFTER 触发器。AFTER 触发器只能在表上指定，且动作晚于约束处理。

    每一个表上只能创建一个 INSTEAD OF 触发器，但可以创建多个 AFTER 触发器。

总结：

     存储过程实质就是部署在数据库端的一组定义代码以及SQL。将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来，那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时，只需调用execute，即可自动完成命令。

     在一般的小系统（逻辑简单）中，存储过程和触发器可以多用，毕竟ms设计的，可以很大程度上提升性能；在复杂的系统中，建议不用触发器，少用存储过程。

                         thanks for your time 不足之处、欢迎交流