ch4.过程语言PL/SQL

PART1.SQL Server

4.1.1 T-SQL编程基础

程序注释语句

–：单行注释；

/*……*/：多行注释。此多行注释中不能包括go语句。
变量
- 分类：
  - 局部变量@
  - 全局变量@@
- 局部变量使用前要先定义，赋值、再使用
declare @变量名数据类型——每次可定义多个变量

set|select @变量名＝常量

set 与select 赋值的区别为：set 只能一次一个变量,select 则可以一次给多个变量赋值。

Declare @x int
Declare @y int
Set @x=10

程序结构
- 语句组
  
  BEGIN
  语句1
  语句2
  END
- if …else 结构——条件分支结构
  
  if 逻辑表达式
  单条语句 or begin…end 语句组（多条语句）
  else
  单条语句 or begin…end 语句组（多条语句）

declare @w char(6)
declare @ss char(4)
declare @aa char(8)
select  @w=sname,@ss=ssex,@aa=sdept from student where sno='101'

if (@ss='男')
   begin
       print @w+'在'+@aa+'是个男同志'
   end
else
   begin
       print @w+'是个女同志'
   end

【例】查看100号同学的最小成绩>=60。如果>=60的话，就打印出’这是个好同志’；否则打印出’还要努力’，然后把这个同学不及格的成绩变为60分。

declare @cc int
select @cc=min(grade) from sc where sno='100'
if (@cc<60)
 begin
   print '还要努力!'
   update sc set grade=60 where sno='100' and grade<60
 end
else
  print '这是个好同志!'

case表达式：

CASE 测试表达式
When 简单表达式1 then 结果表达式1
When 简单表达式2 then 结果表达式2
……..
When 简单表达式n then 结果表达式n
[else 结果表达式n+1]
END

【简单表达式也可以为布尔表达式。】

case多重分支结构：

select 教师姓名,出生年月,年龄层次= 
( case 
       when 出生年月<'1950-01-01' then '老'       
       when 出生年月 between '1950-01-01'and '1969-12-31' then '中'
       when 出生年月>'1970-01-01' then '青'
else        ' '
end ),
工资层次=
(case    
 	   when 工资<1000 then '低'    
       when 工资 between 1000 and 2000 then '中'
       when 工资>2000 then '高'
else        ''
end
) from 教师表

cast能改变变量的数据类型，如： cast(@sum as char(4))

declare @i int,@sum int  	   --定义二个变量@i和@sum
set @i=1                       --让变量@i=1
set @sum=0                     --和的结果初始值为0
while (@i<=100)        		   --只要变量@i<=100,就进行下面的循环
   begin
       set @sum=@sum+@i 	   --每次加一个@i
       print 'i的值='+ cast(@i as char(4))
       print '中间和的值=' + cast(@sum as char(4))
       set @i=@i+1             --每次@i增长1
   end
print @sum        			   --打印最后的和

return和break意思相同。

4.1.2 游标和事务

4.1.2.1 游标

游标是系统为用户开设的一个数据缓冲区，存放Sql语句的执行结果。用户可以通过游标逐一获取记录并赋给主变量，交由主语言进一步处理。也即，游标是结果集的逻辑扩展，它使应用程序得以逐行处理结果集。

游标的使用中，会每次处理一行或一部分行作为个性化处理。游标一定要跟查询语句相关联。

游标定义：DECLARE (游标名字) [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR { READ ONLY | UPDATE [ OF column_name [ ,…n ] ] } ]，其中update定义游标中可更新的列。

游标可认为是C语言的指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理。

四个步骤：

定义游标
打开游标
提取数据
关闭游标

与游标相关的系统全局变量：

@@fetch_status：返回FETCH语句的最后状态

0——表示获取成功，-1——表示获取失败或此行不在结果集中，-2表示被提取的行不存在。
@@cursor_rows：返回本次在服务器连接中打开游标取回的数据行的数目。

---判断成绩是否及格
declare @xuehao char(10),@kechenghao char(10)
declare @chengji int
declare cc1 cursor for select sno,cno,grade from sc  --定义游标cc1
open cc1     --打开游标,执行与游标相关联的查询语句(查询结果放入缓存中)
--从游标中取得第一记录内容存入变量,还有last,prior最前/最后
fetch next from cc1 into @xuehao,@kechenghao,@chengji
while @@fetch_status=0  --@@fetch_status为系统自定义的变量,判断获取是否成功,即记录是否取得完成
   begin
　  if @chengji<60
         print @xuehao+@kechenghao+'不及格'
　    else
         print @xuehao+@kechenghao+'及格'
	fetch next from cc1 into @xuehao,@kechenghao,@chengji
  end
close cc1

利用游标修改数据：

更新操作：

update 表名 set 列名= 表达式 where current of 游标名
删除操作：

delete from 表名 where current of 游标名

current of 游标名：指对当前游标数据行操作

A、---对成绩表按不同的分数段加不同的成绩
--delare 游标名 cursor for select 语句
declare @xuehao char(10),@kechenghao char(10)
declare @chengji int
--定义游标cc3
declare cc3 cursor for select sno,cno,grade from sc for update of grade
open cc3    	 --打开游标;执行与游标相关联的查询语
fetch next from cc3 into @xuehao,@kechenghao,@chengji
--从游标中取得第一记录内容存入变量
while @@fetch_status=0  --判断获取是否成功
   begin 
   if @chengji>90
         update sc set grade=grade+3
                   where current of cc3
   else 
   	  if @chengji>80
         update sc set grade=grade+5
                   where current of cc3
   else
      if @chengji>70
         update sc set grade=grade+7
                   where current of cc3 
   else
       if @chengji>60
         update sc set grade=grade+8
                   where current of cc3
    else
       update sc set grade=grade+8
                   where current of cc3
 fetch next from cc3 into
　　　　 @xuehao,@kechenghao,@chengji
 end
close cc3

4.1.2.2 事务

事务（transaction）是并发控制逻辑上的基本单位。

事务处理控制语句：

begin tain —开始一个事务名
commit tain ——提交一个事务
rollback ——回滚一个事务（在发生情况时，撤消提交）

4.1.3 存储过程

存储过程是一组预先编译好的T-SQL代码。

系统存储过程以SP_或DT_开头

创建存储过程的语法：

create procedure 存储过程名 [{@参数名数据类型}[=default][output]
[{@参数名数据类型}[=default][output]
AS Select 语句

create procedure xs as select * from 学生表

exec xs --执行存储过程

含参数：

create procedure student_grade3
  @student_name char(10),
  @course_name char(20)='数据库原理'
as 
  select sname,cname,grade
    from student s inner join sc on s.sno=sc.sno 
    inner join course c on c.cno=sc.cno
    where sname= @student_name
       and cname= @course_name
exec student_grade3 '王五' 	--没给第二个参数,直接赋默认参数值
exec student_grade3 @student_name ='王五', @course_name='数据库原理'

带输出参数的存储过程（带output）：

带有output标识的参数在存储过程中的修改将被保存；如果不带，那么当存储过程运行结束后，对参数的修改将消失。

如果要设置output，形参和实参要同时设置。

在sql中形参可以改变实参，下面的例子中，使用了output关键字使得其与result（也就是实参）进行了关联。var3在函数内部是形参，对var3的修改会反映到result上。

create proc proc1
  @var1 int,
  @var2 int,
  @var3 int output
as 
  set @var3=@var2*@var1
declare @result int 
exec proc1 5,7, @result output	--在sql中形参可以改变实参(相当于C语言的地址传递)
print @result

create proc proc11
  @cn char(20),
  @avggrade int output
 as
   select @avggrade=avg(grade) from sc join
     course on sc.cno=course.cno 
	   where cname=@cn
declare @aa int
exec proc11 '数据库原理', @aa output 
print @aa

[例]查找老师编号并改变职称。

create proc changaa (@ttno int)
as
 declare @a int
 declare @b int
 set @a=0
 select @b=COUNT(*) from teacher where tno=@ttno
 if @b>0
   begin
     set @a=1
	 update teacher set proff='教授' where tno=@ttno
   end
return @a
exec changaa 804;

[例]创建一个新的表。然后创建一个存储过程，存储每个系每个分数段的学生统计成果。

分数段为90-100，每个系每门课有多少人？

分数段为80-89，每个系每门课有多少人？

分数段为70-79，每个系每门课有多少人？

分数段为60-69，每个系每门课有多少人？

分数段为<60，每个系每门课有多少人？

drop table hh2

create proc procaaaa 
as
select sdept, cname, '>90------' fengshuduan, 
count(*) tonjishu, avg(grade) pingjun
 into hh2 
  from student join sc on student.sno=sc.sno
    join course on sc.cno=course.cno
	  where grade>90 
	    group by sdept,cname 
delete from  hh2 
---select * from hh2 

insert into hh2 
select sdept, cname, '>90' fengshuduan, 
count(*) tonjishu, avg(grade) pingjun
  from student join sc on student.sno=sc.sno
    join course on sc.cno=course.cno
	  where grade>=90 
	    group by sdept,cname 

insert into hh2 
select sdept, cname, '80-89' fengshuduan, 
count(*) tonjishu, avg(grade) pingjun
  from student join sc on student.sno=sc.sno
    join course on sc.cno=course.cno
	  where grade>=80 and grade<90
	    group by sdept,cname 
	    
insert into hh2 
select sdept, cname, '70-79' fengshuduan, 
count(*) tonjishu, avg(grade) pingjun
  from student join sc on student.sno=sc.sno
    join course on sc.cno=course.cno
	  where grade>=70 and grade<80
	    group by sdept,cname 

insert into hh2 
select sdept, cname, '60-69' fengshuduan, 
count(*) tonjishu, avg(grade) pingjun
  from student join sc on student.sno=sc.sno
    join course on sc.cno=course.cno
	  where grade>=60 and grade<70
	    group by sdept,cname 
	    
insert into hh2 
select sdept, cname, '<60' fengshuduan, 
count(*) tonjishu, avg(grade) pingjun
  from student join sc on student.sno=sc.sno
    join course on sc.cno=course.cno
	  where grade<60 
	    group by sdept,cname

4.1.4 触发器

定义：触发器是一种特殊的存储过程。它与表紧密相连，主要用来保证数据的完整性。

create trigger 名 on 表名 for [after][instead of][insert | delete | update ]
AS
SQL语句
deleted 删除数据的表
inserted 插入新数据的表

create trigger mytr on spk1 for delete  --设置一个删除触发器 
as
declare @OID char(10)  --定义一个变量，用于从逻辑表中存储被删除的记录信息
select @OID=商品代号 from deleted   --从逻辑表中检索被删除的商品代号
delete from spk2 where 商品代号=@OID
delete from spk1 where 商品代号='DSJ-120'--此条语句执行后，相应的在SPK2中的DSJ-120记录也被删除。

[例]插入数据触发触发器的语句。

create trigger tt2 on sc for insert
as
   if exists(select * from inserted where grade<0 or grade>100)
    begin
		print '成绩要在0-100之间'
		rollback
	end
insert into sc values ('106','005',-195);

保证update合法：

Create trigger tri_grade on sc for update as
if update(grade) 
   if exists(select * from inserted join deleted on inserted.sno=deleted.sno where inserted.grade>=60 and deleted.grade<60)
   rollback

[例1]在sc表中定义一个保证课程号在课程表的课程范围内的插入触发器。

create trigger tt3 on sc for insert
as
   if exists( select * from inserted where cno not in
	  (select cno from course))
	  rollback

[例2]在sc表上定义一个限制学生选课门数不能超5门的触发器。

drop trigger tg3
create trigger tg3 on sc for insert
as
	declare @aa int
	select @aa = count(*) from sc where sno =(select sno from inserted)
	if @aa>=5
		rollback

[例3]在course表上定义一个限制删除有学生选课程的触发器。

create trigger tg4 on course for delete
as
	if exists(select * from sc where cno in (select cno from deleted))
		rollback

PART2.Oracle

4.2.1 PL/SQL块的组成

一个基本的PL/SQL块由3部分组成：定义部分（DECLARE），可执行部分（BEGIN），异常处理部分（EXCEPTION）。

declare

变量定义

begin

程序主体[可执行部分]

exception

异常处理部分

end

简单变量定义的一般格式：
<variablename> [CONSTANT]<datatype> [[NOT NULL] {DEFAULT|:=} <expression>];

[说明]

在PL/SQL中使用的变量、常量、游标和异常处理的名字都必须先定义后使用。
定义部分是包括在关键字DECLARE和BEGIN之间的部分，每条语句后用’;’结束。
每行只能定义一个标识符。
如果加上关键字CONSTANT,则表示所定义的标识符为一个常量，必须为它赋初值。
标识符的命名规则与SQL的规则基本相同，即每个标识符必须以字母开头，而且不分大小写。如果定义的标识符不能为空，则必须加上关键字NOT NULL,并赋初值。
为标识符赋值时，使用赋值符号’:=‘，默认值为空。

4.2.1.1 定义

Oracle中常用数据类型：

数据类型	描述
Number	数字型，Number(8,2)表示总共有8个有效数字，小数点后有两位
Char	字符型，最大2000个字符
Date	日期型，包含日期、小时、分、秒
Long	大文本
Boolean	逻辑型，取值为true、false、null
Varchar2	变长字符型，最大为4000个字符

声明一个变量，使它的类型与某个变量或数据库基本表中某个列的数据类型一致（不知道该变量或列的数据类型）可以使用%TYPE。

DECLARE
	teacher_name char（5）;
	student_name teacher.name%TYPE;
BEGIN
--……
END

记录类型定义的一般格式：

TYPE <recordtypename>[记录类型名] IS RECORD
( <field1> <datatype> [[NOT NULL]{DEFAULT|:=} <expression>]
[,<field2> …] )；

[说明]

标识符是定义的记录类型名
记录类型可以参照C语言的结构体类型
要定义记录型变量，定义方法与前面标量型变量定义一样
记录类型变量的属性引用方法是**’.’引用**
声明一个记录型变量，使它的类型与某个基本表的数据结构一致，可以使用**%ROWTYPE**的形式定义。

4.2.1.2 可执行部分

组成语句：

变量赋值语句
数据查询、数据操纵和事务控制语句

赋值语句：

1.标量型变量赋值

TEACHER_NAME := 'LIU';

2.记录类型变量的赋值

STUDENT1.NAME :='FAN';

3.%ROWTYPE型变量的赋值

EMP_VALUE.NAME:='BLACK';

4.用SELECT语句为%ROWTYPE型变量整个赋值

在PL/SQL中，SELECT语句必须与INTO子句相配合，在INTO子句后面跟需要赋值的变量。

SELECT * INTO EMP_VALUE FROM EMP WHERE ENAME:=’FA’;

[例1]计算表EMP中所有雇员的平均工资。

DECLARE
--定义一个数值型变量。
avg_sal number(8,3);
BEGIN
--使用select语句进行赋值
select avg(sal)
into avg_sal
from emp;
--输出变量结果。
dbms_output.put_line(avg_sal);
END;
/	--程序写完加/
--使用DBMS_OUTPUT.PUT_LINE之前，应该设置环境变量SERVEROUTPUT。(SET SERVEROUTPUT ON)

4.2.1.3 事务控制命令

事务（transaction）的概念：

事务是指在相邻两条事务控制命令之间的一组sql语句，它是对数据库操作的逻辑单元。

事务控制命令有：

COMMIT (事务提交)
ROLLBACK（事务回退）
SAVEPOINT（设置保存点命令）

在Oracle数据库中，进程包含：

数据处理区	日志区

在缓冲区内存在：

data	log

与磁盘间建立连接。

==事务提交命令==（Commit）：

支持先期写入协议：先写日志，才能写数据；
当发生不可控因素使得数据未写入其中时，观察日志，若发现事务并没有结束，则执行undo撤销还原流程；若发现事务开始、结束流程已经完成，则执行redo重做流程，写入data区。
提交命令就是使对数据的改变永久化，即：脏数据（指数据与磁盘间数据不一致）—>干净数据。
Oracle中设置了一个自动提交的开关，如果把它设置为ON,那么所有的改变数据库中数据的语句都会立即生效，影响数据库中的数据，缺省值为OFF。格式：SET AUTOCOMMIT [ON|OFF];

==事务回退命令==（Rollback）：

在尚未对数据库提交的时候，可以用事务回退命令ROLLBACK，将数据库回退到上次COMMIT后的状态。

一旦事务已经提交，就不能再使用事务回退命令进行回退了。

事务回退将回退整个事务，如果只需回退事务的一部分时，可以使用保存点，将整个事务划分为若干部分，这样就可以回退部分事务了。

保存点的设置和使用格式：

SAVEPOINT <保存点名称>;
ROLLBACK TO <保存点名称>;

4.2.1.4 流程控制

IF_THEN_ELSE语句：

IF <条件> THEN
<语句组1>
ELSE
<语句组2>
END IF;

[例]根据表emp中DEPTNO字段的值，为姓名为FAN的雇员修改工资；若部门号为10，则工资加100；若部门号为20，则工资加300；否则工资加400。

declare
    name emp.ename%type := 'FAN';
    increment emp.sal%type;
    fandept emp.deptno%type;
begin
    select deptno into fandept from emp where ename = 'FAN';
    --Sql Server语法
    --declare @sno int
    --select @sno = Sdept from emp where ename = 'FAN';
    if fandept = 10 then 
        increment :=200;
    elsif fandept = 20 then
        increment :=300;
    else 
    	increment :=400;
    end if;
    update emp set sal = sal +increment where ename = 'FAN';
    commit;
end;

FOR循环：

FOR <循环变量> IN [REVERSE] <下界..上界>
LOOP
<语句组>
END LOOP;

[示例]从键盘接受一个整数，计算它的阶乘，并在屏幕上打印出来。

全局变量的引用时，必须加上’:’。

variable fac number		--全局变量
DECLARE
NUM NUMBER(3):=5;
BEGIN
    :fac:=1;
    if NUM>0 then
        for I in 1..NUM loop	--Sql Server使用while循环
            :fac:=:fac * I;
        end loop;
    end if;
END;
/
PRINT fac;

直到型循环：

LOOP
<语句组1>
EXIT [WHEN <条件>]；
<语句组2>
END LOOP;

执行过程：
先执行循环体<语句组1>，然后判断<条件>，如果条件为真，则结束循环，否则继续循环。

说明：
直到型循环的循环体至少执行1次。

当型循环：

WHILE <条件> LOOP
<语句组>
END LOOP;

标号

在循环中的语法格式：

《标号》
LOOP
<语句组1>
EXIT 标号 [WHEN <条件>]；
<语句组2>
END LOOP 标号 ;

GOTO语句：

《标号》
…
GOTO 标号；

4.2.2 游标

定义游标

CURSOR <游标名> IS <SELECT 语句>;

[说明]

PL/SQL块中，游标的定义应该放在定义部分。

打开游标

OPEN <游标名>；

[说明]

打开游标，实际上是执行游标定义时对应的SELECT语句，将查询结果检索到工作区中。

提取数据

FETCH <游标名> INTO 变量1,变量2,……

[说明]

游标指针只能向下移动，不能回退。

关闭游标

CLOSE <游标名>；

[说明]

关闭游标的作用在于，使游标所对应的内存工作区变为无效，并释放与游标相关的系统资源。

[例]修改表emp中各个雇员的工资，若雇员属于10号部门，则增加$100，若雇员属于20号部门，则增加$200，若雇员属于30号部门，则增加$300.

DECLARE
    Row emp%rowtype;	--记录型变量
    Increment number(4);
    CURSOR cursor_emp IS SELECT * FROM EMP;
    i number(2);
BEGIN
    select count(*) into i from emp;
    OPEN cursor_emp;
    <<label>>
    FETCH cursor_emp INTO row;
    if row.deptno = 10 then
    	increment:=100;
    elsif row.deptno= 20 then
    	increment:=200;
    else
    	increment:=300;
    end if;
    update emp set sal = sal + increment where ename = row.ename;
    i:=i - 1 ;
    if i>0 then goto label;
    end if;
    close cursor_emp;
end;
/

游标的属性

%ISOPEN
该属性是布尔型。如果游标已经打开，返回TRUE,否则为FALSE。

%FOUND
布尔型，如果最近一次使用FETCH语句，有返回结果则为TRUE,否则为FALSE;（相当于Sql Server的@@fetch_status）

%NOTFOUND
布尔型，如果最近一次使用FETCH语句,没有返回结果则为TRUE,否则为FALSE;

%ROWCOUNT
数值型，描述的是到目前为止实际从游标工作区抽取的记录数。

[说明]

游标属性只能在PL/SQL块中使用，不能在SQL命令中使用。

属性的引用格式：游标名[属性名]

[说明]属性名与游标名之间没有空格。

上例可改为：

DECLARE
    row emp%rowtype;
    increment number(4);
    CURSOR cursor_emp IS SELECT * FROM EMP;
    I NUMBER(2);
BEGIN
    OPEN cursor_emp;
    <<label>>
    FETCH cursor_emp INTO row;
    if row.deptno = 10 then
    	increment:=100;
    elsif row.deptno = 20  then 
    	increment := 200;
    else
    	increment:= 300;
    end if;
    update emp set sal = sal+ increment where ENAME = ROW.ENAME;
    if cursor_emp%FOUND then
    	goto label;
    end if;
END;
/

==FOR循环中游标的使用==：

FOR <变量名> IN <游标名> LOOP
<循环体>
END LOOP;

[说明]

系统自动打开游标，不用显式地使用OPEN语句打开；
系统隐含地定义了一个数据类型为%ROWTYPE的变量，并以此作为循环的计数器；
系统重复地自动从游标工作区中提取数据并放入计数器变量中；
当游标工作区中所有的记录都被提取完毕或循环中断时，系统自动地关闭游标。

DECLARE
  increment number(4);
  CURSOR cursor_emp IS SELECT * FROM EMP;
BEGIN
  FOR emp_record in cursor_emp LOOP		--emp_record无需定义,自动就为记录型变量
      if emp_record.deptno = 10  then
         increment:=100;
      elsif emp_record.deptno = 20  then
         increment := 200;
      else
        increment:= 300;
      end if;
      update emp set sal = sal+ increment where EMPNO = emp_record.EMPNO;
  END LOOP;
END;

带参数游标的使用：

CURSOR 游标名（<参数1> <类型> [,<参数2><类型>…]）
IS < SELECT 语句>；

打开带参数的游标时，参数个数和数据类型必须与其定义时保持一致。

DECLARE
    increment number(4);
    CURSOR cursor_emp(v_deptno emp.deptno%type)
      IS SELECT * FROM EMP WHERE deptno = v_deptno;		--定义参数后,查询语句内一定要用到参数
BEGIN
    for I in 1..3 loop
        for emp_record in cursor_emp(I*10) loop
            increment:=100*I;
            update emp set sal = sal +increment where EMPNO= emp_record.empno;
        end loop;
    end loop;
    commit work;
end;
/

4.2.3 PL/SQL中的异常处理

预定义异常：

对于Oracle预定义的异常，当预定义的情况发生时，系统将自动触发。

用户自定义的异常：

需要程序员自己定义代码，需抛出异常（使用==RAISE语句==），对异常情况进行处理。

如果没有对异常进行处理，程序将结束。

异常处理的一般格式：

DECLARE
…;
BEGIN
…;
EXCEPTION
WHEN 异常情况1 [OR 异常情况2…] THEN
…;
WHEN 异常情况3 [OR 异常情况4…] THEN
…；
WHEN OTHERS THEN
…；
END;

[例]处理预定义异常的一个例子。

DECLARE
    cursor emp_cursor is select * from emp;
    record emp%rowtype;
begin
	fetch emp_cursor into record;
exception
    when INVALID_CURSOR then
    if emp_cursor%isopen then
    	null;
    else
        open emp_cursor;
        fetch emp_cursor into record;
        close emp_cursor;
    end if;
    when others then
    	null;
end ;
/

自定义异常：

DECLARE
    out_of_stock EXCEPTION;
    number_on_hand NUMBER;
BEGIN
…
    IF number_on_hand < 1 THEN
    RAISE out_of_stock;
    END IF;
…
EXCEPTION
    WHEN out_of_stock THEN
    	--handle the error
END;

4.2.4 存储过程

CREATE [OR REPLACE] PROCEDURE procedure_name
 [(parameter1 [{输入输出IN|OUT|IN OUT}] datatype
 [{:=|DEFAULT} expression]
 [, parameter2 [{IN|OUT|IN OUT}] datatype
 [{:=|DEFAULT} expression]
 …])]
 [AUTHID {CURRENT_USER|DESIGNER}]
 {IS|AS}
 [declarations]
BEGIN
 code
[EXCEPTION
exception_handlers]
END

由定义可以看出，存储过程的定义有两部分：声明部分，程序主体部分。主体部分实质上是PL/SQL块。

参数类型：

参数的数据类型可以采用PL/SQL语言的标准数据类型。但是，在定义过程参数时，==不能强制说明参数的长度==，否则将出错。
如果要让定义的参数严格和表定义或者表中的某一列的定义一样，这需要使用属性**%ROWTYPE、%TYPE**。

参数样式：

参数的有效样式为：IN、OUT、IN OUT三种。

IN 该参数将作为一个输入变量。这个变量为只读变量，在过程体中不允许被赋值——程序外传给存储过程
OUT 该参数将作为过程的返回值，被调用者使用。在过程体中不允许使用它的值，只能对它赋值——形参可以改变实参
IN OUT 该参数既可以作为输入参数，也可以作为输出参数

[例1]

CREATE OR REPLACE PROCEDURE printLine(width IN INTEGER,chr IN CHAR DEFAULT ‘-’)	--char不能指定长度
IS
BEGIN
    FOR i IN 1..width LOOP
        dbms_output.put(chr);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(‘’);
END printLine;
--调用
begin
	printline(40,’*’);
end;
begin
	printline(20,’=‘);
end;

[例]存储过程query_stu，作用：实现对student表的查询，要求输入学号，得到学生的具体情况。

set serveroutput on;
create or replace procedure query_stu(sno_p in student.sno%type)	--参数
As	--开始为程序主体
   not_exist exception;	--定义异常
   temp student%rowtype;	--定义记录型参数
   cursor judge is select * from student where sno=sno_p;
   i number; 
 begin
  open judge;
  fetch judge into temp;
  if judge%notfound then 
      raise not_exist;
  end if;
  DBMS_OUTPUT.PUT_LINE('sno'||'  '||'sname'||'  '||'sex'||'  '||'birthday'||'  '||'class');	--'||'为制表位
  DBMS_OUTPUT.PUT_LINE(temp.sno||'  '||temp.sname||'  '||temp.sex||'  '||temp.birthday||' '||temp.class);
  close judge;
exception
  when not_exist then
  DBMS_OUTPUT.PUT_LINE('student is not found!');
  close judge;
end;
/

4.2.5 存储函数

与存储过程相比，多一个return returntype（如char等）。

[例1]

CREATE OR REPLACE FUNCTION totalsal(v_empno in emp.empno%type) RETURN number
IS
	totalsal1 number;
Begin
	select sal + comm into totalsal1 from emp where empno =v_empno;
	return totalsal1;
End;     
--调用
select empno,totalsal(empno) from emp where totalsal(empno)>300

[例2]

create or replace function f1(sno1 student.sno%type) return int
is
	e1 exception;
	t1 student%rowtype;		--记录型变量用于提取数据
	cursor c3 is select * from student where sno = sno1;
begin
	open c3;
	fetch c3 into t1;
	if c3%notfound then
		raise e1;
	end if;
	update student set sage = sage +1 where sno = sno1;		--年龄加一
	return 1;
exception
	return -1;
end;

4.2.6 触发器

CREATE [OR REPLACE] TRIGGER trigger
{BEFORE|AFTER} –先执行触发器还是先执行语句
{DELETE|INSERT|UPDATE [OF column [,column]…]}
[OR{DELETE|INSERT|UPDATE [OF column [,column]…]}]…
ON table
FOR EACH ROW [WHEN condition] –每执行一行语句调用一次
…pl/sql block.. –块语句
END [trigger]

CREATE OR REPLACE TRIGGER logemp AFTER INSERT OR UPDATE OR DELETE ON EMP
DECLARE
	statementtype CHAR(20);
BEGIN
	IF INSERTING THEN
		statementtype:='INSERT!';
	ELSIF UPDATING THEN
		statementtype:='UPDATE!';
	ELSE
		statementtype:='DELETE!';
	END IF;
    DBMS_OUTPUT.PUT_LINE(statementtype);
END;

被删的记录放在**:old全局变量（记录型变量）中，新记录放在:new**全局变量中。

[例1]触发器before_del, 当要在teacher表中删除一个老师时判断老师是否已经授课，如果有的话，引发一个异常，返回给用户无法删除的消息。

create or replace trigger before_del before delete on teacher for each row
declare
	temp course%rowtype;
	cannot_delete exception;
	cursor judge is select * from course where tno=:OLD.tno;
begin
	open judge;
	fetch judge into temp;
	if judge%found then raise cannot_delete;	--Oracle抛出异常会自动将当前操作取消
	end if;
	close judge;
exception
	when cannot_delete then
	raise_application_error(-20002,'You can not delete this teacher!');
	close judge;
end;
/

[例2]在sc表上定义一个限制学生选课门数不能超5门的触发器。

create trigger t6 before insert on sc for each row
declare 
	i int;
	e4 exception;
begin
	select count(*) into i from sc sno = :new.sno;
	if i>=5 then raise e4;
	end if;
exception
	--……
end;
/

ch5.关系数据理论

数据依赖包括函数依赖和多值依赖。

5.1 函数依赖

5.1.1 函数依赖的定义

设$R(U)$是属性集$U$上的关系模式， $X,Y\subseteq U$ （$X,Y$是属性），若对于$\forall r(值)∈R(U)$，$\forall t,s∈r$（元组）,如果$t[X]=s[X]$（若每个都不相等也满足条件），能推出$t[Y]=s[Y]$，则称“X函数决定Y”,或“Y函数依赖于X”,记作：$X\rightarrow Y$，称$X$为决定因素。

若$\exists t, s \in r, t[X]=s[X]$，但$t[Y]\neq s[Y]$，则称“X不函数决定Y”或“Y不函数依赖于X”,记作：$X\nrightarrow Y$。

函数依赖关系可以理解成关系数据库中“码”的理论基础；函数依赖与别的数据依赖一样是语义范畴的概念，只能根据语义来确定一个函数依赖。

SDC中的函数依赖：$SNO\rightarrow DEPT,DEPT\rightarrow MNAME,(SNO,CNAME)\rightarrow GRADE$

5.1.2 函数依赖的分类

（1）平凡的函数依赖与非平凡的函数依赖

如果$X\rightarrow Y$，且$Y \nsubseteq X$，则称$X\rightarrow Y$为非平凡的函数依赖。
如果$X\rightarrow Y$，且$Y \subseteq X$，则称$X\rightarrow Y$为平凡的函数依赖。【必然成立】

如：$SNO\rightarrow SNO$是平凡的函数依赖。

在后面的讨论中，一般函数依赖指非平凡的函数依赖。

（2）完全函数依赖与部分函数依赖

如果$X\rightarrow Y$，对于任意$X$的真子集$X^{\prime}$，都有$X^{\prime}\nrightarrow Y$，则称$Y$对$X$完全函数依赖，记作$X \stackrel{f}{\longrightarrow} Y$
如果$X\rightarrow Y$，存在$X$的真子集$X^{\prime}$，使得$X^{\prime}\rightarrow Y$ ，则称$Y$对$X$部分函数依赖，记作$X \stackrel{p}{\longrightarrow} Y$

（3）传递函数依赖与直接函数依赖

在$R(U)$中，如果$X \rightarrow Y(Y \nsubseteq X),Y\nrightarrow X,Y \rightarrow Z$，则称$Z$对$X$传递函数依赖

5.1.3 函数依赖的逻辑蕴含

关系模式$R$，$F$是其函数依赖，$X,Y$是其属性子集，如果从$F$的函数依赖能够推出$X\rightarrow Y$，则称$F$逻辑蕴含$X\rightarrow Y$，记作$F\models X\rightarrow Y$。

被$F$所逻辑蕴含的函数依赖的全体所构成的集合称作$F$的闭包，记作$F^{+}$。[即根据已知F函数依赖能推导出来的集合]

示例：

$R(X, Y), F = {X\rightarrow Y}$

$F^{+} = {X\rightarrow \varnothing, X\rightarrow X, X\rightarrow Y,X\rightarrow XY,
Y\rightarrow \varnothing, Y\rightarrow Y,
XY\rightarrow \varnothing,XY\rightarrow X,XY\rightarrow Y,XY\rightarrow XY}$

5.1.4 Armstrong公理系统

为了从一组函数依赖求得蕴含的函数依赖，为了确定一个关系模式的码，就需要一套推理规则。下面介绍函数依赖的公理系统——Armstrong公理系统。

对于关系模式$R(U,F)$，设$F$是$U$上的一组函数依赖，则：

自反律：若$Y\subseteq X\subseteq U$，则$X \rightarrow Y$为$F$所蕴含；
增广律：若$X \rightarrow Y$为$F$所蕴含（$X\rightarrow Y∈F^{+}$），$Z\subseteq U$，则$XZ\rightarrow YZ$为$F$所蕴含；【两边加相同的属性】
传递律：若$X \rightarrow Y$及$Y \rightarrow Z$为$F$所蕴含（$F^{+}$），则$X \rightarrow Z$为$F$所蕴含。

三个有用的推理规则：

合并规则：由$X \rightarrow Y$,$Y \rightarrow Z$,有$X \rightarrow YZ$；
伪传递规则：由$X \rightarrow Y$,$WY \rightarrow Z$,有$XW \rightarrow Z$；[推导：1式两边都加$W$]
分解规则：由$X \rightarrow Y$,$Z\subseteq Y$,有$X \rightarrow Z$。[推导：$Z\subseteq Y$是平凡函数依赖，则$Y\rightarrow Z$，然后传递]

[推论]$X\rightarrow Y,X\rightarrow Z,X\rightarrow A \longleftrightarrow X\rightarrow YZA$

5.1.5 由函数依赖定义的码

候选码：设$K$为$R$的属性或属性组，若$K \stackrel{f}{\longrightarrow} U$（$K$要完全决定$U$，即**$K$的任意真子集都不能决定$U$**），则称$K$为$R$的候选码

主码：若$R(U , F)$有多个候选码，则可以从中选定一个作为R的主码

主属性：包含在任何一个候选码中的属性

非主属性：不包含在任何候选码中的属性

全码：关系模式的码由整个属性组构成

5.1.6 候选码的求解方法

给定一个关系模式$R(U,F)$，$U={A_{1},A_{2},…,A_{n}}$，$F$是$R$的函数依赖集，那么，可以将属性分为如下四类：

L：仅出现在函数依赖集F左部的属性
R：仅出现在函数依赖集F右部的属性
LR：在函数依赖集F左右部都出现的属性
NLR：在函数依赖集F左右部都未出现的属性

==求候选码的规则==如下：

如果有属性不在函数依赖集中出现，那么它必须包含在候选码中

[解释]不在函数依赖集中出现，即只有自己可以决定自己，别人不能决定它，该属性可以唯一标识元组，包含在候选码中。
如果有属性不在函数依赖集中任何函数依赖的右边出现，那么它必须包含在候选码中
如果有属性只在函数依赖集的左边出现，则该属性一定包含在候选码中
如果有属性或属性组能唯一标识元组，则它就是候选码（验证候选码是否唯一或有多个，也用于最后判断）

5.2 范式及其规范化

范式是对关系（关系模式$R$）的不同数据依赖程度的要求。各种不同的范式都是以对关系模式的属性间允许的数据依赖加以限制的形式表示的。

关系的范式三类异常：插入异常、更新异常、删除异常，另外还有数据冗余问题。

若$R(U,F)$符合$x$范式的要求，则称$R$为$x$范式，记作：$R\in xNF$ 例：x好学生,有德、德智、德智体……

通过模式分解将一个低级范式转换为若干个高级范式的过程称作规范化；限制最少，数据依赖条件越少，范围最广。

5.2.1 1NF

属性不可再分，是原子量

定义：分量是否需要再分，与具体应用有关；如果用到值的一部分，则需要进一步分割。

如果只是查询出生日期，则它满足$1NF$；
如果查询两人生日是否相同，则只比较月、日，需要将生日分解，就不满足$1NF$。

姓名	生日
王军	98.7.10
张立	99.7.10
李明	90.3.28

姓名	年	月日
王军	98	7.10
张立	99	7.10
李明	90	3.28

5.2.2 2NF

若$R\in 1NF$，且每个非主属性完全依赖于码，则称$R\in 2NF$，即**$2NF$不允许有非主属性对码的部分依赖**；若R中有非主属性对码的部分依赖，则$R\notin 2NF$。

[例1]关系模式$SDC(SNO,DEPT,MNAME,CNAME,GRADE)$,判断$SDC\in 2NF$？

步骤：

先根据函数依赖确定候选码，再观察每个非主属性是否完全依赖于码。

因为$DEPT,MNAME$部分依赖于码$(SNO,CNAME) $，所以$SDC\notin 2NF$ 。

若码为一个，一定是完全依赖关系；

消除非主属性对码的部分依赖——模式分解

改造：

非主属性有两种，一种完全依赖于码，一种部分依赖于码。

将$SDC$分解为：

$SC(SNO , CNAME , GRADE)\in 2NF$

$SDM(SNO , DEPT , MNAME)\in 2NF$

解决了部分插入异常和更新异常，删除异常还存在

[例2]关系模式$R(A,B,C,D)$，码为$AB$，给出它的一个函数依赖集，使得$R$属于$1NF$而不属于$2NF$。

$F={(A, B) \stackrel{f}{\rightarrow} C, \quad B \rightarrow D}$

5.2.3 3NF

关系模式$R$中，若不存在这样的码$X$，属性组$Y$及非主属性$Z(Z\nsubseteq Y)$，使得下式成立：

X->Y,Y-\>X,Y->Z

则称$R\in 3NF$。即$3NF$要求：非主属性既不部分也不传递依赖于码。

[例1]判断$SDM (SNO , DEPT , MNAME) \in 3NF$是否成立。

因为$SNO \rightarrow DEPT,DEPT\rightarrow MNAME$，所以$SDM\notin 3NF$

模式分解的方法是：消除非主属性对码的传递依赖

改造：

将$SDM(SNO,DEPT,MNAME)$分解为：

$SD (SNO , DEPT) \in 3NF$ 二元/目关系

$DM(DEPT , MNAME) \in 3NF$

[例2]$SC(SNO , CNAME , GRADE) \in 3NF$不能传递。

全码：所有属性加一起才能构成码，即$U \stackrel{f}{\longrightarrow} U$,必满足$3NF$；

任意二元关系模式都属于第三范式。

[例3]关系模式$R(A,B,C,D)$，码为$AB$，给出它的一个函数依赖集，使得$R$属于$2NF$而不属于$3NF$。

$F={(A,B) \rightarrow C,C\rightarrow D}$

[例4]设有关系模式$R(A,B,C)$，根据语义有如下函数依赖集：$F={A\rightarrow B,(B,C)\rightarrow A}$。关系模式R的规范化程度最高达到（C）

A）1NF B）2NF C）3NF D）4NF

[解]本题中的候选码为**(A,C),(B,C)**，所有的属性均为主属性。

5.2.4 BCNF

每一个决定性因素都包含码。

定义：关系模式$R$中，对于属性组$X,Y$，若$X\rightarrow Y$且$Y \nsubseteq X$时$X$必含有码，则$R\in BCNF$

如$STC \notin BCNF$，因为$T\rightarrow C$，而$T$不含有码；

$ STC(S , T , C)$关系模式中，每位老师只教授一门课，每门课有若干老师。某学生选定一门课，就对应一位老师。则：

它的一组函数依赖为：$T\rightarrow C,(S,T)\rightarrow C,(S,C)\rightarrow T$，候选码为$(S,T)$和$ (S,C)$

此例子可说明：**全是主属性不一定属于$BCNF$，但一定属于$3NF$**。

改造：

将$STC (S , T , C)$分解为：

$ST(S,T)\in BCNF ,TC(T,C)\in BCNF.$

结论：

任何一个二元关系都是BCNF的；

全码属于BCNF。

在函数依赖的范畴内，BCNF达到了最高的规范化程度。

5.3 多值依赖

5.3.1 多值依赖的定义

在一个关系表中，如果给定一个属性集合，该属性集合的值可以决定其他属性集合的所有可能取值，则称这两组属性存在多值依赖关系。

1.描述型：

关系模式$R(U)$，$X,Y,Z \subset U$，并且$Z = U – X – Y$，若对$R(U)$的任一关系$r$，对给定的一对$(x,z)$值有一组$Y$的值，这组值仅仅决定于$x$值而与$z$值无关，则称$Y$多值依赖于$X$，记作：$X\rightarrow \rightarrow Y$。

关系模式$TEACH(C#, T#, B#)$ ，一门课程由多个教员担任，一门课程使用相同的一套参考书。

C# T# B#

C1 {T1,T2} {B1,B2}

C2 {T1,T3} {B3,B4}

有：

C# T# B#

C1 T1 B1

C1 T1 B2

C1 T2 B1

C1 T2 B2

C2 T1 B3

C2 T1 B4

C2 T3 B3

C2 T3 B4

C#	T#	B#
C1	{T1,T2}	{B1,B2}
C2	{T1,T3}	{B3,B4}

C#	T#	B#
C1	T1	B1
C1	T1	B2
C1	T2	B1
C1	T2	B2
C2	T1	B3
C2	T1	B4
C2	T3	B3
C2	T3	B4

在关系模式$TEACH$中，对$(C1 , B1)$有一组$T#$值$(T1 , T2)$，对$(C1 , B2)$也有一组$T#$值$(T1 , T2)$，这组值仅取决于$C#$的取值，而与$B#$[如参考书]的取值无关。因此，$T#$多值依赖于$C#$，记作$C#\rightarrow \rightarrow T#$，同样有$C#\rightarrow \rightarrow B#$。

2.标准化：

关系模式$R(U)$，$X、Y、Z\subset U$，$Z=U–X–Y$，对于$R(U)$的任一关系$r$，若存在元组$t1$，$t2$，使得$t1[X] = t2[X]$，那么就必然存在元组$t3$，$t4$，使得：

$t3[X] = t1[X] = t4[X] = t2[X]$

$t3[Y] = t1[Y], t4[Y] = t2[Y]$

$t3[Z] = t2[Z], t4[Z] = t1[Z] $

则称$Y$多值依赖于$X$，记作：$X\rightarrow \rightarrow Y$。

若$X\rightarrow \rightarrow Y$，而$Z=\varnothing$，则称$X\rightarrow \rightarrow Y$为平凡的多值依赖。

例如：$(C#, T#, B#)$满足$C#\rightarrow \rightarrow T#$，含有元组$t1=(C1,T1, B1)$，$t2=(C1, T2, B2)$，则也一定含有元组$t3=(C1, T1, B2)$，$t4=(C1, T2, B1)$。

即：有三条记录，第四条记录肯定知道/肯定需找到。

5.3.2 多值依赖的性质

多值依赖具有对称性：若$X\rightarrow \rightarrow Y$，则$X\rightarrow \rightarrow Z$
==多值依赖具有传递性==：若$X\rightarrow \rightarrow Y$，$Y\rightarrow \rightarrow Z$，则$X\rightarrow \rightarrow Z-Y$。
函数依赖是多值依赖的特例：若$X\rightarrow Y$，则$X\rightarrow \rightarrow Y$。
若$X\rightarrow \rightarrow Y$，$X\rightarrow \rightarrow Z$，则$Y$、$Z$的交集、并集、差集多值依赖于$X$。
- 若$X\rightarrow \rightarrow Y$，$X\rightarrow \rightarrow Z$，则$X\rightarrow \rightarrow Y \cup Z$。
- 若$X\rightarrow \rightarrow Y$，$X\rightarrow \rightarrow Z$，则$X\rightarrow \rightarrow Y\cap Z$。
- 若$X\rightarrow \rightarrow Y$，$X\rightarrow \rightarrow Z$，则$X\rightarrow \rightarrow Y-Z$， $X\rightarrow \rightarrow Z-Y$。

5.3.3 多值依赖与函数依赖的区别

多值依赖是元组产生依赖，函数依赖是相等产生依赖。
- 函数依赖规定某些元组不能出现在关系中，也称为相等产生依赖。
- 多值依赖要求某种形式的其它元组必须存在于关系中，称为元组产生依赖。
多值依赖的有效性与属性集的范围有关，而函数依赖的有效性则与属性集的范围无关。

如：$X\rightarrow Y$在属性集$W(XY \subseteq W\subseteq U)$上成立，在$U$上也成立；而$X\rightarrow \rightarrow Y$在属性集$W(XY \subseteq W\subseteq U)$上成立，但在$U$上不一定成立。

多值依赖可以缩小范围，不可以扩大范围。如：若$X\rightarrow \rightarrow Y$在$U$上成立，则在属性集$W(XY \subseteq W\subseteq U)$上成立。

如：$A\rightarrow \rightarrow B$在$W=ABC$上成立，而在$U=ABCD$上不成立。 $(a1,{c1,d1})$ $ b1$ ； $(a1,{c1,d2})$ $b2$

A	B	C	D
a1	b1	c1	d1
a1	b1	c2	d1
a1	b2	c1	d2
a1	b2	c2	d2

关于子集的问题：
- 若$X\rightarrow Y$在$R(U)$上成立，则对于$\forall Y’ \subseteq Y, \quad X \rightarrow Y’$均成立。 [$Y\rightarrow Y’$是平凡的函数依赖]
- 若$X\rightarrow \rightarrow Y$在$R(U)$上成立，则不能断言对于$Y’ \subseteq Y$,有$X \rightarrow Y’$成立。

在上面的关系模式中，$A\rightarrow \rightarrow BC$成立，$A\rightarrow \rightarrow B$不成立。

5.3.4 4NF

定义：

关系模式$R\in 1NF$，若对于每一个非平凡(Z不能为空)的多值依赖$X\rightarrow \rightarrow Y$（$Y\nsubseteq X$），$X$都包含码，则称$R\in 4NF$。

如关系模式$TEACH(C#,T#,B#)$，$C#\rightarrow \rightarrow T#$，$C#\rightarrow \rightarrow B#$，码为$(C#, T#, B#)$，所以$TEACH\notin 4NF$。

改造：

将$TEACH$分解为：$CT(C#,T#)\in 4NF$，$CB(C#,B#)\in 4NF$。

在分解后的关系中，虽然仍有$C#\rightarrow \rightarrow T#$，$C#\rightarrow \rightarrow B#$，但它们是平凡的多值依赖，满足$4NF$，分量为$C_{i}$的元组共有$m + n$个。

在多值依赖的范畴内，属于4NF的关系模式已达到了最高的规范化程度。

任何一个二元关系都满足4NF。[只有两个属性不存在非平凡的多值依赖]

全码不一定属于4NF。（如上$TEACH$关系模式例子）

一个全是主属性的关系模式一定可以达到第3范式；

一个全码的关系模式一定可以达到$BCNF$范式。

范式之间的关系：

5.4 关系模式分解

引言：表的数量越多，查询速度越慢，更改（插入、更新、删除异常少）速度越快。

5.4.1 模式分解的定义和要求

模式分解的定义：

关系模式$R$的一个分解是指$\rho = {R_{1}<U_{1},F_{1}> ,R_{2}<U_{2},F_{2}>, … , R_{n}<U_{n},F_{n}>}$，其中$U = U_{1}\cup U_{2}\cup …\cup U_{n}$ ，并且没有$U_{i} \subseteq U_{j} $，$i\ge 1$，$j ≤n$，$F_{i}$是$F$在$U_{i}$上的投影。

模式分解的要求：

一个关系模式分解成若干个关系模式，则分解前后的模式要等价（即分解后没有信息的丢失）。等价的标准常用的有：

分解要具有无损连接性
分解要保持函数依赖
分解既要保持函数依赖又要具有无损连接性

5.4.2 无损分解与有损分解

无损分解的定义：

关系模式$R$ ，$U = U_{1}\cup U_{2}\cup …\cup U_{n}$ ，$\rho = {R_{1}<U_{1},F_{1}> ,R_{2}<U_{2},F_{2}>, … , R_{n}<U_{n},F_{n}>}$是$R$的一个分解，若对于$R$的任一个关系$r$，都有$r = \pi_{R_{1}}(r)\Join\pi_{R_{2}}(r)\Join … \Join \pi_{R_{n}}(r)$ , 则称$\rho$是$R$的一个无损连接分解。
即：对于$R$的任一个关系$r$，$r$在$R_{1},R_{2} , … , R_{n}$上的投影的自然连接等于$r$。

一个关系模式分解为两个模式时无损连接性的判别：

关系模式$R$分解为关系模式$R_{1}<U_{1},F_{1}>,R_{2}<U_{2},F_{2}>$是具有无损连接性的分解的充分必要条件是：

$(U_{1}\cap U_{2}\rightarrow U_{1}-U_{2})\in F^{+}$（或$(U_{1}\cap U_{2}\rightarrow U_{2}-U_{1})\in F^{+}$，即==交集决定差集成立==）, $F^{+}$是所有函数依赖推导的集合。

**[例]**设$R(U,F)$中$U=ABC,F={A\rightarrow B}$, 试判断$R$的下面两个分解的无损连接性：

$\rho_{1}={R_{1}(AB), R_{2}(AC)},\rho {2}={R{1}(AB), R_{2}(BC)}$。

因为对于$\rho_{1} $：$R_{1}\cap R_{2}=A, R_{1}－R_{2}=B$，无损；对于$\rho_{2} $：$R_{1}\cap R_{2}=B,R_{1}－R_{2}=A,R_{2}－R_{1}=C$ ，有损。

实例：

5.4.3 保持函数依赖

保持函数依赖的模式分解的定义：

设$\rho = {R_{1}<U_{1},F_{1}> ,R_{2}<U_{2},F_{2}>, … , R_{n}<U_{n},F_{n}>}$是$R$的一个分解，如果$F^{+} =(F_{1}\cup F_{2}\cup …\cup F_{n})^{+}$，则称$\rho $是保持函数依赖的分解。即：你能推导出来的我的集合也能推导出来，无函数依赖被丢失。

换句话说：$F$所逻辑蕴含的函数依赖一定也由分解得到的各个关系模式中的函数依赖所逻辑蕴含。

实例：

[例1]设关系模式$R<U, F>$，$U = {CITY, ST, ZIP}$,$F ={(CITY, ST) \rightarrow ZIP[邮政编码], ZIP\rightarrow CITY}$，试判断下列分解$\rho_{1}={R_{1}(ST,ZIP), R_{2}(CITY, ZIP)}$是否保持函数依赖？

[解]$\because \pi_{R_{1}}(F) = \varnothing,\pi_{R_{2}}(F) = {ZIP\rightarrow CITY},\pi_{R_{1}}(F)\cup \pi_{R_{2}}(F)= {ZIP\rightarrow CITY},$丢失了函数依赖$(CITY, ST)\rightarrow ZIP$

$ \therefore$分解是没有保持函数依赖的；

又$\because R_{1}\cap R_{2} ={ZIP}, R_{2}－R_{1} ={CITY},R_{1}\cap R_{2}\rightarrow R_{2}－R_{1},$

$\therefore$分解是无损的。

[例2]$R(ABC;A\rightarrow C,B\rightarrow C)$，码为$AB$。

（1）求具有无损连接性的分解。

分解为$R_{1}(AC;A\rightarrow C)$，$R_{2}(AB)$。

因丢失了函数依赖$B\rightarrow C$，所以不是保持函数依赖的分解。

另解：$R1(A,B),R2(B,C)$的函数依赖为：$B\rightarrow A,B\rightarrow C F_{1}=\varnothing,F_{2}=B\rightarrow C$

（2）求保持函数依赖的分解。

分解为$R_{1}(AC;A\rightarrow C)$，$R_{2}(BC;B\rightarrow C)$，但分解是有损的。

5.4.4 关于模式分解的若干结论

分解具有无损连接性和分解保持函数依赖是两个互相独立的标准。具有无损连接性的分解不一定保持函数依赖，保持函数依赖的分解不一定具有无损连接性。一个关系模式的分解可能有三种情况：

若要求分解具有无损连接性，那么分解后的模式一定能达到BCNF。
若要求分解保持函数依赖，那么分解后的模式总可以达到3NF，但不一定能达到BCNF。
若要求分解既具有无损连接性，又保持函数依赖，则分解后的模式可以达到3NF，但不一定能达到BCNF。

5.5 属性集闭包

5.5.1 属性集闭包的定义

设有关系模式$R(U)$,$U={A_{1},A_{2},…,A_{n}}$，$X$是$U$的子集，$F$是$U$上的一个函数依赖集，则属性集$X$关于函数依赖集$F$的闭包$X_{F}^{+}$定义为：$X_{F}^{+}＝{A_{i}|A_{i}\in U,F\models X\rightarrow A_{i}}$ 。即：我能决定哪些属性，就是属性集的闭包。

[例]设关系模式$R(A,B,C)$的函数依赖集为$F={A\rightarrow B,B\rightarrow C}$，则$A_{F}^{+}={A,B,C},B_{F}^{+}={B,C},C_{F}^{+}={C}$。

5.5.2 属性集的闭包计算

算法：求属性集$X(X\subseteq U)$关于$U$上的函数依赖集$F$的闭包$X_{F}^{+}$。

输入：属性全集$U$,$U$上的函数依赖集$F$,以及属性集$X\subseteq U$；

输出：$X$关于$F$的闭包$X_{F}^{+}$。

方法：根据下列步骤计算一系列属性集合$X^{(0)}$，$X^{(1)}$，…

(1) 令$X^{(0)}=X,i=0$（将自身加入）
(2) 求属性集

其中：$V$已找出（知道）的集合，$W$是可能已知、可能未知的属性（未知加入）

(3) $X^{(i+1)}=X^{(i)}\cup B$
(4)判断$X^{(i+1)}=X^{(i)}$吗？
- 若$X^{(i+1)}\ne X^{(i)}$，则用$i+1$取代$i$，返回(2)；
- 若$X^{(i+1)}= X^{(i)}$，则$X_{F}^{+}= X^{(i)}$，结束。

在判断计算何时结束时，可用下面四种方法：

$X^{(i+1)}=X^{(i)}$。
$X^{(i+1)}$已包含了全部属性。
在$F$中再也找不到函数依赖的右部属性是$X^{(i)}$未出现过的属性。
在$F$中再也找不到满足条件$V\subseteq X^{(i)}$的函数依赖$V\rightarrow W$。

5.6 函数依赖集的等价与覆盖

5.6.1 定义

设$F$和$G$是关系$R(U)$上的两个依赖集，若$F^{+}=G^{+}$，则称$F$与$G$等价，记为$F=G$。也可以称$F$覆盖$G$，$G$覆盖$F$，**$F$和$G$相互覆盖**。

一个函数依赖集F可能有若干个与其等价的函数依赖集，我们可以从中选择一个较好以便应用的函数依赖集。标准至少是：

所有函数依赖均独立，即该函数依赖集中不存在这样的函数依赖，它可由这个集合中的别的函数依赖推导出来。
表示最简单，即每个函数依赖的右部为单个属性[可用分解规则来做]，左部最简单[左部不能有冗余属性]。

具体规则见下小节“最小函数依赖集”。

5.6.2 最小函数依赖集

若函数依赖集$F$如果满足下列条件，则称$F$为最小函数覆盖，记为$F_{min}$：

对$F$中任一函数依赖$X\rightarrow A$，$F－{X→A}$都不与$F$等价。[不存在冗余的函数依赖]
$F$中每一个函数依赖的右部都是单个属性。[右部最简单]
对于$F$中的任一函数依赖$X\rightarrow A$，$F－{X→A}\cup {Z\rightarrow A}$都不与$F$等价，其中$Z$为$X$的任一真子集。[左部最简单，左部不能有冗余的属性——X的真子集不能决定A，左边已经是最少的属性]

求函数依赖集F的最小覆盖的方法是：

（1）使右部最简单：检查$F$中的每个函数依赖$X\rightarrow A$，若$A={A_{1},A_{2},…,A_{k}}$,则根据分解规则，用$X\rightarrow A_{i}(i=1,2,…,k)$取代$X\rightarrow A$
（2）检查是否有冗余的函数依赖：检查$F$中的每个函数依赖$X\rightarrow A$，令$G=F－{X\rightarrow A}$，若有$A\in X_{G}^{+}$，则从$F$中去掉此函数依赖。
（3）使左部最简单：检查$F$中各函数依赖$X\rightarrow A$，设$X={B_{1},B_{2},…,B_{m}}$，检查$B_{i}$，当$A\in (X-B_{i}){F}^{+}$时，即以$X－B{i}$替换$X$。

ch6.数据库设计

6.1 需求分析

数据字典是各类数据描述的集合，是进行详细的数据收集和数据分析所获得的主要结果。数据字典在数据库设计中占有很重要的地位，其主要内容包括：

数据项：是不可再分的数据单位。数据项描述＝{数据项名,数据项含义说明,别名,数据类型,长度,取值范围,取值含义,与其他数据项的逻辑关系}
数据结构：反映了数据之间的组合关系。一个数据结构可以由若干数据项或数据结构组成。数据结构描述＝{数据结构名,含义说明,组成:{数据项或数据结构}}
数据流：是数据结构在系统内传输的路径。数据流描述＝{数据流名,说明,数据流来源,数据流去向,组成:{数据结构}, 平均流量,高峰期流量}
数据存储：是数据结构的停留或保存处，也是数据流的来源和去向之一。数据存储描述＝{数据存储名,说明,编号,流入的数据流 ,流出的数据流，组成:{数据结构},数据量,存取方式}
处理过程：具体处理逻辑一般用判定表或判定树来描述，数据字典中只需要描述处理过程的说明性信息。处理过程描述＝{处理过程名,说明,输入:{数据流},输出:{数据流},处理:{简要说明}}

【例】学生学籍管理子系统的数据字典。

数据项以学号为例：
数据项：　学号
含义说明：唯一标识每个学生
别名：　　学生编号
类型：　　字符型
长度：　　 8
取值范围：00000000至99999999
取值含义：前两位标别该学生所在年级，后六位按顺序编号

数据结构以学生为例：
数据结构：　学生
含义说明：　定义了一个学生的有关信息
组成：　　　学号，姓名，性别，年龄，所在系，年级

数据流以体检结果为例：
数据流：　　体检结果
说明：　　　学生参加体格检查的最终结果
数据流来源：体检
数据流去向：批准
组成：　　　……
平均流量：　……
高峰期流量：……　

数据存储以学生登记表为例：
数据存储：　学生登记表
说明：　　　记录学生的基本情况
流入数据流：…… 流出数据流：……
组成：　　　……
数据量：　　每年3000张存取方式：　随机存取

处理过程以分配宿舍为例：
处理过程：分配宿舍
说明：　　为所有新生分配学生宿舍
输入：　　学生，宿舍，
输出：　　宿舍安排
处理：　　在新生报到后，为所有新生分配学生宿舍。要求同一间宿舍只能安排同一性别的学生，同一个学生只能安排在一个宿舍中。每个学生的居住面积不小于3平方米。安排新生宿舍其处理时间应不超过15分钟。

6.2 概念结构设计

描述概念模型的工具是E-R模型。

6.2.1 设计概念结构的四类方法

自顶向下：先定义全局概念结构的框架，再逐步细化。

自底向上：先定义各局部应用的概念结构，再将它们集成起来，得到全局概念结构。

逐步扩张：先定义最重要的核心概念结构，再向外扩充，以滚雪球的方式逐步生成其他概念结构，直至总体概念结构。

[“滚雪球”的方式]

混合策略：将自顶向下和自底向上相结合，用自顶向下策略设计一个全局概念结构的框架，以它为骨架集成由自底向上策略中设计的各局部概念结构。

常用策略有：自顶向下地进行需求分析、自底向上地设计概念结构。自底向上设计概念结构的步骤是先抽象数据并设计局部视图，再集成局部视图，得到全局概念结构。

6.2.2 数据抽象

数据抽象：概念结构是对现实世界的一种抽象。所谓抽象是从实际的人、物、事和概念中抽取所关心的共同特性，忽略非本质的细节，并把这些特性用各种概念精确地加以描述，这些概念组成了某种模型。一般有三种常用抽象：

分类(Classification)：定义某一类概念作为现实世界中一组对象的类型。这些对象具有某些共同的特性和行为，它抽象了对象值和型之间的“is member of”的语义，如张三是学生中的一员。即：客观世界同一类的对象用一种数据类型来表示。在E-R模型中，实体型就是这种抽象。联系也属于一种对象。
聚集(Aggregation)：定义某一类型的组成成分。它抽象了对象内部类型和成分之间“is part of”的语义。在E-R模型中若干属性的聚集组成了实体型，就是这种抽象。如学号、姓名、专业、等属性的聚集组成学生实体型。
概括(Generalization)：定义类型之间的一种子集联系。它抽象了类型之间的“is subset of”的语义。如学生是实体型，本科生、研究生也是实体型，本科生、研究生是学生的子集，称学生为超类，本科生、研究生为子类。概括具有继承性：子类继承超类上定义的所有抽象。 E-R模型中用双竖边的矩形框表示子类，用直线加小圆圈表示超类-子类的联系。

实体和属性的区分：实体与属性是相对而言的，同一事物在一种应用环境中作为属性，在另一应用环境中就必须作为实体。

确定为属性的两条准则是：

属性必须是不可分的数据项；
属性不能与其他实体具有联系，联系只发生在实体之间。

如职工是一个实体，职工号、姓名、年龄是职工的属性，职称如果没有与工资、福利挂钩，即没有需要进一步描述的特性，则根据准则1可以作为职工实体的属性。但如果不同的职称有不同的工资、住房标准和不同的附加福利，则职称将作为一个实体看待。

一个实体转化成一个表，它的属性就是表的属性；联系可以转化成一个独立的关系/表。

【例】第二个E-R图可以转化成三个表，职工、职称、聘任（职工号、职称代码）。

6.2.3 视图集成

视图集成的两种方式：

一次集成：一次集成多个分E-R图，适用于局部视图较简单时
逐步累积：首先集成两个局部视图(通常是比较关键的两个局部视图)，以后每次将一个新的局部视图集成进来。

无论哪种方式，集成局部E-R图必须经过两步：合并分E-R图，生成初步E-R图。

由于各个局部应用所面向的问题不同，且由不同的设计人员进行设计，这就导致了各个分E-R图之间必定存在冲突。合并分E-R图的主要工作与关键所在就是合理消除各分E-R图的冲突。

冲突主要分为如下三类：

属性冲突
属性域冲突：即属性值的类型、取值范围或取值集合不同。如零件号，有的部门将它定义为整型，有的部门将它定义为字符型。又如年龄，有些部门以出生日期形式表示职工年龄，而另一些部门用整数形式表示职工年龄。
属性取值单位冲突：如零件的重量，有的以公斤为单位，有的以斤为单位，有的以克为单位。

属性冲突通常用讨论、协商等手段加以解决。

命名冲突：可能发生在属性级、实体级、联系级上。命名冲突通常用讨论、协商等手段解决。
同名异义：不同意义的对象在不同局部应用中具有相同名字。
异名同义：同一意义的对象在不同局部应用中具有不同名字。如对科研项目，财务科称为项目，科研处称为课题。

6.3 逻辑结构设计

下面介绍E-R图向关系模式的转换。

（1）一个实体型转换为一个关系模式：实体的属性就是关系的属性，实体的码就是关系的码。如职工实体可以转换为关系模式职工(职工号,姓名,年龄,职称)

（2）实体间的联系转换有以下不同情况：

一个1:1联系可以转换为：
- 一个独立的关系模式：关系模式的名字就是联系的名字，相联系两端实体的码以及联系本身的属性都是关系模式的属性，任意一端实体的码都是它的码（存在两个候选码）。
  
  如下面的E-R图中，负责(职工号,产品号)，职工(职工号,……)，产品(产品号,……)。[多了一个表]
  
  职工号、产品号均可作为关系模式的码。
- 与任意一端实体合并：加入另一端实体的码以及联系的属性，码不变。
  
  如下面的E-R图中，职工(职工号,……,产品号)，产品(产品号,……)
一个1:n联系可以转换为：
- 一个独立的关系模式：n端的码是新的关系模式的码，其余与1:1联系相同。
  
  如下面的E-R图中，
  
  部门(部门号,……)，职工(职工号,姓名,……)
  
  属于(部门号,职工号[码])
- 1端与n端合并：要加到n端，码不变。
  
  如下面的E-R图中，职工(职工号,……,部门号)
一个m:n联系只能转换为一个独立的关系模式，码是两端实体码的组合。

如下面的E-R图中，零件-仓库联系中，库存(零件号,仓库号[码],库存量[联系本身的属性])。
三个或三个以上实体间的一个多元联系转换为一个关系模式：

与该多元联系相连的各实体的码以及联系本身的属性均是关系的属性，各实体码的组合是关系的码。如(供应商、产品与零件间的)供应联系是一个三元联系，可以将它转换为如下关系模式：供应(供应商号,产品号,零件号)

ch7.数据库恢复与并发控制

7.1 事务

事务(transaction)：用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位。

其中，Begin Transction表示事务的开始， Commit为事务提交，即告诉事务管理器事务中的所有操作都已完成，数据库处于另一个一致性状态；而RollBack为事务回滚，即告诉事务管理器事务执行时发生故障，所有已完成的操作必须全部撤销，滚回到事务开始的状态。这里的操作指对数据库的更新操作。

事务的性质：事务具有ACID特性。

原子性(Atomicity)：事务是数据库的逻辑工作单位，事务中包括的诸操作要么全做，要么全不做；
一致性(Consistency)：事务的执行必须保证数据库从一个一致性状态转到另一个一致性状态，并行调度与串行调度结果一致（若不一致，把此现象称为”丢失修改”）。为了保证其不冲突，需要采用协议，在这里采用封锁的方法——X锁（对数据进行修改）、S锁（读锁，Shared Lock），上锁后其他人不能修改；
隔离性(Isolation)：一个事务的执行不能被其他事务干扰，并发执行的各个事务间应互相独立；
持久性(Durability)：事务一旦提交，它对数据库中数据的改变应是永久的。

[例] T1事务表示A转帐100元到B， T2事务表示从A中取100元。

一致性：T1单独执行时A+B应不变，如果只write(A)没有write(B)，就不一致；T1、T2并发执行时，A应去掉200元，否则不一致；
原子性：T1必须全部执行6个操作；
隔离性：T1、T2相互独立，不应产生数据错误；
持久性：T1执行后，对数据库的修改永久存在。

保证事务ACID特性是事务处理的任务。事务ACID特性可能遭到破坏的因素有：

多个事务并行运行时，不同事务的操作交叉执行
事务在运行过程中被强行停止

7.2 故障的种类

恢复子系统是DBMS的一个重要组成部分，保证故障发生后能把数据库中的数据从错误状态恢复到某一已知的正确状态，保证事务ACID。恢复技术是衡量系统优劣的重要指标。一般一个大型数据库产品，恢复子系统的代码要占全部代码的10％以上。下面介绍故障的种类。

1.事务故障：

某个事务在运行过程中由于种种原因未运行至正常结束点就夭折了，造成数据库可能处于不正确状态。

事务故障的原因：违反了某些完整性限制、输入数据有误、运算溢出、并行事务发生死锁等。

恢复子系统一般采用回滚(RollBack)，强行撤销(UNDO)该事务对数据库的所有修改，使得这个事务象根本没有启动过一样。

2.系统故障：

系统故障(软故障)指造成系统停止运转的任何事件，使得系统要重新启动。这类故障影响所有正在运行的事务，但不破坏整个数据库。这时内存数据丢失，所有运行事务都异常终止。

系统故障的原因：特定类型的硬件错误(如CPU故障)、操作系统故障、DBMS代码错误、突然停电等。

系统故障的恢复：

日志文件数据不完整，将已经做的全部撤销

发生系统故障时，一些尚未完成的事务可能已将部分更新操作写回数据库，使数据库处于不正确状态，所以恢复子系统在系统重启时必须让所有非正常终止的事务回滚，强行撤销(UNDO)所有未完成事务。
日志文件数据完整（知道操作），数据文件数据不完整—–>根据日志继续操作

发生系统故障时，有些已完成事务可能部分甚至全部留在缓冲区，尚未写回磁盘，系统故障使得这些事务对数据库的修改部分或全部丢失，数据库处于不一致状态，因此恢复子系统在系统重启时，还必须**重做(**REDO)所有已提交事务。

3.介质故障：

介质故障(硬故障)使存储在外存中的数据部分丢失或全部丢失。介质故障发生的可能性较小，但破坏性大，破坏数据库或部分数据库，并影响正在存取这部分数据的所有事务。

介质故障的原因：磁盘损坏、磁头碰撞、瞬时强磁场干扰等。

介质故障的恢复：装入介质故障前某个时刻的数据副本，重做自此时开始的所有成功事务，将这些事务已提交的结果重新记入数据库。

7.3 恢复的实现技术

恢复机制涉及的两个关键问题：如何建立冗余数据(数据转储backup、登录日志文件logging)、如何利用这些冗余数据实施数据库恢复。

数据转储：指DBA将整个数据库复制到磁带或另一磁盘上保存起来的过程。这些备用的数据文本称为后备副本或后援副本。

数据转储分为两类：

静态转储：系统中无运行事务时进行转储，即转储开始时数据库处于一致性状态，而转储期间不允许对数据库的任何存取、修改操作。优点是实现简单；缺点是转储必须等用户事务结束，新的事务必须等转储结束，降低了数据库的可用性。
动态转储：转储期间允许对数据库进行存取或修改，即转储操作与用户事务可以并发进行。优点是不用等待正在运行的用户事务结束，不会影响新事务的运行；缺点是不能保证副本中的数据正确有效，如转储期间的某个时刻Tc系统将数据A=100转储，而在下一时刻Td某一事务将A改为200，转储结束后，后备副本上A的值已经过时。因此，必须利用日志文件进行恢复。

数据转储还可以分为以下两类：（均可以动态/静态）

海量转储：转储全部数据。
增量转储：每次只转储自上一次转储后更新过的数据。

从恢复角度看，使用海量转储得到的后备副本进行恢复往往更方便。但如果数据库很大，事务处理又十分频繁，则增量转储方式更实用更有效。

7.4 日志

日志文件的内容：每个事务的开始标记(BEGIN TRANSACTION)、结束标记(COMMIT或ROLLBACK)和每个更新操作均可作为日志文件中的一个日志记录 (log record)。

基于记录的日志文件：每条日志记录的内容有事务标识、操作类型(插入、删除或修改)、操作对象(记录ID)、更新前数据的旧值(插入操作此项为空)、更新后数据的新值(删除操作此项为空)

基于数据块的日志文件：每条日志记录的内容有事务标识、被更新的数据块Block NO.、更新前数据所在数据块的值(插入操作此项为空)、更新后数据所在数据块的值(删除操作此项为空)

日志文件示例：

日志的操作必须遵循以下两点原则：

登记的次序严格按照并行事务执行的时间次序。
必须先写日志文件，后写数据库写日志文件操作（“先期写入”协议）

把对数据的修改写到数据库中和把表示这个修改的日志记录写到日志文件中是两个不同的操作。有可能在这两个操作之间发生故障，即两个写操作只完成了一个。若先写了数据库修改，而在日志文件中没有登记这个修改，我们无法知道数据库是否修改，则以后就无法恢复这个修改了；若先写日志，但没有修改数据库，按日志文件恢复时只不过是多执行一次不必要的UNDO操作，并不会影响数据库的正确性。

7.5 恢复策略

7.5.1 事务故障的恢复

事务故障指事务在运行至正常终止点前被中止，这时恢复子系统应利用日志文件撤消(UNDO)此事务已对数据库进行的修改。事务故障的恢复由系统自动完成，对用户是透明的。恢复步骤是：

反向扫描文件日志(即从最后向前扫描日志文件)，查找该事务的更新操作。
对该事务的更新操作执行逆操作。即若记录中是插入操作， “更新前的值”为空，则相当于做删除操作；若记录中是删除操作，“更新后的值”为空，则相当于做插入操作；若记录中是修改操作，则用修改前值代替修改后值。
继续反向扫描日志文件，查找该事务的其他更新操作，并做同样处理。
如此处理下去，直至读到此事务的开始标记，事务故障恢复就完成了。

7.5.2 系统故障的恢复

系统故障造成数据库不一致状态的原因有两个，一是未完成事务对数据库的更新已写入数据库，二是一些已提交事务对数据库的更新还留在缓冲区没来得及写入数据库。因此恢复操作要UNDO故障发生时未完成的事务，REDO已完成的事务。系统故障的恢复由系统在重新启动时自动完成，不需要用户干预。恢复步骤是：

正向扫描日志文件(即从头[老时间—>新时间]扫描日志文件)，找出在故障发生前已经提交的事务(这些事务既有Begin Transaction记录，又有Commit记录)，将其事务标识放入REDO队列（继续做完）；同时找出故障发生时尚未完成的事务(这些事务只有Begin Transaction记录、但无Commit记录)，将其事务标识放入UNDO队列（全部撤销）。
反向扫描日志文件，对UNDO队列中各个事务进行撤销处理。
正向扫描日志文件，对REDO队列中各个事务进行重做处理。

[注意]必须先UNDO再REDO。

[例]设事务T1在A中存100，执行更新后在提交之前被中止。事务T2在A中存200，并提交。

T1没有结束标志，T2有开始、结束的标志。

介质故障的恢复与系统故障的恢复相同，发生介质故障后，磁盘上的物理数据和日志文件被破坏，这是最严重的一种故障。恢复方法是重装数据，然后重做已完成的事务。

7.6 并发控制

7.6.1 并发操作引起的问题

并发操作可能引起的三类数据不一致问题：

丢失修改：指两个事务T1和T2从数据库中读入同一数据并修改，T2的提交结果破坏了T1提交的结果，导致T1的修改被丢失

不可重复读：不可重复读是指事务T1读取数据后，事务T2执行更新操作，使T1无法再现前一次读取结果。

不可重复读包括三种情况：

事务T1读取某一数据后，事务T2对其做了修改，当T1再次读该数据时，得到与前一次不同的值。
事务T1按一定条件从数据库中读取了某些数据记录后，事务T2删除了其中部分记录，当T1再次按相同条件读取数据时，发现某些记录神秘地消失了。
事务T1按一定条件从数据库中读取了某些数据记录后，事务T2插入了一些记录，当T1再次按相同条件读取数据时，发现多了一些记录。

后两种情况也称为幻影现象(Phantom Row)。

读“脏”数据：事务T1修改某一数据，并将其写回磁盘，事务T2读取同一数据后，T1由于某种原因被撤消，这时事务1已修改过的数据恢复原值，T2读到的数据就与数据库中的数据不一致，则T2读到的数据称为“脏”数据。（读了数据库中没有的数据）

7.6.2 封锁

任何事务T在对某数据操作之前，先向系统发出请求对其加锁。加锁后事务T就对该数据拥有了一定的控制权，在事务T释放锁之前其它事务不能更新该数据。

**排它锁(X锁,写锁)**：若事务T对数据A加X锁，则只允许T读取和修改A，其他事务不能再对A加任何锁，直到T释放A上的X锁。它保证了在T释放A上X锁之前其他事务不能再读取和修改A ；
**共享锁(S锁,读锁)**：若事务T对数据A加S锁，则事务T只可读A，但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。它保证了在T释放A上S锁之前其它事务可以读A，但不能修改A。

X锁和S锁的相容性：Y为相容，N为不相容。若两个锁不相容，则后提出锁请求的事务必须等待。

7.6.3 封锁协议

封锁协议是指事务对数据封锁时，何时申请X锁或S锁、持锁时间、何时释放等有关封锁的规则。

一级封锁协议：事务T在修改数据A之前必须先对其加X锁，直到该事务结束才释放X锁（防丢失修改）；
二级封锁协议：一级封锁协议加上事务T在读取数据A前必须先对其加S锁，读完后立即释放S锁（防丢失修改，防读“脏”数据）；
三级封锁协议：一级封锁协议加上事务T在读取数据A前必须先对其加S锁，直到该事务结束才释放S锁（防丢失修改，防读“脏”数据，防不可重复读）。

ShiQuLiZhi

https://www.qqzzss.top/2022/08/14/theory/li-lun-04-shu-ju-ku-yuan-li-er/

本博客所有文章除特别声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 ShiQuLiZhi !

理论知识数据库

数据库原理(一) 数据库系统与SQL

2022-08-14 计算机学科理论

理论知识数据库

Python基础

2022-08-14 Python与数据处理

程序设计 Python

数据库原理(二) 过程语言与关系数据理论

ch4.过程语言PL/SQL

PART1.SQL Server

4.1.1 T-SQL编程基础

4.1.2 游标和事务

4.1.2.1 游标

4.1.2.2 事务

4.1.3 存储过程

4.1.4 触发器

PART2.Oracle

4.2.1 PL/SQL块的组成

4.2.1.1 定义

4.2.1.2 可执行部分

4.2.1.3 事务控制命令

4.2.1.4 流程控制

4.2.2 游标

4.2.3 PL/SQL中的异常处理

4.2.4 存储过程

4.2.5 存储函数

4.2.6 触发器

ch5.关系数据理论

5.1 函数依赖

5.1.1 函数依赖的定义

5.1.2 函数依赖的分类

5.1.3 函数依赖的逻辑蕴含

5.1.4 Armstrong公理系统

5.1.5 由函数依赖定义的码

5.1.6 候选码的求解方法

5.2 范式及其规范化

5.2.1 1NF

5.2.2 2NF

5.2.3 3NF

5.2.4 BCNF

5.3 多值依赖

5.3.1 多值依赖的定义

5.3.2 多值依赖的性质

5.3.3 多值依赖与函数依赖的区别

5.3.4 4NF

5.4 关系模式分解

5.4.1 模式分解的定义和要求

5.4.2 无损分解与有损分解

5.4.3 保持函数依赖

5.4.4 关于模式分解的若干结论

5.5 属性集闭包

5.5.1 属性集闭包的定义

5.5.2 属性集的闭包计算

5.6 函数依赖集的等价与覆盖

5.6.1 定义

5.6.2 最小函数依赖集

ch6.数据库设计

6.1 需求分析

6.2 概念结构设计

6.2.1 设计概念结构的四类方法

6.2.2 数据抽象

6.2.3 视图集成

6.3 逻辑结构设计

ch7.数据库恢复与并发控制

7.1 事务

7.2 故障的种类

7.3 恢复的实现技术

7.4 日志

7.5 恢复策略

7.5.1 事务故障的恢复

7.5.2 系统故障的恢复

7.6 并发控制

7.6.1 并发操作引起的问题

7.6.2 封锁

7.6.3 封锁协议