在 C# 中使用带有 LIKE 的 Oracle 绑定变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3790424/
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
Usage of Oracle binding variables with LIKE in C#
提问by PJ.
As part of an effort to stop using dynamic SQL generation and encourage use of bind variables, I am running into some problems.
作为停止使用动态 SQL 生成并鼓励使用绑定变量的努力的一部分，我遇到了一些问题。
I am querying an Oracle 9i database from an ASP.NET page using Oracle Data Providers for .NET
我正在使用 Oracle Data Providers for .NET 从 ASP.NET 页面查询 Oracle 9i 数据库
The query is
查询是
sql = "SELECT somedata FROM sometable WHERE machine = :machineName ";
I define the Oracle Parameter as follows
我将 Oracle 参数定义如下
OracleParameter parameter = new OracleParameter();
parameter.ParameterName = "machineName";
parameter.OracleDbType = OracleDbType.Varchar2;
parameter.Value = machine; //machine is a variable of type string
parameterList.Add(parameter);
This works fine for "=" operator. But I just can't seem to get it to work with "LIKE". I don't know how to format the query so that it accepts usage of the "%" wildcard.
这适用于“=”运算符。但我似乎无法让它与“LIKE”一起使用。我不知道如何格式化查询以使其接受“%”通配符的使用。
I have tried:
我试过了：
sql = "SELECT somedata FROM sometable WHERE machine LIKE :machineName% ";
sql = "SELECT somedata FROM sometable WHERE machine LIKE ':machineName%' ";
sql = "SELECT somedata FROM sometable WHERE machine LIKE :machineName||% ";
and also:
并且：
parameter.Value = machine+'%';
but all I get are ORA-00911 (illegal character) and ORA-01036 (illegal name/value) exceptions.
但我得到的只是 ORA-00911（非法字符）和 ORA-01036（非法名称/值）异常。
What am I doing wrong?
我究竟做错了什么？
回答by OMG Ponies
Try:
尝试：
sql = "SELECT somedata FROM sometable WHERE machine LIKE :machineName || '%' ";
Because of the BIND variable, there wouldn't need to be single quotes around it. But the % is not, so I would expect it needing to be encapsulated.
由于 BIND 变量，因此不需要在它周围加上单引号。但是 % 不是，所以我希望它需要被封装。
