Oracle数据库数据对象分析张达宇.docx
《Oracle数据库数据对象分析张达宇.docx》由会员分享，可在线阅读，更多相关《Oracle数据库数据对象分析张达宇.docx（21页珍藏版）》请在冰点文库上搜索。
Oracle数据库数据对象分析张达宇
Oracle数据库数据对象分析
日期：
2006-01-1808:
00点击：
Oracle数据库数据对象中最基本的是表和视图，其他还有约束、序列、函数、存储过程、包、触发器等。
对数据库的操作可以基本归结为对数据对象的操作,理解和掌握Oracle数据库对象是学习Oracle的捷径。
表和视图
Oracle中表是数据存储的基本结构。
ORACLE8引入了分区表和对象表，ORACLE8i引入了临时表，使表的功能更强大。
视图是一个或多个表中数据的逻辑表达式。
本文我们将讨论怎样创建和管理简单的表和视图。
管理表
表可以看作有行和列的电子数据表，表是关系数据库中一种拥有数据的结构。
用CREATETABLE语句建立表，在建立表的同时，必须定义表名，列，以及列的数据类型和大小。
例如：
CREATETABLEproducts
（PROD_IDNUMBER（4）,
PROD_NAMEVAECHAR2（20）,
STOCK_QTYNUMBER（5,3）
）;
这样我们就建立了一个名为products的表，关键词CREATETABLE后紧跟的表名，然后定义了三列，同时规定了列的数据类型和大小。
在创建表的同时你可以规定表的完整性约束，也可以规定列的完整性约束，在列上普通的约束是NOTNULL,关于约束的讨论我们在以后进行。
在建立或更改表时，可以给表一个缺省值。
缺省值是在增加行时，增加的数据行中某一项值为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个字符串。
表名应该以字母开始，可以在表名中包含数字、下划线、#、$等。
从其它表中建立表
可以使用查询从基于一个或多个表中建立表，表的列的数据类型和大小有查询结果决定。
建立这种形式的表的查询可以选择其他表中所有的列或者只选择部分列。
在CREATETABLE语句中使用关键字AS，例如:
SQL>CREATETABLEempASSELECT*FROMemployee
TABLECREATED
SQL>CREATETABLEYASSELECT*FROMXWHEREno=2
需要注意的是如果查询涉及LONG数据类型，那么CREATETABLE....ASSELECT....将不会工作。
更改表定义
在建立表后，有时候我们可能需要修改表，比如更改列的定义，更改缺省值，增加新列，删除列等等。
ORACLE使用ALTERTABLE语句来更改表的定义
1、增加列
语法：
ALTERTABLE[schema.]table_nameADDcolumn_definition
例：
ALTERTABLEordersADDorder_dateDATE;
TABLEALTER
对于已经存在的数据行，新列的值将是NULL.
2、更改列
语法：
ALTERTABLE[schema.]table_nameMODIFYcolumn_namenew_attributes;
例：
ALTERTABLEordersMODITY（quantitynumber（10,3）,statusvarchar2（15））;
这个例子中我们修改了表orders，将STATUS列的长度增加到15,将QUANTITY列减小到10,3;
修改列的规则如下：
.可以增加字符串数据类型的列的长度，数字数据类型列的精度。
.减少列的长度时，该列应该不包含任何值，所有数据行都为NULL.
.改变数据类型时，该列的值必须是NULL.
.对于十进制数字，可以增加或减少但不能降低他的精度。
3、删除数据列
优化ORACLE数据库，唯一的方法是删除列，重新建立数据库。
在ORACLE8i中有很多方法删除列，你可以删除未用数据列或者可以标示该列为未用数据列然后删除。
删除数据列的语法是：
ALTERTABLE[schema.]table_nameDROP{COLUMcolumn_names|（column_names）}[CASCADECONSTRAINS]
要注意的是在删除列时关于该列的索引和完整性约束也同时删除。
注意关键字CASCADECONSTRAINS，如果删除的列是多列约束的一部分，那么这个约束条件相对于其他列也同时删除。
如果用户担心在大型数据库中删除列要花太多时间，可以先将他们标记为未用数据列，标记未用数据列的语法如下：
ALTERTABLE[schema.]table_nameSETUNUSED{COLUMcolumn_names|（column_names）}[CASCADECONSTRAINS]
这个语句将一个或多个数据列标记为未用数据列，但并不删除数据列中的数据，也不释放占用的磁盘空间。
但是，未用数据列在视图和数据字典中并不显示，并且该数据列的名称将被删除，新的数据列可以使用这个名称。
基于该数据列的索引、约束，统计等都将被删除。
删除未用数据列的语句是：
ALTERTABLE[schema.]table_nameDROP{UNUSEDCOLUM|COLUMNCONTINUE}
删除表和更改表名
删除表非常简单，但它是一个不可逆转的行为。
语法：
DROPTABLE[schema.]table_name[CASCADECONSTRAINTS]
删除表后，表上的索引、触发器、权限、完整性约束也同时删除。
ORACLE不能删除视图，或其他程序单元，但oracle将标示他们无效。
如果删除的表涉及引用主键或唯一关键字的完整性约束时，那么DROPTABLE语句就必须包含CASCADECONSTRAINTS子串。
更改表名
RENAME命令用于给表和其他数据库对象改名。
ORACLE系统自动将基于旧表的完整性约束、索引、权限转移到新表中。
ORACLE同时使所有基于旧表的数据库对象，比如视图、程序、函数等，为不合法。
语法：
RENAMEold_nameTOnew_name;
例：
SQL>RENAMEordersTOpurchase_orders;
TABLERENAMED
截短表
TRUNCATE命令与DROP命令相似，但他不是删除整个数据表，所以索引、完整性约束、触发器、权限等都不会被删除。
缺省情况下将释放部分表和视图空间，如果用户不希望释放表空间，TRUNCATE语句中要包含REUSESTORAGE子串。
TRUNCATE命令语法如下：
TRUNCATE{TABLE|CLUSTER}[schema.]name{DROP|REUSESTORAGE}
例：
SQL>TRUNCATETABLEt1;
TABLEtruncate.
管理视图
视图是一个或多个表中的数据的简化描述，用户可以将视图看成一个存储查询（storedquery）或一个虚拟表（virtualtable）.查询仅仅存储在oracle数据字典中，实际的数据没有存放在任何其它地方，所以建立视图不用消耗其他的空间。
视图也可以隐藏复杂查询，比如多表查询，但用户只能看见视图。
视图可以有与他所基于表的列名不同的列名。
用户可以建立限制其他用户访问的视图。
建立视图
CREATEVIEW命令创建视图，定义视图的查询可以建立在一个或多个表，或其他视图上。
查询不能有FORUPDATE子串，在早期的ORACLE8i版本中不支持ORDERBY子串，现在的版本中CREATEVIEW可以拥有ORDERBY子串。
例：
SQL>CREATEVIEWTOP_EMPAS
SELECTempnoEMPLOYEE_ID,enameEMPLOYEE_NAME,salary
FROMemp
WHEREsalary>2000
用户可以在创建视图的同时更改列名，方法是在视图名后立即加上要命名的列名。
重新定义视图需要包含ORREPLACE子串。
SQL>CREATEVIEWTOP_EMP
（EMPLOYEE_ID，EMPLOYEE_NAME，SALARY）AS
SELECTempno,ename,salary
FROMemp
WHEREsalary>2000
如果在创建的视图包含错误在正常情况下，视图将不会被创建。
但如果你需要创建一个带错误的视图必须在CREATEVIEW语句中带上FORCE选项。
如：
CREATEFORCEVIEWORDER_STATUSAS
SELECT*FROMPURCHASE_ORDERS
WHERESTATUS=APPPOVE;
SQL>/
warning:
Viewcreatewithcompilationerrors
这样将创建了一个名为ORDER_STATUS的视图，但这样的视图的状态是不合法的，如果以后状态发生变化则可以重新编译，其状态也变成合法的。
从视图中获得数据
从视图中获得数据与从表中获得数据基本一样，用户可以在连接和子查询中使用视图，也可以使用SQL函数，以及所有SELECT语句的字串。
插入、更新、删除数据
用户在一定的限制条件下可以通过视图更新、插入、删除数据。
如果视图连接多个表，那么在一个时间里只能更新一个表。
所有的能被更新的列可以在数据字典USER_UPDATETABLE_COLUMNS中查到。
用户在CREATEVIEW中可以使用了WITH子串。
WITHREADONLY子串表示创建的视图是一个只读视图，不能进行更新、插入、删除操作。
WITHCHECKOPTION表示可以进行插入和更新操作，但应该满足WHERE子串的条件。
这个条件就是创建视图WHERE子句的条件，比如在上面的例子中用户创建了一个视图TOP_EMP,在这个视图中用户不能插入salary小于2000的数据行。
删除视图
删除视图使用DROPVIEW命令。
同时将视图定义从数据字典中删除，基于视图的权限也同时被删除，其他涉及到该视图的函数、视图、程序等都将被视为非法。
例：
DROPVIEWTOP_EMP;
完整性约束
完整性约束用于增强数据的完整性，Oracle提供了5种完整性约束：
Check
NOTNULL
Unique
Primary
Foreignkey
完整性约束是一种规则，不占用任何数据库空间。
完整性约束存在数据字典中，在执行SQL或PL/SQL期间使用。
用户可以指明约束是启用的还是禁用的，当约束启用时，他增强了数据的完整性，否则，则反之，但约束始终存在于数据字典中。
禁用约束，使用ALTER语句
ALTERTABLEtable_nameDISABLECONSTRAINTconstraint_name;
或
ALTERTABLEpoliciesDISABLECONSTRAINTchk_gender
如果要重新启用约束：
ALTERTABLEpoliciesENABLECONSTRAINTchk_gender
删除约束
ALTERTABLEtable_nameDROPCONSTRAINTconstraint_name
或
ALTERTABLEpoliciesDROPCONSTRAINTchk_gender;
Check约束
在数据列上Check约束需要一个特殊的布尔条件或者将数据列设置成TRUE，至少一个数据列的值是NULL，Check约束用于增强表中数据内容的简单的商业规则。
用户使用Check约束保证数据规则的一致性。
Check约束可以涉及该行同属Check约束的其他数据列但不能涉及其他行或其他表，或调用函数SYSDATE,UID,USER,USERENV。
如果用户的商业规则需要这类的数据检查，那么可以使用触发器。
Check约束不保护LOB数据类型的数据列和对象、嵌套表、VARRY、ref等。
单一数据列可以有多个Check约束保护，一个Check约束可以保护多个数据列。
创建表的Check约束使用CREATETABLE语句，更改表的约束使用ALTERTABLE语句。
语法：
CONSTRAINT[constraint_name]CHECK（condition）;
Check约束可以被创建或增加为一个表约束，当Check约束保护多个数据列时，必须使用表约束语法。
约束名是可选的并且如果这个名字不存在，那么oracle将产生一个以SYS_开始的唯一的名字。
例：
CREATETABLEpolicies
（policy_idNUMBER,
holder_nameVARCHAR2（40）,
genderVARCHAR2
（1）constraintchk_genderCHECK（genderin（M,F）,
marital_statusVARCHAR2
（1）,
date_of_birthDATE,
constraintchk_maritalCHECK（marital_statusin（S,M,D,W））
）;
NOTNULL约束
NOTNULL约束应用在单一的数据列上，并且他保护的数据列必须要有数据值。
缺省状况下，ORACLE允许任何列都可以有NULL值。
某些商业规则要求某数据列必须要有值，NOTNULL约束将确保该列的所有数据行都有值。
例：
CREATETABLEpolicies
（policy_idNUMBER,
holder_nameVARCHAR2（40）NOTNULL,
genderVARCHAR2
（1）,
marital_statusVARCHAR2
（1）,
date_of_birthDATENOTNULL
）;
对于NOTNULL的ALTERTABLE语句与其他约束稍微有点不同。
ALTERTABLEpoliciesMODIFYholder_nameNOTNULL
唯一性约束（Uniqueconstraint）
唯一性约束可以保护表中多个数据列，保证在保护的数据列中任何两行的数据都不相同。
唯一性约束与表一起创建，在唯一性约束创建后，可以使用ALTERTABLE语句修改。
语法：
column_namedata_typeCONSTRAINTconstraint_nameUNIQUE
如果唯一性约束保护多个数据列，那么唯一性约束要作为表约束增加。
语法如下：
CONSTRAINTconstraint_name（column）UNIQUEUSINGINDEXTABLESPACE（tablespace_name）STORAGE（storedclause）
唯一性约束由一个B-tree索引增强，所以可以在USING子串中为索引使用特殊特征，比如表空间或存储参数。
CREATETABLE语句在创建唯一性约束的同时也给目标数据列建立了一个唯一的索引。
CREATETABLEinsured_autos
（policy_idNUMBERCONSTRAINTpk_policiesPRIMARYKEY,
vinVARCHAR2（10）,
coverage_beginDATE,
coverage_termNUMBER,
CONSTRAINunique_autoUNIQUE（policy_id,vin）USINGINDEXTABLESPACEindexSTORAGE（INITIAL1MNEXT10MPCTINCREASE0）
）;
用户可以禁用未以性约束，但他仍然存在，禁用唯一性约束使用ALTERTABLE语句
ALTERTABLEinsured_autosDISABLECONSTRAINunique_name;
删除唯一性约束，使用ALTERTABLE....DROPCONSTRAIN语句
ALTERTABLEinsured_autosDROPCONSTRAINunique_name;
注意用户不能删除在有外部键指向的表的唯一性约束。
这种情况下用户必须首先禁用或删除外部键（foreignkey）。
删除或禁用唯一性约束通常同时删除相关联的唯一索引，因而降低了数据库性能。
经常删除或禁用唯一性约束有可能导致丢失索引带来的性能错误。
要避免这样错误，可以采取下面的步骤：
1、在唯一性约束保护的数据列上创建非唯一性索引。
2、添加唯一性约束
主键（PrimaryKey）约束
表有唯一的主键约束。
表的主键可以保护一个或多个列，主键约束可与NOTNULL约束共同作用于每一数据列。
NOTNULL约束和唯一性约束的组合将保证主键唯一地标识每一行。
像唯一性约束一样，主键由B-tree索引增强。
创建主键约束使用CREATETABLE语句与表一起创建，如果表已经创建了，可以使用ALTERTABLE语句。
CREATETABLEpolicies
（policy_idNUMBERCONSTRAINTpk_policiesPRIMARYKEY,
holder_nameVARCHAR2（40）,
genderVARCHAR2
（1）,
marital_statusVARCHAR2
（1）,
date_of_birthDATE
）;
与唯一性约束一样，如果主键约束保护多个数据列，那么必须作为一个表约束创建。
CREATETABLEinsured_autos
（policy_idNUMBER,
vinVARCHAR2（40）,
coverage_beginDATE,
coverage_termNUMBER,
CONSTRAINTpk_insured_autosPRIMARYKEY（policy_id,vin）
USINGINDEXTABLESPACEindex
STORAGE（INITIAL1MNEXT10MPCTINCREASE0）
）;
禁用或删除主键必须与ALTERTABLE语句一起使用
ALTERTABLEpoliciesDROPPRIMARYKEY;
或
ALTERTABLEpoliciesDISABLEPRIMARYKEY;
外部键约束（Foreignkeyconstraint）
外部键约束保护一个或多个数据列，保证每个数据行的数据包含一个或多个null值，或者在保护的数据列上同时拥有主键约束或唯一性约束。
引用（主键或唯一性约束）约束可以保护同一个表，也可以保护不同的表。
与主键和唯一性约束不同外部键不会隐式建立一个B-tree索引。
在处理外部键时，我们常常使用术语父表（parenttable）和子表（childtable）,父表表示被引用主键或唯一性约束的表，子表表示引用主键和唯一性约束的表。
创建外部键使用CREATETABLE语句，如果表已经建立了，那么使用ALTERTABLE语句。
CREATETABLEinsured_autos
（policy_idNUMBERCONSTRAINTpolicy_fk
REFERENCEpolicies（policy_id
ONDELETECASCADE,
vinVARCHAR2（40）,
coverage_beginDATE,
coverage_termNUMBER,
makeVARCHAR2（30）,
modelVARCHAR（30）,
yearNUMBER,
CONSTRAINauto_fkFROEIGNKEY（make,model,year）
REFERENCESautomobiles（make,model,year）
ONDELETESETNULL
）;
ONDELETE子串告诉ORACLE如果父纪录（parentrecord）被删除后，子记录做什么。
缺省情况下禁止在子记录还存在的情况下删除父纪录。
外部键和NULL值
在外部键约束保护的数据列中NULL值的处理可能产生不可预料的结果。
ORACLE使用ISOstandarMatchNone规则增强外部键约束。
这个规则规定如果任何外部键作用的数据列包含有一个NULL值，那么任何保留该键的数据列在父表中没有匹配值。
比如，在父表AUTOMOBILES中，主键作用于数据列MAKE，MODEL，YEAR上，用户使用的表INSURED_AUTOS有一个外部约束指向AOTOMOBILES，注意在INSURES_AUTOS中有一数据行的MODEL列为NULL值，这一行数据已经通过约束检查，即使MAKE列也没有显示在父表AUTOMOBILES中，如下表：
表1AUTOMOBILES
MAKEMODELYEARFordTaurus2000ToyotaCamry1999
表2INSURED_AUTOS
POLICY_IDMAKEMODELYEAR576FordTaurus2000577ToyotaCamry1999578TuckerNULL1949
延迟约束检验（DeferredConstraintChecking）
约束检验分两种情况，一种是在每一条语句结束后检验数据是否满足约束条件，这种检验称为立即约束检验（immediatelychecking）,另一种是在事务处理完成之后对数据进行检验称之为延迟约束检验。
在缺省情况下Oracle约束检验是立
升级会员 