[sql] Check if a variable is null in plsql

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

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.

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

Another way:

var := coalesce (var, 5);

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

There's also the NVL function

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

use if var is null

Use IS NULL

reference page

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