SQL Server中的数据库完整性、触发器、存储过程和函数

数据库完整性

数据库完整性是指数据的正确性和相容性。数据的正确性是指数据是符合现实世界语义、反映当前实际情况的;数据的相容性是指数据库同一对象在不同关系表中的数据是符合逻辑的。为维护数据库的完整性，数据库管理系统必须能够实现如下功能：
1.提供定义完整性约束条件的机制
2.提供完整性检查的方法
3.进行违约处理

实体完整性

定义实体完整性
关系模型的实体完整性在CREATE TABLE中用PRIMARY KEY定义。对单属性构成的码有两种说明方法，一种是定义为列级约束条件，另一种是定义为表级约束条件。对多个属性构成的码只有一种说明方法，即定义为表级约束条件。
将Student表中的Sno属性定义为码：

create table Student
	(sno char(9)PRIMARY KEY,
	 sname char(20)NOT NULL,
	 ssex char(2),
	 sage smallint,
	 sdept char(20)
	 );

将SC表中的Sno、Cno属性组定义为码：

create table SC
	(sno char(9) not null,
	 cno char(4) not null,
	 grade smallint,
	 primary key(sno,cno)
	 );

实体完整性检查和违约处理
用PRIMARY KEY短语定义了关系的主码后，每当用户程序对基本表插入一条记录或对主码列进行更新操作时，关系数据库管理系统将按照实体完整性规则自动进行检查。包括：
（1）检查主码值是否唯一，如果不唯一则拒绝插入或修改。
（2）检查主码的各个属性是否为空，只有有一个为空就拒绝插入或修改，从而保证了实体完整性。

参照完整性

定义参照完整性
关系模型的参照完整性在ｃｒｅａｔｅ　ｔａｂｌｅ中用ｆｒｏｅｉｇｎ　ｋｅｙ短语定义哪些列为外码，用ｐｅｆｅｒｅｎｃｅｓ短语指明这些外码参照哪些表的主码。

定义ＳＣ中的参照完整性

create table sc
	(sno char(9) not null,
	 cno char(4) not null,
	 grade smallint,
	 primary key (sno,cno),
	 foreign key(sno)references student(sno),
	 foreign key(cno)references course(cno)
	 );

参照完整性检查和违约处理
参照完整性将两个表中的相应元祖联系起来了。因此，对呗参照表进行增、删、改操作时有可能破坏参照完整性，必须进行检查以保证这两个表的相容性。

被参照表	参照表	违约处理
可能破坏参照完整性	插入元组	拒绝
可能破坏	修改外码值	拒绝
删除元组	可能破坏参照完整性	拒绝／级联删除／设置为空值
修改主码值	可能破坏参照完整性	拒绝／级联修改／设置为空值

显式说明参照完整性的违约处理实例：

create table sc
(sno char(9),
cno cahr(4),
grade smallint,
primary key (sno,cno),
foreign key(sno)references student(sno)
	 on delete cascade
	 on update cascade

foreign key(cno)references course(cno)
	 on delete no action
	 on update cascade
);

注意：此处代码在ＳＱＬ　Ｓｅｒｖｅｒ中与标准ｓｑｌ有差别。

用户定义的完整性

用户定义的完整性就是针对某一具体应用的数据必须满足的语义要求。

属性上的约束条件
在create table中定义属性的同时,可以根据应用要求定义属性上的约束条件,即属性值限制,包括:
1.列值非空(NOT NULL)
2.列值唯一(UNIQUE)
3.检查列值是否满足一个条件表达式(CHECK短语)

在定义ＳＣ表时，说明属性不允许取空值：

create table sc
	(sno char(9)not null,
	 cno char(4)not null,
	 grade smallint not null,
	 primary key(sno,cno)
	 );

建表时，要求属性的列取唯一值，且设定主码：

create table dept
	(deptno numeri(2),
	 dname char(9)unique not null,
	 location char(10),
	 primary key (deptno)
	 );

用ＣＨＥＣＫ短语指定列值应满足的条件：

create table student
	(Sno char(9)primary key,
	 sname char(8)not null,
	 ssex char(2)check (ssex in ('男','女')),
	 sage smallint,
	 sdept char(20)
	 );

元组上的约束条件
与属性上的约束条件类似，在create table语句中可以用ＣＨＥＣＫ短语定义元组上的约束条件，即元组级的限制。同属性值限制相比，元组级的限制可以设置不同属性之间的取值的相互约束条件。
例：当学生的性别为男时，其名字不能以Ms.打头

create table student
	(sno char(9),
	 sname char(8)not null,
	 ssex char(2),
	 sage smallint,
	 sdept char(20),
	 primary key (sno),
	 check (ssex='女' or sname not like 'Ms.')
	 );

完整性约束命名子句

ＳＱＬ在ＣＲＥＡＴＥ　ＴＡＢＬＥ　语句中提供了完整性约束命名子句ＣＯＮＳＴＲＡＩＮＴ，用来对完整性约束条件命名，从而可以灵活地增加、删除一个完整性约束条件
在表上建立约束条件包括主码约束以及列级约束：

create table student
	(sno numeric(6)
		constraint c1 check(sno between 90000 and 99999),
	  sname char(20)
		constraint c2 not null,
	 sage numeric(3)
	    constraint c3 check(sage<30),
	  ssex char(2)
	    constraint c4 check (ssex in('男','女')),
		constraint studentkey primary key (sno)
	);

断言

在SQL中可以使用数据定义语音中的create assertion 语句,通过声明性断言来指定更具一般性的约束.可以定义涉及多个表或聚焦操作的比较复杂的完整性约束.断言创建后,任何对断言中关系所涉及的操作都会触发关系数据库管理系统对断言的检查,任何使断言不为真值的操作都会被拒绝执行。

例：限制数据库课程最多选修学生人数

create assertion asse_sc_db_num
	 check(60>=(select count(*)
				from course,sc
				where sc.cno=course.cno and course.cname='数据库')
		   );

限制每门课的选课人数

create assertion asse_sc_cnum
	check(60>=all(select count(*)
				  from sc
				  group by cno)
				  );

删除断言的语句格式：

drop assertion<断言名>;

如果断言很复杂，则系统在检测和维护断言上的开销较高，这是在使用断言时应该注意的。

触发器

触发器时是用户定义在关系表上的一类由事件驱动的特殊过程。一旦定义，触发器将被保存在数据库服务器中。任何用户对表的增、删、改操作均由服务器自动激活相应的触发器，触发器可以实施更为复杂的检查和操作，具有更精细和更强大的数据控制能力。

定义触发器
触发器又叫做事件—条件—动作规则。当特定的系统事件（如表的增、删、改操作，事务的结束等）发生时，对规则的条件进行检查，如果条件成立则执行规则中的动作，否则不执行该动作。规则中的动作体可以很复杂，可以涉及其他表和其他数据库对象，通常是一段SQL存储过程。
SQL使用create trigger命令建立触发器，其一般格式为：

create trigger<触发器名>
{before|after}<触发事件>on <表名>
referencing new|old row as<变量>
for each{row|statement}
[when<触发条件>]<触发体动作>

例：将每次对表的插入操作所增加的数据个数记录到一个表中

create trigger Student_Count
after insert ON student
referencing
	new table as delta
for each statement
	insert into studentinsertlog(numbers)
	select count(*)from delta

此处运行会报错，目前我还没找到解决方法，先贴上来等找到正确方法在修改。

激活触发器
触发器的执行是由触发事件激活，并由数据库服务自动执行。一个数据表上可能定义了多个触发器，同一个表上的触发器激活时遵循如下的执行顺序;
1.执行该表上的before触发器
2.激活触发器的SQL语句
3.执行该表上的after触发器

对于一个表上的多个触发器，遵循“谁先创建谁先执行”的原则。

删除触发器
语法如下：

drop trigger<触发器名>on<表名>;

存储过程和函数

过程化SQL块主要有两种类型，即命名块和匿名块。匿名块每次执行都要进行编译，它不能被存储到数据库中，也不能在其他过程化SQL块中调用。过程和函数都是命名块，他们被编译后保存在数据库中，称为持久性存储模块，可以被反复调用，运行速度较快。

存储过程
存储过程是由过程化SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，因此称它为存储过程，使用时只要调用即可。
存储过程优点：
1.运行效率高
2.存储过程降低了客户和服务器之间的通信量
3.方便实施企业规划

存储过程的用户接口：

create or replace procedure过程名（[参数1，参数2，...]）
as<过程化SQL块>；

例：利用存储过程实现应用：从账户1转指定数额的款项到账户2中：
标准SQL：

create or replace procedure Transfer(inAccount int ,outAccount int, amount float)
as declare 
	totalDepositOut float;
	totalDepositIn float;
	inAccountnum int;
begin
	select total into totalDespositOut from Account
	where Accountnum = outAccount;
	if totalDespositOut is NULL then
	rollback;
	return ;
	end if;
	if totalDespositOut<amount then
	rollback;
	return ;
	end if;


	select Accountnum into inAccountnum 
	from Account 
	where Accountnum=inAccount;
	if inAccountnum is null then 
	rollback;
	return ;
	end if;

	update Account set total =total -amount
	where Accountnum=outAccout;
	update Account set total =total + amount
	where Accountnum = inAccount;
	commit;
end;

执行存储过程
使用CALL或者PERFORM等方式激活存储过程的执行。在此过程化SQL中，数据库服务器支持在过程体中调用其他存储过程。

CALL/PERFORM PROCEDURE 过程名（[参数1，参数2，...]）;

修改存储过程

可以使用ALTER PROCEDURE重命名一个存储过程：

ALTER PROCEDURE过程名1 RENAME TO 过程名2；

可以使用ALTER PROCEDURE重新编译一个存储过程：

ALTER PROCEDURE 过程名 COMPILE；

删除存储过程

DROP PROCEDURE 过程名（）；

函数

函数和存储过程类似，都是持久性存储模块。函数的定义和存储过程也类似，不同之处是函数必须指定返回的类型。

1.函数的定义语句格式

CREATE OR REPLACE FUNCTION 函数名（[参数1,参数2,...]）RETURNS<类型>
AS  <过程化SQL块>

2.函数的执行语句格式

CALL/SELECT 函数名（[参数1，参数2，...]）；

3.修改函数
可以用ALTER FUNCTION重命名一个自定义函数：

ALTER FUNCTION过程名1 RENAME TO 过程名2；

可以使用ALTER FUNCTION重新编译一个函数：

ALTER FUNCTION 过程名 COMPILE；

由于函数概念与存储过程类似，不多做说明。