Variable Scope in T-SQL & PL/SQL

As per general programming point of view variable declared inner most block should not be available to outer blocks. Let's see if we can conclude how it is approached in T-SQl and PL/SQL.

Scope of TSQL Variables
begin
declare @a int -- vaiable in outer begin/end
set @a=20
Select @a
    begin
        Declare @b int -- @b declared inner begin/end
        set @b=30
        Select @b
    end
Select @a,@b -- @b is accessible
end


SQL Server scoping rules are per batch. BEGIN/END have no meaning on scope in this example.

That is, the variable is *not* declared per BEGIN/END. It is declared once in the batch, so accessible to batch.

MSDN (http://msdn.microsoft.com/en-us/library/ms188927.aspx) says “The scope of a local variable is the batch in which it is declared. “

Scope of PS/SQL Variables

PL/SQL allows the nesting of Blocks within Blocks i.e, the Execution section of an outer block can contain inner blocks. Therefore, a variable which is accessible to an outer Block is also accessible to all nested inner Blocks. The variables declared in the inner blocks are not accessible to outer blocks. Based on their declaration we can classify variables into two types.

Local variables - These are declared in a inner block and cannot be referenced by outside Blocks.
Global variables - These are declared in a outer block and can be referenced by its itself and by its inner blocks.







Declare
      pa number; /* vaiable in outer begin/end*/

Begin
      pa :=20;

      declare 
          pb number; /* declared inner begin/end */
      begin

         pb:=30;
      end;    pc number;
    pc:= pa+pb; /* pa is accessible while pb is not */

end;


Hope it helps.