C#中調用ORACLE的PACKAGE裡方法和預存程序的應用

C#中調用ORACLE的PACKAGE裡方法和預存程序的應用

2008-12-05 23:01:21| 分類： SQL/Oracle | 標籤： |字型大小大中小 訂閱

下面列舉如何在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)
{
}