Online Training Spring Hibernate Training in Delhi R Programming Training Course Big Data and Hadoop Training Java Training

Variables Scope in SQL Procedure

This page discusses - Variables Scope in SQL Procedure

Ads

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

Share on Google+Share on Google+

Variables Scope in SQL Procedure

Posted on: April 18, 2011 If you enjoyed this post then why not add us on Google+? Add us to your Circles

Advertisements

Ads
 

Discuss: Variables Scope in SQL Procedure  

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments:0

Ads

 

Ads