Stored Procedures
In this page:
Introduction to Stored Procedures
A stored procedure bundles a group of SQL statements together and saves them inside the database under a name, so that entire sequence can be re-run on demand instead of being retyped or resent from the application every time.
Example: Introduction to Stored Procedures
DELIMITER //
CREATE PROCEDURE GetAllUsers()
BEGIN
SELECT * FROM users;
END //
DELIMITER ;Calling a Stored Procedure
Once defined, a stored procedure is executed with the CALL statement, which triggers every query saved inside it to run in order as a single unit of work.
Example: Calling a Stored Procedure
CALL GetAllUsers();Dropping a Stored Procedure
DROP PROCEDURE removes a saved procedure permanently. Adding IF EXISTS to the statement prevents an error if the procedure was already deleted or never existed in the first place.
Example: Dropping a Stored Procedure
DROP PROCEDURE IF EXISTS GetAllUsers;Listing Stored Procedures
You can list every stored procedure currently defined in a database, which is a useful way to rediscover what reusable logic is already available before writing new, possibly duplicate, procedures.
Example: Listing Stored Procedures
SHOW PROCEDURE STATUS WHERE Db = DATABASE();Benefits of Stored Procedures
Stored procedures reduce the amount of data sent back and forth over the network since only the CALL and its arguments travel across, and they add a layer of security by hiding table structure details behind a fixed interface.
Example: Benefits of Stored Procedures
-- One CALL travels over the network instead of several separate queries,
-- and the caller never needs to know the underlying table structure.
CALL GetAllUsers();Chapter Quiz — Complete all 5 topics to unlock
0/5 topics done
Complete these topics first: