oracle触发器与存储过程高级编程-9i学习笔记

第一章,关系数据库表
1."关系数据库"(relational database)指的是基于sql 标准的能够解析和执行sql语句的
数据库。即:关系数据库这个术语的意思是一个基于sql标准的数据库
2.sql标准指定了sql的3种用法：嵌入式sql,模块化sql(modular sql)和直接sql
3.当sql创建对象(例如表)时,这就叫做数据定义语言(Data Definition Language,DDL).
4.操纵表中各列的sql语句称为数据操纵语言(Data Manipulation Language,DML)
5.sql语句的分类：
a.数据定义语言(DDL)；ddl语句可以定义数据库对象，以及产生对oracle
数据字典的更新。DDL语句可以创建、修改和删除各种对象，例如表、视图、存储过程、
数据库触发器、数据库链接以及数据库中几十种其他的对象。DDL语句可以更改已有的数
据库对象，包括表。通过alter table命令可以添加列和约束。如果我们要添加一个用于
表示学生年龄的列，那么可以执行下面的sql语句,该语句将修改在数据库的数据字典中
students表的定义。alter table students add (age number(3));
b.数据操纵语言(DML);
DML使用以下语句来操纵数据：
insert,update,delete,select.
select并不真正操纵数据
c.事务控制
  事务控制语句允许将一组DML语句在"全有或全无(all-or-nothing)"领域内捆绑起来。
也就是说，要么"所有语句都成功完成"，否则,只要有语句失败，那么这一组内所有
语句都要失败。事务控制语句的例子有:set transaction、commit和rollback;
d.会话控制
会话控制语句维护用户数据库连接期间的临时性和持久性。在调整sql时,一种有用的
做法是为会话打开sql跟踪(trace)。sql跟踪将会话信息记录到一个用户dump目录中，
以使oracle工具TKPROF进行sql语句分析。下面的语句将启用/禁用会话跟踪功能.
alter session set sqltrace [true|false]
下面是一个常见的修改会话命令。该命令修改那些类型为date的列的默认显示格式,使其
包括:日、月、年、小时、分钟和秒。
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
e.系统控制
系统控制语句基本上都是数据库管理命令。这些sql语句用于打开数据库、关闭数据库，
以及执行一些管理命令,例如修改数据文件的大小，或者开关日志文件，下面是一条系统
控制语句，该语句用于设置oracle任务队列(job queue)进程的个数。
alter system set job_queue_processes=4 scope=both;
6.创建表空间，并分配10M的磁盘空间。
create tablespace student_data datafile 'D:\student_data.dbf' size 10m;
7.在student_data表空间中创建一个students表
create table students
(student_id varchar2(10),
  student_name varchar2(30),
  college_major varchar2(15),
  status varchar2(20)
  )tablespace student_data;
  8.查询数据字典视图user_users，可以获得默认表空间的名称
select default_tablespace from user_users;
  9.一个表只能在一个表空间中创建,但是表的索引常常在一个单独的表空间中
  10.数据字典
执行create table语句时,会有信息存储到数据字典中,数据字典这个术语用于描述
system表空间中存在的表和视图。数据字典实际上是一个储存库，oracle用它来跟踪
与数据库中创建的所有对象相关的信息。这些被跟踪的信息包括：表名，该表归谁所
有，表是何时创建的，列名和数据类型，以及表所属的表空间的名称。所有PL/SQL
存储过程的源代码以及编译后的代码都存储在数据字典中。

数据字典由一些表和视图组成，其中视图是通过对基本表执行select语句而构成的。可
以查询user_tab_columns来判定一个表的列名。用户查询学生列定义的数据字典视图是
user_tab_columns.

下面的sql*plus会话将创建一个students表.通过查询数据字典视图user_tab_columns
，显示students表中所有列的列名和列类型。
create table students
(student_id varchar2(10),
  student_name varchar2(30),
  college_major varchar2(15),
  status varchar2(20)
  )tablespace student_data;

  select table_name,column_name,data_type
  from user_tab_columns
  where table_name='STUDENTS';
  为了查看students表属于哪个表空间,可以使用
  select tablespace_name from user_tables
  where table_name='STUDENTS';
  下面显示了与student_data表空间相关的数据文件和文件大小。该查询从dba_data_
files视图中进行选择，并要求具有dba角色或select_catalog_role角色。
column file_name format a50
select file_name,bytes
from dba_data_files
where tablespace_name='student_data';

第二章。 与oracle交互
11.权限
查询数据字典时,数据字典视图通常有3类，这3类视图使用user,all和dba作为前缀,
但是如果只拥有最基本的oracle角色-connect和resource，那么就会受到一些限制
如果不仅仅是查询user前缀的基本视图,那么还需要下列权限之一:
获得一个dba角色，但如果数据库时一个生产或测试数据库，可能会比较困难。如果
数据库时自己的桌面数据库或开发环境，则可以发布下列授权:
grant dba to username;
即使用自己的个人数据库做实验，也没有必要以system的身份登录来进行日常开发。
这两个账户虽然被授予开放的权限,但是对于经常使用的数据库而言，这并不很理想。
两个其他的选项是:
grant select_catalog_role to username;
grant select any table to_username;
上述的任何一种权限都允许对数据字典编写不受限制的脚步。下面的脚步演示了如何
对动态视图v$instance进行有效的查询。该脚本告诉我们当前所连接到得数据库，当
经常连接到不同数据库时，这一点特别有用。
set heading off;
select 'User='||user||'. You are connected to instance '
||instance_name||' on '||host_name||'.'
from v$instance;
set heading on;

第三章 声明性约束
oracle支持以下sql约束：
a.主键约束(Primary Key constraint)
b.惟一性约束(Unique constraint)
c.外键约束(Foreign Key constraint)
d.检查约束(Check constraint)
e.非空约束(Not Null constraint)这实际上是检查约束的一个特例
以下为创建主键时的几种语法方法和风格:
1>列约束子句
create table temp(id varchar2(5) constraint pk_temp primary key,no number);
2>表约束子句
create table temp
(id varchar2(5),
  no number,
  constraint PK_temp primary key(id)
  )tablespace student_data;
3>alter table语句
create table temp(id varchar2(5),no number);
alter table temp add constraint pk_temp primary key(id);


主键约束

1.1主键索引
1.1>下列的alter table语句创建了主键,同时还为索引指定了表空间-using index
tablespace 是这个语法的关键词组.
create table students
(student_id varchar2(10),
student_name varchar2(30),
college_major varchar2(15),
status varchar2(20)
)tablespace student_data;
alter table students add constraint pk_students primary key(student_id)
using index tablespace student_index;
1.2>.要在默认表空间以外的表空间中创建表和索引，需要具备一定的权限。如果拥有
resource角色，那么可以同时拥有unlimited tablespace权限-这一权限是自动从
resource角色中继承过来的,允许使用无限的表空间配额(tablespace quota).有了
unlimited tablespace权限,就可以在任何表空间(包括system表空间)中创建对象。
基于这个原因，通常要从应用程序开发者那里收回这一权限，而为他们增加表空间
配额.这种对于开发者账户的改变类似于下面对scott账户所作出的改变：
revoke unlimited tablespace from scott;
alter user scott quota unlimited on student_data;
alter user scott quota unlimited on student_index;
alter user scott default tablespace student_data;
1.3>.可以通过下面的sql来检查账户权限和表空间配额.通过查询数据字典视图user_
role_privs和user_sys_privs，就可以查看您的oracle账户被授予的权限和角色:
column role_priv format a30;
select 'role: '||qranted_role role_priv from user_role_privs
union
select ': '||privilege role_priv from user_sys_privs;
1.4>.要查看表空间配privilege额,可以查询user_ts_quotas;
select tablespace_name,max_bytes from user_ts_quotas;
对于任何表空间,上述sql都将返回一个值-1,表示可以为表空间使用无限的配额。
5>.为了确定由students和主键索引分配的物理空间,可以查询dba_extents和dba_
data_files.下面的代码例释了sql*plus对这两个视图的会话查询。因为这两个视图
是以dba开始的.所以您需要有dba角色或者select_catalog_role角色
select a.extent_id,a.segment_name,b.file_name,round(a.bytes/1024) KBytes
from dba_extents a,dba_data_files b where segment_name in('STUDENTS',
'PK_STUDENTS') and a.file_id=b.file_id;


1.2.序列
各种数据库以不同的方式来处理列的自动生成，sql server使用一个identity列,而
oracle则采用序列，首先我们创建一个序列，该序列也是一个对象，这个对象存储在
数据字典中，序列一直保留状态信息，比如当前值，而其上下文是在数据字典中维护的
一旦创建了一个序列，就可以从数据字典视图user_sequences查询该序列的属性。
Sequences_name.nextval 求出接下来的最高值；
Sequences_name.currval 最近一次调用nextval时返回的值
1>序列创建语句
create sequence my_sequence;
2>删除序列
drop sequence my_sequence;
3>创建起始点为10的序列
create sequence my_sequence start with 10;
4>测试语句
select my_sequence.nextval from dual;
5>创建序列时,序列数字可以是循环的,或者是在某个最大值那里停下来.与之对应的关键
字分别是cycle和nocycle.increment by间距(interval)可以使序列按照任何整数的倍数
来增加，默认值是1.为了提高性能，cache选项预先在内存中位一些数字分配了缓存。
下面例释了一个序列,该序列将在0，5，10，15，20这几个值之间循环，到了20后便重回
到0。
create sequence sample_sequence
minvalue 0
start with 0
maxvalue 20
increment by 5
nocache
cycle;
6>一旦创建了一个序列，就可以用它来填充表中的任何一列。由于这个原因，有必要小
心地为序列命名，使其能反映它们的用途，同时限定序列的目的是为了填充某一特定的
列。
7>如果由序列生成主键，则该序列应该以下面的格式命名：table_name_pk_seq
运用该语法,用于产生students表中的主键值的序列可以命名为:
students_pk_seq;
8>视图user_sequences显示了在模式(schema)中所有序列的属性。如果看到名为
students_pk_seq的序列,我们可以十分肯定这个序列是用来填充students表中主键列的
我们之所以肯定，是因为这个序列名,而不是因为任何其他数据字典信息
9>以下是创建students表、带有表空间子句的主键约束和序列的完整DDL:
create table students
(student_id varchar2(10),
student_name varchar2(30),
college_major varchar2(15),
status varchar2(20)
)tablespace student_data;

alter table students add constraint pk_students primary key(student_id)
using index tablespace student_index;

create sequence students_pk_seq;


1.3.连接主键
1>建表DDL
create table student_vehicles
(state varchar2(2),
tag_no varchar2(10),
vehicle_desc varchar2(20),
student_id varchar2(10),
parking_sticker varchar2(10)
)tablespace student_data;

alter table student_vehicles add constraint pk_student_vehicles
primary key(state,tag_no) using index tablespaec student_index;
2>如何判断何时使用主键,何时又不使用主键。当应用程序没有使用序列主键时,有以下
3中选项
a.不使用伪键索引.它没有任何好处。因为在数据中有一个自然形成的主键(state,tagno)
b.在列state和tag_no上创建索引
c。在state和tag_no列上创建一个连接的unique约束。惟一性约束的创建将强制实施自
然的业务规则，以使这些列惟一，并且在他们之上建立索引。
3>执行伪序列以及state和tag_no上的连接惟一性约束的DDL如下:
create table student_vehicles
(vehicle_id number,
state varchar2(2),
tag_no varchar2(10),
vehicle_desc varchar2(20),
student_id varchar2(10),
parking_sticker varchar2(10)
)tablespace student_data;

alter table student_vehicles add constraint pk_student_vehicles
primary key(vehicle_id) using index tablespace student_index;

create sequence student_vehicles_pk_seq;

alter table student_vehicles add constraint uk_student_vehicles_state_tag
unique(state,tag_no)
using index tablespace student_index;

1.4 启用、禁用和删除
1>下列代码可以删除一个主键约束：
alter table <table_name> drop constraint <constraint_name>;
这样删除了主键约束，同时也删除了与该约束有关的索引
2>oracle不允许删除一个在其他表中作为引用外键的主键。如果表中有引用外键，可以drop
cascade约束。这样可以同时删除主键约束以及引用该父键的所有外键。这里不要求用
cascade选项声明外键约束。但可以利用cascade选项删除主键，不过这种删除是永久性的，
也就是说,主键以及外键约束将从数据字典中删除.cascade选项是:
alter table <table_name> drop constraint <constraint_name> cascade;
eg:alter table state_lookup drop constraint pk_state_lookup cascade;
3>另一种温和些的方法是禁用约束。cascade限制同样可以用来禁用约束。如果存在引用
外键，那么可以通过下列代码来禁用主键约束。
alter table state_lookup disable constraint pk_state_lookup cascade;
被禁用的约束在数据字典中仍然有定义，只不过这种约束没有被强制实施。而且，约束的状
态也被设置为disabled。通过cascade选项,主键约束以及所有引用外键约束的状态都被设置
为disabled.
4>主键约束被禁用后,索引页将随之消失-从数据字典中删除。不过，一旦被启用，索引又
可以立即重建。
alter table state_lookup enable constraint pk_state_lookup;
这条alter table语句重建索引，并且将主键约束设置为enabled.但此时外键约束仍然是被
禁用的。所有外键约束都必须通过下面的语句来启用:
alter table students enable constraint fk_students_state;
5>这里不能删除主键列上的索引-这种索引将通过drop或disable约束来删除，可以通过下
面代码。在不会影响到约束的情况下重建索引:
alter index pk_state_lookup rebuild;
6>enable和disable关键字可以追加在任何约束声明后,如果追加了disable关键字，那么约
束声明将创建约束。并且将其状态设置为disable。被禁用的约束可以随时启用。默认情况
下，所有约束子句都使用enable关键字。


1.5 可延迟选项
-- 主键约束可以通过选项 deferrable来创建,这一选项允许应用程序在装载时禁用约束
-- 假设要对表进行操作，但当装载完成时，数据将符合主键约束的规则。应用程序将
-- commit(提交)所装载的数据。在提交过程中，约束被强制实施。程序如果保留表中的
-- 无效数据(那些违反约束的数据)，事务将会回滚.

-- 场景1 使用可延迟选项装载不良数据
-- 一个存储过程可装载1000行，其中有重复的行，装载了1000行之后，将提交程序执行
-- 但是这样的事务将回滚，因为数据违反了约束.

-- 场景2 不使用可延迟选项装载不良数据
-- 一个存储过程可装载1000行，其中有重复的行。程序继续装载数据，在出现重复插入
-- 时，插入失败。应用程序可以选择忽略该条插入(使用异常处理代码)并且继续保留
-- 数据,程序也可以回滚事务.

-- deferrable选项由下列语法创建其中创建了一个带有两列的示例表 parent.
create table parent
(parent_id number(2),
parent_desc varchar2(10)
)

drop table parent

alter table parent
add constraint pk_parent
primary key(parent_id) deferrable

-- 下面代码删除主键约束
alter table parent drop constraint pk_parent

-- 上述约束定义与下面的语句是一样的:
alter table parent add constraint pk_parent primary key (parent_id)
deferrable initially deferred

-- 场景1如以下代码所示.这个pl/sql块装载重复的数据，装载完成后,但在执行commint
-- 之前,重复的记录将被deleted(删除)。该程序最终获得成功.因为在提交之前已删除
-- 了重复的记录.
declare
begin
  execute immediate 'set constraints all deferred';
 
  insert into parent values(1,'a');
  insert into parent values(1,'b');
  insert into parent values(3,'c');
  insert into parent values(4,'d');
 
  delete from parent where parent_desc='b';
  commit;
end;

-- 如果这个程序块没有删除重复记录,就会出现oracle错误.该错误与违反主键约束的
-- 错误一样。下面的程序块完成同样的任务.且装载同样的数据。该场景没有使用可
-- 延迟选项。相反，所有良好的数据都保留在parent表中.

declare
begin
  begin
    insert into parent values(1,'a');
    exception when dup_val_on_index then null;
  end;
  begin
    insert into parent values(1,'b');
    exception when dup_val_on_index then null;
  end;
  begin
    insert into parent values(3,'c');
    exception when dup_val_on_index then null;
  end;
  begin
    insert into parent values(4,'d');
    exception when dup_val_on_index then null;
  end;
 
  commit;
end;
   
-- 保留在表中的数据是第1，3，4行.deferrable选项装载所有数据.然后,应用约束，
-- 并提交或回滚事务。第二个场景提供了选项来回滚重复的插入。另外，应用程序
-- 可以忽略重复的插入，而继续处理良好的数据.

-- 可延迟选项不允许表成为工作区,场景1好像允许应用程序装载数据，然后操纵那些
-- 数据，并清除虚假的记录。在提交时数据很可能是良好的，但是也存在另一种状况
-- 为了装载所要操纵的数据，可以创建临时表。在操纵并且清除数据后，这些记录行
-- 被插入到生产表中.临时表能持续会话或事务的生命期。下面的ddl创建一个临时表
-- 该临时表可用作私有的留在内存的表，可以持久事务.
create global temporary table
parent_temp
(parent_id number(2),
parent_desc varchar2(10))
on commit delete rows

-- 现在场景1可以使用临时表来进一步处理数据。当把数据移入到永久表中时，提交
-- 操作将从临时表中删除那些行。临时表是本次事务所私有的。下面的pl/sql语句
-- 块是没有使用可延迟选项的临时表的场景1:
begin
  insert into parent_temp values(1,'a');
  insert into parent_temp values(2,'b');
  insert into parent_temp values(3,'c');
  insert into parent_temp values(4,'d');
 
  delete from parent_temp where parent_desc='b';
 
  insert into parent select * from parent_temp;
  commit;
end;

-- deferrable 通过每条dml语句来强制实施约束.这是在没有deferrable选项时,约束
-- 的默认行为.不过,deferrable提供了一下功能:
-- 1.应用程序可以 set constraints all deferred,装载数据，以及在事务通过
-- commit语句完成时强制实施该约束.
-- 2.可以通过alter table语句来disable(禁用)约束.我们可以装载数据，利用
-- nov_alidate 选项启用约束.将重复记录留在表中，但对于以后的dml 则强制实施
-- 约束(参见 3.1.10节,"novalidate选项").

-- 可以通过以下属性来声明deferrable选项:
deferrable initially deferred

-- 只有当事务提交时,initially deferred 特征才默认地强制实施约束.在设置此
-- 属性后，就不会单个地执行每条dml语句.只有在事务完成了之后,才能强制实施
-- 约束。该选项提供下列功能:
-- 1.当应用程序知道约束在提交时强制实施的情况下.开始事务．它装载数据，然后
-- 提交，这个事务或者整个被接受，或者完全遗弃.代码中包含一些insert语句和一条
-- commit语句。在代码中，没有任何语句反映inserts在提交时生效.在提交之前，应用
-- 程序能通过以下代码来具体检查约束是否生效.
-- execute immediate 'set constraints all immediate';

-- 以下总结了deferrable选项.
-- (1)可以声明约束deferable。它在数据字典user_constraints视图中含有以下属性
-- deferrable=deferrable
-- deferred=immediate
-- 该选项意味着约束是通过的条dml来强制实施的。可以像先前那样编写代码，命令
-- oracle直到commit或者rollback时才强制实施约束,这要求在代码中有set
-- constraints语句.
-- (2)可以通过选项deferrable initially deferred来声明约束，这样将把数据字典
-- 中的约束设置为:
-- deferrable=deferrable
-- deferred=deferred
-- (3)该选项意味着事务的默认行为是在事务进行commit或者rollback时,强制实施
-- 约束。有了这个选项，pl/sql程序看上去像其他程序一样，只有观察约束的状态
-- 才能知道什么时候强制实施约束。应用程序 可以选择用set constraints all
-- immediate命令来强制实施约束。没有这条语句时，将在事务结束后强制实施约束
-- (4)可以alter 约束的deferrable状态，如果将声明约束为deferrable,那么它将
-- 具有以下状态
-- deferrable=deferrable
-- deferred=immediate

-- 可以执行以下代码
alter table table_name modify constraint
constraint_name initially deferred.

-- 这使得约束状态变为:
deferrable=deferrable
deferred=deferred

-- (5)可以使用下面的代码alter 声明为 initially deferred的约束的deferred
-- 状态。
alter table table_name modify constraint
constraint_name initially immediate.
-- 这使得约束状态变为:
deferrable=deferrable
deferred=immediate

-- 一如既往，我们总可以通过 alter table命令来禁用约束.然后再启用约束。

1.6 novalidate选项
-- novalidate选项允许装载不规范的数据，并将其保留在表中,而约束的规则仅在将来
-- 需要执行插入操作时才启用.该选项可用于数据仓库系统，数据仓库中必须有供分析
-- 的历史数据。而历史数据往往会违反当前的业务规则.

-- 约束在初始创建时必须带有可延迟选项，以便装载不规范数据，在装载历史数据以前
-- 必须禁用约束。以下代码创建一个带有可延迟主键约束的表，在装载数据之前，先
-- 禁用约束。然后，用novalidate选项启用约束。此后，历史数据仍得以保留。但是
-- 所有新插入的记录则必须符合主键的规则。
drop table parent

create table parent
(parent_id number(2),
parent_desc varchar2(10)
);

alter table parent add constraint pk_parent primary key
(parent_id) deferrable;

alter table parent disable constraint pk_parent;

begin
  insert into parent values(1,'a');
  insert into parent values(1,'b');
  insert into parent values(3,'c');
  insert into parent values(4,'d');
end;

select * from parent;

alter table parent enable novalidate constraint pk_parent

-- 在执行了上述的pl/sql块后，表中存在重复记录；尽管如此，所有新的插入的列都
-- 必须与主键一致。

1.7 pl/sql中的错误处理
-- 利用pl/sql内置的异常能轻松地捕捉pl/sql中的重复插入。该异常命名为:
dup_val_on_index

-- 通过一个包括异常处理程序。应用程序就能处理少见的重复现象。下面的存储过程
-- 返回一个布尔值.如果出现重复插入。则表明失败
create or replace
function insert_parent
(v_id number,v_desc varchar2) return boolean
is
begin
  insert into parent values(v_id,v_desc);
  return true;
exception
  when dup_val_on_index then return false;
end;




pl/sql中的错误处理
-- 利用pl/sql内置的异常能轻松地捕捉pl/sql中的重复插入。该异常命名为:
dup_val_on_index

-- 通过一个包括异常处理程序。应用程序就能处理少见的重复现象。下面的存储过程
-- 返回一个布尔值.如果出现重复插入。则表明失败
create or replace
function insert_parent
(v_id number,v_desc varchar2) return boolean
is
begin
  insert into parent values(v_id,v_desc);
  return true;
exception
  when dup_val_on_index then return false;
end;


唯一性约束
-- 本节我们将用一个表来存储学生信息。

-- 唯一性(unique)约束作用于一列，或一组列，以执行下列规则：如果有值存在，则
-- 该值必须唯一
-- 这一定义类似于主键约束。以下是主键约束与唯一性约束之间的比较：
-- 1.一个表只能有一个主键约束。但一个表中可以有多个唯一性约束
-- 2.主键中的列永远不能为空值，唯一性约束中的列可以为null(空)。如果某列有唯一
-- 性约束，那么有很多行在那一列上可以为null，非空的值必须唯一。
-- 3.创建主键时，我们要么创建一个索引，要么重用已有的索引，唯一性约束也是这样
-- 4.主键约束和唯一性约束列可以是外键关系中的父亲。外键约束列常常需要引用主键
-- 约束的列，同样，外键约束也可以引用唯一性约束的列
-- 下面的ddl在列(a,b)上创建连接的唯一性约束:
create table luoping_temp(pk number primary key,a number,b number);
alter table luoping_temp add constraint uk_luoping_temp_a_b unique(a,b);

-- 在数据不具备唯一性的列中，可以使用null作为列值，下面的insert语句是合法的：
-- unique constraint on last 2 columns
insert into luoping_temp values(1,1,1);
insert into luoping_temp values(2,2,1);
insert into luoping_temp values(3,1,2);
insert into luoping_temp values(4,null,null);
insert into luoping_temp values(5,null,null);
insert into luoping_temp values(6,1,null);
insert into luoping_temp values(7,null,1);
insert into luoping_temp values(8,2,null);

select * from luoping_temp;

-- 下面的代码重复了最后一条插入，违反了约束.
insert into luoping_temp values(9,2,null);

-- 需要注意的是：前缀 "ora"和代表错误代码的-1与违反主键约束是一样的

-- 3.2.1 将非空、检查与唯一性约束结合使用
-- 有时候，我们会将非空(not null)约束添加到唯一性(unique)约束上，这一附加的
-- 要求规定列值必须是唯一的，并且不允许有空值，下面的语句例释了这种情况:
drop table luoping_temp;

create table luoping_temp (pk number primary key,a number not null);

alter table luoping_temp add constraint uk_luoping_temp_a unique(a);

-- 连接唯一性约束通常以一个检查约束作为补充，用于防止这种情况:一列为空，
-- 另一列有值，可以通过下面的实例来强制实施这一约束
drop table luoping_temp;

create table luoping_temp (pk number primary key,a number, b number);

alter table luoping_temp add constraint uk_luoping_temp_a_b unique(a,b);

alter table luoping_temp add constraint ck_luoping_temp_a_b check
(
(a is null and b is null) or
(a is not null and b is not null)
)

-- 上边检查约束和唯一性约束的组合允许下面的前两条插入，但是拒绝后两条插入
insert into luoping_temp values(6,1,1) ;
insert into luoping_temp values(7,null,null);
insert into luoping_temp values(6,1,null);
insert into luoping_temp values(7,null,1);

delete from luoping_temp;
select * from luoping_temp;

-- 检查约束、唯一性约束与非空约束的组合允许:
-- 1.如果列值中提供了数据，则该列值必须是唯一的，这由唯一性约束来强制实施
-- 2.在唯一性约束中的任何列或者所有列都可以对各列强制实施非空约束
-- 3.通过检查约束和布尔逻辑，可以应用null 和not null限制的组合，以命令执行
-- 规则

3.2.2 students表实例

-- 考虑一个表students,该表包括关于学生驾驶执照信息的几列。需考虑的是，并非每个
-- 学生都有执照，我们的规则是:
-- 1.每个学生必须有一个唯一的身份证号，或者是student_id 这是主键
-- 2.如果某个学生有驾驶执照，那么执照号与州简称的组合必须是唯一的，州与执照的
-- 列不是强制要有的，所以不需要非空约束。
-- 3.最后的检查约束确保学生必须有执照，并且要同时将州和执照值都输入到系统中。

-- 下面是ddl创建students表，其中在state和license_no上有连接的唯一性约束。

drop table students;

create table students
(student_id  varchar2(10) not null,
student_name varchar2(30) not null,
status       varchar2(15) not null,
state        varchar2(2),
licence_no   varchar2(30)
) tablespace student_date;

alter table students
add constraint pk_student primary key (student_id)
using index tablespace student_index;

alter table students
add constraint uk_students_licence
unique(state,licence_no)
using index tablespace student_index;

alter table students
add constraint ck_students_st_lic
check
(
(state is null and licence_no is null) or
(state is not null and licence_no is not null)
);

-- 以下几段总结了关于上述ddl的要求。
-- 1.我们称主键约束为primary key.唯一性约束就是"unique"。在平时的谈话中。我们
-- 常常会引用"唯一键(unique key)"约束，但在编写ddl时,会将"key"部分去掉。而且
-- 唯一性约束的名称通常被冠以前缀"uk"，主键、外键以及唯一性约束一起发挥作用，
-- 以强制实施引用完整性.但是对于唯一性约束的ddl语法不包含关键字"key"。
-- 2.唯一性约束会导致索引的建立，因此，我们把表空间包括进来，用以放置这种
-- 索引(如果之前以及在这些列上创建了索引，则约束将使用该索引)，对于文本中的
-- 所有ddl，表是在student_date表空间中创建的，而所有的索引是在student_index
-- 表空间中创建的，这是一种相当标准的惯例.
-- 3.唯一性约束的名称为uk_students_licence。约束名要限制在30个字母以内，本书
-- 中的约束名带有前缀，用于说明约束类型，紧随前缀的是表名。对于一个主键约束
-- 的名称，这些就是所有必须的了。对于其他约束，要尝试在表名后加上列名(这是
-- 比较困难的，因为表名与列名可能会超过30个字母，所以有时候需要缩减).更重要
-- 的是，约束名应该能清楚地表示约束的类型和表，约束名包含的列名可是是缩写词
-- 在处理违反约束的应用程序时，这种方法很有帮助.

3.2.3 可延迟和novalidate选项
-- 与主键约束类似，可以将unique约束声明为可延迟的。通过alter table语句，可以
-- 禁用和启用约束。在第3.1.9节，"可延迟选项"以及第3.1.10节,"novalidate"选项
-- 中描述的所有这些选项都适用于unique约束.


-- 3.2.4 Pl/sql中的错误处理
-- 针对主键约束和唯一性约束，pl/sql内置异常将捕捉重复插入，该异常名为:
-- dup_val_on_index

drop table students;

create table students
(student_id varchar2(10) not null,
student_name varchar2(30) not null,
college_major varchar2(10) not null,
status varchar2(15) not null,
state varchar2(2),
license_no varchar2(30)
) tablespace student_date;

alter table students
add constraint pk_students primary key (student_id)
using index tablespace student_index;

alter table students
add constraint uk_students_license
unique(state,license_no)
using index tablespace student_index;

alter table students
add constraint ck_students_st_lic
check((state is null and license_no is null) or
(state is not null and license_no is not null));

-- 以下过程插入一名学生，并且捕获唯一性约束错误，同时还可以捕获检查所欲错误的
-- 错误代码。检查约束错误号可以映射到一个声明的异常，然后，该过程包括一个针对
-- 该异常的异常处理程序，主键约束和唯一性约束错误有在语言中声明的预定义异常。
-- 其他类型的错误，例如检查约束错误，也需要声明的异常，该异常必须映射到oracle
-- 错误号上，检查约束错误号是-2290
create or replace procedure
insert_student(v_student_id varchar2,
v_student_name varchar2,
v_college_marjor varchar2,
v_status varchar2,
v_state varchr2,
v_license_no varchar2)
is
check_constraint_violation exception;
pragma exception_init(check_constraint_violation,-2290);
begin
insert into students values
(v_student_id,v_student_name,
v_college_major,v_status,
v_state,v_license_no);
dbms_output.put_line('insert complete');
exception
  when dup_val_on_index then
    dbms_output.put_line('pk or unique const violation');
  when check_constraint_violation then
    dbms_output.put_line('check constraint violation');
end;
-- 存储过程编译错误。需加以确认。

-- 为了测试错误处理逻辑，可以使用sql*plus。再加上某些数据来execute(执行)该
-- 过程。前两条插入将失败，因为州与执照违反了检查约束规则：要么两者都为null
-- 要么两者都not null.
-- 第3,4条插入成功了，最后一条插入失败，因为它违反了唯一性约束，它插入了与
-- 前者相同的记录。
insert_students('A900','Ann','Math','Degree','CA',null);
insert_students('A900','Ann','Math','Degree',null,'ABC');
insert_students('A900','Ann','Math','Degree',null,null);
insert_students('A902','Joe','Math','Degree','CA','BAB');
insert_students('A903','Ben','Math','Degree','CA','ABC');


外键约束
-- 外键约束(foreign key)强制实施引用完整性。外键约束可限制一个列值的值域。
-- 例如，我们可以限制一个state简写，使其只能取另一个控制结构(即一个父表)中
-- 限定的一组值

-- 在谈及那些提供这些类型的引用信息的表时，常常使用术语"lookup"(查找)。在
-- 一些应用程序中，创建这些表时会使用这个关键字,在实例state_lookup中我们会
-- 沿用这一惯例.
-- 首先创建一个查找表，该表提供了一个完整的关于州简称的列表。然后利用引用
-- 完整性来保证学生拥有有效的州简称，第一个表是以state为主键的州的查找表
create table state_lookup
(state varchar2(2),
state_desc varchar2(30)
)tablespace student_date;

alter table state_lookup
add constraint pk_state_lookup primary key(state)
using index tablespace student_index;

-- 现在插入几行:
insert into state_lookup values('CA','California');
insert into state_lookup values('NY','New York');
insert into state_lookup values('NC','North Carolina');

select * from state_lookup;

-- 我们通过实现父子关系来强制实施引用完整性，如图3-2所示:
-- 图3-2显示了state_lookup表与students表之间的一对多关系。state_lookup表
-- 定义了州简称的"通用集合"。每个州在该表只出现一次，因此,state_lookup表
-- 的主键在state列上。
-- state_lookup表中的一个州可以在students表中出现多次，因为可能有很多学生
-- 来自同一个州，因此，引用完整性实现了从state_lookup表到students表的一对
-- 多关系。
-- 外键也确保了students表中state列的完整性。一个有驾驶执照的学生将会有
-- 一个州的简称，它是state_lookup表中的一员.
-- state_lookup->students
-- 外键列位于students表中，这里外键列就是state.
-- 外键必须要么引用primary key列，要么引用unique列，在本实例中，我们引用
-- 的是state主键

-- 外键约束是在子表上创建的，以下是带有一个外键约束的students表，通过
-- alter table 语句，可以声明state列有外键约束，并且该外键引用的是
-- state_lookup表的主键列.
drop table students;

create table students
(student_id varchar2(10) not null,
student_name varchar2(30) not null,
college_major varchar2(15) not null,
status varchar2(20) not null,
state varchar2(2),
license_no varchar2(30)
)tablespace student_date;

alter table students
add constraint pk_students primary key(student_id)
using index tablespace student_index;

alter table students
add constraint uk_students_license
unique (state,license_no)
using index tablespace student_index;

alter table students
add constraint ck_students_st_lic
check((state is null and license_no is null) or
(state is not null and license_no is not null));

alter table  students
add constraint fk_students_state
foreign key(state) references state_lookup(state);

-- 脚本3-1 带有州查找ddl的外键
-- 脚本3-1中的ddl脚本创建了students表和表约束。这些约束强制实施以下规则:
-- 规则                                          通过什么来强制实施
-- 学生由student_id唯一标识                      主键约束

-- 学习可能有驾驶执照，如果它们有，则州与执照    在state和license
-- 的组合在所有其他学生中是唯一的                上的唯一性约束

-- 如果state为null,则lincense_no也必须为null，   在state和license
-- 否则，两者同时为not null                      上的检查约束

-- 对于state列,学生可能有一个列值，如果它们      在state上的外键约束
-- 有，则state简称相对state_lookup表而言
-- 是有效的

-- 3.3.1 四类错误
-- 在对父表进行更新和删除操作，对子表进行插入和更新操作时，都需要强制实施
-- 引用完整性的规则。引用完整性可以影响到的sql语句有:
-- parent-update：不能使用某个值update(更新)state_lookup表中的一个state,
--                从而使得学生所具有的州简称不再在state_lookup表中。
-- parent-delete: 不能delete(删除)一个state,从而使学生的州简称不再在父
--                查找表中，例如，如果学生有加州执照，它的简称为'CA',
--                这样就不能从state_lookup表中删除'CA'行.
-- child_insert:  不能insert(插入)其所在州不在state_lookup表中的学生，
--                例如，不能插入一个有执照的学生，而将state列设置为
--                state_lookup表中不存在的一个值
-- child-update:  不能在update一名学生时用父州查找表中不存在的州替换现有
--                的州

-- 下面的sql语句显示了四种错误类型，以及在违反约束时返回的oracle错误，这些
-- 插入，更新和删除以state_lookup表和students表的数据为假想对象:

state_lookup
州             州的描述
ca             califomia
ny             new york
nc             north carolina

students
学生id   学生姓名     专业          状态      州      执照号
a101     John         biology       degree    null    null
a102     Mary         Math/science  degree    null    null
a103     kathryn      HIstory       degree    ca      mv-232-13
a104     steven       biology       degree    ny      mv-232-14
a105     william      English       degree    nc      mv-232-15


select * from students;

insert into students values('a101','John','biology','degree',null,null);
insert into students values('a102','mary','math/science','degree',null,
null);
insert into students values('a103','kathryn','history','degree','CA',
'mv-232-13');
insert into students values('a104','steven','biology','degree','NY',
'mv-232-14');
insert into students values('a105','villiam','english','degree','NC',
'mv-232-15');

select * from state_lookup;

-- 前两条sql语句是更改父表，在更改父表之前，oracle必须检查子表的内容，以
-- 确保数据完整性
-- parent-update
update state_lookup
set state='XX'
where state='CA';

delete from state_lookup;

-- 接下来的两条语句是更改子表，在子表上的每条dml语句要求oracle检查父表的
-- 内容,以确保数据完整性
-- child-insert
insert into students values('A000','Joseph','History','Degree','XX',
'MV-232-00');

-- child-update
update students
set state='XX'
where student_id='A103';

-- 要使oracle强制实施这四条规则，它必须读这两个表，假设有外键约束，对
-- students表的一条简单的insert就要求oracle读取state_lookup表，如果需要
-- 从state_lookup表中delte记录，oracle必须读取students表以确保没有哪行
-- 学生引用了state_lookup行的state值.

-- 在以上的四种错误类型中，每种错误的错误号是相同的:ora-02291

-- 引用完整性是数据库设计的一个关键部分，很少有表既不是父表，有不是其他表
-- 的子表，如果看到一个在大型绘图机上显示的数据模型，那么首先注意到的就是
-- 那些没有连线的表，它们既无父表，又无子表。

-- 3.3.2 删除级联.sql
-- 可以使用外键语法选项来指定删除级联特性，该特性只影响父表中的删除语句。
-- 有了这个选项，父表中的删除语句将同时自动删除所有相关子表记录，在脚本
-- 3.1中我们已经创建了外键约束，"foreign key"，有了delte casecade选项，下面
-- 的sql将删除state_lookup表中关于california的记录。同时还删除所有持有
-- california执照的学生.
delete from state_lookup where state='CA';

-- delete cascade的语法是:
on delete cascade

-- 脚本3-1中所示的用于外键约束的语法可以用级联方式重写一遍，如下所示:
alter table students drop constraint fk_students_state;

alter table students
add constraint fk_students_state
foreign key(state) references state_lookup(state) on delete cascade;

-- 级联删除方式是一种例外,而不是规则，当删除级联父查找表中的一行时,无意中
-- 可能使大量的数据丢失，该方法在有些应用程序中是非常有用的.如果数据是临时
-- 性的.即只存活较短的时间,并且最终将被删除，那么使用这种方法可以带来很大
-- 的方便.

-- 删除几个表之间的级联可能潜在地影响系统的其他部分，如果删除操纵工程浩大，
-- 需要扫描表，还要删除数百万行记录，则需要有能跟上如此规模的回滚空间来写
-- 撤销信息。如果删除级联过多，则要分析回滚空间。另外，同时对表进行查询的
-- 的性能也将受到影响，在声明删除级联选项时，应该考虑以下几点.
-- 1,级联是否适合应用程序?偶然地删除父查找表中的记录时不应该删除客户帐户.
-- 2.要声明的链是怎样的？考虑删除操作的潜在影响及其规模，以及它对性能有
-- 怎样的影响。

- 3.3.3 强制外键列.sql
-- 外键约束规定了这样一条规则：孩子可能是父亲的一员，如果孩子是一个成员,就
-- 必须是完整的,而外键约束上的not null约束则将可能变成了必须.

-- 脚本3-1中的外键约束强制实施以下规则:
-- 1.学生可能有驾驶执照，如果它们有,该州是state_lookup表的一员
-- 该规则可以有不同的表现形式,重述如下:
-- 2.学生必须有驾驶执照，州是state_lookup表中的一员

-- 后者仍旧是引用完整性的一种陈述，其中仍旧存在一对多的关系,当子列上声明了
-- not null约束时,规则就变成是必须的，业务规则也被修正，因为state和license
-- 是强制的.所以check约束被取消，外键关系从可能变成了必须.

-- 脚本3-2中的students表和state_lookup表的ddl强制实施以下规则:
规则                                   通过什么来强制实施
学生由student_id唯一标识               主键约束

学生必须有驾驶执照，州与执照的组合在   唯一性约束
所有学生中是唯一的

对于state,学生必须有列值,state简称对   外键约束
state_lookup表是有效的                 state列上的值not null

-- 执行这些规则的del脚本语句与3.1中的相似,其中没有check约束。但是添加了
-- not null约束,不需要改变state_lookup表来适应规则的改变.
drop table students;

-- 脚本2,带强制约束的外键

create table students
(student_id varchar2(10) not null,
student_name varchar2(30) not null,
college_major varchar2(15) not null,
status varchar2(20) not null,
state varchar2(2) not null,
license_no varchar2(30) not null)
tablespace student_date;

alter table students
add constraint pk_students primary key(student_id)
using index tablespace student_index;

alter table students
add constraint uk_student_license
unique (state,license_no)
using index tablespace student_index;

alter table students
add constraint fk_students_state
foreign key(state) references state_lookup(state);

- 3.3.4 引用父语法.sql
-- 在创建了外键约束后,外键上的列将引用那些组成了主键约束和唯一性约束的列。在
-- 外键引用父主键的情况下，不需要指定列，例如,脚本3-2使用了下面的语法:
alter table students
add constraint fk_students_state
foreign key(state) references state_lookup(state);

-- 没有引用父列时,默认引用的父列的主键,以下代码效果是一样的.
alter table students
add constraint fk_students_state
foreign key(state) references state_lookup;

-- 当外键引用了有唯一性约束的父列时，必须要在add constraint语句中指定该父列

-- 3.3.5 跨模式和数据库的引用完整性.sql
-- 表中的外键可以引用其他模式中的父表。这是不可取的.至少从数据库管理员的
-- 角度来看是如此，完全处于一个单一模式中的应用程序是有很好的可移植性，作为
-- 一个单一的所有者导出的应用程序很容易导入到另一个数据库中。当应用程序需要
-- 扫描模式的功能时,移植过程就不那么顺利了。应用程序往往得不到完全的移植。
-- 因为在移植的过程中会忽略那些处于其他模式中的功能，导出的dump文件在导入时
-- 可能引来错误.这时外键约束也将失效。因为被引用的模式不存在，被引用模式中
-- 的对象也不存在，或者没有创建针对被引用模式的权限。

-- 引用完整性可以不同程度地跨数据库实现，这可能会比较复杂,可用的一些选择是
-- 使用触发器，刷新的物化视图(materialized view)或者oracle复制,物化视图是
-- 一种直接而平衡的解决方案。物化视图可成为本地模式中的对象。利用其他数据库
-- 中的数据可以将其刷新。这里要求数据库链接的同时要有create any
-- materialized view权限.视图的刷新率并不能使数据库是实时的，但是如果物化
-- 视图经常刷新，则可以接近实时.

-- 假设学生数据库需要从政府执照代理处获得相对较新的数据。例如学生数据库需要
-- license表中的数据,在这种环境下，执照代理处是数据库,而license表是主表.

-- license表在政府代理处的服务器agency.gov中的prod数据库上，它的用户名/密码
-- 为scott/tiger.license表一定有主键。目标是拥有license表的一个本地快照。
-- 每15分钟就刷新一次，这并不是实时的，但肯定能满足学生数据库的需求。图
-- 3。3说明了这一远程连接。

-- 该过程首先在prod.agency.gov数据库上创建license表的物化视图日志
-- (materialized view log). oracle将利用该日志跟踪对主表的更改，日志格式中
-- 数据用于刷新学生数据库中的物化视图.  
学生数据库      快照每15分钟刷新一次        prod.agency.gov数据库
state_lookup->students 物化视图<------------license表

-- 在远程数据库中:
create materialized view log on license;

-- 在学生数据库中，创建到远程prod数据库的链接,然后创建物化视图，该视图每15
-- 分钟刷新一次，如果网络关闭。学生应用程序仍然可以访问最近一次刷新的数据
create database link prod.agency.gov
connect to scott identified by tiger
using 'prod.agency.gov';

create materialized view student_license_records
refresh fast next sysdate*1/96
as select * from [e-mail]

-- 现在,学生表就可以从有执照信息的政府数据库表中定位本地的数据快照。
-- 创建物化视图需要非默认的权限，物化视图应该根据快照大小用存储字句来创建

3.3.6 多父及ddl迁移.sql

-- 一个子表通常有多个查找表，通常使用查找表可以大大提高数据的完整性。students
-- 表的第一列是state列，该列的值含两个字符，可以创建查找表state_lookup来增加
-- 该列的完整性。在填充了state_lookup表后，就可以创建外键了，我们可以增加
-- state简称的值域以及州的描述。而不影响子表。在整个开发过程中，常常要添加
-- 一些查找表。以提供数据完整性。

-- 从终端用户的角度看，查找表的维护常常要解决终端用户的角色和权限等问题。
-- 对于任何应用程序，都需要有特定的角色和权限来运行。而对一些表(例如
-- state_lookup表)进行更改时，将会、也应该需要特殊的访问途径。

-- 开发应用软件来支持终端用户对查找表的维护只需执行一些简单的剪切与粘贴
-- 操作，大部分的查找表有两列。一旦开发了第一个查找维护屏幕，则对另一个
-- 查找表的维护就差不多只需复制了。

-- 显示查找数据的应用程序代码要遵循一个标准惯例，那就是决不显示主键列。要
-- 在屏幕上显示学生数据，则要求联结students表和state_lookup表.对于每位学生
-- 显示学生姓名和州描述，但不是州字段的两个字母，由州描述来填充html表单元素
-- 例如下拉列表。但要将州作为选项值.
<option value=state>state_description</option>

-- 通过观察第3.3.1 一节表中数据的概述。很容易使人相信学生的专业也应该在
-- 查找表中。该附加的查找表会将专业描述的取值范围限制为一个查找表的值，
-- major_lookup表将存储像'Biology'和'math'这样的描述。添加的这些值将由
-- 图3-4所示的实体图来建模
state_lookup ->students<-  major_lookup
图3-4多父

-- 在students表和state_lookup表的基础上再添加major_lookup表时需要利用下面的
-- 语句实现，假设state_lookup表和students表中存在像3.3.1 一节描述的数据。
-- 必须创建和填充查找表major_lookup,以下包含了create脚本。主键以及数据装载
create table major_lookup
(major varchar2(2) not null,
major_desc varchar2(15) not null)
tablespace student_date;

insert into major_lookup values('UD','Undeclared');
insert into major_lookup values('BI','Biology');
insert into major_lookup values('MS','Math/Science');
insert into major_lookup values('HI','History');
insert into major_lookup values('EN','English');

select * from major_lookup;
            
alter table major_lookup
add constraint pk_major_lookup primary key(major)
using index tablespace student_index;

-- 必须改变students表，这个表以varchar2(15)类型存储专业。这里应该将其替换
-- 为由两个字符组成的字段。这个字段将成为major_lookup表的外键、该方法为
-- students表创建了一个有数据的临时复制(表temp)。接下来删除students表。
-- 创建一个新的Students表。同时将保存的数据迁移到新的students表中.
select * from luoping_temp;

insert into students values('a103','kathryn','History','degree','CA',
'mv-232-13');
insert into students values('a104','steven','biology','degree','NY',
'mv-232-14');
insert into students values('a105','villiam','english','degree','NC',
'mv-232-15');

select * from students;
select * from state_lookup;


create table students_temp as select * from students;

drop table students;

create table students
(student_id varchar2(10) not null,
student_name varchar2(30) not null,
college_major varchar2(2) not null,
status varchar2(15) not null,
state varchar2(2),
license_no varchar2(30)
)tablespace student_date;

insert into students
select student_id,student_name,decode
(college_major,
'Undeclared' , 'UD',
'Biology'    , 'BI',
'Math/Science','MS',
'History'    , 'HI',
'English'    , 'EN'
),
status,
state,
license_no
from students_temp;

-- 这里填充了新的students表，但填充时是用BI代表biology的。在students表上
-- 添加了约束。这包括:
-- 1.primary key
-- 2.在state,license_no上的unique约束
-- 3.在state，license_no上的check约束
-- 4.引用state_lookup表的外键
-- 5.引用major_lookup表的外键

alter table students
add constraint pk_students primary key(student_id)
using index tablespace student_index;

alter table students
add constraint uk_students_license
unique(state,license_no)
using index tablespace student_index;

alter table students
add constraint ck_students_st_lic
check((state is null and license_no is null) or
(state is not null and license_no is not null));

alter table students
add constraint fk_students_state
foreign key(state) references state_lookup(state);

alter table students
add constraint fk_students_college_major
foreign key(college_major) references major_lookup(major);

3.3.7 多对多关系.sql
-- 在逻辑层，数据建模工具将用符号画出多对多关系。如图3-5所示:
students<->courses 多对多关系

-- 图3-5中的模型表明，一个学生可以选多门功课。而每门功课可以由多个学生选修。在
-- 学生与功课之间有多对多的关系。在物理上，这不是直接实现的，而是我们把一个交叉
-- 引用表包括进来，该交叉引用表，即students_courses将包含某位学生选修的所有功课
-- 以及选修了某一功课的所有学生。其物理模型就变成了图3-6中所示的图形.
students<-student_courses<-courses
-- 以下类型的约束普遍适用于多对多关系的物理实现
-- 1.在Students中有一个主键
-- 2.在courses中有一个主键
-- 3.在交叉引用表students_courses中，有concatenated primary key.
-- 4.主键中的一部分是students表的外键.还有一部分是courses表的外键
-- 5.在students_courses表中有一个外键，用以确保此表中的每个学生也是students表中
-- 的一名学生。此外键中的列也是主键中的一部分
-- 6.在students_courses表中有一个外键，用以确保此表中每门功课也是students表中
-- 的一门功课。此外键中的列也是主键中的一部分.

-- 当我们以约束的形式应用这些规则时，会有3条create table语句，3个primary
-- key,以及2个foreign key 约束

-- 交叉引用表的列包含那些必须引用父表的列，在这种情况下，父表就是students
-- 和courses,这是第一步。这个表中还可以添加一些附加的列。例如讲授某节课的
-- 教授以及何时讲授该课。交叉引用表可以只是连接的列，也可以包含附加的属性。
-- 脚本3-3中显示了用于表示多对多关系的ddl
drop table students;

create table students(
student_id varchar2(10) not null,
student_name varchar2(30) not null,
college_major varchar2(15) not null,
status varchar2(20) not null,
state varchar2(2),
license_no varchar2(30)
)tablespace student_date;


create table courses(
course_name varchar2(10) not null,
course_desc varchar2(20) not null,
no_of_credits number(2,1) not null
)tablespace student_date;

create table students_courses(
student_id varchar2(10) not null,
course_name varchar2(10) not null
) tablespace student_date;

alter table students
add constraint pk_students
primary key(student_id)
using index tablespace student_index;

alter table courses
add constraint pk_courses
primary key(course_name)
using index tablespace student_index;

alter table students_courses
add constraint pk_students_courses
primary key(student_id,course_name)
using index tablespace student_index;


alter table students_courses
add constraint fk_students_courses_st_id
foreign key (student_id)
references students(student_id);

alter table students_courses
add constraint fk_students_courses_course
foreign key(course_name)
references courses(course_name);






3.3.7 多对多关系.sql
-- 在逻辑层，数据建模工具将用符号画出多对多关系。如图3-5所示:
students<->courses 多对多关系

-- 图3-5中的模型表明，一个学生可以选多门功课。而每门功课可以由多个学生选修。在
-- 学生与功课之间有多对多的关系。在物理上，这不是直接实现的，而是我们把一个交叉
-- 引用表包括进来，该交叉引用表，即students_courses将包含某位学生选修的所有功课
-- 以及选修了某一功课的所有学生。其物理模型就变成了图3-6中所示的图形.
students<-student_courses<-courses
-- 以下类型的约束普遍适用于多对多关系的物理实现
-- 1.在Students中有一个主键
-- 2.在courses中有一个主键
-- 3.在交叉引用表students_courses中，有concatenated primary key.
-- 4.主键中的一部分是students表的外键.还有一部分是courses表的外键
-- 5.在students_courses表中有一个外键，用以确保此表中的每个学生也是students表中
-- 的一名学生。此外键中的列也是主键中的一部分
-- 6.在students_courses表中有一个外键，用以确保此表中每门功课也是students表中
-- 的一门功课。此外键中的列也是主键中的一部分.

-- 当我们以约束的形式应用这些规则时，会有3条create table语句，3个primary
-- key,以及2个foreign key 约束

-- 交叉引用表的列包含那些必须引用父表的列，在这种情况下，父表就是students
-- 和courses,这是第一步。这个表中还可以添加一些附加的列。例如讲授某节课的
-- 教授以及何时讲授该课。交叉引用表可以只是连接的列，也可以包含附加的属性。
-- 脚本3-3中显示了用于表示多对多关系的ddl
drop table students;

create table students(
student_id varchar2(10) not null,
student_name varchar2(30) not null,
college_major varchar2(15) not null,
status varchar2(20) not null,
state varchar2(2),
license_no varchar2(30)
)tablespace student_date;


create table courses(
course_name varchar2(10) not null,
course_desc varchar2(20) not null,
no_of_credits number(2,1) not null
)tablespace student_date;

create table students_courses(
student_id varchar2(10) not null,
course_name varchar2(10) not null
) tablespace student_date;

alter table students
add constraint pk_students
primary key(student_id)
using index tablespace student_index;

alter table courses
add constraint pk_courses
primary key(course_name)
using index tablespace student_index;

alter table students_courses
add constraint pk_students_courses
primary key(student_id,course_name)
using index tablespace student_index;


alter table students_courses
add constraint fk_students_courses_st_id
foreign key (student_id)
references students(student_id);

alter table students_courses
add constraint fk_students_courses_course
foreign key(course_name)
references courses(course_name);

3.3.8 自引用完整性.sql
-- 自引用完整性常常出现在很多应用程序中.自引用完整性允许相同实体实例间存在
-- 父-子关系。例如，所有教授在下面的frofessors表中.表中还包括所在系的系主任
-- 多名教授有一个系主任，这些系主任同样也是教授，以下代码创建一个表和一个
-- 主键。然后建立一个自引用完整性约束。
drop table professors;

create table professors(
prof_name varchar2(10) not null,
specialty varchar2(20) not null,
hire_date date         not null,
salary    number(5)    not null,
dept_head varchar2(10)
)tablespace student_date;

alter table professors
add constraint pk_professors
primary key(prof_name)
using index tablespace student_index;

alter table professors
add constraint fk_professors_prof_name
foreign key(dept_head)
references professors(prof_name);

-- 允许这种场景存在，某个教授可能有一个系主任(dept_head)。如果他们有一个系
-- 主任的话，则该列的值必须是一个现有的prof_name。例如,如图3-7所示,blake是
-- milton的系主任.
prof_name   specialty   hire_date  salary   dept_head
blake       English     26-JUL-99  10000   
wilson      Physics     27-JUL-98  10000
milton      English     24-JUL-02  10000    Black
jones       Math        29-JUL-03  10000    Wilson

-- 对于以上的数据。自引用完整性只有一层。外键定义允许无限制的嵌套。下面的
-- 例子就举例说明了多层嵌套。我们创建了一个示例表temp。它有3列。worker,
-- salary和manager. 一名员工可能是一个管理者，也可能没有管理者，但一个管理
-- 者首先必须作为一名员工插入到表中。一名员工可以是其他员工的管理者，其他
-- 员工可能同时也是别的员工的管理者，这些员工之间的关系以及它们的薪水如下
-- 所示:
create table worker_temp(
worker varchar2(10) primary key,
salary number(3),
manager varchar2(10) references worker_temp(worker)
)tablespace student_date;

-- Allen管理bill和beth. beth管理cindy和carl。 carl管理dean和dave。
allen (salary=10) managers:bill(salary=10),beth(salary=10)
beth (salary=10) managers:cindy(salary=5), carl(salary=5)
carl (salary=5) managers:dean(salary=5), dave(salary=5)

-- 这些数据包含了多层关系。并构成了一个逻辑树结构。例如。使用connect by
-- 和start子句的select语句使查询能够返回从树上某一特定点开始的所有薪水
-- 的总和。数据插入语句如下所示:
insert into worker_temp values('Allen',10,null);
insert into worker_temp values('Bill',10,'Allen');
insert into worker_temp values('Beth',10,'Allen');
insert into worker_temp values('Cindy',5,'Beth');
insert into worker_temp values('Carl',5,'Beth');
insert into worker_temp values('Dean',5,'Carl');
insert into worker_temp values('Deve',5,'Carl');

-- 以下是一条start at select语句。该语句用于返回beth以及那些为bean工作的
-- 员工的薪水。这里就是beth、cindy,carl以及由carl管理的dean,dave的薪水
-- 总和。总数是30.00美元.

select sum(salary) from worker_temp
start with worker='Allen'
connect by prior worker=manager;

3.3.9 与父/子表相关的pl sql错误处理.sql
-- 通过将异常映射为oracle错误。即-2291.可以捕获到外键的约束错误。以下过程
-- 插入一行学生的记录。并且捕捉到重复插入。在这个例子中。重复可能违反主键
-- 约束。也可能违反唯一性约束。两者都产生dup_val_on_index异常
-- 脚本3-1中的ddl在state和license_no上声明了check约束。下面的过程插入一位
-- 学生。并捕获可能违反主键约束或唯一性约束和检查约束的重复插入
create or replace procedure
  insert_student(v_student_id varchar2,
                 v_student_name varchar2
                 v_college_major varchar2,
                 v_status varchar2,
                 v_state varchar2,
                 v_license_no varchar2)
is
  check_constraint_violation exception;
  pragma exception_init(check_constraint_violation,-2290);
 
  foreign_key_violation exception;
  pragma exception_init(foreign_key_violation,-2291);
begin
  insert into students values
  (v_student_id,v_student_name,
  v_college_major,v_status,
  v_state,v_license_no);
  dbms_output.put_line('insert compete');
  exception
  when dup_val_on_index then
    dbms_output.put_line('PK or unique const violation');
  when check_constraint_violation then
    dbms_output.put_line('check constraint violation');
  when foreign_key_violation then
    dbms_output.put_line('foreign key violation');
end;

3.3.10 可延迟选项.sql
-- 在本节中我们使用两个存储一般父子数据的表.其数据模型如图3-8所示
-- parent<-child 父子关系

select * from parent;
drop table parent;

-- 下面的DDL包含了一个外键约束上的deferrable选项
create table parent
(parent_name varchar2(2) constraint