存储过程:
create or replace procedure pr_uploadlearninglog
(snstr in varchar2,
machineid in number,
modulesnamestr in varchar2,
startstudytime in date,
endstudytime in date)
as
studyid number;
modulesid number;
learningid number;
begin
select a.id into modulesid from m_model a where a.modelname=modulesnamestr;
select M_STUDY_SEQUENCES.nextval into studyid from dual;
select count(d.id) into learningid from M_LEARNINGLOG d where modulesid = d.modulesid and d.modelid = machineid
and d.sn = snstr;
if learningid <=0
then
insert into M_LEARNINGLOG(id,SN,MODELID,MODULESID,ADDTIME) values(studyid,snstr,machineid,modulesid,sysdate);
end if;
insert into M_LEARNINGDETAIL(Id,STARTTIME,ENDTIME) values(studyid,startstudytime,endstudytime);
commit;
exception
when others then
rollback;
end pr_uploadlearninglog;
调用存储过程: public static int Upload(string[] args)
{ OracleParameter snParameter = new OracleParameter("snstr", OracleType.VarChar, 64); snParameter.Value = args[0]; OracleParameter typeidParameter = new OracleParameter("typeidint", OracleType.Number); typeidParameter.Value = Convert.ToInt32(args[1]); OracleParameter modelnameParameter = new OracleParameter("modelnamestr", OracleType.VarChar, 64); modelnameParameter.Value = args[2];
OracleParameter modelStartTimeParameter = new OracleParameter("modelstarttime", OracleType.DateTime); modelStartTimeParameter.Value = Convert.ToDateTime(args[3]); OracleParameter modelEndTimeParameter = new OracleParameter("modelendtime", OracleType.DateTime); modelEndTimeParameter.Value = Convert.ToDateTime(args[4]); OracleParameter[] parameter = { snParameter, typeidParameter, modelnameParameter, modelStartTimeParameter ,modelEndTimeParameter}; int result = SDProvider.RunProcedure("pr_uploadlearninglog", parameter); return result;
}
public static int RunProcedure(string storedProcName, IDataParameter[] parameters)
{
int result = 0; try
{
using (OracleConnection connection = new OracleConnection(GetConnectionString()))
{
connection.Open(); OracleCommand command = BuildQueryCommand(connection, storedProcName, parameters); result = command.ExecuteNonQuery();
}
}
catch (Exception e)
{
} return result;
}
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;
}
报错:
ORA-06550: 第 1 行, 第 7 列:
PLS-00306: 调用 'PR_UPLOADLEARNINGLOG' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored郁闷的不行了,老是报错我都不知道哪儿有问题。
create or replace procedure pr_uploadlearninglog
(snstr in varchar2,
machineid in number,
modulesnamestr in varchar2,
startstudytime in date,
endstudytime in date)
as
studyid number;
modulesid number;
learningid number;
begin
select a.id into modulesid from m_model a where a.modelname=modulesnamestr;
select M_STUDY_SEQUENCES.nextval into studyid from dual;
select count(d.id) into learningid from M_LEARNINGLOG d where modulesid = d.modulesid and d.modelid = machineid
and d.sn = snstr;
if learningid <=0
then
insert into M_LEARNINGLOG(id,SN,MODELID,MODULESID,ADDTIME) values(studyid,snstr,machineid,modulesid,sysdate);
end if;
insert into M_LEARNINGDETAIL(Id,STARTTIME,ENDTIME) values(studyid,startstudytime,endstudytime);
commit;
exception
when others then
rollback;
end pr_uploadlearninglog;
调用存储过程: public static int Upload(string[] args)
{ OracleParameter snParameter = new OracleParameter("snstr", OracleType.VarChar, 64); snParameter.Value = args[0]; OracleParameter typeidParameter = new OracleParameter("typeidint", OracleType.Number); typeidParameter.Value = Convert.ToInt32(args[1]); OracleParameter modelnameParameter = new OracleParameter("modelnamestr", OracleType.VarChar, 64); modelnameParameter.Value = args[2];
OracleParameter modelStartTimeParameter = new OracleParameter("modelstarttime", OracleType.DateTime); modelStartTimeParameter.Value = Convert.ToDateTime(args[3]); OracleParameter modelEndTimeParameter = new OracleParameter("modelendtime", OracleType.DateTime); modelEndTimeParameter.Value = Convert.ToDateTime(args[4]); OracleParameter[] parameter = { snParameter, typeidParameter, modelnameParameter, modelStartTimeParameter ,modelEndTimeParameter}; int result = SDProvider.RunProcedure("pr_uploadlearninglog", parameter); return result;
}
public static int RunProcedure(string storedProcName, IDataParameter[] parameters)
{
int result = 0; try
{
using (OracleConnection connection = new OracleConnection(GetConnectionString()))
{
connection.Open(); OracleCommand command = BuildQueryCommand(connection, storedProcName, parameters); result = command.ExecuteNonQuery();
}
}
catch (Exception e)
{
} return result;
}
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;
}
报错:
ORA-06550: 第 1 行, 第 7 列:
PLS-00306: 调用 'PR_UPLOADLEARNINGLOG' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored郁闷的不行了,老是报错我都不知道哪儿有问题。
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货