Home Sql Variables Scope in SQL Procedure
Questions:Ask|Latest

 
 

Share on Google+Share on Google+

Variables Scope in SQL Procedure

Advertisement
Variable Scope in SQL Procedure is used to define the scope of a variable used in SQL.

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

delimiter $$
create procedure abc()
BEGIN
DECLARE x CHAR(5) DEFAULT 'outer';
	BEGIN 
		DECLARE x CHAR(5) DEFAULT 'inner';
		Select x;
	END;
Select x;
END$$
delimiter ;

Call Procedure

To invoke a Procedure 'abc', we use the given below Syntax :

call abc();

Result

+-------+
| x     |
+-------+
| inner |
+-------+
1 row in set (0.00 sec)
+-------+
| x     |
+-------+
| outer |
+-------+
1 row in set (0.03 sec)
Advertisements

If you enjoyed this post then why not add us on Google+? Add us to your Circles



Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: December 20, 2008

Related Tutorials

Ask Questions?    Discuss: Variables Scope in SQL Procedure  

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments
DMCA.com