Posted on 2008-12-03T00:00:00 @ 10:15:45
Previously, I gave a brief introduction into what stored routines actually are and gave some advantages of using them. Now, I'll cover some of the basics for creating stored routines.
As mentioned in the last article, stored routines can either be run as the definer or invoker - for now, we'll stick with the simpler case of running as the invoker.
Creating and using stored routines
Where do stored routines live?
Stored routines are associated with a particular schema - that is, the access rights for the creation, modification and execution of stored routines are checked against the schema. There is an implicit "USE" statement executed at the start of stored routines that automatically switches the default schema to the one in which the routine has been created.
The stored routines actually live in the "proc" table of the system "mysql" schema, although this shouldn't be edited directly (there is no guarantee that the MySQL server will pick up these changes without restarting). There are very, very few reasons why you'd actually need to change this table directly, although reading this table can be useful to get the input parameters, return value, name of the definer, whether the routine runs as invoker or definer, and the creation and modification dates.
To create a stored routine, you must have the "CREATE ROUTINE" privilege for the schema that you wish to create the stored routine in. If binary logging is turned on you will either need the SUPER privilege or set a system variable before you can create the routine (either will allow the routine to execute properly on slave servers).
GRANT CREATE ROUTINE ON schema.* TO user;
Although the routine is defined against a particular schema, there is nothing stopping you referencing tables in other schemas from within the stored routine.
Always use full the fully qualified schema.table name when referencing tables within a stored routine. This will make it perfectly clear which table you're referring to, and can remove the surprise of the implicit USE statement.
To alter or delete a stored routine, you must have the "ALTER ROUTINE" privilege for the database (or routine) that you wish to change or delete. To grant this to specific routines, you will need to add either 'FUNCTION' or 'PROCEDURE' (depending on the type of routine) before the required routine name:
GRANT ALTER ROUTINE ON schema.* TO user;
GRANT ALTER ROUTINE ON FUNCTION schema.stored_func TO user;
GRANT ALTER ROUTINE ON PROCEDURE schema.stored_proc TO user;
To actually run a stored routine, you must have the "EXECUTE" privilege. Like the ALTER ROUTINE privilege, this can be granted on a per-routine basis - schema-wide access may be granted to allow the execution of all of the stored routines created for that schema, but this will include those that run as 'definer', which may be a security issue. By default, the definer automatically has permission to alter and execute stored routines, although there is a server configuration option to turn this off.
GRANT EXECUTE ON schema.* TO user;
GRANT EXECUTE ON FUNCTION schema.stored_func TO user;
GRANT EXECUTE ON PROCEDURE schema.stored_proc TO user;
Access to stored routines in a schema does not require any access to the tables - they will still be able to execute the stored routines, although they will receive privilege errors if the routines try to access tables.
Creating stored routines
If you're using the command-line MySQL client, you will need to change the delimiter (the symbol that indicates an SQL statement is finished and triggers execution) otherwise your stored routine may not be created (any semi-colons within your routine definition will prematurely close it). The delimiter can be anything that doesn't appear in your stored routine, but usually two forward slashes (//) are used.
A simple stored function
This is possibly the simplest stored function you can create:
CREATE FUNCTION incr
You would use it like this:
mysql> SELECT incr(5);
| incr(5) |
| 6 +
1 row in set (0.01 sec)
mysql> SELECT incr(100);
| incr(100) |
| 101 +
1 row in set (0.01 sec)
Okay, so not a greatly useful function, but it demonstrates how they're created and used. Splitting the definition over several lines is allowed, and is a good idea to improve readability.
I always write stored routines in a separate text editor and copy + paste it into the MySQL client - saving them as files also allows you to have an external (to the MySQL server) copy of the code for later review, and lets you keep them in version control.
Firstly, we change the delimiter - in this example it's not strictly necessary, but it's a good habit to get into.
The next line declares what type of stored routine we want to create (a FUNCTION) and it's name. You cannot use reserved words as routine names (you'll get an error) but you can use the name of existing MySQL functions (you will need to fully qualify the name with the schema name when you want to use the routine, otherwise the built-in function gets called)
The following line declares the input values. For functions, all parameters are IN functions - they are passed by value, so the function can modify their value without that change being made to the original variable. Here, we have a single input variable called 'orig' of type INT.
As this is a function, it must return a value, and the next line defines the type being returned (INT). Note the 's' on the end - you'll get an error otherwise.
Following the return type is the function characteristics. Here we just have 'DETERMINISTIC' - this indicates to the MySQL server that this function always returns the same output value for a given input value. The MySQL server may be able to optimise queries based on this information, although in our example probably not by much.
I always put a blank line between the statements that define the function properties and the actual body of the function - this can also improve readability. Our simple function does one simple thing - returns the value of the 'orig' input parameter plus one.
As we changed the delimiter at the start of the definition, the MySQL client considers all of this to be a single statement, so far. We now use the delimiter to indicate that the command is finished and should be executed, and then change the delimiter back to the default (a semi-colon). Unless an error was raised, the new function is available to use, as demonstrated above.
By now, you should be able to create basic stored functions of your own. Next time I'll include a more complex example of a stored function, and cover stored procedures as well.