Oracle相关操作
长期以来，我一直用的是 MS SQL Server / Access 数据库，通过 .NET 访问 MS 自家的东西几乎没碰到过什么麻烦。最近项目中要用 Oracle 作为数据库，学习研究了一些 .NET 访问 Oracle 的东西，发现问题倒真的不少。
1。System.Data.OracleClient 和 System.Data.OleDb 命名空间
虽然通过这两个命名空间的类都可以访问 Oracle 数据库，但和 SQL Server 类似的（System.Data.SqlClient 命名空间的类效率要比 System.Data.OleDb 命名空间中的类高一些），System.Data.OracleClient 命名空间中的类要比 System.Data.OleDb 命名空间的类效率高一些（这一点我没有亲自验证，但大多数地方都会这么说，而且既然专门为 Oracle 作的东西理论上也应该专门作过针对性的优化）。
当然还有另一点就是从针对性上说，System.Data.OracleClient 要更好一些：
比如数据类型，System.Data.OleDb.OleDbType 枚举中所列的就没有 System.Data.OracleClient.OracleType 枚举中的那些有针对性；另外，Oracle 的 Number 类型如果数字巨大，超出 .NET 数据类型范围的情况中，就必须使用 System.Data.OracleClient 中的专门类 -- OracleNumber 类型。
好了，不再赘述这两个的比较，下面主要讨论 System.Data.OracleClient 命名空间中的类型，即 ADO.NET for Oracle Data Provider (数据提供程序)。
2。数据库连接：
无论是 System.Data.OleDb 还是 System.Data.OracleClient 访问 Oracle 都需要在 .NET 运行的机器（ASP.NET 中就是 Web 服务器）安装 Oracle 客户端组件。（这一点是和 MS 的两种数据库不同的，MS 的东西安装 MDAC: Microsoft Data Access Component 2.6 以上版本后，就无须再安装 SQL Server 客户端或者 Office 软件，就能访问。）
System Requirements：
(1)如用 System.Data.OracleClient 访问 Oracle，客户端组件版本应在 Oracle 8i Client Release 3 (8.1.7)以上版本。MS 只确保访问 Oracle 8.1.6、Oracle 8.1.7、Oracle 9i 服务器时的情况。MDAC 2.6 以上。
(2)如用 System.Data.OleDb 访问 Oracle，客户端组件版本 7.3.3.4.0 以上或 8.1.7.4.1 以上。MDAC 2.6 以上。
如服务器为 Oracle8i 以上，客户端组件版本应为 8.0.4.1.1c。
在 .NET 运行的机器中，安装 Oracle 客户端，然后打开 Net Manager (Oracle 9i) / Easy Config (Oracle 8i) 按你以前的经验设置本地服务的映射（这里的服务名将用于数据库连接串）。
System.Data.OracleClient 中访问 Oracle 数据库的连接串是：
User ID=用户名; Password=密码; Data Source=服务名
(上述为一般的连接串，详细的连接串项目可以在 System.Data.OracleClient.OracleConnection.ConnectionString 属性的文档中找到。)
System.Data.OleDb 中的访问 Oracle 数据库的连接串是：
Provider=MSDAORA.1; User ID=用户名; Password=密码; Data Source=服务名
3。Oracle 中的数据类型：
Oracle 的数据类型和 SQL Server 相比，要“奇怪”一些：SQL Server 的大多数据类型很容易找到 .NET 中比较接近的类型，Oracle 中的类型就离 .NET 类型远了许多，毕竟 Oracle 是和 Java 亲近的数据库。
number: 数字类型，一般是 Number(M,N)，M是有效数字，N是小数点后的位数（默认0），这个是按十进制说的。
nvarchar2: 可变长字符型（Unicode），这个比较像 SQL Server 的 nvarchar（但不知 Oracle 为什么加了个“2”）。（去掉“n”为非 Unicode 的，下同。）
nchar: 定长字符型(Unicode)。
nclob: “写作文”的字段，存储大量字符（Unicode）时用。
date: 日期类型，比较接近 SQL Server 的 datetime。
Oracle 中字段不能是 bit 或者 bool 之类的类型，一般是 number(1) 代替的。
和 SQL Server 一样在 SQL 命令中，字符类型需要用单引号（'）隔开，两个单引号（''）是单引号的字符转义（比如： I'm fat. 写入一个 SQL 命令是： UPDATE ... SET ...='I''m fat.' ...）。
比较特殊的是日期类型：比如要写入 2004-7-20 15:20:07 这个时刻需要如下写：
UPDATE ... SET ... = TIMESTAMP '2004-7-20 15:20:07' ...
注意这里使用了 TIMESTAMP 关键字，并使用单引号隔开；另外请注意日期格式，上面的格式是可识别的，Oracle 识别的格式没有 SQL Server 那般多。这是和 SQL Server 不同的地方。
顺便提一句：Access 中的日期类型是用井号(#)隔开的，UPDATE ... SET ... = #2004-7-20 15:20:07# ...
4。访问 Oracle 过程/函数（1）
SQL Server 作程序时经常使用存储过程，Oracle 里也可以使用过程，还可以使用函数。Oracle 的过程似乎是不能有返回值的，有返回值的就是函数了（这点有些像 BASIC，函数/过程区分的很细致。SQL Server 存储过程是可以有返回值的）。
.NET 访问 Oracle 过程/函数的方法很类似于 SQL Server，例如：
OracleParameter[] parameters = {
new OracleParameter("ReturnValue", OracleType.Int32, 0, ParameterDirection.ReturnValue, true, 0, 0, "",
DataRowVersion.Default, Convert.DBNull )
new OracleParameter("参数1", OracleType.NVarChar, 10),
new OracleParameter("参数2", OracleType.DateTime),
new OracleParameter("参数3", OracleType.Number, 1)
};
parameters[1].Value = "test";
parameters[2].Value = DateTime.Now;
parameters[3].Value = 1; // 也可以是 new OracleNumber(1);
OracleConnection connection = new OracleConnection( ConnectionString );
OracleCommand command = new OracleCommand("函数/程名", connection);
command.CommandType = CommandType.StoredProcedure;
foreach(OracleParameter parameter in parameters)
command.Parameters.Add( parameter );
connection.Open();
command.ExecuteNonQuery();
int returnValue = parameters[0].Value; //接收函数返回值
connection.Close();
Parameter 的 DbType 设定请参见 System.Data.OracleClient.OracleType 枚举的文档，比如：Oracle 数据库中 Number 类型的参数的值可以用 .NET decimal 或 System.Data.OracleClient.OracleNumber 类型指定； Integer 类型的参数的值可以用 .NET int 或 OracleNumber 类型指定。等等。
上面例子中已经看到函数返回值是用名为“ReturnValue”的参数指定的，该参数为 ParameterDirection.ReturnValue 的参数。
5。访问 Oracle 过程/函数 (2)
不返回记录集（没有 SELECT 输出）的过程/函数，调用起来和 SQL Server 较为类似。但如果想通过过程/函数返回记录集，在 Oracle 中就比较麻烦一些了。
在 SQL Server 中，如下的存储过程：
CREATE PROCEDURE GetCategoryBooks
(
@CategoryID int
)
AS
SELECT * FROM Books
WHERE CategoryID = @CategoryID
GO
在 Oracle 中，请按以下步骤操作：
(1)创建一个包，含有一个游标类型：（一个数据库中只需作一次）
CREATE OR REPLACE PACKAGE Test
AS
TYPE Test_CURSOR IS REF CURSOR;
END Test;
(2)过程：
CREATE OR REPLACE PROCEDURE GetCategoryBooks
(
p_CURSOR out Test.Test_CURSOR, -- 这里是上面包中的类型，输出参数
p_CatogoryID INTEGER
)
AS
BEGIN
OPEN p_CURSOR FOR
SELECT * FROM Books
WHERE CategoryID=p_CatogoryID;
END GetCategoryBooks;
(3).NET 程序中：
OracleParameters parameters = {
new OracleParameter("p_CURSOR", OracleType.CURSOR, 2000, ParameterDirection.Output, true, 0, 0, "",
DataRowVersion.Default, Convert.DBNull),
new OracleParameter("p_CatogoryID", OracleType.Int32)
};
parameters[1].Value = 22;
OracleConnection connection = new OracleConnection( ConnectionString );
OracleCommand command = new OracleCommand("GetCategoryBooks", connection);
command.CommandType = CommandType.StoredProcedure;
foreach(OracleParameter parameter in parameters)
command.Parameters.Add( parameter );
connection.Open();
OracleDataReader dr = command.ExecuteReader();
while(dr.Read())
{
// 你的具体操作。这个就不需要我教吧？
}
connection.Close();
另外有一点需要指出的是，如果使用 DataReader 取得了一个记录集，那么在 DataReader 关闭之前，程序无法访问输出参数和返回值的数据。
好了，先这些，总之 .NET 访问 Oracle 还是有很多地方和 SQL Server 不同的，慢慢学习了。
一、调用一个带输入、输出参数的存储过程
首先，在Oralce中创建存储过程如下：
or replace procedure
select;
然后，在C#项目中作如下调用：
string connectionString = "Data Source=YXZHANG;User ID=YXZHANG;Password=YXZHANG";
string queryString = "getrecords";
OracleConnection cn = new OracleConnection(connectionString);
OracleCommand cmd = new OracleCommand(queryString,cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("name_out",OracleType.VarChar,20);
cmd.Parameters["name_out"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("age_in",21);
try
{
cn.Open();
cmd.ExecuteNonQuery();
Console.WriteLine("Name is:{0}",cmd.Parameters["name_out"].Value.ToString());
cn.Close();
}
catch( OracleException ex )
{
Console.WriteLine("Exception occurred!");
Console.WriteLine("The exception message is:{0}",ex.Message.ToString());
}
finally
{
Console.WriteLine("------------------End-------------------");
}
调用不返回数据的存储过程
首先，在Oralce中创建存储过程如下：
or replace procedure UserAge in varchar2) is
begin
insert into test values (UserID, UserName, UserAge);
end;
然后，在C#项目中作如下调用：
connectionString = "Data Source=YXZHANG;User ID=YXZHANG;Password=YXZHANG";
queryString = "insertRecord";
new OracleConnection(connectionString);
new OracleCommand(queryString,cn);
cmd.Parameters.Add("UserID","007");
cmd.Parameters.Add("UserName","Dell");
cmd.Parameters.Add("UserAge","40");
try
{
cn.Open();
cmd.ExecuteNonQuery();
Console.WriteLine("Record inserted!");
cn.Close();
}
catch( OracleException ex )
{
Console.WriteLine("Exception occurred!");
Console.WriteLine("The exception message is:{0}",ex.Message.ToString());
}
finally
{
Console.WriteLine("------------------End-------------------");
}
小结：
不返回数据的存储过程一般有Delete, Insert, Update等。虽然它们的SQL语句不同，但是调用方面都是一样的。只要传入正确的参数，一般不会出什么问题。不过还是要注意，在使用OralceClient作为数据提供者的时候，参数名称一定要和存储过程定义中的一致！至于参数添加的顺序倒是无所谓的，因为有参数名称作为区别。
三、IDENTITY 和 SEQUENCE
Sql Server中，定义一个列为递增列很容易，但我在Oracle中却怎么也找不到设置的方法。不过查了点资料后知道Oracle中有个叫Sequence的对象，产生一个唯一的序列号，类似于Sql Server中的IDENTITY。于是，我做了如下实验：
首先，在Oracle中创建了一个名为TEST_SEQ的Sequence对象，SQL语句如下：
sequence
with102
by1
;
语法应该是比较易懂的，最小最大值分别用minvalue,maxvalue表示，初始值是102（这个数字是动态变化的，我创建的时候设的是100，后因插入了2条数据后就自动增加了2），increment当然就是步长了。在PL/SQL中可以用test_seq.nextval访问下一个序列号，用test_seq.currval访问当前的序列号。
定义完了Sequence，接下来就是创建一个存储过程InsertRecordWithSequence：
这次我修改了test表的定义，和前面的示例不同。其中，UserID是PK。
InsertRecordWithSequence(UserID out number,
UserName in varchar2,
UserAge in number)
begin
insert into test(id, name, age) --
values(test_seq.nextval, UserName, UserAge);
返回PK值。注意Dual表的用法*/
test_seq.currval into UserID from dual;
InsertRecordWithSequence;
接下来，就是在C#中进行调用了：
connectionString = "Data Source=YXZHANG;User ID=YXZHANG;Password=YXZHANG";
string queryString = "InsertRecordWithSequence";
OracleConnection cn = new OracleConnection(connectionString);
OracleCommand cmd = new OracleCommand(queryString,cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("UserID",OracleType.Number);
cmd.Parameters["UserID"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("UserName","Jack");
cmd.Parameters.Add("UserAge",40);
try
{
cn.Open();
int rowAffected = cmd.ExecuteNonQuery();
Console.WriteLine("{0}行已插入。",rowAffected);
Console.WriteLine("插入行的ID为：{0}",cmd.Parameters["UserID"].Value.ToString());
cn.Close();
}
catch( OracleException ex )
{
Console.WriteLine("Exception occurred!");
Console.WriteLine("The exception message is:{0}",ex.Message.ToString());
}
finally
{
Console.WriteLine("------------------End-------------------");
}
小结：
使用Sequece对象可以很容易地创建唯一序列，在存储过程中的调用也十分方便，只要sequence_name.nextval以及sequence.currval就能得到下一个以及当前的序列值。倒是Dual表值得注意。
为了让存储过程返回结果集，必须定义一个游标变量作为输出参数。这和Sql Server中有着很大的不同！并且还要用到Oracle中“包”（Package）的概念，似乎有点繁琐，但熟悉后也会觉得很方便。
关于“包”的概念，有很多内容可以参考，在此就不赘述了。首先，我创建了一个名为TestPackage的包，包头是这么定义的：
or replace package TestPackage is
type mycursor is ref cursor; -- 定义游标变量 procedure GetRecords(ret_cursor out mycursor); -- 定义过程，用游标变量作为返回参数 TestPackage;
包体是这么定义的：
or replace package body TestPackage is
/*过程体*/ procedure GetRecords(ret_cursor out mycursor) as
begin
open ret_cursor for select * from test;
end GetRecords;
TestPackage; 已经万事具备了，让我们前台调用试试：
connectionString = "Data Source=YXZHANG;User ID=YXZHANG;Password=YXZHANG";
string queryString = "TestPackage.GetRecords"; // OracleConnection cn = new OracleConnection(connectionString);
OracleCommand cmd = new OracleCommand(queryString,cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("ret_cursor",OracleType.Cursor); // cmd.Parameters["ret_cursor"].Direction = ParameterDirection.Output;
try
{
cn.Open();
OracleDataReader dr = cmd.ExecuteReader();
int i = 1;
while( dr.Read() )
{
Console.WriteLine("Record {0}:",i++);
Console.WriteLine("ID:{0} Name:{1} Age:{2}",
Console.WriteLine();
}
dr.Close(); // cn.Close(); //DataReader }
catch( OracleException ex )
{
Console.WriteLine("Exception occurred!");
Console.WriteLine("The exception message is:{0}",ex.Message.ToString());
}
