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

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