pl/sql入门第四章--数据库数据对象分析（上）

Oracle数据库数据对象中最基本的是表和视图，其他还有约束、序列、函数、存储过程、包、触发器等。对数据库的操作可以基本归结为对数据对象的操作,理解和掌握Oracle数据库对象是学习Oracle的捷径。

　　表和视图

　　Oracle中表是数据存储的基本结构。ORACLE8引入了分区表和对象表，ORACLE8i引入了临时表，使表的功能更强大。视图是一个或多个表中数据的逻辑表达式。本文我们将讨论怎样创建和管理简单的表和视图。

　　管理表

　　表可以看作有行和列的电子数据表，表是关系数据库中一种拥有数据的结构。用CREATE TABLE语句建立表，在建立表的同时，必须定义表名，列，以及列的数据类型和大小。例如：

CREATE TABLE products 　　( PROD_ID NUMBER(4), 　　　PROD_NAME VAECHAR2(20), 　　　STOCK_QTY NUMBER(5,3) 　　);

　　这样我们就建立了一个名为products的表， 关键词CREATE TABLE后紧跟的表名，然后定义了三列，同时规定了列的数据类型和大小。

　　在创建表的同时你可以规定表的完整性约束，也可以规定列的完整性约束，在列上普通的约束是NOT NULL,关于约束的讨论我们在以后进行。

　　在建立或更改表时，可以给表一个缺省值。缺省值是在增加行时，增加的数据行中某一项值为null时，oracle即认为该值为缺省值。

　　下列数据字典视图提供表和表的列的信息：

　　　. DBA_TABLES
　　　. DBA_ALL_TABLES
　　　. USER_TABLES
　　　. USER_ALL_TABLES
　　　. ALL_TABLES
　　　. ALL_ALL_TABLES
　　　. DBA_TAB_COLUMNS
　　　. USER_TAB_COLUMNS
　　　. ALL_TAB_COLUMNS

　　表的命名规则

　　表名标识一个表，所以应尽可能在表名中描述表，oracle中表名或列名最长可以达30个字符串。表名应该以字母开始，可以在表名中包含数字、下划线、#、$等。

　　从其它表中建立表

　　可以使用查询从基于一个或多个表中建立表，表的列的数据类型和大小有查询结果决定。建立这种形式的表的查询可以选择其他表中所有的列或者只选择部分列。在CREATE TABLE语句中使用关键字AS，例如:

SQL>CREATE TABLE emp AS SELECT * FROM employee TABLE CREATED SQL> CREATE TABLE Y AS SELECT * FROM X WHERE no=2

　　需要注意的是如果查询涉及LONG数据类型，那么CREATE TABLE....AS SELECT....将不会工作。

　　更改表定义

　　在建立表后，有时候我们可能需要修改表，比如更改列的定义，更改缺省值，增加新列，删除列等等。ORACLE使用ALTER TABLE语句来更改表的定义

　　1、增加列

　　语法：

ALTER TABLE [schema.] table_name ADD column_definition

　　例：

ALTER TABLE orders ADD order_date DATE; TABLE ALTER

　　对于已经存在的数据行，新列的值将是NULL.

　　2、更改列

　　语法：

ALTER TABLE [schema.] table_name MODIFY column_name new_attributes;

　　例：

ALTER TABLE orders MODITY (quantity number(10,3),status varchar2(15));

　　这个例子中我们修改了表orders，将STATUS列的长度增加到15,将QUANTITY列减小到10,3;

　　修改列的规则如下：

　　　. 可以增加字符串数据类型的列的长度，数字数据类型列的精度。

　　　. 减少列的长度时，该列应该不包含任何值，所有数据行都为NULL.

　　　. 改变数据类型时，该列的值必须是NULL.

　　　. 对于十进制数字，可以增加或减少但不能降低他的精度。

　　3、删除数据列

　　优化ORACLE数据库，唯一的方法是删除列，重新建立数据库。在ORACLE8i中有很多方法删除列，你可以删除未用数据列或者可以标示该列为未用数据列然后删除。

　　删除数据列的语法是：

ALTER TABLE [schema.] table_name DROP {COLUM column_names | (column_names)}[CASCADE CONSTRAINS]

　　要注意的是在删除列时关于该列的索引和完整性约束也同时删除。注意关键字CASCADE CONSTRAINS，如果删除的列是多列约束的一部分，那么这个约束条件相对于其他列也同时删除。

　　如果用户担心在大型数据库中删除列要花太多时间，可以先将他们标记为未用数据列，标记未用数据列的语法如下：

ALTER TABLE [schema.] table_name SET UNUSED {COLUM column_names | (column_names)}[CASCADE CONSTRAINS]

　　这个语句将一个或多个数据列标记为未用数据列，但并不删除数据列中的数据，也不释放占用的磁盘空间。但是，未用数据列在视图和数据字典中并不显示，并且该数据列的名称将被删除，新的数据列可以使用这个名称。基于该数据列的索引、约束，统计等都将被删除。

　　删除未用数据列的语句是：

ALTER TABLE [schema.] table_name DROP {UNUSED COLUM | COLUMN CONTINUE}

删除表和更改表名

　　删除表非常简单，但它是一个不可逆转的行为。

　　语法：

DROP TABLE [schema.] table_name [CASCADE CONSTRAINTS]

　　
　　删除表后，表上的索引、触发器、权限、完整性约束也同时删除。ORACLE不能删除视图，或其他程序单元，但oracle将标示他们无效。如果删除的表涉及引用主键或唯一关键字的完整性约束时，那么DROP TABLE语句就必须包含CASCADE CONSTRAINTS子串。

　　更改表名

　　RENAME命令用于给表和其他数据库对象改名。ORACLE系统自动将基于旧表的完整性约束、索引、权限转移到新表中。ORACLE同时使所有基于旧表的数据库对象，比如视图、程序、函数等，为不合法。

　　语法：

RENAME old_name TO new_name;

　　例：

SQL> RENAME orders TO purchase_orders; TABLE RENAMED

　　截短表

　　TRUNCATE命令与DROP命令相似， 但他不是删除整个数据表，所以索引、完整性约束、触发器、权限等都不会被删除。缺省情况下将释放部分表和视图空间，如果用户不希望释放表空间，TRUNCATE语句中要包含REUSE STORAGE子串。TRUNCATE命令语法如下：

TRUNCATE {TABLE|CLUSTER} [schema.] name {DROP|REUSE STORAGE}

　　例：

SQL> TRUNCATE TABLE t1; TABLE truncate.

　　管理视图

　　视图是一个或多个表中的数据的简化描述，用户可以将视图看成一个存储查询（stored query)或一个虚拟表(virtual table).查询仅仅存储在oracle数据字典中，实际的数据没有存放在任何其它地方，所以建立视图不用消耗其他的空间。视图也可以隐藏复杂查询，比如多表查询，但用户只能看见视图。视图可以有与他所基于表的列名不同的列名。用户可以建立限制其他用户访问的视图。

　　建立视图

　　CREATE VIEW命令创建视图，定义视图的查询可以建立在一个或多个表，或其他视图上。查询不能有FOR UPDATE子串，在早期的ORACLE8i版本中不支持ORDER BY子串，现在的版本中CREATE VIEW可以拥有ORDER BY子串。

　　例：

SQL> CREATE VIEW TOP_EMP AS SELECT empno EMPLOYEE_ID,ename EMPLOYEE_NAME,salary FROM emp WHERE salary >2000

　　用户可以在创建视图的同时更改列名，方法是在视图名后立即加上要命名的列名。重新定义视图需要包含OR REPLACE子串。

SQL> CREATE VIEW TOP_EMP （EMPLOYEE_ID，EMPLOYEE_NAME，SALARY) AS SELECT empno ,ename ,salary FROM emp WHERE salary >2000

　　如果在创建的视图包含错误在正常情况下，视图将不会被创建。但如果你需要创建一个带错误的视图必须在CREATE VIEW语句中带上FORCE选项。如：

CREATE FORCE VIEW ORDER_STATUS AS SELECT * FROM PURCHASE_ORDERS WHERE STATUS='APPPOVE'; SQL>/ warning :View create with compilation errors

　　这样将创建了一个名为ORDER_STATUS的视图，但这样的视图的状态是不合法的，如果以后状态发生变化则可以重新编译，其状态也变成合法的。

　　从视图中获得数据

　　从视图中获得数据与从表中获得数据基本一样，用户可以在连接和子查询中使用视图，也可以使用SQL函数，以及所有SELECT语句的字串。

　　插入、更新、删除数据

　　用户在一定的限制条件下可以通过视图更新、插入、删除数据。如果视图连接多个表，那么在一个时间里只能更新一个表。所有的能被更新的列可以在数据字典USER_UPDATETABLE_COLUMNS中查到。

　　用户在CREATE VIEW中可以使用了WITH子串。WITH READ ONLY子串表示创建的视图是一个只读视图，不能进行更新、插入、删除操作。WITH CHECK OPTION表示可以进行插入和更新操作，但应该满足WHERE子串的条件。这个条件就是创建视图WHERE子句的条件，比如在上面的例子中用户创建了一个视图TOP_EMP,在这个视图中用户不能插入salary小于2000的数据行。

　　删除视图

　　删除视图使用DROP VIEW命令。同时将视图定义从数据字典中删除，基于视图的权限也同时被删除，其他涉及到该视图的函数、视图、程序等都将被视为非法。

　　例：

DROP VIEW TOP_EMP;

Oracle数据库数据对象分析（中）

 

完整性约束

　　完整性约束用于增强数据的完整性，Oracle提供了5种完整性约束：

　　　 Check
　　　 NOT NULL
　　　 Unique
　　　 Primary
　　　 Foreign key

　　完整性约束是一种规则，不占用任何数据库空间。完整性约束存在数据字典中，在执行SQL或PL/SQL期间使用。用户可以指明约束是启用的还是禁用的，当约束启用时，他增强了数据的完整性，否则，则反之，但约束始终存在于数据字典中。

　　禁用约束，使用ALTER语句

ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;

　　或

ALTER TABLE policies DISABLE CONSTRAINT chk_gender

　　如果要重新启用约束：

ALTER TABLE policies ENABLE CONSTRAINT chk_gender

　　删除约束

ALTER TABLE table_name DROP CONSTRAINT constraint_name

　　或

ALTER TABLE policies DROP CONSTRAINT chk_gender;

　　Check 约束

　　在数据列上Check 约束需要 一个特殊的布尔条件或者将数据列设置成TRUE，至少一个数据列的值是NULL，Check约束用于增强表中数据内容的简单的商业规则。用户使用Check约束保证数据规则的一致性。Check约束可以涉及该行同属Check约束的其他数据列但不能涉及其他行或其他表，或调用函数SYSDATE,UID,USER,USERENV。如果用户的商业规则需要这类的数据检查，那么可以使用触发器。Check约束不保护LOB数据类型的数据列和对象、嵌套表、VARRY、ref等。单一数据列可以有多个Check约束保护，一个Check约束可以保护多个数据列。

　　创建表的Check约束使用CREATE TABLE语句，更改表的约束使用ALTER TABLE语句。

　　语法：

CONSTRAINT [constraint_name] CHECK (condition);

　　Check约束可以被创建或增加为一个表约束，当Check约束保护多个数据列时，必须使用表约束语法。约束名是可选的并且如果这个名字不存在，那么oracle将产生一个以SYS_开始的唯一的名字。

　　例：

CREATE TABLE policies (policy_id NUMBER, holder_name VARCHAR2(40), gender VARCHAR2(1) constraint chk_gender CHECK (gender in ('M','F'), marital_status VARCHAR2(1), date_of_birth DATE, constraint chk_marital CHECK (marital_status in('S','M','D','W')) );

　　NOT NULL约束

　　NOT NULL约束应用在单一的数据列上，并且他保护的数据列必须要有数据值。缺省状况下，ORACLE允许任何列都可以有NULL值。某些商业规则要求某数据列必须要有值，NOT NULL约束将确保该列的所有数据行都有值。

　　例：

CREATE TABLE policies (policy_id NUMBER, holder_name VARCHAR2(40) NOT NULL, gender VARCHAR2(1), marital_status VARCHAR2(1), date_of_birth DATE NOT NULL );

　　对于NOT NULL的ALTER TABLE语句与其他约束稍微有点不同。

ALTER TABLE policies MODIFY holder_name NOT NULL

唯一性约束(Unique constraint)

　　唯一性约束可以保护表中多个数据列，保证在保护的数据列中任何两行的数据都不相同。唯一性约束与表一起创建，在唯一性约束创建后，可以使用ALTER TABLE语句修改。

　　语法：

column_name data_type CONSTRAINT constraint_name UNIQUE

　　如果唯一性约束保护多个数据列，那么唯一性约束要作为表约束增加。语法如下：

CONSTRAINT constraint_name (column) UNIQUE USING INDEX TABLESPACE (tablespace_name) STORAGE (stored clause)

　　唯一性约束由一个B-tree索引增强，所以可以在USING子串中为索引使用特殊特征，比如表空间或存储参数。CREATE TABLE语句在创建唯一性约束的同时也给目标数据列建立了一个唯一的索引。

CREATE TABLE insured_autos (policy_id NUMBER CONSTRAINT pk_policies PRIMARY KEY, vin VARCHAR2(10), coverage_begin DATE, coverage_term NUMBER, CONSTRAIN unique_auto UNIQUE (policy_id,vin) USING INDEX TABLESPACE index STORAGE (INITIAL 1M NEXT 10M PCTINCREASE 0) );

　　用户可以禁用未以性约束，但他仍然存在，禁用唯一性约束使用ALTER TABLE 语句

ALTER TABLE insured_autos DISABLE CONSTRAIN unique_name;

　　删除唯一性约束，使用ALTER TABLE....DROP CONSTRAIN语句

ALTER TABLE insured_autos DROP CONSTRAIN unique_name;

　　注意用户不能删除在有外部键指向的表的唯一性约束。这种情况下用户必须首先禁用或删除外部键（foreign key)。

　　删除或禁用唯一性约束通常同时删除相关联的唯一索引，因而降低了数据库性能。经常删除或禁用唯一性约束有可能导致丢失索引带来的性能错误。要避免这样错误，可以采取下面的步骤：

　　1、在唯一性约束保护的数据列上创建非唯一性索引。

　　2、添加唯一性约束

　　主键(Primary Key)约束

　　表有唯一的主键约束。表的主键可以保护一个或多个列，主键约束可与NOT NULL约束共同作用于每一数据列。NOT NULL约束和唯一性约束的组合将保证主键唯一地标识每一行。像唯一性约束一样，主键由B-tree索引增强。

　　创建主键约束使用CREATE TABLE语句与表一起创建，如果表已经创建了，可以使用ALTER TABLE语句。

CREATE TABLE policies (policy_id NUMBER CONSTRAINT pk_policies PRIMARY KEY, holder_name VARCHAR2(40), gender VARCHAR2(1), marital_status VARCHAR2(1), date_of_birth DATE );

　　与唯一性约束一样，如果主键约束保护多个数据列，那么必须作为一个表约束创建。

CREATE TABLE insured_autos (policy_id NUMBER, vin VARCHAR2(40), coverage_begin DATE, coverage_term NUMBER, CONSTRAINT pk_insured_autos PRIMARY KEY (policy_id,vin) USING INDEX TABLESPACE index STORAGE (INITIAL 1M NEXT 10M PCTINCREASE 0) );

　　禁用或删除主键必须与ALTER TABLE 语句一起使用

ALTER TABLE policies DROP PRIMARY KEY;

　　或

ALTER TABLE policies DISABLE PRIMARY KEY;

　　外部键约束（Foreign key constraint)

　　外部键约束保护一个或多个数据列，保证每个数据行的数据包含一个或多个null值，或者在保护的数据列上同时拥有主键约束或唯一性约束。引用（主键或唯一性约束）约束可以保护同一个表，也可以保护不同的表。与主键和唯一性约束不同外部键不会隐式建立一个B-tree索引。在处理外部键时，我们常常使用术语父表（parent table)和子表（child table),父表表示被引用主键或唯一性约束的表，子表表示引用主键和唯一性约束的表。

　　创建外部键使用CREATE TABLE语句，如果表已经建立了，那么使用ALTER TABLE语句。

CREATE TABLE insured_autos (policy_id NUMBER CONSTRAINT policy_fk REFERENCE policies(policy_id ON DELETE CASCADE, vin VARCHAR2(40), coverage_begin DATE, coverage_term NUMBER, make VARCHAR2(30), model VARCHAR(30), year NUMBER, CONSTRAIN auto_fk FROEIGN KEY (make,model,year) REFERENCES automobiles (make,model,year) ON DELETE SET NULL );

　　ON DELETE子串告诉ORACLE如果父纪录（parent record)被删除后，子记录做什么。缺省情况下禁止在子记录还存在的情况下删除父纪录。

　　外部键和NULL值

　　在外部键约束保护的数据列中NULL值的处理可能产生不可预料的结果。ORACLE 使用ISO standar Match None规则增强外部键约束。这个规则规定如果任何外部键作用的数据列包含有一个NULL值，那么任何保留该键的数据列在父表中没有匹配值。

　　比如，在父表AUTOMOBILES中，主键作用于数据列MAKE，MODEL，YEAR上，用户使用的表INSURED_AUTOS有一个外部约束指向AOTOMOBILES，注意在INSURES_AUTOS中有一数据行的MODEL列为NULL值，这一行数据已经通过约束检查，即使MAKE列也没有显示在父表AUTOMOBILES中，如下表：

　　表1 AUTOMOBILES

MAKE	MODEL	YEAR
Ford	Taurus	2000
Toyota	Camry	1999

　　表2 INSURED_AUTOS

POLICY_ID	MAKE	MODEL	YEAR
576	Ford	Taurus	2000
577	Toyota	Camry	1999
578	Tucker	NULL	1949

　　延迟约束检验（Deferred Constraint Checking）

　　约束检验分两种情况，一种是在每一条语句结束后检验数据是否满足约束条件，这种检验称为立即约束检验（immediately checking）,另一种是在事务处理完成之后对数据进行检验称之为延迟约束检验。在缺省情况下Oracle约束检验是立即检验（immediately checking),如果不满足约束将先是一条错误信息，但用户可以通过SET CONSTRAINT语句选择延迟约束检验。语法如下：

SET CONSTRAINT constraint_name|ALL DEFEERRED|IMMEDIATE --;

序列（Sequences）

　　Oracle序列是一个连续的数字生成器。序列常用于人为的关键字，或给数据行排序否则数据行是无序的。像约束一样，序列只存在于数据字典中。序列号可以被设置为上升、下降，可以没有限制或重复使用直到一个限制值。创建序列使用SET SEQUENCE语句。

CREATE SEQUENCE [schema] sequence KEYWORD

　　KEYWORD包括下面的值：

KEYWORD	描述
START WITH	定义序列生成的第一个数字，缺省为1
INCREMENT BY	定义序列号是上升还是下降，对于一个降序的序列INCREMENT BY为负值
MINVALUE	定义序列可以生成的最小值，这是降序序列中的限制值。缺省情况下该值为NOMINVALUE,NOMINVALUE，对于升序为1，对于降序为-10E26.
MAXVALUE	序列能生成的最大数字。这是升序序列中的限制值，缺省的MAXVALUE为NOMAXVALUE,NOMAXVALUE，对于升序为10E26，对于降序为-1。
CYCLE	设置序列值在达到限制值以后可以重复
NOCYCLE	设置序列值在达到限制值以后不能重复，这是缺省设置。当试图产生MAXVALUE+1的值时，将会产生一个异常
CACHE	定义序列值占据的内存块的大小，缺省值为20
NOCACHE	在每次序列号产生时强制数据字典更新，保证在序列值之间没有间隔当创建序列时，START WITH值必须等于或大于MINVALUE。

　　删除序列使用DROP SEQUENCE语句

DROP SEQUENCE sequence_name

　　索引(INDEXES)

　　索引是一种可以提高查询性能的数据结构，在这一部分我们将讨论索引如何提高查询性能的。ORACLE提供了以下几种索引：

　　 B-Tree、哈希（hash)、位图（bitmap)等索引类型
　　 基于原始表的索引
　　 基于函数的索引
　　 域（Domain）索引

　　实际应用中主要是B-Tree索引和位图索引，所以我们将集中讨论这两种索引类型。

　　B-Tree索引

　　B-Tree索引是最普通的索引，缺省条件下建立的索引就是这种类型的索引。B-Tree索引可以是唯一或非唯一的，可以是单一的（基于一列）或连接的（多列）。B-Tree索引在检索高基数数据列（高基数数据列是指该列有很多不同的值）时提供了最好的性能。对于取出较小的数据B-Tree索引比全表检索提供了更有效的方法。但当检查的范围超过表的10%时就不能提高取回数据的性能。正如名字所暗示的那样，B-Tree索引是基于二元树的，由枝干块（branch block)和树叶块（leaf block)组成,枝干块包含了索引列（关键字）和另一索引的地址。树叶块包含了关键字和给表中每个匹配行的ROWID。

　　位图索引

　　位图索引主要用于决策支持系统或静态数据，不支持行级锁定。位图索引可以是简单的（单列）也可以是连接的（多列），但在实践中绝大多数是简单的。位图索引最好用于低到中群集（cardinality)列，在这些列上多位图索引可以与AND或OR操作符结合使用。位图索引使用位图作为键值，对于表中的每一数据行位图包含了TRUE（1）、FALSE（0）、或NULL值。位图索引的位图存放在B-Tree结构的页节点中。B-Tree结构使查找位图非常方便和快速。另外，位图以一种压缩格式存放，因此占用的磁盘空间比B-Tree索引要小得多。

　　同义词（Synonyms）

　　对另一个数据对象而言同义词是一个别名。public同义词是针对所有用户的，相对而言private同义词则只针对对象拥有者或被授予权限的账户。在本地数据库中同义词可以表示表、视图、序列、程序、函数或包等数据对象，也可以通过链接表示另一个数据库的对象。

　　创建同义词语法如下：

CREATE [PUBLIC] SYNONYM synonym_name FOR [schema.] object[@db_link];

　　
　　例：

CREATE PUBLIC SYNONYM policies FOR poladm.policies@prod; CREATE SYNONYM plan_table FOR system.plan_table;