使用 ADO.NET 和 Oracle 进行高级数据访问

程序员文章站 2022-03-26 08:12:52

...

使用 ADO.NET 和 Oracle 进行高级数据访问

[转自MSDN]

发布日期： 11/4/2004 | 更新日期： 11/4/2004

Bill Hamilton

适用范围：

Microsoft ADO.NET 1.1

Oracle 数据类型

摘要：学习如何使用 ADO.NET 1.1 从复杂的 Oracle 数据类型中检索数据。

使用 ADO.NET 和 Oracle 进行高级数据访问

博客分类： Oracle .netOracleASP.net数据结构Microsoft

引言

.NET Framework Data Provider for Oracle 并不总是能够方便地检索出以复杂方式存储的数据。本文将讨论如何访问存储在下表中列出的 Oracle 特定的数据类型中的数据：

数据类型	说明
Any	此数据类型用于明确定义可以存储任何类型的数据的数据类型。
BFile	指向包含非结构化二进制数据（存储在 Oracle 数据库之外）的操作系统文件的指针。
Interval	此数据类型用于指定时间间隔。
LOB	此数据类型用于存储非结构化二进制数据。
RAW	此数据类型用于存储不是由 Oracle 解释的二进制数据。
REF CURSOR	指向 SQL 游标的 PL/SQL 指针，用于返回数据库中的某一行。
ROWID	此数据类型用于表示数据库中某一行的特定地址。
Timestamp	此数据类型用于扩展 DATE 数据类型的功能。
URI	此数据类型用于存储引用某个文档或文档中某个特定部分的统一资源标识符 (URI)。
User-defined	根据基本 Oracle 数据类型和用户定义的数据类型创建的复杂数据类型。
XMLType	此数据类型用于存储 XML 数据。

System.Data.OracleClient 命名空间包含用来访问使用 .NET Framework Data Provider for Oracle 的 Oracle 数据源的类型。除了 Oracle 特定的 DataReader、DataAdapter、Command 和 Connection 类外，该命名空间还包含一些类型，以支持 Oracle 数据类型并简化 Oracle 数据类型的使用。下表列出了这些类型：

类型	说明
OracleBFile	用于表示 Oracle BFILE 数据类型的类。
OracleBinary	用于表示可变长度的二进制数据流的结构。
OracleBoolean	用于表示从 Oracle 数据类型之间的数据库比较返回的值的结构。
OracleDateTime	用于表示从公元前 4712 年 1 月 1 日到公元 4712 年 12 月 31 日之间的日期和时间值的结构。
OracleLob	用于表示存储在 Oracle 数据库中的大型二进制对象 (LOB) 的类。
OracleMonthSpan	用于存储以月表示的时间间隔并表示 Oracle INTERVAL YEAR TO MONTH 数据类型的结构。
OracleNumber	用于表示存储在 Oracle 数据库中、介于 -10-38 – 1 到 1038 – 1 之间的固定精度和范围的数字值的结构。
OracleString	用于表示存储在 Oracle 数据库中的可变长度字符流的结构。
OracleTimeSpan	用于存储时间间隔并表示 Oracle INTERVAL DAY TO SECOND 数据类型的结构。
OracleType	Oracle 数据类型枚举

对于每一种数据类型，以下代码示例显示了如何在表中创建该数据类型，如何用数据填充每个数据类型列，以及如何访问这些数据。

返回页首

Any

Oracle 9i 引入了三种可供存储通用数据（即任何已知的数据类型或未命名的数据类型）的数据类型。这些数据类型为：

•

SYS.ANYTYPE

包括对象和集合类型在内的任何 SQL 类型的数据类型说明。

•

SYS.ANYDATA

包含给定类型的一个实例以及该类型的说明。ANYDATA 可以在不同的行中包含不同的数据类型。

•

SYS.ANYDATASET

包含一组数据及数据类型的说明。

以下代码将创建一个包含 ANYDATA 列的表格：

OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE MyAnyTable (Id NUMBER,
AnydataCol SYS.ANYDATA)";
cmd.ExecuteNonQuery();

ANYDATA 包含一组静态函数，允许您通过明确转换参数并将参数作为 ANYDATA 数据类型值返回，以此来创建一个 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 函数返回与 ANYDATA 字段中实际存储的数据对应的 ANYTYPE。以下代码将使用 GETTYPENAME 在插入的两行中检索 ANYDATA 列中的数据类型：

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();

控制台输出为：

SYS.VARCHAR2
SYS.NUMBER

ANYDATA 包含一组成员函数，允许您将 ANYDATA 数据作为适当数据类型的值进行检索：

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>")

以下函数允许您查询 ANYDATA 列，并在 VARCHAR2 中返回每一行的结果：

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;

可以对此函数进行扩展，以支持其他本机数据类型和用户定义的数据类型。以下代码将使用函数返回表格中两行的值。

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();

控制台输出为：

1; SYS.VARCHAR2; TEST STRING
2; SYS.NUMBER; 100

您也可以在 ANYTYPE 字段中存储用户定义的数据类型的实例。以下代码将创建用户定义的类型 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 数据类型的值插入到表格中：

cmd.CommandText = "INSERT INTO MyAnyTable (Id, AnydataCol) VALUES
(3, SYS.ANYDATA.CONVERTOBJECT(Employee_t('Bill Hamilton', 5)))";
cmd.ExecuteNonQuery();

GetTypeName 函数将以 <schemaname>.<UserTypeName> 的格式返回数据类型，例如 MySchema.EMPLOYEE_T。您可以使用访问用户定义的数据类型的方法（本文稍后会进行讨论）扩展存储过程，从而访问包含用户定义类型的 ANYTYPE 字段的内容。

返回页首

BFILE

BFILE 数据类型是对存储在操作系统文件的数据库之外的二进制数据的引用。它的最大值为 4GB。存储在这些列中的数据是只读的。

以下代码将创建一个包含 BFILE 数据类型列的表格：

OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE MyBfileTable (Id NUMBER, BfileCol BFILE)";
cmd.ExecuteNonQuery();

以下代码将创建一个 DIRECTORY 并将 SH_ANALZ.SQL 文件（随 Oracle 9i 一起安装）插入到 BFILE 数据类型中。运行此代码的帐户需要具有 CREATE ANY DIRECTORY 权限：

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 并将其内容保存到一个字符串中：

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 数据类型用于指定时间间隔。有两种 Interval 数据类型：

•

INTERVAL DAY TO SECOND

此数据类型的固定大小为 11 个字节，它存储以天、小时、分钟和秒表示的时间间隔。

•

INTERVAL YEAR TO MONTH

此数据类型的固定大小为 5 个字节，它存储以年和月表示的时间间隔。

以下代码将创建一个包含 INTERVAL DAY TO SECOND 和 INTERVAL YEAR TO MONTH 数据类型列的表格：

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 结构插入一个包含时间间隔数据的行：

OracleDataAdapter da = new OracleDataAdapter(
"SELECT Id, IntervalYTMCol, IntervalDTSCol FROM MyIntervalTable",
CONNECTSTRING);
DataTable dt = new DataTable();
// 获取架构
da.FillSchema(dt, SchemaType.Source);

OracleCommandBuilder cb = new OracleCommandBuilder(da);

int id = 1;
// 1 年，2 个月
OracleMonthSpan intervalYTMCol = new OracleMonthSpan(1, 2);
// 1 天，2 小时，3 分钟，4 秒，5 毫秒
OracleTimeSpan intervalDTSCol = new OracleTimeSpan(1, 2, 3, 4, 5);

// 创建一个包含该数据的行
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() 类型的访问器方法来检索时间间隔数据：

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();

控制台输出为：

14; 1.02:03:04.0050000

返回页首

LOB

Oracle LOB 数据类型可以存储高达 4GB 大小的非结构化数据（例如，多媒体内容和文本）。LOB 允许随机和分段访问数据。Oracle 建议在新应用程序中使用 LOB 类型，而不要使用 LONG RAW 类型。与 LONG RAW 不同的是，一个表格中可以包含多个 LOB 列。LOB 数据类型包括：

•

BLOB

存储二进制数据。

•

CLOB

存储单字节数据库字符集数据，以默认的服务器字符集为基础。

•

NCLOB

存储 Unicode 字符数据，以数据库的全局字符集为基础。

以下代码将创建一个包含 BLOB、CLOB 和 NCLOB 列的表格：

OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE MyBlobTable (Id NUMBER, BlobCol BLOB,
ClobCol CLOB, NclobCol NCLOB)";
cmd.ExecuteNonQuery();

以下代码将一个用户选定的文件插入到 BLOB 列中，并将一些测试文本插入到 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();
// 获取架构
da.FillSchema(dt, SchemaType.Source);

OracleCommandBuilder cb = new OracleCommandBuilder(da);

int id = 1;
string clob = "test CLOB text";
string nclob = "test NCLOB text";
    
// 创建一个包含该数据的行
DataRow row = dt.NewRow();
row["Id"] = id;
row["BlobCol"] = blob;
row["ClobCol"] = clob;
row["NclobCol"] = nclob;
dt.Rows.Add(row);

// 更新表格
da.Update(dt);
}

以下代码将检索 LOB 数据：

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);
// 对数据流执行某些操作
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();

控制台输出为：

BLOB length = 1171; test CLOB text; test NCLOB text

返回页首

RAW

存储不希望被解释的可变长度的二进制数据或其他数据。Oracle 不会象处理 VARCHAR2 那样试图解释这些 RAW 数据，例如不会转换字符集或填充空白。

RAW 数据类型是为了与现有应用程序兼容而提供的，在新的应用程序中，应该使用 LOB 和 BFILE 数据类型。

有两种 RAW 类型：

•

RAW

与 VARCHAR2 的最大长度相同（32767 字节），声明变量时必须指定此长度。

•

LONG RAW

可变长度的二进制数据，最大长度为 2GB。一个表格中只允许有一个 LONG RAW 数据类型。

以下代码将创建一个包含 RAW 和 LONG RAW 列的表格：

OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE MyRawTable (Id NUMBER, RawCol RAW(50), LongRawCol LONG RAW)";
cmd.ExecuteNonQuery();

以下代码将创建一个行并将数据插入到 RAW 和 LONG RAW 列中：在 RAW 字段中插入字节数组，而在 LONG RAW 字段中插入用户选定的文件的内容：

OpenFileDialog ofd = new OpenFileDialog();

if (ofd.ShowDialog() == DialogResult.OK)
{
// 创建 RAW
byte[] raw = new byte[50];
for (byte i = 0; i < raw.Length; i++)
raw[i] = i;

// 创建 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();
// 获取架构
da.FillSchema(dt, SchemaType.Source);

OracleCommandBuilder cb = new OracleCommandBuilder(da);

int id = 1;
// 创建一个包含该数据的行
DataRow row = dt.NewRow();
row["Id"] = id;
row["RawCol"] = raw;
row["LongRawCol"] = longRaw;
dt.Rows.Add(row);

// 更新表格
da.Update(dt);
}

以下代码将 RAW 和 LONG RAW 字段中的数据检索到 OracleBinary 结构中：

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

此数据类型指向 PL/SQL 查询所返回的结果集。REF CURSOR 被用作输出参数，负责将结果集从 Oracle 结构化程序返回给调用应用程序。可以在行集内移动游标以返回多行。

使用 REF CURSOR 输出参数将结果集从 Oracle 结构化程序返回给调用应用程序。

以下数据包和数据包正文将 REF CURSOR 返回给 HR.EMPLOYEES 表格中包含所有员工的结果集。数据包定义如下：

CREATE OR REPLACE PACKAGE GET_EMPLOYEES AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE GetEmployees (cur_Employees OUT T_CURSOR);
END GET_EMPLOYEES;

数据包正文定义一个单独的过程，该过程检索所有员工的所有数据并作为一个 REF CURSOR 输出参数返回：

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 检索存储过程 GetEmployees 返回的结果集。

// 创建连接
OracleConnection conn = new OracleConnection(CONNECTSTRING);

// 为存储过程创建命令
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "GET_EMPLOYEES.GetEmployees";
cmd.CommandType = CommandType.StoredProcedure;

// 添加 REF CURSOR 参数以检索结果集
cmd.Parameters.Add("cur_Employees", OracleType.Cursor).Direction =
ParameterDirection.Output;

// 打开连接并创建 DataReader
conn.Open();
OracleDataReader dr = cmd.ExecuteReader();

// 输出员工的 ID、姓名和电子邮件地址（前四个字段）
// 并关闭连接。
while(dr.Read())
{
for(int i = 0; i <= 3; i++)
Console.Write(dr[i].ToString() + ";");

Console.WriteLine();
}
conn.Close();

控制台输出将列出所有员工的 ID、姓名和电子邮件地址。前五行为：

100;Steven;King;SKING;
101;Neena;Kochhar;NKOCHHAR;
102;Lex;De Haan;LDEHAAN;
103;Alexander;Hunold;AHUNOLD;
104;Bruce;Ernst;BERNST;

返回页首

ROWID

Oracle 使用此数据类型存储数据库中每一行的唯一地址。ROWID 类型包括：

•

ROWID

10 字节长的数据类型，用于表示数据库中某行的页、记录和偏移地址。其值以 bbbbbbbb.ssss.ffff 格式表示实际显示的二进制数据，其中：

•	bbbbbbbb 为块 ID
•	ssss 为块中的序列
•	ffff 为文件 ID

•

UROWID

通用 ROWID (UROWID)，它支持逻辑和物理 ROWID 以及外部表格（包括非 Oracle 表格）的 ROWID。UROWID 的最大长度为 4000 字节。

•

ROWID 伪列

每个表格都有一个名为 ROWID 的伪列，它允许您使用保留字 ROWID 作为列名来访问任意行的地址。ROWID 伪列不存储在数据库中，不占用任何空间，也不能被修改或删除。只要表格中存在某个行，就不能修改该行的 ROWID，ROWID 提供了访问表格行的最快方式。

以下代码将创建一个包含 ROWID 和 UROWID 列的表格：

OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE MyRowidTable (Id NUMBER, RowidCol ROWID,
UrowidCol UROWID)";
cmd.ExecuteNonQuery();

以下代码将创建一条记录并在 ROWID 和 UROWID 字段中存储 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 伪列的值存储到 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();

控制台输出为：

AAAHM7AAFAAAABWAAA; AAAHM7AAFAAAABWAAA
100; Steven; King

返回页首

Timestamp

用于表示日期/时间值的数据类型。此数据类型向 DATE 类型添加小数秒以提高精度。Timestamp 数据类型使用精度参数定义秒字段中存储的数据的精度，精度值范围从 0 到 9，默认值为 6。有三种 Timestamp 数据类型：

•

TIMESTAMP

用于表示日期/时间的可变大小值，大小范围从 7 个字节到 11 个字节。

•

TIMESTAMP WITH TIME ZONE

13 个字节的固定大小值，除日期/时间外还包括时区。可以使用 UTC 偏移（与英国格林威治零子午线的当前时间或格林威治标准时间 [GMT] 之间的时差）或 V$TIMEZONE_NAMES 表格中的时区名来设置时区。您可以使用以下查询获取有效区域的列表：

SELECT tzname, tzabbrev FROM V$TIMEZONE_NAMES

•

TIMESTAMP WITH LOCAL TIME

TIMESTAMP 的值与 TIMESTAMP WITH TIME ZONE 的值类似，只是数据被规范化为数据库服务器的时区。此数据类型允许您调整日期/时间以说明时区差异，包括该时区是否遵守夏时制。不会存储 UTC 偏移。

以下代码将创建一个包含 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();

以下代码将当前时间插入到每个 Timestamp 数据类型的列中：

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 数据类型的值：

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();

控制台输出为：

7/9/2004 1:43:31 PM; 7/9/2004 1:43:31 PM; 7/9/2004 10:43:31 AM

返回页首

UriType

UriType 数据类型存储指向内部或外部统一资源标识符 (URI) 资源的指针，它们可以引用一个文档或文档中的特定部分。从通用 UriType 数据类型派生出以下子类型：

•

HttpURIType

使用超文本传输协议 (HTTP) 访问的 Web 页或文件的 URL。

•

DBUriType

对数据库内的数据（单行、多行或单个列）的引用。可以使用成员函数来访问数据，这些成员函数的语法与 XPath 的语法类似。

•

XdbUriType

存储 XML 文档的引用，该文档存储在数据库中的 Oracle XML DB 知识库内。

您可以使用 UriType 或某一特定子类型创建列。

UriType 包含以下函数，它们可用于任何子类型：

函数	说明
createUri(uri IN VARCHAR2)	构造一个 URITYPE 子类型。每个子类型都有其自己的构造函数： DBUriType dbUriType() HttpUriType httpUriType() XDBUriType XDBUritype()
getBlob()	返回 URL 指向的 BLOB。
getClob()	返回 URL 指向的值，作为使用数据库字符集编码的 CLOB。
getContentType()	返回 URL 的 MIME 信息。
getExternalUrl()	与 getUrl() 类似，只是它转义了 URL 中的字符。
getUrl()	返回数据类型中存储的 URL。
getXML()	返回 URI 的 XMLType 对象，URI 必须指向有效的 XML 文档。

以下代码将创建一个包含 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();

以下代码将在表格中插入一个数据行。此行包含 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();

以下代码将检索三个 UriTypes 的 URL 以及作为 CLOB 的 HTTPURITYPE 和 DBURITYPE 的内容：

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();

控制台输出为：

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 数据类型包括内置的数据类型和其他由用户定义的数据类型。User-defined 数据类型提取应用程序数据中真实世界实体的结构。此类型是以下对象的模板：该对象通过名称标识并具有属性（内置类型或其他由用户定义的类型）和方法。

以下代码将创建用户定义的类型 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 数据类型列的表格：

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();

以下代码将检索该行的数据：

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();

控制台输出为：

1; Bill Hamilton; 5

返回页首

XmlType

此数据类型使用一个隐含的 CLOB 在表格中存储 XML 文档。此数据类型包含多个成员函数，它们可用于创建 XML、对文档执行 XPath 查询、提取数据片断、验证 XML 以及返回内容。

以下代码将创建一个包含 XMLType 列的表格：

OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE MyXmltypeTable (Id NUMBER,
XmlTypeCol SYS.XMLTYPE)";
cmd.ExecuteNonQuery();

以下代码将插入一行并使用 XMLType 的 CREATEXML 成员函数设置 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 成员函数检索 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();

控制台输出为：

<?xml version="1.0"?><EMAIL>SKING</EMAIL>

返回页首

结论

Oracle 提供的多种数据类型可以帮助您处理复杂数据，包括存储在数据库内或数据库外的大型对象、XML、通用 (ANY) 数据、日期和时间以及用户定义的聚合数据结构。Microsoft .NET Framework 1.1 中的 Microsoft .NET Oracle Provider 提供了 System.Data.OracleClient 命名空间中的类型，使您可以方便地访问和操作存储在这些类型中的数据。

返回页首

参考资料

ADO.NET Cookbook

ADO.NET in a Nutshell

Bill Hamilton 是一位软件设计师，他专门研究使用 Microsoft .NET 和 J2EE 技术设计、开发以及实现分布式应用程序。作为新技术的最早采用者，他经常为客户提供评估、建议和帮助，以帮助客户有效地使用新技术。Bill 著有两本关于 ADO.NET 的书，如上面的参考资料部分所示。

返回页首

上一篇： Asp生成RSS的类_给网站加上RSS第1/2页

下一篇： Redis作者：深度剖析Redis持久化

使用 ADO.NET 和 Oracle 进行高级数据访问

使用 ADO.NET 和 Oracle 进行高级数据访问

引言

Any

BFILE

Interval

LOB

RAW

REF CURSOR

ROWID

Timestamp

UriType

User Defined

XmlType

结论

参考资料

使用imp和exp命令对Oracle数据库进行导入导出操作详解

Oracle数据库无法使用localhost和127.0.0.1访问

Oracle数据库无法使用localhost和127.0.0.1访问

Oracle——21使用数据泵data bump进行导入和导出

ORACLE使用EXPDP和IMPDP数据泵进行导出导入的方法