带有 Oracle 数组绑定的 ExecuteReader
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3423171/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ExecuteReader with Oracle array binding
提问by MonkeyWrench
I'm trying to improve performance of my Oracle SQL queries by using array binding to an OracleParameter.
我正在尝试通过使用数组绑定到 OracleParameter 来提高我的 Oracle SQL 查询的性能。
This is basically what I'm trying to do:
这基本上就是我想要做的：
List<string> IDValList = new List<string>();
IDValList.Add( "IDOne" );
IDValList.Add( "IDTwo" );
List<int> sizes = new List<int>();
foreach( string id in IDValList )
{
sizes.Add( id.Length );
}
using( OracleCommand cmd = new OracleCommand( "select col1, col2, col3 from table where col4 in ( :idArray )", _conn ) )
{
cmd.CommandType = System.Data.CommandType.Text;
OracleParameter arrayParam = new OracleParameter( "idArray", OracleDbType.Varchar2 );
arrayParam.Direction = System.Data.ParameterDirection.Input;
arrayParam.Value = IDValList.ToArray();
arrayParam.ArrayBindSize = sizes.ToArray();
cmd.ArrayBindCount = IDValList.Count;
cmd.Parameters.Add( arrayParam );
using( OracleDataReader dr = cmd.ExecuteReader() )
{
while( dr.Read() )
{
// now read the row...
This compiles and runs, but I always only get back one row, for the first ID. Its like its ignoring the rest of the values in the array in the parameter.
这将编译并运行，但对于第一个 ID，我总是只返回一行。它就像忽略参数数组中的其余值一样。
Interestingly enough, the ArrayBindStatus of the parameter is successful for all the values.
有趣的是，参数的 ArrayBindStatus 对所有值都成功。
What am I missing? Or will this not work with an OracleReader?
我错过了什么？或者这不适用于 OracleReader？
Thanks
谢谢
Edit: Basically, I'm trying to follow this example, but I want to be able to read the resulting dataset from the query using a DataReader.
编辑：基本上，我正在尝试遵循此示例，但我希望能够使用 DataReader 从查询中读取结果数据集。
http://www.oracle.com/technology/oramag/oracle/09-sep/o59odpnet.html
http://www.oracle.com/technology/oramag/oracle/09-sep/o59odpnet.html
回答by Harrison
There are several ways of doing the "variable in list" that you are searching for. To utilize a bound parameter.
有几种方法可以执行您正在搜索的“列表中的变量”。利用绑定参数。
This is based on this: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:210612357425
这是基于此：http: //asktom.oracle.com/pls/asktom/f?p=100:11:0 ::::P11_QUESTION_ID: 210612357425
frankly I try to use bind variables as often as possible if not just for the performance increase, but for the additional level of security.
坦率地说，我尝试尽可能多地使用绑定变量，这不仅是为了提高性能，而且是为了额外的安全级别。
while there are many ways to skin a cat, this (while verbose) ought to do it
虽然有很多方法可以给猫剥皮，但这个（虽然冗长）应该这样做
in Oracle --create your type create or replace type varcharTableType as table of varchar2 (255);
在 Oracle 中 --create 你的类型 create or replace type varcharTableType as table of varchar2 (255);
--create your function
function in_varchar( p_string in varchar2 ) return varcharTableType
as
l_string long default p_string || ',';
l_data varcharTableType := varcharTableType();
n number;
begin
loop
exit when l_string is null;
n := instr( l_string, ',' );
l_data.extend;
l_data(l_data.count) :=
ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
l_string := substr( l_string, n+1 );
end loop;
RETURN L_DATA;
END in_varchar;
now ammend your query in .net
现在在 .net 中修改您的查询
col4 in ( select COLUMN_VALUE from table(in_varchar(:idArray )) )
(btw I copied most of this code from a previous posting I answered on oracle forums: http://forums.oracle.com/forums/thread.jspa?messageID=4299793�
（顺便说一句，我从之前在 oracle 论坛上回答的帖子中复制了大部分代码：http: //forums.oracle.com/forums/thread.jspa?messageID=4299793鰑
this would actually make it so you wouldn't have to use the array binding, just make sure that is a comma delimited string: :idArray = "A,B,C"
这实际上可以做到这样你就不必使用数组绑定，只需确保它是一个逗号分隔的字符串： :idArray = "A,B,C"
Another option is to return the select statements into a ref cursor array:
另一种选择是将 select 语句返回到 ref 游标数组中：
/* example table
*
Create Table Zzztab(Deptno Number, Deptname Varchar2(50) , Loc Varchar2(50) , State Varchar2(2) , Idno Number(10)) ;
/
insert into Zzztab(Deptno , Deptname , Loc , State , Idno)
values (0,'Zero','US','NY',0);
insert into Zzztab(Deptno , Deptname , Loc , State , Idno)
values (1,'One','CA','ON',1);
insert into Zzztab(Deptno , Deptname , Loc , State , Idno)
values (2,'Three','IS',null,2);
insert into Zzztab(Deptno , Deptname , Loc , State , Idno)
values (3,'Four','BD',null,3);
*/
string connectStr = GetConnectionString();
// Initialize array of data
String[] myArrayDeptName = { "Zero", "Three", "Four" };
OracleConnection connection = new OracleConnection(connectStr);
OracleCommand command = new OracleCommand();
command.Connection = connection;
command.CommandType = CommandType.Text ;
command.CommandText = "begin open :cur for SELECT DEPTNO, DEPTNAME FROM ZZZTAB WHERE DEPTNAME = :DEPT; end;";
command.ArrayBindCount = myArrayDeptName.Length ;
command.BindByName = true;
OracleParameter cur = new OracleParameter("cur", OracleDbType.RefCursor );
cur.Direction = ParameterDirection.Output;
cur.Value = myArrayDeptName;
command.Parameters.Add(cur);
// deptname parameter
OracleParameter deptNameParam = new OracleParameter("DEPT", OracleDbType.Varchar2);
deptNameParam.Direction = ParameterDirection.Input;
deptNameParam.Value = myArrayDeptName;
command.Parameters.Add(deptNameParam);
try
{
connection.Open();
command.ExecuteNonQuery();
foreach (Oracle.DataAccess.Types.OracleRefCursor rc in (Oracle.DataAccess.Types.OracleRefCursor[])cur.Value)
{ ... fill in an join the datatables
you can use the exact same logic and just have the columns return into each their own array.
您可以使用完全相同的逻辑，只需将列返回到每个自己的数组中。
回答by Michael Pakhantsov
it will works:
它会起作用：
using
( OracleCommand cmd =
new OracleCommand("select col1, col2, col3 from table where col4 in (" + string.Join(",", IDValList) + ")", _conn ) )
because now you just pass first element from your list.
因为现在您只需从列表中传递第一个元素。
