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

User · Answer

Use IS NULL  reference page

User · Answer

There s also the NVL function  http   www techonthenet com oracle functions nvl php

User · Answer

Another way   var    coalesce  var  5     COALESCE is the ANSI equivalent  more or less  of Oracle s NVL function

User · Answer

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  lt  gt  1 then     i  1      foobar      end if  end      Never goes inside the if clause   These would work   if 1 lt  gt nvl i 1  then if i lt  gt  1 or i is null then

User · Answer

use if var is null

User · Answer

In PL SQL you can t use operators such as     or   lt    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    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  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

User · Answer

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

User · Answer

if var is NULL then   var   5  end if

[sql] Check if a variable is null in plsql

Examples related to sql

Examples related to oracle

Examples related to plsql