PL/SQL Variables 📦
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.