A segment of declarative SQL statements which is stored inside the database catalog is called stored procedures, invoked by a triggers.
- 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.
- 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.
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: