存储过程代码为:
create or replace procedure proc_test(pCursor OUT pak_pub.ut_cursor) AS
begin
-- 使用游标
open pCursor for select * from temp;
end ;
其中pak_pub.ut_cursor的定义为:
/*创建一个package存放定义的游标*/
create or replace package pak_pub
as
type ut_cursor is ref cursor;
end;
C#执行oracle存储过程:
//存储过程参数设置
OracleParameter[] par =
{
new OracleParameter("pCursor", OracleType.Cursor)
};
par[].Direction = ParameterDirection.Output;
DataTable re = new DBHelper(connection).GetDataTable("proc_test",CommandType.StoredProcedure, par);
DBHelper类:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Data.OracleClient; namespace Jory.Core.Utility
{
public class DBHelper
{
#region 数据库连接字符串 /// <summary>
/// 数据库连接字符串
/// </summary>
private string conStr { get; set; }
public DBHelper()
{
conStr = "";
} public DBHelper(PbConnection connection)
{
conStr = connection.ConnectionString;
}
#endregion #region 数据的增删改
/// <summary>
/// 数据的增删改
/// </summary>
/// <param name="sql"></param>
/// <param name="opar"></param>
/// <returns></returns>
public int ExecuteNonQuery(string sql, CommandType commandType, params OracleParameter[] opar)
{
using (OracleConnection con = new OracleConnection(conStr))
{
using (OracleCommand cmd = new OracleCommand(sql, con))
{
con.Open();
// 设置命令文本(存储过程名或SQL语句)
cmd.CommandType = commandType;
cmd.Parameters.AddRange(opar);
return cmd.ExecuteNonQuery();
}
}
}
#endregion #region 数据查询结果集为一行一列
/// <summary>
/// 数据查询结果集为一行一列
/// </summary>
/// <param name="sql"></param>
/// <param name="opar"></param>
/// <returns></returns>
public object ExecuteScalar(string sql, CommandType commandType, params OracleParameter[] opar)
{
using (OracleConnection con = new OracleConnection(conStr))
{
using (OracleCommand cmd = new OracleCommand(sql, con))
{
cmd.Parameters.AddRange(opar);
// 设置命令文本(存储过程名或SQL语句)
cmd.CommandType = commandType;
con.Open();
return cmd.ExecuteScalar();
}
}
}
#endregion #region 返回查询结果集Table
/// <summary>
/// 返回查询结果集Table
/// </summary>
/// <param name="sql"></param>
/// <param name="opar"></param>
/// <returns></returns>
public DataTable GetDataTable(string sql, CommandType commandType, params OracleParameter[] opar)
{
DataTable dt = new DataTable();
using (OracleDataAdapter dap = new OracleDataAdapter(sql, conStr))
{
dap.SelectCommand.Parameters.AddRange(opar);
dap.SelectCommand.CommandType = commandType;
dap.Fill(dt);
}
return dt;
}
#endregion #region 返回查询结果集DataReader
/// <summary>
/// 返回查询结果集DataReader
/// </summary>
/// <param name="sql"></param>
/// <param name="opar"></param>
/// <returns></returns>
public OracleDataReader DataReader(string sql, CommandType commandType, params OracleParameter[] opar)
{
OracleConnection con = new OracleConnection();
using (OracleCommand cmd = new OracleCommand(sql, con))
{
cmd.CommandType = commandType;
cmd.Parameters.AddRange(opar);
try
{
con.Open();
// 执行完关闭对象 con
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch
{
con.Close();
con.Dispose();
throw;
}
}
}
#endregion
}
}
PbConnection类：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text; namespace Jory.FrameWork.Common
{
public class PbConnection
{
public string ConnectionString
{
get;
set;
} public PbConnection()
{
}
public PbConnection(string connectionString)
{
ConnectionString = connectionString;
}
}
}