Oracle存储过程传参，参数使用date日期类型的参数，返回结果集

存储过程中使用date日期类型的参数，需要定义日期类型date的变量重新赋值一次，赋值的过程貌似会做一次转换，之后才能正常使用，不然使用这个日期查询结果不准确，经常会得到无效的结果。

CREATE OR REPLACE PROCEDURE PROC_SITM_EFFECT_SP(BEGIN_DATE     DATE,

                                                END_DATE       DATE,

                                                COUNT_END_DATE DATE,

                                                SITM_TYPE      VARCHAR2,

                                                WELL_TYPE      INT,

                                                MY_CUR         OUT SYS_REFCURSOR) IS

  V_WELL_ID    VARCHAR2(50);

  STIMCONENT   VARCHAR2(200);

  V_START_DATE DATE;

   V_BEGIN_DATE DATE := BEGIN_DATE;

  V_END_DATE   DATE := END_DATE;

  V_COUNT_END_DATE  DATE := COUNT_END_DATE;--切记要重新赋值才能正常使用

BEGIN

........

--返回结果集

OPEN MY_CUR FOR

    SELECT BLOCK_NAME,

           WELL_NAME,

           --ZONE_NAME,

           STIM_CONENT,

           START_DATE,

           COMPLETION_DATE,

           PRE_LIQ,

           PRE_OIL,

           PRE_WATER_CUT,

           PRE_SALT_CUT,

           PRE_DWATER_LEVEL,

           AFT_LIQ,

           AFT_OIL,

           AFT_WATER_CUT,

           AFT_SALT_CUT,

           AFT_DWATER_LEVEL,

           CUR_LIQ,

           CUR_OIL,

           CUR_WATER_CUT,

           CUR_SALT_CUT,

           CUR_DWATER_LEVEL,

           PROD_DAYS,

           EFFECTIVE_DAYS,

           EFFECTIVE_INCREASE,

           ACC_OIL_INCREASE,

           ACC_OIL_INCREASE_YEAR

      FROM TEMP_STIM_EFFECT_RESULT_OIL

     ORDER BY WELL_NAME;

END;

存储过程的传参及返回DataTable的存储过程的调用：

using System.Data.OracleClient;

 public DataTable GetUCProdData(ref DataQueryCondition conditions)

{

。。。

dataTable.TableName = "QueryTable";//数据库查询结果返回，DataTable必须有name。

            OracleParameter[] parameters = {

            new OracleParameter("result", OracleType.Cursor, 0, ParameterDirection.ReturnValue, true, 0, 0, "",

                 DataRowVersion.Default, Convert.DBNull ),

            new OracleParameter("begin_date", OracleType.DateTime),

            new OracleParameter("end_date",   OracleType.DateTime),

            new OracleParameter("count_end_date", OracleType.DateTime),

            new OracleParameter("sitm_type",   OracleType.VarChar, 200)};

            parameters[1].Value = DateTime.Parse(conditions.date_Query[0]);

            parameters[2].Value = DateTime.Parse(conditions.date_Query[1]);

            parameters[3].Value = DateTime.Parse(conditions.date_Query[2]);

            parameters[4].Value = conditions.wellType;

            using(OracleConnection connection = new OracleConnection( dataBaseInfo.GetConnectionString() ))

            {

                OracleCommand command = new OracleCommand("func_sitm_effect_sp", connection);

                command.CommandType = CommandType.StoredProcedure;

                foreach(OracleParameter parameter in parameters)

                        command.Parameters.Add( parameter );

                OracleDataAdapter adp = new OracleDataAdapter(command);

                adp.Fill(dataTable);

            }

            return dataTable;

}