Stored procedure in MySQL database

[Ameeryan]

Greetings,

I am using MySQL database and not so much familiar with this database because it is newly in my computer system.I don't go for institutions to learn this.I am hopefully depends on you all guys.Would you introduce me regrading the use of stored procedures for MySQL database.Your help would be appreciated .

Thank you very much .

[Praetor]

The stored procedure are very useful objects in the database which can be used in the program to short your codes.It is a segment of database which is constructed using the SQL code and stored in the database catalog.The programmer can invoke it using a calling statement.

There are some usefull actions are performed by the procedures like -

1- It can increases the performance of application
2- It helps to reduce the traffic between application and database server
3-The Stored procedure keep secured in the database and managed by database administrator.

[Katty]

There are some defined procedures are stored in the database for specific activities but you can create another whenever you need to create.The procedures which would be created by the user called user defined procedure and one stored already in the database catalog called stored or system defined procedure.

A stored procedure in the database that calls itself is known as recursive stored procedure.Almost the Relational Database Management System supports recursive stored procedure but MySQL is doesn't support this concept.Your can investigate the current version of your MySQL database before using recursive stored procedure.

[kelfro]

This is the simple stored procedure introduced by me. It access the list of products from the products table.

you need to take a look at the stored procedure code given below:

 DELIMITER //
 CREATE PROCEDURE Get_Products()
   BEGIN
   SELECT *  FROM products;
   END //
 DELIMITER ;

[Zecho]

Declaring variables in stored priocedure

During the creation of stored procedures,you need some variables for input and output operation.you can create the variable within the code as follows-

DECLARE variable_name data-type(size) DEFAULT default_value;

Just include it in your source code and pass the name of variables as an argument.After creating variables you can assign the values in the variables.The procedure to assign the value in the variable is shown below-

DECLARE st_varb INT DEFAULT 0
 SET st_varb = 20;

The SET command is used to assign the value in variables.

[Reegan]

Calling the stored procedure
When,you done from the creation of procedure to put the desired functionality.It would be stored in the database catalog and if you need to call it then you have to create a program and the program will use the defined statement to call a particular procedure -

The calling of the procedure can be preformed as follows-

CALL STORED_PROCEDURE_NAME()

The CALL command is used to call any stored procedure in MySQL database.