SQLServer_第8章 存储过程和触发器

第8章 存储过程和触发器

8.1 存储过程

存储过程就是定义在数据库中的子程序，是数据库对象之一。

使用存储过程的优点有：

1）  在服务器端执行，速度快；

2）  执行一次后，其执行规划就驻留在高速缓冲存储器，以后再次执行，就直接运行已经编译好的二进制代码，提高了系统性能；

3）  确保数据库的安全；

4）  自动完成需要预先完成的任务。

8.1.1 存储过程的类型

1）系统存储过程：这些存储过程存储在master数据库中，其前缀是sp_。它允许系统管理员执行修改系统表的数据库管理任务，可以在任何一个数据库中执行。

2）本地存储过程：是指在用户数据库中创建的存储过程，完成特定的数据库操作任务，其前缀不能是sp_；

3）临时存储过程：属于本地存储过程。如果存储过程名称前面有“＃”，说明其是局部临时存储过程，如果其前面有“＃＃”，说明其是全局临时存储过程；】

4）远程存储过程：从远程服务器上调用；

5）扩展存储过程：在SQLServer环境之外执行的动态链接库称为扩展存储过程，其前缀是sp_。使用时需要先加载到SQLServer系统中，并且按照使用存储过程的方法执行。

8.1.2 用户存储过程的创建和执行

1．创建存储过程

Create procedure procedure_name[;number]

[{@parameter data_type}

[varying][=default][output]

[,…n]

[with {reconpile |  encryption | recompile,encryption}]

[for replication]

As sql_statement[,….n]

注意：

1）  用户自定义的存储过程只能在当前数据库中创建，临时过程总是在tempdb中创建；

2）  过程名称存储在sysobjects系统表中，而其定义的文本存储在syscomments中；

3）  自动执行的存储过程必须有系统管理员在master中创建，并在sysadmin下作为后台过程执行，且不能有任何参数；

4）  存储过程的定义不能跨越批处理；

5）  可以使用with encryption 选项对用户隐藏存储过程的文本；

6）  通过系统存储过程sp_helptext可显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图文本。如：exec sp_helptext xs_cursor。

例子：没有参数的存储过程

/*检查是否已经存在同名的存储过程*/

if exists(select name from sysobjects where name='student_info' and type='p')

       drop procedure student_info

go

/*创建存储过程*/

create procedure student_info

as

       select xs.stu_id,xs.stu_name,kc.kc_name,xs_kc.score,kc_credit

       from xs inner join xs_kc on (xs.stu_id=xs_kc.stu_id)

       inner join kc on (xs_kc.kc_id=kc.kc_id)

go

/*执行该存储过程*/

exec student_info

例子：使用带参数的存储过程

/*检查是否已经存在同名的存储过程*/

if exists(select name from sysobjects where name='student_info1' and type='p')

       drop procedure student_info1

go

create proc student_info1

@name char(8),@cname char(16)

as

       select xs.stu_id,xs.stu_name,kc.kc_name,xs_kc.score,kc_credit

       from xs inner join xs_kc on (xs.stu_id=xs_kc.stu_id)

       inner join kc on (xs_kc.kc_id=kc.kc_id)

       where xs.stu_name like @name and kc.kc_name like @cname

go

例子：使用带有通配符参数的存储过程

/*检查是否已经存在同名的存储过程*/

if exists(select name from sysobjects where name='student_info1' and type='p')

       drop procedure student_info1

go

create proc student_info1

@name varchar(30)='王%'

as

       select @name as name1

       select xs.stu_id,xs.stu_name,kc.kc_name,xs_kc.score,kc_credit

       from xs inner join xs_kc on (xs.stu_id=xs_kc.stu_id)

       inner join kc on (xs_kc.kc_id=kc.kc_id)

       where xs.stu_name like @name

go

例子：使用带output参数的存储过程

/*用于计算指定学生的总学分*/

if exists(select name from sysobjects where name='total_credit' and type='p')

       drop procedure total_credit

go

create proc total_credit

@name varchar(40),@total int output

as

       select @total=sum(kc.kc_credit) from xs inner join xs_kc on (xs.stu_id=xs_kc.stu_id)

       inner join kc on (xs_kc.kc_id=kc.kc_id)

       where stu_name like @name  and score>=60

       group by xs.stu_id

declare @t_credit char(20),@temp int

exec total_credit '王_%',@temp output

select @temp

例子：使用output游标参数的存储过程

/*output游标用于返回存储过程的局部游标

*在xs表上声明并打开一个游标*/

if exists (select name from sysobjects where name='xs_cursor'and type='p')

       drop procedure xs_cursor

go

create procedure xs_cursor @xs_cursor cursor varying output

as

       set @xs_cursor=cursor forward_only static

       for

              select * from xs

open @xs_cursor

go

例子：/*调用上述的存储过程*/

declare @mycursor Cursor

exec xs_cursor @xs_cursor=@mycursor output

while (@@fetch_status=0)

       begin

              fetch next from @mycursor

       end

close @mycursor

deallocate @mycursor

例子：/*用户自定义的系统存储过程

*创建一个过程，显示表名以xs开头的所有表及其对应的索引。如果，没有指定参数，该过程将返回表名以kc开头的所有表及对应的索引*/

if exists(select name from sysobjects where name='sp_showtable' and type='p')

       drop proc sp_showtable

go

use master

go

create proc sp_showtable @table varchar(30) ='kc%'

as

       select tab.name as table_name,

              inx.name as index_name,

              indid as index_id

       from sysindexes inx inner join sysobjects tab on tab.id=inx.id

       where tab.name like @table

go

use xscj

exec sp_showtable 'xs%'

2．存储过程的修改和删除

Alter和drop命令 略

8.2 触发器

触发器是一类特殊的存储过程，用于保护表中的数据，当有操作影响到触发器保护的数据时，触发器自动执行。一般情况下，对表数据的操作有插入、修改、删除，因而触发器也可以分为三种类型：insert、update、delete。

同一个表中可使用多个触发器，即使是同一个类型的触发器，也可以使用多个。

使用触发器能够方便实现数据库中数据的完整性。还可以使一些处理任务自动进行。例如，在销售系统中，通过更新触发器可以检测什么时候库存量下降到需要再进货的量，并自动生成给供货商的量。

8.2.1 语法格式

Create trigger trigger_name on  {table |view}

[with encryption]

{ {for | after | insteade of }  {[ delete],[insert],[update]}}

[not for replication]

As

       [

              {

 if update(column)[ {and | or} update (column)][,…n]

              | if (columns_updated() {bitwise_operate} updated_bitmask)

                     {comparision_operator}column_bitmask[,…n]

              }

       ]

       Sql_statement[,…n]

}}

注意：

1）  after用于说明触发器在指定操作都成功执行后触发，是默认设置。不能在在视图上建立after触发器。

2）  instead of  触发器指定用触发器中的操作代替触发语句的操作，在表或视图上，每个insert、delete、update语句最多定义一个instead of触发器。

3）  如果触发器表存在约束，则在instead of执行后，在after执行前检查约束。如果不符合约束，则回滚instead of事务或不执行after触发器。

8.2.2 触发器中使用的特殊表

Inserted逻辑表和deleted逻辑表

8.2.3 使用触发器的限制

1）create trigger必须是批处理中的第1条语句，并且只能应用到1个表中；

2）触发器只能在当前的数据库中创建，但可以引用当前数据库的外部对象；

3）如果指定触发器所有者名限定触发器，要以相同的方式限定表名；

4）在同一create trigger语句中，可以为多种操作定义相同的触发器操作；

5）如果一个表的外键在delete、update操作上定义了级联，则不能在该表上定义instead of delete （update）触发器；

6）在触发器中可以指定任意set语句，所选择的set选项在触发器执行期间有效，执行完之后恢复到以前的设置；

7）触发器中不允许有对数据库、日志等一些操作的SQL语句；

8）触发器不返回任何结果，为了阻止从触发器返回结果，不要在触发器定义中包含select语句或变量赋值。如果必须在触发器中进行变量赋值，则要在触发器开头使用

Set nocount 语句，以避免返回任何结果集。

8.2.4 权限

默认授予表的所有者，sysadmin、db_owner db_ddladmin ，并且不可转让。

8.2.5 举例

例子：/*如果在xs表中添加或更改数据，则将向客户端显示一条信息

*使用带消息提示的触发器*/

use xscj

go

if exists ( select name from sysobjects where name='xs_reminder' and type='tr')

       drop trigger xs_reminder

go

create alter

 trigger xs_reminder on xs

       for insert,update

       as RAISERROR('sucess!',16,10) /*也可以使用数字如4008用户定义的消息，可以使用sp_addmessage创建*/

go

例子：/*当向xs_kc表中插入一个记录时，检查该记录的学号在xs表是否存在，检查课程号在kc表中是否存在，如果有一项不存在，则不允许插入*/

if exists(select name from sysobjects where name='xs_kc_check_trig' and type='tr')

       drop trigger xs_kc_check_trig

go

create trigger xs_kc_check_trig on xs_kc

for insert

as

       if exists (select *

       from inserted a

       where a.stu_id not in (select b.stu_id from xs b) or

       a.kc_id not in (select c.kc_id from kc c))

       begin

              raiserror('违背了数据的一致性',16,1)

              rollback transaction

       end

例子：/*在xs_kc表上建立一个触发器，如果对学号列和课程号列修改，则给出提示，并取消修改操作*/

create trigger xs_kc_update_trig on xs_kc

for update

as

       if (columns_updated() &3 >0) /* columns_updated()函数可快速测试对表列的修改*/

              begin

                     raiserror('违背了数据的一致性',16,1)

                     rollback transaction

       end

8.2.6 instead of触发器的设计

如果视图的数据来自于多个基表，则必须使用instead of触发器来支持引用表中数据的插入、修改和删除。

如果视图的列为以下几种情况时，该视图的insert语句必须为这些列指定值：

1）  基表的计算列

2）  基本中的标识列；

3）  具有timestamp数据类型的基表列。

Instead of触发器在构成将值插入基表的insert语句时会忽略指定的值。

/*说明instead of insert触发器的使用*/

use xscj

create table books

(BookKey int identity(1,1)

,BookName nvarchar(10) not null

,Color nvarchar(10) not null

,ComputedCol as (BookName+Color)

,Pages int)

go

/*建立一个视图*/

create view view_test

as

       select * from books

go

create trigger InsteadTrig on view_test

instead of insert

as

       begin

              /*实际插入时，insert语句中不包含BookKey和ComputedCol字段的值*/

       insert into books

       select BookName,Color,Pages from inserted

       end

go

/*意义说明：

*直接引用books表的insert语句不能为BookKey字段和ComputedCol字段提供值，如：

*insert into books values(2,'计算机辅助设计','红色','lvshe',100)就是错误的语句；

而insert into books values('计算机辅助设计','红色',100)才是正确的语句；

*但对于引用视图的insert语句，使用：

*insert into view_test values(2,'计算机辅助设计','红色','lvshe',100)也是正确的语句；

*insert into view_test values('计算机辅助设计','红色',100)确是错误的语句

*/