Variables in PL/SQL

  • A variable is a meaningful name that provides facility for programmer to store data temporary during execution of code.
  • It helps to manipulate data in PL/SQL.
  • Each variable in the PL/SQL has a specific data type that defines the size and layout of the variable's memory.
  • A variable should not be more than 30 characters and optionally followed by more letters like dollar signs, underscore etc.
Syntax:
variable_name[CONSTANT] datatype [NOT NULL] [:=default initial _value]

Initializing Variables in PL/SQL

On declaration PL/SQL defines a default value as NULL. If a user wishes to initialize a variable with a value other than NULL, he can do so by using one of the following methods.

1. DEFAULT keyword

Example: Initialize variables using DEFAULT keyword.

counter binary_integer := 0;  
greetings varchar2(20) DEFAULT 'Hello TutorialRide.com';


2. Assignment operator

Example: Initialize variables using DEFAULT keyword.

DECLARE  
   a integer := 40;  
   b integer := 10;  
   c integer;  
   f real;  
BEGIN  
   c := a + b;  
   dbms_output.put_line('Value of c: ' || c);  
   f := 25.0/3.0;  
   dbms_output.put_line('Value of f: ' || f);  
END;  
/


Note: The backward slash '/' in the above program indicates to execute the above PL/SQL Block.

Output:
Value of c: 50
Value of f: 08.3333

Scope of variable in PL/SQL

PL/SQL allows nesting of blocks.

There are two types of variable scope.

1. Local variable: Local variables are the inner variable and not accessible to outer block.

2. Global variable: Global variable are declared in outermost box.

DECLARE  
-- Global variables   
   num1 number := 45;   
   num2 number := 20;   
BEGIN   
   dbms_output.put_line('Outer Variable num1: ' || num1);  
   dbms_output.put_line('Outer Variable num2: ' || num2);  
   DECLARE   
      -- Local variables  
      num1 number := 55;   
      num2 number := 65;   
   BEGIN   
      dbms_output.put_line('Inner Variable num1: ' || num1);  
      dbms_output.put_line('Inner Variable num2: ' || num2);  
   END;   
END;  
/


Output:
Outer Variable num1: 45
Outer Variable num2: 20  
Inner Variable num1: 55
Inner Variable num2: 65
▲