Three BOOLEAN values of PL/SQL: TRUE, FALSE, NULL

The BOOLEAN type in Oracle PLSQL has three values: TRUE, FALSE, and NULL. These three values cause unnecessary trouble during development.

The BOOLEAN type in Oracle PL/SQL has three values: TRUE, FALSE, and NULL. These three values cause unnecessary trouble during development.

The BOOLEAN type in Oracle PL/SQL has three values: TRUE, FALSE, and NULL. These three values cause unnecessary troubles during development, for example

DECLARE
B _FLAG_TRUE BOOLEAN: = TRUE;
B _FLAG_FALSE BOOLEAN: = FALSE;
B _FLAG_NULL BOOLEAN: = NULL;
BEGIN
IF (B _FLAG_NULL AND B _FLAG_TRUE) THEN
DBMS_OUTPUT.PUT_LINE ('null AND true ');
End if;
If not (B _FLAG_NULL AND B _FLAG_TRUE) THEN
DBMS_OUTPUT.PUT_LINE ('not (null and true )');
End if;
END;

The above two judgments have no output characters. Therefore, let's take a look at the problem:

DECLARE
B _FLAG_TRUE BOOLEAN: = TRUE;
B _FLAG_FALSE BOOLEAN: = FALSE;
B _FLAG_NULL BOOLEAN: = NULL;
B _FLAG_RESULT BOOLEAN;
Function tsboolean (B _FLAG BOOLEAN) RETURN VARCHAR2 IS
BEGIN
Return case B _FLAG
When true then 'true'
When false then 'false'
ELSE 'null'
END;
End tsboolean;
BEGIN
B _FLAG_RESULT: = (B _FLAG_NULL AND B _FLAG_TRUE );
DBMS_OUTPUT.PUT_LINE (TSBOOLEAN (B _FLAG_RESULT ));
IF B _FLAG_RESULT THEN
DBMS_OUTPUT.PUT_LINE (''| '=>' | '');
End if;
If not B _FLAG_RESULT THEN
DBMS_OUTPUT.PUT_LINE (''| '=>' | '');
End if;
END;

Originally, the output result above is NULL, so there is a standard: "We must expect that the result of a Boolean expression will have a NULL value, so we must use NVL () function to avoid unexpected results! ". Perform the following operations based on the actual result value:

DECLARE
B _FLAG_TRUE BOOLEAN: = TRUE;
B _FLAG_FALSE BOOLEAN: = FALSE;
B _FLAG_NULL BOOLEAN: = NULL;
B _FLAG_RESULT BOOLEAN;
BEGIN
B _FLAG_RESULT: = (NVL (B _FLAG_NULL, FALSE) AND B _FLAG_TRUE );
IF (B _FLAG_RESULT) THEN
DBMS_OUTPUT.PUT_LINE ('null AND true ');
End if;
If not B _FLAG_RESULT THEN
DBMS_OUTPUT.PUT_LINE ('not (null and true )');
End if;
END;