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值的舍入。

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就會失敗；另外，也不可以刪除在為其他表而定義的外鍵約束中引用的表。首先必須刪除這個表(或者這個約束)。

練習5-3 創建表

在本練習中，使用SQL Developer創建一個堆表，使用子查詢來插入一些行，并修改表。使用SQL*Plus進行更多修改，然后刪除表。

(1) 使用SQL Developer，以用戶HR身份連接數據庫。

(2) 右擊導航樹中的Tables分支，單擊New Table選項。

(3) 將新表命名為EMPS，使用Add Column按鈕，將它設置為如圖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按鈕創建列。

(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-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) 在兩個會話中通過刪除表來清理環境。