数据的完整性与其它方案对象.ppt

1、方案对象管理之,第十一章 数据的完整性与其它方案对象,第十一章 数据的完整性及其它方案对象,【教学导航】,第十一章 数据的完整性及其它方案对象,11.1 数据的完整性 11.2 管理完整性约束 11.2.1 oracle提供的约束条件 11.2.2 数据完整性约束的创建 11.2.3 数据完整性约束的维护 11.2.4 获取数据完整性约束的信息 11.3 其它方案对象 索引 视图 存储过程 触发器 同义词 序列,第十一章 数据的完整性及其它方案对象,（1）创建/删除CHECK约束。 （2）创建/删除PRIMARY KEY和UNIQUE约束。 （3）创建/删除FOREIGN KEY约束。,【任务

2、描述】,11.1 数据的完整性,数据库不仅仅是存储数据，还必须保证所保存的数据的正确性、完整性和可靠性 数据的完整性 是关系数据库模型的基本原则，是用户在表上定义的一系列规则或约束条件，以及在表之间定义的一系列相互关系。 其作用就是强制要求数据库只能接受正确的、合理的数据，防止错误的或无效的数据被插入到表中。 完整性保护 防止不合语义的数据进入数据库 例: 员工的性别只能是男或女； 员工的编号一定是惟一的； 员工所在的部门必须是公司已存在的部门,11.1 数据的完整性,数据的完整性和安全性是两个不同的概念 数据的完整性：是防止数据库中存在不符合语义的数据，保证数据库中数据是正确的，避免非法的不

3、合语义的错误数据的输入和输出，即所谓的“垃圾进垃圾出”所造成的无效操作和错误结果。 数据的安全性：是保护数据库防止恶意的破坏和非法的存取。 即，安全性措施的防范对象是非法用户和非法操作； 完整性措施的防范对象是不合语义的数据。,数据完整性与安全性,11.1 数据的完整性,维护数据的完整性的方法,Applicationcode,Table,Data,Integrityconstraint,Databasetrigger,11.2 管理完整性约束,ORACLE系统支持四种类型的数据完整性： 1域完整性： 域完整性为列级和元组级完整性。它为列或列组指定一个有效的数据集，并确定该列是否允许为空。 2实

4、体完整性 ：实体完整性为表级完整性，它要求表中所有的元组都应该有一个惟一的标识符 3引用完整性：参照完整性是表级完整性，它维护参照表中的外码与被参照表中主码的相容关系。 4用户定义完整性,11.2.1 数据完整性的类型,11.2 管理完整性约束,约束的类型 PRIMARY KEY 主键约束 FOREIGN KEY 外键约束 UNIQUE 唯一约束 CHECK 检查约束 NULL 空值约束 CASCADE 级联引用一致性约束,11.2.1 数据完整性约束的类型,决定使用何种约束（约束类型）,11.2 管理完整性约束,ENABLENOVALIDATE （激活而无效）,Existing data,N

5、ew data,DISABLENOVALIDATE （禁止而无效）,DISABLEVALIDATE （禁止而有效）,=,=,ENABLEVALIDATE （激活而有效）,11.2.2 完整性约束的状态,完整性约束的各种状态之间的变化 遵循以下的原则： 如果在激活(ENABLE)约束时没有说明NOVALIDATE,则默认为ENABLE VALIDATE 如果在禁止(DISABLE)约束时没有说明VALIDATE,则默认为DIABLE NOVALIDATE 如果惟一约束或主键约束从禁止（DISABLE）状态变为激活(ENABLE)状态时没有索引，系统会自动地为之创建一个惟一索引。反之则会自动删除惟

6、一索引。 当任何一个约束从NOVALIDATE状态变为VALIDATE状态时，系统会检查所有的数据。反之，不进行数据检查。 将一个约束从ENABLE NOVALIDATE状态变为ENABLE VALIDATE状态时，系统仍可以进行任何的读、写及其他的DDL操作。,11.2 管理完整性约束,11.2.2 完整性约束的状态,11.2 管理完整性约束,DML statement,Check nondeferred constraints,COMMIT,Check deferred constraints,11.2.3 约束的检验,11.2 管理完整性约束,包含所有约束的基本描述信息 USER_CON

7、STRAINTS DBA_CONSTRAINTS ALL_CONSTRAINTS 包含定义了约束的字段信息，利用这些视图可以查看约束被定义在哪些字段上。 DBA_CONS_COLUMNS ALL_CONS_COLUMNS USER_CONS_COLUMNS,11.2.4 获取约束的信息,11.2 管理完整性约束,例11-1：获取有关约束的信息,获取数据完整性约束的信息,Sql col constraint_name for a16 col table_name for a12 select constraint_name,table_name, constraint_type,status,d

8、eferrable,deferred,validated from dba_constraints where owner=SCOTT;,11.2 管理完整性约束,获取数据完整性约束的信息,例11-2：查询定义了约束的列,SQL Select table_name,constraint_name, column_name,positon from user_cons_columns where table_name=EMP;,11.2 管理完整性约束,使用 CREATE TABLE 或者 ALTER TABLE CREATE TABLE 是在创建表时创建约束 ALTER TABLE 是在一个已

9、有的表上创建约束 可以添加约束到已有数据的表上 可添加单列或多列约束 若约束应用于单列-列级约束 若约束引用了多列-表级约束，即使它并没有引用表中的所有列,11.2. 5数据完整性约束的创建,11.2 管理完整性约束,定义约束的指南,主键和唯一键: 将它们的索引放在（与表）不同的表空间中。 如果经常有大规模数据的装入使用非唯一索引。 自引用的外键: 在初始装入数据之后再定义或开启外键。 延迟约束的检查。,11.2 管理完整性约束,CREATE TABLE scott.employee( empid number(5) primary key, ename varchar2(15) not nu

10、ll, ssex varchar2(6), age number(4), job varchar2(10), hiredate date default (sysdate), deptno number(3) not null , CONSTRAINT FK_deptno1 FOREIGN KEY(deptno) REFERENCES scott.dept(deptno) ),在创建一个表时定义约束,【例11-3】,验证：Select constraint_name,table_name,constraint_type,status, deferrable,deferred,validated

11、 from dba_constraints where owner=SCOTT;,11.2 管理完整性约束,例11-4 为表employee中ename添加一个惟一性约束 SQL alter table employee add ( constraint ename_uk unique (ename) ); 例11-5为表employee中age 添加一个NOT NULL约束 SQLalter table employee modify 注：此处为modify非add age not null; 例11-6为表employee添加约束，限制员工性别为“男”、“女” SQLalter table

12、 employee add check (ssex in (男、女);,使用ALTER TABLE语句管理约束,11.2 管理完整性约束,例11-7删除约束dept_fkey，这样deptno字段就不再是一个外键了 SQLalter table employee drop constraint dept_fkey;,使用ALTER TABLE语句管理约束,11.2 管理完整性约束,例11-8删除employee表中ename列上的UNIQUE约束 SQLalter table employee drop unique (ename),使用ALTER TABLE语句管理约束,11.3 其它方案对

13、象,11.3.1 索引,11.3.2 视图,11.3.5 序列,11.3.6 同义词,11.3.3 存储过程,11.3.4 触发器,11.3 其它方案对象,1、概述 在Oracle中，索引是除表之外另一个重要的模式对象，索引主要用于提高表的查询速度。 索引与表一样，有独立的数据段存储，并且可以通过设置存储参数控制索引段的盘区分配方式。 索引可以由用户显式创建，也可以由Oracle自动创建。,11.3.1 索引,11.3 其它方案对象,2、使用索引的目的 快速查询 经常在查询中引用索引可以帮助oracle以最快的速度检索数据 惟一值 oracle自动产生索引来实施表中主键的惟一值，也可对任何其他

14、需要惟一值的列（或一组列）使用这一特性,11.3 其它方案对象,3、索引类型 在oracle 10g中有如下几种可用的索引类型： B-树索引(B-tree index) 位映射索引(Bitmap index) B-树簇索引(B-tree cluster index) 哈希簇索引(Hash cluster index) 全局和本地索引(Global and local index) 基于函数的索引(Function-based index) 域索引(Domain index),常用,Index entry header,Key column length,Key column value,ROW

15、ID,Root,Branch,Leaf,Index entry,B-树 索引,位图索引,29,索引与约束,Oracle在创建表或修改表的时候，可以为表的列定义约束。 如果在表中定义了PRIMARY KEY或UNIQUE约束，Oracle会自动为约束列建立惟一索引。 在默认情况下，如果禁用了约束，则相应的索引将被删除；相反，当用户激活约束时，则会自动重新创建相应的索引。,11.3 其它方案对象,30,创建索引,由于在Oracle数据库中，索引可以拥有它自己的存储空间，不必与相关联的表位于同一个表空间中。因此在创建索引时，用户可使用TABLESPACE子句为索引指定存储空间，如果未使用该子句，则O

16、racle会自动在用户的默认表空间中创建一个索引段。当索引和相对应的表分别存放在不同的表空间时，可以获取更好的性能。,11.3 其它方案对象,创建 索引: 指南,平衡查询和DML的需要。 将其放入单独的表空间。 使用统一的 extent 尺寸: 数据块尺寸的5的倍数或表空间的 MINIMUM EXTENT的尺寸。 对大索引可考虑使用NOLOGGING。 索引的INITRANS 参数通常应改比相对应表的高。,11.3 其它方案对象,1、 建立B-树索引 CREATE UNIQUE INDEX 模式.索引名称 ON ( 模式.)表名称 ( 列名ASC|DESC ,列名ASC|DESC ) 排序方式

17、,11.3 其它方案对象,例11-9 ： 对emp表中的ename列建立索引,建立B-树索引,SQLCreate UNIQUE index scott.ename_index On scott.emp(ename),SQLCreate index scott.ename_index On scott.emp(ename),例11-10： 使用UNIQUE选项对上述ename列建立惟一性索引,11.3 其它方案对象,例11-11： 在emp表中对列job,hiredate，建立组合索引,例11-12： 为job建立降序索引，并且将索引存储在表空间pioneerindexts01中,SQL Cre

18、ate index scott.ename_index On scott.emp(job,hiredate),SQLcreate index ind_job on emp(job desc) Tablespace pioneerindexts01;,11.3 其它方案对象,2 建立位映射索引 CREATE BITMAP INDEX 模式.索引名称 ON ( 模式.)表名称 ( 列名ASC|DESC ,列名ASC|DESC ),11.3 其它方案对象,例11-13： 假设有一个汽车数据表carinfo，记录有1000多万条，其中包含了大量的低基数列，比如color(汽车颜色)、make（汽车品牌

19、）、model（汽车型号）、year（出厂日期）等，以上列各自包含的值数目不超过100，则适合采用位映射索引,SQLcreate BITMAP index car_bmp_index on carinfo(color,make,model,year),37,11.3 其它方案对象,与表一样，在创建索引之后，还可以使用ALTER INDEX语句对索引的存储参数或存储结构进行修改和调整。另外，DROP INDEX语句可以用来从数据库中删除索引。,索引的维护,11.3 其它方案对象,Oracle允许的改动： 使用不同的存储参数或物理属性来重建索引 对索引更名 将一个索引移动到另一个表空间 指定是否并

20、行执行并改变并行程度 重新分配不使用的空间或分配一个新的区间 标志此索引不可被使用 开始或终止对一个索引的使用情况进行监控,索引的维护,使用ALTER INDEX 命令可进行如下的操作: 将一个索引移到另一个表空间 去掉已被删除的项以改进空间的利用率,例11-14： SQLALTER INDEX orders_region_id_idx REBUILD TABLESPACE indx02;,索引的维护,11.3 其它方案对象,删除一个索引的情况,索引的维护,在大规模装入数据之前删除和重建索引。 删除索引不常用的索引，并在需要时重建。 删除并重建无效的索引。,例11-15 ： SQLDROP I

21、NDEX hr.departments_name_idx;,11.3 其它方案对象,获取索引信息,通过查询以下视图可以获取有关索引的信息: DBA_INDEXES: 提供了有关索引的信息 DBA_IND_COLUMNS: 提供了有关索引列的信息 V$OBJECT_USAGE: 提供了有关索引使用的信息,42,11.3 其它方案对象,视图是一个虚拟表，是由一个或多个表（或其他视图）经过一条SELECT子查询语句定义的一个逻辑表。 视图同真实的表一样，也包含一系列带有名称的列和行数据。但是，视图并不在数据库中存储的数据值。行和列的数据来自定义视图的查询语句所引用的表（基表），数据库只在数据字典中存

22、储了视图定义本身。,11.3.2 视图,11.3 其它方案对象,使用视图的目的 提供各种数据表现形式 增加安全性 隐藏数据的逻辑复杂性并简化查询语句 简单用户权限的管理 执行某些必须使用视图的查询,11.3 其它方案对象,由于视图的特殊性，在创建视图时，必须满足如下要求： 在当前方案中创建视图，用户必须具有create view系统权限，若在其它方案中创建视图，要求必须具有create any view系统权限 视图的拥有都必须被明确授予访问在视图定义中所参考的所有基础对象的权限 若视图的拥有都要授权其他用户访问视图，则视图拥有都必须已经获得了对于基础对象的带有GTANT OPTION的对象权

23、限，或带有ADMIN OPTION的系统权限 定义视图的查询不能包含FOR UPDATE子句,11.3 其它方案对象,一、 使用CREATE VIEW语句创建视图 语法格式： CREATE OR REPLACE FORCE | NOFORCE VIEW schema.view_name ( column_name ,n ) AS select_statement WITH CHECK OPTION CONSTRAINT constraint_name WITH READ ONLY,创建视图,二. 用create view 语句创建视图的一种较好的方法 在创建视图之前，为了确保视图的正确性，应先

24、测试SELECT子查询语句以确保能返回正确的结果。创建视图的步骤如下： 1）编写SELECT语句 2） 测试SELECT语句 3）检查测试结果的正确性 4） 使用该SELECT语句创建视图。,11.3 其它方案对象,11.3 其它方案对象,1、创建简单视图 简单视图是指基于单个表建立，不包含任何函数、表达式和分组数据的视图 例11-16:基于emp表创建一个雇员基本信息视图,SQLcreate view v_staff as select empno,ename,job,hiredate,deptno from emp,11.3 其它方案对象,可将该视图当作表一样使用，描述其结构，从中查询数据

25、，甚至可以执行insert,update,delete等操作 例11-17: 使用with read only选项，指定视图只用于执行SELECT语句，禁止执行inert、update、delete语句,SQLcreate view v_staff_readonly as select empno,ename,job,hiredate,deptno from emp with read only,11.3 其它方案对象,例11-18： 使用WITH CHECK OPTION选项在视图上定义CHECK约束,SQLcreate view v_staff_check as select empno,

26、ename,job,hiredate,deptno from emp where deptno=10 with check option constraint v_staff_chk,11.3 其它方案对象,2、创建连接视图 连接视图指基于多个表所创建的视图，目的简化连接查询 例11-19：创建一个能获得部门编号为10、30的部门及雇员信息,SQLcreate view v_dept_emp as select d.deptno,e.ename,d.loc,e.hiredate,e.sal from emp e,dept d where e.deptno=d.deptno and a.dept

27、no in (10,30),11.3 其它方案对象,3、创建复杂视图 复杂视图指包含函数、表达式或分组数据的视图，目的简化查询操作，主要用于执行查询操作，不用于执行DML操作 注：当视图的SELECT子查询中包含函数或表达式时，必须为其定义列别名,11.3 其它方案对象,例11-20： 创建一个可获得目前每个岗位的平均工资、工资总和、最高工资和最低工资的视图 Create view v_emp_job_sal (job,avgsal,sumsal,maxsal,minsal) as Select job,avg(sal),sum(sal),max(sal),min(sal) from emp

28、group by job 若指定的列名数与select子查询中的列名数不相等，则会有错误提示,11.3 其它方案对象,在对视图进行更改（或重定义）之前，需考虑以下几个问题： 视图的更改不会影响视图所信赖的所有基础对象 原有视图中具有WITH CHECK OPTION选项，若重定义时没有该选项，则以前的WITH WHECK OPTION选项将自动删除 更改视图之后，信赖于该视图的所有视图和PL/SQL程序都将变为INVALID状态（失效状态）,修改视图,11.3 其它方案对象,在Oracle中，可以在数据库中定义子程序，这种程序块称为存储过程(procedure)。它存放在数据字典中，可以在不同

29、用户和应用程序之间共享，并可实现程序的优化和重用。,存储过程,11.3 其它方案对象,使用存储过程的优点是： (1)过程在服务器端运行，执行速度快。 (2)过程执行一次后代码就驻留在高速缓冲存储器，在以后的操作中，只需从高速缓冲存储器中调用已编译代码执行，提高了系统性能。 (3) 确保数据库的安全。可以不授权用户直接访问应用程序中的一些表，而是授权用户执行访问这些表的过程。非表的授权用户除非通过过程，否则就不能访问这些表。 (4) 自动完成需要预先执行的任务。过程可以在系统启动时自动执行，而不必在系统启动后再进行手工操作，大大方便了用户的使用，可以自动完成一些需要预先执行的任务。,存储过程,1

30、1.3 其它方案对象,例11-21：创建一个存储过程，用于向员工表添加数据 create procedure insertemp as begin insert into emp(empno,ename,job,sal) values(1001,scot,clerk,2000) exception when dup_val_on_index then dbms_output.put_line(重复的员工编号); when others then dbms_output.put_line(发生其他错误); end;,存储过程,11.3 其它方案对象,例11-22：调用存储过程 Execute i

31、nsertemp 例11-23：修改存储过程 create or replace procedure insertemp as begin . end;,存储过程,58,11.3 其它方案对象,触发器是一个能由系统自动执行对数据库修改的语句（PL/SQL代码块）。它与特定表上的DML操作相关联。 注意触发器与数据库中其他过程的区别，过程或函数都由用户直接调用，而触发器的执行用户则不能直接调用。Oracle会在相应的事件发生时，自动调用触发器。,触发器,11.3 其它方案对象,触发事件：是指对数据库的插入Insert 、删除delete 、修改update等操作。在这些事件发生时，触发器将开始工

32、作。 约束条件：触发器将测试约束条件是否成立。如果成立就执行相应的动作，否则什么也不做。 违约反映动作：指明触发器执行的动作是什么。如果触发器测试满足预定的条件，那么就由DBMS执行这些动作（即对数据库的操作）。这些动作能使触发事件不发生，这些动作也可以是一系列对数据库的操作，甚至可以是与触发事件本身无关的其他操作。,SQL触发器的结构 - 三个部分,11.3 其它方案对象,触发器的适用情况 维护在表创建阶段通过声明限制无法实现的复杂完整性限制 通过记录修改内容和修改者来审计表中的信息 在表内容发生变更时，自动通知其他程序采取相应的处理 在订阅发布环境下，发布有关各种事件的信息,Oracle触发器的类型,Oracle具有不同类型的触发器，可以让开发者实现不同的功能。Oracle提供的触发器类型主要包括： DML触发器 当对表进行DML操作时触发，可以在DML操作前或操作后进行触发。 替代触发器 替代触发器是Oracle来用替换所使用的实际语句而执行的触发器。