Variables in PLSQL

Variable:

The variable is the name of a reserved memory location. Each variable has a specific data type which determines the range of values and set of operations for that variable.

PL/SQL variables naming rules:

A variable name can’t contain more than 30 characters.
A variable name must start with an ASCII letter followed by any number, underscore (_), or dollar sign ($).
PL/SQL is case-insensitive i.e. var and VAR refer to the same variable.

How to declare a variable in PL/SQL:

We have to declare a PL/SQL variable in the declaration section or a package as a global variable. After declaration PL/SQL allocates memory for the variable and the variable name is used to identify the storage location.

Syntax:

variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]

Where:
variable_name is a valid identifier name.
datatype is a valid PL/SQL datatype.

Initializing Variables in PL/SQL:

When we declare a variable PL/SQL assigns it NULL as the default value. If we want to initialize a variable with a non-NULL value, we can do it during the declaration. We can use any one of the following methods:
1. The DEFAULT keyword
Num1 binary_integer := 0;

2. The assignment operator
siteName varchar2(20) DEFAULT ‘w3spoint’;

Example:

DECLARE
   var1 integer := 20;
   var2 integer := 40;
   var3 integer;
   var4 real;
BEGIN
   var3 := var1 + var2;
   dbms_output.put_line('Value of var3: ' || var3);
   var4 := 50.0/3.0;
   dbms_output.put_line('Value of var4: ' || var4);
END;
/

Output

Value of var3: 60
Value of var4: 16.66666666666666666666666666666666666667

Variable Scope in PL/SQL:

As we discussed in an earlier tutorial PL/SQL allows the nesting of blocks i.e. blocks with blocks. We can divide PL/SQL variables into the following categories based on the nesting structure:
Local variables – Those variables that are declared in an inner block and not accessible to outer blocks are known as local variables.
Global variables – Those variables that are declared in the outer block or a package and accessible to itself and inner blocks are known as global variables.

Example:

DECLARE
   -- Global variables 
   num1 number := 10; 
   num2 number := 20; 
BEGIN 
   dbms_output.put_line('Outer Variable num1: ' || num1);
   dbms_output.put_line('Outer Variable num2: ' || num2);
   DECLARE 
      -- Local variables
      num3 number := 30; 
      num4 number := 40; 
   BEGIN 
      dbms_output.put_line('Outer variable in inner block num1: ' || num1);
      dbms_output.put_line('Outer variable in inner block num2: ' || num2);
      dbms_output.put_line('Inner Variable num3: ' || num3);
      dbms_output.put_line('Inner Variable num4: ' || num4);
   END; 
END;
/

Output:

Outer Variable num1: 10
Outer Variable num2: 20
Outer variable in inner block num1: 10
Outer variable in inner block num2: 20
Inner Variable num3: 30
Inner Variable num4: 40

Alex Martin