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.
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.
Here is a simple program to understand this.
SET SERVEROUTPUT ON;
Test_var1 NUMBER; — Declaring variable Test_var
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.
Two main questions which I am going to address in this section are
- Where can we initialize the variables?
- 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.
- 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.
- 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.
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.
In this example we will declare the variable in declaration section and initialize it by fetching value from the table employees of HR user.
SELECT salary INTO var_salary FROM employees WHERE employee_id=100;
DBMS_OUTPUT.put_line(‘Salary is ‘||var_salary);
Here in this simple PL/ SQL code I am fetching the salary of an employee whose employee id is 100 from the employees table of HR user into the variable var_salary;
Whenever you want to store value from a column of a row into the variable you use INTO clause of SELECT statement as I did here. INTO clause signifies that you want to store the value of salary column into the variable var salary.
Similarly if you want you can store the value from two columns of a row into two variables, let’s see how.
var_fname VARCHAR2 (15);
SELECT salary, first_name INTO var_salary, var_fname
FROM employees WHERE employee_id=100;
DBMS_OUTPUT.put_line (‘Salary is ‘||var_salary);
DBMS_OUTPUT.put_line (‘Name is ‘||var_fname); END;
To store two distinguished values we need two different variables thus I declared two variables var salary and var fname in declaration section. I stored value from salary column into var salary and value from first name into var fname column using Select statement in execution section.
Always remember here that mapping is one on one which means the value from first column in the Select list will be stored in the first variable in the INTO list as happened here. Value from salary column stored in var salary variable and value from 2nd column first name gets stored in 2nd variable which is var fname similarly you can have as many columns as you want.
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!
[…] User variables. Discussed in PL/SQL Tutorial 2 […]