How can i use the stroed procedure in MySql?

Question

What is the use of stroed procedure and where is it use?

Answer 1

Stored procedures is part of a stored routine, let me explain to you what a stored routine mean. Before I continue, remember that MySQL5.0 + only supports stored routines...

Stored routines consists of procedures and functions. A stored procedure is a set of SQL statements that can be stored in the server. Once this has been done, clients don't need to keep reissuing the individual statements but can refer to the stored procedure instead.

Think of it as a stored procedure as embedded sql code within the mysql server.

It is very useful when security is at highest point. Banks, for example, use stored procedures and functions for all common operations. This provides a consistent and secure environment, and routines can ensure that each operation is properly logged. In such a setup, applications and users would have no access to the database tables directly, but can only execute specific stored routines.

This is a simple way to construct a stored procedure...

DELIMITER |
CREATE PROCEDURE sp_getUserInfo(IN userID INT)
BEGIN
SELECT * FROM users WHERE userID = userID;
END|

The basic stored procedure syntax is as follows:

DELIMITER |
Create Procedure Procedure_Select_Statement
Begin
Select statement ...
END|

Good Luck !