Oracle程式開發小技巧(一)

文章目錄

•     Oracle有許多的優點，但是如果使用不當，同樣也不能發揮出它的強大優勢，下面就本人在Oracle開發過程中積累的一點小經驗，和大家做一個分享。

    Oracle有許多的優點，但是如果使用不當，同樣也不能發揮出它的強大優勢，下面就本人在Oracle開發過程中積累的一點小經驗，和大家做一個分享。

一、 資料庫設計中欄位的使用

    在一些表的設計中，有一些常用的這段，已經基本成為一個規範，在大型系統中，多可以看到這些欄位的蹤影，當然欄位的名字可能有所不同。常用的欄位分為以下幾類：

1.WHO欄位

    這類欄位多用於記錄每行記錄的操作變更資訊，比如是誰添加的這行記錄，誰做過修改等，詳細說明如下：

欄位名稱	類型	說明
LAST_UPDATE_DATE	DATE	最後修改日期
LAST_UPDATED_BY	NUMBER(15)	最後修改人
CREATION_DATE	DATE	建立日期
CREATED_BY	NUMBER(15)	建立人

    I．建立人

    在任何一個系統中，一般都會有一個許可權驗證和登入的過程，在登入後，會在系統的記憶體中記錄登入人的資訊，當此人在對資料庫的某個表進行添加操作的時候，會同時把這個操作人員的ID值寫入表中，供後期統計及審計

    II．建立時間

    與建立人的含義類似，在建立的同時，寫入系統的目前時間，這個欄位的值一般取自於伺服器而不是用戶端，比如在oracle中，可以直接使用SYSDATE作為這個欄位的值

    III．最後修改人

    一條記錄被建立後，同樣會有被修改的可能性，這裡需要對修改的人進行一個記錄，以便於後期審計。

    但是這裡要注意的是，這裡只記錄最後的修改資訊，如果一條記錄經過多次的修改，中間的修改將無跡可尋，如果需要記錄詳細修改資訊，需要使用日誌功能，已經超出此欄位的功效。

    IV．最後修改日期

    與最後修改人同時寫入記錄中，同理，也是記錄最後一次修改的時間，中間的修改均被最後一次覆蓋。

2． 狀態及有效期間欄位

    在一些新聞類的內容中，經常會涉及到一個時效性，即一條新聞可能只在某一個時間段內是對外可見的，超過這個時間段則將不允許發布，還有就是有些時候如果發現某些內容有問題，需要暫時對外屏蔽的時候，就可以用到狀態欄位，詳細說明如下：

欄位名稱	類型	說明
STATUS	NUMBER	狀態
START_DATE	DATE	有效開始日期
END_DATE	DATE	有效結束日期

    I．狀態

    這個欄位一般用數值型表達，0表示失效，1表示有效，當然在使用的時候，可以把這兩個值進行轉義，用“有效”和“失效”來顯示，不影響儲存方式。

失效不等於刪除，經過失效的內容，經過管理程式的調整，把狀態變成有效後，還可以恢複正常使用。

    II．有效開始日期

    這個欄位如果填有具體的值，只有當時間超過這個時間後，資訊才是有效，在這個日期之前，資訊將自動按無效處理，特別需要注意的是，如果這個欄位置空，應該按跳過這個條件檢查來處理，這樣可以實現程式的靈活性。

    III．有效結束日期

    具體含義同上，只是超過此期，內容將按失效處理。

3．邏輯刪除

    在資料庫系統中處理的刪除的做法一般有兩種：物理刪除和邏輯刪除，所謂物理刪除，就是在資料庫中直接使用delete等命令，從資料庫裡把資料真正的刪除，這種刪除從正常途徑將無法恢複資料，雖然可以部分程度減小整體的資料量，但不利於審計跟蹤；邏輯刪除指的是對資料不做任何刪除處理，而是對記錄打個標記，也就是在某個欄位上賦值，表示此記錄已經刪除。

    邏輯刪除的處理邏輯只由應用程式自己使用，因為資料在資料庫中實際還是存在的。

    所涉及欄位如下：

欄位名稱	類型	說明
DELETED	NUMBER	是否刪除
DELETE_DATE	DATE	刪除時間
DELETED_BY	NUMBER	刪除人

    I． 刪除標誌

    此欄位有兩個取值,0表示正常，1表示已經刪除。

    打上刪除標誌後，所以的查詢語句必須同時在判斷條件的地方加上deleted=0的條件，否則會造成重大的失誤。

    II． 刪除時間

    與刪除標誌一起使用，表示刪除的時間，可以使用當前伺服器的時間，用sysdate來填充即可。

    III． 刪除人

    與最後修改人類似，需要記錄刪除的具體操作人員

    刪除與標誌位不同，採用標誌位的方式，雖然前端無法展示資料，但是後台管理員人一樣可以通過管理程式來調整該狀態，但是刪除標誌則不同，確認刪除後，對整個應該來講，這條記錄都應該理解為不存在了。

4． 自增欄位

    所謂自增欄位，是指隨著使用，它可以自動增加的欄位。

    這種欄位的值一般沒有明確的含義，只用於一個唯一標識，這個欄位一般也會設定成主鍵。

    如果應用只針對Oracle，而不考慮資料庫無關性，那麼sequence無非是最好的一個選擇。對於以前用習慣MSSQL等其它資料庫的朋友來說，Oracle這種用法簡直是太費勁了，要花很大的功夫才能做好一個自增欄位，但是正因為如此，它也帶來了其它資料庫不能相比的優點，舉例來說，一個訂單系統，即有訂單頭，又有訂單行，一般是先插入訂單頭，再插入訂單行，對於MSSQL等資料庫的自增欄位，只有插入後才知道具體的ID值是多少，那麼寫入後，還要返查一下這個欄位值再給訂單行使用；而對於oracle來說，只要先從sequence裡取出一個值來，頭和行一起使用即可，最主要是的sequence的效果是非常高的，不需要擔心效能問題。

5． 彈性欄位

    在資料庫表結構設計的時候，最好多留出幾個備用的欄位來，因為隨著系統的使用，一般會有增加欄位的需求。預留欄位的好處是，只要需要的時候啟用即可，不需要進行DDL操作，對資料庫後期維護的風險很低，並且一般的DDL操作，會造成級聯的VIEW/PACKAGE等程式失效，預留了彈性欄位，則不會有這個問題。

    預留的欄位也可以按類型分成三種：字串型，數值型及日期型，可以每個類型預留10個，或根據需要來決定，可以採用如下的樣式：

    NUMBER_ATTRIBUTE1

    STRING_ATTRIBUTE1

    DATE_ATTRIBUTE1

    彈性欄位如果不啟用，會不會佔用過多的儲存空間呢，答案是否定的，因為在這種大型資料庫的結構中，只有一個欄位真正被用到的時候，才會去佔用實際的空間，否則它只是一個“說明”，並不佔用實際的空間，所以不會造成空間浪費。

6． 拆分欄位

    這並不是一個欄位的類型，而是指在表設計的時候，可以適當的把一個大表拆成不同的小表來儲存，比如使用者表，可以包括登入名稱，密碼，姓名，生日，等一系列的欄位，在某些情況下，包括的會員屬性可能達到上百個之多。

    在資料量小的時候，無論怎麼樣的儲存，都不會有效能問題，但是當資料量比較大的時候，就必須考慮效能問題。如果索引比較合理，不管資料量多大，一般查詢速度都不會太慢，但是當某些特別情況，不能使用索引的時候，就會產生FTS（所謂的全表掃描），那麼掃描一個小表和掃描一個大表所佔的時間就完全不一樣了，所以建議比較大的表分開儲存，把常用的幾個欄位單獨提取出來，這樣即便全表掃描，也能比較好的控制效率。

    在使用的時候，只要主表和子表都有索引，把它們聯合起來查詢，和一個真正的大表的效果基本上是一樣的，雖然效能肯定比一個真實的大錶慢一點，但是和另一方面的效能提升比較起來，是值得的。

    目前有些大的系統採用這種拆分方式

二、 合理使用視圖

    視圖是對基表的一個展現形式，它並不在物理上儲存資料，只是在需要的時候去基表中調用資料，可以理解為把一個SQL封裝起來方便使用的工具。

視圖的優點有很多：

1． 方便性

    如果一個查詢很複雜，在程式中引用的時候，會非常不方便，特別是一個程式中反覆調用這個SQL的時候，會使用程式顯的非常臃腫，如果把SQL封裝成視圖，則會使整個程式顯得非常清爽乾淨。

2． 靈活性

    當一個表被多個程式引用的時候，如果表的結構發生變化，那麼所有的程式都需要去做相應的調整，工作量非常大，而如果此時程式中只引用的是一個視圖的話，我們只需要對該視圖進行正確的修改，則所有的程式都不會有任何問題了。

3． 安全性

    視圖可以僅包括一個基表的有限幾個欄位，這樣在公開許可權的時候，使用該視圖的使用者，就能查看基表的其它保密欄位了。

視圖的優點比較多，建議在實際工作中多用視圖。

三、 PACKAGE

    PACKAGE即程式包，它的特點是可以在一個包中同時包括變數、函數及預存程序，並且在整個包內，所有的公開變數可以共用，這點是它最方便的地方。

    包還有一個特點是對外介面變得更簡單，不管包內部如何複雜，只要對外開放有限的介面即可。

    包的這些特點在任何文檔中都可以找到，不再多說，這裡只強調一點，是關於包的失效，在aspx的程式引用oracle的package的時候，因為系統緩衝的原因，它會記錄package的失效狀態，如果一個package的狀態已經從失效重新編譯為有效，那麼IIS的cache記錄的狀態有可能不會自動更新，仍然按失效處理，這時就會造成系統的錯誤。為了避免這種錯誤的發生，我一般在package裡加一個公用函數，取名為STATUS，此函數僅僅簡單的返回一個數字1，在調用package其它函數之前，先調用這個函數來檢查package的目前狀態，如果能正確返回1，則可以繼續後面的操作，如果返回為空白，則表示當前package有問題，可以有一個互動來提示使用者進行處理。當您的一個交易處理需要多個package協調處理的時候，如果前面的package已經處理成功，而後面的不能通過的時候，是一件很痛苦的事情，因為可能造成事務的不完整性，所以這時最需要提前檢查一下所有的package的狀態，把所有可能存在的危險都提前排除掉。

四、 索引

    索引的作用很明顯，可以大大的提高檢查的速度，特別是對於大表來說，如果沒有索引，就會需要全表掃描，這是一個非常佔用時間的動作，所以需要建立相應的索引來提速。

    但是在有些時候，索引會帶來負面的影響，比如大量的插入或修改刪除操作等，因為每個小的動作都會同時連帶修改索引，會大大降低DML操作的效率，因此，在大量的DML操作之前，建議先把索引刪除，操作完成後，重建立立索引，從總的時間上來講，可以節約很多。