What is the correct way to check for null values in PL/SQL?
oracle, plsql, sql
Solution
The correct test is
IF purpose IS NULL THEN
This is because `NULL` is not a value stored in a field. It is an attribute about the field stored elsewhere (but within in the row).
Setting a field to `NULL` appears to be an ordinary assignment so it is seems perfectly orthogonal to expect testing for it by direct comparison. However, for it to work as it does, I surmise the SQL assignment primitive has a magic hidden aspect which diverts assignment of the special symbol `NULL` into setting an attribute and not the field.
Problem
``` IF purpose = null THEN v_purpose := ''; ELSE v_purpose := ' for ' || purpose; END IF; ``` When purpose is null, it still goes to the else...why?!