ORACLE数据库开发基础第三章表及索引文档格式.docx
《ORACLE数据库开发基础第三章表及索引文档格式.docx》由会员分享，可在线阅读，更多相关《ORACLE数据库开发基础第三章表及索引文档格式.docx（40页珍藏版）》请在冰豆网上搜索。
table_name表名
column列名（字段名），ORACLE7最多254列，ORACLE8可达1000个列。
87
datatype列数据类型
DEFAULT当前列的缺省值（常数）
Columnconstraint列约束
Table_constraint表约束
PCTFREE用于更新（update）的空间百分比（1-99）
0表示在插入时完全填满数据块，缺省为10
PCTUSED为表的每个数据块保留的可用空间的最小百分比.取值1-99，缺省为40。
PCTFREE和PCTUSED的组合决定了将插入的数据放入已存在的数据块还是放入一个新的块中。
INITRANS指定一个在每一个数据块中分配的事务入口的初始数1-255，
缺省为1，每一个更新块的事务都需要在块中有一个事务入口
（大小依OS），一般不需要指此参。
MAXTRANS指定用于更新分配给表的数据块的并发事务的最大数，1-
255，用户一般不应改此参。
TABLESPACE表空间。
如果缺省则表建在用户缺省的表空间（如果建立用户不指定表空间
则该用户的缺省表空间为system）。
STORAGE存储分配参数
INITIALinteger初始大小
NEXTinteger下一次的大小
MINEXTENTSinteger最小分配次数
MAXEXTENTSinteger最大分配次数
PCTINCREASEinteger增长百分比（>
=0）
ENABLE激活完整性约束
DISABLE取消完整性约束
Assubquery建表中查出数据给新表，此语句如果使用，则表的数据类型不需指定，
而是继承原表的类型。
FREELISTGROUP在并行服务器中指定表或分类、索引组的列表数目。
FREEUST在并行服务器中指定表、簇、索引的列表数。
提示1：
一般情况下，如果表含有long字段，这样势必需大量的空间，系统会在每次插入新记录时，
经常分配空间给表，不久就会出现：
"
ORA-01547:
Failedtoallocateextentofsizexxxxxin
tablespace'
xxxx'
"
此种情况如果表空间还剩较多的连续空间的话。
则可能是该表分配的空间次数已达最
大值。
为了对该表能插入新数据，需对该表的存储参数作修改，比如：
SQL>
altertablexxxstorage（MAXEXTENTS999）;
提示2:
建议不要对表结构或索引使用pctincrease大于0的参数以避免将来在运行中产生
空间超支问题.
提示3:
建立表结构最重要的部分是存储参数（STORAGE）的说明。
设置者要特别重视存储参
数的估计，设置合理的大小。
详细见〈Oracle8i/9i初级数据库管理〉
88
3.1.2建立表结构例子
例1：
在SCOTT模式下建立表emp,并指定表空间和存储参数：
Createtablescott.emp
（
Empnonumber（5）primarykey,
Enamevarchar2（15）notnull,
Jobvarchar2（10）,
Mgrnumber（5）,
Hiredatedatedefaultsysdate,
Salnumber（7,2）CHECK（sal>
100）,
Commnumber（3）default0.0,
Deptnumberconstraint
dept_fkeyReferencesscott.dept
）
Tablespaceusers
PCTFREE10
PCTUSED70
STORAGE
INITIAL50K
NEXT50k
MAXEXTENTS10
）;
例2：
在建立表过程中对有限制的列使用NOTNULL:
CREATETABLECHECKUP_HISTORY
（CHECKUP_NONUMBER（10,0）NOTNULL,
ID_NONUMBER（10,0）,
CHECKUP_TYPEVARCHAR2（30）,
CHECKUP_DATEDATE,
DOCTOR_NAMEVARCHAR2（50））;
本例除了要求CHECKUP_NO非空外，其它无任何限制.
例3：
在建立表时指定列CHECKUP_TYPE为外部列：
CREATETABLESEAPARK.CHECKUP_HISTORY
CHECKUP_NONUMBER（10）NOTNULL,
89
DOCTOR_NAMEVARCHAR2（50）,
FOREIGNKEY（CHECKUP_TYPE）REFERENCES
SEAPARK.CHECKUP（CHECKUP_TYPE）,
PRIMARYKEY（CHECKUP_NO）
PCTFREE20
PCTUSED60
INITRANS2
MAXTRANS255
STORAGE（INITIAL1250K
NEXT2K
MINEXTENTS1
MAXEXTENTS121
Pctincrease0）
TABLESPACEuser_data;
例子指定了所有者，主键，外部键,表空间及存储参数等，主键和外部键在后面章节介绍。
3.1.3建立临时表结构
Oracle现在可以使用CREATEGLOBALTEMPORARYTABLE命令来实现建立临时表结构。
这样的
表它的数据只在用户会话期间存在，当会话完成后就自动清除。
看下面例子：
createglobaltemporarytablemyempasselect*fromemp;
表已创建。
descmyemp
名称空?
类型
---------------------------------------------------------------
ENAMEVARCHAR2（20）
SALNUMBER（9,2）
DEPTNONUMBER（4）
TELVARCHAR2（20）
select*frommyemp;
未选定行
insertintomyempvalues（'
赵元杰'
32456.99,10,'
12'
）;
90
已创建1行。
ENAMESALDEPTNOTEL
----------------------------------------------------------
赵元杰32456.991012
connectsys/sys
已连接。
connectzhao/zhao
l
1*select*frommyemp
/
从上面可看出当连接到SYS在连接回来后数据就不存在了。
对于临时表，可以用DROPTABLE
来删除其结构。
如：
droptablemyemp;
表已丢弃。
3.3修改表结构
修改表结构是对已经创建完成（实际是存放在数据库字典里）的表的结构进行修改。
不同的
Oracle版本允许对表的修改也不一样。
新版的Oracle8i可以对表中的列进行删除。
3.3.1修改表结构命令
修改表结构的命令由ALTERTABLE来完成。
该命令的参数较多，下面仅给出一些基本的部分。
详细请参考《ORACLE8iSQLREFERENCE》。
ALTERTABLE[user.]table
[ADD（{colum_element|table_constraint}
[,{column_element|table_constraint}]...）]
[MODIFY（column_element[,column_element]...）]
[DROPCONSTRAINTconstraint]...
91
[PCTFREEinteger][PCTUSEDinteger]
[INITRANSinteger][MAXTRANSinteger]
[STORAGEstorage]
[BACKUP]
ALTERTABLE可以作的操作有：
增加一个列（字段）宽度;
减少一个列（字段）宽度（该列必须无数据）;
增加一个列（字段）;
修改列的定义;
或一个限制;
（如数据类型，NOTNULL）;
仅当某列的值为空时才能修改其类型;
去掉限制;
修改存储分配;
记录表已作过BACKUP;
删除已存在的列（仅Oracle8i及以后版本）;
重新定位和组织表（仅Oracle8i及以后版本）;
将表标识为不可用（仅Oracle8i及以后版本）。
3.3.2修改表结构例子
例1：
对已经存在的表增加一新的列：
altertabledeptadd（headcountnumber（3））;
对表的列修改其大小：
altertabledeptmodify（Dnamechar（20））;
如果被修改的列没有空（已有数据），则被提示：
ORA-01439:
Columntobemodifiedmustbeemptytochange
datatype
ORA-01441:
Columntobemodifiedmustbeemptytodecrease
columnlength
复制一个表：
CREATETABLEHOLD_TANKASSELECTTANK_NO,CHIEF_CARETAKER_NAME
FROMTANK;
92
例4：
参照某个已存在的表建立一个表结构（不需要数据）
createtableemp2asselect*fromempwhererownum<
1;
例5：
修改已存在表存储参数：
Altertableemp2storage（next256kpctincrease0）;
例6：
删除表中的列：
这是Oracle8i的新功能，它的基本语法为：
ALTERTABLE......DROPCOLUMN[CASCADECONSTRAINTS];
如:
Altertableempdropcolumncomm;
例7：
重新定位和组织表：
这是Oracle8i的新功能，可以实现：
将未分区的表从一个表空间移到另一个表空间;
重新组织一个未分区表的存储。
它的基本语法为：
ALTERTABLE......MOVETABLESPACE;
Altertableempmovetablespaceusers;
例8：
将表标识为不可用：
这是Oracle8i的新功能，可以实现对空间的收回等。
基本语法为：
ALTERTABLE......SETUNUSEDCOLUMN;
AltertableempsetUNUSEDCOLUMNxyz;
提示：
虽然Oracle允许用户对表的结构进行修改。
但建议你在工作中不要采用方式。
因为
表结构被多次修改会影响应用系统的性能。
93
3.3.3删除表结构
Oracle提供DROPTABLE命令可以实现删除表数据和结构。
提醒初学者，不要轻易使用DROP
TABLE命令。
DROPTABLE命令语法：
DROPTABLE[user.]table_name[CASCADECONSTRAINTS]
CASCADECONSTRAINTS表示所有指向本表的主键，外部键被删掉。
当删除一个表时，下面的
对象也随之被删掉。
表的索引;
指向本表的外部键;
本表的触发器;
本表中的分区;
本表的快照;
本表的角色和用户权限;
加在本表的所有限制。
如果你在定义表结构时，采用了主键、外部键来定义了一序列表。
则在删除表结构时
要小心。
不要轻易用CASCADE子句。
3.3.4使用CHECK作限制约束
Oracle提供了一个很有用的子句CHECK，它可以实现对数据的自动检查。
它的用法是在创建
表结构时使用。
Createtableworker
（empnonumber（4）primarykey,
namevarchar2（10）,
agenumber
（2）CHECK（agebetween18and65）,
/*agenumber
（2）CHECK（age>
=18andage<
=65）*/
lodgingchar（15）ReferencesLODGING（lodging）
Createtableemp3
（empnonumber（4）constraintabcprimarykey,
enamevarchar2（10）,
jobvarchar2（10）,
sexchar
（2）check（sex=‘男’orsex=’女’）,
mgrnumber（4）,
94
hiredatedate,
salnumber（7,2）,/*工资*/
commnumber（7,2）,/*奖金*/
deptnonumber
（2）,
CHECK（sal+comm>
0andsal+comm<
=5000）
建议：
在设计数据库表结构时，建议你分析用户的数据的取值范围，从而将那些取值范围一
定的字段用CHECK进行描述。
以保证以后数据的正确性。
3.3.5使用UNRECOVERABLE创建表
对于特殊的需要，可以考虑将表创建成为不需恢复（UNRECOVERABLE）的表。
如复制一
个已存在的表就可以采用这种方法以减少系统的开销。
例：
参考emp表创建一个新的emp_new表：
createtablenew_empasselect*fromempUNRECOVERABLE;
或
CREATETABLEnew_empASselect*fromempNOLOGGING;
注：
虽然上面提到UNRECOVERABLE，但是Oracle推荐你使用NOLOGGING或
LOGGING;
3.3.6将表移动到新的数据段或新的表空间
最新的Oracle8i版本可以用Altertable…MOVE语句将表移动到一个新的段或新表空
间上，这样可以实现对不合理存储参数进行修改，包括用一般的ALTERTABLE不能修改的
参数。
通过移动来实现存储参数的修改：
AltertableempMOVE
STORAGE（INITIAL1mnext512kminextents1maxextents999pctincrease0）;
例2：
将那些使用system表空间的对象移动到合适的表空间中：
1）移动前表所使用的表空间情况：
95
selecttablespace_name,table_name,initial_extentfromuser_tables;
TABLESPACE_NTABLE_NAMEINITIAL_EXTENT
-------------------------------------------------------------------------
SYSTEMABC65536
SYSTEMBONUS65536
SYSTEMDEPT65536
SYSTEMEMP65536
SYSTEMEMP265536
SYSTEMEMP365536
SYSTEMEMP465536
USERSPAY_LST_DET1048576
SYSTEMPLAN_TABLE65536
SYSTEMSALGRADE65536
USERSUNIT_INF1048576
11rowsselected.
2）用Altertable...MOVE语句对表进行移动，下面例子对表进行移动并重新指定存储参
数。
altertableempmovetablespaceuser_data
2storage（initial128knext128kminextents1pctincrease0）;
Tablealtered.
altertabledeptmovetablespaceuser_data
altertableBONUSmovetablespaceuser_data
3）移动后的表及表空间的情况：
---------------------------------------------------------------------------
USER_DATABONUS131072
96
USER_DATADEPT131072
USER_DATAEMP131072
3.3.7手工分配表的存储空间
使用ALTERTABLE加ALLOCATEEXTENT选项来实现分配一个指定的空间。
ALTERTABLEemp
ALLOCATEEXTENT（SIZE5KINSTANCE4）;
3.3.8标记不使用的列和删除不使用的列
前面介绍过，新版的Oracle8I可以删除某个列。
从LONG_TAB表中将LONG_PICS列删除掉：
ALTERTABLELONG_TABDROPCOLUMNLONG_PICS;
可以使用ALTERTABLE...SETUNUSED语句实现将表中的列设置为不用的状态以达
到快速处理的目的。
其结果是：
1）在显示结果时看不到该列;
2）不删除该列的数据（但可以将该列删掉）;
select*fromemp;
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
----------------------------------------------------------------------
升级会员 