第 6章 存储过程、触发器和数据完整性
?6.1 存储过程
?6.2 触发器
?6.3 数据完整性
6.1 存储过程
客户 /服务器数据库与传统的数据库结构的
一个很重要的区别是,在传统的数据库中只存
放数据,所有的应用程序都在用户端,都与用
户实际运行的应用程序捆绑在一起;而在客户 /
服务器结构的数据库中,在数据库中还可以存
放程序,即存储过程。
基本概念
存储过程是事先编好的、存储在数据库
中的程序,这些程序用来完成对数据库的指
定操作。
系统存储过程
SQL Server本身提供了一些存储过程,用于管
理 SQL Server和显示有关数据库和用户的信息,我
们称之为系统存储过程。
用户存储过程
用户也可以编写自己的存储过程,并把它存放
在数据库中。这样安排的主要目的就是要充分发挥
数据库服务器的功能,尽量减少网络上的堵塞。
客户端应用
( 不使用存储过程 )
客户端应用
( 使用存储过程 )
.
Start transaction
.
INSERT data
UPDATE data
DELETE data
End transaction
.
.
Start transaction
.
Call Stored procedure
End transaction
.
DBMS
Server
DBMS
Server
Procedure:
INSERT data
UPDATE data
DELETE data
(a)
(b)
不使用
存储过程时,
所有的数据
处理都在客
户端完成;
而使用存储
过程时,可
以使数据处
理在服务器
端完成。
创建存储过程
CREATE PROC[edure] procedure_name [ ; number ]
[ @parameter data_type [ = default ],… ]
AS sql_statement
s procedure_name:给出存储过程名;
s number:对同名的存储过程指定一个序号;
s @parameter:给出参数名;
s data_type:指出参数的数据类型;
s = default:给出参数的默认值;
s sql_statement:存储过程所要执行的 SQL语句,它可以是一组
SQL语句,可以包含流程控制语句等。
例:创建一个最简单的存储过程:
CREATE PROCedure sp_getemp;1
AS
SELECT * FROM 职工
例:带参数的存储过程:
CREATE PROCedure sp_getemp;2
(@salary int)
AS
SELECT * FROM 职工 WHERE 工资 > @salary
存储过程一般用来完成数据查询和数据处理
操作,所以在存储过程中不可以使用创建数据库
对象的语句,即在存储过程中一般不能含有以下
语句:
· CREATE TABLE
· CREATE VIEW
· CREATE DEFAULT
· CREATE RULE
· CREATE TRIGGER
· CREATE PROCEDURE
执行存储过程
[EXECute]
[@<返回状态码 >=]
<存储过程名 >
[[@<参数 >=]{<值 >|@<变量 >}…]
例:执行带参数的 sp_getemp;2存储过程
execute sp_getemp;2 1240
存储过程的返回值和状态信息
无论什么时候执行存储过程,总要返回
一个结果码,用以指示存储过程的执行状态。
如果存储过程执行成功,返回的结果码是 0;
如果存储过程执行失败,返回的结果码一般
是一个负数,它和失败的类型有关。我们在
创建存储过程时,也可以定义自己的状态码
和错误信息。
CREATE PROCedure sp_getemp;3
(@salary int =NULL)
AS
IF @salary IS NULL
BEGIN
PRINT '必须提供一个数值作参数 ! '
RETURN 13
END
IF NOT EXISTS (SELECT * FROM 职工 WHERE 工资 > @salary)
BEGIN
PRINT '没有满足条件的记录 ! '
RETURN -103
END
SELECT * FROM 职工 WHERE 工资 > @salary
RETURN 0
例:带参数和返回状态值的存储过程。
例:执行以上存储过程。
DECLARE @status int
EXECUTE @status=sp_getemp;3 1200
print @status
存储过程的修改和删除
?修改存储过程的语句是 ( 一般格式 ),
ALTER PROC[edure] procedure_name [ ; number ]
[ @parameter data_type [ = default ],… ]
AS sql_statement
?删除存储过程的语句是:
DROP PROC[edure] procedure_name
注意,删除存储过程的语句中不能指定序号 。 也就是说,
该语句将同时删除同名的所有存储过程 。
要用好存储过程
存储过程是客户 /服务器机制的一个重要
组成部分,如果使用客户 /服务器机制的数据
库管理系统,但是不理解存储过程或没有充
分利用存储过程,那将使客户 /服务器机制的
功能大打折扣,使系统的整体性能可能降低
很多。
6.2 触发器
触发器可以看作是一类特殊的存储过程,
它在满足某个特定条件时自动触发执行。
触发器是为表上的更新、插入、删除操
作定义的,也就是说当表上发生更新、插入
或删除操作时触发器将执行。
触发器的用途
1,触发器可以通过级联的方式对相关的表
进行修改。比如,对父表的修改,可以引起对
子孙表的一系列修改,从而保证数据的一致性
和完整性。
2,触发器可以禁止或撤消违反参照完整性
的修改。
3,触发器可以强制比用 CHECK约束定义
更加复杂的限制。
触发器是依附于表的数据库对象
?一个触发器和三部分内容有关:
?定义触发器的表
?激活触发器的数据操作语句
?触发器要采取的动作
建立触发器的语句
CREATE TRIGGER trigger_name
ON table
FOR { INSERT | UPDATE | DELETE }
AS
[IF UPDATE(column) [{AND | OR} UPDATE(column)…]]
sql_statement
strigger_name:给出了触发器的名称;
stable:说明了定义触发器的表或视图;
sFOR { INSERT | UPDATE | DELETE }:说明了激活触发器的数据操作语句;
sIF UPDATE(column):对应于 UPDATE类触发器,说明如果更新某(些)列则
做如何处理;
ssql_statement:触发器所要执行的 SQL语句,它可以是一组 SQL语句,可以包
含流程控制语句等。
例:建立一个简单的触发器。
CREATE TRIGGER wh_trigger
ON 仓库
FOR INSERT
AS PRINT '插入了一个仓库元组 '
插入视图和删除视图
为触发器运行而自动派生的两个视图:
inserted—— 存放刚插入的新记录
deleted—— 存放刚删除的旧记录
删除类触发器
删除类触发器就是当表上发生删除操作时
所触发执行的程序。
例:定义一个触发器,使得当删除仓库记录时,
同时将所属所有职工记录的仓库号字段值置为
空值 NULL:
CREATE TRIGGER w_del_trigger
ON 仓库 FOR DELETE
AS
UPDATE 职工
SET 仓库号 =NULL
WHERE 仓库号 = (SELECT 仓库号 FROM deleted)
插入类触发器
插入类触发器就是当表上发生插入
操作时所触发执行的程序。
例,对职工表的插入操作定义一个触发器,使得当
插入职工记录时,检查相应的仓库元组是否存在,
如果不存在则撤消所做的插入操作。
CREATE TRIGGER e_ins_trigger
ON 职工 FOR INSERT
AS
IF (SELECT COUNT(*) FROM 仓库 w,inserted i
WHERE w.仓库号 = i.仓库号 ) = 0
BEGIN
RAISERROR ('非法仓库号! ',1,1)
ROLLBACK TRANSACTION
END
更新类触发器
更新类触发器就是当表上发生更新操作时
所触发执行的程序。
例,对职工表的更新操作定义一个触发器,使得当
职工变换所属仓库时,检查相应的仓库元组是否存
在,如果不存在则撤消所做的更新操作,如果新的
仓库号是 WH2则将工资提高 10%。
CREATE TRIGGER e_upd_trigger
ON 职工 FOR UPDATE
AS
DECLARE @wh_no CHAR(4)
IF UPDATE(仓库号 )
BEGIN
IF (SELECT COUNT(*) FROM 仓库 w,inserted i
WHERE w.仓库号 = i.仓库号 ) = 0
BEGIN
RAISERROR ('非法仓库号! ',16,1)
ROLLBACK TRANSACTION
END
ELSE
BEGIN
SELECT @wh_no = 仓库号 FROM inserted
IF @wh_no = 'WH2'
UPDATE 职工 SET 工资 = 工资 *1.10
WHERE 职工号 = (SELECT 职工号 FROM inserted)
END
END
6.3 数据完整性
?在第 3章已经介绍了在关系数据模型上
数据完整性的概念和规则;在第 5章介
绍了 CREATE TABLE语句中可以实现
的一些完整性约束 。 这里介绍与数据完
整性有关的其他一些内容 。,
6.3.1 规则
?在 CREATE TABLE语句中可以使用 CHECK子句
实现一些用户定义完整性或域完整性约束 。 另外
还可以通过, 规则, ( RULE) 来实现用户定义
完整性或域完整性 。
?CHECK约束固定在一个表的一个列上, 它只在指
定的列上起作用 。 如果在不同的列上有相同的约
束条件, 则可以使用规则, 一个规则可以绑定在
多个列上 。
?规则是一种独立的数据库对象,它可以绑定到一
个列上来约束该列的取值范围等。
建立规则的命令是:
CREATE RULE rule AS condition_expression
?rule:给出新建规则的名称;
?condition_expression:定义规则的条件,可以是任
何有效的表达式,并且可以包含诸如算术运算符、
关系运算符和谓词(如 IN,LIKE,BETWEEN)
之类的元素。
?注意,规则不能引用列或其它数据库对象,规则
可以包含不引用数据库对象的内置函数;
condition_expression需要包含一个变量,变量的前
面有一个 @前缀;该表达式引用通过 UPDATE或
INSERT语句输入或传递的字段值。
例如,规定某类数值对象的取值范围是
1000~ 3000,则可以定义规则:
CREATE RULE range_rule
AS
@range>=1000 AND @range<=3000
规则的绑定
?规则是独立的数据库对象, 要通过系统存储过程
sp_bindrule把规则绑定到数据列上, 该系统存储
过程的格式是:
sp_bindrule rulename,objname [,futureonly ]
?rulename是用 CREATE RULE命令建立的规则名;
?objname指出要绑定的表和列或用户定义的数据类型;
?futureonly,当绑定规则到用户定义的数据类型时可以
选用此项, 该选项是禁止已 经存在的, 用用户定义数
据类型定义的列遵循新的规则 。
例如,将规则 range_rule绑定到职工表的工资列上
sp_bindrule range_rule,‘职工,工资 ’
?使用 CREATE RULE命令创建的规则对象,可以绑定到多
个数据列上,即一个规则可以反复使用。
绑定的消除和规则的删除
?绑定到数据列上的规则可以去除, 相应的
系统存储过程是 sp_unbindrule。 例如, 取消
绑定在职工表工资列上的规则可以使用如
下语句:
sp_unbindrule '职工,工资 '
?规则可以删除, 删除规则的命令是 DROP
RULE,但是删除规则之前, 必须首先解除
所有的绑定 。
6.3.2 默认值
?在 CREATE TABLE命令中可以使用 DEFAULT
约束为数据列定义默认值。这里介绍另外一种
方法:使用 CREATE DEFAULT命令创建默认
值对象。
CREATE DEFAULT命令的格式
CREATE DEFAULT default AS constant_expression
? default:是建立的默认值对象名;
? constant_expression:定义默认值的常量表达式 。
例如,定义一个值为“北京”的默认值对象 val_bj
CREATE DEFAULT val_bj
AS '北京 '
绑定默认值
?默认值是独立的数据库对象,它要作用于某个数
据对象,则也和绑定规则一样,需要用类似的系
统存储过程把默认值绑定到列,绑定默认值的系
统存储过程是 sp_bindefault,具体格式是:
sp_bindefault defname,objname [,futureonly ]
?defname:是用 CREATE DEFAULT命令建立的默
认值对象名;
?objname:指出要绑定的表和列或用户定义的数据
类型;
?futureonly,当绑定默认值到用户定义的数据类型
时可以选用此项,该选项是禁止已经存在的、用
用户定义数据类型定义的列遵循新的默认值约定。
例如,将定义的默认值对象 val_bj绑定到仓库关
系的城市列上和供应商关系的地址列上:
sp_bindefault val_bj,'仓库,城市 '
sp_bindefault val_bj,'供应商,地址 '
绑定的去除和默认值的删除
?绑定到数据列上的默认值可以去除, 相应
的系统存储过程是 sp_unbindefault。
?例如, 取消绑定在供应商表地址列上的默
认值可以使用如下语句:
sp_unbindefault '供应商,地址 '
?默认值也可以删除, 删除默认值的命令是
DROP DEFAULT,但是删除默认值之前,
必须首先解除所有的绑定 。
6.3.3 用户定义数据完整性
?除了实体完整性约束和参照完整性约束,其
他与数据完整性的有关的内容都是用户定义
数据完整性的范畴。
?而实现用户定义数据完整性,除了 CREATE
TABLE命令中的 CHECK约束,以及本章介
绍的规则( RULE)和默认值 (DEFAULT),
更多的是使用触发器来实现灵活、复杂的数
据完整性要求。
举例
?假设有如下关系模式:
管理 (仓库号, 设备号, 职工号 )
?它所包含的语义是:
?一个仓库可以有多个职工;
?一名职工仅在一个仓库工作;
?在每个仓库一种设备仅由一名职工保管 ( 但每名职工可
以保管多种设备 ) 。
?显然该关系模式的关键字是 (仓库号,设备号 )。 进一步假设,
该关系中已经有元组:
('WH1','P1','E1')
('WH1','P2','E1')
?现在接着要插入元组 ('WH2','P2','E1'),注意:这种操作显然
违反了语义, 一名职工仅在一个仓库工作,, 但它不违反实
体完整性约束, 一般的域完整性约束也无济于事 。 这时则只
能通过触发器来实现数据完整性约束 。
建立触发器
CREATE TRIGGER wh_emp ON 管理
FOR INSERT,UPDATE
AS
DECLARE @emp CHAR(6),@wh CHAR(6)
SELECT @wh=仓库号,@emp=职工号 FROM inserted
IF (SELECT COUNT(*) FROM 管理
WHERE 职工号 =@emp AND 仓库号 <>@wh) <> 0
BEGIN
RAISERROR ('该职工已经属于其他仓库 ! ',16,1)
ROLLBACK TRANSACTION
END
用户定义的数据类型
?用户定义完整性另一种最常见的形式就是
创建用户定义的数据类型。用户定义的数
据类型并不是一种新的数据类型,它只是
用另外一种形式来描述已有的数据类型。
如果数据库中存在很多复杂的企业规则,
用户定义数据类型可以增加系统的透明度,
这对维护数据完整性是有帮助的。在 SQL
Server中,使用系统存储过程 sp_addtype定
义用户数据类型。
【 本章小节 】
?存储过程和触发器,它们都是独立的数据
库对象和存储在数据库上的特殊的程序。
?存储过程由用户调用,完成指定的数据处
理任务;触发器则由特定的操作触发,从
而自动完成相关的处理任务。
?使用触发器可以实现更强的数据完整性方
面的约束。
?规则、默认值。