Intro: MySQL Stored Procedures

A segment of declarative SQL statements which is stored inside the database catalog is called stored procedures, invoked by a triggers.

Advantages:

• When stored procedures are created it is compiled and stored in the database so that when it is called multiple times then the compiled version is used.
• It is very secure.
• It is reusable.
• It helps to reduce the traffic between application and database server.

Disadvantages:

• Developing and maintaining stored procedures are not an easy things.
• Debugging stored procedures is very difficult.
• Using lots of stored procedures makes memory usage high.

Example:

DELIMITER //
CREATE PROCEDURE GetAllUsers()
BEGIN
SELECT *  FROM users;
END //
DELIMITER ;

Here GetAllUsers() stored procedure selects all users from the users table.

To call stored procedure:

CALL GetAllUsers();