NVL

From Wikipedia, the free encyclopedia
Jump to: navigation, search

In Oracle's dialect of SQL (and in PL/SQL), the NVL function lets you substitute a value when a null value is encountered.[1]

The syntax for the NVL function is:

  • NVL( string1, replace_with_if_null )
    
    string1 is the string to test for a null value. replace_with_if_null is the value returned if string1 is null.

If the first argument is a character type the function returns a varchar2 value. If the first argument is numeric the function returns a numeric value. You cannot use this function to replace a null integer by a string unless you call the TO_CHAR function on that value:

  • NVL(TO_CHAR(numeric_column), 'some string')
    

In comparison to COALESCE NVL evaluates all arguments before execution:

With NVL:

SQL> DECLARE
  2    a NUMBER;
  3  
  4    FUNCTION err RETURN NUMBER IS
  5    BEGIN
  6      RAISE PROGRAM_ERROR;
  7    END err;
  8  BEGIN
  9    a := NVL(1, err);
 10  END;
 11  /
DECLARE
  a NUMBER;
 
  FUNCTION err RETURN NUMBER IS
  BEGIN
    RAISE PROGRAM_ERROR;
  END err;
BEGIN
  a := NVL(1, err);
END;
ORA-06501: PL/SQL: program error
ORA-06512: AT line 6
ORA-06512: AT line 9

With COALESCE:

SQL> DECLARE
  2    a NUMBER;
  3  
  4    FUNCTION err RETURN NUMBER IS
  5    BEGIN
  6      RAISE PROGRAM_ERROR;
  7    END err;
  8  BEGIN
  9    a := COALESCE(1, err);
 10  END;
 11  /
PL/SQL PROCEDURE successfully completed

See also[edit]

References[edit]

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm