数据库高级对象(存储过程,事务,锁,游标,触发器)

一. 事务和锁

了解事务和锁

事务：保持逻辑数据一致性与可恢复性，必不可少的利器。

锁：多用户访问同一数据库资源时，对访问的先后次序权限管理的一种机制，没有他事务或许将会一塌糊涂，不能保证数据的安全正确读写。

死锁：是数据库性能的重量级杀手之一，而死锁却是不同事务之间抢占数据资源造成的。

不懂的听上去，挺神奇的，懂的感觉我在扯淡，下面带你好好领略下他们的风采，嗅査下他们的狂骚。。

先说事务--概念，分类

用华仔无间道中的一句来给你诠释下：去不了终点，回到原点。

举例说明：

在一个事务中，你写啦2条sql语句，一条是修改订单表状态,一条是修改库存表库存-1 。 如果在修改订单表状态的时候出错，事务能够回滚，数据将恢复到没修改之前的数据状态，下面的修改库存也就不执行，这样确保你关系逻辑的一致，安全。。

事务就是这个样子，倔脾气，要么全部执行，要么全部不执行，回到原数据状态。

书面解释：事务具有原子性，一致性，隔离性，持久性。

  • 原子性：事务必须是一个自动工作的单元，要么全部执行，要么全部不执行。
  • 一致性：事务结束的时候，所有的内部数据都是正确的。
  • 隔离性：并发多个事务时，各个事务不干涉内部数据，处理的都是另外一个事务处理之前或之后的数据。
  • 持久性：事务提交之后，数据是永久性的，不可再回滚。

然而在SQL Server中事务被分为3类常见的事务：

  • 自动提交事务：是SQL Server默认的一种事务模式，每条Sql语句都被看成一个事务进行处理，你应该没有见过，一条Update 修改2个字段的语句，只修该了1个字段而另外一个字段没有修改。。
  • 显式事务：T-sql标明，由Begin Transaction开启事务开始，由Commit Transaction 提交事务、Rollback Transaction 回滚事务结束。
  • 隐式事务：使用Set IMPLICIT_TRANSACTIONS ON 将将隐式事务模式打开，不用Begin Transaction开启事务，当一个事务结束，这个模式会自动启用下一个事务，只用Commit Transaction 提交事务、Rollback Transaction 回滚事务即可。

显式事务的应用

常用语句就四个。

  • Begin Transaction：标记事务开始。
  • Commit Transaction：事务已经成功执行，数据已经处理妥当。
  • Rollback Transaction：数据处理过程中出错，回滚到没有处理之前的数据状态，或回滚到事务内部的保存点。
  • Save Transaction：事务内部设置的保存点，就是事务可以不全部回滚，只回滚到这里，保证事务内部不出错的前提下。

上面的都是心法，下面的给你来个招式，要看仔细啦。

 

 1 ---开启事务

 2 begin tran

 3 --错误扑捉机制，看好啦，这里也有的。并且可以嵌套。

 4 begin try 

 5    --语句正确

 6    insert into lives (Eat,Play,Numb) values ('猪肉','足球',1)

 7    --Numb为int类型，出错

 8    insert into lives (Eat,Play,Numb) values ('猪肉','足球','abc')

 9    --语句正确

10    insert into lives (Eat,Play,Numb) values ('狗肉','篮球',2)

11 end try

12 begin catch

13    select Error_number() as ErrorNumber,  --错误代码

14           Error_severity() as ErrorSeverity,  --错误严重级别，级别小于10 try catch 捕获不到

15           Error_state() as ErrorState ,  --错误状态码

16           Error_Procedure() as ErrorProcedure , --出现错误的存储过程或触发器的名称。

17           Error_line() as ErrorLine,  --发生错误的行号

18           Error_message() as ErrorMessage  --错误的具体信息

19    if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务

20       rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。

21 end catch

22 if(@@trancount>0)

23 commit tran  --如果成功Lives表中，将会有3条数据。

24

25 --表本身为空表，ID ,Numb为int 类型，其它为nvarchar类型

26 select * from lives

 

 

 

---开启事务

begin tran

--错误扑捉机制，看好啦，这里也有的。并且可以嵌套。

begin try   

   --语句正确

   insert into lives (Eat,Play,Numb) values ('猪肉','足球',1)  

    --加入保存点

   save tran pigOneIn

   --Numb为int类型，出错

   insert into lives (Eat,Play,Numb) values ('猪肉','足球',2)

   --语句正确

   insert into lives (Eat,Play,Numb) values ('狗肉','篮球',3)

end try

begin catch

   select Error_number() as ErrorNumber,  --错误代码

          Error_severity() as ErrorSeverity,  --错误严重级别，级别小于10 try catch 捕获不到

          Error_state() as ErrorState ,  --错误状态码

          Error_Procedure() as ErrorProcedure , --出现错误的存储过程或触发器的名称。

          Error_line() as ErrorLine,  --发生错误的行号

          Error_message() as ErrorMessage  --错误的具体信息

   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务

      rollback tran   ---由于出错，这里回滚事务到原点，第一条语句也没有插入成功。

end catch

if(@@trancount>0)

rollback tran pigOneIn --如果成功Lives表中，将会有3条数据。

 

--表本身为空表，ID ,Numb为int 类型，其它为nvarchar类型

select * from lives

 

 

使用set xact_abort

设置 xact_abort on/off , 指定是否回滚当前事务，为on时如果当前sql出错，回滚整个事务，为off时如果sql出错回滚当前sql语句，其它语句照常运行读写数据库。

 需要注意的时：xact_abort只对运行时出现的错误有用，如果sql语句存在编译时错误，那么他就失灵啦。

 

delete lives  --清空数据

set xact_abort off

begin tran

    --语句正确

   insert into lives (Eat,Play,Numb) values ('猪肉','足球',1)  

   --Numb为int类型，出错,如果1234..那个大数据换成'132dsaf' xact_abort将失效

   insert into lives (Eat,Play,Numb) values ('猪肉','足球',12345646879783213)

   --语句正确

   insert into lives (Eat,Play,Numb) values ('狗肉','篮球',3)

commit tran

select * from lives

 

 

为on时，结果集为空，因为运行是数据过大溢出出错，回滚整个事务。

事务把死锁给整出来啦

跟着做：打开两个查询窗口，把下面的语句，分别放入2个查询窗口，在5秒内运行2个事务模块。

begin tran

  update lives set play='羽毛球'

  waitfor delay '0:0:5' 

  update dbo.Earth set Animal='老虎'

commit tran

 

begin tran

  update Earth set Animal='老虎'

  waitfor  delay '0:0:5' --等待5秒执行下面的语句

  update lives set play='羽毛球'

commit tran

select * from lives

select * from Earth

 

 

 

为什么呢，下面我们看看锁，什么是锁。

并发事务成败皆归于锁——锁定

在多用户都用事务同时访问同一个数据资源的情况下，就会造成以下几种数据错误。

  • 更新丢失：多个用户同时对一个数据资源进行更新，必定会产生被覆盖的数据，造成数据读写异常。
  • 不可重复读：如果一个用户在一个事务中多次读取一条数据，而另外一个用户则同时更新啦这条数据，造成第一个用户多次读取数据不一致。
  • 脏读：第一个事务读取第二个事务正在更新的数据表，如果第二个事务还没有更新完成，那么第一个事务读取的数据将是一半为更新过的，一半还没更新过的数据，这样的数据毫无意义。
  • 幻读：第一个事务读取一个结果集后，第二个事务，对这个结果集经行增删操作，然而第一个事务中再次对这个结果集进行查询时，数据发现丢失或新增。

然而锁定，就是为解决这些问题所生的，他的存在使得一个事务对他自己的数据块进行操作的时候，而另外一个事务则不能插足这些数据块。这就是所谓的锁定。

锁定从数据库系统的角度大致可以分为6种：

  • 共享锁（S）：还可以叫他读锁。可以并发读取数据，但不能修改数据。也就是说当数据资源上存在共享锁的时候，所有的事务都不能对这个资源进行修改，直到数据读取完成，共享锁释放。
  • 排它锁（X）：还可以叫他独占锁、写锁。就是如果你对数据资源进行增删改操作时，不允许其它任何事务操作这块资源，直到排它锁被释放，防止同时对同一资源进行多重操作。
  • 更新锁（U）：防止出现死锁的锁模式，两个事务对一个数据资源进行先读取在修改的情况下，使用共享锁和排它锁有时会出现死锁现象，而使用更新锁则可以避免死锁的出现。资源的更新锁一次只能分配给一个事务，如果需要对资源进行修改，更新锁会变成排他锁，否则变为共享锁。
  • 意向锁：SQL Server需要在层次结构中的底层资源上（如行，列）获取共享锁，排它锁，更新锁。例如表级放置了意向共享锁，就表示事务要对表的页或行上使用共享锁。在表的某一行上上放置意向锁，可以防止其它事务获取其它不兼容的的锁。意向锁可以提高性能，因为数据引擎不需要检测资源的每一列每一行，就能判断是否可以获取到该资源的兼容锁。意向锁包括三种类型：意向共享锁（IS），意向排他锁（IX），意向排他共享锁（SIX）。
  • 架构锁：防止修改表结构时，并发访问的锁。
  • 大容量更新锁：允许多个线程将大容量数据并发的插入到同一个表中，在加载的同时，不允许其它进程访问该表。

这些锁之间的相互兼容性，也就是，是否可以同时存在。 

 

现有的授权模式

 

 

 

 

 

请求的模式

IS

S

U

IX

SIX

X

意向共享 (IS)

是

是

是

是

是

否

共享 (S)

是

是

是

否

否

否

更新 (U)

是

是

否

否

否

否

意向排他 (IX)

是

否

否

是

否

否

意向排他共享 (SIX)

是

否

否

否

否

否

排他 (X)

否

否

否

否

否

否

锁兼容性具体参见：http://msdn.microsoft.com/zh-cn/library/ms186396.aspx

锁粒度和层次结构参见：http://msdn.microsoft.com/zh-cn/library/ms189849(v=sql.105).aspx

 死锁

什么是死锁，为什么会产生死锁。我用 “事务把死锁给整出来啦” 标题下的两个事务产生的死锁来解释应该会更加生动形象点。

例子是这样的：

第一个事务（称为A）：先更新lives表 --->>停顿5秒---->>更新earth表

第二个事务（称为B）：先更新earth表--->>停顿5秒---->>更新lives表

先执行事务A----5秒之内---执行事务B，出现死锁现象。

过程是这样子的：

  1. A更新lives表，请求lives的排他锁，成功。
  2. B更新earth表，请求earth的排他锁，成功。
  3. 5秒过后
  4. A更新earth，请求earth的排它锁，由于B占用着earth的排它锁，等待。
  5. B更新lives，请求lives的排它锁，由于A占用着lives的排它锁，等待。

这样相互等待对方释放资源，造成资源读写拥挤堵塞的情况，就被称为死锁现象，也叫做阻塞。而为什么会产生，上例就列举出来啦。

然而数据库并没有出现无限等待的情况，是因为数据库搜索引擎会定期检测这种状况，一旦发现有情况，立马选择一个事务作为牺牲品。牺牲的事务，将会回滚数据。有点像两个人在过独木桥，两个无脑的人都走在啦独木桥中间，如果不落水，必定要有一个人给退回来。这种相互等待的过程，是一种耗时耗资源的现象，所以能避则避。

哪个人会被退回来，作为牺牲品，这个我们是可以控制的。控制语法：

set deadlock_priority  <级别>

死锁处理的优先级别为 low<normal<high，不指定的情况下默认为normal，牺牲品为随机。如果指定，牺牲品为级别低的。

还可以使用数字来处理标识级别：-10到-5为low，-5为normal，-5到10为high。

减少死锁的发生，提高数据库性能

死锁耗时耗资源，然而在大型数据库中，高并发带来的死锁是不可避免的，所以我们只能让其变的更少。

  1. 按照同一顺序访问数据库资源，上述例子就不会发生死锁啦
  2. 保持是事务的简短，尽量不要让一个事务处理过于复杂的读写操作。事务过于复杂，占用资源会增多，处理时间增长，容易与其它事务冲突，提升死锁概率。
  3. 尽量不要在事务中要求用户响应，比如修改新增数据之后在完成整个事务的提交，这样延长事务占用资源的时间，也会提升死锁概率。
  4. 尽量减少数据库的并发量。
  5. 尽可能使用分区表，分区视图，把数据放置在不同的磁盘和文件组中，分散访问保存在不同分区的数据，减少因为表中放置锁而造成的其它事务长时间等待。
  6. 避免占用时间很长并且关系表复杂的数据操作。
  7. 使用较低的隔离级别，使用较低的隔离级别比使用较高的隔离级别持有共享锁的时间更短。这样就减少了锁争用。

可参考：http://msdn.microsoft.com/zh-cn/library/ms191242(v=sql.105).aspx

查看锁活动情况：

--查看锁活动情况

select * from sys.dm_tran_locks

--查看事务活动情况

dbcc opentran

可参考：http://msdn.microsoft.com/zh-cn/library/ms190345.aspx

为事务设置隔离级别

所谓事物隔离级别，就是并发事务对同一资源的读取深度层次。分为5种。

  • read uncommitted：这个隔离级别最低啦，可以读取到一个事务正在处理的数据，但事务还未提交，这种级别的读取叫做脏读。
  • read committed：这个级别是默认选项，不能脏读，不能读取事务正在处理没有提交的数据，但能修改。
  • repeatable read：不能读取事务正在处理的数据，也不能修改事务处理数据前的数据。
  • snapshot：指定事务在开始的时候，就获得了已经提交数据的快照，因此当前事务只能看到事务开始之前对数据所做的修改。
  • serializable：最高事务隔离级别，只能看到事务处理之前的数据。 

--语法

set tran isolation level <级别>

read uncommitted隔离级别的例子：

begin tran

  set deadlock_priority low

  update Earth set Animal='老虎'

  waitfor  delay '0:0:5' --等待5秒执行下面的语句

rollback tran

开另外一个查询窗口执行下面语句

set tran isolation level read uncommitted

select * from Earth  --读取的数据为正在修改的数据 ，脏读

waitfor  delay '0:0:5'  --5秒之后数据已经回滚

select * from Earth  --回滚之后的数据

 

read committed隔离级别的例子：

begin tran

  update Earth set Animal='老虎'

  waitfor  delay '0:0:10' --等待5秒执行下面的语句

rollback tran

set tran isolation level read committed

select * from Earth ---获取不到老虎，不能脏读

update Earth set Animal='猴子1'   --可以修改

waitfor  delay '0:0:10'  --10秒之后上一个事务已经回滚

select * from Earth  --修改之后的数据，而不是猴子

 

剩下的几个级别，不一一列举啦，自己理解吧。

设置锁超时时间

发生死锁的时候，数据库引擎会自动检测死锁，解决问题，然而这样子是很被动，只能在发生死锁后，等待处理。

然而我们也可以主动出击，设置锁超时时间，一旦资源被锁定阻塞，超过设置的锁定时间，阻塞语句自动取消，释放资源，报1222错误。

好东西一般都具有两面性，调优的同时，也有他的不足之处，那就是一旦超过时间，语句取消，释放资源，但是当前报错事务，不会回滚，会造成数据错误，你需要在程序中捕获1222错误，用程序处理当前事务的逻辑，使数据正确。

--查看超时时间,默认为-1

select @@lock_timeout

--设置超时时间

set lock_timeout 0 --为0时，即为一旦发现资源锁定，立即报错，不在等待，当前事务不回滚，设置时间需谨慎处理后事啊，你hold不住的。

 

二. 存储过程

存储过程简介

什么是存储过程：存储过程可以说是一个记录集吧，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。

存储过程的好处：

1.由于数据库执行动作时，是先编译后执行的。然而存储过程是一个编译过的代码块，所以执行效率要比T-SQL语句高。

2.一个存储过程在程序在网络中交互时可以替代大堆的T-SQL语句，所以也能降低网络的通信量，提高通信速率。

3.通过存储过程能够使没有权限的用户在控制之下间接地存取数据库，从而确保数据的安全。

小结：总之存储过程是好东西，在做项目时属于必备利器，下面介绍存储过程的基本语法。

存储过程的语法和参数讲解

存储过程的一些基本语法：

 

--------------创建存储过程-----------------

 

CREATE PROC [ EDURE ] procedure_name [ ; number ]

    [ { @parameter data_type }

        [ VARYING ] [ = default ] [ OUTPUT ]

    ] [ ,...n ]

 

[ WITH

    { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

 

[ FOR REPLICATION ]

 

AS sql_statement [ ...n ]

 

--------------调用存储过程-----------------

 

EXECUTE Procedure_name '' --存储过程如果有参数，后面加参数格式为：@参数名=value，也可直接为参数值value

 

--------------删除存储过程-----------------

 

drop procedure procedure_name    --在存储过程中能调用另外一个存储过程，而不能删除另外一个存储过程

 

创建存储过程的参数：
1.procedure_name ：存储过程的名称，在前面加#为局部临时存储过程，加##为全局临时存储过程。

2.; number：是可选的整数，用来对同名的过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。例如，名为 orders 的应用程序使用的过程可以命名为 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 语句将除去整个组。如果名称中包含定界标识符，则数字不应包含在标识符中，只应在 procedure_name 前后使用适当的定界符。 

3.@parameter： 存储过程的参数。可以有一个或多个。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有 2.100 个参数。 
使用 @ 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其它过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象的名称。有关更多信息，请参见 EXECUTE。 

4.data_type：参数的数据类型。所有数据类型（包括 text、ntext 和 image）均可以用作存储过程的参数。不过，cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor，也必须同时指定 VARYING 和 OUTPUT 关键字。有关 SQL Server 提供的数据类型及其语法的更多信息，请参见数据类型。 
说明 对于可以是 cursor 数据类型的输出参数，没有最大数目的限制。 

5.VARYING： 指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。 

6.default： 参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。默认值必须是常量或 NULL。如果过程将对该参数使用 LIKE 关键字，那么默认值中可以包含通配符（%、_、[] 和 [^]）。

7.OUTPUT ：表明参数是返回参数。该选项的值可以返回给 EXEC[UTE]。使用 OUTPUT 参数可将信息返回给调用过程。Text、ntext 和 image 参数可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以是游标占位符。 

8.RECOMPILE: 表明 SQL Server 不会缓存该过程的计划，该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时，请使用 RECOMPILE 选项。

9.ENCRYPTION: 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 语句文本的条目。使用 ENCRYPTION 可防止将过程作为 SQL Server 复制的一部分发布。 说明 在升级过程中，SQL Server 利用存储在 syscomments 中的加密注释来重新创建加密过程。 

10.FOR REPLICATION :指定不能在订阅服务器上执行为复制创建的存储过程。.使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。本选项不能和 WITH RECOMPILE 选项一起使用。 

11.AS :指定过程要执行的操作。

12.sql_statement :过程中要包含的任意数目和类型的 Transact-SQL 语句。但有一些限制。

小结：看过这些基本语法后，下面我就根据语法创建各式的存储过程。

 创建存储过程

 

UserAccount

UserID

UserName

PassWord

RegisterTime

RegisterIP

12

6                   

6                   

2012-12-31

6

18

5                   

5                   

2013-01-01

5

19

1                   

1                   

2013-01-01

1

20

2                   

2                   

2013-01-01

2

21

3                   

3                   

2013-01-01

3

22

4                   

4                   

2013-01-01

4

23

5                   

5                   

2013-01-01

5

25

7                   

7                   

2013-01-01

7

26

8                   

8                   

2013-01-01

8

NULL

NULL

NULL

NULL

NULL

针对上面的表，我使用存储过程对它做一些操作：

1. 只返回单一记录集的存储过程 

 

-------------创建名为GetUserAccount的存储过程----------------

create Procedure GetUserAccount

as

select * from UserAccount

go

 

-------------执行上面的存储过程----------------

exec GetUserAccount

 

 结果：相当于运行 select * from UserAccount 这行代码，结果为整个表的数据。

2.没有输入输出的存储过程 

 

-------------创建名为GetUserAccount的存储过程----------------

 

create Procedure inUserAccount

as

insert into UserAccount (UserName,[PassWord],RegisterTime,RegisterIP) values(9,9,'2013-01-02',9)

go

 

-------------执行上面的存储过程----------------

 

exec inUserAccount

 

 结果：相当于运行 insert into UserAccount (UserName,[PassWord],RegisterTime,RegisterIP) values(9,9,'2013-01-02',9) 这行代码。

3.有返回值的存储过程 

 

-------------创建名为GetUserAccount的存储过程----------------

 

create Procedure inUserAccountRe

as

insert into UserAccount (UserName,[PassWord],RegisterTime,RegisterIP) values(10,10,'2013-01-02',10)

return @@rowcount

go

 

-------------执行上面的存储过程----------------

 

exec inUserAccountRe

 

 解释：这里的@@rowcount为执行存储过程影响的行数，执行的结果是不仅插入了一条数据，还返回了一个值即 return value =1  ，这个可以在程序中获取，稍后在c#调用存储过程中会有说到。

4.有输入参数和输出参数的存储过程 

 

-------------创建名为GetUserAccount的存储过程----------------

 

create Procedure GetUserAccountRe

@UserName nchar(20),

@UserID int output

as

if(@UserName>5)

select @UserID=COUNT(*) from UserAccount where UserID>25

else

set @UserID=1000

go

 

-------------执行上面的存储过程----------------

 

exec GetUserAccountRe '7',null

 

 解释：@UserName为输入参数，@UserID为输出参数。 运行结果为@userID为COOUT（*）即 =1。

5. 同时具有返回值、输入参数、输出参数的存储过程 

 

-------------创建名为GetUserAccount的存储过程----------------

 

create Procedure GetUserAccountRe1

@UserName nchar(20),

@UserID int output

as

if(@UserName>5)

select @UserID=COUNT(*) from UserAccount where UserID>25

else

set @UserID=1000

return @@rowcount

go

 

-------------执行上面的存储过程----------------

 

exec GetUserAccountRe1 '7',null

 

 结果：@userID为COOUT（*）即 =1，Retun Value=1。

6.同时返回参数和记录集的存储过程 

 

-------------创建名为GetUserAccount的存储过程----------------

 

create Procedure GetUserAccountRe2

@UserName nchar(20),

@UserID int output

as

if(@UserName>5)

select @UserID=COUNT(*) from UserAccount where UserID>25

else

set @UserID=1000

select * from UserAccount

return @@rowcount

go

 

-------------执行上面的存储过程----------------

 

exec GetUserAccountRe2 '7',null

 

 结果：返回执行 select * from UserAccount 这句代码的结果集，同时@userID为COOUT（*）即 =1，Retun Value=9。 

7.返回多个记录集的存储过程 

 

-------------创建名为GetUserAccount的存储过程----------------

 

create Procedure GetUserAccountRe3

as

select * from UserAccount

select * from UserAccount where UserID>5

go

 

-------------执行上面的存储过程----------------

 

exec GetUserAccountRe3

 

 结果：返回两个结果集，一个为 select * from UserAccount，另一个为 select * from UserAccount where UserID>5 。

 

三. 游标

 

什么是游标

结果集，结果集就是select查询之后返回的所有行数据的集合。

游标则是处理结果集的一种机制吧，它可以定位到结果集中的某一行，多数据进行读写，也可以移动游标定位到你所需要的行中进行操作数据。

一般复杂的存储过程，都会有游标的出现，他的用处主要有：

  1. 定位到结果集中的某一行。
  2. 对当前位置的数据进行读写。
  3. 可以对结果集中的数据单独操作，而不是整行执行相同的操作。
  4. 是面向集合的数据库管理系统和面向行的程序设计之间的桥梁。

游标的分类

根据游标检测结果集变化的能力和消耗资源的情况不同，SQL Server支持的API服务器游标分为一下4种：

  • 静态游标: 静态游标的结果集，在游标打开的时候建立在TempDB中，不论你在操作游标的时候，如何操作数据库，游标中的数据集都不会变。例如你在游标打开的时候，对游标查询的数据表数据进行增删改，操作之后，静态游标中select的数据依旧显示的为没有操作之前的数据。如果想与操作之后的数据一致，则重新关闭打开游标即可。
  • 动态游标:这个则与静态游标相对，滚动游标时，动态游标反应结果集中的所有更改。结果集中的行数据值、顺序和成员在每次提取时都会变化。所有用户做的增删改语句通过游标均可见。如果使用API函数或T-SQL Where Current of子句通过游标进行更新，他们将立即可见。在游标外部所做的更新直到提交时才可见。
  • 只进游标：只进游标不支持滚动，只支持从头到尾顺序提取数据，数据库执行增删改，在提取时是可见的，但由于该游标只能进不能向后滚动，所以在行提取后对行做增删改是不可见的。
  • 键集驱动游标：打开键集驱动游标时，该有表中的各个成员身份和顺序是固定的。打开游标时，结果集这些行数据被一组唯一标识符标识，被标识的列做删改时，用户滚动游标是可见的，如果没被标识的列增该，则不可见，比如insert一条数据，是不可见的，若可见，须关闭重新打开游标。

静态游标在滚动时检测不到表数据变化，但消耗的资源相对很少。动态游标在滚动时能检测到所有表数据变化，但消耗的资源却较多。键集驱动游标则处于他们中间，所以根据需求建立适合自己的游标，避免资源浪费。。

游标的生命周期

游标的生命周期包含有五个阶段：声明游标、打开游标、读取游标数据、关闭游标、释放游标。

 1.声明游标，语法

 

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]

     [ FORWARD_ONLY | SCROLL ]

     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]

     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]

     [ TYPE_WARNING ]

     FOR select_statement

     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]

 

 

参数说明：

  • cursor_name：游标名称。
  • Local：作用域为局部，只在定义它的批处理，存储过程或触发器中有效。
  • Global：作用域为全局，由连接执行的任何存储过程或批处理中，都可以引用该游标。
  • [Local | Global]:默认为local。
  • Forward_Only:指定游标智能从第一行滚到最后一行。Fetch Next是唯一支持的提取选项。如果在指定Forward_Only是不指定Static、KeySet、Dynamic关键字，默认为Dynamic游标。如果Forward_Only和Scroll没有指定，Static、KeySet、Dynamic游标默认为Scroll，Fast_Forward默认为Forward_Only
  • Static:静态游标
  • KeySet：键集游标
  • Dynamic：动态游标，不支持Absolute提取选项
  • Fast_Forward：指定启用了性能优化的Forward_Only、Read_Only游标。如果指定啦Scroll或For_Update，就不能指定他啦。
  • Read_Only:不能通过游标对数据进行删改。
  • Scroll_Locks：将行读入游标是，锁定这些行，确保删除或更新一定会成功。如果指定啦Fast_Forward或Static，就不能指定他啦。
  • Optimistic：指定如果行自读入游标以来已得到更新，则通过游标进行的定位更新或定位删除不成功。当将行读入游标时，sqlserver不锁定行，它改用timestamp列值的比较结果来确定行读入游标后是否发生了修改，如果表不行timestamp列，它改用校验和值进行确定。如果已修改改行，则尝试进行的定位更新或删除将失败。如果指定啦Fast_Forward,则不能指定他。
  • Type_Warning:指定将游标从所请求的类型隐式转换为另一种类型时向客户端发送警告信息。
  • For Update[of column_name ,....] :定义游标中可更新的列。

2.声明一个动态游标

declare orderNum_02_cursor cursor scroll

for select OrderId from bigorder where orderNum='ZEORD003402'

3.打开游标

--打开游标语法

open [ Global ] cursor_name | cursor_variable_name

cursor_name:游标名，cursor_variable_name：游标变量名称，该变量引用了一个游标。

--打开游标

open orderNum_02_cursor

4.提取数据

 

--提取游标语法

Fetch

[ [Next|prior|Frist|Last|Absoute n|Relative n ]

from ]

[Global] cursor_name

[into @variable_name[,....]]

 

参数说明：

  • Frist：结果集的第一行
  • Prior：当前位置的上一行
  • Next：当前位置的下一行
  • Last：最后一行
  • Absoute n：从游标的第一行开始数，第n行。
  • Relative n：从当前位置数，第n行。
  • Into @variable_name[,...] : 将提取到的数据存放到变量variable_name中。

例子：

 

--提取数据

fetch first from orderNum_02_cursor

fetch relative 3 from orderNum_02_cursor

fetch next from orderNum_02_cursor

fetch absolute 4 from orderNum_02_cursor

fetch next from orderNum_02_cursor

fetch last from orderNum_02_cursor

fetch prior from orderNum_02_cursor

select * from bigorder where orderNum='ZEORD003402'

 

结果（对比一下，就明白啦）：

 

例子：

--提取数据赋值给变量

declare @OrderId int

fetch absolute 3 from orderNum_02_cursor into @OrderId

select @OrderId as id

select * from bigorder where orderNum='ZEORD003402'

结果：

 

通过检测全局变量@@Fetch_Status的值，获得提取状态信息，该状态用于判断Fetch语句返回数据的有效性。当执行一条Fetch语句之后，@@Fetch_Status可能出现3种值：0，Fetch语句成功。-1：Fetch语句失败或行不在结果集中。-2：提取的行不存在。

这个状态值可以帮你判断提取数据的成功与否。

 

declare @OrderId int

fetch absolute 3 from orderNum_02_cursor into @OrderId

while @@fetch_status=0  --提取成功，进行下一条数据的提取操作

 begin

   select @OrderId as id

   fetch  next from orderNum_02_cursor into @OrderId  --移动游标

 end

 

5.利用游标更新删除数据 

--游标修改当前数据语法

Update 基表名 Set 列名=值[,...] Where Current of 游标名

--游标删除当前数据语法

Delete 基表名  Where Current of 游标名

 

---游标更新删除当前数据

---1.声明游标

declare orderNum_03_cursor cursor scroll

for select OrderId ,userId from bigorder where orderNum='ZEORD003402'

--2.打开游标

open orderNum_03_cursor

--3.声明游标提取数据所要存放的变量

declare @OrderId int ,@userId varchar(15)

--4.定位游标到哪一行

fetch First from orderNum_03_cursor into @OrderId,@userId  --into的变量数量必须与游标查询结果集的列数相同

while @@fetch_status=0  --提取成功，进行下一条数据的提取操作

 begin

   if @OrderId=122182

     begin

     Update bigorder Set UserId='123' Where Current of  orderNum_03_cursor  --修改当前行

     end

   if @OrderId=154074

      begin

      Delete bigorder Where Current of  orderNum_03_cursor  --删除当前行

      end

   fetch next from orderNum_03_cursor into @OrderId ,@userId  --移动游标

 end 

 

6.关闭游标

 游标打开后，服务器会专门为游标分配一定的内存空间存放游标操作的数据结果集，同时使用游标也会对某些数据进行封锁。所以游标一旦用过，应及时关闭，避免服务器资源浪费。

--关闭游标语法

close [ Global ] cursor_name | cursor_variable_name

--关闭游标

close orderNum_03_cursor

7.删除游标

删除游标，释放资源

--释放游标语法

deallocate  [ Global ] cursor_name | cursor_variable_name

--释放游标

deallocate orderNum_03_cursor

 

 四.触发器

概念：

  触发器（trigger）是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。

触发器和存储过程的区别:

  触发器与存储过程的区别是运行方式的不同，触发器不能执行EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发执行而存储过程需要用户，应用程序或者触发器来显示地调用并执行。

回到顶部

一:触发器的优点

 1.触发器是自动的。当对表中的数据做了任何修改之后立即被激活。

 2.触发器可以通过数据库中的相关表进行层叠修改。

 3.触发器可以强制限制。这些限制比用CHECK约束所定义的更复杂。与CHECK约束不同的是，触发器可以引用其他表中的列。

回到顶部

二:触发器的作用

 触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂参照完整性和数据的一致性，它能够对数据库中的相关表进行级联修改，提高比CHECK约束更复杂的的数据完整性，并自定义错误消息。触发器的主要作用主要有以下接个方面：

  1. 强制数据库间的引用完整性
  2. 级联修改数据库中所有相关的表，自动触发其它与之相关的操作
  3. 跟踪变化，撤销或回滚违法操作，防止非法修改数据
  4. 返回自定义的错误消息，约束无法返回信息，而触发器可以
  5. 触发器可以调用更多的存储过程

回到顶部

三:触发器的分类

 SqlServer包括三种常规类型的触发器：DML触发器、DDL触发器和登录触发器。

1.DML(数据操作语言,Data Manipulation Language)触发器

 DML触发器是一些附加在特定表或视图上的操作代码，当数据库服务器中发生数据操作语言事件时执行这些操作。SqlServer中的DML触发器有三种：

  1. insert触发器:向表中插入数据时被触发；
  2. delete触发器：从表中删除数据时被触发；
  3. update触发器：修改表中数据时被触发。

当遇到下列情形时，应考虑使用DML触发器：

  1. 通过数据库中的相关表实现级联更改
  2. 防止恶意或者错误的insert、update和delete操作，并强制执行check约束定义的限制更为复杂的其他限制。
  3. 评估数据修改前后表的状态，并根据该差异才去措施。

2.DDL(数据定义语言,Data Definition Language)触发器

 DDL触发器是当服务器或者数据库中发生数据定义语言(主要是以create,drop,alter开头的语句)事件时被激活使用，使用DDL触发器可以防止对数据架构进行的某些更改或记录数据中的更改或事件操作。

3.登录触发器

    登录触发器将为响应 LOGIN 事件而激发存储过程。与 SQL Server 实例建立用户会话时将引发此事件。登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。因此，来自触发器内部且通常将到达用户的所有消息（例如错误消息和来自 PRINT 语句的消息）会传送到 SQL Server 错误日志。如果身份验证失败，将不激发登录触发器。

回到顶部

四：触发器的工作原理

触发器触发时：

  1. 系统自动在内存中创建deleted表或inserted表；
  2. 只读，不允许修改，触发器执行完成后，自动删除。

inserted表：

  1. 临时保存了插入或更新后的记录行；
  2. 可以从inserted表中检查插入的数据是否满足业务需求；
  3. 如果不满足，则向用户发送报告错误消息，并回滚插入操作。　

deleted表：

  1. 临时保存了删除或更新前的记录行；
  2. 可以从deleted表中检查被删除的数据是否满足业务需求；
  3. 如果不满足，则向用户报告错误消息，并回滚插入操作。

inserted表和deleted表对照：　

修改操作记录

inserted表

deleted表

增加(insert)记录

存放新增的记录

............

删除(deleted)记录

..............

存放被删除的记录

修改(update)记录

存放更新后的记录

存放更新前的记录

 

 

 

 

回到顶部

五:创建触发器

 创建触发器的语法： 

 

CREATE TRIGGER trigger_name

 ON table_name

 [WITH ENCRYPTION]

  FOR | AFTER | INSTEAD OF [DELETE, INSERT, UPDATE]

 AS

  T-SQL语句

GO

--with encryption 表示加密触发器定义的sql文本

--delete,insert,update指定触发器的类型

 

 准备测试数据:

 

--创建学生表

create table student(

    stu_id int identity(1,1) primary key,

    stu_name varchar(10),

    stu_gender char(2),

    stu_age int

)

 

1.创建insert触发器

 

--创建insert触发器

create trigger trig_insert

on student

after insert

as

begin

    if object_id(N'student_sum',N'U') is null--判断student_sum表是否存在

        create table student_sum(stuCount int default(0));--创建存储学生人数的student_sum表

    declare @stuNumber int;

    select @stuNumber = count(*)from student;

    if not exists (select * from student_sum)--判断表中是否有记录

        insert into student_sum values(0);

    update student_sum set stuCount =@stuNumber; --把更新后总的学生数插入到student_sum表中

end

 

 

--测试触发器trig_insert-->功能是向student插入数据的同时级联插入到student_sum表中，更新stuCount

--因为是后触发器，所以先插入数据后，才触发触发器trig_insert;

insert into student(stu_name,stu_gender,stu_age)values('吕布','男',30);

select stuCount 学生总人数 from student_sum;   

insert into student(stu_name,stu_gender,stu_age)values('貂蝉','女',30);           

select stuCount 学生总人数 from student_sum;

insert into student(stu_name,stu_gender,stu_age)values('曹阿瞒','男',40);               

select stuCount 学生总人数 from student_sum;

 

执行上面的语句后，结果如下图所示：

 

 既然定义了学生总数表student_sum表是向student表中插入数据后才计算学生总数的，所以学生总数表应该禁止用户向其中插入数据

 

--创建insert_forbidden,禁止用户向student_sum表中插入数据

create trigger insert_forbidden

on student_sum

after insert

as

begin

    RAISERROR('禁止直接向该表中插入记录，操作被禁止',1,1)--raiserror 是用于抛出一个错误

rollback transaction

end

 

--触发触发器insert_forbidden

insert student_sum (stuCount) values(5);

结果如下：

 

 2.创建delete触发器

　　用户执行delete操作，就会激活delete触发器，从而控制用户能够从数据库中删除数据记录，触发delete触发器后，用户删除的记录会被添加到deleted表中，原来表的相应记录被删除，所以在deleted表中查看删除的记录。

 

--创建delete触发器

create trigger trig_delete

on student

after delete

as

begin

    select stu_id as 已删除的学生编号,stu_name stu_gender,stu_age

    from deleted

end;

 

--执行一一条delete语句触发trig_delete触发器

delete from student where stu_id=1;

结果如下：

 

 3.创建UPDATE触发器

　　update触发器是当用户在指定表上执行update语句时被调用被调用，这种类型的触发器用来约束用户对数据的修改。update触发器可以执行两种操作：更新前的记录存储在deleted表中，更新后的记录存储在inserted表中。

 

--创建update触发器

create trigger trig_update

on student

after update

as

begin

    declare @stuCount int;

    select @stuCount=count(*) from student;

    update student_sum set stuCount =@stuCount;

    select stu_id as 更新前学生编号,stu_name as 更新前学生姓名 from deleted

    select stu_id as 更新后学生编号,stu_name as 更新后学生姓名 from inserted

end

 

--创建完成，执行一条update语句触发trig_update触发器

update student set stu_name='张飞' where stu_id=2;

 

 4.创建替代触发器

　　与前面介绍的三种after触发器不同，SqlServer服务器在执行after触发器的sql代码后，先建立临时的inserted表和deleted表，然后执行代码中对数据库操作，最后才激活触发器中的代码。而对于替代(instead of)触发器,SqlServer服务器在执行触发instead of 触发器的代码时，先建立临时的inserted表和deleted表，然后直接触发instead of触发器，而拒绝执行用户输入的DML操作语句。

 

--创建instead of 触发器

create trigger trig_insteadOf

on student

instead of insert

as

begin

    declare @stuAge int;

    select @stuAge=(select stu_age from inserted)

if(@stuAge >120)

    select '插入年龄错误' as '失败原因'

end

 

创建完成，执行一条insert语句触发触发器trig_insteadOf

 

5.嵌套触发器介绍

　如果一个触发器在执行操作时调用了另外一个触发器，而这个触发器又接着调用了下一个触发器，那么就形成了嵌套触发器。嵌套触发器在安装时就被启用，但是可以使用系统存储过程sp_configure禁用和重新启用嵌套触发器。

 

　　嵌套触发器不一定要形成一个环，它可以 T1->T2->T3...这样一直触发下去，最多允许嵌套 32 层。如果嵌套的次数超过限制，那么该触发器将被终止，并回滚整个事务，使用嵌套触发器需要注意以下几点：

  • 默认情况下，嵌套触发器配置选项是开启的。
  • 在同一个触发器事务中，一个嵌套触发器不能被触发两次。
  • 由于触发器是一个事务，如果在一系列嵌套触发器的任意层次中发生错误，则整个事物都将取消，而且所有数据回滚。

嵌套是用来保持整个数据库的完整性的重要功能，但有时可能需要禁用嵌套，如果禁用了嵌套，那么修改一个触发器的实现不会再触发该表上的任何触发器。在下述情况下，需要禁用嵌套触发器：

  • 嵌套触发要求复杂而有理论的设计，级联修改可能会修改用户不想涉及的数据。
  • 在一系列嵌套触发器中的任意点的时间修改操作都会触发一些触发器，尽管这时数据库提供很强的保护功能，但如果以特定的顺序更新表，就会产生问题。

使用下列语句禁用嵌套和再次启用嵌套：

--禁用嵌套

exce sp_configure 'nested triggers',0;

--启用嵌套

exce sp_configure 'nested triggers',1;

6.递归触发器

　　触发器的递归是指一个触发器从其内部再一次激活该触发器，例如update操作激活的触发器内部还有一条数据表的更新语句，那么这个更新语句就有可能激活这个触发器本身，当然，这种递归的触发器内部还会有判断语句，只有一定情况下才会执行那个T_SQL语句，否则就成为无线调用的死循环了。

SqlServer中的递归触发器包括两种：直接递归和间接递归。

  • 直接递归：触发器被触发后并执行一个操作，而该操作又使用一个触发器再次被触发。
  • 间接递归：触发器被触发并执行一个操作，而该操作又使另一个表中的某个触发器被触发，第二个触发器使原始表得到更新，从而再次触发第一个触发器。

默认情况下，递归触发器选项是禁用的。递归触发器最多只能递归16层，如果递归中的第16个触发器激活了第17个触发器，则结果与发布的rollback命令一样，所有数据都将回滚。 

我们举例解释如下，假如有表1、表2名称分别为 T1、T2，在 T1、T2 上分别有触发器 G1、G2。

  • 间接递归：对 T1 操作从而触发 G1，G1 对 T2 操作从而触发 G2，G2 对 T1 操作从而再次触发 G1...
  • 直接递归：对 T1 操作从而触发 G1，G1 对 T1 操作从而再次触发 G1... 

设置直接递归：

默认情况下是禁止直接递归的，要设置为允许有两种方法：

  • T-SQL：exec sp_dboption 'dbName', 'recursive triggers', true；
  • EM：数据库上点右键->属性->选项。 

回到顶部

六:管理触发器　

1.查看触发器

(1).查看数据库中所有的触发器

--查看数据库中所有的触发器

use 数据库名

go

select * from sysobjects where xtype='TR'

sysobjects 保存着数据库的对象，其中 xtype 为 TR 的记录即为触发器对象。在 name 一列，我们可以看到触发器名称。

(2).sp_helptext 查看触发器内容

use 数据库名

go

exec sp_helptext '触发器名称'

 将会以表的样式显示触发器内容。 

 除了触发器外，sp_helptext 还可以显示 规则、默认值、未加密的存储过程、用户定义函数、视图的文本。

(3).sp_helptrigger 用于查看触发器的属性

　　sp_helptrigger 有两个参数：第一个参数为表名；第二个为触发器类型，为 char(6) 类型，可以是 INSERT、UPDATE、DELETE，如果省略则显示指定表中所有类型触发器的属性。

use 数据库名

go

exec sp_helptrigger tableName

2.禁用启用触发器

　　禁用:alter table 表名 disable trigger 触发器名称
　　启用:alter table 表名 enable trigger 触发器名称

　　如果有多个触发器，则各个触发器名称之间用英文逗号隔开。

　　如果把“触发器名称”换成“ALL”，则表示禁用或启用该表的全部触发器。

3修改触发器

 

--修改触发器语法

ALTER TRIGGER  trigger_name

     ON  table_name

     [ WITH ENCRYPTION ]

     FOR {[DELETE][,][INSERT][,][UPDATE]}

     AS

       sql_statement;

 

4.删除触发器

 --语法格式:

      DROP  TRIGGER   { trigger } [ ,...n ]

参数:

 trigger: 要删除的触发器名称

 n:表示可以删除多个触发器的占位符      

 

 

 

posted @ 2018-12-11 11:17  linjierd  阅读(507)  评论(0)  编辑  收藏  举报