Declaring variables in PL SQL :
In my previous article, I have given the basic idea of different datatypes in PL SQL. Variables are nothing but name given to the storage area which is used to manipulate the data. In this article, I will try to give you the idea about the PL SQL Variables and Declaring variables in PL SQL. I will try to give the idea about the Types of PL SQL variables. Variables can be created and modified by multiple ways. Each variable has particular datatype as well as specific size, which will identify the memory allocation. Declaring variable will allocate the specific storage space for the variable. There are multiple types of variables like collections, records that I will try to explain in other articles. This article will give you idea about Declaring variables in PL SQL.
Declaring variables in PL SQL Examples:
Variable is nothing but the storage area allocated to manipulate or process the data. The variable assignment at a time of declaration is known as variable initialization.
Syntax of Declaration of variable:
Variable_Name Datatype [Constant] (Size of variable according to syntax) [:= Value of variable]
Variable_Name:
The Syntax of variable contain the Variable_name, which is valid name. The PL SQL has to follow the specific format. The variable name should start with V.The user should define maximum of 30 character or alphanumeric variable name. Valid characters include letters, numerals, dollar signs, underscore and number signs. There is no standard naming convention but most of the programmers use V_ while starting with variable.
Datatype (Size of variable according to syntax):
The datatype should be any datatype of that variable. It should be Number, float,double,integer,Boolean accordingly.The size of variable should be according to the datatype.
Constant:
Constant keyword is used to define the constants in PL SQL.The constants are variables in which the value does not change.
: = Symbol:
The := symbol is used to define the value to the variable.
Different Examples Declaring variables in PL SQL :
I will give some important real life scenarios and examples of declaration of variable in PL SQL environment so that user will get the actual idea of it.
User can display the variable using DBMS_OUTPUT.PUT_LINE function which is used in every following examples
Example 1:
Declare the varachar2 variable:
Declare
V_New varchar2(30);
Begin
DBMS_OUTPUT.Put_Line(‘V_New is declared here’);
End;
Output :
V_New is declared here.
The above example contains the declaration of the variable named V_New but that variable is not initialized.
Example 2:
Declare varchar2 variable with initialization:
Using assignment operator user can declare as well as initialize the value in the variable.
DECLARE
V_New VARCHAR2(11) := ‘Initialization variable’;
BEGIN
DBMS_OUTPUT.PUT_LINE(V_New);
END;
Output :
Initialization variable
In Above example the variable assignment is done through the := operator. Most of the time users making mistake using the := Operator.User is using = operator in spite of := operator.
Example 3 :
Declare variable using default keyword
DECLARE
V_New VARCHAR2(11) not null default ‘Default Keyword’;
BEGIN
DBMS_OUTPUT.PUT_LINE(V_New);
END;
Output :
Default keyword
The above example will initialize,declares and sets the default value as ‘Default keyword’ to V_New variable.
Example 4: := Operator in Begin Section
Variables can also be assigned values after declaration, in the executable section. This is typically done in one of two ways, using the assignment operator (:=) or a SELECT INTO statement.
DECLARE
V_date DATE;
BEGIN
V_date := current_date;
DBMS_OUTPUT.PUT_LINE(‘Todays Date is: ‘ ||V_date);
END;
Output:
Todays Date is : 24/JAN/2018
Example 5 : Select into Clause
User can assign the variables using Select into Clause also. This is most useful program for PL SQL developers. In most of PL SQL programs, the Select into clause is used.
DECLARE
V_date DATE;
BEGIN
Select sysdate into V_date from dual;
DBMS_OUTPUT.PUT_LINE(‘Todays Date using select into is: ‘ ||V_date);
END;
Output:
Todays Date using select into is: 24/JAN/2018
Example 6 : Dealing with NULL Values.
There are some cases where the variable is declared but not initialized with any value.When user try to increment the value of that variable the result is always null.
DECLARE
V_number NUMBER := 0;
BEGIN
V_number := V_number + 1;
DBMS_OUTPUT.PUT_LINE(‘My number is: ‘ || my_number);
END;
These are some most important and useful examples of Declaring variables in PL SQL.Each and every variables has its own scope.Just like a C programming language the PL SQL variables are also in global and local scope.
Variable Scope :
PL/SQL allows the nesting of blocks, i.e., each program block may contain another inner block. If a variable is declared within an inner block, it is not accessible to the outer block. However, if a variable is declared and accessible to an outer block, it is also accessible to all nested inner blocks. There are two types of variable scope −
- Local variables − Variables declared in an inner block and not accessible to outer blocks.
- Global variables − Variables declared in the outermost block or a package.
Example :
DECLARE — Global variables
number1 number := 950;
number2 number := 850;
BEGIN
dbms_output.put_line(‘Outer Variable number1: ‘ || number1);
dbms_output.put_line(‘Outer Variable number2: ‘ || number2);
DECLARE — Local variables
number1 number := 1950;
number2 number := 1850;
BEGIN
dbms_output.put_line(‘Inner Variable number1: ‘ || number1);
dbms_output.put_line(‘Inner Variable number2: ‘ || number2);
END;
END; /
Output :
Outer Variable number1: 950
Outer Variable num2: 850
Inner Variable number1: 1950
Inner Variable num2: 1850
PL/SQL procedure successfully completed.
So PL SQL variables are in to 2 different scopes one is global and other is local.Local variable scope is within that block only otherwise global variable scope is global to that PL SQL block.Hope Everyone will like this article.Kindly share this article with everyone.