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