Oracle 11g 學習筆記 (3)

一些 ASP.NET + Oracle 11g 系統邊寫邊學的隨筆，包括 Oracle 的「ROWNUM」、「Sequence 流水號」。

(六) Oracle 的 ROWNUM，等同其它資料庫的 SELECT TOP

Oracle 不支援 SELECT TOP 文法，若要擷取最大的幾筆、或最小的幾筆記錄，必須用 ROWNUM 關鍵詞並搭配 Subquery。

例如要取最小的 10 筆，可用如下語句：
SELECT id, name, ROWNUM FROM (SELECT id, name FROM table ORDER BY id) WHERE ROWNUM <= 10;

若要取最大的 10 筆，就再加上 DESC：
SELECT id, name, ROWNUM FROM (SELECT id, name FROM table ORDER BY id DESC) WHERE ROWNUM <= 10;

此外，還可以做一些變化應用：
SELECT a.id, a.name, ROWNUM FROM (SELECT id, name FROM table ORDER BY id) a WHERE ROWNUM <= 10 ORDER BY ROWNUM DESC;

要注意的是，包含有 ROWNUM 的 WHERE 條件式，一定要包含 1，例如：
WHERE ROWNUM >0
WHERE ROWNUM >=1
WHERE ROWNUM <10
若不包含 1 的話，所下的查詢會永遠查無資料。

Oracle 的 ROWNUM、Top-N Query 官方教學 (英文)：
http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html
http://www.oracle.com/technology/oramag/oracle/07-jan/o17asktom.html

此外，Oracle 還有兩種 ROW_NUMBER() OVER 和 RANK() OVER 文法，亦可將已查詢出來的全部資料，再給予連續的流水號。雖然它們無法像 ROWNUM 一樣給 WHERE 條件式，但適
合用來撰寫 ASP.NET GridView 控制項的「分頁」功能的 Stored Procedure。有關其用法，可參考：
http://i.cn.yahoo.com/gaoxiaoqing2003/blog/p_8/
http://keke-wanwei.javaeye.com/blog/138632

 

(七) Oracle 的 Sequence，等同其它資料庫的 Identity

Oracle 不支援 SQL Server 和 Sybase 都支援的 Identity 自動增號欄位，也不支援 INSERT INTO 以後立即取得最新一筆記錄 Identity 號碼的「SELECT @@identity;」文法，
要達成上述功能，必須改用 Sequence (流水號)。

Sequence 不包含在 table 中，某一個 Sequence 亦不和 table 做一對一的對應。要用 Sequence，必須先自己手動建立，文法為：
CREATE SEQUENCE seq_name;
或
CREATE SEQUENCE seq_name
INCREASE BY 1
START WITH 1
MAXVALUE 9999
NOCACHE
NOCYCLE;

若不下參數，預設從 1 開始，每次增號 1，最大值為 10 的 27 次方，儲存值達到 MAXVALUE 不會自動重新編號 (若對應至 table 的 Primary Key，此值應采預設值 NOCYCLE)；
CACHE 選項的預設值，會在 memory 產生 20 筆資料。

執行以下語句，可看到所有 Sequence 的設定及儲存內容。其中的 LAST_NAME 為其下一個將要產生的值。
select * from user_sequences;

要看某一個 Sequence 的當前值、下一個值，可用如下語句：
select table1_seq.CURRVAL from dual;
select table1_se1.NEXTVAL from dual;

需注意第二個語句 NEXTVAL 只要一被執行到，該個 Sequence 的內部編號，就會自動增加一個號碼，而不僅只是 select 擷取而已。

若要搭配 Sequence，新增一筆記錄到 table，可用如下文法：
INSERT INTO table1 (id, name) VALUES (table1_seq.NEXTVAL, 'name1');

在 Oracle 10g 以前的版本，或您用的是 OleDb 聯機方式 (OracleClient 亦可)，當您想在 INSERT INTO 記錄時，Primary Key 希望能寫入 Sequence 的值，可用如下寫法 (亦
可在新增完成後，立即傳回該筆記錄最新的 Sequence 值)：

using System.Data.OleDb;
OleDbConnection odConn = null;
OleDbCommand odCmd = null;
Int64 intDATA_ID_AfterInserted = 0;
string strSql = "BEGIN SELECT table1_seq.NEXTVAL INTO :id FROM dual; INSERT INTO table1(id, name) VALUES(:id, :name); END;";

...中間略...

OleDbParameter p;
p = odCmd.Parameters.Add(":id", OleDbType.Double, 7);
p.Direction = ParameterDirection.Output;
odCmd.Parameters.Add(":name", OleDbType.VarWChar, 30).Value = TextBox1.Text;

odCmd.ExecuteNonQuery();
intDATA_ID_AfterInserted = Convert.ToInt64(p.Value); // 立即傳回該筆記錄最新的 Sequence 值

若您用的是 Oracle 10g 及以後的版本，且用的是 OracleClient Data Provider，則可用以下的「RETURNING INTO」更簡潔寫法。但須注意，OleDb 聯機方式若用此種寫法，在寫
入時並不會造成 error 或引發 exception，但寫入值會不正常。

string strSql = "INSERT INTO table1(id, name) VALUES(table1_seq.NEXTVAL, :name) RETURNING id INTO :id";

---------------------------------------------
本帖第 (七) 點的 ASP.NET 2.0 + Oracle 11g 樣本下載點 (批次新增 + 新增後馬上取得最新 Sequence 值)：
http://files.cnblogs.com/WizardWu/081128.zip
---------------------------------------------

若您執行本樣本的作業系統中，並未安裝 Oracle 11g server-side 軟體 (資料庫安裝在別台主機)，則當您用 Visual Studio 執行本樣本時，可能會出現類似以下的錯誤訊息：
“OraOLEDB.Oracle.1”未在本機電腦註冊

解決方式，是去 Oracle 官方網站，下載 Data Provider 和 Client-side 程式並安裝，下載網址如下：
http://www.oracle.com/technology/software/tech/windows/odpnet/index.html (較新)
http://www.oracle.com/technology/software/tech/dotnet/utilsoft.html (較舊)

安裝完後，即能以 Visual Studio 執行本樣本。但若改以 IIS 執行時，仍會出現上述的錯誤資訊，因為您還要再設定一些讓 IIS / ASP.NET 的使用者，有寫入伺服端 Oracle 所在檔案夾的許可權。有關其設定，其參考本站下一篇文章「Oracle 11g 學習筆記 (4)」。