December 15, 2006

Equal and UnEqual

Sometimes you have to check the Equality of two variables.

Writing "IF A = B THEN" is not the solution for all cases. If one variable is NULL the whole statement is NULL and NULL becomes FALSE in an IF-Statement. So you have to work with a different technique:


FUNCTION Equal (P_String1 IN VARCHAR2,
P_String2 IN VARCHAR2) RETURN BOOLEAN IS
BEGIN
IF P_String1 = P_String2
OR (P_String1 IS NULL AND P_String2 IS NULL) THEN
RETURN (TRUE);
ELSE
RETURN (FALSE);
END IF;
END;

FUNCTION UnEqual (P_String1 IN VARCHAR2,
P_String2 IN VARCHAR2) RETURN BOOLEAN IS
BEGIN
IF P_String1 != P_String2
OR ( P_String1 IS NULL
AND P_String2 IS NOT NULL)
OR ( P_String1 IS NOT NULL
AND P_String2 IS NULL) THEN
RETURN (TRUE);
ELSE
RETURN (FALSE);
END IF;
END;

Now you can easily use Equal and UnEqual

IF UnEqual (Var1, Var2) THEN
-- do something
ELSE
-- do something different
END IF;


try it

1 comment:

Anonymous said...

In Oracle, as you pointed out, two nulls are never equal, so if A and B are both nulls, then they are not equal.

However, your statement:
'Writing "IF A = B THEN" is not correct at all.'
is not necessarily true. There are many cases when you don't want either A or B to be null, so it is desirable (and correct) to use
"IF A=B THEN"