5.9 同義詞

同義詞是對象的備選名稱。如果對象存在同義詞，那么任何SQL語句都能通過實(shí)際名稱或通過同義詞來訪問該對象。同義詞看上去似乎沒有什么作用，然而實(shí)際情況并非如此。使用同義詞意味著應(yīng)用程序可以對任何用戶起作用，而不考慮哪個(gè)模式擁有視圖和表，甚至不考慮表駐留在哪個(gè)數(shù)據(jù)庫中。分析如下語句：

        select * from hr.employees@prod;

發(fā)出該語句的用戶必須知道員工表由數(shù)據(jù)庫鏈接PROD標(biāo)識(shí)的數(shù)據(jù)庫中的HR模式擁有(不需要關(guān)心數(shù)據(jù)庫連接的細(xì)節(jié)，它們是一種訪問不是目前所登錄數(shù)據(jù)庫的其他數(shù)據(jù)庫中對象的方式)。如果用如下語句創(chuàng)建公有同義詞：

        create public synonym emp for hr.employees@prod;

那么所有用戶都需要輸入如下語句：

        select * from emp;

這個(gè)語句既提供了數(shù)據(jù)無關(guān)性，又提供了位置透明性。用戶必須有訪問底層對象的權(quán)限，才能成功使用基于同義詞的引用。只要調(diào)整同義詞，就可以在不修改代碼的情況下重命名或重定位表和視圖。

與SELECT語句一樣，DML語句可以像訪問同義詞引用的對象一樣訪問同義詞。

私有同義詞是模式對象。它們要么必須在自己的模式中，要么必須用模式名限定。公有同義詞的存在與模式無關(guān)。任何具有查看權(quán)限的用戶都可以引用公有同義詞，不需要用模式名限定。私有同義詞必須在模式中有唯一的名稱，而公有同義詞可以與模式對象同名。當(dāng)執(zhí)行訪問不帶模式限定符的對象的語句時(shí)，Oracle會(huì)先在局部模式中查找對象，只有在局部模式中找不到時(shí)才會(huì)在公有模式中查找。因此在前面的示例中，如果用戶碰巧擁有名為EMP的表，它看到的就是這個(gè)表——而不是公有同義詞指向的名為EMP的表。

創(chuàng)建同義詞的語法如下：

        CREATE [PUBLIC] SYNONYM synonym FOR object ;

需要為用戶授予創(chuàng)建私有同義詞的權(quán)限，并且進(jìn)一步授予創(chuàng)建公有同義詞的權(quán)限。通常，只有數(shù)據(jù)庫管理員能夠創(chuàng)建(或刪除)公有同義詞。這是因?yàn)槭欠翊嬖诠型x詞會(huì)影響每個(gè)用戶。

刪除同義詞的語法如下：

        DROP [PUBLIC] SYNONYM synonym ;

如果同義詞引用的對象(表或視圖)被刪除，同義詞仍然存在。這時(shí)試圖使用這樣的同義詞會(huì)返回一個(gè)錯(cuò)誤。在這一方面，同義詞的行為方式與視圖相同。如果重新創(chuàng)建對象，那么在使用同義詞前必須重新編譯。與視圖一樣，在下次訪問同義詞時(shí)自動(dòng)重新編譯，也可以使用如下語句顯式地完成編譯：

        ALTER SYNONYM synonym COMPILE;

練習(xí)5-8 創(chuàng)建和使用同義詞

在本練習(xí)中，將用HR模式中的對象創(chuàng)建和使用私有同義詞。可以使用SQL*Plus或SQL Developer。

(1) 作為用戶HR連接到數(shù)據(jù)庫。

(2) 為練習(xí)5-7中創(chuàng)建的三個(gè)視圖創(chuàng)建同義詞：

        create synonym emp_s for emp_anon_v;
        create synonym dept_s for dept_anon_v;
        create synonym dsum_s for dep_sum_v;

(3) 確認(rèn)同義詞等同于底層對象：

        describe emp_s;
        describe emp_anon_v;

(4) 通過對同義詞而不是視圖運(yùn)行練習(xí)5-7中的語句，來確認(rèn)同義詞有效(甚至到產(chǎn)生相同錯(cuò)誤的程度)：

        select * from dsum_s;
        insert into dept_s values   (99, 'Temp Dept',1800 );
        insert into emp_s values   (sysdate, 'AC_MGR',10000,0,99);
        update emp_s set salary=salary*1.1;
        rollback;
        select max(salaries / staff) from dsum_s;

(5) 刪除兩個(gè)視圖：

        drop view emp_anon_v;
        drop view dept_anon_v;

(6) 查詢基于已刪除視圖的復(fù)雜視圖：

        select * from dep_sum_v;

可以注意到查詢失敗。

(7) 嘗試重新編譯被破壞的視圖：

        alter view dep_sum_v compile;

此次編譯嘗試也會(huì)失敗。

(8) 刪除DEP_SUM_V視圖：

        drop view dep_sum_v;

(9) 查詢已刪除視圖的同義詞：

        select * from emp_s;

該查詢會(huì)失敗。

(10) 重新編譯被破壞的同義詞：

        alter synonym emp_s compile;

注意，雖然這樣做不會(huì)拋出錯(cuò)誤，而是從第(9)步開始重新運(yùn)行查詢。該同義詞無疑仍然處于破壞狀態(tài)。

(11) 通過刪除同義詞進(jìn)行整理：

        drop synonym emp_s;
        drop synonym dept_s;
        drop synonym dsum_s;

5.10 序列

序列是生成唯一整數(shù)值的結(jié)構(gòu)。由于只有一個(gè)會(huì)話能夠讀取下一個(gè)值，因此強(qiáng)制該值遞增。這是序列化的要點(diǎn)，因此生成的每個(gè)值都將是唯一的。

序列是用來生成主鍵的寶貴工具。很多應(yīng)用程序都需要自動(dòng)生成主鍵值。日常業(yè)務(wù)數(shù)據(jù)處理的一個(gè)示例是客戶編號(hào)或訂單編號(hào)：業(yè)務(wù)分析師會(huì)指出，每個(gè)訂單必須有唯一的編號(hào)，編號(hào)應(yīng)連續(xù)地遞增。其他應(yīng)用程序可能沒有這樣的行規(guī)要求，但是仍然需要序列來實(shí)施關(guān)系完整性。以電話賬單系統(tǒng)為例：在行規(guī)中的唯一標(biāo)識(shí)符是電話號(hào)碼(字符串)，一次電話呼叫的標(biāo)識(shí)符將是主叫電話號(hào)碼和通話開始時(shí)間(時(shí)間戳)。這些數(shù)據(jù)類型太復(fù)雜，對于通過電話交換系統(tǒng)的大流量的主鍵來說，沒必要采用這么復(fù)雜的類型。盡管使用這些數(shù)據(jù)類型也能夠記錄信息，但是使用簡單的數(shù)值列來定義主鍵和外鍵要快得多。這些列中的值可以作為序列的基礎(chǔ)。

序列機(jī)制與表、行鎖機(jī)制及提交或回滾過程無關(guān)。這意味著序列每分鐘能發(fā)出數(shù)千個(gè)唯一值，這比任何涉及從表中選擇列、更新列并提交修改的方法快得多。

圖5-12顯示了兩個(gè)從一個(gè)序列SEQ1中選擇值的會(huì)話。

注意，圖5-12中SEQ1.NEXTVAL的每個(gè)選擇項(xiàng)生成一個(gè)唯一數(shù)值。這些數(shù)值按選擇的時(shí)間順序連續(xù)發(fā)出，數(shù)值會(huì)全局地遞增，而不是僅在一個(gè)會(huì)話中遞增。

5.10.1 創(chuàng)建序列

創(chuàng)建序列的完整語法如下：

        CREATE SEQUENCE [schema.]sequencename
        [INCREMENT BY number]
        [START WITH number]
        [MAXVALUE number | NOMAXVALUE]
        [MINVALUE number | NOMINVALUE]
        [CYCLE | NOCYCLE]
        [CACHE number | NOCACHE]
        [ORDER | NOORDER] ;

可以看出，序列的創(chuàng)建可以非常簡單。例如，圖5-12中使用的序列是用如下語句創(chuàng)建的：

        create sequence seq1;

各選項(xiàng)如表5-2所示。

對INCREMENT BY、START WITH，以及MAXVALUE或MINVALUE的適當(dāng)設(shè)置取決于業(yè)務(wù)分析師。

CYCLE很少使用，因?yàn)樗剐蛄邪l(fā)出重復(fù)值。如果用序列來生成主鍵值，那么只有當(dāng)數(shù)據(jù)庫中存在刪除原有行比序列重新發(fā)出數(shù)值快得多的例程時(shí)，CYCLE才有意義。

緩存序列值對于性能至關(guān)重要。從序列中選擇是應(yīng)用程序代碼中串行化的要點(diǎn)：一次只能有一個(gè)會(huì)話產(chǎn)生這個(gè)序列值。這種機(jī)制非常有效率：相比于先鎖住行，再更新行，然后用COMMIT解鎖行，使用這種機(jī)制會(huì)快很多。但是即便如此，從序列中選擇仍然會(huì)造成會(huì)話之間的資源爭用。CACHE關(guān)鍵字通知Oracle在緩存中預(yù)先生成序號(hào)。這意味著如果需要根據(jù)要求生成序號(hào)，則可以更快速地發(fā)出這些序號(hào)。

5.10.2 使用序列

為使用序列，會(huì)話可以用強(qiáng)制序列遞增的偽列NEXTVAL選擇下一個(gè)值，也可以用偽列CURRVAL選擇發(fā)送給該會(huì)話的上一個(gè)(或“當(dāng)前”)值。NEXTVAL將是全局唯一值：選擇它的每個(gè)會(huì)話的每個(gè)SELECT會(huì)得到不同的、遞增的值。直到再次選擇NEXTVAL前，CURRVAL將是某個(gè)會(huì)話的常量。無法找出序列發(fā)出的上一個(gè)值是什么值：可以使用NEXTVAL遞增來獲得下一個(gè)值，也可以使用CURRVAL來重新調(diào)用發(fā)送給會(huì)話的上一個(gè)值，但是無法找到發(fā)出的上一個(gè)值。

序列的一種典型用途是用于主鍵值。本例使用序列CUST_SEQ生成唯一客戶號(hào)，使用序列ORDER_SEQ生成唯一訂單號(hào)，使用LINE_ SEQ生成該訂單的行條目的唯一行號(hào)。先創(chuàng)建序列，這是只執(zhí)行一次的操作：

        create sequence order_seq start with 10;
        create sequence line_seq start with 10;

然后插入訂單，每個(gè)訂單所在的行作為單個(gè)事務(wù)：

        insert into orders (order_id, order_date, customer_id)
        values (order_seq.nextval, sysdate, '1000' );
        insert into order_items (order_id, order_item_id, product_id)
        values (order_seq.currval, line_seq.nextval, 'A111');
        insert into order_items (order_id, order_item_id, product_id)
        values (order_seq.currval, line_seq.nextval, 'B111');
        commit;

第一條INSERT語句生成了一個(gè)訂單，它具有從客戶編號(hào)1000的序列ORDER_SEQ得到的唯一訂單號(hào)。第二條和第三條語句插入訂單的兩行：使用前面從ORDER_SEQ發(fā)出的訂單號(hào)作為將這兩行連接到訂單的外鍵，并使用LINE_SEQ中的下一個(gè)值生成每一行的唯一標(biāo)識(shí)符。最后，提交事務(wù)。

序列沒有綁定到任何一個(gè)表。在前面的示例中，從技術(shù)上來說沒有理由不使用一個(gè)序列來生成訂單和行的主鍵值。

對于持久遞增序列來說，COMMIT并不是必需的語句：序列遞增是持久的操作，一旦發(fā)生就對其余部分可見，并且不能回滾。序列更新的發(fā)生與事務(wù)管理系統(tǒng)無關(guān)。由于這個(gè)原因，序列中總是存在間隔。如果數(shù)據(jù)庫重啟并且使用CACHE子句，那么間隔會(huì)比較大。當(dāng)數(shù)據(jù)庫關(guān)閉時(shí)，所有已生成并緩存但還沒有發(fā)出的數(shù)值會(huì)丟失。下次重啟時(shí)，序列的當(dāng)前值是上次生成的數(shù)值，而不是上次發(fā)出的數(shù)值。因此，如果使用默認(rèn)的CACHE 20，那么每次關(guān)閉/啟動(dòng)會(huì)丟失20個(gè)數(shù)值。

如果業(yè)務(wù)分析師指出序列中不能有間隔，那么必須使用另一種生成唯一數(shù)值的方式。對于前面生成訂單的示例，當(dāng)前訂單號(hào)可以存儲(chǔ)在該表中，并將它初始化為10：

        create table current_on(order_number number);
        insert into current_on values(10);
        commit;

然后創(chuàng)建訂單的代碼將變成：

        update current_on set order_number=order_number + 1;
        insert into orders (order_number, order_date, customer_number)
        values ((select order_number from current_on), sysdate, '1000');
        commit;

作為一種生成唯一訂單號(hào)的方式，當(dāng)然可以采用這樣的代碼，因?yàn)橛唵翁?hào)的遞增在插入訂單的事務(wù)中，所以必要時(shí)可以使用插入回滾它：除非故意刪除訂單，否則訂單號(hào)中不會(huì)有間隔。但是，這樣做的效率遠(yuǎn)不如使用序列，而且眾所周知的是，像這樣的代碼會(huì)引起致命的爭用問題。如果有很多會(huì)話試圖鎖定并遞增包含當(dāng)前編號(hào)的一行，那么當(dāng)隊(duì)列在等待輪到自己時(shí)，整個(gè)應(yīng)用程序就會(huì)掛起。

創(chuàng)建和使用序列后，可以對序列進(jìn)行修改。語法如下：

        ALTER SEQUENCE sequencename
        [INCREMENT BY number]
        [START WITH number]
        [MAXVALUE number | NOMAXVALUE]
        [MINVALUE number | NOMINVALUE]
        [CYCLE | NOCYCLE]
        [CACHE number | NOCACHE]
        [ORDER | NOORDER] ;

ALTER命令與CREATE命令基本相同，只有一點(diǎn)區(qū)別：ALTER命令不能設(shè)置起始值。如果要重啟該序列，唯一的方法是刪除并重新創(chuàng)建它。為調(diào)整默認(rèn)緩存值以改進(jìn)前面的訂單條目示例的性能，可以使用如下代碼：

        alter sequence order_seq cache 1000;

然而，如果希望將該序列重置為它的起始值，唯一的方法是先刪除它：

        drop sequence order_seq;

然后創(chuàng)建該序列。

練習(xí)5-9 創(chuàng)建和使用序列

在本練習(xí)中，創(chuàng)建一些序列并使用它們。需要兩個(gè)并發(fā)會(huì)話，可以使用SQL Developer或SQL*Plus。

(1) 在單獨(dú)的會(huì)話中作為HR登錄到數(shù)據(jù)庫兩次。將其中一次登錄看成A會(huì)話，另一次看成B會(huì)話。

(2) 在A會(huì)話中，創(chuàng)建如下所示的序列：

        create sequence seq1 start with 10 nocache maxvalue 15 cycle;

NOCACHE的使用會(huì)降低性能。如果指定了MAXVALUE，那么有必要用CYCLE防止到達(dá)MAXVALUE時(shí)出錯(cuò)。

(3) 在適當(dāng)?shù)臅?huì)話中按正確的順序執(zhí)行表5-3中的命令，以觀察NEXTVAL和CURRVAL的使用以及序列的循環(huán)。

(4) 創(chuàng)建一個(gè)帶主鍵的表：

    create table seqtest(c1 number, c2 varchar2(10));
    alter table seqtest add constraint seqtest_pk primary key (c1);

(5) 創(chuàng)建一個(gè)序列來生成主鍵值：

    create sequence seqtest_pk_s;

(6) 在A會(huì)話中，向新表中插入一行并提交：

    insert into seqtest values (seqtest_pk_s.nextval, 'first');
    commit;

(7) 在B會(huì)話中，向新表中插入一行且不提交：

    insert into seqtest values (seqtest_pk_s.nextval, 'second');

(8) 在A會(huì)話中，插入第三行并提交：

    insert into seqtest values (seqtest_pk_s.nextval, 'third');
    commit;

(9) 在B會(huì)話中，回滾第二個(gè)插入：

    rollback;

(10) 在B會(huì)話中，查看表的內(nèi)容：

    select * from seqtest;

這就演示了在事務(wù)控制機(jī)制外部遞增序列并立即發(fā)布下一個(gè)值。

(11) 整理表和序列：

        drop table seqtest;
        drop sequence seqtest_pk_s;
        drop sequence seq1;

(12) 使用SQL Developer或SQL*Plus連接到HR模式，并創(chuàng)建三個(gè)將在后續(xù)練習(xí)中使用的序列。

        create sequence prod_seq;
        create sequence cust_seq;
        create sequence order_seq;

5.11 本章知識(shí)點(diǎn)回顧

分類主要的數(shù)據(jù)庫對象

● 有些對象包含數(shù)據(jù)，主要是表和索引。

● 編程對象(如存儲(chǔ)過程和函數(shù))是可執(zhí)行的代碼。

● 視圖和同義詞是能夠訪問其他對象的對象。

● 表是保存使用列定義的行的二維結(jié)構(gòu)。

● 表在模式中。模式名和表名形成唯一標(biāo)識(shí)符。

列舉列可用的數(shù)據(jù)類型

● 最常見的字符數(shù)據(jù)類型有VARCHAR2、NUMBER和DATE。

● 還有其他許多數(shù)據(jù)類型。

創(chuàng)建簡單的表

● 可以從頭開始創(chuàng)建表或者使用子查詢創(chuàng)建表。

● 創(chuàng)建之后，可以添加、刪除或者修改列定義。

● 表定義可以包含列的默認(rèn)值。

創(chuàng)建和使用臨時(shí)表

● 只有插入行的會(huì)話才能訪問臨時(shí)表中的行。

● 針對臨時(shí)表的DML不生成重做數(shù)據(jù)。

● 臨時(shí)表只存在于會(huì)話的PGA或臨時(shí)段中。

● 臨時(shí)表只在會(huì)話期間或事務(wù)期間(具體取決于創(chuàng)建方式)保存行。

索引

● 要實(shí)施唯一約束和主鍵約束，就必須使用索引。

● B*樹索引不包括NULL，但位圖索引包含NULL。

● B*樹索引可為唯一索引，也可為非唯一索引，這些將決定是否接受重復(fù)鍵值。

● B*樹索引適用于基數(shù)大的列，而位圖索引適用于基數(shù)小的列。

● 位圖索引可以采用復(fù)合形式，可以基于函數(shù)，也可以降序排列。B*樹索引可以是唯一的、壓縮的和反向鍵。

約束

● 可以在創(chuàng)建表時(shí)定義約束，也可以在隨后添加。

● 可以與列一起定義約束，或者在列之后的表級(jí)別定義約束。

● 表級(jí)別約束可能比內(nèi)聯(lián)定義的約束更復(fù)雜。

● 表只能有一個(gè)主鍵，但可以有許多唯一鍵。

● 主鍵的功能等同于UNIQUE加上NOT NULL。

● 唯一約束不阻止多個(gè)空值的插入。

● 外鍵約束定義表之間的關(guān)系。

視圖

● 簡單視圖有一個(gè)明細(xì)表(或基表)，既不使用函數(shù)也不使用聚合。

● 復(fù)雜視圖可以基于任何SELECT語句，不管多么復(fù)雜。

● 視圖是模式對象。要在另一個(gè)模式中使用視圖，必須用模式名限定視圖名。

● 可以像查詢表一樣查詢視圖。

● 可以將視圖聯(lián)接到其他視圖或者聯(lián)接到表，它們可以被聚合，在有些情況下它們可以接受DML語句。

● 視圖僅作為數(shù)據(jù)字典結(jié)構(gòu)存在。每當(dāng)查詢視圖時(shí)，必須運(yùn)行底層SELECT語句。

同義詞

● 同義詞是視圖或表的另一個(gè)名稱。

● 私有同義詞是模式對象；公有同義詞存在于用戶模式之外，不需要指定模式名作為限定符就可以使用。

● 同義詞與視圖和表共享相同的名稱空間，因此可以與它們互換使用。

序列

● 序列生成唯一值——除非指定了MAXVALUE或MINVALUE和CYCLE。

● 遞增序列不需要提交，不能回滾。

● 任何會(huì)話都能通過讀取它的下一個(gè)值來遞增序列。可以獲得上次發(fā)送給會(huì)話的值，但不能獲得上次發(fā)出的值。