MySQL stored routines

From Cosmin's Wiki

Jump to: navigation, search

Home > MySQL > MySQL stored routines

A stored routine is either a stored procedure or a function.

Database servers like Microsoft SQL Server offer for quite a while the functionality of writing stored procedures and functions. As of version 5.0, MySQL also allows this functionality. So what exactly are stored routines?

Stored routines are collections of SQL statements that are grouped together, are stored and will be executed on the database server. Almost any valid sql statement can go into a stored routine. One of the indirect benefits of using stored routines is that application code becomes smaller and easier to understand. Another advantage of stored routines is that the SQL can be “pre-compiled” increasing the speed of the application. Because stored routines contain program logic, more processing can take place on the database server, which can reduce the amount of bandwidth consumed sending data back to the application.

What is the difference between a stored procedure and a function? Well, actually there isn't any in the base logic of how they work. However, depending on their role and the place where they're called from ... there is a difference. Basically, you would use a stored procedure when you want to do some operations against the database (select, insert, update, calculations...) and then eventually return a result. The function does the same, but the result of the function can be used within a SELECT statement.

Another difference would be that stored functions cannot be recursive, as opposed to stored procedures where recursion is allowed, but is disabled by default. To enable recursion for stored procedures, set the max_sp_recursion_depth server system variable to a value greater than zero. Stored procedure recursion increases the demand on thread stack space. If you increase the value of max_sp_recursion_depth, it may be necessary to increase thread stack size by increasing the value of thread_stack at server startup.

Let's see how each of the 2 stored routine types can be used:

MySQL Stored Procedures

MySQL Stored Functions

MySQL Triggers