PL/SQL Variables in Oracle PL/SQL Tutorials

In this article, we will discuss PL/SQL Variables in Oracle PL/SQL tutorial, and you will learn about PL/SQL variables and how to use them effectively.

If you missed the previous article of data types in PL/SQL, then please click here.

Let’s start the tour…

A variable is a title assigned to a storage area that a program can use. Each variable in PL/SQL has a specific data type, which determines the size and layout of the variable’s memory.

NOTE: A variable should not exceed 30 characters. Its letter optionally followed by more letters, dollar signs, numerals, underscore etc.

The variables must be declared in the declaration section or in a package as a global variable. After declaring the variables, PL/SQL allocates memory for the variable’s value, and the storage location identifies by the variable name.

The syntax for a PL/SQL Variables declaration is :

variable_name datatype [NOT NULL] [:= Default initial_values];

Here, First, specify the name of the variable. The name of the variable should be as descriptive as possible, e.g., 2_total_sales.Second, choose an appropriate data type for the variable, depending on the kind of value which you want to store, for example, number, character, Boolean, and DateTime. PL/SQL allows us to set a default value for a variable at the declaration time. To assign a default value to a variable, we use the assignment operator (:=) or the DEFAULT keyword. or you can also initiate the value of the variable.

Like every programming language, the variable must follow some naming rules. So, here in PL/SQL variables, some rules are there:

• The variable_name should not exceed 30 characters.
• The name of the variable must begin with ASCII letter. The PL/SQL is not case sensitive so, it can be either lowercase or uppercase. For example, v_data and V_DATA both refer to the same variables.
• We should make sure that our variables are easy to read and understand, after the first character, it may be any number, underscore (_) or a dollar sign ($).
• The NOT NULL is an optional specification on the variable.

Whenever we declare a variable, PL/SQL assigns it a default value of NULL. If we want to initialize a variable with a value other than the NULL value, then we can do so during the declaration of a variable, using either of the following −

• The DEFAULT keyword
• The assignment operator

For example −

counter binary_integer := 0;  
greetings varchar2(20) DEFAULT 'Hello Lucine';    

Here, we can also specify NOT NULL constraint to avoid NULL value. If we specify the NOT NULL constraint, you must assign an initial value for that variable.

It is a good programming practice to initializing the variables properly; otherwise, sometimes programs would produce unexpected results.

Example of initializing variable:

DECLARE 
 var_salary number(8); 
 var_emp_id number(6) = 226; 
BEGIN
 SELECT salary 
 INTO var_salary 
 FROM employee 
 WHERE emp_id = var_emp_id; 
 dbms_output.put_line(var_salary); 
 dbms_output.put_line('The employee ' 
  	|| var_emp_id || ' has  salary  ' || var_salary); 
END; 
/ 

NOTE: backward slash ‘/’ in the above record indicates to execute the above PL/SQL Block.

PL/SQL allows the nesting of blocks. A program block could contain another inner block. If we declare a variable within an inner block, it is not accessible to an outer block. There are two types of variable scope in PL/SQL:

• Local variables – These are declared in an inner block and cannot be referenced by outside Blocks.
• Global variables – These are declared in an outer block and can be referenced by its itself and by its inner blocks.

Example of Local and Global variables:

DECLARE  
 -- Global variables   
   vnum1 number := 95;   
   vnum2 number := 75;   
BEGIN   
   dbms_output.put_line('The outer Variable num1: ' || vnum1);  
   dbms_output.put_line('The outer Variable num2: ' || vnum2);  
   DECLARE   
      -- Local variables  
      num1 number := 195;   
      num2 number := 185;   
   BEGIN   
      dbms_output.put_line('Inner Variable num1: ' || num1);  
      dbms_output.put_line('Inner Variable num2: ' || num2);  
   END;   
END;  
/ 

After the execution, this will produce the following result:

Outer Variable vnum1: 95 
Outer Variable vnum2: 75 
Inner Variable num1: 200 
Inner Variable num2: 195  

PL/SQL procedure successfully completed. 

This article is all about the variables their uses and etc. I hope this article is as much as easy for anyone. In the future article we will discuss the constant, Literals, operators and many more for that, please stay with us.

Thank you 🙂