asp.net中oracle存储过程的运用

本文内容：1、oracle带游标存储过程写法

                    2、一层程序中直接调用

                    3、三层架构中程序写法

1、oracle存储过程（带游标），输出游标数据集。

输入参数flightid，输出游标cur_meal

create or replace procedure proc_find_meal(flightId in varchar2,cur_Meal out sys_refcursor) is
begin
open cur_Meal for
  select count(ww.food_name) as count_meal,ww.food_name from (
      select vv.flight_id,(case when vv.duty_code='F' and vv.serial_no=1 then vv.food_name||'(机长)' else vv.food_name end)as food_name from
      (
      select c.flight_id,c.p_code,c.duty_code,c.serial_no,c.c_name,o.menu_id,m.food_name
      from flight_crew c, meal_order o,meal_menu m
      where c.p_code=o.p_code and c.flight_id=o.flight_id and o.menu_id=m.menu_id and c.flight_id=flightId
      union
      select cc.flight_id,cc.p_code,cc.duty_code,cc.serial_no,cc.c_name,pp.menu_id,nvl(pp.food_name,'标准餐') as food_name
       from
      (
      select c.p_code,m.meal_type_id,c.flight_id,c.flight_date,c.flight_no,c.departure_airport,c.arrival_airport,c.duty_code,c.serial_no,c.c_name from flight_crew c left join meal_weixin_staff m on c.p_code = m.p_code where c.flight_id =flightId
      )cc
      left join
      (
      select p.flight_no,p.departure_airport,p.arrival_airport,p.crew_meal,m.menu_id,m.food_name,m.meal_type_id,p.begin_date,p.end_date from meal_menu m,meal_plans p where p.crew_meal = m.crew_meal
      ) pp on cc.flight_no=pp.flight_no and cc.departure_airport = pp.departure_airport and cc.arrival_airport=pp.arrival_airport and cc.meal_type_id = pp.meal_type_id  and cc.flight_date>=pp.begin_date and cc.flight_date<=pp.end_date
      where cc.p_code not in (select o.p_code from meal_order o where o.flight_id =flightId)
  )vv )ww group by ww.food_name;
end proc_find_meal;

2、一层程序中直接调用

            IDbConnection conn = new OracleConnection(PubConstant.ConnectionStringFoc);
            conn.Open();
            IDbTransaction trans = conn.BeginTransaction();//Session.Connection.;
            IDbCommand cmd = conn.CreateCommand();
            cmd.Transaction = trans;    //Set Transaction For Command  
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "proc_find_meal";

            System.Data.OracleClient.OracleParameter p;
            p = new System.Data.OracleClient.OracleParameter("flightId", System.Data.OracleClient.OracleType.VarChar, 20);
            cmd.Parameters.Add(p);
            p.Direction = System.Data.ParameterDirection.Input;
            p.Value = FLIGHT_ID;

            //输出的DataSet  
            p = new System.Data.OracleClient.OracleParameter("cur_Meal", System.Data.OracleClient.OracleType.Cursor);
            cmd.Parameters.Add(p);
            p.Direction = System.Data.ParameterDirection.Output;//设置为Output  

            DataSet ds = new DataSet();
            IDbDataAdapter da = new System.Data.OracleClient.OracleDataAdapter(cmd as System.Data.OracleClient.OracleCommand);
            da.Fill(ds);
            trans.Commit();
            conn.Close();
            return ds;

3、三层架构中调用方法

  web.aspx.cs

            string strMenu = "";
            string strMenuName = "";
            string strTotal = "";
            DataSet ds = m.ProcedureMeal(FLIGHT_ID);
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                DataRow dr = ds.Tables[0].Rows[i];
                strMenuName = Convert.ToString(dr["food_name"]);
                strTotal = Convert.ToString(dr["count_meal"]);
                strMenu += strMenuName+":"+ strTotal + "份；";
            }
            return strMenu;

bll.cs

        public DataSet ProcedureMeal(string FLIGHT_ID) //每个航班订餐信息
        {
            return dal.ProcedureMeal2(FLIGHT_ID);
        }

dal.cs(重点部分)

        public DataSet ProcedureMeal2(string FLIGHT_ID)
        {
            OracleParameter[] parameters = {
		    new OracleParameter("flightId",OracleType.VarChar,20),
                    new OracleParameter("cur_Meal",OracleType.Cursor)};
            parameters[0].Direction = System.Data.ParameterDirection.Input;
            parameters[0].Value = FLIGHT_ID;
            parameters[1].Direction = System.Data.ParameterDirection.Output;
            DataSet ds = DbHelperOraFoc.RunProcedureDs("proc_find_meal", parameters);
            return ds;
           
        }

DBUtility.cs

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>DataSet</returns>
        public static DataSet RunProcedureDs(string storedProcName, IDataParameter[] parameters)
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                OracleDataAdapter sqlDA = new OracleDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet, "ds");
                connection.Close();
                return dataSet;
            }
        }

        /// <summary>
        /// 构建 OracleCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>OracleCommand</returns>
        private static OracleCommand BuildQueryCommand(OracleConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            OracleCommand command = new OracleCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (OracleParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    // 检查未分配值的输出参数,将其分配以DBNull.Value.
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }

            return command;
        }