先看看存储过程的定义:
create or replace procedure GetSettData
(saleno_in in varchar, windowno_in in varchar,batchno_in in varchar,
incount_out out number,inamt_out out number, incardnum_out out number,
outcount_out out number,outamt_out out number, outcardnum_out out number
)
is
……
end GetSettData;
存储过程应该是没问题的,我自己在PL/SQL上测试过下面看C#的代码:con = DB.instance.GetConnection();
OracleCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GetSettData";OracleParameter[] parameters ={
new OracleParameter("saleno_in",OracleType.VarChar),
new OracleParameter("windowno_in",OracleType.VarChar),
new OracleParameter("batchno_in",OracleType.VarChar),
new OracleParameter("incount",OracleType.Number),
new OracleParameter("inamt",OracleType.Number),
new OracleParameter("incardnum",OracleType.Number),
new OracleParameter("outcount",OracleType.Number),
new OracleParameter("outamt",OracleType.Number),
new OracleParameter("outcardnum",OracleType.Number)
};
parameters[0].Value = "ABC";
parameters[1].Value = "CDE";
parameters[2].Value = "FGH";
parameters[0].Direction = ParameterDirection.Input;
parameters[1].Direction = ParameterDirection.Input;
parameters[2].Direction = ParameterDirection.Input;
parameters[3].Direction = ParameterDirection.Output;
parameters[4].Direction = ParameterDirection.Output;
parameters[5].Direction = ParameterDirection.Output;
parameters[6].Direction = ParameterDirection.Output;
parameters[7].Direction = ParameterDirection.Output;
parameters[8].Direction = ParameterDirection.Output;for (int i = 0; i < parameters.Length; i++)
cmd.Parameters.Add(parameters[i]);cmd.ExecuteNonQuery();
在cmd.ExecuteNonQuery();这句是报错:
ORA-06550: 第 1 行, 第 7 列:
PLS-00306: 调用 'GETSETTDATA' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列: 我是第一次用存储过程,大家帮我看看是咋回事,谢谢了
create or replace procedure GetSettData
(saleno_in in varchar, windowno_in in varchar,batchno_in in varchar,
incount_out out number,inamt_out out number, incardnum_out out number,
outcount_out out number,outamt_out out number, outcardnum_out out number
)
is
……
end GetSettData;
存储过程应该是没问题的,我自己在PL/SQL上测试过下面看C#的代码:con = DB.instance.GetConnection();
OracleCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GetSettData";OracleParameter[] parameters ={
new OracleParameter("saleno_in",OracleType.VarChar),
new OracleParameter("windowno_in",OracleType.VarChar),
new OracleParameter("batchno_in",OracleType.VarChar),
new OracleParameter("incount",OracleType.Number),
new OracleParameter("inamt",OracleType.Number),
new OracleParameter("incardnum",OracleType.Number),
new OracleParameter("outcount",OracleType.Number),
new OracleParameter("outamt",OracleType.Number),
new OracleParameter("outcardnum",OracleType.Number)
};
parameters[0].Value = "ABC";
parameters[1].Value = "CDE";
parameters[2].Value = "FGH";
parameters[0].Direction = ParameterDirection.Input;
parameters[1].Direction = ParameterDirection.Input;
parameters[2].Direction = ParameterDirection.Input;
parameters[3].Direction = ParameterDirection.Output;
parameters[4].Direction = ParameterDirection.Output;
parameters[5].Direction = ParameterDirection.Output;
parameters[6].Direction = ParameterDirection.Output;
parameters[7].Direction = ParameterDirection.Output;
parameters[8].Direction = ParameterDirection.Output;for (int i = 0; i < parameters.Length; i++)
cmd.Parameters.Add(parameters[i]);cmd.ExecuteNonQuery();
在cmd.ExecuteNonQuery();这句是报错:
ORA-06550: 第 1 行, 第 7 列:
PLS-00306: 调用 'GETSETTDATA' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列: 我是第一次用存储过程,大家帮我看看是咋回事,谢谢了
检查类型和参数个数
http://msdn.microsoft.com/zh-cn/library/system.data.oracleclient.oracletype.aspx
System.Data.SqlClient.SqlConnection sqlSvrDbCnt = new System.Data.SqlClient.SqlConnection(); //数据库连接对象
System.Data.SqlClient.SqlDataAdapter sqlSvrDbAdp = null; //数据适配器对象 System.Data.SqlClient.SqlCommand sqlSvrDbCmd = null; //数据命令处理对象
System.Data.SqlClient.SqlDataReader sqlSvrDbReader = null; //只进读取器对象 sqlSvrDbCnt.ConnectionString = "Data Source=LANDSEA-8CC1455\\SQLEXPRESS;Initial Catalog=master;Integrated Security=True";
string sProcName = "p_test";
sqlSvrDbCmd = new System.Data.SqlClient.SqlCommand(); //建立数据命令对象的
sqlSvrDbCnt.Open(); //打开数据库连接
string sErrMessage = ""; try
{
sqlSvrDbCmd = new System.Data.SqlClient.SqlCommand(sProcName, sqlSvrDbCnt);//实例化命令对象 sqlSvrDbCmd.CommandType = CommandType.StoredProcedure; //赋参数值
/*
sampParm = sampleCMD.Parameters.Add("@InputParm", SqlDbType.NVarChar, 12);
sampParm.Value = "Sample Value"; sampParm = sampleCMD.Parameters.Add("@OutputParm", SqlDbType.NVarChar, 28);
sampParm.Direction = ParameterDirection.Output;
*/ System.Data.SqlClient.SqlParameter sampParm = null;
sampParm = sqlSvrDbCmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int);
sampParm.Direction = ParameterDirection.ReturnValue; sqlSvrDbCmd.Parameters.Add("@REPORTYEAR", SqlDbType.Int).Value = year;
sqlSvrDbCmd.Parameters.Add("@REPORTMONTH", SqlDbType.Int).Value = month;
sqlSvrDbCmd.Parameters.Add("@SHIP_ID", SqlDbType.Char).Value = shipid;
sampParm = sqlSvrDbCmd.Parameters.Add("@rt", SqlDbType.VarChar,100);
sampParm.Direction = ParameterDirection.Output; sqlSvrDbReader = sqlSvrDbCmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(sqlSvrDbReader); string rt="";
rt = sqlSvrDbCmd.Parameters["@rt"].Value.ToString();
rt = sqlSvrDbCmd.Parameters["RETURN_VALUE"].Value.ToString();
return dt;
}
catch (System.Data.SqlClient.SqlException e)
{
//给错误信息赋值 sErrMessage = getDbError(e);
return null;
}
catch (Exception e)
{
//给错误信息赋值 sErrMessage = e.Message;
return null;
}
finally
{
//释放资源
if (sqlSvrDbCmd != null)
{
sqlSvrDbCmd.Dispose();
}
if (sqlSvrDbCnt.State == ConnectionState.Open)
{
sqlSvrDbCnt.Close();
}
}if object_id('p_test') is not null drop proc p_test
go
create proc p_test @REPORTYEAR int,@REPORTMONTH int,@SHIP_ID char(36),@rt varchar(100) output
as
begin
set @rt='ok'
if @REPORTYEAR=0 or @REPORTYEAR is null
set @REPORTYEAR=year(getdate())
if @REPORTMONTH=0 or @REPORTMONTH is null
set @REPORTMONTH=month(getdate())
if @SHIP_ID='' or @SHIP_ID is null
begin
set @rt='error'
return -1
end
select a.SMR_47_ID,a.ship_id,a.REPORTYEAR,a.REPORTMONTH,a.REPORTDATE,a.SEQUNCE,a.PROJECTNAME,a.REPAIRCONTENT,a.REPAIRDATE,a.COMPLETEDATE,
a.COMPLETEDBY,a.REMARK,a.CO,a.MASTER,a.EMANAGER, a.VIEWDATE
from T_CT_SMR_47 a
where REPORTYEAR=@REPORTYEAR
and REPORTMONTH=@REPORTMONTH
and upper(SHIP_ID) = @SHIP_ID order by SEQUNCE
endprivate string getDbError(System.Data.SqlClient.SqlException sqlException)
{
string errText = "";
string dbErrText = sqlException.Message.ToLower();
switch (sqlException.Number)
{
case -3:
errText = "本次修改内容,被其他用户修改过";
break;
case 2601://唯一索引
errText = "值必须唯一";
break;
case 547://外键约束
//DELETE 语句与 REFERENCE 约束"FK_T_MATOUT_SHIP"冲突。该冲突发生于数据库"Wuhai4500KK",表"dbo.T_MATERIEL_OUT", column 'SHIP_ID'。 //step 1 find foreign table name
//step 2 instence a table object of foreign table
//step 3 find foreign column name
//step 4 concentrate into one sentence
//string fkeyTablename;
//OurTable theFkeyTable;
////string fkeycoulumnname;
//fkeyTablename = dbErrText.Substring(dbErrText.IndexOf("dbo.t_"), dbErrText.IndexOf("\"", dbErrText.IndexOf("dbo.t_")) - dbErrText.IndexOf("dbo.t_"));
//fkeyTablename = fkeyTablename.Substring(4);
//theFkeyTable = OurTableServices.GetInstance.LoadATableByName(fkeyTablename);
//if (!theFkeyTable.IsWrong)
//{
// fkeyTablename = theFkeyTable.TableChineseName + "[" + fkeyTablename + "]";
//}
//errText = "由于外键约束导致无法进行当前的数据操作,更多描述见以下内容:\n\n"
// + "[" + fkeyTablename + "]中使用了当前操作数据的信息,进行本次操作前必须先删除其引用数据"; break;
case 2627://主键约束
errText = "主键约束";
break;
case 515://字段不可为空
//string tablename;
//OurTable theTable;
//string coulumnname;
//tablename = dbErrText.Substring(dbErrText.IndexOf("dbo.t_"), dbErrText.IndexOf("'", dbErrText.IndexOf("dbo.t_")) - dbErrText.IndexOf("dbo.t_"));
//tablename = tablename.Substring(4);
//theTable = OurTableServices.GetInstance.LoadATableByName(tablename);
//if (!theTable.IsWrong)
//{
// tablename = theTable.TableChineseName + "[" + tablename + "]";
//}
//coulumnname = dbErrText.Substring(dbErrText.IndexOf("'"), dbErrText.IndexOf("'", dbErrText.IndexOf("'") + 1) - dbErrText.IndexOf("'"));
//errText = tablename + "的字段" + coulumnname + "不可为空";
//sqlException.Message不能将值 NULL 插入列 'UNIT_NAME',表 'LSShipMis_Ship.dbo.T_UNIT';列不允许有空值。UPDATE 失败
break;
case 242://日期出错误 errText = "日期必须大于1753年1月1日";
break;
case 7391://启动分布式事务 errText = "启动分布式事务";
break;
case 208://检索的表不存在
errText = "检索的表不存在!";
break;
case 241://日期数据不正确 errText = "日期数据不正确!";
break;
case 999://有打印脚本存在 errText = "有打印脚本存在";
break;
case 1205:// DeadLock Victim
errText = "数据库发生死锁,如果是偶然发生此问题,请关闭页面或关闭程序后,重新执行当前操作,如果是经常发生此错误,请将发生此错误的具体情况形成文档并适当截图,发送给管理员!";
break;
case 8114:
errText = "数据类型转化错误,如某字段为必填项,但用户未进行填写而保持其默认空值时,经常会发生此问题!";
break;
case 8152:
errText = "当前提交的数据中某列长度超出原要求!";
break;
default:
errText = "发生未知的数据库执行脚本错误,可能是必填数据未进行填写造成,\n更多错误信息请关注以下内容:"
+ sqlException.Message;
break;
}
return errText;
}