123 c#调用oracle存储过程返回数据集 --通过oracle存储过程返回数据集

c#调用oracle存储过程返回数据集

2008-12-20 10:59:57|  分类: net|字号 订阅

 

CREATE OR REPLACE PACKAGE pkg_tableType
IS
     type Tabletype is ref cursor;
     PROCEDURE SP_CPZD
      (
      CPNO IN VARCHAR2,
      STATUS IN VARCHAR2,
      t_sql out Tabletype
      );
END;
CREATE OR REPLACE package BODY pkg_tableType
is
PROCEDURE SP_CPZD
(
CPNO IN VARCHAR2,
STATUS IN VARCHAR2,
t_sql out Tabletype
)
IS
BEGIN
        OPEN t_sql FOR

select *  from ball b where b.no=CPNO  and  b.type =STATUS ;

END SP_CPZD;
END pkg_tableType;

#region 存储过程操作
     /// <summary>
  /// 执行存储过程
  /// </summary>
  /// <param name="storedProcName">存储过程名</param>
  /// <param name="parameters">存储过程参数</param>
  /// <returns>OracleDataReader</returns>
  public static OracleDataReader RunProcedure(string storedProcName, IDataParameter[] parameters )
  {
   OracleConnection connection = new OracleConnection(connectionString);
   OracleDataReader returnReader;
   connection.Open();
   OracleCommand command = BuildQueryCommand( connection,storedProcName, parameters );
   command.CommandType = CommandType.StoredProcedure;
   returnReader = command.ExecuteReader();    
   return returnReader;   
  }
  /// <summary>
  /// 执行存储过程
  /// </summary>
  /// <param name="storedProcName">存储过程名</param>
  /// <param name="parameters">存储过程参数</param>
  /// <param name="tableName">DataSet结果中的表名</param>
  /// <returns>DataSet</returns>
  public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName )
  {
   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, tableName );
    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)
   {
    command.Parameters.Add( parameter );
   }
   return command;   
  }
  /// <summary>
  /// 执行存储过程,返回影响的行数  
  /// </summary>
  /// <param name="storedProcName">存储过程名</param>
  /// <param name="parameters">存储过程参数</param>
  /// <param name="rowsAffected">影响的行数</param>
  /// <returns></returns>
  public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected )
  {
   using (OracleConnection connection = new OracleConnection(connectionString))
   {
    int result;
    connection.Open();
    OracleCommand command = BuildIntCommand(connection,storedProcName, parameters );
    rowsAffected = command.ExecuteNonQuery();
    result = (int)command.Parameters["ReturnValue"].Value;
    //Connection.Close();
    return result;
   }
  }
  /// <summary>
  /// 创建 OracleCommand 对象实例(用来返回一个整数值) 
  /// </summary>
  /// <param name="storedProcName">存储过程名</param>
  /// <param name="parameters">存储过程参数</param>
  /// <returns>OracleCommand 对象实例</returns>
  private static OracleCommand BuildIntCommand(OracleConnection connection,string storedProcName, IDataParameter[] parameters)
  {
   OracleCommand command = BuildQueryCommand(connection,storedProcName, parameters );
   command.Parameters.Add( new OracleParameter ( "ReturnValue",
    OracleType.Int32,4,ParameterDirection.ReturnValue,
    false,0,0,string.Empty,DataRowVersion.Default,null ));
   return command;
  }

#endregion

public DataSet GetCPResult(string s_aN_CPNO)
        {
            OracleParameter[] parameter = { 
                new OracleParameter("CPNO",OracleType.VarChar,100),
                new OracleParameter("STATUS",OracleType.VarChar,100),//注意:这里参数名称前面都不能加":"
                new OracleParameter("t_sql", OracleType.Cursor)};
            parameter[0].Value = s_aN_CPNO;
            parameter[1].Value = "3";
            parameter[2].Direction = System.Data.ParameterDirection.Output;
            return RunProcedure("pkg_tabletype.sp_cpzd", parameter, "ds");
        }

-==-------------------------------------------------=========================

随笔- 125  文章- 0  评论- 7 

--通过oracle存储过程返回数据集

==========
http://www.cnblogs.com/xiaomi7732/archive/2008/01/01/1022083.html
======
Microsoft Enterprise Library中所带的Data Access Application Block(以下简称DAAB),对ADO.NET进行了一次封装,为数据库访问带来了极大的便利,尤其是与SQL Server配合使用,可谓得心应手。但是,其访问Oracle数据库,特别是使用Oracle的存储过程时,大家时常会遇到一些问题,例如:怎么通过Oracle的存储过程,来返回一个数据集?既然它们的访问方式有所不同,怎么才能让程序既适用于SQL Server又适用于Oracle?小弟有幸在项目中遇到了此种需求,并且找到了解决方案,拿出来与大家讨论。
说到Oracle中存储过程返回数据集与SQL有何不同,就要说到包的概念,因为SQL Server中不存在相对应的概念。网上有许多相关的文章,在此不再赘述。另一个不同点,就是SQL Server的存储过程中,执行一个SELECT语句,数据集会自动返回出来,而在Oracle中,需要通过一个指针来实现数据集的返回。来看具体的例子。
首先,我们来建立一个包,包头部分如下,它相当于C++中的头文件,用作声明之用,没有任何实现用的代码。

123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集CREATE OR REPLACE PACKAGE TEST.PKG_TEST IS
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集    TYPE MYCURSOR IS REF CURSOR;
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集    PROCEDURE GET_DEC_BILL_LIST(cur_OUT OUT MYCURSOR);
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集END;

以上代码中,第一步,通过TYPE建立了一个指针类型MYCURSOR。第二步,声明了:此包中有一个存储过程GET_DEC_BILL_LIST,并且,这个存储过程中有一个cur_OUT的输出变量,其类型为MYCURSOR。
然后,我们来建立一个包体:

123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集CREATE OR REPLACE PACKAGE BODY TEST.PKG_TEST IS
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集       PROCEDURE GET_DEC_BILL_LIST(cur_OUT OUT MYCURSOR) AS
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集       BEGIN
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集            OPEN cur_OUT FOR SELECT * FROM ALL_TABLES WHERE OWNER='SYS';
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集       END;
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集END;

以上代码中,实现了GET_DEC_BILL_LIST,其实质是打开了一个指针,其内容为SYS用户下的所有表的情况。这样子,数据库部分就Ready了。
接下来,我们就可以通过DAAB来调用这个存储过程了:

123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集using Microsoft.Practices.EnterpriseLibrary.Data;
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集//123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集Other Code here
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集//123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集        private void button1_Click(object sender, EventArgs e)
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集        {
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集            Database db = DatabaseFactory.CreateDatabase("LocalOracle"); //建立数据库对象
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集            //全称:TEST用户下PKG_TEST包中的GET_DEC_BILL_LIST存储过程
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集            string spName = "TEST.PKG_TEST.GET_DEC_BILL_LIST";
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集            try
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集            {
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集                DbCommand cmd = db.GetStoredProcCommand(spName); //获取Command对象
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集                DataSet ds;
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集                ds = db.ExecuteDataSet(cmd); //执行SQL语句
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集                dataGridView1.DataSource = ds.Tables[0].DefaultView;//绑定数据并且显示
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集            }
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集            catch (Exception ex)
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集            {
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集                MessageBox.Show(ex.Message);
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集            }
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集            
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集        }

执行效果如图。
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集
继续往下看,精彩继续 ^_^
如果您照着示例做,但是运行时,收到一个错误提示,说参数类型不匹配,那么,答案在这里,这是DAAB中OracleDatabase.cs中的代码:

123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集                /// <devdoc>
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集        /// This is a private method that will build the Oracle package name if your stored procedure
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集        /// has proper prefix and postfix. 
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集        /// This functionality is include for
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集        /// the portability of the architecture between SQL and Oracle datbase.
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集        /// This method also adds the reference cursor to the command writer if not already added. This
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集        /// is required for Oracle .NET managed data provider.
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集        /// </devdoc>        
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集        private void PrepareCWRefCursor(DbCommand command)
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集        {
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集            if (command == null) throw new ArgumentNullException("command");
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集            if (CommandType.StoredProcedure == command.CommandType)
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集            {
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集                // Check for ref. cursor in the command writer, if it does not exist, add a know reference cursor out
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集                // of "cur_OUT"
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集                if (QueryProcedureNeedsCursorParameter(command))
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集                {
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集                    AddParameter(command as OracleCommand, RefCursorName, OracleType.Cursor, 0, ParameterDirection.Output, true, 0, 0, String.Empty, DataRowVersion.Default, Convert.DBNull);
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集                }
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集            }
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集        }

AddParameter的第二个参数在前面的代码中有定义,为一个字符串常量:cur_OUT。所以,请修改您的存储过程,把输出指针的名称改为“cur_OUT”。
回头再看看前面示例中的C#代码,注意spName这个变量,用惯SQL Server的人一定会觉得这个名字怪怪的。能不能把代码改得像SQL Server中一样,只传递一个存储过程名称呢?呵呵,Microsoft在设计DAAB时已经考虑到了这个问题了,为了实现代码的可移植性,需要对app.config进行一下配置。在解决方案浏览器中右击app.config文本,选择Edit Enterprise Libraray Configuration。在配置好的Oracle Packages节点上右击,点击New、Oracle Package。如下图。
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集
然后,设置新建的Oracle包属性如下图:
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集

只要存储过程前缀与设置的相同,DAAB执行存储过程时,便会自动添加前缀,于是示例代码便可以修改如下。

123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集private void button1_Click(object sender, EventArgs e)
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集        {
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集            Database db = DatabaseFactory.CreateDatabase("LocalOracle"); //建立数据库对象
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集            //string spName = "TEST.PKG_TEST.GET_DEC_BILL_LIST";//全称
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集            string spName="GET_DEC_BILL_LIST";//与SQL SERVER中一样,仅仅传入存储过程名称
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集            try
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集            {
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集                DbCommand cmd = db.GetStoredProcCommand(spName); //获取Command对象
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集                DataSet ds;
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集                ds = db.ExecuteDataSet(cmd); //执行SQL语句
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集                dataGridView1.DataSource = ds.Tables[0].DefaultView;//绑定数据并且显示
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集            }
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集            catch (Exception ex)
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集            {
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集                MessageBox.Show(ex.Message);
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集            }
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集
123 c#调用oracle存储过程返回数据集  --通过oracle存储过程返回数据集        }

总结一下,通过DAAB,我们实现了在C#中,调用ORACLE的存储过程返回一个数据集,并且,通过简单的配置,把调用方法与SQL统一了起来。
=================
oracle存储过程如何返回数据集?
=====
oracle 只能用cursor返回结果集呀! 
或者可以用基于session的临时表然后你在程序中从表中获取数据
=====
1.定义一个输出变量  
rcResult out PK_Var.RC   
2.打开这个记录集   
OPEN rcResult FOR sSQL;

CREATE OR REPLACE PACKAGE pkg_test 
AS   
  TYPE myrctype IS REF CURSOR;   
  PROCEDURE get (p_id NUMBER, p_rc OUT myrctype);   
END pkg_test;   
/   
-------------------    
CREATE OR REPLACE PACKAGE BODY pkg_test   
AS   
 PROCEDURE get(p_id NUMBER, p_rc OUT myrctype)   
  IS   
    sqlstr VARCHAR2 (500);   
BEGIN   
  IF p_id = 0 THEN   
    OPEN p_rc FOR   
     SELECT ID, NAME, sex, address, postcode, birthday FROM student;   
  ELSE sqlstr := 'selectid,name,sex,address,postcode,birthday from student where id=:w_id';   
  OPEN p_rc FOR sqlstr USING p_id;   
  END IF;   
 END get;   
END pkg_test;

=========
过程可以返回结果集合,关键是是使用引用游标   
测试表:table_test   
--建立包   
  create   or   replace   package   pkg_test   as   
          --定义一个返回类型为表table_test的记录结构的引用游标类型rc_test   
          type rc_test is ref cursor return table_test%rowtype;   
          --声明过程,参数是类型为rc_test的引用游标cur,传出参数   
          procedure get_data(cur out rc_test);   
  end   pkg_test;   
--定义包体   
create or replace package body pkg_test as   
  procedure get_data(cur out rc_test) is   
     begin   
       open cur for select * from table_test;   
  end get_data;   
end pkg_test;
=============

上一篇:html&css复习题(参考答案)


下一篇:c#调用oracle存储过程返回数据集