Variables in PL/SQL

By Manish Sharma

Proper way of Declaring and Initializing variables in PL/SQL.

Variables are place holders in the computer’s main memory which hold some data. Every variable has a name which is user defined, also a data type that defines the nature of data a variable can hold and the total amount of space they can have in main memory along with some value. Like every other programming language in PL/SQL also we first need to declare a variable before using it.

Variable Declaration

All the variable declaration must be done in Declare Section of the PL/SQL block. As soon as you declare a variable, the compiler will allocate the memory according to the data type to that variable. Though you can assign value to the variable either in declare section or in execution section of your PL/SQL block but the declaration must be done in declare section.

Example 1

Here is a simple program to understand this.

  Test_var1 NUMBER; -- Declaring variable Test_var
  Test_var1:= 10;
This is a very simple program in which I first declared a variable by the name of test_var1 which has data type number in declaration section and later in execution section I initialized it and assigned a numeric value 10 and then using DBMS_OUTPUT statement I displayed the value of this variable.

Assignment Operator (:=)

If you have noticed in the above program that unlike conventional assignment operators in other programming language which is Equal to (=) operator, here we used colon ( : ) with equal to (=) operator for assigning the value to the variable.

Yes in PL/SQL the combo of colon and equal to operator (:=) works as assignment operator which is very different from the other programming languages.

Note: There is no space between colon and equal to operator.

Variable Initialization

Two main questions which I am going to address in this section are

  1. Where can we initialize the variables?
  2. Different ways of initializing variables in PL/SQL program.

So let’s start with the first question which is where can we initialize the variables in PL/SQL program?

Though it’s mandatory to declare all the variables of your programs in declaration section of your PL/SQL block but initializing them and assigning them some value in execution section is not mandatory. This means that you can initialize or say assign values to your variables anywhere in your program.

You can initialize a variable in declaration section while creating it or you can initialize the variable in execution section as we did in the example 1. This is the answer to the first question which I have given you in as simple a language as possible.

Now the second question is what are the different ways of initializing a variable in PL/SQL program?

Variable initialization means assigning some value to the variable which you previously declared. There are two ways of assigning value to the variable.

  1. First is the direct way of giving value to the variable. We have seen this demonstrated in the previous example where we assign integer 10 to the variable test_var1.
  2. Second way is by fetching value from the column of a row of a table and assigning that value to the variable.
So let’s see some examples and try to understand the above concept.

Example 2.

Declaring variable in declaration section and assigning value by direct way.

    var_test1 VARCHAR2(30) := 'RebellionRider'; --Declare & initialize the variable at same time
A very simple program where I declared a variable by the name of var_test with data type VARCHAR2 and data width 30 also I initialized this variable and assigned a string ‘RebellionRider’ right after declaring it in the declaration section.

That’s it in this section of PL/SQL. Hope you liked it and found it helpful. Kindly please share it on your social networking and help me reach out to more people. Thanks & have a great day guys!

SQL Script and Presentation used

You can DOWNLOAD SQL script and presentation used in the Video and in this article.

NO SQL Script used in Video and in this article

  • Manish Sharma Oracle Rebellion Rider
  • Manish Sharma Oracle certified SQL expert
  • Manish Sharma oracle certified associate
  • Manish Sharma oracle certified professional
  • View Manish Sharma's profile on LinkedIn
  •          View Manish Sharma's profile on LinkedIn