5.4 创建简单的表
可用多种方法将表保存在数据库中，最简单的是堆表。堆表包含了随机排列的可变长度的行。输入行的顺序和排列行的顺序之间可能有某种关联，但这只是巧合而已。更高级的表结构，如下所示，可能对行强制顺序和分组，或者要求随机分布：
● 索引组织的表 按索引键的顺序存储行。
● 索引群集 可以反规范化父子关系表，这样来自不同表的相关行就可以存储在一起。
● 哈希群集 要求随机分布行，这样可以打破依据条目序列的排序。
● 分区表 以单独的物理结构(分区)存储行，依据列的值分配行。
使用更高级的表结构对SQL没有影响。对用这些选项定义的表执行的每个SQL语句都会返回相同的结果，就像表是标准的堆表一样，因此使用这些特性不会影响代码。但当它们的使用对于编程人员而言是透明时，它们的确大大提高了性能。
5.4.1 使用列规范创建表
要创建标准的堆表，可以使用下面的语法：
CREATE TABLE [schema.]table [ORGANIZATION HEAP]
(column datatype [DEFAULT expression]
[, column datatype [DEFAULT expression]...);
至少指定表名(如果没有指定其他人的，就会在自己的模式中创建它)和具有某个数据类型的一列。很少有开发人员指定ORGANIZATION HEAP，因为这是默认的，是行业标准的SQL。列定义中的DEFAULT关键字要求提供一个表达式，如果INSERT语句没有提供值，那么在插入行时，这个表达式会生成列的值。
分析下面的语句：
create table scott.emp
(empno number(4),
ename varchar2(10),
hiredate date default trunc(sysdate),
sal number(7,2),
comm number(7,2) default 0.03);
这会在SCOTT模式中创建一个名为EMP的表。用户SCOTT本身必须发出语句(这种情况下，就没必要命名模式)，或者另一个用户可以发出它(如果允许他们在SCOTT模式中创建表)。下面逐列进行考察：
● EMPNO可以是4位数长，没有小数部分。如果在INSERT语句中包含小数，就会将它们舍入(向上舍入或者向下舍入)为最接近的整数。
● ENAME可以最多存储10个字符。
● HIREDATE可以接受任何日期，可能包含时间，但如果没有提供值，就会输入今天午夜的日期。
● SAL表示员工的薪水，可以接受数值，最多可达到7位。小数点左边最多可以有5位数字。如果超过两位的数字出现在小数点右边，就会舍入它们。
● COMM(表示佣金率)的默认值是0.03，如果INSERT语句没有包含这一列的值，就会输入它。
创建表之后，下面这些语句插入行和选择结果：
insert into scott.emp (empno, ename, sal)
values (1000, 'John', 1000.789);
1 row created.
select *
from scott.emp;
EMPNO ENAME HIREDATE SAL COMM
---------- ---------- --------- ---------- ----------
1000 John 19-NOV-13 1000.79 .03
注意，INSERT语句中没有提到的列值由DEFAULT子句生成。如果表定义中没有定义这些子句，列就为NULL。还要注意对SAL值的舍入。
提示：
DEFAULT子句可能很有用，但它功能有限。不能使用子查询来生成默认值：只能指定字面值或者函数或者序列。
5.4.2 使用子查询创建表
除了创建一个空表，然后插入行之外(如前所述)，还可以使用子查询通过其他表来创建表。这种方法可以创建表定义，并使用一条语句给表填充行。所有查询都可用作表结构和行的来源。其语法如下所示：
CREATE TABLE [schema.]table AS subquery;
所有查询都返回行的二维集合；这个结果存储为新的表。使用子查询创建表的简单示例是：
create table employees_copy as
select *
from employees;
这条语句会创建表EMPLOYEES_COPY，它完全是EMPLOYEES表的副本，它们的定义和包含的行完全相同。列上的所有NOT NULL(非空)和CHECK(检查)约束也将应用于新的表，但所有PRIMARY KEY(主键)、UNIQUE(唯一值)或者FOREIGN KEY(外键)约束则不适用(稍后将讨论约束的概念)。这是因为这三类约束需要索引，这些索引可能不是必需的。
下面这个示例更复杂：
create table emp_dept as
select last_name ename, department_name dname, round(sysdate -hire_date)
service
from employees
natural join departments
order by dname, ename;
新表中的行是联接这两个源表的结果，两个选中的列已经改变名称。计算员工被雇佣天数的算术运算的结果将填充新的SERVICE列，并将会按指定的顺序插入这些行。后面的DML不会保持这个顺序，但假设是标准的HR模式数据，新表会如下所示：
select *
from emp_dept
where rownum < 10;
ENAME DNAME SERVICE
--------------- --------------- ----------
Gietz Accounting 4203
De Haan Executive 4713
Kochhar Executive 3001
Chen Finance 2994
Faviet Finance 4133
Popp Finance 2194
Sciarra Finance 2992
Urman Finance 2834
Austin IT 3089
9 rows selected.
子查询当然可以包含WHERE子句来限制插入新表的行。要创建一个没有行的表，可以使用排除所有行的WHERE子句：
create table no_emps as
select *
from employees
where 1=2;
WHERE子句1=2绝不会返回TRUE，因此会创建表结构以备用，但在创建时没有插入行。
5.4.3 在创建之后更改表定义
创建表后，可以对表进行许多更改。影响物理存储的那些更改属于数据库管理员领域，但许多更改只是逻辑的，由SQL开发人员实施。下面就是示例(它们大部分都很简单)：
● 添加列：
alter table emp
add (job_id number);
● 修改列：
alter table emp
modify (comm number(4,2) default 0.05);
● 删除列：
alter table emp
drop column comm;
● 将列标记为未使用：
alter table emp
set unused column job_id;
● 重命名列：
alter table emp
rename column hiredate to recruited;
● 将表标记为只读：
alter table emp
read only;
所有这些变更都是包含内置COMMIT的DDL命令。因此它们是不可逆的，如果表上有活动事务，它们就会失败。其实它们也是瞬间完成的(删除列除外)。删除列可能是一个耗时的操作，因为删除各列之后，必须重新构造各行来删除列的数据。SET UNUSED命令——对于SQL而言，它让这些列不存在——通常是更好的选择，如果方便的话，后面采用下列命令，一次删除表中所有未使用的列：
ALTER TABLE tablename
DROP UNUSED COLUMNS;
对于DML命令而言，将表标记为只读会产生错误。但还是能够删除表。这可能令人困惑，但理解之后，就会发现它完全符合逻辑。DROP命令实际上不影响表：它影响数据字典中的表，这些表定义表，它们不是只读的。
5.4.4 删除和截断表
TRUNCATE TABLE命令能够删除表的所有行，同时保持表定义不变。DROP TABLE更极端，因为同时会删除表定义。其语法如下所示：
DROP TABLE [schema.]tablename ;
如果没有指定模式，那么当前登录的模式中名为tablename的表就会被删除。
和TRUNCATE一样，SQL在删除表之前不会生成警告，而且，与任何DDL命令一样，它包含COMMIT。因此DROP一般不可逆。但也有一些限制：如果会话(甚至是你自己的)有正在处理的事务，该事务包含表中的行，那么DROP就会失败；另外，也不可以删除在为其他表而定义的外键约束中引用的表。首先必须删除这个表(或者这个约束)。
提示：
Oracle 12c包含一个默认为启用的垃圾箱选项。它允许还原任何已删除的表，除非表是用PURGE选项删除的，或者禁用了垃圾箱选项。
练习5-3 创建表
在本练习中，使用SQL Developer创建一个堆表，使用子查询来插入一些行，并修改表。使用SQL*Plus进行更多修改，然后删除表。
(1) 使用SQL Developer，以用户HR身份连接数据库。
(2) 右击导航树中的Tables分支，单击New Table选项。
(3) 将新表命名为EMPS，使用Add Column按钮，将它设置为如图5-6所示。
图5-6 设置新表
(4) 单击DDL标签，看看是不是已经构造了语句。它应该如下所示：
CREATE TABLE EMPS
(
EMPNO NUMBER
,ENAME VARCHAR2(25)
,SALARY NUMBER
,DEPTNO NUMBER(4, 0)
);
返回Table选项卡(如图5-6所示)，单击OK按钮创建表。
(5) 运行下列语句：
insert into emps
select employee_id, last_name, salary, department_id
from employees;
并提交插入：
commit;
(6) 右击SQL Developer导航器中的EMPS表，单击Column和Add按钮。
(7) 定义一个新的列EMAIL，数据类型为DATE，如图5-7所示；单击Apply按钮创建列。
图5-7 定义新列
(8) 使用SQL*Plus作为HR用户连接到数据库。
(9) 为EMPS表中的HIRED列定义一个默认值：
alter table emps
modify (hired default sysdate);
(10) 在没有指定HIRED值的情况下插入一行，检查新的行有HIRED日期，而其他行没有：
insert into emps (empno, ename)
values (99, 'Newman');
select hired, count(1)
from emps
group by hired;
(11) 删除新表，进行整理。
drop table emps;
5.5 创建和使用临时表
临时表包含所有会话都可以访问的定义，但其中的行是插入行的会话专用的。编程人员可将它们作为私有存储区，用于操纵大量数据。语法如下：
CREATE GLOBAL TEMPORARY TABLE temp_tab_name
(column datatype [, column datatype] )
[ON COMMIT {DELETE | PRESERVE} ROWS] ;
列定义与普通表没有差别，但确实可以通过子查询来提供。末尾的可选子句确定插入的任何行的生命周期。默认方式是：在插入行的事务完成之时，删除相应的行，但也可以更改此行为，以便将它们保留到插入行的会话结束为止。无论选择哪个选项，数据都专门用于每个会话：不同用户可将自己的行插入自己的表的副本中，谁都看不到其他人的行。
在很多方面，临时表都与永久表类似。可以针对其执行任何DML或SELECT命令。可以为临时表定义索引、约束以及触发器。可以在视图和同义词中引用它，或将它与其他表联接在一起。二者的区别在于：临时表的数据是临时的，专用于相应的会话，针对其执行的所有SQL命令的速度远比针对永久表的命令快。
速度快的第一个原因是，临时表不是永久表空间中的段。表将写出到用户临时表空间的临时段中。临时表空间上的I/O远比永久表空间上的I/O速度快，原因是前者不使用数据库缓冲区缓存，而全部由会话的服务器进程直接在磁盘上执行。
速度快的第二个原因在于：针对临时表的DML不生成重做数据。由于数据仅在会话生存期间(可能仅是事务期间)保留，就没必要生成重做数据。这带来了双重好处：针对表的会话的DML更快，且取消了重做生成系统的负担(在忙碌的多用户数据库中，这是一个糟糕的争用点)。
图5-8显示了使用SQL*Plus来创建和使用临时表的过程。Database Control的Table Creation Wizard也可以创建临时表。
图5-8 创建和使用临时表
练习5-4 创建和使用临时表
本练习创建用于报告当前员工的临时表。使用两个SQL*Plus 会话演示数据是每个会话专用的。
(1) 使用SQL*Plus，以用户HR的身份连接到数据库。
(2) 按如下方式创建临时表：
create global temporary table tmp_emps on commit preserve rows as
select * from employees where 1=2
(3) 插入一些行，并将其提交：
insert into tmp_emps select * from employees where department_id=30;
commit;
(4) 以HR的身份启动第二个SQL*Plus会话。
(5) 在第二个会话中，确认第一个会话中的第一个插入虽已提交但不可见，并插入一些不同的行：
select count(*) from tmp_emps;
insert into tmp_emps select * from employees where department_id=50;
commit;
(6) 在第一个会话中，将表截断：
truncate table tmp_emps;
(7) 在第二个会话中，确认表的会话副本仍然包含行：
select count(*) from tmp_emps;
(8) 在第二个会话中，演示会话的终止并不会清除行。这需要断开连接并再次连接：
disconnect;
connect hr/hr
select count(*) from tmp_emps;
(9) 在两个会话中通过删除表来清理环境。