I was lucky and I had the chance to study MySQL (&PHP basics) on a 20 week full-time course, with a Tutor who had 15+years experience in web development. He really guided us if something was not clear. Unfortunately nowadays most people just start at home google the subject and start to pick it up. I do/did the same with couple of IT subject but the problem comes if you hurry to have something in your hand to work with and then you fly over things would be a MUST.

Stored procedures can be used to insert new values for more than one table. This could be handy if the data is stored in few tables as well as restricting the inputs coming from the application layer. The following example will show how to create a stored procedure for insert values in MySQL database. This can give the general idea of how to create a stored procedure which contains even some calculation made ‘on the fly’.

Did you ever notice that MySQL auto increment used as default value to write in, if column value is null (or not specified). This is a really great feature, used I think in 80% of tables as id. If you delete the last row of a table eg. where id=5, time comes for inserting a new record with ‘null’ as id value, auto_increment gives id=6 as next to use. In other words if we delete a row auto increment can’t pick up the deleted values.

Stored procedures in MySQL can encapsulate statements which can be used later. I think the real power of using stored procedures is in the amount of time can be saved by using one line against maybe dozens of insert, delete etc. Benefits of Stored Procedures in MySQL: Stored Routines can be created with error handlers. Code packaging and encapsulation Separation of the logic Reduction of network bandwidth Security by giving access to a specific routine handling the data.