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…

What’s inside:
Declaration of Variables
The Naming Rules
Initializing Variables
Variable Scope
FAQs:

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.

How to declare variables in Oracle PL/SQL

pl/sql variables

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.

Naming rules for PL/SQL variables

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.

Initializing Variables in PL/SQL

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.

Variable Scope in PL/SQL

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. 

FAQs:

1. What is constrained declaration?

Answer: A data type with size, scale or precision limit is called a constrained declaration. The constrained declaration needs less memory than an unconstrained declaration.

2. SQL does not provide the programming techniques of conditional checking. True or False?

Answer: True.

3. What is BLOB datatype?

Answer: A BLOB data type is a varying length binary string that is used to store two gigabytes of memory. Length should be specified in Bytes for BLOB

4. What is NULL value in oracle?

Answer: the NULL value represents missing or unknown data. This uses as a place holder or represented, it in as default entry to indicate that there is no actual data present.

5. What are WITH CHECK OPTIONS?

Answer: The WITH CHECK option clause specifies the verify level to be done in DML statements. It is used to prevent changes to a view that would produce results that are not included in the subquery.


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 🙂

25 thoughts on “PL/SQL Variables in Oracle PL/SQL Tutorials

  1. Good day! Would you mind if I share your blog with my twitter group?
    There’s a lot of folks that I think would really enjoy your content.
    Please let me know. Cheers

  2. After looking into a handful of the blog articles on your site, I truly like your way of writing a blog.
    I saved as a favorite it to my bookmark webpage list and will be checking back in the near
    future. Please check out my web site as well and tell
    me how you feel.

  3. Hi there i am kavin, its my first time to commenting
    anywhere, when i read this article i thought i could also
    make comment due to this good piece of writing.

  4. A person necessarily help to make severely posts I’d state.

    This is the very first time I frequented your website page and so far?
    I surprised with the research you made to create this actual submit amazing.
    Wonderful activity!

  5. Great blog right here! Also your site rather a lot up very fast!
    What web host are you the usage of? Can I get your affiliate link to your host?

    I wish my site loaded up as fast as yours lol

  6. I simply could not depart your site before suggesting
    that I actually loved the standard info an individual supply for
    your visitors? Is going to be again incessantly to check out new posts

  7. Thank you for the good writeup. It in fact was a amusement account
    it. Look advanced to far added agreeable from you! By the way, how can we communicate?

  8. Your style is really unique in comparison to other folks I have read stuff from.
    Many thanks for posting when you have the opportunity, Guess I’ll just book mark this site.

Leave a Reply

Your email address will not be published. Required fields are marked *