数据库是oracle,目的是返回一个数据集.CREATE OR REPLACE PACKAGE PKG_JCCTEST1
AS
type rc_class is ref cursor;
procedure GetTableTt(p1 number,p2 number ,
ResultCursor out rc_class);
end PKG_JCCTEST1; CREATE OR REPLACE PACKAGE BODY PKG_JCCTEST1
AS
procedure GetTableTt(p1 number,p2 number ,
ResultCursor out rc_class)
IS
BEGIN
open ResultCursor for
select p1+p2 as "sum", p1-p2 as "sub" from T_TYPE;
END ;
这段脚本报错,应该怎么搞。
假如我要用C#调用,获取返回这个数据集,怎么实现?
AS
type rc_class is ref cursor;
procedure GetTableTt(p1 number,p2 number ,
ResultCursor out rc_class);
end PKG_JCCTEST1; CREATE OR REPLACE PACKAGE BODY PKG_JCCTEST1
AS
procedure GetTableTt(p1 number,p2 number ,
ResultCursor out rc_class)
IS
BEGIN
open ResultCursor for
select p1+p2 as "sum", p1-p2 as "sub" from T_TYPE;
END ;
这段脚本报错,应该怎么搞。
假如我要用C#调用,获取返回这个数据集,怎么实现?
解决方案 »
- C#导入dll时用DllImport怎么操作
- 我是个学生 对一个C#问题需要解决 谢谢帮助
- c#打开窗口问题,高手来答答
- 如何把字符型转变成浮点型
- 求救!!!怎么样防止文本输入参数攻击、SQL攻击??
- 在C#中用什么语句可以筛选出access处在两个日期之间的记录 并统计他们的数目 如 我要选择日期为08-01-01到08-03-05这个时间段之间的记录 并统计他们的数目 ?
- 关于多线程问题
- 怎样取得系统日期格式?
- C# .Net ACCESS
- 开发C#的环境
- 类型“System.Web.UI.WebControls.Repeater”不具有名为“template”的公共属性
- 如何控制小数位数
{
OracleCommand command = new OracleCommand("PKG_JCCTEST1");
command.CommandType = CommandType.StoredProcedure;
command.Connection = connection;
try
{
connection.Open();
OracleDataReader read =command.ExecuteReader();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
namespace CommLB.wmj.AllDataProdivers.Command
{
/// <summary>
/// OracleDataProvider 的摘要说明
/// </summary>
internal class OracleDataProvider : CommLB.wmj.AllDataProdivers.CommandOfInterface.IDataProvider
{
private System.Data.OracleClient.OracleConnection oracleConnection;
private System.Data.OracleClient.OracleCommand oracleCommand;
private string connectionString;
public OracleDataProvider()
: this(null)
{
//
// TODO: 在此处添加构造函数逻辑
//
} public OracleDataProvider(string connectionString)
{
if (connectionString == null || connectionString.Trim() == string.Empty)
{
System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader();
this.connectionString = (string)(configurationAppSettings.GetValue("oracleConnectionString", typeof(string)));
}
else
{
this.connectionString = connectionString;
}
} /// <summary>
/// 执行Sql数组语句查询,并将查询返回的结果作为一个数据集返回
/// </summary>
/// <param name="sql">Select 语句数组</param>
/// <param name="tableName">TableName</param>
/// <returns>数据集 DataSet</returns>
public DataSet RetriveDataSet(string[] sqls, params string[] tableNames)
{
if (sqls == null || sqls.Length == 0)
{
throw new Exception("抱歉,SQL 语句为空...");
}
int sqlLength;
sqlLength = sqls.Length;
using (oracleConnection = this.GetOracleConnection())
{
if (oracleConnection == null)
return null;
DataSet ds = new DataSet();
int tableNameLength = tableNames.Length;
for (int i = 0; i < sqlLength; i++)
{
using (OracleDataAdapter da = new OracleDataAdapter(sqls[i], oracleConnection))
{
try
{
if (i < tableNameLength)
da.Fill(ds, tableNames[i]);
else
da.Fill(ds, "table" + i);
}
catch (Exception ex)
{
throw ex;
}
}
}
return ds;
}
} public DataSet RetriveDataSet(string[,] sql_tableNames)
{
if (sql_tableNames.GetLength(0) != 2)
{
throw new Exception("抱歉,SQL语句-表名 参数必须为二维数组...");
}
int length = sql_tableNames.GetLength(1);
if (length <= 0)
{
throw new Exception("抱歉,SQL语句-表名 为空...");
} string[] sqls = new string[length];
string[] tableNames = new string[length];
for (int i = 0; i < length; i++)
{
sqls[i] = sql_tableNames[i, 0];
tableNames[i] = sql_tableNames[i, 1];
} return RetriveDataSet(sqls, tableNames);
} public bool ExecuteTransaction(string[] sqls, object[][] myParams)
{
throw new Exception("抱歉,未实现...");
} /// <summary>
/// 执行Sql数组语句查询,并将查询返回的结果作为一个数据读取器返回
/// </summary>
/// <param name="sql"></param>
/// <returns>OracleDataReader</returns>
public OracleDataReader RetriveDataReader(string sql)
{
if (sql == null || sql == string.Empty)
{
throw new Exception("抱歉,SQL 语句为空...");
}
using (oracleConnection = this.GetOracleConnection())
{
if (oracleConnection == null)
return null;
using (oracleCommand = new OracleCommand(sql, oracleConnection))
{
try
{
OracleDataReader oracleDataReader = oracleCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
return oracleDataReader;
}
catch (Exception ex)
{
throw ex;
}
}
}
} /// <summary>
/// 执行一个查询式的存贮过程,返回得到的数据集
/// </summary>
/// <param name="proceName">存贮过程名称</param>
/// <param name="myParams">所有属性值</param>
/// <returns></returns>
public DataSet ExecStoredProcedure(string proceName, object[] myParams)
{
if (proceName == null || proceName == string.Empty)
{
throw new Exception("抱歉,存贮过程名称为空...");
} using (oracleConnection = this.GetOracleConnection())
{
if (oracleConnection == null)
return null; DataSet ds = new DataSet(); try
{
if (oracleConnection.State == System.Data.ConnectionState.Closed)
oracleConnection.Open();
oracleCommand = oracleConnection.CreateCommand();
oracleCommand.CommandType = CommandType.StoredProcedure;
oracleCommand.CommandText = proceName;
if (myParams != null)
{
for (int i = 0; i < myParams.Length; i++)
oracleCommand.Parameters.Add((OracleParameter)myParams[i]);
}
using (OracleDataAdapter da = new OracleDataAdapter(oracleCommand))
{
int returnValue = da.Fill(ds);
if (returnValue < 0)
throw new Exception("存储过程执行错误:" + (returnValue >= -14 ?
((StoreProcReturn)returnValue).ToString() : "ErrCode:" + returnValue));
return ds;
}
}
catch (Exception ex)
{
throw ex;
}
}
} /// <summary>
/// 执行一个非查询式的存贮过程
/// </summary>
/// <param name="proceName">存贮过程名称</param>
/// <param name="myParams">所有属性值</param>
/// <returns>存储过程return值</returns>
public int ExecNonQueryStoredProcedure(string proceName, ref object[] myParams)
{
if (proceName == null || proceName == string.Empty)
{
throw new Exception("抱歉,存贮过程名称为空...");
} using (oracleConnection = this.GetOracleConnection())
{
if (oracleConnection == null)
throw new Exception("抱歉,数据库连接没有初始化..."); try
{
if (oracleConnection.State == System.Data.ConnectionState.Closed)
oracleConnection.Open();
oracleCommand = oracleConnection.CreateCommand();
oracleCommand.CommandType = CommandType.StoredProcedure;
oracleCommand.CommandText = proceName;
if (myParams != null)
{
for (int i = 0; i < myParams.Length; i++)
oracleCommand.Parameters.Add((OracleParameter)myParams[i]);
}
int returnValue = oracleCommand.ExecuteNonQuery();
if (returnValue < 0)
throw new Exception("存储过程执行错误:" + (returnValue >= -14 ?
((StoreProcReturn)returnValue).ToString() : "ErrCode:" + returnValue));
return returnValue;
}
catch (Exception ex)
{
throw ex;
}
}
} public void Dispose()
{
this.connectionString = null;
if (this.oracleCommand != null)
this.oracleCommand.Dispose();
if (this.oracleConnection != null)
this.oracleConnection.Dispose();
}
}
}