将数组传递给存储过程 [英] pass array to stored procedure
问题描述
我必须将数组和字符串传递给存储过程并返回数据表
I have to pass arrays and strings to stored procedure and return data table
C#侧:
public DataTable fetchRequested(string [] empID, string [] account, string [] refNo, string orgID, string Id, DateTime valueDate)
{
string connetionString = null;
OracleConnection con;
OracleDataAdapter objAdapter = null;
OracleCommand objComm = new OracleCommand();
connetionString = @"Data Source= Payment_devlope; User ID=ORGPAYMENT;Password=OrgPayment";
con = new OracleConnection (connetionString);
try
{
con.Open();
objComm.Connection = con;
objComm.CommandType = CommandType.StoredProcedure;
objComm.CommandText = "PKG_REPORTS.Requested_Payment";
// Add and Set Procedure Parameters
////////////////////////////////////////////////////////////////////
objComm.Parameters.Add("p_empID", OracleDbType.NVarchar2, 100);
objComm.Parameters["p_empID"].CollectionType = OracleCollectionType.PLSQLAssociativeArray;
if (empID.Length != 0)
objComm.Parameters["p_empID"].Value = empID;
////////////////////////////////////////////////////////////////////
objComm.Parameters.Add("p_account", OracleDbType.NVarchar2, 100);
objComm.Parameters["p_account"].CollectionType = OracleCollectionType.PLSQLAssociativeArray;
if (account.Length != 0)
objComm.Parameters["p_account"].Value = account;
////////////////////////////////////////////////////////////////////
objComm.Parameters.Add("p_refrence_number", OracleDbType.NVarchar2, 100);
objComm.Parameters["p_refrence_number"].CollectionType = OracleCollectionType.PLSQLAssociativeArray;
if (refNo.Length != 0)
objComm.Parameters["p_refrence_number"].Value = refNo;
////////////////////////////////////////////////////////////////////
objComm.Parameters.Add("p_Organization_Id", OracleDbType.Varchar2);
objComm.Parameters["p_Organization_Id"].Direction = ParameterDirection.Input;
if (!String.IsNullOrEmpty(orgID))
{
objComm.Parameters["p_Organization_Id"].Value = orgID;
}
////////////////////////////////////////////////////////////////////
objComm.Parameters.Add("p_Id", OracleDbType.Varchar2);
objComm.Parameters["p_Id"].Direction = ParameterDirection.Input;
if (!String.IsNullOrEmpty(Id))
{
objComm.Parameters["p_Id"].Value = Id;
}
////////////////////////////////////////////////////////////////////
objComm.Parameters.Add("p_Value_date", OracleDbType.Date);
objComm.Parameters["p_Value_date"].Direction = ParameterDirection.Input;
if (valueDate == null)
{
objComm.Parameters["p_Value_date"].Value =valueDate;
}
////////////////////////////////////////////////////////////////////
objComm.Parameters.Add("cur_report_data", OracleDbType.RefCursor);
objComm.Parameters["cur_report_data"].Direction = ParameterDirection.Output;
////////////////////////////////////////////////////////////////////
objAdapter = new OracleDataAdapter();
objAdapter.SelectCommand = objComm;
// Filling Dataset with searched result
DataSet ds = new DataSet();
objAdapter.Fill(ds,"ReportData");
return ds.Tables["ReportData"];
}
finally
{
if (objAdapter != null)
{
objAdapter.Dispose();
objAdapter = null;
}
if (objComm != null)
{
objComm.Dispose();
objComm = null;
}
}
}
PL/sql端:
Procedure Requested_Payment (
p_empID in nvarchar_array := Null ,
p_account in nvarchar_array := Null,
p_refrence_number in nvarchar_array := Null,
p_Organization_Id in nvarchar2 := NULL,
p_Id in nvarchar2 := NULL,
p_Value_date in date := NULL,
cur_report_data out Data_Table
)
is
BEGIN
open cur_report_data for
SELECT /*+ index(bp,B_PAYMENT_PK) */
org.ORGANIZATION_CODE org_ID,
bm.ID,
bd.BENEFICIARY_organization_ID Employee_ID,
bd.BENEFICIARY_NAME Employee_Name,
bp.REFERENCE_NUMBER Reference_No,
bp.CREDIT_ACCOUNT ACC_NO,
ct.ENGLISH_DESCRIPTION Reason,
from
Organization org inner join B_MASTER bm
on bm.organization_CODE = org.organization_CODE
inner join B_DETAIL bd
on bd.MASTER_SEQUENCE_ID = bm.MASTER_SEQUENCE_ID
inner join B_payment bp
on bp.MASTER_SEQUENCE_ID = bm.MASTER_SEQUENCE_ID
inner join CODE_TABLE ct
on ct.code = BP.PAYMENT_STATUS
where
(p_Organization_Id is Null or org.organization_CODE = p_Organization_Id)
AND (p_Id is Null OR bm.BATCH_NUMBER = p_Id)
AND (p_Value_date is null or bm.Debit_VALUE_DATE between startofday(p_Value_date) and endofday(p_Value_date))
AND (ct.GROUP_CODE = 4)
AND (BD.DETAIL_SEQUENCE_ID = BP.DETAIL_ID)
AND (p_empID is Null or bd.BENEFICIARY_organization_ID in (Select column_value FROM TABLE (p_empID)))
AND (p_account is null or bp.CREDIT_ACCOUNT in (Select column_value FROM TABLE (p_account)))
AND (p_refrence_number is null or bp.REFERENCE_NUMBER in (Select column_value FROM TABLE (p_account)))
;
end Requested_Payment ;
创建类型:
CREATE OR REPLACE TYPE ORGPAYROLL.NVARCHAR_ARRAY
AS TABLE OF VARCHAR2(100)
当数组之一为空时，我在Visual Studio中收到此错误:
and I got this error in Visual Studio when one of the array is empty:
OracleParameter.Value无效
OracleParameter.Value is invalid
当我传递所有三个数组时，我得到了这个错误:
and I got this error when I pass all the three array:
ORA-06550:第1行，第7列:
PLS-00306:调用"REQUESTED_PAYMENT"时参数的数量或类型错误
ORA-06550:第1行，第7列:
PLS-00306:调用"REQUESTED_PAYMENT"时参数的数量或类型错误
ORA-06550:第1行，第7列:
PLS-00306:调用"REQUESTED_PAYMENT"时参数的数量或类型错误
ORA-06550:第1行，第7列:
PL/SQL:语句被忽略
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'REQUESTED_PAYMENT'
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'REQUESTED_PAYMENT'
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'REQUESTED_PAYMENT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
推荐答案
您的存储过程不应包含表类型，而应包含associative array.您可以在存储过程中将其转换为所需的类型.请参阅此链接更多详细信息
Your stored procedure should not be expecting table type instead it should be expecting associative array. You can convert this to your desired type with in your stored procedure. See this link more details
这篇关于将数组传递给存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
