PL/SQL Fundamentals Exercises: PL/SQL block to describe NULL values in unequal comparison

PL/SQL Fundamentals: Exercise-15 with Solution

Write a PL/SQL block to describe the usage of NULL values in equal comparison, unequal comparison and NOT  NULL equals NULL comparison.

In the following example the m and n seem unequal. But, NULL values are indeterminate. Whether m equals n is unknown. Therefore, the IF condition yields NULL and the sequence of statements is bypassed, similarly o and p seem equal. But, again, that is unknown, so the IF condition yields NULL and the sequence of statements is bypassed and however, if either q or r is NULL, then the first IF statement assigns the value of r to large and the second IF statement assigns the value of q to large.

PL/SQL Code:

DECLARE
  m NUMBER := 7;
  n NUMBER := NULL;

  o NUMBER := NULL;
  p NUMBER := NULL;

  q    INTEGER := 4;
  r    INTEGER := 9;

 large INTEGER;

----------------------------------
BEGIN
  IF m != n THEN  -- yields NULL, not TRUE
    DBMS_OUTPUT.PUT_LINE('m != n');  -- not run
  ELSIF m = n THEN -- also yields NULL
    DBMS_OUTPUT.PUT_LINE('m = n');
  ELSE
    DBMS_OUTPUT.PUT_LINE
      ('Can not say whether m and n are equal or not.');
  END IF;

-----------------------------------
  IF o = p THEN  -- yields NULL, not TRUE
    DBMS_OUTPUT.PUT_LINE('o = p');  -- not run
  ELSIF o != p THEN  -- yields NULL, not TRUE
    DBMS_OUTPUT.PUT_LINE('o != p');  -- not run
  ELSE
    DBMS_OUTPUT.PUT_LINE('Can not say whether two NULLs are equal');
  END IF;
--------------------------------------
  IF (q > r)       -- If q or r is NULL, then (q > r) is NULL
    THEN large  := q;  -- run if (q > r) is TRUE
    ELSE large  := r;  -- run if (q > r) is FALSE or NULL
DBMS_OUTPUT.PUT_LINE('The value of large : '||large);
  END IF;
  
  IF NOT (q > r)   -- If q or r is NULL, then NOT (q > r) is NULL
    THEN large  := r;  -- run if NOT (q > r) is TRUE
    ELSE large  := q;  -- run if NOT (q > r) is FALSE or NULL
DBMS_OUTPUT.PUT_LINE('The value of large : '||large);
  END IF;

END;
/

Sample Output:

Can not say whether m and n are equal or not.
Can not say whether two NULLs are equal
The value of large : 9

Statement processed.

0.00 seconds

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a PL/SQL block to create a procedure using the "IS [NOT] NULL Operator" and show  NOT operator returns the opposite of its operand, unless the operand is NULL.
Next: Write a PL/SQL block to describe the usage of LIKE operator including wildcard characters and escape character.