我们来自五湖四海,不为别的,只因有共同的爱好,为中国互联网发展出一分力!

Oracle相关操作

2014年05月09日22:06 阅读: 3286 次

标签: 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());

 

}

 

分享到: 更多
©2021 安全焦点 版权所有.
人才招聘联系我们