（转）C#中调用ORACLE的PACKAGE里方法和存储过程的应用

转自http://www.cnblogs.com/aosiyelong/archive/2011/07/13/2105444.html

下面列举如何在C#中调用ORACLE的PACKAGE的东西,主要包括PACKAGE的方法和存储过程

一,首先在ORACLE里建立如下PACKAGE
PACKAGE分SPEC和body两部分.
1.SPEC是声明部分.

CREATE OR REPLACE PACKAGE
FirstPage is

type outlist is ref cursor ;

Procedure p_get(
maxrow in number , minrow in number , return_list
out outlist );

function f_get( str in varchar2 ) return varchar2 ;

END
FirstPage;
/

2.BODY是功能实现部分

CREATE OR REPLACE package body
FirstPage is

Procedure p_get(
maxrow in number , minrow in number , return_list
out outlist )
is
begin

open
return_list for
select * from ( select a. * ,rownum rnum
from
IPS_WL_INNOLUXPN a where rownum <= maxrow)
where
rnum >= minrow;
end
;

Function f_get( str in varchar2 )
return varchar2

is
str_temp varchar2 ( 200 ) : = ' Good Luck! ' ;
begin
str_temp
: =
str_temp || str ;
return
str_temp;
end f_get;


end
FirstPage;
/

以上,就在ORACLE里面建立了一个名字叫FIRSTPAGE的PACKAGE,这个PACKAGE里面有一个名叫P_GET的存储过程,它有3个参数,一个是maxrow,minrow是输入,result_list是个CURSOR,用来存放传回的数据集

二.C#部分代码:

string connStr = " Data
Source=E4MT;user id=mnt;password=mnt " ;
OracleConnection orcn = new
OracleConnection(connStr);

// C#
調用Package中的Function
OracleCommand cmd = new
OracleCommand( " FIRSTPAGE.f_get " ,orcn);
cmd.CommandType =
CommandType.StoredProcedure;
OracleParameter p1 = new
OracleParameter( " str " ,OracleType.VarChar, 10 );
p1.Direction
=
ParameterDirection.Input;
p1.Value = " Andy " ;
OracleParameter
p2 =
new
OracleParameter( " result " ,OracleType.VarChar, 100 );
p2.Direction
=
ParameterDirection.ReturnValue;
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
orcn.Open();
cmd.ExecuteNonQuery();
orcn.Close();

// C#調用Package中的Procedure
cmd = new
OracleCommand( " FIRSTPAGE.p_get " ,orcn);
cmd.CommandType =
CommandType.StoredProcedure;
p1 = new
OracleParameter( " maxrow " ,OracleType.Number);
p1.Direction =
ParameterDirection.Input;
p1.Value = 50 ;
p2
=
new
OracleParameter( " minrow " ,OracleType.Number);
p2.Direction =
ParameterDirection.Input;
p2.Value = 10 ;
OracleParameter p3 = new
OracleParameter( " return_list " ,OracleType.Cursor);
p3.Direction =
ParameterDirection.Output;
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);

DataTable
dt =
new
DataTable();
OracleDataAdapter da = new
OracleDataAdapter(cmd);
da.Fill(dt);

foreach (DataRow row
in
dt.Rows)
{
}