Introduction to PLSQL Variable

PL/ SQL Variable is the storage space location place in the memory which helps you to store the value of a particular type. We can store any kind of values inside the variable such as numbers, strings, Boolean or characters. The variable helps you to assign the name to a particular location in the memory which you can use to store and access the value in your program.

We can even change the value of the variable in the later program after declaring and initializing it. We can even assign the value of one variable to the other variable inside our program. In this article, we will study about the variables in PL/ SQL, how to declare them, initialize them and modify and access its value along with the help of certain examples.

Syntax of PLSQL Variable

Before you go for using your variable inside the program it is necessary that you declare its name and the constraints related to it in the declaration block of your PL/ SQL program. In order to declare your variable, you can make the use of following syntax –

Name of variable datatype [NOT NULL] [: = starting initial value];

In the above syntax the terminologies used are described below –

• Name of variable: This is the variable name that you wish to give to your variable using which you can access it further in your program to assign or manipulate or retrieve its value in the PL/ SQL program. It is necessary to provide the meaningful names to the variables used in PL/ SQL.  The datatype of the variable can be assigned depending on what type of value will be stored in the variable such as character, Boolean, date time or number.
• The scope of variables is either local or global depending on where we have declared it and where we will be able to access its value. If the scope of variable is going to be local, we declare the name of variable preceding with l_ and if the scope is going to be global then the variable should have the its name preceding with g_ .

Examples of PLSQL Variable

In the below program we are trying to declare three local variables with names as l_joining_date, l_retrirement_date, l_duty_period in the declaration part of PL/SQL program.

DECLARE
l_joining_date DATE;
l_retirement_date DATE;
l_duty_period NUMBER (10,0);
BEGIN
NULL;
END;

The output of the above code is as shown in the below image –

Assigning the Default Value

We can also set the initial value to a variable while declaring it in the declaration block by using the DEFAULT keyword or even with the help of assignment operator i.e.: = operator. Let us consider the following example where we are trying to initialize a variable named gadget with mobile phone string.

DECLARE
l_gadget VARCHAR2( 100 ) := 'Mobile Phone';
BEGIN
NULL;
END;

The execution of above program gives out the following output –

The program shown above will work in the same way when we will write it in the below format –

DECLARE
l_gadget VARCHAR2( 100 ) DEFAULT 'Mobile Phone';
BEGIN
NULL;
END;

The output of the above program is also same giving the result as below –

Just what change we did was instead of using the assignment operator :=, we made the use of DEFAULT keyword.

Applying NOT NULL Constraint

It is optional to give your variable constraint of NOT NULL. But in case if you have assigned this constraint then you cannot store NULL value in your variable because it will throw out the error. Also remember that the blank string is treated as NULL value in PL/ SQL. Hence, in case if you have declared a variable with NOT NULL constraint of datatype varchar or string and you are trying to assign blank value to it, program will throw an error while executing it.

Let us consider the same scenario where we will try to assign the zero length string value to a variable named l_order_details having NOT NULL constraint as shown in the below program –

DECLARE
l_order_details VARCHAR2( 125 ) NOT NULL := 'Skipping Rope';
BEGIN
l_shipping_status := '';
END;

The execution of above code will throw an error as shown below –

This was because a variable with NOT NULL constraint could not accept the zero-length string as its value as it is also considered NULL in PL/ SQL.

Assigning Values to Variables

We can assign the value to the variable right when we are declaring the variable in the variable block by using the DEFAULT keyword or by using := assignment operator. Let us consider one example related to the same thing –

DECLARE
l_medical_equipment VARCHAR2(100) := 'Oximeter';
BEGIN
l_medical_equipment := 'BP Measuring Machine';
DBMS_OUTPUT.PUT_LINE(l_medical_equipment);
END;

The output of executing the above program is as shown below –

Assigning Value of One Variable to Other

We can even assign the value of the other variable to one variable in PL/ SQL program. Let us see one such example where we will declare two variables named l_written_for and l_article_topic. The variable l_article_topic is initialized with value PL/ SQL Variable. The value of l_article_topic variable is assigned to l_written_for variable and when we are trying to print the value of the l_written_for variable value in the output then we get the same value which is PL/ SQL  Variable as assigned as default value to l_article_topic variable.

DECLARE
l_article_topic VARCHAR2(100) := 'PL/ SQL Variable';
l_written_for VARCHAR2(100);
BEGIN
l_written_for := l_article_topic;
DBMS_OUTPUT.PUT_LINE(l_written_for);
END;

The output of the execution of above program is as shown below –

We can even make the use of variables declared in the program anywhere in the program such as while specifying if conditions, counters for the loop statements or even while creating a query statement in the condition and constraint specification.

Conclusion

In PL/ SQL variables are the names assigned to the memory locations so that we can store the value in the memory block which can be assigned the datatype to specify what type of values will be stored in them. We can even access this memory location values by using the variable names.

Recommended Articles

This is a guide to PLSQL Variable. Here we also discuss the introduction and syntax of  PLSQL Variable along with different examples and its code implementation. You may also have a look at the following articles to learn more –

1. PostgreSQL Encryption
2. SQLite Database
3. SQLite Show Tables
4. SQLite Create Database

The post PLSQL Variable appeared first on EDUCBA.