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