Check whether two fields are the same in OraclePL/SQL.

When two fields in OraclePLSQL are judged to be equal or not equal, it often appears that the field values are clearly not equal (one is empty, one is not empty), but when the judgment is not equal

When two fields in Oracle PL/SQL are judged to be equal or not equal, it often appears that the field values are clearly not equal (one is blank, one is not blank), but when the judgment is not equal

When two fields in Oracle PL/SQL are determined to be equal or not equal, it often appears that the field values are obviously not equal (one is blank and the other is not empty ), however, TRUE is not obtained when the results are not equal.

For example, the following code:

...

A1 VARCHAR2 (50 );

A2 VARCHAR2 (50 );

B1 VARCHAR2 (50 );

B2 VARCHAR2 (50 );

...

IF (a1 <> a2 OR b1 <> b2) THEN

My_Modify (a2, b2 );

End if;

...

IF (a1 = a2 AND b1 = b2) THEN

-- Do something not useful

A1: = a1;

ELSE

My_Modify (a2, b2 );

End if;

When a1 is equal to a2 and b1 is not equal to b2:

My_Modify (a2, b2); is not executed frequently, but I don't know why. Is the symbol unstable?

A: The field null is not considered as unstable. For details, see the analysis below.

2012-8-27 Add instructions:

After testing and analysis, it IS found that the field value IS not <> unstable, but NULL. The = or <> comparison value cannot be used, and is null should be used to determine whether it is null.

When one or more of the a1, a2, b1, and b2 fields are null, the following two statements are invalid:

1. IF (a1 <> a2 OR b1 <> b2) THEN

2. IF (a1 = a2 AND b1 = b2) THEN

For example, if a1 is null, a2, b1, and b2 are not empty, the statement should be written as follows:

1. IF (a1 is null and a2 is not null) OR b1 <> b2) THEN

2. IF (a1 is null and a2 is null) AND b1 = b2) THEN

To solve the problem of "unable to judge equal or unequal" caused by NULL, we can use the NVL function to solve the problem. The statement is as follows:

1. IF (NVL (a1, 0) <> NVL (a2, 0) or nvl (b1, 0) <> NVL (b2, 0) THEN

2. IF (NVL (a1, 0) = NVL (a2, 0) and nvl (b1, 0) = NVL (b2, 0) THEN

Note: NVL (args, deafultValue), args is the variable, and deafultValue is the default value (usually 0) set when args is null ).

Where: NVL (a1, 0) and NVL (a1, '0') effect samples, the last a1 value is a string: '0' (not a character !).

Here is a piece of test code:

-- Created on 2012
DECLEAR
A1 VARCHAR2 (50 );
A2 VARCHAR2 (50 );
B1 VARCHAR2 (50 );
B2 VARCHAR2 (50 );
BEGIN
A1: = 'a ';
A2: = 'a ';
B1: = 'B ';
B2: = NULL;

IF (a1 <> a2 OR b1 <> b2) THEN
DBMS_OUTPUT.put_line ('123 ');
End if;

IF (NVL (a1, 0) <> NVL (a2, 0) or nvl (b1, 0) <> NVL (b2, 0) THEN
DBMS_OUTPUT.put_line ('there is a pair of unequal values from ');
End if;

IF (NVL (a1, 0) = NVL (a2, 0) and nvl (b1, 0) = NVL (b2, 0) THEN
DBMS_OUTPUT.put_line ('all equal ');
End if;

IF (a1 = a2 AND b1 = b2) THEN
DBMS_OUTPUT.put_line ('123 ');
End if;
END;