Variables Declaration and Initialization in PL/SQL

Variables are essential components of any programming language, and PL/SQL is no exception. They serve as containers in the computer's memory that temporarily hold data. In this comprehensive guide, we will dive deep into how to declare, initialize, and use variables in PL/SQL, complete with code examples, syntax explanations, and best practices.

---

What is a Variable in PL/SQL?

A variable in PL/SQL:

• Holds data temporarily in memory.

• Has a name (identifier) which is defined by the user.

• Has a data type that determines the kind of value it can hold (e.g., number, string, date).

• Occupies memory as allocated by the compiler based on its data type.

---

Structure of a PL/SQL Block

Before we dive into variables, let's look at the basic structure of a PL/SQL block:

DECLARE
   -- Declaration section
BEGIN
   -- Execution section
EXCEPTION
   -- Exception handling (optional)
END;

All variables must be declared in the DECLARE section before being used.

---

Declaring a Variable in PL/SQL

Syntax:

variable_name datatype [:= initial_value];

Example 1: Declaring a Variable and Assigning Value Later

SET SERVEROUTPUT ON;
DECLARE
   val_var NUMBER;  -- Declaration
BEGIN
   test_var := 32;   -- Initialization in execution section
   DBMS_OUTPUT.PUT_LINE('Value is: ' || val_var);
END;

Explanation:

• We declared val_var as a variable of type NUMBER.

• We assigned it the value 32 using the := assignment operator.

• The DBMS_OUTPUT.PUT_LINE prints the value on the screen.

---

Assignment Operator in PL/SQL

Unlike other programming languages that use = for assignment, PL/SQL uses :=.

variable_name := value;

Note: There must be no space between : and =.

---

Initializing Variables

Where Can We Initialize?

1. During Declaration

2. In Execution Section

Example 2: Initialization During Declaration

DECLARE
   site VARCHAR2(30) := 'Barcelona';
BEGIN
   DBMS_OUTPUT.PUT_LINE('Website: ' || site);
END;

This is a more concise way of initializing variables.

---

Fetching Data from Table into a Variable

One powerful feature of PL/SQL is the ability to fetch values directly from a database table and store them into variables.

Example 3: Fetching a Single Column Value

DECLARE
   var_salary NUMBER(8,2);
BEGIN
   SELECT salary INTO var_salary FROM employees WHERE employee_id = 100;
   DBMS_OUTPUT.PUT_LINE('Salary: ' || var_salary);
END;

Explanation:

• The SELECT INTO clause fetches the salary from the employees table.

• The value is stored in the var_salary variable.

• This technique is useful for working with data directly from the database.

---

Fetching Multiple Columns into Multiple Variables

You can fetch more than one value at a time into multiple variables.

Example 4: Fetching Two Column Values

DECLARE
   var_salary NUMBER(8,2);
   var_name VARCHAR2(20);
BEGIN
   SELECT salary, first_name
   INTO var_salary, var_name
   FROM employees
   WHERE employee_id = 100;

   DBMS_OUTPUT.PUT_LINE('Name: ' || var_name);
   DBMS_OUTPUT.PUT_LINE('Salary: ' || var_salary);
END;

Important Notes:

• The order of columns in SELECT must match the order of variables in INTO.

• Each column value will be assigned to the corresponding variable by position.

---

Best Practices for Using Variables in PL/SQL

1. Use meaningful names for variables. E.g., total_salary instead of x.

2. Initialize variables immediately if possible.

3. Match data types of variables with column data types to avoid conversion issues.

4. Use %TYPE and %ROWTYPE for dynamic and maintainable code (explained below).

---

Using %TYPE for Dynamic Data Typing

If you want your variable to always have the same data type as a particular column in a table:

DECLARE
   var_salary employees.salary%TYPE;
BEGIN
   SELECT salary INTO var_salary FROM employees WHERE employee_id = 100;
   DBMS_OUTPUT.PUT_LINE('Salary: ' || var_salary);
END;

This way, even if the datatype of salary column changes, your variable stays in sync.

---

Using %ROWTYPE to Declare Record Variables

If you want to store an entire row from a table:

DECLARE
   emp_record employees%ROWTYPE;
BEGIN
   SELECT * INTO emp_record FROM employees WHERE employee_id = 100;
   DBMS_OUTPUT.PUT_LINE('Name: ' || emp_record.first_name);
   DBMS_OUTPUT.PUT_LINE('Salary: ' || emp_record.salary);
END;

This creates a record with fields matching all columns in the employees table.

---

If you found this guide helpful, please share it with your peers and community. Happy coding!