Latest Tutorials| Questions and Answers|Ask Questions?|Site Map



Home Sql Mysql-example Mysql Declare Procedure

Related Tutorials


 
 

Share on Google+Share on Google+

Mysql Declare Procedure

Advertisement
Mysql Declare Procedure is used to define a local variable within a procedure. The local variable are used within the procedure.

Mysql Declare Procedure

     

 Mysql Declare Procedure is used to define a local variable within a procedure. The local variable are used within the procedure.

Understanding with Example

The Tutorial grasp you an example from 'Mysql Declare Procedure'. To understand this example we create a table 'Stu' and 'Lib' with required fieldname and datatype respectively.

 

 

 

Create Table Stu

Create Table Stu(Id int,Name Varchar(15));

Create Table Lib

Create Table Lib(Id int,libno int);

Insert Values into Stu

The Insert into is used to add the records or rows to the table 'Stu' and 'Lib'.

Insert Into Stu values(1,'Ajay');
Insert Into Stu values(2,'Bhanu');
Insert Into Stu values(4,'Rakesh');
Insert Into Stu values(5,'Santosh');
Insert Into Stu values(3,'Komal');

Insert Values into Lib

Insert Into Lib values(1,101);
Insert Into Lib values(2,102);
Insert Into Lib values(4,103);
Insert Into Lib values(5,104);
Insert Into Lib values(3,105);

Create Procedure Display

Now we create a procedure 'Display'.The Declare appear at the beginning of the stored procedure, followed by the first Begin Statement.The Default statement specify the name of table to be choosen when we don't specify any table.In this query we use 'stu' as default table and return the records from the table 'stu', when we don't choose any of the given table.

DELIMITER $$
CREATE PROCEDURE display()
BEGIN
    DECLARE tablename varchar(10) default 'stu';
    CASE tablename
	WHEN 'stu' THEN SELECT * FROM Stu;
	WHEN 'lib' THEN SELECT * FROM Lib;
    END CASE;
End$$
DELIMITER ;

Call Procedure Display

The call procedure Display return the records detail from table 'stud'.

call display ();

Result

+------+---------+
| Id   | Name    |
+------+---------+
| 1    | Ajay    |
| 2    | Bhanu   |
| 4    | Rakesh  |
| 5    | Santosh |
| 3    | Komal   |
+------+---------+
Advertisement

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: January 20, 2009

Related Tutorials

Discuss: Mysql Declare Procedure  

Post your Comment


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