This page discusses - Variables Scope in SQL ProcedureThis page discusses - Variables Scope in SQL Procedure
Variable Scope in SQL Procedure is used to define the scope of a variable used in SQL.
Understand with Example
The Tutorial illustrates an example from Variable Scope in SQL Procedure.
For sake of your understanding :
Local variables - These variable are declared inside a inner block and cannot be referenced by outside blocks.
Global variables - These variable are declared in a outer block and reference itself and its inner blocks.
To understand Variable Scope in SQL, we create a Procedure abc that include a Local variable. The Local variable are declared in a inner block and cannot be accessed by outside blocks In this example we declare a variable 'x', whose data type is char and default value is 'inner' in the Local variables. The SQL Query include a outer block and declare a variable 'x' whose data type is 'char' type. These are declared in outer block which can be referenced by its itself and inner blocks.
create procedure abc() BEGIN DECLARE x CHAR(5) DEFAULT 'outer';
BEGIN DECLARE x CHAR(5) DEFAULT 'inner'; Select x; END; Select x; END$$
To invoke a Procedure 'abc', we use the given below Syntax :
+-------+ | x | +-------+ | inner | +-------+ 1 row in set (0.00 sec)
+-------+ | x | +-------+ | outer | +-------+ 1 row in set (0.03 sec)