Oracle null characters are equal

From: http://blog.csdn.net/zhiguo2010/article/details/5737492

In Oracle, the Null String is considered null, and any value compared with null is null. In this way, an exception occurs when comparing two strings. See the following example:

Declare
I varchar2 (10): = NULL;
V varchar2 (10): = 'abc ';
Begin
If (I = V) then
Dbms_output.put_line ('Equality ');
Else
Dbms_output.put_line ('unequal ');
End if;
End;
Please use your smart mind to determine what the result should be? It is easy to get the result: 'unequal '. Good. You're right. The running result on sqlplus is the same as what you want. Then let me change the program and you can judge again:
Declare
I varchar2 (10): = NULL;
V varchar2 (10): = 'abc ';
Begin
If (I <> V) then
Dbms_output.put_line ('unequal ');
Else
Dbms_output.put_line ('Equality ');
End if;
End;
It seems that there is no big difference with the first program, and it is easy to come to the result: 'unequal '. Haha. Are you sure this is the result? Test SQL plus to verify that you are correct. Unfortunately, the correct result should be: 'Equality '. Are you surprised? As mentioned at the beginning: the result of comparing any value with null is null. That is, the result of I = V comparison in the first program should be null, and the result of I <> V comparison in the second program is also null. When the condition in the IF structure is null, the current branch will be skipped and entered to else or ended. No? Run the following program and wait for verification:
Begin
If (null) then
Dbms_output.put_line ('not null ');
Else
Dbms_output.put_line ('null ');
End if;
End;
The result is 'null '.
So how should we correctly compare the two strings?
First, let's talk about how to determine whether the two strings are equal (or use the two variables I and V above ).
1. When I and V are both null, I and V are considered equal. I is null and V is null (do not write it like this: I = v. From the above analysis, we can know that the Write result is null ).
2. When only one of I and V is null, it is definitely not equal.
3. When neither I nor V is null, we can use '=' to determine whether they are equal. I is not null and V is not null and I = v.
Based on the above three points, we can obtain the condition expression that is equal to I and V: I is null and V is null or I is not null and V is not null and I = v.
If the two strings are not equal, you only need to judge the equal expression as false.
Write the function of determining whether two strings are equal into a function:
Create or replace function isequal
(
Var1 in varchar2,
Var2 in varchar2
)
Return number -- 0: unequal 1: Equal-1: Error
Is
If (var1 is null and var2 is null or var1 is not null and var2 is not null and var1 = var2) then
Return 1;
Else
Return 0;
Enf if;
Begin
Exception
When others then
Return-1;
End;
Test the procedure as follows:
Declare
Var1 varchar2 (10): = NULL;
Var2 varchar2 (10): = 'a ';
Begin
If (isequal (var1, var2) = 1) then
Dbms_output.put_line ('= ');
Else
Dbms_output.put_line ('<> ');
End if;
End;