一个很棒的ORACLE参数绑定问题的解决

在最近做的一个项目里面碰到了类似下面的问题(为了便于阐述，进行了适当的简化)：
Dim strsql As String
For i=0 to N
    strsql="UPDATE TABLE SET NAME='wayne' WHERE NAME = :strName"
    Dim cmd As New OracleCommand(strsql, mOracleCnnDB)
    For j= 0 to M
        cmd.Parameters.Add(New OracleParameter(":strName", OracleType.VarChar)).Value = EmptyToDBNull( strName(j) )
        cmd.ExecuteNonQuery()
    Next
Next
其中EmptyToDBNull函数依据输入的字符串str给出输出value，若str=""则value=DBNull.Value；若str<>""则value=str
注意上面的strsql语句，本意是想利用ORACLE的参数绑定，减少对SQL语句的编译，提高程序性能。但是却发现一个严重的问题：
当在第二层循环中对绑定的参数:strName进行赋值的时候，如果strName(j)的值不为空，比如strName(0)="Jack"，那么strsql语句实际上是：
UPDATE TABLE SET NAME='wayne' WHERE NAME='Jack'
毫无疑问，这个SQL语句是可以正确执行的，会将所有NAME字段值等于'Jack'的记录的NAME字段值更新为'wayne'。
如果某一个strName(j)值为空的话，比如strName(2)=""，好了，问题就来了，此时strsql语句变成了
UPDATE TABLE SET NAME='wayne' WHERE NAME=''
很明显，这个SQL语句虽然可以执行，但却并不是我们预想的结果，因为这个SQL语句不会更新任何记录。我们知道，在ORACLE中对于空值的判断应该采用下面的方式：
UPDATE TABLE SET NAME='wayne' WHERE NAME IS NULL
这样才能将所有为空值的NAME字段更新为'wayne'。

下面我们要做的工作就是怎么样才能改进这个strsql语句，使它能真正适应我们的需求呢？
有人给出下面的解决方法，思路就是在前期生成strsql语句时候就通过判断:strName变量是否为空，从而生成不同的strsql语句，即如下形式：
If strName(j) <> ""
    strsql="UPDATE TABLE SET NAME='wayne' WHERE NAME = :strName"
Else
    strsql="UPDATE TABLE SET NAME='wayne' WHERE NAME IS NULL"
End If
很明显，这种改法并不符合我们预期的想法，一旦这样改的话，就失去了参数绑定的优势了，与普通不用参数绑定的方式效率一样了。
还有人说，根本不能按照你预想的方式写出这样的strsql语句。想仅仅通过NAME=:strName来统一空值和非空值是决不可能的。

问题当然是可以解决的啦，不然就没有这篇随笔啦，呵呵。最后，就让我们看看怎么解决这个问题：
其实是在MSDN上面找到我们需要的解决办法，在MSDN中有一节”数据访问：使用 ADO.NET 的最佳实践（ADO.NET 技术文档）“中讲到了“测试Null” 和“把Null作为参数值传递”，原文是这样写的：

测试 Null
如果表（在数据库中）中的列允许为空，就不能测试参数值是否“等于”空。相反，需要写一个 WHERE 子句，测试列和参数是否都为空。下面的 SQL 语句返回一些行，它们的 LastName 列等于赋给 @LastName 参数的值，或者 LastName 列和 @LastName 参数都为空。
SELECT * FROM Customers　WHERE (LastName = @LastName) OR (LastName IS NULL AND @LastName IS NULL)

把 Null 作为参数值传递
对数据库的命令中，当把空值作为参数值发送时，不能使用 null（Visual Basic .NET 中为 Nothing）。而需要使用 DBNull.Value。例如：
'Visual Basic
Dim param As SqlParameter = New SqlParameter("@Name", SqlDbType.NVarChar, 20)
param.Value = DBNull.Value
//C#
SqlParameter param = new SqlParameter("@Name", SqlDbType.NVarChar, 20);
param.Value = DBNull.Value;

基于上面的两点参考，不难将我们的程序中的strsql修改为如下形式即可：
strsql="UPDATE TABLE SET NAME='wayne' WHERE (NAME = :strName) OR (NAME IS NULL AND NVL(:strName,'空白值')='空白值')"
问题迎刃而解了，哈哈。

p.s.推荐大家一个很好用的下载微软Webcast课程的C#编写的开源工具：IReaper