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_g

cnming

1814人浏览 · 2008-11-11 19:41:00

cnming · 2008-11-11 19:41:00 发布

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