使用 ADO.NET 和 Oracle 進(jìn)行高級(jí)數(shù)據(jù)訪問
[轉(zhuǎn)自MSDN]
Bill Hamilton
適用范圍:
Microsoft ADO.NET 1.1
Oracle 數(shù)據(jù)類型
摘要: 學(xué)習(xí)如何使用 ADO.NET 1.1 從復(fù)雜的 Oracle 數(shù)據(jù)類型中檢索數(shù)據(jù)。

引言
.NET Framework Data Provider for Oracle 并不總是能夠方便地檢索出以復(fù)雜方式存儲(chǔ)的數(shù)據(jù)。本文將討論如何訪問存儲(chǔ)在下表中列出的 Oracle 特定的數(shù)據(jù)類型中的數(shù)據(jù):
數(shù)據(jù)類型 | 說明 |
Any |
此數(shù)據(jù)類型用于明確定義可以存儲(chǔ)任何類型的數(shù)據(jù)的數(shù)據(jù)類型。 |
BFile |
指向包含非結(jié)構(gòu)化二進(jìn)制數(shù)據(jù)(存儲(chǔ)在 Oracle 數(shù)據(jù)庫之外)的操作系統(tǒng)文件的指針。 |
Interval |
此數(shù)據(jù)類型用于指定時(shí)間間隔。 |
LOB |
此數(shù)據(jù)類型用于存儲(chǔ)非結(jié)構(gòu)化二進(jìn)制數(shù)據(jù)。 |
RAW |
此數(shù)據(jù)類型用于存儲(chǔ)不是由 Oracle 解釋的二進(jìn)制數(shù)據(jù)。 |
REF CURSOR |
指向 SQL 游標(biāo)的 PL/SQL 指針,用于返回?cái)?shù)據(jù)庫中的某一行。 |
ROWID |
此數(shù)據(jù)類型用于表示數(shù)據(jù)庫中某一行的特定地址。 |
Timestamp |
此數(shù)據(jù)類型用于擴(kuò)展 DATE 數(shù)據(jù)類型的功能。 |
URI |
此數(shù)據(jù)類型用于存儲(chǔ)引用某個(gè)文檔或文檔中某個(gè)特定部分的統(tǒng)一資源標(biāo)識(shí)符 (URI)。 |
User-defined |
根據(jù)基本 Oracle 數(shù)據(jù)類型和用戶定義的數(shù)據(jù)類型創(chuàng)建的復(fù)雜數(shù)據(jù)類型。 |
XMLType |
此數(shù)據(jù)類型用于存儲(chǔ) XML 數(shù)據(jù)。 |
System.Data.OracleClient 命名空間包含用來訪問使用 .NET Framework Data Provider for Oracle 的 Oracle 數(shù)據(jù)源的類型。除了 Oracle 特定的 DataReader 、 DataAdapter 、 Command 和 Connection 類外,該命名空間還包含一些類型,以支持 Oracle 數(shù)據(jù)類型并簡(jiǎn)化 Oracle 數(shù)據(jù)類型的使用。下表列出了這些類型:
類型 | 說明 |
OracleBFile |
用于表示 Oracle BFILE 數(shù)據(jù)類型的類。 |
OracleBinary |
用于表示可變長(zhǎng)度的二進(jìn)制數(shù)據(jù)流的結(jié)構(gòu)。 |
OracleBoolean |
用于表示從 Oracle 數(shù)據(jù)類型之間的數(shù)據(jù)庫比較返回的值的結(jié)構(gòu)。 |
OracleDateTime |
用于表示從公元前 4712 年 1 月 1 日到公元 4712 年 12 月 31 日之間的日期和時(shí)間值的結(jié)構(gòu)。 |
OracleLob |
用于表示存儲(chǔ)在 Oracle 數(shù)據(jù)庫中的大型二進(jìn)制對(duì)象 (LOB) 的類。 |
OracleMonthSpan |
用于存儲(chǔ)以月表示的時(shí)間間隔并表示 Oracle INTERVAL YEAR TO MONTH 數(shù)據(jù)類型的結(jié)構(gòu)。 |
OracleNumber |
用于表示存儲(chǔ)在 Oracle 數(shù)據(jù)庫中、介于 -10-38 – 1 到 1038 – 1 之間的固定精度和范圍的數(shù)字值的結(jié)構(gòu)。 |
OracleString |
用于表示存儲(chǔ)在 Oracle 數(shù)據(jù)庫中的可變長(zhǎng)度字符流的結(jié)構(gòu)。 |
OracleTimeSpan |
用于存儲(chǔ)時(shí)間間隔并表示 Oracle INTERVAL DAY TO SECOND 數(shù)據(jù)類型的結(jié)構(gòu)。 |
OracleType |
Oracle 數(shù)據(jù)類型枚舉 |
對(duì)于每一種數(shù)據(jù)類型,以下代碼示例顯示了如何在表中創(chuàng)建該數(shù)據(jù)類型,如何用數(shù)據(jù)填充每個(gè)數(shù)據(jù)類型列,以及如何訪問這些數(shù)據(jù)。
Any
Oracle 9i 引入了三種可供存儲(chǔ)通用數(shù)據(jù)(即任何已知的數(shù)據(jù)類型或未命名的數(shù)據(jù)類型)的數(shù)據(jù)類型。這些數(shù)據(jù)類型為:
? |
SYS.ANYTYPE 包括對(duì)象和集合類型在內(nèi)的任何 SQL 類型的數(shù)據(jù)類型說明。 |
? |
SYS.ANYDATA 包含給定類型的一個(gè)實(shí)例以及該類型的說明。ANYDATA 可以在不同的行中包含不同的數(shù)據(jù)類型。 |
? |
SYS.ANYDATASET 包含一組數(shù)據(jù)及數(shù)據(jù)類型的說明。 |
以下代碼將創(chuàng)建一個(gè)包含 ANYDATA 列的表格:
OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "CREATE TABLE MyAnyTable (Id NUMBER, AnydataCol SYS.ANYDATA)"; cmd.ExecuteNonQuery();
ANYDATA 包含一組靜態(tài)函數(shù),允許您通過明確轉(zhuǎn)換參數(shù)并將參數(shù)作為 ANYDATA 數(shù)據(jù)類型值返回,以此來創(chuàng)建一個(gè) ANYDATA 類型:
ConvertNumber(num IN NUMBER) ConvertDate(dat IN DATE) ConvertChar(c IN CHAR) ConvertVarchar(c IN VARCHAR) ConvertVarchar2(c IN VARCHAR2) ConvertRaw(r IN RAW) ConvertBlob(b IN BLOB) ConvertClob(c IN CLOB) ConvertBfile(b IN BFILE) ConvertObject(obj IN "<object_type>") ConvertRef(rf IN "<object_type>") ConvertCollection(n IN "COLLECTION_1")
以下代碼將在表格中插入兩行,即在第一行的 ANYDATA 列中插入 VARCHAR2,在第二行的 ANYDATA 列中插入 NUMBER:
OracleConnection conn = new OracleConnection(CONNECTSTRING); conn.Open(); OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "INSERT INTO MyAnyTable (Id, AnydataCol) VALUES (1, SYS.ANYDATA.CONVERTVARCHAR2('TEST STRING'))"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO MyAnyTable (Id, AnydataCol) VALUES (2, SYS.ANYDATA.CONVERTNUMBER(100))"; cmd.ExecuteNonQuery(); conn.Close();
ANYTYPE 類型的 GETTYPENAME 函數(shù)返回與 ANYDATA 字段中實(shí)際存儲(chǔ)的數(shù)據(jù)對(duì)應(yīng)的 ANYTYPE。以下代碼將使用 GETTYPENAME 在插入的兩行中檢索 ANYDATA 列中的數(shù)據(jù)類型:
OracleConnection conn = new OracleConnection(CONNECTSTRING); OracleCommand cmd = new OracleCommand( "SELECT e.AnydataCol.GetTypeName() FROM MyAnyTable e", conn); conn.Open(); OracleDataReader dr = cmd.ExecuteReader(); while(dr.Read()) Console.WriteLine(dr.GetString(0)); dr.Close(); conn.Close();
控制臺(tái)輸出為:
SYS.VARCHAR2 SYS.NUMBER
ANYDATA 包含一組成員函數(shù),允許您將 ANYDATA 數(shù)據(jù)作為適當(dāng)數(shù)據(jù)類型的值進(jìn)行檢索:
GetNumber(self IN AnyData, num OUT NOCOPY NUMBER) GetDate(self IN AnyData, dat OUT NOCOPY DATE) GetChar(self IN AnyData, c OUT NOCOPY CHAR) GetVarchar(self IN AnyData, c OUT NOCOPY VARCHAR) GetVarchar2(self IN AnyData, c OUT NOCOPY VARCHAR2) GetRaw(self IN AnyData, r OUT NOCOPY RAW) GetBlob(self IN AnyData, b OUT NOCOPY BLOB) GetClob(self IN AnyData, c OUT NOCOPY CLOB) GetBfile(self IN AnyData, b OUT NOCOPY BFILE) GetObject(self IN AnyData, obj OUT NOCOPY "<object_type>") GetRef(self IN AnyData, rf OUT NOCOPY "<object_type>") GetCollection(self IN col AnyData, OUT NOCOPY "<collection_type>")
以下函數(shù)允許您查詢 ANYDATA 列,并在 VARCHAR2 中返回每一行的結(jié)果:
CREATE OR REPLACE FUNCTION GETANYDATA (data IN SYS.ANYDATA) return VARCHAR2 as l_varchar2 varchar2(4000); l_rc number; begin case data.getTypeName when 'SYS.NUMBER' then l_rc := data.getNumber(l_varchar2); when 'SYS.VARCHAR2' then l_rc := data.getVarchar2(l_varchar2); else l_varchar2 := 'ERROR:unknown data type'; end case; return l_varchar2; end;
可以對(duì)此函數(shù)進(jìn)行擴(kuò)展,以支持其他本機(jī)數(shù)據(jù)類型和用戶定義的數(shù)據(jù)類型。以下代碼將使用函數(shù)返回表格中兩行的值。
OracleConnection conn = new OracleConnection(CONNECTSTRING); OracleCommand cmd = new OracleCommand("SELECT id, e.AnydataCol.GetTypeName(), GETANYDATA(anydataCol) dataValue FROM MyAnyTable e", conn); conn.Open(); OracleDataReader dr = cmd.ExecuteReader(); while(dr.Read()) Console.WriteLine(dr.GetInt32(0) + "; " + dr.GetString(1) + "; " + dr.GetString(2)); dr.Close(); conn.Close();
控制臺(tái)輸出為:
1; SYS.VARCHAR2; TEST STRING 2; SYS.NUMBER; 100
您也可以在 ANYTYPE 字段中存儲(chǔ)用戶定義的數(shù)據(jù)類型的實(shí)例。以下代碼將創(chuàng)建用戶定義的類型 EMPLOYEE_T:
OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "CREATE OR REPLACE TYPE Employee_t IS OBJECT (Name VARCHAR(50), YearsService INTEGER)"; cmd.ExecuteNonQuery();
可以使用以下代碼將 EMPLOYEE_T 數(shù)據(jù)類型的值插入到表格中:
cmd.CommandText = "INSERT INTO MyAnyTable (Id, AnydataCol) VALUES (3, SYS.ANYDATA.CONVERTOBJECT(Employee_t('Bill Hamilton', 5)))"; cmd.ExecuteNonQuery();
GetTypeName 函數(shù)將以 < schemaname >.< UserTypeName > 的格式返回?cái)?shù)據(jù)類型,例如 MySchema.EMPLOYEE_T。您可以使用訪問用戶定義的數(shù)據(jù)類型的方法(本文稍后會(huì)進(jìn)行討論)擴(kuò)展存儲(chǔ)過程,從而訪問包含用戶定義類型的 ANYTYPE 字段的內(nèi)容。
BFILE
BFILE 數(shù)據(jù)類型是對(duì)存儲(chǔ)在操作系統(tǒng)文件的數(shù)據(jù)庫之外的二進(jìn)制數(shù)據(jù)的引用。它的最大值為 4GB。存儲(chǔ)在這些列中的數(shù)據(jù)是只讀的。
以下代碼將創(chuàng)建一個(gè)包含 BFILE 數(shù)據(jù)類型列的表格:
OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "CREATE TABLE MyBfileTable (Id NUMBER, BfileCol BFILE)"; cmd.ExecuteNonQuery();
以下代碼將創(chuàng)建一個(gè) DIRECTORY 并將 SH_ANALZ.SQL 文件(隨 Oracle 9i 一起安裝)插入到 BFILE 數(shù)據(jù)類型中。運(yùn)行此代碼的帳戶需要具有 CREATE ANY DIRECTORY 權(quán)限:
int id = 1; OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = @"CREATE OR REPLACE DIRECTORY MYBFILEDIR AS 'C:/oracle/ora92/demo/schema/sales_history'"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO MyBfileTable VALUES (" + id + ", BFILENAME('MYBFILEDIR', 'SH_ANALZ.SQL'))"; cmd.ExecuteNonQuery();
以下代碼將檢索 BFILE 并將其內(nèi)容保存到一個(gè)字符串中:
int id = 1; byte[] bfile = null; OracleConnection conn = new OracleConnection(CONNECTSTRING); conn.Open(); OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT * FROM MyBfileTable WHERE id = " + id; OracleDataReader dr = cmd.ExecuteReader(); if(dr.Read()) { OracleBFile bf = dr.GetOracleBFile(1); bfile = new byte[bf.Length]; bf.Read(bfile, 0, bfile.Length); bf.Close(); } dr.Close(); conn.Close(); UTF7Encoding utf = new UTF7Encoding(); string s = utf.GetString(bfile);
Interval
Interval 數(shù)據(jù)類型用于指定時(shí)間間隔。有兩種 Interval 數(shù)據(jù)類型:
? |
INTERVAL DAY TO SECOND 此數(shù)據(jù)類型的固定大小為 11 個(gè)字節(jié),它存儲(chǔ)以天、小時(shí)、分鐘和秒表示的時(shí)間間隔。 |
? |
INTERVAL YEAR TO MONTH 此數(shù)據(jù)類型的固定大小為 5 個(gè)字節(jié),它存儲(chǔ)以年和月表示的時(shí)間間隔。 |
以下代碼將創(chuàng)建一個(gè)包含 INTERVAL DAY TO SECOND 和 INTERVAL YEAR TO MONTH 數(shù)據(jù)類型列的表格:
OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "CREATE TABLE MyIntervalTable (Id NUMBER, IntervalYTMCol INTERVAL YEAR TO MONTH, IntervalDTSCol INTERVAL DAY TO SECOND)"; cmd.ExecuteNonQuery();
以下代碼將使用 OracleMonthSpan 和 OracleTimeSpan 結(jié)構(gòu)插入一個(gè)包含時(shí)間間隔數(shù)據(jù)的行:
OracleDataAdapter da = new OracleDataAdapter( "SELECT Id, IntervalYTMCol, IntervalDTSCol FROM MyIntervalTable", CONNECTSTRING); DataTable dt = new DataTable(); // 獲取架構(gòu) da.FillSchema(dt, SchemaType.Source); OracleCommandBuilder cb = new OracleCommandBuilder(da); int id = 1; // 1 年,2 個(gè)月 OracleMonthSpan intervalYTMCol = new OracleMonthSpan(1, 2); // 1 天,2 小時(shí),3 分鐘,4 秒,5 毫秒 OracleTimeSpan intervalDTSCol = new OracleTimeSpan(1, 2, 3, 4, 5); // 創(chuàng)建一個(gè)包含該數(shù)據(jù)的行 DataRow row = dt.NewRow(); row["Id"] = id; row["IntervalYTMCol"] = intervalYTMCol.Value; row["IntervalDTSCol"] = intervalDTSCol.Value; dt.Rows.Add(row); da.Update(dt);
以下代碼將使用 OracleDataReader 的 GetOracleMonthSpan() 和 GetOracleTimeSpan() 類型的訪問器方法來檢索時(shí)間間隔數(shù)據(jù):
int id = 1; OracleMonthSpan intervalYTMCol; OracleTimeSpan intervalDTSCol; OracleConnection conn = new OracleConnection(CONNECTSTRING); OracleCommand cmd = new OracleCommand("SELECT IntervalYTMCol, IntervalDTSCol FROM MyIntervalTable WHERE Id = " + id, conn); conn.Open(); OracleDataReader dr = cmd.ExecuteReader(); if(dr.Read()) { intervalYTMCol = dr.GetOracleMonthSpan(0); intervalDTSCol = dr.GetOracleTimeSpan(1); Console.WriteLine(intervalYTMCol.ToString() + "; " + intervalDTSCol.ToString()); } dr.Close(); conn.Close();
控制臺(tái)輸出為:
14; 1.02:03:04.0050000
LOB
Oracle LOB 數(shù)據(jù)類型可以存儲(chǔ)高達(dá) 4GB 大小的非結(jié)構(gòu)化數(shù)據(jù)(例如,多媒體內(nèi)容和文本)。LOB 允許隨機(jī)和分段訪問數(shù)據(jù)。Oracle 建議在新應(yīng)用程序中使用 LOB 類型,而不要使用 LONG RAW 類型。與 LONG RAW 不同的是,一個(gè)表格中可以包含多個(gè) LOB 列。LOB 數(shù)據(jù)類型包括:
? |
BLOB 存儲(chǔ)二進(jìn)制數(shù)據(jù)。 |
? |
CLOB 存儲(chǔ)單字節(jié)數(shù)據(jù)庫字符集數(shù)據(jù),以默認(rèn)的服務(wù)器字符集為基礎(chǔ)。 |
? |
NCLOB 存儲(chǔ) Unicode 字符數(shù)據(jù),以數(shù)據(jù)庫的全局字符集為基礎(chǔ)。 |
以下代碼將創(chuàng)建一個(gè)包含 BLOB、CLOB 和 NCLOB 列的表格:
OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "CREATE TABLE MyBlobTable (Id NUMBER, BlobCol BLOB, ClobCol CLOB, NclobCol NCLOB)"; cmd.ExecuteNonQuery();
以下代碼將一個(gè)用戶選定的文件插入到 BLOB 列中,并將一些測(cè)試文本插入到 CLOB 和 NCLOB 列中:
OpenFileDialog ofd = new OpenFileDialog(); if (ofd.ShowDialog() == DialogResult.OK) { FileStream fs = new FileStream(ofd.FileName, FileMode.Open, FileAccess.Read); byte[] blob = new byte[fs.Length]; fs.Read(blob, 0, blob.Length); fs.Close(); OracleDataAdapter da = new OracleDataAdapter(" SELECT Id, BlobCol, ClobCol, NclobCol FROM MyBlobTable", CONNECTSTRING); DataTable dt = new DataTable(); // 獲取架構(gòu) da.FillSchema(dt, SchemaType.Source); OracleCommandBuilder cb = new OracleCommandBuilder(da); int id = 1; string clob = "test CLOB text"; string nclob = "test NCLOB text"; // 創(chuàng)建一個(gè)包含該數(shù)據(jù)的行 DataRow row = dt.NewRow(); row["Id"] = id; row["BlobCol"] = blob; row["ClobCol"] = clob; row["NclobCol"] = nclob; dt.Rows.Add(row); // 更新表格 da.Update(dt); }
以下代碼將檢索 LOB 數(shù)據(jù):
int id = 1; OracleLob blob = null; String clob = ""; String nclob = ""; OracleConnection conn = new OracleConnection(CONNECTSTRING); OracleCommand cmd = new OracleCommand( "SELECT BlobCol, ClobCol, NclobCol FROM MyBlobTable WHERE Id = " + id, conn); conn.Open(); OracleDataReader dr = cmd.ExecuteReader(); if(dr.Read()) { if(!dr.IsDBNull(0)) blob = dr.GetOracleLob(0); MemoryStream ms = new MemoryStream((byte[])blob.Value); // 對(duì)數(shù)據(jù)流執(zhí)行某些操作 ms.Close(); if(!dr.IsDBNull(1)) clob = dr.GetOracleLob(1).Value.ToString(); if(!dr.IsDBNull(2)) nclob = dr.GetOracleLob(2).Value.ToString(); Console.WriteLine("BLOB length = " + blob.Length + "; " + clob.ToString() + "; " + nclob.ToString()); } dr.Close(); conn.Close();
控制臺(tái)輸出為:
BLOB length = 1171; test CLOB text; test NCLOB text
RAW
存儲(chǔ)不希望被解釋的可變長(zhǎng)度的二進(jìn)制數(shù)據(jù)或其他數(shù)據(jù)。Oracle 不會(huì)象處理 VARCHAR2 那樣試圖解釋這些 RAW 數(shù)據(jù),例如不會(huì)轉(zhuǎn)換字符集或填充空白。
RAW 數(shù)據(jù)類型是為了與現(xiàn)有應(yīng)用程序兼容而提供的,在新的應(yīng)用程序中,應(yīng)該使用 LOB 和 BFILE 數(shù)據(jù)類型。
有兩種 RAW 類型:
? |
RAW 與 VARCHAR2 的最大長(zhǎng)度相同(32767 字節(jié)),聲明變量時(shí)必須指定此長(zhǎng)度。 |
? |
LONG RAW 可變長(zhǎng)度的二進(jìn)制數(shù)據(jù),最大長(zhǎng)度為 2GB。一個(gè)表格中只允許有一個(gè) LONG RAW 數(shù)據(jù)類型。 |
以下代碼將創(chuàng)建一個(gè)包含 RAW 和 LONG RAW 列的表格:
OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "CREATE TABLE MyRawTable (Id NUMBER, RawCol RAW(50), LongRawCol LONG RAW)"; cmd.ExecuteNonQuery();
以下代碼將創(chuàng)建一個(gè)行并將數(shù)據(jù)插入到 RAW 和 LONG RAW 列中:在 RAW 字段中插入字節(jié)數(shù)組,而在 LONG RAW 字段中插入用戶選定的文件的內(nèi)容:
OpenFileDialog ofd = new OpenFileDialog(); if (ofd.ShowDialog() == DialogResult.OK) { // 創(chuàng)建 RAW byte[] raw = new byte[50]; for (byte i = 0; i < raw.Length; i++) raw[i] = i; // 創(chuàng)建 LONGRAW FileStream fs = new FileStream(ofd.FileName, FileMode.Open, FileAccess.Read); byte[] longRaw = new byte[fs.Length]; fs.Read(longRaw, 0, raw.Length); fs.Close(); OracleDataAdapter da = new OracleDataAdapter( "SELECT Id, RawCol, LongRawCol FROM MyRawTable", CONNECTSTRING); DataTable dt = new DataTable(); // 獲取架構(gòu) da.FillSchema(dt, SchemaType.Source); OracleCommandBuilder cb = new OracleCommandBuilder(da); int id = 1; // 創(chuàng)建一個(gè)包含該數(shù)據(jù)的行 DataRow row = dt.NewRow(); row["Id"] = id; row["RawCol"] = raw; row["LongRawCol"] = longRaw; dt.Rows.Add(row); // 更新表格 da.Update(dt); }
以下代碼將 RAW 和 LONG RAW 字段中的數(shù)據(jù)檢索到 OracleBinary 結(jié)構(gòu)中:
int id = 1; OracleBinary raw = null; OracleBinary longRaw = null; OracleConnection conn = new OracleConnection(CONNECTSTRING); OracleCommand cmd = new OracleCommand( "SELECT RawCol, LongRawCol FROM MyRawTable WHERE Id = " + id, conn); conn.Open(); OracleDataReader dr = cmd.ExecuteReader(); if(dr.Read()) { if(!dr.IsDBNull(0)) raw = dr.GetOracleBinary(0); if(!dr.IsDBNull(1)) longRaw = dr.GetOracleBinary(1); } dr.Close(); conn.Close();
REF CURSOR
此數(shù)據(jù)類型指向 PL/SQL 查詢所返回的結(jié)果集。REF CURSOR 被用作輸出參數(shù),負(fù)責(zé)將結(jié)果集從 Oracle 結(jié)構(gòu)化程序返回給調(diào)用應(yīng)用程序。可以在行集內(nèi)移動(dòng)游標(biāo)以返回多行。
使用 REF CURSOR 輸出參數(shù)將結(jié)果集從 Oracle 結(jié)構(gòu)化程序返回給調(diào)用應(yīng)用程序。
以下數(shù)據(jù)包和數(shù)據(jù)包正文將 REF CURSOR 返回給 HR.EMPLOYEES 表格中包含所有員工的結(jié)果集。數(shù)據(jù)包定義如下:
CREATE OR REPLACE PACKAGE GET_EMPLOYEES AS TYPE T_CURSOR IS REF CURSOR; PROCEDURE GetEmployees (cur_Employees OUT T_CURSOR); END GET_EMPLOYEES;
數(shù)據(jù)包正文定義一個(gè)單獨(dú)的過程,該過程檢索所有員工的所有數(shù)據(jù)并作為一個(gè) REF CURSOR 輸出參數(shù)返回:
CREATE OR REPLACE PACKAGE BODY GET_EMPLOYEES AS PROCEDURE GetEmployees (cur_Employees OUT T_CURSOR) IS BEGIN OPEN cur_Employees FOR SELECT * FROM EMPLOYEES; END GetEmployees; END GET_EMPLOYEES;
以下代碼顯示如何使用 OracleDataReader 檢索存儲(chǔ)過程 GetEmployees 返回的結(jié)果集。
// 創(chuàng)建連接 OracleConnection conn = new OracleConnection(CONNECTSTRING); // 為存儲(chǔ)過程創(chuàng)建命令 OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = "GET_EMPLOYEES.GetEmployees"; cmd.CommandType = CommandType.StoredProcedure; // 添加 REF CURSOR 參數(shù)以檢索結(jié)果集 cmd.Parameters.Add("cur_Employees", OracleType.Cursor).Direction = ParameterDirection.Output; // 打開連接并創(chuàng)建 DataReader conn.Open(); OracleDataReader dr = cmd.ExecuteReader(); // 輸出員工的 ID、姓名和電子郵件地址(前四個(gè)字段) // 并關(guān)閉連接。 while(dr.Read()) { for(int i = 0; i <= 3; i++) Console.Write(dr[i].ToString() + ";"); Console.WriteLine(); } conn.Close();
控制臺(tái)輸出將列出所有員工的 ID、姓名和電子郵件地址。前五行為:
100;Steven;King;SKING; 101;Neena;Kochhar;NKOCHHAR; 102;Lex;De Haan;LDEHAAN; 103;Alexander;Hunold;AHUNOLD; 104;Bruce;Ernst;BERNST;
ROWID
Oracle 使用此數(shù)據(jù)類型存儲(chǔ)數(shù)據(jù)庫中每一行的唯一地址。ROWID 類型包括:
? |
ROWID 10 字節(jié)長(zhǎng)的數(shù)據(jù)類型,用于表示數(shù)據(jù)庫中某行的頁、記錄和偏移地址。其值以 bbbbbbbb.ssss.ffff 格式表示實(shí)際顯示的二進(jìn)制數(shù)據(jù),其中:
|
||||||
? |
UROWID 通用 ROWID (UROWID),它支持邏輯和物理 ROWID 以及外部表格(包括非 Oracle 表格)的 ROWID。UROWID 的最大長(zhǎng)度為 4000 字節(jié)。 |
||||||
? |
ROWID 偽列 每個(gè)表格都有一個(gè)名為 ROWID 的偽列,它允許您使用保留字 ROWID 作為列名來訪問任意行的地址。ROWID 偽列不存儲(chǔ)在數(shù)據(jù)庫中,不占用任何空間,也不能被修改或刪除。只要表格中存在某個(gè)行,就不能修改該行的 ROWID,ROWID 提供了訪問表格行的最快方式。 |
以下代碼將創(chuàng)建一個(gè)包含 ROWID 和 UROWID 列的表格:
OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "CREATE TABLE MyRowidTable (Id NUMBER, RowidCol ROWID, UrowidCol UROWID)"; cmd.ExecuteNonQuery();
以下代碼將創(chuàng)建一條記錄并在 ROWID 和 UROWID 字段中存儲(chǔ) HR.Employees 表格中第一行的 ROWID 偽列的值:
int id = 1; OracleString rowId = null; OracleConnection conn = new OracleConnection(CONNECTSTRING); conn.Open(); // 獲取 HR.Employees 中第一行的 ROWID 偽列的值 OracleCommand cmd = new OracleCommand("SELECT ROWID FROM HR.Employees", conn); OracleDataReader dr = cmd.ExecuteReader(); if(dr.Read()) rowId = dr.GetOracleString(0); conn.Close(); // 將 ROWID 偽列的值存儲(chǔ)到 ROWID 和 UROWID 字段中 OracleDataAdapter da = new OracleDataAdapter("SELECT ID, RowidCol, UrowidCol FROM MyRowidTable", CONNECTSTRING); DataTable dt = new DataTable(); da.FillSchema(dt, SchemaType.Source); OracleCommandBuilder cb = new OracleCommandBuilder(da); DataRow row = dt.NewRow(); row["Id"] = id; row["RowidCol"] = rowId; row["UrowidCol"] = rowId; dt.Rows.Add(row); da.Update(dt);
以下代碼將檢索 ROWID 和 UROWID 列中的值,然后使用檢索到的值檢索 HR.Employees 表格中的第一行:
int id = 1; OracleString rowId = null; OracleString urowId = null; // 獲取 ROWID 和 UROWID 的值 OracleConnection conn = new OracleConnection(CONNECTSTRING); OracleCommand cmd = new OracleCommand("SELECT RowidCol, UrowidCol FROM MyRowIdTable WHERE Id = " + id, conn); conn.Open(); OracleDataReader dr = cmd.ExecuteReader(); if(dr.Read()) { rowId = dr.GetOracleString(0); urowId = dr.GetOracleString(1); Console.WriteLine(rowId + "; " + urowId); } int employeeId; string firstName; string lastName; // 從 EMPLOYEES 表格中獲取行 cmd = new OracleCommand("SELECT * FROM Employees WHERE ROWID = '" + rowId.Value + "'", conn); dr = cmd.ExecuteReader(); if(dr.Read()) { employeeId = dr.GetInt32(0); firstName = dr.GetString(1); lastName = dr.GetString(2); Console.WriteLine(employeeId.ToString() + "; " + firstName + "; " + lastName); } conn.Close();
控制臺(tái)輸出為:
AAAHM7AAFAAAABWAAA; AAAHM7AAFAAAABWAAA 100; Steven; King
Timestamp
用于表示日期/時(shí)間值的數(shù)據(jù)類型。此數(shù)據(jù)類型向 DATE 類型添加小數(shù)秒以提高精度。Timestamp 數(shù)據(jù)類型使用精度參數(shù)定義秒字段中存儲(chǔ)的數(shù)據(jù)的精度,精度值范圍從 0 到 9,默認(rèn)值為 6。有三種 Timestamp 數(shù)據(jù)類型:
? |
TIMESTAMP 用于表示日期/時(shí)間的可變大小值,大小范圍從 7 個(gè)字節(jié)到 11 個(gè)字節(jié)。 |
? |
TIMESTAMP WITH TIME ZONE 13 個(gè)字節(jié)的固定大小值,除日期/時(shí)間外還包括時(shí)區(qū)。可以使用 UTC 偏移(與英國(guó)格林威治零子午線的當(dāng)前時(shí)間或格林威治標(biāo)準(zhǔn)時(shí)間 [GMT] 之間的時(shí)差)或 V$TIMEZONE_NAMES 表格中的時(shí)區(qū)名來設(shè)置時(shí)區(qū)。您可以使用以下查詢獲取有效區(qū)域的列表: SELECT tzname, tzabbrev FROM V$TIMEZONE_NAMES |
? |
TIMESTAMP WITH LOCAL TIME TIMESTAMP 的值與 TIMESTAMP WITH TIME ZONE 的值類似,只是數(shù)據(jù)被規(guī)范化為數(shù)據(jù)庫服務(wù)器的時(shí)區(qū)。此數(shù)據(jù)類型允許您調(diào)整日期/時(shí)間以說明時(shí)區(qū)差異,包括該時(shí)區(qū)是否遵守夏時(shí)制。不會(huì)存儲(chǔ) UTC 偏移。 |
以下代碼將創(chuàng)建一個(gè)包含 TIMESTAMP、TIMESTAMP WITH TIMEZONE 和 TIMESTAMP WITH LOCAL TIME ZONE 列的表格:
OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "CREATE TABLE MyTimestampTable (Id NUMBER, TimestampCol TIMESTAMP, TimeStampTZCol TIMESTAMP WITH TIME ZONE, TimeStampLTZCol TIMESTAMP WITH LOCAL TIME ZONE)"; cmd.ExecuteNonQuery();
以下代碼將當(dāng)前時(shí)間插入到每個(gè) Timestamp 數(shù)據(jù)類型的列中:
int id = 1; DateTime timestampCol = DateTime.Now; DateTime timestampTZCol = DateTime.Now; DateTime timestampLTZCol = DateTime.Now; OracleConnection conn = new OracleConnection(CONNECTSTRING); conn.Open(); OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "INSERT INTO MyTimestampTable VALUES (" + id + ", '" + timestampCol.ToString("dd MMM yyyy hh:mm:sstt") + "', '" + timestampTZCol.ToString("dd MMM yyyy hh:mm:sstt") + "', '" + timestampLTZCol.ToString("dd MMM yyyy hh:mm:sstt") + "')"; cmd.ExecuteNonQuery(); conn.Close();
以下代碼將檢索三種 Timestamp 數(shù)據(jù)類型的值:
int id = 1; DateTime timestampCol; DateTime timestampTZCol; DateTime timestampLTZCol; OracleConnection conn = new OracleConnection(CONNECTSTRING); OracleCommand cmd = new OracleCommand("SELECT TimestampCol, TimestampTZCol, TimestampLTZCol FROM MyTimestampTable WHERE Id = " + id, conn); conn.Open(); // 獲取 Timestamp 值 OracleDataReader dr = cmd.ExecuteReader(); if(dr.Read()) { timestampCol = dr.GetDateTime(0); timestampTZCol = dr.GetDateTime(1); timestampLTZCol = dr.GetDateTime(2); Console.WriteLine(timestampCol.ToString() + "; " + timestampTZCol.ToString() + "; " + timestampLTZCol.ToString()); } dr.Close(); conn.Close();
控制臺(tái)輸出為:
7/9/2004 1:43:31 PM; 7/9/2004 1:43:31 PM; 7/9/2004 10:43:31 AM
UriType
UriType 數(shù)據(jù)類型存儲(chǔ)指向內(nèi)部或外部統(tǒng)一資源標(biāo)識(shí)符 (URI) 資源的指針,它們可以引用一個(gè)文檔或文檔中的特定部分。從通用 UriType 數(shù)據(jù)類型派生出以下子類型:
? |
HttpURIType 使用超文本傳輸協(xié)議 (HTTP) 訪問的 Web 頁或文件的 URL。 |
? |
DBUriType 對(duì)數(shù)據(jù)庫內(nèi)的數(shù)據(jù)(單行、多行或單個(gè)列)的引用。可以使用成員函數(shù)來訪問數(shù)據(jù),這些成員函數(shù)的語法與 XPath 的語法類似。 |
? |
XdbUriType 存儲(chǔ) XML 文檔的引用,該文檔存儲(chǔ)在數(shù)據(jù)庫中的 Oracle XML DB 知識(shí)庫內(nèi)。 |
您可以使用 UriType 或某一特定子類型創(chuàng)建列。
UriType 包含以下函數(shù),它們可用于任何子類型:
函數(shù) | 說明 |
createUri(uri IN VARCHAR2) |
構(gòu)造一個(gè) URITYPE 子類型。每個(gè)子類型都有其自己的構(gòu)造函數(shù):
|
getBlob() |
返回 URL 指向的 BLOB。 |
getClob() |
返回 URL 指向的值,作為使用數(shù)據(jù)庫字符集編碼的 CLOB。 |
getContentType() |
返回 URL 的 MIME 信息。 |
getExternalUrl() |
與 getUrl() 類似,只是它轉(zhuǎn)義了 URL 中的字符。 |
getUrl() |
返回?cái)?shù)據(jù)類型中存儲(chǔ)的 URL。 |
getXML() |
返回 URI 的 XMLType 對(duì)象,URI 必須指向有效的 XML 文檔。 |
以下代碼將創(chuàng)建一個(gè)包含 URITYPE、HTTPURITYPE 和 DBURITYPE 列的表格:
OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "CREATE TABLE MyUritypeTable (Id NUMBER, UriTypeCol SYS.URITYPE, HttpUriTypeCol SYS.HTTPURITYPE, DBUriTypeCol SYS.DBURITYPE)"; cmd.ExecuteNonQuery();
以下代碼將在表格中插入一個(gè)數(shù)據(jù)行。此行包含 MSDN 文章的 URL 和 HR.EMPLOYEES 表格中 EMPLOYEE_ID = 100 的員工的 EMAIL 值的引用:
int id = 1; OracleConnection conn = new OracleConnection(CONNECTSTRING); conn.Open(); OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "INSERT INTO MyUritypeTable VALUES (" + id + ", SYS.URIFACTORY.GETURI('http://msdn.microsoft.com/data/default.aspx? pull=/library/en-us/dnadonet/html/msdnorsps.asp')" + ", SYS.HTTPURITYPE('msdn.microsoft.com/data/default.aspx? pull=/library/en-us/dnadonet/html/msdnorsps.asp')" + ", SYS.DBURITYPE('/HR/EMPLOYEES/ROW[EMPLOYEE_ID=''100'']/EMAIL', NULL))"; cmd.ExecuteNonQuery(); conn.Close();
以下代碼將檢索三個(gè) UriTypes 的 URL 以及作為 CLOB 的 HTTPURITYPE 和 DBURITYPE 的內(nèi)容:
int id = 1; string uriTypeCol; string httpUriTypeCol; string dBUriTypeCol; OracleLob httpUriTypeColClob; OracleLob dBUriTypeColClob; OracleConnection conn = new OracleConnection(CONNECTSTRING); OracleCommand cmd = new OracleCommand("SELECT e.UriTypeCol.getUrl(), e.HttpUriTypeCol.getUrl(), e.DBUriTypeCol.getUrl(), e.HttpUriTypeCol.getClob(), e.DBUriTypeCol.getClob() FROM HR.MyUritypeTable e WHERE Id = " + id, conn); conn.Open(); OracleDataReader dr = cmd.ExecuteReader(); if(dr.Read()) { // 獲取 URI uriTypeCol = dr.GetString(0); httpUriTypeCol = dr.GetString(1); dBUriTypeCol = dr.GetString(2); // 獲取 HTTPURITYPE 列的 HTML httpUriTypeColBlob = dr.GetOracleLob(3); // 獲取 HR.EMPLOYEES 中的 EMAIL 值 dBUriTypeColClob = dr.GetOracleLob(4); Console.WriteLine(uriTypeCol + "; " + httpUriTypeCol + "; " + dBUriTypeCol + "; HTTPURITYPE CLOB length = " + httpUriTypeColClob.Length + "; " + dBUriTypeColClob.Value); } dr.Close(); conn.Close();
控制臺(tái)輸出為:
http://msdn.microsoft.com/data/default.aspx?pull=/library/en-us/dnadonet/html/msdnorsps.asp; http://msdn.microsoft.com/data/default.aspx?pull=/library/en-us/dnadonet/html/msdnorsps.asp; /ORADB/HR/EMPLOYEES/ROW[EMPLOYEE_ID='100']/EMAIL; HTTPURITYPE CLOB length = 112964; <?xml version="1.0"?><EMAIL>SKING</EMAIL>
User Defined
User-defined 數(shù)據(jù)類型包括內(nèi)置的數(shù)據(jù)類型和其他由用戶定義的數(shù)據(jù)類型。User-defined 數(shù)據(jù)類型提取應(yīng)用程序數(shù)據(jù)中真實(shí)世界實(shí)體的結(jié)構(gòu)。此類型是以下對(duì)象的模板:該對(duì)象通過名稱標(biāo)識(shí)并具有屬性(內(nèi)置類型或其他由用戶定義的類型)和方法。
以下代碼將創(chuàng)建用戶定義的類型 Employee_t:
OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "CREATE OR REPLACE TYPE Employee_t IS OBJECT (Name VARCHAR(50), YearsService INTEGER)"; cmd.ExecuteNonQuery();
以下代碼將創(chuàng)建一個(gè)包含 Employee_t 數(shù)據(jù)類型列的表格:
OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "CREATE TABLE MyUserdefinedTable (Id NUMBER, Employee HR.EMPLOYEE_T)"; cmd.ExecuteNonQuery(); 以下代碼將在表格中插入一行: OracleConnection conn = new OracleConnection(CONNECTSTRING); conn.Open(); OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "INSERT INTO MyUserdefinedTable VALUES (1, EMPLOYEE_T('Bill Hamilton', 5))"; cmd.ExecuteNonQuery(); conn.Close();
以下代碼將檢索該行的數(shù)據(jù):
int id = 1; string employee_Name; OracleNumber employee_YearsService; OracleConnection conn = new OracleConnection(CONNECTSTRING); OracleCommand cmd = new OracleCommand("SELECT Id, e.Employee.Name, e.Employee.YearsService FROM MyUserdefinedTable e WHERE Id = " + id, conn); conn.Open(); OracleDataReader dr = cmd.ExecuteReader(); if(dr.Read()) { id = dr.GetInt32(0); employee_Name = dr.GetString(1); employee_YearsService = dr.GetOracleNumber(2); Console.WriteLine(id.ToString() + "; " + employee_Name + "; " + employee_YearsService); } dr.Close(); conn.Close();
控制臺(tái)輸出為:
1; Bill Hamilton; 5
XmlType
此數(shù)據(jù)類型使用一個(gè)隱含的 CLOB 在表格中存儲(chǔ) XML 文檔。此數(shù)據(jù)類型包含多個(gè)成員函數(shù),它們可用于創(chuàng)建 XML、對(duì)文檔執(zhí)行 XPath 查詢、提取數(shù)據(jù)片斷、驗(yàn)證 XML 以及返回內(nèi)容。
以下代碼將創(chuàng)建一個(gè)包含 XMLType 列的表格:
OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "CREATE TABLE MyXmltypeTable (Id NUMBER, XmlTypeCol SYS.XMLTYPE)"; cmd.ExecuteNonQuery();
以下代碼將插入一行并使用 XMLType 的 CREATEXML 成員函數(shù)設(shè)置 XMLType 列:
int id = 1; string xmltypeCol = @"<?xml version=""1.0""?><EMAIL>SKING</EMAIL>"; OracleConnection conn = new OracleConnection(CONNECTSTRING); conn.Open(); OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "INSERT INTO MyXmltypeTable VALUES (" + id + ", SYS.XMLTYPE.CREATEXML('" + xmltypeCol + "'))"; cmd.ExecuteNonQuery(); conn.Close();
以下代碼將使用 XMLType 的 GETSTRINGVAL 成員函數(shù)檢索 XMLType 列的值:
int id = 1; string xmltypeCol; OracleConnection conn = new OracleConnection(CONNECTSTRING); OracleCommand cmd = new OracleCommand( "SELECT SYS.XMLTYPE.GETSTRINGVAL(xmltypeCol) FROM MyXmltypeTable WHERE Id = " + id, conn); conn.Open(); OracleDataReader dr = cmd.ExecuteReader(); if(dr.Read()) { xmltypeCol = dr.GetString(0); Console.WriteLine(xmltypeCol); } dr.Close(); conn.Close();
控制臺(tái)輸出為:
<?xml version="1.0"?><EMAIL>SKING</EMAIL>
結(jié)論
Oracle 提供的多種數(shù)據(jù)類型可以幫助您處理復(fù)雜數(shù)據(jù),包括存儲(chǔ)在數(shù)據(jù)庫內(nèi)或數(shù)據(jù)庫外的大型對(duì)象、XML、通用 (ANY) 數(shù)據(jù)、日期和時(shí)間以及用戶定義的聚合數(shù)據(jù)結(jié)構(gòu)。Microsoft .NET Framework 1.1 中的 Microsoft .NET Oracle Provider 提供了 System.Data.OracleClient 命名空間中的類型,使您可以方便地訪問和操作存儲(chǔ)在這些類型中的數(shù)據(jù)。
參考資料
Bill Hamilton 是一位軟件設(shè)計(jì)師,他專門研究使用 Microsoft .NET 和 J2EE 技術(shù)設(shè)計(jì)、開發(fā)以及實(shí)現(xiàn)分布式應(yīng)用程序。作為新技術(shù)的最早采用者,他經(jīng)常為客戶提供評(píng)估、建議和幫助,以幫助客戶有效地使用新技術(shù)。Bill 著有兩本關(guān)于 ADO.NET 的書,如上面的參考資料部分所示。
? 2004 Microsoft Corporation 版權(quán)所有。保留所有權(quán)利。使用規(guī)定。
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號(hào)聯(lián)系: 360901061
您的支持是博主寫作最大的動(dòng)力,如果您喜歡我的文章,感覺我的文章對(duì)您有幫助,請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點(diǎn)擊下面給點(diǎn)支持吧,站長(zhǎng)非常感激您!手機(jī)微信長(zhǎng)按不能支付解決辦法:請(qǐng)將微信支付二維碼保存到相冊(cè),切換到微信,然后點(diǎn)擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
