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.

Here, we will explore various methods of declaring variables in PL/SQL, including syntax, examples, and practical use cases. We will cover variable initialization, scope, and the use of variable attributes like %TYPE and %ROWTYPE.

How to Declare PL/SQL Variables?

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.

Variables in PL/SQL are declared using the DECLARE keyword within an anonymous block or a named program unit such as a procedure, function, or package.

Common Methods for Declaring Variables in PL/SQL

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

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

1. Using Declare Variables in PL/SQL

To declare a variable in PL/SQL, use the DECLARE keyword followed by the variable name and its data type. Optionally, you can also assign an initial value to the variable using the ':=' operator.

Syntax:

DECLARE
   variable_name datatype := initial_value;

here,

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

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.

a. 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:

b. 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;

Explanation: The global_var can be accessed throughout the entire program, while the local_var is only accessible within 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, 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. Using %ROWTYPE Attribute

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.

Important Points About PL/SQL Variables

• All variables must be declared before they can be used in a PL/SQL block. Declaration involves specifying the variable's name and data type.
• You can declare variables as constants using the CONSTANT keyword, ensuring their value cannot be changed once assigned.
• Use %TYPE whenever possible to avoid data type mismatches and to ensure compatibility with changes in database schema.
• Avoid using Oracle reserved keywords as variable names.

jaimin78

Follow

Improve

Article Tags :

• Databases
• PL/SQL
• PL/SQL Query