Check If A Variable Is Null In Plsql

Answer :

if var is NULL then   var :=5; end if; 

Use:

IF Var IS NULL THEN   var := 5; END IF; 

Oracle 9i+:

var = COALESCE(Var, 5) 

Other alternatives:

var = NVL(var, 5) 

Reference:

• COALESCE
• NVL
• NVL2

In PL/SQL you can't use operators such as '=' or '<>' to test for NULL because all comparisons to NULL return NULL. To compare something against NULL you need to use the special operators IS NULL or IS NOT NULL which are there for precisely this purpose. Thus, instead of writing

IF var = NULL THEN... 

you should write

IF VAR IS NULL THEN... 

In the case you've given you also have the option of using the NVL built-in function. NVL takes two arguments, the first being a variable and the second being a value (constant or computed). NVL looks at its first argument and, if it finds that the first argument is NULL, returns the second argument. If the first argument to NVL is not NULL, the first argument is returned. So you could rewrite

IF var IS NULL THEN   var := 5; END IF; 

as

var := NVL(var, 5); 

I hope this helps.

EDIT

And because it's nearly ten years since I wrote this answer, let's celebrate by expanding it just a bit.

The COALESCE function is the ANSI equivalent of Oracle's NVL. It differs from NVL in a couple of IMO good ways:

1. It takes any number of arguments, and returns the first one which is not NULL. If all the arguments passed to COALESCE are NULL, it returns NULL.

2. In contrast to NVL, COALESCE only evaluates arguments if it must, while NVL evaluates both of its arguments and then determines if the first one is NULL, etc. So COALESCE can be more efficient, because it doesn't spend time evaluating things which won't be used (and which can potentially cause unwanted side effects), but it also means that COALESCE is not a 100% straightforward drop-in replacement for NVL.