C#调用Oracle的存储过程，其参数为数组类型作者：abbit | 出处：博客园 | 2011/11/18 22:15:49 | 阅读13次
---------------------------------------------------------------------------
-------------------
Create or replace package packtest
as
type string_array is table of varchar2(21) index by binary_integer;
type int_array is table of number(4) index by binary_integer;
procedure test(v_string  in  string_array,
v_int      out int_array);
end packtet; oracle手动调用存储过程
/
--------------------------------------------------------------------------
Create or replace package body packtest
as
procedure test(v_string in string_array,
v_int    out int_array)
as
cursor c is select num from test;
v_content  varchar2(21);
begin
open c;
for i in 1.. 3
loop
fetch c into v_int;
v_content:=v_string(i);
end loop;
close c;
end test;
end packtest;
/
-------------------------------------------------------------------------
public void BindAssocArray()
{
.
..
OracleCommand cmd = new OracleCommand();
cmd.CommandType=CommandType.StoredProcedure;
cmd.CommandText="st";
//
OracleParameter Param1 = cmd.Parameters.Add(...);
OracleParameter Param2 = cmd.Parameters.Add(...);
//
Param1.Direction = ParameterDirection.Input;
Param2.Direction = ParameterDirection.Output;
// Specify that we are binding PL/SQL Associative Array
Param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
Param2.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
// Setup the values for PL/SQL Associative Array
Param1.Value = new string[3]{"First Element",
"Second Element ",
"Third Element "};
Param3.Value = null;
// Specify the maximum number of elements in the PL/SQL Associative
Array
Param1.Size = 3;
Param2.Size = 3;
/
/
Param2.DbType=DbType.Int32;
// Setup the ArrayBindSize for Param1
Param1.ArrayBindSize = new int[3]{13, 14, 13};
// Setup the ArrayBindStatus for Param1
Param1.ArrayBindStatus = new OracleParameterStatus[3]{
OracleParameterStatus.Success,
OracleParameterStatus.Success,
OracleParameterStatus.Success};
// Setup the ArrayBindSize for Param2
Param2.ArrayBindSize = new int[3]{20, 20, 20};
/
/ execute the cmd
cmd.ExecuteNonQuery();
//print out the parameter's values
int[] matrixint=(int[])Param2.Value;
......
- Show quoted text -
}