输出存储过程中的参数(oracle数据库)
本文关键字：oracle 数据库 参数 存储 存储过程 过程中 输出 | 更新日期: 2023-09-27 18:08:57
我在Oracle中使用存储过程。我如何在c#代码中使用SP的参数?
我使用以下代码:
OracleSP
PROCEDURE TABMPWORKREQUEST_INS(INTWORKREQUEST_ IN NUMBER, VCWORKREQUESTNUMBER_ OUT VARCHAR2,
INTREQUESTEDBY_ IN NUMBER, INTWORKTYPE_ IN NUMBER,INTACTIONTYPE_ IN NUMBER,
INTPRIORITY_ IN NUMBER, INTRECORDID_ IN NUMBER, INTPERMITREQUIRED_ IN NUMBER, VCINSPECTIONREQUIRED_ IN CHAR,
VCLASTUSERID_ IN VARCHAR2, INTCOSTCENTRECODE_ IN NUMBER, VCBUDGETHEAD_ IN VARCHAR2
, VCREFERENCEWORKORDERNUMBER_ IN VARCHAR2, VCJOBDESCRIPTION_ IN VARCHAR2,
VCSTATUS_ IN CHAR, VCHISTORYFLAG_ IN CHAR, VCREASONCODE_ IN VARCHAR2
, VCMSSNUMBER_ IN VARCHAR2, INTAUTHORIZELEVEL_ IN NUMBER, INTPRINTFLAG_ IN NUMBER,
INTINSPECTIONFLAG_ IN NUMBER, INTDAYNUMBER_ IN NUMBER, INTDURATION_ IN NUMBER, INTPROGRESS_ IN NUMBER,
INTWORKSHOPFLAG_ IN CHAR)
IS
EXISTANCE number;
UID_ VARCHAR(5);
SS varchar2(20);
BEGIN
SELECT COUNT(*) into EXISTANCE FROM TABMPWORKREQUEST WHERE INTWORKREQUEST = INTWORKREQUEST_ ;
IF VCLASTUSERID_ = '0' THEN
UID_ := 'U2';
ELSE
UID_ := VCLASTUSERID_;
END IF;
select GetWorkOrderNumber(INTREQUESTEDBY_) INTO SS from dual ;
VCWORKREQUESTNUMBER_ :=SS;
INSERT INTO TABMPWORKREQUEST
VALUES( CMMS.SEQTABMPWORKREQUEST.NEXTVAL , SS , sysdate , INTrequestedby_ ,
INTworktype_ , INTactiontype_ , INTpriority_ , Intrecordid_ , Intpermitrequired_ , Vcinspectionrequired_ ,
Vclastuserid_ , SYSDATE , sysdate , INTcostcentrecode_ , Vcbudgethead_ , sysdate ,
Vcreferenceworkordernumber_ , Vcjobdescription_ , '01' , Vchistoryflag_ , Vcreasoncode_ ,
sysdate , sysdate , Vcmssnumber_ , Intauthorizelevel_ , Intprintflag_ , Intinspectionflag_ ,
Intdaynumber_ , Intduration_ , Intprogress_ , Intworkshopflag_ );
END TABMPWORKREQUEST_INS;
和c#代码
public bool InsertMpWORKREQUEST(ClsFieldsMpWORKREQUEST fieldsMpWORKREQUEST)
{
string DTBDDATETIME;
if (fieldsMpWORKREQUEST.DTBDDATETIME != null)
{
DTBDDATETIME = String.Format("{0:dd/MM/yyyy HH:mm:ss}", fieldsMpWORKREQUEST.DTBDDATETIME);
DTBDDATETIME = "TO_DATE('" + DTBDDATETIME + "', 'MM/DD/YYYY HH24:MI:SS')";
}
else
{
DTBDDATETIME = "NULL";
}
string DateExpire = string.Empty;
if (fieldsMpWORKREQUEST.DTEXPIRYDATE != null)
{
DateExpire = String.Format("{0:dd/MM/yyyy HH:mm:ss}", fieldsMpWORKREQUEST.DTEXPIRYDATE);
DateExpire = "TO_DATE('" + DateExpire + "', 'MM/DD/YYYY HH24:MI:SS')";
}
else
{
DateExpire = "NULL";
}
string DtStartDate;
if (fieldsMpWORKREQUEST.DTSTARTDATE != null)
{
DtStartDate = String.Format("{0:dd/MM/yyyy HH:mm:ss}", fieldsMpWORKREQUEST.DTSTARTDATE);
DtStartDate = "TO_DATE('" + DtStartDate + "', 'MM/DD/YYYY HH24:MI:SS')";
}
else
{
DtStartDate = "NULL";
}
string DtComplementationDate;
if (fieldsMpWORKREQUEST.DTCOMPLETIONDATE != null)
{
DtComplementationDate = String.Format("{0:dd/MM/yyyy HH:mm:ss}", fieldsMpWORKREQUEST.DTCOMPLETIONDATE);
DtComplementationDate = "TO_DATE('" + DtComplementationDate + "', 'MM/DD/YYYY HH24:MI:SS')";
}
else
{
DtComplementationDate = "NULL";
}
OracleConnection cn = new OracleConnection(this.GetConnectionString());
OracleCommand cmd = new OracleCommand("CMMS_MPWORKREQUEST_PKG.TABMPWORKREQUEST_INS", cn);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter INTWORKREQUEST = new OracleParameter();
INTWORKREQUEST.ParameterName = "INTWORKREQUEST_";
INTWORKREQUEST.OracleType = OracleType.Number;
INTWORKREQUEST.Direction = ParameterDirection.Input;
INTWORKREQUEST.Value = fieldsMpWORKREQUEST.INTWORKREQUEST;
cmd.Parameters.Add(INTWORKREQUEST);
// cmd.Parameters.AddWithValue("VCWORKREQUESTNUMBER_", fieldsMpWORKREQUEST.VCWORKREQUESTNUMBER).Direction=ParameterDirection.Output;
OracleParameter requestNo = new OracleParameter();
requestNo.ParameterName = "VCWORKREQUESTNUMBER_";
requestNo.OracleType = OracleType.VarChar;
requestNo.Size = 14;
requestNo.Direction = ParameterDirection.Output;
cmd.Parameters.Add(requestNo);
OracleParameter INTREQUESTEDBY = new OracleParameter();
INTREQUESTEDBY.ParameterName = "INTREQUESTEDBY_";
INTREQUESTEDBY.OracleType = OracleType.Number;
INTREQUESTEDBY.Direction = ParameterDirection.Input;
INTREQUESTEDBY.Value = fieldsMpWORKREQUEST.INTREQUESTEDBY.INTSECTIONID;
cmd.Parameters.Add(INTREQUESTEDBY);
OracleParameter INTWORKTYPE = new OracleParameter();
INTWORKTYPE.ParameterName = "INTWORKTYPE_";
INTWORKTYPE.OracleType = OracleType.Number;
INTWORKTYPE.Direction = ParameterDirection.Input;
INTWORKTYPE.Value = fieldsMpWORKREQUEST.INTREQUESTEDBY.INTSECTIONID;
cmd.Parameters.Add(INTWORKTYPE);
OracleParameter INTACTIONTYPE = new OracleParameter();
INTACTIONTYPE.ParameterName = "INTACTIONTYPE_";
INTACTIONTYPE.OracleType = OracleType.Number;
INTACTIONTYPE.Direction = ParameterDirection.Input;
INTACTIONTYPE.Value = fieldsMpWORKREQUEST.INTACTIONTYPE.INTSECTIONID;
cmd.Parameters.Add(INTACTIONTYPE);
OracleParameter INTPRIORITY = new OracleParameter();
INTPRIORITY.ParameterName = "INTPRIORITY_";
INTPRIORITY.OracleType = OracleType.Number;
INTPRIORITY.Direction = ParameterDirection.Input;
INTPRIORITY.Value = fieldsMpWORKREQUEST.INTPRIORITY.INTGENERALCODE;
cmd.Parameters.Add(INTPRIORITY);
OracleParameter INTRECORDID = new OracleParameter();
INTRECORDID.ParameterName = "INTRECORDID_";
INTRECORDID.OracleType = OracleType.Number;
INTRECORDID.Direction = ParameterDirection.Input;
INTRECORDID.Value = fieldsMpWORKREQUEST.INTRECORDID;
cmd.Parameters.Add(INTRECORDID);
OracleParameter INTPERMITREQUIRED = new OracleParameter();
INTPERMITREQUIRED.ParameterName = "INTPERMITREQUIRED_";
INTPERMITREQUIRED.OracleType = OracleType.Number;
INTPERMITREQUIRED.Direction = ParameterDirection.Input;
INTPERMITREQUIRED.Value = fieldsMpWORKREQUEST.INTPERMITREQUIRED;
cmd.Parameters.Add(INTPERMITREQUIRED);
OracleParameter VCINSPECTIONREQUIRED = new OracleParameter();
VCINSPECTIONREQUIRED.ParameterName = "VCINSPECTIONREQUIRED_";
VCINSPECTIONREQUIRED.OracleType = OracleType.Char;
VCINSPECTIONREQUIRED.Direction = ParameterDirection.Input;
VCINSPECTIONREQUIRED.Value = fieldsMpWORKREQUEST.VCINSPECTIONREQUIRED;
cmd.Parameters.Add(VCINSPECTIONREQUIRED);
OracleParameter VCLASTUSERID = new OracleParameter();
VCLASTUSERID.ParameterName = "VCLASTUSERID_";
VCLASTUSERID.OracleType = OracleType.VarChar;
VCLASTUSERID.Direction = ParameterDirection.Input;
VCLASTUSERID.Value = fieldsMpWORKREQUEST.VCLASTUSERID;
cmd.Parameters.Add(VCLASTUSERID);
OracleParameter INTCOSTCENTRECODE = new OracleParameter();
INTCOSTCENTRECODE.ParameterName = "INTCOSTCENTRECODE_";
INTCOSTCENTRECODE.OracleType = OracleType.Number;
INTCOSTCENTRECODE.Direction = ParameterDirection.Input;
INTCOSTCENTRECODE.Value = fieldsMpWORKREQUEST.INTCOSTCENTRECODE.INTGENERALCODE;
cmd.Parameters.Add(INTCOSTCENTRECODE);
OracleParameter vCBUDGETHEAD = new OracleParameter();
vCBUDGETHEAD.ParameterName = "VCBUDGETHEAD_";
vCBUDGETHEAD.OracleType = OracleType.VarChar;
vCBUDGETHEAD.Direction = ParameterDirection.Input;
vCBUDGETHEAD.Value = fieldsMpWORKREQUEST.VCBUDGETHEAD;
cmd.Parameters.Add(vCBUDGETHEAD);
OracleParameter VCREFERENCEWORKORDERNUMBER = new OracleParameter();
VCREFERENCEWORKORDERNUMBER.ParameterName = "VCREFERENCEWORKORDERNUMBER_";
VCREFERENCEWORKORDERNUMBER.OracleType = OracleType.VarChar;
VCREFERENCEWORKORDERNUMBER.Direction = ParameterDirection.Input;
VCREFERENCEWORKORDERNUMBER.Value = fieldsMpWORKREQUEST.VCREFERENCEWORKORDERNUMBER;
cmd.Parameters.Add(VCREFERENCEWORKORDERNUMBER);
OracleParameter VCJOBDESCRIPTION = new OracleParameter();
VCJOBDESCRIPTION.ParameterName = "VCJOBDESCRIPTION_";
VCJOBDESCRIPTION.OracleType = OracleType.VarChar;
VCJOBDESCRIPTION.Direction = ParameterDirection.Input;
VCJOBDESCRIPTION.Value = fieldsMpWORKREQUEST.VCJOBDESCRIPTION;
cmd.Parameters.Add(VCJOBDESCRIPTION);
OracleParameter VCSTATUS = new OracleParameter();
VCSTATUS.ParameterName = "VCSTATUS_";
VCSTATUS.OracleType = OracleType.Char;
VCSTATUS.Direction = ParameterDirection.Input;
VCSTATUS.Value = fieldsMpWORKREQUEST.VCSTATUS;
cmd.Parameters.Add(VCSTATUS);
OracleParameter VCHISTORYFLAG = new OracleParameter();
VCHISTORYFLAG.ParameterName = "VCHISTORYFLAG_";
VCHISTORYFLAG.OracleType = OracleType.Char;
VCHISTORYFLAG.Direction = ParameterDirection.Input;
VCHISTORYFLAG.Value = fieldsMpWORKREQUEST.VCHISTORYFLAG;
cmd.Parameters.Add(VCHISTORYFLAG);
OracleParameter VCREASONCODE = new OracleParameter();
VCREASONCODE.ParameterName = "VCREASONCODE_";
VCREASONCODE.OracleType = OracleType.VarChar;
VCREASONCODE.Direction = ParameterDirection.Input;
VCREASONCODE.Value = fieldsMpWORKREQUEST.VCREASONCODE;
cmd.Parameters.Add(VCREASONCODE);
OracleParameter VCMSSNUMBER = new OracleParameter();
VCMSSNUMBER.ParameterName = "VCMSSNUMBER_";
VCMSSNUMBER.OracleType = OracleType.VarChar;
VCMSSNUMBER.Direction = ParameterDirection.Input;
VCMSSNUMBER.Value = fieldsMpWORKREQUEST.VCMSSNUMBER;
cmd.Parameters.Add(VCMSSNUMBER);
OracleParameter INTAUTHORIZELEVEL = new OracleParameter();
INTAUTHORIZELEVEL.ParameterName = "INTAUTHORIZELEVEL_";
INTAUTHORIZELEVEL.OracleType = OracleType.Number;
INTAUTHORIZELEVEL.Direction = ParameterDirection.Input;
INTAUTHORIZELEVEL.Value = fieldsMpWORKREQUEST.INTAUTHORIZELEVEL;
cmd.Parameters.Add(INTAUTHORIZELEVEL);
OracleParameter INTPRINTFLAG = new OracleParameter();
INTPRINTFLAG.ParameterName = "INTPRINTFLAG_";
INTPRINTFLAG.OracleType = OracleType.Number;
INTPRINTFLAG.Direction = ParameterDirection.Input;
INTPRINTFLAG.Value = fieldsMpWORKREQUEST.INTPRINTFLAG;
cmd.Parameters.Add(INTPRINTFLAG);
OracleParameter INTINSPECTIONFLAG = new OracleParameter();
INTINSPECTIONFLAG.ParameterName = "INTINSPECTIONFLAG_";
INTINSPECTIONFLAG.OracleType = OracleType.Number;
INTINSPECTIONFLAG.Direction = ParameterDirection.Input;
INTINSPECTIONFLAG.Value = fieldsMpWORKREQUEST.INTINSPECTIONFLAG;
cmd.Parameters.Add(INTINSPECTIONFLAG);
OracleParameter INTDAYNUMBER = new OracleParameter();
INTDAYNUMBER.ParameterName = "INTDAYNUMBER_";
INTDAYNUMBER.OracleType = OracleType.Number;
INTDAYNUMBER.Direction = ParameterDirection.Input;
INTDAYNUMBER.Value = fieldsMpWORKREQUEST.INTDAYNUMBER;
cmd.Parameters.Add(INTDAYNUMBER);
OracleParameter INTDURATION = new OracleParameter();
INTDURATION.ParameterName = "INTDURATION_";
INTDURATION.OracleType = OracleType.Number;
INTDURATION.Direction = ParameterDirection.Input;
INTDURATION.Value = fieldsMpWORKREQUEST.INTDURATION;
cmd.Parameters.Add(INTDURATION);
OracleParameter INTPROGRESS = new OracleParameter();
INTPROGRESS.ParameterName = "INTPROGRESS_";
INTPROGRESS.OracleType = OracleType.Number;
INTPROGRESS.Direction = ParameterDirection.Input;
INTPROGRESS.Value = fieldsMpWORKREQUEST.INTPROGRESS;
cmd.Parameters.Add(INTPROGRESS);
OracleParameter INTWORKSHOPFLAG = new OracleParameter();
INTWORKSHOPFLAG.ParameterName = "INTWORKSHOPFLAG_";
INTWORKSHOPFLAG.OracleType = OracleType.Char;
INTWORKSHOPFLAG.Direction = ParameterDirection.Input;
INTWORKSHOPFLAG.Value = fieldsMpWORKREQUEST.INTWORKSHOPFLAG;
cmd.Parameters.Add(INTWORKSHOPFLAG);
string requestNo2 = string.Empty;
int rowEfect = 0;
try
{
cn.Open();
rowEfect = cmd.ExecuteNonQuery();
requestNo2 = cmd.Parameters["VCWORKREQUESTNUMBER_"].Value.ToString();
}
catch (Exception)
{
throw;
}
finally
{
if (cn.State != ConnectionState.Closed) cn.Close();
}
return rowEfect > 0;
}
但是我得到以下错误:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'TABMPWORKREQUEST_INS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
如何解决这个问题?
MSFT系统中的对象。与Oracle相关的数据命名空间已被弃用:
http://msdn.microsoft.com/en-us/library/77d8yct7.aspx但是有一个通用的System.Data.OleDB命名空间:
http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparameter.direction (v = VS.71) . aspx
Command对象有一个参数集合。每个参数都有几个属性，包括一个direction属性。当您将该属性设置为output时，您可以在发出命令后检查其值。
Oracle的第三方提供商应该提供类似的功能。
注:我已经很长时间没有使用OleDB名称空间了——但是回想一下，参数必须按照它们在SP签名中出现的顺序添加到parameters集合中。请确保为每个参数设置适当的数据类型，并提供传递的任何文本值的长度。
P.P.S.看起来好像你向集合中添加了两次相同的参数;其中有一个应该被注释掉吗?
cmd.Parameters.AddWithValue("VCWORKREQUESTNUMBER_",fieldsMpWORKREQUEST.VCWORKREQUESTNUMBER).Direction=ParameterDirection.Output;
OracleParameter requestNo = new OracleParameter();
requestNo.ParameterName = "VCWORKREQUESTNUMBER_";
requestNo.OracleType = OracleType.VarChar;
requestNo.Size = 14;
requestNo.Direction = ParameterDirection.Output;
cmd.Parameters.Add(requestNo);