Variable in PL/SQL

PL/SQL provides variables to store, process and manipulate data within programs. Variables act as placeholders for values used during program execution and help perform calculations efficiently.

• They help perform calculations and data manipulation.
• Variables can be initialized while declaration or later in the program.
• PL/SQL supports attributes like %TYPE and %ROWTYPE for flexible declarations.
• Proper variable declaration improves code readability and efficiency.

Methods for Declaring Variables

The below methods are used to declare a variable in PL/SQL are as follows:

1. Using Declare Variables in PL/SQL

In PL/SQL, variables are declared using the DECLARE keyword. A variable stores data and can also be given an initial value using the := operator.

Syntax:

DECLARE
   variable_name datatype := initial_value;

• variable_name: It is the name of the variable.
• datatype: It is the data type of the variable.
• := initial_value: It is an optional assignment of an initial value to the variable.

Example:

DECLARE
   name VARCHAR2(20) := 'GeeksForGeeks';
BEGIN
   DBMS_OUTPUT.PUT_LINE(name);
END;

Output:

• A variable name is declared with a size of 20 characters.
• It is initialized with the value GeeksForGeeks.
• DBMS_OUTPUT.PUT_LINE is used to print the value of the variable.

2. Using Initializing Variables in PL/SQL

In this method Variables can be initialized in two ways either during declaration or later in the code.

1. Initializing during declaration

Variables can be assigned values when declared, as shown below:

Syntax:

DECLARE
  my_variable NUMBER := value;
BEGIN
  -- PL/SQL code 
END;

Example:

DECLARE
   name VARCHAR2(20) := 'GeeksForGeeks';
BEGIN
   DBMS_OUTPUT.PUT_LINE(name);
END;

Output:

2. Initialization After Declaration

You can also assign a value to a variable later in the code using the := operator.

Syntax:

DECLARE
  my_variable NUMBER;
BEGIN
  my_variable := value;
END;

Example:

DECLARE
   num1 NUMBER;
   num2 NUMBER;
   result NUMBER;
BEGIN
   num1 := 5;
   num2 := 3;
   result := num1 + num2;
   DBMS_OUTPUT.PUT_LINE('Sum: ' || result);
END;

Output:

3. Using Variable Scope in PL/SQL

Variable scope determines where a variable can be accessed within a program. In PL/SQL, variable scope can be either local or global.

• Local Variables: Declared within a block or subprogram, accessible only inside that block or subprogram.
• Global Variables: Declared in the outermost block and accessible by nested blocks.

Example:

DECLARE
  global_var NUMBER; -- global variable
BEGIN
  -- PL/SQL code using global_var
  DECLARE
    local_var NUMBER; -- local variable
  BEGIN
    -- PL/SQL code using local_var and global_var
  END;
  -- Here you can't access local_var 
END;

• global_var can be accessed throughout the entire program.
• local_var can only be accessed inside the inner block.

4. Using Variable Attributes (%TYPE and %ROWTYPE)

PL/SQL provides two powerful attributes, %TYPE and %ROWTYPE, which allow variables to inherit data types from existing columns or entire rows.

• %TYPE: It defines a variable with the same data type as another variable or column.
• %ROWTYPE: It defines a record with the same structure as a table or cursor.

Example: The below example is demonstrating the use of %TYPE and %ROWTYPE attribute

Create a employees table and Insert some records into a employees table

-- Creating a employees table
CREATE TABLE employees (
  employee_id NUMBER PRIMARY KEY,
  first_name VARCHAR2(50),
  last_name VARCHAR2(50),
  salary NUMBER
);

-- Inserting some records
INSERT INTO employees VALUES (1, 'John', 'Doe', 50000);
INSERT INTO employees VALUES (2, 'Jane', 'Smith', 60000);
INSERT INTO employees VALUES (3, 'Bob', 'Johnson', 55000);

1. Using %TYPE Attribute

In this example, salary_var is declared using %TYPE to use the same data type as the salary column in the employees table. A value is assigned to it and displayed using DBMS_OUTPUT.PUT_LINE.

DECLARE
  salary_var employees.salary%TYPE;
BEGIN
  -- Assign a value to the variable
  salary_var := 70000;

  -- Display the assigned value
  DBMS_OUTPUT.PUT_LINE('Assigned Salary: ' || salary_var);
END;

Output:

• A variable salary_var is declared using %TYPE.
• It uses the same data type as the salary column of the employees table.
• The value 70000 is assigned to the variable.
• DBMS_OUTPUT.PUT_LINE is used to display the salary value.

2. Using %ROWTYPE Attribute

In this example, a record variable employee_record is declared using %ROWTYPE to match the employees table structure. Data is fetched into it using SELECT INTO and displayed using DBMS_OUTPUT.PUT_LINE.

DECLARE
  employee_record employees%ROWTYPE;
BEGIN
  -- Fetch data from the table into the record variable
  SELECT * INTO employee_record FROM employees WHERE employee_id = 2;

  -- Display the retrieved data
  DBMS_OUTPUT.PUT_LINE('Employee ID: ' || employee_record.employee_id);
  DBMS_OUTPUT.PUT_LINE('First Name: ' || employee_record.first_name);
  DBMS_OUTPUT.PUT_LINE('Last Name: ' || employee_record.last_name);
  DBMS_OUTPUT.PUT_LINE('Salary: ' || employee_record.salary);
END;

Output:

• A record variable employee_record is declared based on the structure of the employees table.
• The SELECT INTO statement fetches data of the employee whose employee_id is 2.
• The fetched data is stored in the employee_record variable.
• DBMS_OUTPUT.PUT_LINE is used to display the retrieved employee details.