Check if a variable is null in plsql

Question

I want to check if a variable is null. If it is null, then I want to set a value to that variable:

//data type of var is  number 
if Var = null then
  var :=5;
endif

But I am geting error in it. How can I check if a variable is null?

I am using oracle data type

Answer 1

Use IS NULL

reference page

Answer 2

use if var is null

Answer 3

Use:

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

Oracle 9i+:

var = COALESCE(Var, 5)

Other alternatives:

var = NVL(var, 5)

Reference:

• COALESCE
• NVL
• NVL2

Answer 4

if Var is NULL then
  var :=5;
endif

Answer 5

There's also the NVL function

http://www.techonthenet.com/oracle/functions/nvl.php

Answer 6

Always remember to be careful with nulls in pl/sql conditional clauses as null is never greater, smaller, equal or unequal to anything. Best way to avoid them is to use nvl.

For example

declare
  i integer;
begin
  if i <> 1 then
    i:=1;
    foobar();
  end if;
end;
/

Never goes inside the if clause.

These would work.

if 1<>nvl(i,1) then
if i<> 1 or i is null then

Answer 7

Another way:

var := coalesce (var, 5);

COALESCE is the ANSI equivalent (more or less) of Oracle's NVL function.

Answer 8

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.