数据完整性、存储过程、函数
数据库的实体完整性:数据的正确性和相容性。
一、实体完整性
实体完整性在CREATE TABLE 中用PRIMARY KEY定义。
(1)对单属性构成的码有两种说明方法,一种是定义为列级约束条件,另一种是定义为表级约束条件。
(2)对多个属性构成的码只有一种说明方法,即定义为表级约束条件。
1、列级定义主码
例1:将Student表中的Sno属性定义为码。
代码:
CREATE TABLE Student --定义表
(Sno CHAR(9) PRIMARY KEY, --在列级定义主码
Sname CHAR(20) NOT NULL, --属性值唯一
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);
2、表级定义主码
例2:将Student表中的Sno属性定义为码。
代码:
CREATE TABLE Student --定义表
(Sno CHAR(9) ,
Sname CHAR(20) NOT NULL, --属性值唯一
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20),
PRIMARY KEY(Sno) --在表级定义主码
);
例3:将SC表中的Sno、Cno属性组定义为主码
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中用FOREIGN KEY短语定义哪些列为外码,用REFERENCES短语指明这些外码参照哪些表的主码。
1、表级定义完整性
例1:关系SC中一个元组表示个学生选修的某门课程的成绩, (Sno, Cno)是主码,Sno、Cno分别参照引用Student表的主码和Course表的主码。
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)--表级完整性约束
);
可能破坏参照完整性的情况:
被参照表 | 参照表 | 违约处理 |
---|---|---|
可能破坏参照完整性 | 插入元祖 | 拒绝 |
可能破坏参照完整性 | 修改外码值 | 拒绝 |
删除元组 | 可能破坏参照完整性 | 拒绝、级联删除、设置空值 |
修改主码值 | 可能破坏参照完整性 | 拒绝、级联修改、设置空值 |
注:某些属性列既是外码,又是主码属性组中的一个,此时不允许设置空值
因此定义参照完整性,除了应该定义外码,还应定义外码列是否允许空值。
2、显示说明参照完整性
如果想让系统采用其他策略则必须在创建参照完整性是显示地加以说明。
例1:显示说明参照完整性的违约示例
代码:
CREATE TABLE SC
(Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY(Sno,Cno),--只能在表级定义完整性约束
FOREIGN KEY (Sno) REFERENCES Student(Sno)
ON DELETE CASCADE --当删除Student表中的元组时,级联删除SC表中相应的元组
ON UPDATE CASCADE, --当更新Student表中的元组时,级联更新SC表中相应的元组
FOREIGN KEY(Cno) REFERENCES Course(Cno)
ON DELETE NO ACTION --当删除Course表中的元组造成与SC表不一致时,拒绝删除
ON UPDATE CASCADE -当更新Course表中的元组时,级联更新SC表中相应的元组
);
三、用户自定义完整性
在CREATE TABLE中定义属性的同时,可以根据应用要求定义属性上的约束条件,即属性值限制。
1、属性上的约束条件
(1)NOT NULL
不允许取空值
例:在定义SC表时,说明Sno、Cno、Grade属性不允许取空值
CREATE TABLE SC
(Sno CHAR(9) NOT NULL, --不允许取空值
Cno CHAR(4) NOT NULL, --不允许取空值
Grade SMALLINT NOT NULL, --不允许取空值
PRIMARY KEY(Sno,Cno),--只能在表级定义完整性约束
FOREIGN KEY (Sno) REFERENCES Student(Sno),--表级完整性约束
FOREIGN KEY(Cno) REFERENCES Course(Cno)--表级完整性约束
);
(2)UNIQUE
列值唯一
例:建立部门表DEPT,要求部门名称Dname列取值唯一,部门编号Deptno列为主码
CREATE DEPT
(Deptno NUMERIC(2) PRIMARY KEY,
Dname CHAR(9) UNIQUE NOT NULL,
Location CHAR(10)
);
(3)用CHECK短语指定列值应该满足的条件
例1:Student表中Ssex只允许取“男”或“女”。
CREATE TABLE Student --定义表
(Sno CHAR(9) PRIMARY KEY, --设置主码
Sname CHAR(20) UNIQUE, --属性值唯一
Ssex CHAR(2) CHECK(Ssex IN('男','女')),
Sage SMALLINT,
Sdept CHAR(20)
);
例2:SC表中的Grade的值应该在0和100之间
CREATE TABLE SC
(Sno CHAR(9) NOT NULL, --不允许取空值
Cno CHAR(4) NOT NULL, --不允许取空值
Grade SMALLINT CHECK(Grade >=0 AND Grade<=100),
PRIMARY KEY(Sno,Cno),--只能在表级定义完整性约束
FOREIGN KEY (Sno) REFERENCES Student(Sno),--表级完整性约束
FOREIGN KEY(Cno) REFERENCES Course(Cno)--表级完整性约束
);
2、元组上的约束条件
与属性上约束条件的定义类似,在CREATE TABLE语句中可以用CHECK短语定义元组上的约束条件,即元组级的限制。同属性值限制相比,元组级的限制可以设置不同属性之间的取值的相互约束条件。
例:当学生的性别是男时,其名字不能以Ms.打头。
CREATE TABLE Student --定义表
(Sno CHAR(9) PRIMARY KEY, --设置主码
Sname CHAR(20) UNIQUE, --属性值唯一
Ssex CHAR(2) CHECK(Ssex IN('男','女')),
Sage SMALLINT,
Sdept CHAR(20),
CHECK (Ssex='女' OR Sname NOT LIKE 'Ms.%')
);
四、完整性约束命名子句
完整性约束条件除了在CREATE TABLE语句中定义,还CREATE TABLE语句中提供了完整性约束命名子句CONSTRAINT,用来对完整性约束条件命名,从而灵活地增加、删除一个完整性约束条件。
1、完整性约束命名子句
CONSTRAINT <完整性约束条件名> <完整性约束条件>
<完整性约束条件>包括NOT NULL、UNIQUE、 PRIMARY KEY、FOREIGN KEY、CHECK短语等。
例1:建立学生登记表 Student,要求学号在90000~99999之间,姓名不能取空值,年龄小于30,性别只能是“男”或“女”。
CREATE TABLE Student --定义表
(Sno CHAR(9)
CONSTRAINT C1 CHECK(Sno BETWEEN 90000 AND 99999),
Sname CHAR(20)
CONSTRAINT C2 NOT NULL,
Ssex CHAR(2)
CONSTRAINT C3 CHECK(Ssex IN('男','女')),
Sage SMALLINT
CONSTRAINT C4 CHECK(Sage<30),
Sdept CHAR(20),
CONSTRAINT StudentKey PRIMARY KEY(Sno)
);
例2:建立教师表TEACHER,要求每个教师的应发工资不低于3000元。应发工资是工资列Sal与扣除项Deduct之和。
CREATE TABLE TEACHER
(Eno NUMERIC(4) PRIMARY KEY,
Job CHAR(8),
Sal NUMERIC(7,2),
Deduct NUMERIC(7,2),
Deptno NUMERIC(2),
CONSTRAINT TEACHERKey FOREIGN KEY(Deptno)
REFERENCES DEPT(Deptno),
CONSTRAINT C5 CHECK(Sal+Deduct>=3000),
);
2、修改表中完整性限制
可以使用ALTER TABLE语句修改表中的完整性限制。
例1:去掉Student表中对性别的限制。
ALTER TABLE Student
DROP CONSTRAINT C4;
例2:修改表Student表中的约束条件,要求学号改为在900000~999999之间,年龄由小于30改为小于40
ALTER TABLE Student
DROP CONSTRAINT C1;
ALTER TABLE Student
DROP CONSTRAINT C3;
ALTER TABLE Student
ADD CONSTRAUBT C1 CHECK(Sno BETWEEN 900000 AND 999999);
ALTER TABLE Student
ADD CONSTRAUBT C3 CHECK(Sage<40);
五、断言
在SQL中可以使用数据定义语言中的CREATE ASSERTION 语句,通过声明性断言来指定更具一般性的约束。 可以定义涉及多个表或聚集操作的比较复杂的完整性约束。断言创建以后,任何对断言中所涉及关系的操作都会触发关系数据库管理系统对断言的检查,任何使断言不为真值的操作都会被拒绝执行。
1、创建断言
CREATE ASSERTION <断言名> <CHECK子旬>
每个断言都被赋予一个名字,<CHECK子向>中的约束条件与WHERE子句的条件表达式类似。
例:限制数据库课程最多 60名学生选修。
标准SQL语句:
CREATE ASSERTION ASSE_SC_DB_NUM
CHECK (60 >=
(SELECT count(*)
FROM Course,SC
WHERE SC.Cno=Course.Cno AND
Course.Cname ='数据库')
);
例2:限制每一门课程最多60名学生选修
CREATE ASSERTION ASSE_SC_CNUM1
CHECK(60 >= ALL
(SELECT count(*)
FROM SC
GROUP BY cno)
);
例3:限制每个学期每一门课程最多60名学生选修
首先修改SC表的模式,增加一个“学期(TEAM)”的属性:
ALTER TABLE SC
ADD TEAM DATE;
然后定义断言:
CREATE ASSERTION ASSE_SC_CNUM2
CHECK (60 >= ALL
(SELECT COUNT(*)
FROM SC
GROUP BY Cno,TEAM));
2、删除断言
DROP ASSERTION <断言名>;
注:T-SQL 中没有 ASSERTION 功能,需要用触发器编写。
SQL SERVER中无断言
六、触发器
触发器使用户定义在关系上的一类由事件驱动的特殊过程。 触发器类似于约束,但是比约束更加灵活,可以实施更为复杂的检查和操作,具有更惊喜和更强大的数据控制能力。
1、定义触发器
触发器又叫做事件 - 条件 - 动作规则。当特定的系统事件(如对一个表的增、删、改操作,事务的结束等)发生时,对规则的条件进行检查,如果条件成立则执行规则中的动作,否则不执行该动作。 规则中的动作体可以很复杂,可以涉及其他表和其他数据库对象,通常是一段SQL存储过程。
建立触发器的一般格式:
CREATE TRIGGER <触发器名> /*每当触发事件发生时,该触发器被激活*/
{BEFORE |AFTER} <触发事件> ON <表名> /*指明触发器激活的时间是在执行触发事件前或后*/
REFERENCING NEW|OLD ROW AS<变量> /*REFERENCING 指出引用的变量*/
FOR EACH {ROW|STATEMENT}/* 定义触发器的类型,指明动作体执行的频率*/
[WHEN <触发条件>] <触发动作体> /*仅当触发条件为真时才执行触发动作体*/
例:当对表 SC的Grade属性进行修改时,若分数增加了10%,则将此次操作记录到另一个表SCU(Sno、Cno、 Oldgrade、Newgrade)中,其中Oldgrade是修改前的分数,Newgrade 是修改后的分数。
创建表:
CREATE TABLE SCU
(Sno CHAR(12),
Cno CHAR(10),
Oldgrade INT,
Newgrade INT);
创建触发器:
标准SQL语句:
CREATE TRIGGER SC_T
AFTER UPDATE OF GRADE ON SC
REFERENCING
OLDROW AS Oldtuple
NEWROW AS Newtuple
FOR EACH ROW
WHEN(Newtuple>=1.1*Oldtuple)
INSERT
INTO SCU
VALUES(Oldtuple.Sno,Oldtuple.Cno,Oldtuple.Grade,Newtuple.Grade)
T-SQL语句:
CREATE TRIGGER SC_T
ON SC
AFTER UPDATE
AS
BEGIN
IF UPDATE (Grade )
DECLARE @Sno CHAR(10)
DECLARE @Cno CHAR(10)
DECLARE @Oldtuple INT
DECLARE @Newtuple INT
SELECT @Oldtuple=Grade FROM DELETED
SELECT @Newtuple=Grade FROM INSERTED
SELECT @Cno=Cno FROM SC
SELECT @Cno=Cno FROM SC
IF (@Newtuple>=1.1*@Oldtuple)
INSERT
INTO SCU
VALUES(@Sno,@Cno,@Oldtuple,@Newtuple)
END
[例5.22] 将每次对表Student 的插入操作所增加的学生个数记录到表StudentInsertLog中。
创建表:
CREATE TABLE StudentInsertLog
(Number INT);
创建触发器:
标准SQL语句:
CREATE TRIGGER Student_Count
AFTER INSERT ON Student
REFERENCING
NEWTABLE AS DELTA
FOR EACH STATEMENT
INSERT
INTO StudentInsertLog(Number)
SELECT COUNT(*) FROM DELTA;
T-SQL语句:
CREATE TRIGGER Student_Count
ON Student
AFTER INSERT
AS
INSERT
INTO StudentInsertLog(Number)
SELECT COUNT(*) FROM Student;
例3:定义一个BEFORE行级触发器,为教师表Teacher定义完整性规则“教授的工资不得低于4000元,如果低于4000元,自动改为4000元”。
创建表:
CREATE TABLE Teacher
(Name CHAR(20),
Salary INT,
Job CHAR(10));
标准SQL语句:
CREATE TRIGGER Insert_or_Update_Sal
AFTER INSERT OR UPDATE ON Teacher
REFERENCING NEW row AS newTuple
FOR EACH ROW
BEGIN
IF(newtuple='教授')AND(newtuple.Sal<4000)
THEN newtuple.Salary:=4000;
END IF
END;
T-SQL语句:
CREATE TRIGGER Insert_or_Update_Sal
ON Teacher
FOR INSERT,UPDATE
AS
DECLARE @Newtuple INT
DECLARE @Job CHAR(10)
IF (UPDATE(Salary))
SELECT @Salary=Salary FROM INSERTED
SELECT @Job=Job FROM Teacher
IF(@Job='教授')AND(@Salary<4000)
UPDATE Teacher
SET Salary=4000
WHERE Job='教授'AND Salary<4000;
2、激活触发器
触发器的执行,是由触发事件激活的,并由数据库服务器自动执行!
一个数据表上可能定义了多个触发器,遵循如下的执行顺序:
(1) 执行该表上的BEFORE触发器;
(2) 激活触发器的SQL语句;
(3) 执行该表上的AFTER触发器。
3、删除触发器
删除触发器语句:
DROP TRIGGER <触发器名> ON <表名>
七、存储过程
存储过程是由过程化SQL语句书写的过程,这个过程经编译和优化后存储在数据库服务器中,使用时只要调用即可。
1、创建存储过程
CREATE OR REPLACE PROCEDURE /*过程名([参数1,参数二...])/*存储过程首部*/
AS<过程化SQL块>;/*存储过程体,描述该存储过程的操作*/
存储过程包括过程首部和过程体。在过程首部,“过程名”是数据库服务器合法的对象标识;参数列表[参数1,参数2,…用名字来标识调用时给出的参数值,必须指定值的数据类型。 可以定义输入参数、输出参数或输入/输出参数,默认为输入参数,也可以无参数。
过程体是一个<过程化 SQL块>,包括声明部分和可执行语句部分。
例:利用存储过程实现下面的应用:从账户1转指定数额的款项到账户2中。假设账户关系表为Account(Accountnum,Total)。
创建表:
DROP TABLE IF EXISTS Account;
CREATE TABLE Account
(Accountnum CHAR(20),
Total INT);
INSERT INTO Account VALUES(01003813828,50000);
INSERT INTO Account VALUES(01003815868,20000);
SELECT * FROM Account;
创建存储过程:
标准SQL语句:
CREATE OR REPLACE PROCEDURE TRANSFER(inAccount INT,outAccount INT,amount FLOAT)
AS DECLARE
totalDepositOut FLOAT;
totalDepositIN FLOAT;
inAccountnum INT;
BEGIN
SELECT Total INTO totalDepositOut FROM Account WHERE accountnum=outAccount;
IF totalDepositOut IS NULL THEN
ROLLBACK;
RETURN;
END IF;
IF totalDepositOut < account THEN
ROLLBACK;
RETURN;
END IF;
SELECT Accountnum INTO inAccountnum FROM Account
WHERE accountnum=inAccount;
IF inAccount IS NULL THEN
ROLLBACK;
RETURN;
END IF;
UPDATE Account SET total=total-amount WHERE accountnum=outAccount;
UPDATE Account SET total=total+amount WHERE accountnum=inAccount;
COMMIT;
END;
编译出错:
T-SQL语句:
IF(EXISTS(SELECT * FROM sys.objects
WHERE name='Proc_TRANSFER'))
DROP PROCEDURE Proc_TRANSFER
GO
CREATE PROCEDURE Proc_TRANSFER(@inAccount INT,@outAccount INT,@amount FLOAT)--创建存储过程
AS
BEGIN TRANSACTION TRANS
DECLARE --定义变量
@totalDepositOut FLOAT,
@totalDepositIn FLOAT,
@inAccountnum INT; --检查转出账户余额
SELECT @totalDepositOut=total FROM Account
WHERE accountnum=@outAccount;--如果转出账户不存在或没有存款
IF @totalDepositOut IS NULL
BEGIN
PRINT'转出账户不存在或账户没有存款'
ROLLBACK TRANSACTION TRANS;
RETURN;
END;
IF @totalDepositOut<@amount--如果账户存款不足
BEGIN
PRINT'账户存款不足'
ROLLBACK TRANSACTION TRANS;
RETURN;
END;
BEGIN --如果条件无异常,开始转账
UPDATE Account SET total =total-@amount
WHERE accountnum=@outAccount;--修改转出账户余额,减去转出额
UPDATE Account SET total =total+@amount
WHERE accountnum=@inAccount;--修改转入账户余额,增加转入额
PRINT'转账完成,请取走银行卡'
COMMIT TRANSACTION TRANS;--提交转账事物
RETURN;
END;
2、执行存储过程
执行语句:
CALL/PERFORM PROCEDURE 过程名([参数1,参数2,...]);
例:从账户01003815868转10000元到01003813828账户中。
标准SQL语句:
CALL PROCEDURE Proc_TRANSFER(01003813828,01003815868,10000);
T-SQL语句:
EXEC Proc_TRANSFER
@inAccount = 01003813828, --转入账户
@outAccount = 01003815868, --转出账户
@amount = 10000 --转出金额
SELECT *
FROM Account;
3、修改存储过程
重命名语句:
ALTER PROCEDURE 过程名 RENAME TO 过程名;
重新编译语句:
ALTER PROCEDURE 过程名 COMPILE;
4、删除存储过程
删除语句:
DROP PROCEDURE 过程名;
八、函数
函数和存储过程类似,都是持久性存储模块。函数的定义和存储过程也类似,不同之处是函数必须指定返回的类型。
1、定义函数
定义函数语句:
CREATE OR REPLACE FUNCTION函数名([参数1,参数2,...)
RETURNS <类型>
AS <过程化SQL块>;
2、执行函数
调用语句:
CALL/SELECT 函数名([参数1,参数2,...]);
3、修改函数
重命名语句:
ALTER FUNCTION 过程名1 RENAME TO 过程名2;
重新编译语句:
ALTER FUNCTION 过程名 COMPILE;