A great solution to Oracle parameter binding Problems

In a recent project, I encountered a problem similar to the following (to facilitate the elaboration, I made a proper simplification ):
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
The emptytodbnull function provides the output value based on the input string Str. If STR = ", value = dbnull. value; If STR <>" ", value = Str
Note that the preceding strsql statement is intended to use ORACLE parameter binding to reduce SQL statement compilation and improve program performance. However, we found a serious problem:
When a value is assigned to the bound parameter strname In the second loop,If the strname (j) value is not emptyFor example, if strname (0) = "Jack", the strsql statement is actually:
Update table set name = 'Wayne 'Where name = 'jack'
Undoubtedly, this SQL statement can be correctly executed, and the value of the Name field of all records whose name field value is 'jack' is updated to 'Wayne '.
If a strname (j) value is nullFor example, strname (2) = "". All right, the problem arises. The strsql statement becomes
Update table set name = 'Wayne 'Where name =''
Obviously, although this SQL statement can be executed, it is not the expected result, because this SQL statement will not update any records. We know that the following method should be used to determine null values in Oracle:
Update table set name = 'Wayne 'Where name is null
In this way, all name fields with null values can be updated to 'Wayne '.

What we need to do next is to improve the strsql statement so that it can truly adapt to our needs?
Some people provide the following solution. The idea is to generate different strsql statements by checking whether the strname variable is null at the early stage:
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
Obviously, this change does not conform to our expectation. Once this change is made, the advantage of parameter binding is lost, which is the same as that of common parameter binding without parameters.
Some people say that such strsql statements cannot be written as expected. It is impossible to unify null values and non-null values by name =: strname alone.

Of course, the problem can be solved. Otherwise, there will be no such essay. Finally, let's see how to solve this problem:
In fact, we found the solution we needed on msdn. In msdn, there is a section "Data Access: using ADO. net Best Practices (ADO. net Technical Documentation) "describes" test null "and" Pass null as a parameter value ". The original Article is written as follows:

Test null
If columns in a table (in a database) can be empty, you cannot test whether the parameter value is "equal to" null. On the contrary, you need to write a where clause to test whether the columns and parameters are empty. The following SQL statement returns some rows whose lastname column is equal to the value assigned to the @ lastname parameter, or the lastname column and @ lastname parameters are empty.
Select * from MERs where (lastname = @ lastname) or (lastname is null and @ lastname is null)

Pass null as the parameter value
In database commands, when a null value is sent as a parameter value, null cannot be used (nothing in Visual Basic. net ). Dbnull. value is required. For example:
'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;

Based on the above two references, it is not difficult to modify strsql in our program to the following form:
Strsql = "Update table set name = 'Wayne 'Where (name =: strname) or (name is null and nvl (: strname, 'blank value') = 'blank value ')"
The problem is solved, haha.

P.s. We recommend that you download ireaper, an open-source tool compiled by C # Of the Microsoft webcast course.