oracle OracleParameter 和 DBNull.Value
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1701382/
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
OracleParameter and DBNull.Value
提问by Viper
we have a table in an Oracle Database which contains a column with the type Char(3 Byte).
Now we use a parameterized sql to select some rows with a DBNull.Value and it doesn't work:
我们在 Oracle 数据库中有一个表，其中包含一个类型为 Char(3 Byte) 的列。
现在我们使用参数化的 sql 来选择一些带有 DBNull.Value 的行，但它不起作用：
OracleCommand command = null;
OracleDataReader dataReader = null;
string sql = "select * from TEST_TABLE where COLUMN_1 = :COLUMN_1";
try
{
OracleConnection connection = (OracleConnection) dbConnection;
command = new OracleCommand( sql, connection );
OracleParameter param_1 = new OracleParameter( "COLUMN_1", OracleDbType.Char );
command.Parameters.Add( param_1 );
param_1.Value = DbNull.Value;
dataReader = command.ExecuteReader( );
int recordCount = 0;
while( dataReader.Read( ) == true )
{
recordCount++;
}
Console.WriteLine( "Count = " + recordCount ); // is 0
}
[...]
Did i miss something? We definitly have some rows which contains a DBNull,
but the circumstance that you would write a 'normal' sql with "is null" and not "= null"
is also noticeable.
我错过了什么？我们肯定有一些包含 DBNull 的行，
但是您将使用“is null”而不是“= null”编写“正常”sql的
情况也很明显。
Can somebody explain this behaviour? How do you write a parameterized sql where you need a condition to check for a DBNull?
有人可以解释这种行为吗？您如何编写需要条件来检查 DBNull 的参数化 sql？
Thanks
谢谢
回答by David Oneill
Null is the absence of being set to anything so you won't get the correct behavior with '= null'. Because null is the absence of value, it is not meaningful to say "This variable that lacks any value has the same value as this other variable that lacks any value." You can't have the same value of something else if you don't have a value.
Null 是没有被设置为任何东西，所以你不会得到'= null'的正确行为。因为 null 是没有值，所以说“这个没有任何值的变量与另一个没有任何值的变量具有相同的值”是没有意义的。如果你没有价值，你就不能拥有与其他事物相同的价值。
One way to get around this is to create two sql statements, one that accepts the parameter and another with 'is null'. Then use an 'if' statement to choose which one to use.
解决此问题的一种方法是创建两个 sql 语句，一个接受参数，另一个为“为空”。然后使用“if”语句来选择要使用的语句。
statement 1:
声明 1：
string sql = "select * from TEST_TABLE where COLUMN_1 = :COLUMN_1
statement 2:
陈述2：
string sql = "select * from TEST_TABLE where COLUMN_1 is null
That is unless you're always comparing to null. Then, just use statement 2
除非您总是与 null 进行比较。然后，只需使用语句 2
回答by Phil Ross
In this situation you have to use IS NULL:
在这种情况下，您必须使用IS NULL：
string sql = "select * from TEST_TABLE where COLUMN_1 is null";
Performing any comparison with a null value in SQL will always result in an unknownresult, which means you won't get any rows returned.
在 SQL 中执行与空值的任何比较将始终导致未知结果，这意味着您不会返回任何行。
回答by David Aldridge
you can do this:
你可以这样做：
select *
from TEST_TABLE
where (COLUMN_1 = :COLUMN_1 and :COLUMN_1 Is Not Null) Or
(COLUMN_1 Is Null and :COLUMN_1 Is Null)
回答by Boris Modylevsky
Modify your code like this:
像这样修改你的代码：
OracleCommand command = null;
OracleDataReader dataReader = null;
string sql = "select * from TEST_TABLE where COLUMN_1 IS NULL"
try
{
OracleConnection connection = (OracleConnection) dbConnection;
command = new OracleCommand( sql, connection );
dataReader = command.ExecuteReader( );
int recordCount = 0;
while( dataReader.Read( ) == true )
{
recordCount++;
}
Console.WriteLine( "Count = " + recordCount ); // is 0
}
回答by Marius Burz
SELECT t1.*
FROM t1, (SELECT :s v FROM dual) tmp
WHERE t1.s = tmp.v OR (t1.s IS NULL AND tmp.v IS NULL)
This would also do it.
这也可以。
