Stored Procedures and Functions In MySQL

In MySQL, a stored procedure is nothing but a set of SQL statements that are stored in the server. The advantage of using a stored procedure is that once a stored procedure is stored in the server, instead of the individual statements the procedure will be referred to for each client request.

These routines are very useful when many client applications (written in a different language or for a different platform) perform the same database operations.

It also helps in performance improvement as a very small amount of data needs to transfer between the client and server. Moreover, most of the operations are performed on the server side which in turn reduces the load of the clients.

In large and complex applications, like online shopping, banking systems, etc., stored procedures are mostly used even for very simple operations.

How to create a Stored Procedure?

delimiter //
 
mysql> CREATE PROCEDURE myproc (OUT myparam INT)
-> BEGIN
->   SELECT COUNT(*) INTO myparam FROM mytable;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)
  
mysql> delimiter ;

How to call a Stored Procedure?

mysql> CALL myproc(@a);
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT @a;
+------+
| @a   |
+------+
| 3    |
+------+
1 row in set (0.00 sec) 

How to alter a Stored Procedure?

ALTER PROCEDURE myproc [characteristic ...]

How to drop a Stored Procedure?

DROP PROCEDURE IF EXISTS myproc

Happy Exploring!