How to Declare a Variable in PL/SQL?

Declaring variables in PL/SQL is a fundamental step towards building powerful and efficient database applications. Variables act as placeholders for data which enable us to manipulate and store information within our PL/SQL programs.

Understanding how to declare variables is essential for writing effective and scalable code. In this article, we will learn about how to declare a variable with the help of various methods along with the syntax and examples.

How to Declare Variables in PL/SQL?

When writing PL/SQL code it is important to declare variables properly to store and manipulate data effectively. Variables act as containers for values and enable various operations on the stored data.

In PL/SQL, variables are declared using the DECLARE keyword within a block. The below methods are used to declare a variable in PL/SQL are as follows:

1. Using Declare Variables in PL/SQL
2. Using Initializing Variables in PL/SQL
3. Using Variable Scope in PL/SQL
4. Using Variable Attributes

Let’s understand each method one be one along with the Examples.

1. Using Declare Variables in PL/SQL

Syntax:

DECLARE
   variable_name datatype := initial_value;

In this syntax,

• 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:

Output

Explanation: In the above Query, We have declares a variable named “name” with a size of 20 characters and initializes it with the value GeeksForGeeks then prints the value of the variable using DBMS_OUTPUT.PUT_LINE.

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.

Initializing during declaration

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:

We can also Initialize later in the code.

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

In PL/SQL variable scope can be a local or global. Declaring variable within a block or subprogram are only accessible within that block or subprogram and Declaring variable in the outermost block are accessible to nested blocks and subprograms.

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;

4. Using Variable Attributes

PL/SQL provides %TYPE and %ROWTYPE attribute:

• %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. Declaring a variable using %TYPE to match the data type of a column.

In this example, we have declared a variable salary_var using %TYPE to match the data type of the salary column in the employees table then we have assigned a value to salary_var and displayed the assigned value 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:

Explanation: In the above Query, We have declares a variable salary_var with the same data type as the salary column in the employees table, assigns a value of 70000 to it, and then prints the assigned salary using DBMS_OUTPUT.PUT_LINE.

2. Declaring a record variable using %ROWTYPE to match the structure of the employees table.

In this example, We have declared a record variable employee_record using %ROWTYPE to match the structure of the employees table and fetched the data from the employees table into the employee_record variable using a SELECT INTO statement then we have displayed the retrieved data from the employee_record 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:

Explanation: In the above Query, We have declares a record variable employee_record that matches the structure of the employees table. It then fetches the data for the employee with employee_id 2 into the employee_record variable using a SELECT INTO statement and prints the retrieved data using DBMS_OUTPUT.PUT_LINE.

Conclusion

Overall, After reading Whole article now you have good understanding about declare variable in PL/SQL. We have seen various methods like Using Declare Variables in PL/SQL, Using Initializing Variables in PL/SQL, Using Variable Scope in PL/SQL and Using Variable Attributes. Now you can easily dclare variable with the help of above methods easily.