Local and global variables in PL/SQL with an example

Updated

PL/SQL  stands for Procedural Language structured Query Language. It is an extension of Structured Query Language (SQL) that is used in Oracle. PL/SQL allows the programmer to write code in a procedural format. It combines the data manipulation power of SQL with the processing power of procedural language to create super-powerful SQL queries.

Before understanding the usage of Local and Global variables, first, let’s understand what is Local and Global variables

Local variables :

Variables are 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  
num1 number :=95;
num2 number :=85;
BEGIN
dbms_output.put_line('Outer Variable num1: '|| num1);
dbms_output.put_line('Outer Variable num2: '|| num2);
   DECLARE  
--Local variables 
num1 number :=195;
num2 number :=185;
BEGIN
dbms_output.put_line('Inner Variable num1: '|| num1);
dbms_output.put_line('Inner Variable num2: '|| num2);
END;
END;

Output :

When the above code is executed, it produces the following result −
Outer Variable num1: 95 
Outer Variable num2: 85 
Inner Variable num1: 195 
Inner Variable num2: 185  

PL/SQL procedure successfully completed.

Explanation :

In the above example, num1 = 95 and num2 = 85 declared at the starting of the code are Global variables. 
So on printing those variables, it will print the same result. 
Whereas, the variables declared inside second DECLARE statement are local variables, which are not accessible outside the block. 
So on printing those variables inside the block will give you the same value as 195 and 185.
But if we try to print those variables outside the block, it will print the values of Global variables
Comment
Mithlesh Upadhyay Published 18 Nov 2026 · 2 min read

Mithlesh Upadhyay is a Computer Science and AI expert from Madhya Pradesh with strong academic background (BE in CSE and M.Tech in AI) and over six years of experience in technical content development. He has contributed tech articles, led teams, and worked in Full Stack Development and Data Science. He founded the w3colleges.org portal for learning resources.

Topics:

Related tutorials

  • Difference between CouchDB and IBM Db2 databases

    1. CouchDB : Apache CouchDB is an open-source document-oriented NoSQL database that uses multiple formats and protocols to store, transfer, and process its data, it uses JSON to…

    2 min read
  • What is a database link in DBMS and its types

    We can define a database link to be a connection between 2 databases or two or more physical databases. Database links plays an significant role in case of…

    3 min read
  • Database Transaction Models and the CAP Theorem

    According to Brewer’s theorem (also called CAP theorem), any distributed data store (like a database) can only provide two of the following three guarantees: Consistency Availability Partition tolerance…

    3 min read
  • Some Interesting Facts About MySQL

    MySQL is a relational database management system (RDBMS) based on SQL- Structured Query Language and is free and open source.MySQL was created by MySQL AB, a Swedish company…

    2 min read
  • Single row character functions

    Single row character function are those function which accepts character values as arguments and return either a numeric or character values. These are two types: Case Manipulation Character…

    2 min read
  • Normalization in DBMS: 1NF, 2NF, 3NF and BCNF

    If a table has data redundancy and is not properly normalized, then it will be difficult to handle and update the database, without facing data loss. It will…

    4 min read