Oracle 11g 學習筆記 (5)

一些 ASP.NET + Oracle 11g 邊學邊寫的隨筆。本系列文章可協助對 Oracle 不熟悉、甚至完全不會使用的 .NET 程式員，能在最短時間內上手並開發應用程式，避免像版工我一樣花了大把時間翻書、查檔案、撰碼實作和測試、發問等回複。本帖內容包括：Oracle 的「Null 字元串 = NULL」特性、Oracle 的 Transaction Isolation Level、Oracle 的備份 & 還原。

(十) Oracle 小技巧

* 在 SQL Plus 中，用「desc」指令，可瀏覽資料表的 schema，如：desc table1;

* 不論是用 SQL Plus，還是 Oracle SQL Developer 工具，在執行完 INSERT、UPDATE、DELETE 語句後，都必須再執行一次「Commit;」指令，才會真正寫入資料庫，這點和他牌的資料庫不同；但若是透過 ASP.NET 應用程式寫入 Oracle，則不必加上此一動作。若某個人透過 SQL Plus 或 Oracle SQL Developer 寫入 Oracle 之後，未再執行「Commit;」或「Rollback;」指令，會造成該筆記錄被鎖住，別人都無法「修改」該筆記錄 (使用者的應用程式或瀏覽器會一直處於等待狀態)，但仍可「讀取」。

------------------------------------------------------------------------------------------

 

(十一) Oracle 裡沒有「Null 字元串」這種東西

Oracle 雷根本沒有「Null 字元串 ('')」這種東西，而會將Null 字元串視為 Null，但在 SQL Server、其它廠牌的資料庫和 ANSI SQL 92 的定義卻並非如此。

當您將 Oracle 裡一個表的 varchar2 欄位，更新成Null 字元串時，如下：
UPDATE table1 SET col1='' [WHERE id=80];
在 Oracle 中會等同於：
UPDATE table1 SET col1=NULL [WHERE id=80];

此時，當您用如下的 SQL 陳述式去 SELECT 時，會查不出任何資料 (但用其它廠牌的資料庫則查得到)：
SELECT * FROM table1 WHERE col1='';

若改成如下，用 IS NULL 去當查詢條件，才查得到資料 (但用其它廠牌的資料庫則查不到)：
SELECT * FROM table1 WHERE col1 IS NULL;

即使您改用 ADO.NET 的 OracleDataReader.IsDBNull 方法，寫 .NET 應用程式去判斷，結果也是一樣。

此時，當您用 LENGTH 函數去判斷 col1 欄位的長度，會返回 Null，而不是 0，如下：
SELECT LENGTH(col1) FROM table1 WHERE id=80;

這些 Oracle 的「Null 字元串」特性，和其它廠牌的資料庫，以及 ANSI SQL 92 的定義都不同。

參考檔案：
http://www.adp-gmbh.ch/ora/misc/null.html
http://blog.darkthread.net/blogs/darkthreadtw/archive/2008/04/01/empty-string-in-oracle.aspx

------------------------------------------------------------------------------------------

 

(十二) Oracle 的「Transaction (事務；交易)」隔離層級

SQL Server 的 Isolation Level (事務隔離層級) 有七種，並可和 ADO.NET 做搭配；而 Oracle 的 Isolation Level 只有兩種，分別為：ReadCommitted、Serializable，但 Oracle 的 ReadCommitted，和 SQL Server 的 ReadCommitted，名稱雖然相同，但在功能和定義上皆不同。Oracle 的 ReadCommitted，類似於 SQL Server 2005 的「快照隔離 (Snapshot)」。

ReadCommitted 是 SQL Server 預設的 Isolation Level，其特性為當某一筆記錄在「 修改 (Edit;Update)」時，別人都無法「讀取」；ReadCommitted 也是 Oracle 預設的 Isolation Level，但其特性和 SQL Server 的版本完全不同，Oracle 的版本為，當某一筆記錄在「修改」時，別人仍可以「讀取」，但不能「修改」(該筆記錄會被鎖定，直到正在修改的那個人 Commit 或 Rollback)。

Oracle 的 ReadCommitted，其特性為，當一筆記錄在被「修改」時，若有別人剛好也來「讀取」這一筆記錄，Oracle 會將這筆記錄上一次被 Commit 的版本先取出來給其它人讀取，避免造成「共用鎖定定」，亦即避免讓其它人的瀏覽器或應用程式一直處於等待中，而無法進行其它的工作。

ADO.NET / Visual Studio 中用來聯機 Oracle 的 Data Provider，不論是 OleDb、OracleClient、ODP.NET 皆是如此，和 SqlClient 的 Isolation Level 完全不同。

參考檔案：
http://www.programmer-club.com/pc2020v5/forum/ShowSameTitleN.asp?URL=N&board_pc2020=oracle&index=3&id=3985&mode=&type_pc2020=sametitleLevel-2
http://www.oracle.com.cn/viewthread.php?tid=54983&extra=page%3D109%26amp%3Bfilter%3D0%26amp%3Borderby%3Ddateline%26amp%3Bascdesc%3DDESC
http://cherishchen.javaeye.com/blog/183229
http://msdn.microsoft.com/zh-tw/library/system.data.oracleclient.oracletransaction.isolationlevel.aspx
http://www.oracle.com/technology/global/cn/pub/articles/price_dbtrans_dotnet.html
http://blog.darkthread.net/blogs/darkthreadtw/archive/2007/6/22.aspx

------------------------------------------------------------------------------------------

 

(十三) Oracle 的備份、還原方式

Oracle 的「備份、還原」，有下列四種方式：
* Cool Backup (Offline Backup)
* Hot Backup (Online Backup)
* RMAN
* Exp / Imp

到了 Oracle 10g 時，引入了新的 Data Pump 工具，且效能比舊的 Exp / Imp 要好，因其不必經由 Session 進行，但使用上較麻煩，必須先建立備份用的相關目錄。其指令如下：
expdp userid=system/密碼 tables=xxx,yyy directory=abc_dir => abc_dir: 此為 Oracle 目錄，要先建立
impdp userid=system/密碼 dumpfile=EXPDAT.DMP directory=abc_dir

而另一種最古老的 Emp / Imp 只能算是一種轉儲存工具，執行速度慢，只適合小型資料庫。版工我測試結果，用 Export 指令備份 Oracle 11g 的整個 orcl 資料庫，裡面只有十八個建立的資料表 (每個表只有十多筆，到最多一兩百筆記錄)，就花了半個多小時；Import 還原指令更慢，跑了一個多小時還沒跑完，跑到隔天到公司上班才跑完。其指令如下，但它不是在 SQL Plus 裡下指令，以 Windows 而言，是要在「命令提示字元 (cmd.exe)」裡去下指令 (指令最後面不用加「分號」)：
exp system/密碼@orcl file=c:\orcl_081207.dmp full=y
imp system/密碼@orcl full=y ignore=y file=c:\orcl_081207.dmp

如此即會匯出整個 orcl 資料庫，包括：Index、Sequence、…等等。其中的參數，ignore=y 表示當匯入時，要覆蓋同名的資料表；若沒加此參數，則同名的表就不會匯入。

若要讓 Exp / Imp 跑快一點，就不要下 full=y 的參數，改用 user mode (指定 owner=xxxx ) 的備份會比較快。此外，亦可只匯出或匯入某一個或某幾個資料表，如下 (最後面不用加「分號」)：
exp system/密碼@orcl file=c:\orcl_081207.dmp tables=(table1,table2)
imp system/密碼@orcl file=c:\orcl_081207.dmp tables=(table1)

若想看到指令的 help，可用如下的 help 參數：
exp -help
imp -help

此外，要執行 Emp / Imp 前，TNS (tnsnames.ora) 必須先正確設定。以 Windows Server 2003、Oracle 11g 而言，該檔案位於下列目錄：
C:\app\Administrator\product\11.1.0\db_1\NETWORK\ADMIN

tnsnames.ora 我的設定如下：

ORCL_S =
　(DESCRIPTION =
　　(ADDRESS_LIST =
　　　(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521))
　　)
　(CONNECT_DATA =
　　(SID = ORCL)
　)
)

亦可透過 Oracle 內建的圖形介面工具「Net Manager」做設定。注意上述的 SID 一定要設定 (設成資料庫的名稱)，但 Oracle 剛安裝好時，SID 不一定會自動加上。設定好後，可用「tnsping 服務名稱」指令，來測試是否有設定正確，以上述的 tnsnames.ora 而言，可在 Windows 作業系統的「命令提示字元」裡，像這樣下 tnsping 指令 (最後面不用加「分號」)：
tnsping ORCL_S

參考檔案：
http://big5.webasp.net/article/21/20961_print.htm
http://www.blueshop.com.tw/board/show.asp?subcde=BRD20081202144229DSV&fumcde=FUM200410061527578K1&rplcnt=11

------------------------------------------------------------------------------------------

 

(十四) Oracle 的預存程序，與 ASP.NET 的「分頁」應用程式撰寫

過去版工在用 SQL Server 和 Sybase 資料庫，搭配 ASP.NET 2.0 程式撰寫時，為了應付 GridView 控制項的「分頁」功能處理，都會寫一種用來處理「分頁」的 Stored Procedure，可參考本站的幾篇系列文章 - ASP.NET 的資料分頁：

http://www.cnblogs.com/WizardWu/archive/2008/09/28/1301616.html
http://www.cnblogs.com/WizardWu/archive/2008/09/07/1286270.html
http://www.cnblogs.com/WizardWu/archive/2008/08/06/1261589.html
http://www.cnblogs.com/WizardWu/archive/2008/08/02/1258832.html

SQL Server 和 Sybase 資料庫的預存程序，都可直接 return 一個 ResultSet 格式的資料 (亦即有多行多列的表格式資料，如同 ADO.NET 中的 DataTable)，給呼叫 (調用) 它的應用程式，如下所示，但 Oracle 卻不能直接這樣 return：

CREATE PROCEDURE dbo.pager_SqlServer2005
AS
　SET @sqlDataTable = 'SELECT * FROM table1'
　exec (@sqlDataTable)
RETURN

Oracle 有它另一套做法，只能透過 REF CURSOR 類型，去處理和返回「結果集」，但聽說這樣的效能並不好。不知為何 Oracle 幹麻要這樣自己搞另一套 (包括先前提到的「Null 字元串」和 Null)，非得和其它廠牌的資料庫與眾不同才高興，也搞得所有程式員得另外學習一套標準。有關 Oracle 的 Stored Procedure 和 REF CURSOR，可參考下列檔案：

http://msdn.microsoft.com/zh-cn/library/ms971506.aspx
http://msdn.microsoft.com/zh-cn/library/4s2zbbsz(VS.80).aspx
http://msdn.microsoft.com/zh-tw/library/4s2zbbsz(VS.80).aspx (同上，但此為繁體中文版本)
http://support.microsoft.com/kb/308072/zh-tw
http://big5.chinaz.com:88/www.chinaz.com/Program/.NET/0H512HH007.html
http://www.phpq.net/oracle/oracle-stored-procedure-tutorial.html
http://www.bccn.net/Article/sjk/oracle/200709/6126.html
http://blog.csdn.net/dacula/archive/2005/03/01/306566.aspx

至於 ASP.NET 的 GridView「分頁」功能，版工我是放棄 Oracle 的預存程序，改自己寫一個 .NET 的函數，去接收 ObjectDataSource 控制項傳來的參數 (要擷取第幾筆到第幾筆的記錄)，將原本可在預存程序中處理的工作，搬到 .NET App_Code 檔案夾裡的 DAL Layer 來處理。

------------------------------------------------------------------------------------------