Skip to content

PL/SQL Constants 💎

Prerequisites: PL/SQL Variables

Mentor's Note: A constant is a variable that cannot be changed once it is created. It is a "Fixed Rule". If you try to change it, Oracle will throw an error. 💡

🌟 The Scenario: The Tax Rule ⚖️

Imagine you are writing a billing program. - Variable: The "Order_Total" changes for every customer. 🛒 - Constant: The "GST_Tax_Rate" is fixed at 18% for everyone. 🏛️ - The Protection: You make the tax rate a constant so nobody can accidentally change it to 5% or 50% by mistake.

💻 1. Declaration Syntax

You use the CONSTANT keyword. You must assign a value immediately.

DECLARE
   -- Syntax: name CONSTANT type := value;
   c_tax_rate CONSTANT NUMBER(3, 2) := 0.18;
   c_pi       CONSTANT NUMBER       := 3.14159;
BEGIN
   DBMS_OUTPUT.PUT_LINE('Tax Rate is: ' || (c_tax_rate * 100) || '%');

   -- ❌ This would cause a compilation error:
   -- c_tax_rate := 0.05; 
END;
/

🛡️ 2. Why use Constants? (Architect's Note)

  1. Safety: Prevents accidental "data corruption" in your logic.
  2. Readability: It's easier to understand price * c_tax_rate than price * 0.18. If the rate changes next year, you only change it in one place (the declaration).
  3. Optimization: The PL/SQL compiler can sometimes optimize code better when it knows a value will never change.

🎨 Visual Logic: Fixed vs. Fluid

Type Box Analogy Usage
Variable A cardboard box (Open) 📦 Usernames, Balances, Counters
Constant A glass display case (Locked) 💎 Pi, Conversion Rates, Version Numbers

📈 Learning Path