Stored Procedures in MySQL
Stored Procedures in MySQL
=====================
MySQL 5.0 introduced Stored Procedures which allow us to automate or program our way out of many tasks on directly on the server rather than having to write external scripts to do complex manipulation of data.
There is no direct support for dynamic declaration of details such as column or table names in MySQLs stored procedures. However, by utilizing user variables (which have been around for a while)
For example:
============
average population of the cities defined in the world database:
SELECT AVG(Population) FROM City;
+—————–+
| AVG(Population) |
+—————–+
| 350468.2236 |
+—————–+
A user variable is distinguished by having a ‘@’ symbol in front of it, and values are assigned using the SET statement:
SET @a := ‘abc’;
SELECT @a;
+——+
| @a |
+——+
| abc |
+——+
SET @s := CONCAT(’SELECT AVG(’ , ‘Population’ , ‘) FROM ‘ , ‘City’);
SELECT @s;
+———————————-+
| @s |
+———————————-+
| SELECT AVG(Population) FROM City |
+———————————-+
