PL/SQL Variables and Constants

A variable in any programming language is the name of space where values are stored and controlled by our code/program

• We can not use reserve keyword as a variable name 
• Variable length should not exceed 30 characters
• The variable name consists of letters followed by other letters, dollar sign, underscore and numerals
• The variable name should be clear and easy to understand

Here we'll learn how to declare and initialize variables in PL/SQL

The basic syntax for declaring a variable in PL/SQL is following

variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value] 

Here variable_name is the identifier of variable and datatype is a valid PL/SQL datatype. CONSTANT and DEFAULT are keywords used to define constants and set default values of variables

Initializing Variables in PL/SQL

When we declare any variable, PL/SQL assigns it a default value of null but if we want to assign any other value to variable initially then we can do this using any of following methods during declaration

• Using DEFAULT Keyword
• Using assignment operator

DECLARE
-- Initialize variable using assignment operator
name varchar2(50) := 'Ashish Awasthi';
-- Initialise variable using DEFAULT Keyword
age number(3) DEFAULT 25;
BEGIN
dbms_output.put_line('Name- '||name||' Age- '||age);
END;

After execution of script result is-

Variable Scope in PL/SQL

Like many other programming languages,  In PL/SQL We have two types (Scope Wise) of variable - Local Variable and Global Variable
PL/SQL supports nested blocks so a block can have another inner block, In this type of case the variables declared in the inner block are not accessible in its parent (outer) block

Local Variable- Variables declared in the inner block are not accessible in the outer block are called local variables

Global Variable- Variables declared in the outermost block are called global variables

Example of Variables Scope

DECLARE
-- Global Variables in Outermost block
name varchar2(50) := 'Ashish';
age number(3) DEFAULT 25;
BEGIN
dbms_output.put_line('**GLOBAL VARIABLE*** Name- '||name||' Age- '||age);
DECLARE 
-- Local Variables in Inner block
name varchar2(50) := 'James';
age number(3) DEFAULT 30;
BEGIN
dbms_output.put_line('***LOCAL VARIABLE*** Name- '||name||' Age-'||Age);
END;
END;

And output is

PL/SQL Constants

Constant means - that does not change
and Constant Variables are those variables that are declared once and their value remain unchanged  throughout the program
A constant variable is declared using CONSTANT keyword and requires an initial value

Let's see how to declare and use a constant variable in PL/SQL block

DECLARE 
-- Constant Variable
pi CONSTANT number :=3.14;
--Other Variable
r number(5,2);
area number(10,2);
BEGIN
-- Initialize other variable
r:=5.2;
-- Perform calculation
area:=pi*r*r;
dbms_output.put_line('Area is- '||area);
END;

and output is

Cheers :) Happy Learning

Next Page >>

<<Prev Page