表的定义,修改,删除操作
ORACLE数据库系统中,表是数据库的基本对象,与桌面数据库中的文件类似,我们可以把所有的基本实体都看成为表,不管应用中的表有多复杂,都可以使用(拆成)一个或多个表来表示。用以存放实体的数据。下面针对建表所需要的知识作简单的介绍。
§3.1 建立表结构
建立表结构是每个应用系统都必须进行的工作。由于建立表结构是一项统一规划和统一设计的工作。应该是由总设计师根据用户的具体应用需要来定。表的设计是否合理关系到应用系统将来的成败与性能问题。因此,任何担当总设计师角色的人都不要轻视这项工作。
§3.1.1 建立表结构命令
由于创建表的命令非常长,这里仅给出一些主要的部分,详细的请参考《ORACLE8i SQL REFERENCE》 。
CREATE TABLE 命令简要语法如下:
CREATE TABLE [USER.] table_name
( { COLUMN1 DATATYPE
[DEFAULT EXPN] [COLUMN_CONSTRAINT] |TABLE_CONSTRAINT }
[, { COLUMN1 DATATYPE
[DEFAULT EXPN] [COLUMN_CONSTRAINT] |
TABLE_CONSTRAINT }] ... )
[CLUSTER CLUSTER (COLUMN1 [,COLUMN2] ...) ]
[PCTFREE N]
[PCTUSED N]
[INITRANS N]
[MAXTRANS N]
[STORAGE N]
[TABLESPACE TABLESPACE]
[ ENABLE | DISABLE]
[ AS QUERY]
其中:
schema    包括基表的模式(缺省:当前用户的帐号)
table_name表名
column    列名(字段名),ORACLE7最多254列,ORACLE8可达1000个列。
datatype  列数据类型
DEFAULT             当前列的缺省值(常数)
Column constraint   列约束
Table_constraint    表约束
PCTFREE   用于更新(update)的空间百分比(1-99
           0表示在插入时完全填满数据块,缺省为10
PCTUSED   为表的每个数据块保留的可用空间的最小百分比取值1-99,缺省为40
PCTFREEPCTUSED的组合决定了将插入的数据放入已存在的数据块还是放入一个新的块中。
INITRANS 指定一个在每一个数据块中分配的事务入口的初始数1-255
                 缺省为1,每一个更新块的事务都需要在块中有一个事务入口
                 (大小依OS),一般不需要指此参。
MAXTRANS   指定用于更新分配给表的数据块的并发事务的最大数,1-
                        255,用户一般不应改此参。
TABLESPACE   表空间。如果缺省则表建在用户缺省的表空间(如果建立用户不指定表空间
             则该用户的缺省表空间为system)。
STORAGE         存储分配参数
                 INITIAL      integer   初始大小
                 NEXT         integer   下一次的大小
                 MINEXTENTS   integer   最小分配次数
                 MAXEXTENTS   integer   最大分配次数
                 PCTINCREASE integer   增长百分比(>=0
ENABLE        激活完整性约束
DISABLE       取消完整性约束
As subquery      建表中查出数据给新表,此语句如果使用,则表的数据类型不需指定,
                   而是继承原表的类型。
FREELIST   GROUP  在并行服务器中指定表或分类、索引组的列表数目。
FREEUST     在并行服务器中指定表、簇、索引的列表数。
提示1
一般情况下,如果表含有long字段,这样势必需大量的空间,系统会在每次插入新记录时,经常分配空间给表,不久就会出现:
"ORA-01547:Failed   to   allocate   extent   of   size   xxxxx   in 
tablespace    'xxxx' "
     此种情况如果表空间还剩较多的连续空间的话。则可能是该表分配的空间次数已达最大值。为了对该表能插入新数据,需对该表的存储参数作修改,比如:
     SQL>alter   table   xxx   storage(MAXEXTENTS    999 )
提示2:建议不要对表结构或索引使用 pctincrease大于0的参数以避免将来在运行中产生空间超支问题.
提示3:建立表结构最重要的部分是存储参数(STORAGE)的说明。设置者要特别重视存储参数的估计,设置合理的大小。详细见〈Oracle8i/9i 初级数据库管理〉
§3.1.2 建立表结构例子
1:在SCOTT模式下建立表emp,并指定表空间和存储参数:
Create   table   scott.emp
(
         Empno   number(5)   primary   key,
         Ename   varchar2(15)   not   null,
         Job     varchar2(10),
         Mgr     number(5),
         Hiredate   date   default   sysdate,
         Sal     number(7,2)   CHECK(sal>100),
         Comm    number(3)   default 0.0 ,
         Dept    number   constraint 
         dept_fkey     References   scott.dept
)
Tablespace   users
PCTFREE   10
PCTUSED   70
STORAGE
      (
      INITIAL   50K
      NEXT       50k
      MAXEXTENTS   10
      )
2:在建立表过程中对有限制的列使用NOT NULL:
CREATE TABLE CHECKUP_HISTORY
(CHECKUP_NO NUMBER(10,0) NOT NULL,
ID_NO NUMBER(10,0),
CHECKUP_TYPE VARCHAR2(30),
CHECKUP_DATE DATE,
DOCTOR_NAME VARCHAR2(50));
本例除了要求CHECKUP_NO非空外,其它无任何限制.
3:在建立表时指定列CHECKUP_TYPE 为外部列:
CREATE TABLE SEAPARK.CHECKUP_HISTORY
(
CHECKUP_NO NUMBER(10) NOT NULL,
ID_NO NUMBER(10,0),
CHECKUP_TYPE VARCHAR2(30),
CHECKUP_DATE DATE,
DOCTOR_NAME VARCHAR2(50),
FOREIGN KEY (CHECKUP_TYPE) REFERENCES
SEAPARK.CHECKUP (CHECKUP_TYPE),
PRIMARY KEY (CHECKUP_NO)
)
PCTFREE 20
PCTUSED 60
INITRANS 2
MAXTRANS 255
STORAGE ( INITIAL 1250K
NEXT 2K
MINEXTENTS 1
MAXEXTENTS 121
Pctincrease 0)
TABLESPACE user_data;
例子指定了所有者,主键,外部键,表空间及存储参数等,主键和外部键在后面章节介绍。
§3.1.3 建立临时表结构
Oracle现在可以使用 CREATE GLOBAL TEMPORARY TABLE命令来实现建立临时表结构。这样的表它的数据只在用户会话期间存在,当会话完成后就自动清除。看下面例子:
SQL> create global temporary table myemp as select * from emp;
表已创建。
SQL> desc myemp
 名称                                      空?     类型
 ----------------------------------------- -------- --------------
 ENAME                                              VARCHAR2(20)
 SAL                                                NUMBER(9,2)
 DEPTNO                                             NUMBER(4)
 TEL                                                VARCHAR2(20)
SQL> select * from myemp;
未选定行
SQL> insert into myemp values('赵元杰',32456.99,10,'12');
已创建 行。
SQL> select * from myemp;
ENAME                       SAL     DEPTNO TEL
-------------------- ---------- ---------- ------------------
赵元杰                 32456.99         10 12
SQL> connect sys/sys
已连接。
SQL> connect zhao/zhao
已连接。
SQL> l
 1* select * from myemp
SQL> /
未选定行
从上面可看出当连接到SYS在连接回来后数据就不存在了。对于临时表,可以用DROP TABLE来删除其结构。如:
SQL> drop table myemp;
表已丢弃。
§3.3修改表结构
修改表结构是对已经创建完成(实际是存放在数据库字典里)的表的结构进行修改。不同的Oracle版本允许对表的修改也不一样。新版的Oracle8i可以对表中的列进行删除。
§3.3.1 修改表结构命令
修改表结构的命令由ALTER TABLE来完成。该命令的参数较多,下面仅给出一些基本的部分。详细请参考《ORACLE8i SQL REFERENCE》 。
ALTER   TABLE   [user.]   table
[ADD   ({colum_element|table_constraint}
        [,{column_element|table_constraint}]...)]
[MODIFY(column_element[,column_element]...)]
[DROP   CONSTRAINT   constraint]...
[PCTFREE   integer][PCTUSED   integer]
[INITRANS   integer][MAXTRANS   integer]
[STORAGE   storage]
[BACKUP]
ALTER TABLE可以作的操作有:
l      增加一个列(字段)宽度;
l      减少一个列(字段)宽度(该列必须无数据)
l      增加一个列(字段);
l      修改列的定义 ;
l      或一个限制;(如数据类型,NOT NULL);仅当某列的值为空时才能修改其类型;
l      去掉限制;
l      修改存储分配;
l      记录表已作过BACKUP
l      删除已存在的列(Oracle8i及以后版本)
l      重新定位和组织表(Oracle8i及以后版本)
l      将表标识为不可用(Oracle8i及以后版本)
§3.3.2  修改表结构例子
1:对已经存在的表增加一新的列:
SQL>alter   table   dept   add   ( headcount   number(3) );
2:对表的列修改其大小:
SQL>alter   table   dept   modify( Dname   char(20) );
如果被修改的列没有空(已有数据),则被提示:
ORA-01439: Column to be modified must be empty to change
datatype
ORA-01441: Column to be modified must be empty to decrease
column length
3:复制一个表:
CREATE TABLE HOLD_TANK AS SELECT TANK_NO, CHIEF_CARETAKER_NAME
FROM TANK;
4:参照某个已存在的表建立一个表结构(不需要数据)
create table emp2 as select * from emp where rownum<1;
5:修改已存在表存储参数:
Alter table emp2 storage( next 256k pctincrease 0 );
6:删除表中的列:
这是Oracle8i的新功能,它的基本语法为:
ALTER TABLE . . . . . . DROP COLUMN [ CASCADE CONSTRAINTS ];
:
Alter table emp   drop column comm ;
7:重新定位和组织表:
这是Oracle8i的新功能,可以实现:
l      将未分区的 表从一个表空间移到另一个表空间;
l      重新组织一个未分区表的存储。
它的基本语法为:
ALTER TABLE . . . . . . MOVE TABLESPACE ;
:
Alter table emp   move tablespace users;
8:将表标识为不可用:
这是Oracle8i的新功能,可以实现对空间的收回等。
基本语法为:
ALTER TABLE . . . . . . SET UNUSED COLUMN;
:
Alter table emp   set UNUSED COLUMN xyz;
提示:虽然Oracle允许用户对表的结构进行修改。但建议你在工作中不要采用方式。因为表结构被多次修改会影响应用系统的性能。
§3.3.3 删除表结构
Oracle提供DROP TABLE命令可以实现删除表数据和结构。提醒初学者,不要轻易使用DROP TABLE命令。DROP TABLE 命令语法:
DROP TABLE [user.]table_name[CASCADE CONSTRAINTS]
CASCADE CONSTRAINTS表示所有指向本表的主键,外部键被删掉。当删除一个表时,下面的对象也随之被删掉。
l      表的索引;
l      指向本表的外部键;
l      本表的触发器;
l      本表中的分区;
l      本表的快照;
l      本表的角色和用户权限;
l      加在本表的所有限制。
提示:如果你在定义表结构时,采用了主键、外部键来定义了一序列表。则在删除表结构时要小心。不要轻易用CASCADE子句。
§3.3.4 使用CHECK作限制约束
Oracle提供了一个很有用的子句CHECK,它可以实现对数据的自动检查。它的用法是在创建表结构时使用。如: