（转）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)
{
}