How to Use Stored Procedures in MySQL

Stored procedures are created in MySQL by the following command:

CREATE FUNCTION name ([list_of_parameters]) RETURNS data_type
or
CREATE PROCEDURE name ([list_of_paremeters])

For instance, here is a function for concatenating long strings, which we will call cutdown

mysql>CREATE FUNCTION cutdown(string VARCHAR(255))
->RETURNS VARCHAR(255)
->BEGIN
->IF ISNULL(string) THEN RETURN ”;
->ELSE
->IF CHAR_LENGTH(string)<=10 THEN RETURN string;
->ELSE
->RETURN CONCAT(LEFT(string, 5), ‘…data removed…’, RIGHT(string,5));
->END IF;
->END IF;
->END;

This function checks to see if the passed string is empty, in which case it returns a blank result, or if the string is less than 10 characters long. If so, it returns the string verbatim. If the string is 11 or more characters long it takes the first and last five characters and separates them with the phrase '...data removed...'.

To try out the procedure:

mysql> SELECT cutdown(“Richard of York gave battle in vain”);

Would return:

Richa...data removed... vain

You can drop the function/procedure, with (perhaps unsurprisingly):

DROP FUNCTION [IF EXISTS] name
or
DROP PROCEDURE [IF EXISTS] name

So, what are the differences between functions and procudures? In short:
-Functions can be called in all SQL commands, procedures only with CALL.
-Procedures can return SELECT results, functions return a single value (with the declaration RETURNS)
-Functions do not allow commands in the code that access tables; with a procedure, all SQL commands are allowed.

Editing procedures is sometimes difficult – under MySQL version 5.0.3 and earlier for instance, there is no facility to edit. Instead one needs to DROP and then CREATE the function again. But you can see the code of a stored procedure/function:

SHOW CREATE FUNCTION cutdown

To see all functions and procedures in all databases:

SHOW FUNCTION STATUS

To be more specific, query the table information_schema.routines:

SELECT routine_name, created, routine_type
FROM information_schema.routines
WHERE routine_schema=’a particular database’

The easiest way to back up specific procedures or functions is via:

SHOW CREATE PROCEDURE proc_name > proc_name_BAK.sql
SHOW CREATE FUNCTION func_name > func_name_BAK.sql

or, if you have root privileges you can backup all stored procedures:

mysqldump -u root -p mysql proc > backupfile.sql