These two words represent the very features that many people never learn about nor utilize with MySQL. Let's change that. When you write PHP code you often times organize your coding into various constructs; whether it's a function or a class method. What may surprise you is that MySQL has its own implementation of these. They come in the form of procedures and functions. They are what we call stored routines.

Procedures: Procedures are similar to functions in programming languages such as PHP, Python, Ruby, etc.  They perform a predefined action and may be called at appropriate times.  A procedure is like standard functions, in that it takes parameters. However, unlike standard functions, it can not return a value. Procedures also can not be used in a query (for example, a SELECT clause).  This may sound like an excessive restriction but it works because they don't return values.

Functions: Functions are very similar to procedures, but like their standard counterpart (in the likes of PHP or Python), they may return a value. In fact, that is generally where you'll determine which of the two to use: do you need a return or do you not need a return? Functions may also be used inside queries, unlike procedures which need to explicitly be called.

Both procedures and functions take parameters. Not surprisingly we give all parameters a name and declare its data type (varchar, double, date, etc.).

But with procedures is we give them an additional flag: the direction the variable will be moving. Procedure parameters are declared as either IN, OUT or INOUT. A parameter declared as IN may only be used as input. An OUT parameter is used as output. And lastly, an INOUT can be used as both. But wait, didn't I just say procedures can't return a value? Well they can't in that we cannot declare a procedure as a data type and use the RETURN() function at the end. But can generate output with procedures through OUT parameters. In fact, this is where procedures have a one-up on functions: a procedure can generate multiple outputs; a function can only return one value of its designated data type.

I'm now going to drop some SQL code for you to look over.  I've commented the code heavily to explain what is happening.  If you are working with MySQL from the command line then you'll need to set the delimiter to another character besides the semicolon.  We use semicolons inside the routines.  You can do this simply by issuing the following command:

delimiter ||

What you choose to set the delimiter to is your prerogative.  Just remember that you'll want to set it back to the semicolon unless you wish to continue using the new delimiter until you end your MySQL session.

CREATE PROCEDURE divide_numbers(IN lhv NUMERIC(5,3), IN rhv NUMERIC(5,3), OUT answer NUMERIC(5,3))BEGIN
 -- This a pointless variable that's only here to show you what they look like
 DECLARE tmpAnswer NUMERIC(5,3);
 -- MySQL supports conditionals!
 IF(rhv > 0) THEN
  SELECT (lhv / rhv) INTO answer;
 ELSE
  SELECT (0) INTO answer;
 END IF;END;

-- How do we use a procedure?Well we CALL it-- The @ declares a variableCALL divide_numbers (10, 2, @answer);SELECT @answer;
-- Drop the procedure as we no longer need it
DROP PROCEDURE divide_numbers

CREATE FUNCTION divide_numbers(lhv NUMERIC(5,3), rhv NUMERIC(5,3))
 RETURNS NUMERIC(5,3) -- Declare the return type
 DETERMINISTIC
 -- DETERMINISTIC means the function will have the same output for the given parameters
 -- An example of a "NOT DETERMINISTIC" function would be a random number generator
 -- But 10 / 2 will always be 5, so this is DETERMINISTIC
BEGIN
 DECLARE answer NUMERIC(5,3);
 
 IF(rhv > 0) THEN
  SET answer = lhv / rhv;
 ELSE
  SET answer = 0;
 END IF;
 
 RETURN(answer);
END;

- We don't CALL functions but rather use them in queries (because they return values)
SELECT divide_numbers(100, 30);

-- Drop the function
DROP FUNCTION divide_numbers;