c#调用oracle的package里面的函数和存储过程的应用

下面列举如何在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);

mandType = 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);

mandType = 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)

{

}