Oracle存储过程返回结果集

create or replace procedure p_Test_getTableOfBookInfo

(result out sys_refcursor)  --游标作为返回参

as

begin

open result for select * from book;

end;

 

下面是程序调用存储过程的代码

 

 调用的方法参数可以自拟，并不需要按这样方式写(参数的存储过程名和外参名都可以写在方法体内)

/// <summary>
        /// 通过游标输出参的存储过程获取数据集
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="param">外参名</param>
        /// <returns></returns>
        public static DataTable GetTableByProc(string procName,string param)
        {
            DataTable dt = new DataTable ();
            OracleConnection conn = GetConnection();  //这个方法就不贴了，创建个Connnection
            try
            {
                if (conn.State == ConnectionState.Closed)//如果连接关闭，将其开启
                {
                    conn.Open();
                }
                OracleCommand cmd = new OracleCommand(procName,conn);
                cmd.CommandType = CommandType.StoredProcedure;

                OracleParameter outparam = new OracleParameter(param,OracleType.Cursor);
                outparam.Direction = ParameterDirection.Output;//输出参数
                cmd.Parameters.Add(outparam);

                OracleDataAdapter adapt = new OracleDataAdapter(cmd);//适配器                
                adapt.Fill(dt);                
                adapt.Dispose();//填充完后，释放所有资源
                 
            }

            catch (Exception e)
            {
                if (conn.State == ConnectionState.Open)//将连接池关闭
                {
                    conn.Close();
                }

                MessageBox.Show(e.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Stop);//错误是提示框蓝顶上的信息---Show Error
            }

            finally
            {
                if (conn.State == ConnectionState.Open)
                { 
                    conn.Close();
                } 
                conn.Dispose();//释放掉资源
            }
            return dt;
        }

 

 

EX2：

 也可以传参的

 

create or replace procedure p_Test_GetAttrubuteTab(p_Fno varchar2,result out sys_refcursor)
as
begin
     open result for  select distinct t.g3e_field as FieldId,
                                 t.g3e_username as FieldName,
                      t.g3e_cno,t2.g3e_table as TableId,t2.g3e_username as TypeName
                            from g3e_attribute t, g3e_component t2
                           where t.g3e_cno = t2.g3e_cno
                             and t.g3e_cno in
                                 (select t.g3e_cno
                                    from g3e_component        t,
                                         g3e_featurecomponent t2
                                   where t.g3e_cno = t2.g3e_cno
                                     and t2.g3e_fno in (p_Fno))
                             and t2.g3e_table like '%_N'
                            order by FieldId;
end p_Test_GetAttrubuteTab;

 

 

调用方法

  dGrid.DataSource=DBCommon.GetOutParamsValue("p_Test_GetAttrubuteTab", "result", "p_Fno");

 

 

 代码：如上，参数不一定要求这样写

 

/// <summary>
        /// 通过游标输出参的存储过程获取数据
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="param">输出参名</param>
        /// <param name="inParam">输入参名</param>
        /// <returns></returns>
        public static DataTable GetOutParamsValue(string procName,string param,string inParam)
        {
            DataTable dt = new DataTable();
            OracleConnection conn = GetConnection();
            try
            {
                if (conn.State == ConnectionState.Closed)//如果连接关闭，将其开启
                {
                    conn.Open();
                }
                OracleCommand cmd = new OracleCommand(procName, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                OracleParameter outparam = new OracleParameter(param, OracleType.Cursor);
                outparam.Direction = ParameterDirection.Output;//输出参数

                OracleParameter inparam = new OracleParameter(inParam, OracleType.VarChar);
                inparam.Value = "298";
                inparam.Direction = ParameterDirection.Input;

                cmd.Parameters.Add(outparam);
                cmd.Parameters.Add(inparam);

                OracleDataAdapter adapt = new OracleDataAdapter(cmd);//适配器                
                adapt.Fill(dt);
                adapt.Dispose();//填充完后，释放所有资源

            }

            catch (Exception e)
            {
                if (conn.State == ConnectionState.Open)//将连接池关闭
                {
                    conn.Close();
                }

                MessageBox.Show(e.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Stop);//错误是提示框蓝顶上的信息---Show Error
            }

            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();//释放掉资源
            }
            return dt;
        }