Please disable your adblock and script blockers to view this page

Search this blog

Friday, 9 March 2018

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

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

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

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

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

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

and output is

Cheers :) Happy Learning

No comments :

Post a Comment