關(guān)于約束、CASE語句和游標(biāo) (作者：Tom Kyte )

zhouok

關(guān)于約束、CASE語句和游標(biāo)

作者：Tom Kyte

我們的技術(shù)專家談完整性、時(shí)間和歸屬問題。

我仔細(xì)閱讀了《Oracle9i 數(shù)據(jù)庫概念手冊(cè)（Oracle9i Database Concepts Manual）》和你們的站點(diǎn)，但對(duì)下述概念仍不明白：

"定義為可延遲（deferrable）的約束可以指定為：

1. initially immediate（初始化立即執(zhí)行）或
2. initially deferred（初始化延遲執(zhí)行）。"

我知道什么是延遲約束，但不明白什么叫"初始化立即執(zhí)行的可延遲約束"和"初始化延遲執(zhí)行的可延遲約束"。請(qǐng)解釋二者的區(qū)別。還有，這些約束有什么用途？這是通常容易混淆的問題。我希望下面的例子能解釋清楚。初始化立即執(zhí)行/延遲執(zhí)行規(guī)定了在默認(rèn)情況下應(yīng)該如何執(zhí)行約束：


初始化立即執(zhí)行--在每條語句執(zhí)行結(jié)束時(shí)檢驗(yàn)約束


初始化延遲執(zhí)行--一直等到事務(wù)完成后（或者調(diào)用set constraint immediate語句時(shí)）才檢驗(yàn)約束

來看下面的代碼：


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.

所以，當(dāng)兩個(gè)約束同時(shí)滿足時(shí)才能正確無誤地插入行。但是，如果我試圖插入違反CHECK_X約束（初始化立即執(zhí)行的約束）的行，則系統(tǒng)會(huì)立即檢驗(yàn)約束，并得到下面的結(jié)果：


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是可延遲但初始化為立即執(zhí)行的約束，所以這一行立刻被拒絕了。而CHECK_Y則不同，它不僅是可延遲的，而且初始化為延遲執(zhí)行，這就意味著直到我用COMMIT命令提交事務(wù)或?qū)⒓s束狀態(tài)設(shè)置為立即執(zhí)行時(shí)才檢驗(yàn)約束。


SQL>; insert into t values ( 1,-1);
1 row created.


現(xiàn)在它是成功的（總之到目前為止是成功的）。我將約束檢驗(yàn)延遲到了執(zhí)行COMMIT的時(shí)候：


SQL>; commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint
(OPS$TKYTE.CHECK_Y) violated

此時(shí)數(shù)據(jù)庫將事務(wù)回滾，因?yàn)檫`反約束導(dǎo)致了COMMIT語句的失敗。這些語句說明了初始化立即執(zhí)行與初始化延遲執(zhí)行約束之間的區(qū)別。initially（初始化）部分指定Oracle什么時(shí)候會(huì)進(jìn)行默認(rèn)的約束檢驗(yàn)--是在語句結(jié)束時(shí)［immediate（立即執(zhí)行）］，還是在事務(wù)結(jié)束時(shí)［deferred（延遲執(zhí)行）］。我還要說明deferred（可延遲）子句有什么用。我可以發(fā)出命令，讓所有可延遲的約束變?yōu)檠舆t執(zhí)行的。注意，你也可以對(duì)一個(gè)約束使用該命令；你不必讓所有可延遲的約束都變?yōu)檠舆t執(zhí)行的：


SQL>; set constraints all deferred;
Constraint set.

SQL>; insert into t values ( -1,1);
1 row created.


由于將初始化立即執(zhí)行的約束設(shè)置為延遲執(zhí)行的模式，這個(gè)語句似乎執(zhí)行成功；但是，當(dāng)我用COMMIT語句提交事務(wù)時(shí)，看一下會(huì)發(fā)生什么：


SQL>; commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint
(OPS$TKYTE.CHECK_X) violated


事務(wù)提交失敗并回滾，因?yàn)樵贑OMMIT語句之后對(duì)約束進(jìn)行了檢驗(yàn)。相反，我可以將初始化為延遲執(zhí)行的約束變?yōu)?quot;立即"執(zhí)行的約束：


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


前面在我提交前能執(zhí)行的語句現(xiàn)在立即出了問題。因?yàn)槲沂謩?dòng)修改了默認(rèn)的約束模式。

延遲約束有哪些實(shí)際用處呢？ 有很多。它主要用于物化視圖（快照）。這些視圖會(huì)使用延遲約束來進(jìn)行視圖刷新。在刷新物化視圖的過程中，可能會(huì)破壞完整性，而且將不能逐句檢驗(yàn)約束。但到執(zhí)行COMMIT時(shí)，數(shù)據(jù)完整性就沒問題了，而且能滿足約束。沒有延遲約束，物化視圖的約束可能會(huì)使刷新過程不能成功進(jìn)行。

使用延遲約束的另一個(gè)普遍原因是，當(dāng)預(yù)測(cè)是否需要更新父/子關(guān)系中的主鍵時(shí)，它有助于級(jí)聯(lián)更新。如果你將外鍵設(shè)為可延遲、但初始化為立即執(zhí)行，那么你就可以


將所有約束設(shè)置為可延遲。


將父鍵更新為一個(gè)新值--至此子關(guān)系的完整性約束不會(huì)被驗(yàn)證。


將子外鍵更新為這個(gè)新值。


COMMIT--只要所有受更新影響的子記錄都指向現(xiàn)有的父記錄，這條命令就能成功執(zhí)行。

如果沒有延遲約束，這一更新過程將極為艱難。參見asktom.oracle.com/~tkyte/update_cascade/index.html中給出的沒有這一特性時(shí)進(jìn)行級(jí)聯(lián)更新所必需的示例代碼！

此外，你可以在各種多語句事務(wù)中使用延遲約束，這些事務(wù)在處理的過程中需要暫時(shí)破壞完整性，但最后它們都會(huì)物歸原樣。

如何計(jì)算時(shí)間

你是如何計(jì)算asktom.oracle.com第一頁中AGE列顯示的時(shí)間的？我之所以問這個(gè)問題是因?yàn)槲铱吹剿�有多種格式，如9個(gè)月3小時(shí)；2.3年；19個(gè)小時(shí)；等等。我是Oracle新手，想知道你們?cè)谑褂媚姆N日期計(jì)算法。

我就是使用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語句，則會(huì)出現(xiàn)一個(gè)錯(cuò)誤消息，因?yàn)镻L/SQL語法分析程序不識(shí)別CASE語句。（請(qǐng)注意，在Oracle9i不存在這樣的問題。）為了避開Oracle8i的限制，你可以


將CASE語句隱藏在視圖當(dāng)中，并用PL/SQL來查詢視圖。


使用嵌套的DECODE語句來代替CASE語句。

我本人愿意使用視圖，但讀者M(jìn)artin Burbridge在asktom.oracle.com 網(wǎng)站上公布了下面這段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語句的功能完全相同--只是不太明顯。

文件放在哪？

我正在考慮為一個(gè)應(yīng)用程序設(shè)計(jì)些選項(xiàng)，利用它用戶可以上傳和存儲(chǔ)可供他人下載的文檔。文檔可以是平均大小為150K的Microsoft Word文檔。最初需要（從CD）移植18000到20000個(gè)文檔，當(dāng)使用該應(yīng)用程序時(shí)存儲(chǔ)數(shù)量會(huì)增加到大約25000個(gè)文檔。瀏覽器前端是用于上傳和下載的PL/SQL插件（PL/SQL cartridge）頁面。一開始，會(huì)有400到500人幾乎同時(shí)訪問該應(yīng)用程序，兩周內(nèi)每天將有300人訪問（分散訪問）。 文檔本身在數(shù)據(jù)庫中作為BLOB存儲(chǔ)。

從使用方面考慮，你覺得這樣的選項(xiàng)好嗎？它會(huì)過多占用系統(tǒng)全局區(qū)（SGA）嗎？考慮到應(yīng)用程序的需求以及前端（基于瀏覽器），除了保存為BLOB，還有沒有其他選擇，如文件系統(tǒng)？

我什么都存在數(shù)據(jù)庫里。就是這樣。如果數(shù)據(jù)就是你的一切，無論它們有什么樣的值，事實(shí)上都要放到數(shù)據(jù)庫中，在那里數(shù)據(jù)可以得到專業(yè)化的管理、備份，恢復(fù)而且安全。除了這些實(shí)實(shí)在在的好處，你還可以索引及搜索文檔。（誠然，用文件系統(tǒng)也可以做這些，但在索引和文檔之間不存在完整性。）在數(shù)據(jù)庫中，你可以轉(zhuǎn)換文檔格式（例如，上傳一個(gè)DOC文件，而顯示為HTML格式）。你的數(shù)據(jù)是完全集成的、安全的、有備份的而且隨時(shí)供你使用。

在Oracle公司內(nèi)部，我們將一個(gè)幾千吉字節(jié)的數(shù)據(jù)庫作為整個(gè)公司的一個(gè)單一的文件服務(wù)器。公司所有文檔都存在那里，存在這樣一個(gè)單一的地方，可以對(duì)這些文檔進(jìn)行備份、搜索、建立索引和訪問。在常規(guī)的文件系統(tǒng)中管理成千上萬的文檔是不可能的，即便文件系統(tǒng)能存下這些文檔。

至于SGA的問題，這完全在你。如果你不想把BLOB放在緩沖區(qū)里，可以對(duì)其使用NOCACHE，這樣你就不必?fù)?dān)心"過多占用"SGA的問題了。

游標(biāo)（Cursors）放在哪？

你能告訴我在編寫PL/SQL代碼時(shí)最好把游標(biāo)放在哪嗎？我們應(yīng)該把它們放在包說明中還是包體中？我問這些問題是因?yàn)楹臀乙黄鸸ぷ鞯囊幻�開發(fā)人員硬要把所有游標(biāo)都放在包說明中。他告訴我這樣做才對(duì)。的確，如果在包中不止一次使用這些游標(biāo)，我們應(yīng)該把它們放在包說明中。但這個(gè)包里的所有游標(biāo)都只使用一次，所以我認(rèn)為應(yīng)該把它們放到調(diào)用它們的過程/函數(shù)的聲明部分。我說得對(duì)嗎？把所有游標(biāo)都放在包說明中有什么優(yōu)缺點(diǎn)？游標(biāo)的放置位置影響性能嗎？

無論現(xiàn)在、過去還是將來我個(gè)人的偏好都是在大多數(shù)情況下使用隱式游標(biāo)，也就是說根本就不顯式地定義游標(biāo)！例如：


is
...
begin
  ....
  for x in ( select * from emp )
  loop

這個(gè)技巧用于大約50到100行以內(nèi)的結(jié)果集時(shí)特別好。比起顯式游標(biāo)來我更喜歡這種方法是因?yàn)椋?


與使用顯式游標(biāo)相比，使用它的CPU效率更高。


我可以瀏覽代碼，輕松地查看正在處理的數(shù)據(jù)。查詢過程就在我面前。


當(dāng)查詢變大時(shí)，我可以使用視圖。我在視圖中仍能查看正在查詢的數(shù)據(jù)，但是視圖的復(fù)雜性被隱藏了起來。我不是把它隱藏在游標(biāo)中，而是隱藏在視圖中。

有些時(shí)候你必須使用顯式游標(biāo)，最常見的是要處理更大的結(jié)果集以及在使用FETCH語句時(shí)需要使用BULK COLLECT以保證系統(tǒng)性能的情況。當(dāng)我必須使用顯式定義的游標(biāo)時(shí)，我選擇定義在過程自身內(nèi)（不僅在包體中，而且正好在包體的過程里）的局部游標(biāo)。為什么呢？


與使用全局游標(biāo)（在說明中定義）相比，使用它的CPU效率更高。


我仍能瀏覽代碼，并能輕松地查看正在處理的數(shù)據(jù)。


它使我可以使用視圖，因?yàn)椴樵兪窃谶^程中，而我不希望它影響到代碼的其余部分。


游標(biāo)屬于誰一目了然。

你理解這里的模式嗎？同樣，具有局部作用域（在過程中）的游標(biāo)使用后會(huì)自動(dòng)清除。沒有具有%isopen屬性的游標(biāo)垃圾弄亂我的代碼。 （我的代碼從沒用過isopen"特性"。）我不必?fù)?dān)心："你知道過程P1使用了cursor_x，我也使用了cursor_x，而且由于它們是同一個(gè)cursor_x，所以我們也許會(huì)互相干擾。"所以我沒有人們使用全局變量時(shí)總出現(xiàn)的問題。我以與查看全局變量相同的不信任級(jí)查看包說明或包體中的游標(biāo)（這些游標(biāo)不是在過程中定義的，但有全局作用域）；多個(gè)過程訪問這些全局變量產(chǎn)生副作用的可能性太高了。只是在別無選擇時(shí)我才使用全局變量。

總而言之，優(yōu)先選擇的順序?yàn)椋?

1.不用游標(biāo)（select into, for x in ( select..... ）

2.不管出于什么原因被迫使用游標(biāo)時(shí)都聲明局部游標(biāo)，如：
     a. 需要使用LIMIT子句的批量綁定
     b. 引用游標(biāo)（ref cursors）

我建議不要在包說明中聲明全局游標(biāo)，理由是：


這會(huì)喪失封裝的良好特性。游標(biāo)可以全局訪問，任何能訪問該包的人都能看到它。


這多少會(huì)降低一些性能（我強(qiáng)調(diào)多少，而且這不是主要方面）。


會(huì)降低包體的可讀性。


一般來講，使用全局參數(shù)是編寫代碼時(shí)應(yīng)盡量避免的一個(gè)不好的習(xí)慣。

在Oracle9i數(shù)據(jù)庫中切換UNDO表空間

我這樣理解，如果我用ALTER SYSTEM命令將UNDO從一個(gè)表空間切換到另一個(gè)表空間，Oracle實(shí)際上只有在所有使用第一個(gè)表空間的活動(dòng)事務(wù)都被提交或回滾后才切換到另一個(gè)表空間。我的理解對(duì)嗎？另外，如果我想知道在第一個(gè)UNDO表空間中哪些事務(wù)是活動(dòng)的，我該怎么辦？任何視圖或查詢都會(huì)有用。

你的理解不正確。 Oracle會(huì)立刻開始使用另一個(gè)UNDO表空間。下一個(gè)例子很好，它不僅證明了這一點(diǎn)，而且還為你提供所需要的查詢，這樣你就可以查看誰在使用要啟動(dòng)的UNDO表空間中的哪個(gè)回滾段。

我要做的是先開始某一會(huì)話中的一個(gè)事務(wù)，但不用COMMIT命令提交。我通過查詢來看一看哪些會(huì)話正在使用哪些表空間中哪些回滾段。然后，我發(fā)出ALTER SYSTEM命令以切換UNDO表空間，執(zhí)行會(huì)話中的另一個(gè)事務(wù)，再執(zhí)行查詢看一下誰在使用哪一個(gè)UNDO表空間。這時(shí)我要查看一下舊UNDO表空間中的舊事務(wù)和新UNDO表空間中的新事務(wù)。首先，我要看一看誰在使用什么。該查詢將V$SESSION（得到會(huì)話信息）與V$TRANSACTION（只報(bào)告有活動(dòng)事務(wù)的會(huì)話）及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


這說明有一個(gè)事務(wù)是活動(dòng)的，它使用名為UNDO的UNDO表空間。現(xiàn)在我要切換UNDO表空間：


alter system
  set undo_tablespace = undo2;


現(xiàn)在我在這個(gè)會(huì)話中開始另一個(gè)事務(wù)：


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


這時(shí)我看到這兩個(gè)表空間都在被使用。我還不能撤消UNDO表空間，因?yàn)樗�有活�?dòng)事務(wù)，但它不能用于任何新的事務(wù)。

此時(shí)你也用到了你想要的查詢。

隨機(jī)性

怎樣用一條SQL語句在1到49之間創(chuàng)建6個(gè)各不相同的隨機(jī)數(shù)？

如果你有時(shí)從過程的角度考慮SQL是"基于集"的，你就能更深入地使用SQL。SQL被認(rèn)為是一種非過程語言，但有時(shí)我發(fā)現(xiàn)，如果我考慮某些過程化的需要，它們也能幫助我設(shè)計(jì)一個(gè)查詢。

為了解答你這個(gè)問題，我需要：


生成一個(gè)從1到49的數(shù)字集合。我要從這個(gè)數(shù)字集合中抓取6個(gè)隨機(jī)數(shù)。  


將這49個(gè)數(shù)隨機(jī)排序。這有點(diǎn)類似于為這49個(gè)數(shù)中的每個(gè)數(shù)分配一個(gè)隨機(jī)數(shù)，然后按照它們排序。


取結(jié)果集的前6個(gè)數(shù)。
為了生成由49個(gè)數(shù)組成的集合，我只需一個(gè)至少有49行的表。我發(fā)現(xiàn)ALL_OBJECTS是一個(gè)非常安全的表，能用于這種場(chǎng)合。它里面始終有至少1000行，而且在各種系統(tǒng)上人人都能訪問它。

首先，我需要?jiǎng)?chuàng)建由49個(gè)數(shù)組成的集合。這條SQL查詢很簡(jiǎn)單：


select rownum r
  From all_objects
where rownum < 50


這樣便會(huì)生成數(shù)字1、2、3、……、49。接下來，我需要用這個(gè)集合并將它隨機(jī)排序。我會(huì)使用一個(gè)內(nèi)聯(lián)視圖來完成這件事。在下面的語句中，用上面的查詢代替QUERY這個(gè)詞：


select r
  from ( QUERY )
order by dbms_random.value


此時(shí)，如果你在SQL*Plus中反復(fù)運(yùn)行order by dbms_ random.value查詢，你會(huì)發(fā)現(xiàn)總能得到49行，而且每次執(zhí)行查詢都返回不同的順序。

現(xiàn)在我只需取前6個(gè)數(shù)。我要使用另一個(gè)內(nèi)聯(lián)視圖將前面查詢的結(jié)果限制在前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
                12
                44
                26

6 rows selected.

如果我再執(zhí)行一次，將會(huì)得到6個(gè)不同的數(shù)。

Tom Kyte (thomas.kyte@oracle.com) 從1993年起一直在Oracle工作。Kyte是負(fù)責(zé)Oracle 管理、教育和保健集團(tuán)的副總裁，也是"Effective Oracle by Design"（Oracle 出版社出版）和"Expert One-on-One： Oracle"（Apress出版）兩書的作者。


向Tom咨詢
asktom.oracle.com
Oracle副總裁Tom Kyte為你解答最棘手的技術(shù)問題。本專欄從該論壇中精選了部分重要內(nèi)容。

閱讀
Tom的更多文章
asktom.oracle.com/~tkyte
《高效的Oracle 設(shè)計(jì)（Effective Oracle by Design）》 shop.osborne.com/cgi-bin/oraclepress /0072230657.html

Oracle資料
所提供的PL/SQL包以及類型參考
otn.oracle.com/documentation

討論Oracle技術(shù)
oracle.com/forums