[C#對Oracle操作]C#操作調用Orcale預存程序有參數

標籤：

/// <summary>        /// 擷取ERP固定資產計提資料        /// </summary>        /// <param name="strCompanyCode">公司代碼</param>        /// <param name="strClient">應用環境</param>        /// <param name="strLanguage">語言</param>        /// <param name="strRealDeprarea">賬套代碼：‘01’、‘02’、‘03’</param>        /// <param name="strFiscalYear">會計年度</param>        /// <param name="strMonth">會計月度：‘mm’</param>        /// <returns>DataTable資料集</returns>        public static DataTable getData(string strCompanyCode, string strClient, string strLanguage, string strRealDeprarea, string strFiscalYear, string strMonth)        {            OracleConnection myOracleConnection = null;            try            {                DataTable dt = new DataTable();                myOracleConnection = new OracleConnection();                myOracleConnection.ConnectionString = ConfigurationManager.ConnectionStrings["FTERP"].ConnectionString;                myOracleConnection.Open();                OracleCommand cmd = new OracleCommand("FXA_MAINVALUEINFO", myOracleConnection);                cmd.CommandType = CommandType.StoredProcedure;                cmd.Parameters.Add("v_companycode", OracleType.Char).Direction = ParameterDirection.Input; //指明傳入的參數是給Oracle預存程序用的                cmd.Parameters["v_companycode"].Value = strCompanyCode;                cmd.Parameters.Add("v_client", OracleType.Char).Direction = ParameterDirection.Input;                cmd.Parameters["v_client"].Value = strClient;                cmd.Parameters.Add("v_language", OracleType.Char).Direction = ParameterDirection.Input;                cmd.Parameters["v_language"].Value = strLanguage;                cmd.Parameters.Add("v_realdeprarea", OracleType.Char).Direction = ParameterDirection.Input;                cmd.Parameters["v_realdeprarea"].Value = strRealDeprarea;                cmd.Parameters.Add("v_fiscalyear", OracleType.Char).Direction = ParameterDirection.Input;                cmd.Parameters["v_fiscalyear"].Value = strFiscalYear;                cmd.Parameters.Add("v_postperiod", OracleType.Char).Direction = ParameterDirection.Input;                cmd.Parameters["v_postperiod"].Value = strMonth;                cmd.Parameters.Add("v_returnvalue", OracleType.Float).Direction = ParameterDirection.Output;                OracleDataAdapter da = new OracleDataAdapter(cmd);                da.Fill(dt);                string eval = cmd.Parameters["v_returnvalue"].Value.ToString();                //myOracleConnection.Close();                DataTable dtResult = dt.Clone();                foreach (DataRow dr in dt.Rows)                {                    dtResult.Rows.Add(dr.ItemArray);                }                return dtResult;            }            catch (Exception ex)            {                throw new Exception(ex.ToString());            }            finally            {                if (myOracleConnection.State == ConnectionState.Open)                {                    myOracleConnection.Close();                }            }        }

如下是Oracle的預存程序CREATE OR REPLACE PROCEDURE BZREPDB.FXA_MAINVALUEINFO (   v_companycode    IN     CHAR,   v_client         IN     CHAR,   v_language       IN     CHAR,   v_realdeprarea   IN     CHAR,   v_fiscalyear     IN     CHAR,   v_postperiod     IN     CHAR,   v_returnvalue       OUT NUMBER)

 

[C#對Oracle操作]C#操作調用Orcale預存程序有參數