關于約束、CASE語句和游標
我們的技術專家談完整性、時間和歸屬問題。
我仔細閱讀了《Oracle9i 數據庫概念手冊（Oracle9i Database Concepts Manual）》和你們的站點，但對下述概念仍不明白：
"定義為可延遲（deferrable）的約束可以指定為：
1. initially immediate（初始化立即執行）或
2. initially deferred（初始化延遲執行）。"
我知道什么是延遲約束，但不明白什么叫"初始化立即執行的可延遲約束"和"初始化延遲執行的可延遲約束"。請解釋二者的區別。還有，這些約束有什么用途？這是通常輕易混淆的問題。我希望下面的例子能解釋清楚。初始化立即執行/延遲執行規定了在默認情況下應該如何執行約束：
初始化立即執行--在每條語句執行結束時檢驗約束
初始化延遲執行--一直等到事務完成后（或者調用set constraint immediate語句時）才檢驗約束
來看下面的代碼：
SQL> create table t
2 ( x int constraint
check_x check ( x > 0 )
deferrable
initially immediate,
3 y int constraint
check_y check ( y > 0 )
deferrable
initially deferred
4 )
5 /
Table created.
SQL> insert into t values ( 1,1 );
1 row created.
SQL> commit;
Commit complete.
所以，當兩個約束同時滿足時才能正確無誤地插入行。但是，假如我試圖插入違反CHECK_X約束（初始化立即執行的約束）的行，則系統會立即檢驗約束，并得到下面的結果：
SQL> insert into t values ( -1,1);
insert into t values ( -1,1)
*
ERROR at line 1:
ORA-02290: check constraint
(OPS$TKYTE.CHECK_X) violated
由于CHECK_X是可延遲但初始化為立即執行的約束，所以這一行馬上被拒絕了。而CHECK_Y則不同，它不僅是可延遲的，而且初始化為延遲執行，這就意味著直到我用COMMIT命令提交事務或將約束狀態設置為立即執行時才檢驗約束。
SQL> insert into t values ( 1,-1);
1 row created.
現在它是成功的（總之到目前為止是成功的）。我將約束檢驗延遲到了執行COMMIT的時候：
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint
(OPS$TKYTE.CHECK_Y) violated
此時數據庫將事務回滾，因為違反約束導致了COMMIT語句的失敗。這些語句說明了初始化立即執行與初始化延遲執行約束之間的區別。initially（初始化）部分指定Oracle什么時候會進行默認的約束檢驗--是在語句結束時［immediate（立即執行）］，還是在事務結束時［deferred（延遲執行）］。我還要說明deferred（可延遲）子句有什么用。我可以發出命令，讓所有可延遲的約束變為延遲執行的。注重，你也可以對一個約束使用該命令；你不必讓所有可延遲的約束都變為延遲執行的：
SQL> set constraints all deferred;
Constraint set.
SQL> insert into t values ( -1,1);
1 row created.
由于將初始化立即執行的約束設置為延遲執行的模式，這個語句似乎執行成功；但是，當我用COMMIT語句提交事務時，看一下會發生什么：
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint
(OPS$TKYTE.CHECK_X) violated
事務提交失敗并回滾，因為在COMMIT語句之后對約束進行了檢驗。相反，我可以將初始化為延遲執行的約束變為"立即"執行的約束：
SQL> set constraints all immediate;
Constraint set.
SQL> insert into t values ( 1,-1);
insert into t values ( 1,-1)
*
ERROR at line 1:
ORA-02290: check constraint
(OPS$TKYTE.CHECK_Y) violated
前面在我提交前能執行的語句現在立即出了問題。因為我手動修改了默認的約
束模式。
延遲約束有哪些實際用處呢？ 有很多。它主要用于物化視圖（快照）。這些視圖會使用延遲約束來進行視圖刷新。在刷新物化視圖的過程中，可能會破壞完整性，而且將不能逐句檢驗約束。但到執行COMMIT時，數據完整性就沒問題了，而且能滿足約束。沒有延遲約束，物化視圖的約束可能會使刷新過程不能成功進行。
使用延遲約束的另一個普遍原因是，當猜測是否需要更新父/子關系中的主鍵時，它有助于級聯更新。假如你將外鍵設為可延遲、但初始化為立即執行，那么你就可以
將所有約束設置為可延遲。
將父鍵更新為一個新值--至此子關系的完整性約束不會被驗證。
將子外鍵更新為這個新值。
COMMIT--只要所有受更新影響的子記錄都指向現有的父記錄，這條命令就能成功執行。
假如沒有延遲約束，這一更新過程將極為艱難。參見asktom.oracle.com/~tkyte/update_cascade/index.Html中給出的沒有這一特性時進行級聯更新所必需的示例代碼！
此外，你可以在各種多語句事務中使用延遲約束，這些事務在處理的過程中需要暫時破壞完整性，但最后它們都會物歸原樣。
如何計算時間
你是如何計算asktom.oracle.com第一頁中AGE列顯示的時間的？我之所以問這個問題是因為我看到它有多種格式，如9個月3小時；2.3年；19個小時；等等。我是Oracle新手，想知道你們在使用哪種日期計算法。
我就是使用Oracle8i第2版（8.1.6版）中介紹的使用已久但很好用的CASE語句：
Select
case
when sysdate-timestamp < 1/24
then round(24*60*(sysdate-timestamp))
' minutes old '
when sysdate-timestamp < 1
then round(24*(sysdate-timestamp))
' hours old '
when sysdate-timestamp < 14
then trunc(sysdate-timestamp)
' days old '
when sysdate-timestamp < 60
then trunc((sysdate-timestamp)/7)
' weeks old '
when sysdate-timestamp < 365
then round(months_between
(sysdate,timestamp))
' months old '
else round(months_between
(sysdate,timestamp)/12,1)
' years old '
end age, ...
假如你想在Oracle8i的PL/SQL中使用CASE語句，則會出現一個錯誤消息，因為PL/SQL語法分析程序不識別CASE語句。（請注重，在Oracle9i不存在這樣的問題。）為了避開Oracle8i的限制，你可以
將CASE語句隱藏在視圖當中，并用PL/SQL來查詢視圖。
使用嵌套的DECODE語句來代替CASE語句。
我本人愿意使用視圖，但讀者Martin Burbridge在asktom.oracle.com 網站上公布了下面這段DECODE代碼：
decode(sign(sysdate-timestamp-1/24),-1,
round(24*60*(sysdate-timestamp))
' minutes old ',
decode(sign(sysdate-timestamp - 1), -1,
round(24*(sysdate-timestamp))
' hours old ',
decode(sign(sysdate-timestamp-14),-1,
trunc(sysdate-timestamp)
' days old ',
decode(sign(sysdate-timestamp-60),-1,
trunc((sysdate-timestamp)/7)
' weeks old ',
decode(sign(sysdate-timestamp-365),-1,
round(months_between
(sysdate,timestamp))
' months old ',
round(months_between
(sysdate,timestamp)/12,
1)
' years old '
))))) age
它與CASE語句的功能完全相同--只是不太明顯。
文件放在哪？
我正在考慮為一個應用程序設計些選項，利用它用戶可以上傳和存儲可供他人下載的文檔。文檔可以是平均大小為150K的Microsoft Word文檔。最初需要（從CD）移植18000到20000個文檔，當使用該應用程序時存儲數量會增加到大約25000個文檔。瀏覽器前端是用于上傳和下載的PL/SQL插件（PL/SQL cartridge）頁面。一開始，會有400到500人幾乎同時訪問該應用程序，兩周內天天將有300人訪問（分散訪問）。 文檔本身在數據庫中作為BLOB存儲。
從使用方面考慮，你覺得這樣的選項好嗎？它會過多占用系統全局區（SGA）嗎？考慮到應用程序的需求以及前端（基于瀏覽器），除了保存為BLOB，還有沒有其他選擇，如文件系統？
我什么都存在數據庫里。就是這樣。假如數據就是你的一切，無論它們有什么樣的值，事實上都要放到數據庫中，在那里數據可以得到專業化的治理、備份，恢復而且安全。除了這些實實在在的好處，你還可以索引及搜索文檔。（誠然，用文件系統也可以做這些，但在索引和文檔之間不存在完整性。）在數據庫中，你可以轉換文檔格式（例如，上傳一個DOC文件，而顯示為HTML格式）。你的數據是完全集成的、安全的、有備份的而且隨時供你使用。
在Oracle公司內部，我們將一個幾千吉字節的數據庫作為整個公司的一個單一的文件服務器。公司所有文檔都存在那里，存在這樣一個單一的地方，可以對這些文檔進行備份、搜索、建立索引和訪問。在常規的文件系統中治理成千上萬的文檔是不可能的，即便文件系統能存下這些文檔。
至于SGA的問題，這完全在你。假如你不想把BLOB放在緩沖區里，可以對其使用NOCACHE，這樣你就不必擔心"過多占用"SGA的問題了。
游標（Cursors）放在哪？
你能告訴我在編寫PL/SQL代碼時最好把游標放在哪嗎？我們應該把它們放在包說明中還是包體中？我問這些問題是因為和我一起工作的一名開發人員硬要把所有游標都放在包說明中。他告訴我這樣做才對。的確，假如在包中不止一次使用這些游標，我們應該把它們放在包說明中。但這個包里的所有游標都只使用一次，所以我認為應該把它們放到調用它們的過程/函數的聲明部分。我說得對嗎？把所有游標都放在包說明中有什么優缺點？游標的放置位置影響性能嗎？
無論現在、過去還是將來我個人的偏好都是在大多數情況下使用隱式游標，也就是說根本就不顯式地定義游標！例如：
is
...
begin
....
for x in ( select * from emp )
loop
這個技巧用于大約50到100行以內的結果集時非凡好。比起顯式游標來我更喜歡這種方法是因為：
與使用顯式游標相比，使用它的CPU效率更高。
我可以瀏覽代碼，輕松地查看正在處理的數據。查詢過程就在我面前。
當查詢變大時，我可以使用視圖。我在視圖中仍能查看正在查詢的數據，但是視圖的復雜性被隱藏了起來。我不是把它隱藏在游標中，而是隱藏在視圖中。
有些時候你必須使用顯式游標，最常見的是要處理更大的結果集以及在使用FETCH語句時需要使用BULK COLLECT以保證系統性能的情況。當我必須使用顯式定義的游標時，我選擇定義在過程自身內（不僅在包體中，而且正好在包體的過程里）的局部游標。為什么呢？
與使用全局游標（在說明中定義）相比，使用它的CPU效率更高。
我仍能瀏覽代碼，并能輕松地查看正在處理的數據。
它使我可以使用視圖，因為查詢是在過程中，而我不希望它影響到代碼的其余部分。
游標屬于誰一目了然。
你理解這里的模式嗎？同樣，具有局部作用域（在過程中）的游標使用后會自動清除。沒有具有%isopen屬性的游標垃圾弄亂我的代碼。 （我的代碼從沒用過isopen"特性"。）我不必擔心："你知道過程P1使用了cursor_x，我也使用了cursor_x，而且由于它們是同一個cursor_x，所以我們也許會互相干擾。"所以我沒有人們使用全局變量時總出現的問題。我以與查看全局變量相同的不信任級查看包說明或包體中的游標（這些游標不是在過程中定義的，但有全局作用域）；多個過程訪問這些全局變量產生副作用的可能性太高了。只是在別無選擇時我才使用全局變量。
總而言之，優先選擇的順序為：
1.不用游標（select into, for x in ( select..... ）
2.不管出于什么原因被迫使用游標時都聲明局部游標，如：
a. 需要使用LIMIT子句的批量綁定
b. 引用游標（ref cursors）
我建議不要在包說明中聲明全局游標，理由是：
這會喪失封裝的良好特性。游標可以全局訪問，任何能訪問該包的人都能看到它。
這多少會降低一些性能（我強調多少，而且這不是主要方面）。
會降低包體的可讀性。
一般來講，使用全局參數是編寫代碼時應盡量避免的一個不好的習慣。
在Oracle9i數據庫中切換UNDO表空間
我這樣理解，假如我用ALTER SYSTEM命令將UNDO從一個表空間切換到另一個表空間，Oracle實際上只有在所有使用第一個表空間的活動事務都被提交或回滾后才切換到另一個表空間。我的理解對嗎？另外，假如我想知道在第一個UNDO表空間中哪些事務是活動的，我該怎么辦？任何視圖或查詢都會有用。
你的理解不正確。 Oracle會馬上開始使用另一個UNDO表空間。下一個例子很好，它不僅證實了這一點，而且還為你提供所需要的查詢，這樣你就可以查看誰在使用要啟動的UNDO表空間中的哪個回滾段。
我要做的是先開始某一會話中的一個事務，但不用COMMIT命令提交。我通過查詢來看一看哪些會話正在使用哪些表空間中哪些回滾段。然后，我發出ALTER SYSTEM命令以
切換UNDO表空間，執行會話中的另一個事務，再執行查詢看一下誰在使用哪一個UNDO表空間。這時我要查看一下舊UNDO表空間中的舊事務和新UNDO表空間中的新事務。首先，我要看一看誰在使用什么。該查詢將V$session（得到會話信息）與V$TRANSACTION（只報告有活動事務的會話）及DBA_ROLLBACK_SEGS（呈交回滾段信息，如名稱和表空間）連接起來：
select a.username,
b.segment_name,
b.tablespace_name tspace
from v$session a,
dba_rollback_segs b,
v$transaction c
where a.taddr = c.addr
and b.segment_id = c.xidusn
/
USERNAME SEGMENT_NAME TSPACE
-------- ------------ -------
OPS$TKYTE _SYSSMU11$ UNDO
這說明有一個事務是活動的，它使用名為UNDO的UNDO表空間。現在我要切換UNDO表空間：
alter system
set undo_tablespace = undo2;
現在我在這個會話中開始另一個事務：
update dept set deptno = deptno;
2 rows updated.
select a.username,
b.segment_name,
b.tablespace_name tspace
from v$session a,
dba_rollback_segs b,
v$transaction c
where a.taddr = c.addr
and b.segment_id = c.xidusn
/
USERNAME SEGMENT_NAME TSPACE
-------- ------------ -------
OPS$TKYTE _SYSSMU11$ UNDO
OPS$TKYTE _SYSSMU16$ UNDO2
這時我看到這兩個表空間都在被使用。我還不能撤消UNDO表空間，因為它有活動事務，但它不能用于任何新的事務。
此時你也用到了你想要的查詢。
隨機性
怎樣用一條SQL語句在1到49之間創建6個各不相同的隨機數？
假如你有時從過程的角度考慮SQL是"基于集"的，你就能更深入地使用SQL。SQL被認為是一種非過程語言，但有時我發現，假如我考慮某些過程化的需要，它們也能幫助我設計一個查詢。
為了解答你這個問題，我需要：
生成一個從1到49的數字集合。我要從這個數字集合中抓取6個隨機數。
將這49個數隨機排序。這有點類似于為這49個數中的每個數分配一個隨機數，然后按照它們排序。
取結果集的前6個數。
為了生成由49個數組成的集合，我只需一個至少有49行的表。我發現ALL_OBJECTS是一個非常安全的表，能用于這種場合。它里面始終有至少1000行，而且在各種系統上人人都能訪問它。
首先，我需要創建由49個數組成的集合。這條SQL查詢很簡單：
select rownum r
From all_objects
where rownum < 50
這樣便會生成數字1、2、3、……、49。接下來，我需要用這個集合并將它隨機排序。我會使用一個內聯視圖來完成這件事。在下面的語句中，用上面的查詢代替QUERY這個詞：
select r
from ( QUERY )
order by dbms_random.value
此時，假如你在SQL*Plus中反復運行order by dbms_ random.value查詢，你會發現總能得到49行，而且每次執行查詢都返回不同的順序。
現在我只需取前6個數。我要使用另一個內聯視圖將前面查詢的結果限制在前6行。完整的查詢是：
select r
from
( select r
from
( select rownum r
from all_objects
where rownum < 50 )
order by dbms_random.value )
where rownum <= 6
/
R
-----
8
20
32
&nbs
p;12
44
26
6 rows selected.
假如我再執行一次，將會得到6個不同的數。
Tom Kyte (thomas.kyte@oracle.com) 從1993年起一直在Oracle工作。Kyte是負責Oracle 治理、教育和保健集團的副總裁，也是"Effective Oracle by Design"（Oracle 出版社出版）和"EXPert One-on-One： Oracle"（APRess出版）兩書的作者。
