Null Handling in PL/SQL

Table of Contents

1. Introduction
2. Understanding NULL in PL/SQL
3. Checking for NULL Values
4. Using NVL, NVL2, COALESCE, and NULLIF
   • NULL Handling Function Comparison Table
   • NVL (Null Value Logic)
   • NVL2 (Extended NVL)
   • COALESCE (First Non-NULL Value)
   • NULLIF (Handling Equal Values as NULL)
5. NULL in Joins and Aggregates
   • NULLs in Joins
   • NULLs in Aggregates
6. Performance Considerations
7. NULL in PL/SQL Exception Handling
8. Practical Use Cases
9. Best Practices Summary Table
10. Conclusion

Introduction

Handling NULL values in PL/SQL is a crucial aspect of database programming. Unlike other programming languages where NULL might be treated as zero or an empty string, in SQL and PL/SQL, NULL represents an unknown or missing value. This makes NULL handling tricky, as direct comparisons and operations involving NULL yield unexpected results if not properly handled.

For example, consider a financial application calculating bonuses:

DECLARE
    v_bonus NUMBER := NULL;
    v_total_bonus NUMBER;
BEGIN
    v_total_bonus := v_bonus + 500;  -- This results in NULL
    DBMS_OUTPUT.PUT_LINE('Total Bonus: ' || NVL(v_total_bonus, 0));
END;

This article explores various aspects of NULL handling in PL/SQL, covering different scenarios, best practices, performance considerations, and advanced topics.

Understanding NULL in PL/SQL

In PL/SQL:

• NULL represents an unknown or missing value.
• Any arithmetic operation involving NULL results in NULL.
• Comparing NULL with any value (even NULL itself) using = or != results in UNKNOWN.
• Functions and expressions must explicitly handle NULL values.

Checking for NULL Values

Since = and != do not work with NULLs, PL/SQL provides the IS NULL condition to check for NULL values:

DECLARE
    v_value NUMBER := NULL;
BEGIN
    IF v_value IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('Value is NULL');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Value is NOT NULL');
    END IF;
END;

Using NVL, NVL2, COALESCE, and NULLIF

PL/SQL provides built-in functions to handle NULL values efficiently.

NULL Handling Function Comparison Table

| Function | Description | Example | |———-|————-|———| | NVL(expr, replacement) | Replaces NULL with a specified value | NVL(NULL, 100) → 100 | | NVL2(expr, not_null_value, null_value) | Returns different values based on NULL presence | NVL2(NULL, 200, 500) → 500 | | COALESCE(expr1, expr2, ...) | Returns the first non-NULL expression | COALESCE(NULL, 300, 400) → 300 | | NULLIF(expr1, expr2) | Returns NULL if both values are equal | NULLIF(10, 10) → NULL |

1. NVL (Null Value Logic)

DECLARE
    v_salary NUMBER := NULL;
    v_final_salary NUMBER;
BEGIN
    v_final_salary := NVL(v_salary, 5000);
    DBMS_OUTPUT.PUT_LINE('Final Salary: ' || v_final_salary);
END;

2. NVL2 (Extended NVL)

DECLARE
    v_bonus NUMBER := NULL;
    v_result NUMBER;
BEGIN
    v_result := NVL2(v_bonus, v_bonus * 2, 1000);
    DBMS_OUTPUT.PUT_LINE('Bonus Amount: ' || v_result);
END;

3. COALESCE (First Non-NULL Value)

DECLARE
    v_value1 NUMBER := NULL;
    v_value2 NUMBER := 200;
    v_final_value NUMBER;
BEGIN
    v_final_value := COALESCE(v_value1, v_value2, 500);
    DBMS_OUTPUT.PUT_LINE('Final Value: ' || v_final_value);
END;

4. NULLIF (Handling Equal Values as NULL)

DECLARE
    v_a NUMBER := 10;
    v_b NUMBER := 10;
    v_result NUMBER;
BEGIN
    v_result := NULLIF(v_a, v_b);
    IF v_result IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('Result is NULL');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
    END IF;
END;

NULL in Joins and Aggregates

NULLs in Joins

SELECT * FROM employees e  
LEFT JOIN departments d ON e.dept_id = d.dept_id;

If dept_id contains NULLs, those rows may not match as expected, resulting in missing data.

NULLs in Aggregates

• COUNT(*) includes NULLs, but COUNT(column_name) excludes them.
• SUM(column) ignores NULL values.
• Use COALESCE to handle NULLs in aggregates.

SELECT SUM(NVL(salary, 0)) FROM employees;

Performance Considerations

Handling NULL values effectively can significantly impact query performance. Consider the following aspects:

1. Indexes and NULLs: B-tree indexes generally do not store NULL values, so queries filtering on NULL columns may not benefit from indexing.
2. Avoid NVL in WHERE Clauses: Using NVL(column, default_value) in WHERE conditions can lead to full table scans instead of index usage.
3. COALESCE vs NVL: COALESCE is more flexible and ANSI-compliant but can have a slight performance overhead compared to NVL.
4. Use IS NULL Instead of Comparisons: Avoid column = NULL; always use column IS NULL to prevent unexpected results and improve clarity.

NULL in PL/SQL Exception Handling

NULL values can lead to unexpected logic errors but generally do not raise exceptions.

Practical Use Cases

• Financial Calculations
• Reporting Dashboards
• Data Cleansing Pipelines

Best Practices Summary Table

Conclusion

NULL handling in PL/SQL requires careful attention to avoid logical errors and unexpected results.