What is a stored procedure?
A stored procedure is a set or collection or group of SQL queries which performs a task. It may fetch some data from a table, delete or update a record or may alter a table or perform all of these tasks at once.
A stored procedure can accept parameters, declare variables and even return values. A stored procedure can also contain decision statements such as IF-ELSE and CASE, looping statements such as LOOP, WHILE, REPEAT and LEAVE.
If you are familiar with a programming language, then you can compare a stored procedure with a function or a method which is a block of statements and performs some task.
Advantages of stored procedure
A stored procedure is a group of SQL queries and it is always better to create a stored procedure instead of executing those queries again and again due to the following reasons.
1. Provides compile time checks
A stored procedure is compiled before it can be executed and any syntax error in the procedure and in the SQL queries used in the procedure are caught at compile time rather than at execution time.
2. Saves time
Once compiled, a stored procedure is saved(as per its name, stored) at the database end for a database session. It can be executed any number of times without recompiling it again and again, thus saving compilation time.
3. Secure
It is possible to restrict access to a procedure for particular users or applications thus preventing it from being accidentally executed or modified.
4. Reduced network traffic
As stated earlier, a stored procedure is a set of SQL statements and is saved after compilation. So, you only need to compile it once and after that, no transfer of statements between the application and database is required over the network.
5. Flexible execution
A stored procedure can accept parameters which are supplied to it from outside when it is called. These parameters can be used to replace fixed values, which means that same set of queries can be executed with different values simply by supplying those values as parameters while calling the procedure and without actually modifying the queries.
This tutorial will cover all aspects of stored procedures in MySQL comprehensively with examples. After going through this tutorial, you will be able to
- Create your own stored procedures.
- Execute or call it.
- Delete a procedure.
- Supply parameters to a stored procedure.
- Use variables inside it.
- Use conditions and loops inside a procedure.
Keep learning!!!