Variables, Constants and Data Type

📖 Theory

The Dynamic College Registry

Imagine managing the CampusLib application during peak college admissions. A student's residential address or phone number might change multiple times across semesters, but their University Roll Number remains permanently frozen from day one. In database programming, you cannot hardcode values because some data flows like water, while other data is completely set in stone. To handle this gracefully in PL/SQL, we must master variables, constants, and data types.

📖 Theory

The Labelled Storage Boxes

Think of a variable like a plastic tiffin box labeled 'Lunch'. Today it holds maggi; tomorrow you can wash it and fill it with fried rice. The box label stays the same, but the internal content changes. A constant is like a sealed, stamped university exam envelope, once packed at the central office, nobody can alter what is inside without breaking the regulations. A Data Type is simply the physical shape of the box: a round thermos can only hold liquid items (like DATE or NUMBER), while a flat folder holds sheets of text (like VARCHAR2).

📖 Theory

Allocating Smart Memory

Formally, a Variable is a named memory location whose value can fluctuate during program execution, whereas a Constant uses the CONSTANT keyword to ensure its initialized value remains read-only throughout the transaction block. Beyond basic scalar types, Oracle introduces a magnificent shorthand tool: the %TYPE attribute. By writing v_name members.name%TYPE;, you tell Oracle to copy the exact data type and length configuration from that table column automatically, preventing code crashes if a system administrator expands the database field size later.

📊 At a glance

Core structural properties of data storage elements in PL/SQL

⌨️ Practical

Declaring and Testing Memory Anchors

-- A sample block demonstrating safe variable and constant declarations
DECLARE
  -- Constant: Max books allowed must be initialized immediately
  MAX_BOOKS_ALLOWED CONSTANT NUMBER := 5;
  
  -- Smart Variable: Automatically inherits data type from CampusLib schema
  v_book_title      books.title%TYPE;
  
  -- Standard Variable with a default initialization value
  v_current_fine    NUMBER(5,2) := 0.00;
BEGIN
  -- Assigning a value to our smart variable using the proper operator
  v_book_title := 'Mastering Oracle';
  v_current_fine := 45.50;
  
  DBMS_OUTPUT.PUT_LINE('Book Title: ' || v_book_title);
  DBMS_OUTPUT.PUT_LINE('Fine Amount: Rs. ' || v_current_fine);
  DBMS_OUTPUT.PUT_LINE('Max Limit: ' || MAX_BOOKS_ALLOWED);
END;
/

🪜 Follow along

How Oracle Processes %TYPE at Runtime

1. 1. Schema Check The Oracle compiler reads the declaration section and locates the target table (e.g., books) in the database dictionary.
2. 2. Structure Mirroring It extracts the data type and precise byte size of the specified column (e.g., VARCHAR2(100)) on the fly.
3. 3. Memory Allocation The compiler allocates an identical internal storage buffer for your local PL/SQL variable configuration.
4. 4. Decoupled Execution The variable runs safely even if database administrators alter column sizes later, eliminating manual code updates.

🧩 Quiz

What happens if you declare a constant using the CONSTANT keyword in the DECLARE section but forget to assign an initial value to it right there?

1. Oracle automatically assigns a default value of 0 or an empty string.
2. Oracle allows you to declare it blank and assign its value later inside the BEGIN block.
3. Oracle throws a compilation error because constants must be initialized at the exact moment of declaration.
4. The block compiles successfully but converts the constant into a standard variable automatically.

⚠️ Watch out

The Silent NULL Arithmetic Trap

If you declare a standard numeric variable like v_total_fine NUMBER; and do not explicitly initialize it with := 0, Oracle sets its default structural state to NULL. In SQL and PL/SQL, any mathematical operation performed on a NULL value immediately results in NULL (e.g., 50 + NULL = NULL). If you try to add a late fee to an uninitialized variable, your total calculation disappears into a blank space! Always initialize numeric counters to 0.

💭 Think first

The Dynamic Columns Schema Test

Mental Challenge: If you change a table's column definition from VARCHAR2(50) to VARCHAR2(100) in your database schema, do you need to manually find, rewrite, and recompile a PL/SQL block that references that column via %TYPE? Think it through before tapping.

✅ Summary

Key takeaways

• Variables hold fluctuating data buffers that can be overwritten anytime within the executable block.
• Constants use the CONSTANT keyword and must be assigned an immutable value immediately at declaration.
• Scalar data types cover basic individual values like text strings, numbers, dates, and truth conditions.
• The %TYPE attribute dynamically binds local variables to actual database schema columns.
• Uninitialized variables default to NULL, which can silently wipe out mathematical calculations if left unchecked.
• Memory hook: Variables are loose pages, constants are hardcovers, and %TYPE is a live structural mirror!