Skip to content

PL/SQL Variables 📦

Prerequisites: PL/SQL Anonymous Block

Mentor's Note: A variable is just a named storage location in memory. Think of it as a Labeled Box where you can keep a value and change it later while your program is running. 💡

🌟 The Scenario: The Storage Room 🏢

Imagine you are managing a warehouse. - You have many boxes. - You put a label on a box: "Daily_Income". - Throughout the day, you add money to the box. The value inside changes, but the label stays the same.

💻 1. Declaring Variables

Variables must be declared in the DECLARE section.

DECLARE
   v_name    VARCHAR2(50);
   v_age     NUMBER(3) := 25; -- Declaration with initial value
   v_salary  NUMBER(10, 2) NOT NULL := 5000; -- Cannot be NULL
BEGIN
   NULL;
END;

🗝️ Naming Convention:

We usually start variables with v_ (e.g., v_salary) to distinguish them from database columns.

💻 2. Assignment (Changing the Value)

In PL/SQL, we use := for assignment.

DECLARE
   v_total NUMBER;
BEGIN
   v_total := 10 + 20; -- Assign math result
   DBMS_OUTPUT.PUT_LINE('The total is: ' || v_total);
END;

💻 3. Dynamic Assignment (SELECT INTO) 📥

This is the most common way to get data from a table into your variable.

DECLARE
   v_emp_name VARCHAR2(100);
BEGIN
   -- 🚀 Action: Pull name from table into variable
   SELECT first_name INTO v_emp_name 
   FROM employees 
   WHERE employee_id = 101;

   DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_emp_name);
END;

🏗️ Architect's Note: Variable Scope 🛡️

Variables exist only within the block where they are declared. - Local: If you declare it inside a nested block, the outside block cannot see it. - Global: If you declare it in the main block, all inner blocks can use it. - Architect's Tip: Keep your variable scope as "narrow" as possible. This prevents bugs where one part of a program accidentally changes data needed by another part.

📈 Learning Path