Understanding Constants in PL/SQL

🧠 Introduction

In any programming language, there are scenarios where you want certain values to remain unchanged throughout the execution of a program. In PL/SQL (Oracle's Procedural Language extension to SQL), such immutable values are defined using constants.

Constants not only improve the readability and maintainability of your code but also make it less error-prone by ensuring that fixed values, such as Pi or tax rates, do not get altered accidentally during execution.

This article dives deep into PL/SQL constants—what they are, how to declare them, their attributes, and how to use them effectively with practical examples and syntax explanations.

---

🔑 What Are Constants in PL/SQL?

A constant in PL/SQL is a user-defined identifier whose value is assigned at the time of declaration and cannot be changed later in the program. Constants are declared in the declaration section of a PL/SQL block and follow a specific syntax.

Think of constants like labels you attach to values that should never change—like mathematical values, configuration settings, or business rules.

---

🧾 Syntax of PL/SQL Constant Declaration

constant_name CONSTANT datatype [NOT NULL] := value;

🔍 Syntax Breakdown:

• constant_name: A valid identifier for your constant.

• CONSTANT: A mandatory keyword that tells PL/SQL this is a constant.

• datatype: The data type of the constant (e.g., NUMBER, VARCHAR2, DATE).

• NOT NULL (optional): Ensures the constant cannot be assigned a NULL value.

• := value: Assigns a value to the constant. This is mandatory.

You can also use the DEFAULT keyword instead of := to assign a value.

---

✅ Basic Example

SET SERVEROUTPUT ON;

DECLARE
    v_pi CONSTANT NUMBER(7,6) := 3.141592;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Value of PI: ' || v_pi);
END;
/

Output:

Value of PI: 3.141592

The value 3.141592 assigned to v_pi cannot be changed anywhere else in the block.

---

📘 Using the DEFAULT Keyword

Instead of using the assignment operator (:=), PL/SQL allows the use of the DEFAULT keyword.

DECLARE
    v_pi CONSTANT NUMBER(7,6) DEFAULT 3.1415926;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Value of PI using DEFAULT: ' || v_pi);
END;
/

Both := and DEFAULT do the same job—initializing the constant—but some developers prefer DEFAULT for readability, especially when initializing multiple variables/constants.

---

🚫 NOT NULL Constraint with Constants

Even though constants are implicitly NOT NULL, PL/SQL allows you to explicitly declare this constraint:

DECLARE
    v_pi CONSTANT NUMBER(7,6) NOT NULL DEFAULT 3.1415926;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Value of PI with NOT NULL: ' || v_pi);
END;
/

Although adding NOT NULL is redundant for constants, it is often included for clarity and consistency, especially when declaring a mix of variables and constants.

---

🧪 Practical Examples

🔢 Example 1: Sales Tax Calculation

DECLARE
    SALES_TAX_RATE CONSTANT NUMBER(5,2) := 18.00;
    item_price      NUMBER(10,2) := 1000;
    tax_amount      NUMBER(10,2);
BEGIN
    tax_amount := item_price * SALES_TAX_RATE / 100;
    DBMS_OUTPUT.PUT_LINE('Tax: ' || tax_amount);
    DBMS_OUTPUT.PUT_LINE('Total Price: ' || (item_price + tax_amount));
END;
/

📆 Example 2: Business Rules

DECLARE
    MAX_EMPLOYEES CONSTANT NUMBER := 500;
    current_employees NUMBER := 490;
BEGIN
    IF current_employees >= MAX_EMPLOYEES THEN
        DBMS_OUTPUT.PUT_LINE('Cannot hire more employees.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Hiring allowed.');
    END IF;
END;
/

📝 Example 3: String Constant

DECLARE
    COMPANY_NAME CONSTANT VARCHAR2(50) := 'Tech Solutions Pvt Ltd';
BEGIN
    DBMS_OUTPUT.PUT_LINE('Welcome to ' || COMPANY_NAME);
END;
/

---

⚠️ Common Mistakes to Avoid

---

🔚 Conclusion

Constants in PL/SQL are a powerful tool to ensure the integrity and consistency of values that should remain unchanged throughout the lifecycle of your program. By mastering the use of constants:

• You improve code readability

• You reduce the risk of errors

• You adhere to best programming practices

---

Feel free to comment below or share this post if you found it useful. Happy coding! 😊

---