第8  存储过程与触发器

  存储过程和触发器是由一系列的"Transact-SQL"语句组成的子程序,用来满足更高的应用需求,触发器也是一种存储过程,它是一种在基本表被修改时自动执行的内嵌过程,它主要是通过事件进行触发而被执行,而存储过程可以通过存储过程的名字被直接调用。

8.1存储过程概述

8.1.1什么是存储过程

  当开发一个应用程序时,为了易于修改和扩充,常将负责不同功能的语句集中起来而且按照用途分别独立放置,以便能够反复调用,而这些独立放置且拥有不同功能的语句,即是“过程”(Procedure)

  存程过程作用就类似于这样一个“过程”

  存储过程:包含一些Transact-SQL语句并以特定的名称存储在数据库中(它是一种数据对象)。

8.1.2 存储过程的类型

1.系统存储过程(system stored procedure)

  它主要存储在master数据库中,并以sp_为前缀,如:sp_helptext。

  如果过程以sp_开始,又在当前数据库(如在用户数据库执行,则先在用户数据库中找)找不到。则SQL就在master数据库中寻找。
  以sp_前缀命名的过程中引用的表如果不能在当前数据库中解析出来,将在master数据库查找。

2.本地存储过程(Local Stored Procedures)
 
  它是用户自行创建并存储在用户数据库中的存储过程。

3.临时存储过程(Temporary Stored Procedures)

1>本地临时存储过程
  以#为名称的第一个字符,则该存储过程将被存放在tempdb数据库中的本地临时存储过程。
  eg: create procedure #book_proc...)

注:只有创建它有用户可以执行它,并当该用户断SQL时,会自动被删除。


2>全局临时存储过程
 以##为名称开头的存储过程名称,则它将成为一个存储在tempdb数据库中的全局临时存储过程.
 eg:create procedure ##book_proc...
 
注:任意用户可执行它,当一用户断开时,SQL会查询有无其它用户使用,若有,则保留,直到所有用户断开,则才被自动删除。但当有用户断开后,该存储过程将不能再重新执行。


4.远程存储过程(Remote Stord Procedure)
 
   位于远程服务器上的存储过程。可使用分布式查询和EXECUTE命令执行。

5.扩展存储过程(Extended Stored Procedures)
 
  它是用户使用外部程序语言编写的存储过程。它以xp_开头,它是以动态链接库(DLLS)的形式存在。
它一定要存储在系统数据库master中.


8.1.4存储过程与视图的比较

(1)可以在单个存储中执行一系统Transact_SQL语句,而在视图中只能是SELECT语句。
(2)视图不能接受参数,只能返回结果集;而存储过程可以接受参数(输入,输出参数),并能返回单个或多个结果集以及返回值。



8.2 创建和执行存储过程

1.创建存储过程

CREATE PROCEDURE procedure_name
[WITH ENCRYPTION]
[WITH RECOMPILE]
AS
sql_statement

其中:
WITH ENCRYPTION:对存储过程进行加密。
WITH RECOMPILE: 对存储过程重新编译。

例1 在book数据库中创建一个名为p_book1的存储过程,该“存储过程”返回book1表中所有出版社为“中国长安”的记录。

use book
go
create procedure p_book1
as
select * from book1 where 出版社='中国长安'

2.执行存储过程。
EXEC procedure_name


例句:
if exists(select * from sysobjects where name='proc_stu')
  drop procedure proc_stu
go
create procedure proc_stu
as
declare @avgage float;
select @avgAge=avg(stuage) from stuinfo
 if (@avgage>20)
   begin
    print '平均年龄大于岁,年龄最大的为'
    select top 1 stuname from stuinfo order by stuage desc
   end
 else
   begin
    print '平均年龄不到岁,年龄最小的为'
    select top 1 stuname from stuinfo order by stuage
   end

3.查看“存储过程”

在“对象资源管理器"--BOOK--可编程性---可以看到p_book1的存储过程了(看不到刷新一下)



4.带参数的存储过程

1>带输入参数的存储过程

语法格式:
CREATE PROCEDURE procedure_name
@parameter_name datatype=[default]
[with encryption]
[with recompile]
as
sql_statement

其中:
(1)@parameter_name:存储过程的参数名,必须以符号@为前缀.
(2)Datatype:参数的数据类型。
(3)Default:参数的默认值。

例4 在book数据库中创建一个名为p_booklp的带参数存储过程,根据出版社(变量)来查对应的记录。

create procedure p_booklp
@出版社 varchar(20)                        #把“出版社”变量定义为20位长的字符串#
as
select * from book1 where 出版社=@出版社

2>执行含有输入参数的存储过程

1)使用参数名传递参数值

语法格式:
EXEC procedure_name
[@parameter_name=value]
[,...n]

例5 执行存储过程p_booklp,分别查询出版社为“中国长安“和”安微人民“书的记录。

EXEC p_booklp @出版社='中国长安'
go
EXEC p_booklp @出版社='安微人民'
go

例:
创建带输入参数的存储过程
  if exists(select * from sysobjects where name='proc_stu2')
  drop procedure proc_stu2
go
create procedure proc_stu2
@age int
as
declare @avgage float;
select @avgAge=avg(stuage) from stuinfo
 if (@avgage>@age)
   begin
    print '平均年龄大于'+convert(varchar,@age)+'岁,年龄最大的为'
    select top 1 stuname from stuinfo order by stuage desc
   end
 else
   begin
    print '平均年龄不到'+convert(varchar,@age)+'岁,年龄最小的为'
    select top 1 stuname from stuinfo order by stuage
   end
exec proc_stu2 22

2)按位置传递参数值
 
  在执行存储过程的语句中,不通过参数传递参数值而直接给出参数的传递值。

注意:当存储过程含有多个输入参数时,传递值的顺序必须与存储过程中定义的输入顺序相一致。按位置传递参数时,也可以忽略空值和具有默认值的参数,但不能因此破坏输入参数的设定顺序。

  比如:在一个含有4个参数的存储过程中,用户可以忽略第3和第4个参数。但无法在忽略第3个参数的情况下而指定第4个参数的输入值。

语法格式:
EXEC procedure_name
[value1,value2,....]

例6 按位置传递参数值的方法执行存储过程p_booklp,分别查找出版社为“中国人口"和“内蒙人民”书的记录。

EXEC p_booklp '内蒙人民'
go
EXEC p_booklp '中国人口'
go


3>带输出参数的存储过程

语法格式:
@parameter_name datatype=[default] OUTPUT


例7 创建存储过程p_book1Num,要求能根据用户给定的出版社,统计该出版社的书数量,并将数量以输出变量的形式返回给用户。

CREATE PROCEDURE p_booklNum
@出版社 varchar(20), @booklNum smallint output
as
set @booklUnm
(
select count(*) from book1
where 出版社=@出版社
)
print @booklNum


例8 执行存储过程p_booklNum
  由于p_booklNum使用了两个参数,故要先定义相应的变量,@出版社要赋值,而输入参数@booklNum无需赋值,它从存储过程中获得。

DECLARE @出版社 varchar(20),@booklNum smallint
set @出版社='中国长安'
EXEC p_booklNum @出版社,@booklNum


8.3 修改,删除,重命名存储过程

1.存储过程的修改

语法格式:

ALTER PROCEDURE procedure_name
[WITH ENCRYPTION]
[WITH ENCOMPILE]
AS
Sql_statement

例9 修改存储过程p_booklp,根据用户提供“出版社名称”进入模糊查询,并要求加密。

ALTER PROCEDURE p_booklp
@出版社VARCHAR(20)
WITH encryption
AS
SELECT 出版社,ISBN号,定价,作者姓名
from book1,teacher
WHERE book1.编号=teacher.编号 and 出版社 like '%@出版社%'

------------p_booklp原内容--------------------------
create procedure p_booklp
@出版社 varchar(20)                        #把“出版社”变量定义为20位长的字符串#
as
select * from book1 where 出版社=@出版社
----------------------------------------------------


2.存储过程的删除
 
  通过DROP来删除.

例10 删除存储过程p_book1

use book
go
drop procedure p_book1


3.存储过程的重命名

  使用SQL界面操


8.4存储过程的重编译处理

  数据库被索引或会影响数据库统计的更改后,会降低已编译的存储过程的效率,则需要通过对存储过程进行重新编译,可以重新优化查询。

SQL提供了3种重新编译方法

1.在创建存储过程时使用WITH RECOMPILE子句

例13 在每次执行时重新编译和优化。

use book
go
create procedure p_booklp
@出版社 varchar(20)
WITH recompile
as
select * from book1 where 出版社=@出版社

2. 在执行存储过程时设定重新编译选项

语法格式:

EXECUTE procedure_name WITH RECOMPILE

例14 以重新编译的方式执行存储过程p_booklp

use book
go
EXECUTE p_booklp '中国长安' with RECOMPILE

3.通过系统存储过程设定重新编译选项

语法格式:
EXEC sp_recomile OBJECT

其中
OBJECT:当前数据库中的存储过程、表或视图的名称。

例15 执行下面的语句将导致book1表的触发器和存储过程下次运行时将被重新编译.

EXEC sp_recompile book



8.5 解发器的创建和管理

1.触发器的基本概述
 
  SQL中,存储过程和触发器都是SQL语句和流程控制语句的集合。本质而言,触发器也是一种存储过程。它是一种在基本表被修改(insert,update,delect等)时自动执行的内嵌过程。 主要通过事件进行触发而被执行,而存储过程可以通过存储过程名而被直接调用。

在执行触发器时,系统会自动创建两张临时表inserted,deleted,这两张表的结构与触发器所依赖的表类似,用于保存在用户操作过程中被插入或被删除的数据。在执行结束后两个临时表会自动被系统删除。


2.触发器的创建

语法格式:
CREATE RTIGGER trigge_name
ON(table|view)
{FOR | AFTER | INSTEAD OF} {[INSERT],[UPDATE],[DELETE]}
[WITH ENCRYPTION]
AS
IF UPDATE(contumn_name)
[(and | or) UPDATE (column_name)...]
sql_statesments

其中:
(1)trigge_name:触发器的名称.
(2)table | view: 执行触发器的表或视图。
(3)after: 指在对表的相关操作正常操作后,触发器被触发。
(4)INSTEAD OF:指定执行触发器而不是执行触发语句,从而替代触发语句的操作。
(5){[INSERT],[UPDATE],[DELETE]}:指在表或视图上执行哪些数据修改语句时激活触发器的关键字。
(6)ENCRYPTION:加密含有CREATE TRIGGER语句正文文本的syscomments项.
(7)sql_statesments:定义触发器被触发后,将执行数据库操作。
(8)IF UPDATE:指定对表内某列做增加或修改内容时,触发器才起作用,IF子句中多个触发器可以放在BEGIN和END之间.


1.insert触发器

例16 在book数据库的book1表上创建一个book1_triggerl触发器,当执行insert操作时,该触发器被触发。

use book
go
create trigger book1_trigger1
on book1
for insert
as
print '数据插入成功'
go

如当我们执行:
insert into book1
values('YBZT2411','7500433921')

则我们发现数据被插入到book1中了,原因是我们使用的是FOR选项,它会在SQL语句(insert)执行完后,触发器才被激发。

如果想在SQL语句(insert)执行之前激活触发器,并取消SQL语句呢?这我们就要用到instead of关键字来实现了。


例17 在book中的book1表上创建一个book1_trigger2触发器,当执行DELETE操作时触发器被触发,并要求触发触发器的DELETE语句在执行后被取消,即删除不成功.

use book
go
create trigger book1_trigger2
on book1
instead of delete
as
print '数据删除不成功'
go

我们来测试一下
delete
from book1
where 编号='YBZT2411'

下面结果栏中显示我们的设定'数据删除不成功'
use book
select *
from book1

我们也发现 编号='YBZT2411'的数据没有被删。

------------
use test
go
if exists(select * from sys.triggers where name = 'insert_sco')
 drop trigger insert_sco
go
create trigger insert_sco on scores
instead of insert
 as
    if exists (select * from inserted where stuno in (select stuno from stuinfo))
       print'添加成功'
    else
    begin
       print'添加失败'
       rollback transaction
end
-----------


2.UPDATE触发器
 
  使用UPDATE触发器时,用户可以通过定义IF UPDATE(column name)语句来实现。

例18 在book的book1表上建名为book1_trigger3的触发器,UPDATE时触发器被激活,该触发器不允许用户修改表的“定价”,本例不用INSTEAD OF,而是通过ROLLBACK TRANSACTION子句恢复原来数据的方法,来实现字段不被修改).

Use book
go
CREATE TRIGGER book1_trigger3
ON book1
FOR UPDATE
AS
IF UPDATE(定价)
BEGIN
ROLLBACK TRANSACTION
END

我们来UPDATE一下定价

use book
go
update book1
set 定价=5000
where 编号='YBZT2411'

执行后,我们发现更新不成功。

注:我们只定义了“定价”不能更新,但其它字段是可以更新的,意思是更新其它字段时,这个触发器不会被激活,但如果这个触发器被激话了,整一条记录都不能更新,即其它字段也不能。


3.DELETE触发器

例19 建立book1_trigger4的DELETE触发器,给出操作提示信息,并取消当前的删除操作。

use book
go
CREATE TRIGGER book1_trigger4
on book1
FOR DELETE
AS
BEGIN
RAISERROR('Unauthorized1',10,1)
ROLLRACK TRANSACTION
END

测试一下

delete
from book1
where 编号='YBZT2411'

我们发现删除不成功.

--------------
use test
go
if exists(select * from sys.triggers where name = 'del_info')
 drop trigger del_info
go
create trigger del_info
on stuinfo
after delete
as
delete from scores where stuno in(select stuno from deleted)
select * from deleted
go
-----------

8.5.3 管理触发器

1.查看触发器信息

SQL提供了多种方法:

1>使用系统存储过程

1)sp_help: 可以了解触发器的一般信息(名字,属性,类型等)
2)sp_helptext: 可查看触发器的定义信息.
               如:sp_helptext book1_trigger1  查看book1_trigger1的定义信息。
3)sp_depends: 能查看触发器所引用的表或指定的表涉及的所有触发器。

注:用WITH ENCRYPTION加密后的触发器无法用sp_helptext来查看。

查看特定表上存在的触发器,语法格式:

EXEC sp_helptrigger table_name

例20 查看book1表上存在的所有触发器相关信息.

use book
go
EXEC sp_helptrigger book1
go


2>使用系统表

  通过查询系统表sysobjects得到触发器的相关信息。

例21 使用系统表sysobjects查看数据库book1上的存在的所有触发器的相关信息.

use book
go
select name
from sysobjects
where type='TR'
go



2.修改触发器

1>使用sp_rename命令修改解发器,语法格式:
 sp_rename oldname,newname

2>通过ALTER trigger命令修改触发器的正文。

语法格式:

ALTER TRIGGER trigger_name
ON { table | view }
{FOR | AFTER | INSTEAD OF } { [INSERT],[UPDATE],[DELETE]}
[WITH ENCRYPTION]
AS
IF UPDATE (cotumn_name)
[{ and | or } UPDATE (column_name)...]
sql_statesments

其中,各参数的意义与建立触发器语句中参数的意义相同。

例22 修改book1_trigger1,使在insert,update,delete时,提示错误,并撤销此次操作。

use book
go
ALTER TRIGGER book1_trigger1
ON book1
INSTEAD OF DELETE,INSERT,UPDATE
AS
PRINT '你执行的删除,增加,修改无效'

3>删除触发器

(1)使用命令drop trigger,语法格式:
  drop trigger trigger_name

(2)删除触发器所在的表时,SQL会自动删除与该表相关的触发器.

4>禁止和启动触发器,语法格式:

ALTER TABLE table_name
{ENABLE | DISABLE } TRIGGER
{ALL [trigger_name [,...n] }

当某触发器被停止时,需要重新启用,才能生效。

例23 禁止或启用在book数据库中book1表上创建的所有触发器。

ALTER TABLE book DISABLE TRIGGER ALL
ALTER TABLE book ENABLUE TRIGGER ALL