Syntax:
CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

CREATE
    [DEFINER = { user | CURRENT_USER }]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body
    
proc_parameter:
    [ IN | OUT | INOUT ] param_name type
    
func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

routine_body:
    Valid SQL procedure statement

These statements create stored routines. By default, a routine is
associated with the default database. To associate the routine
explicitly with a given database, specify the name as db_name.sp_name
when you create it.

The CREATE FUNCTION statement is also used in MySQL to support UDFs
(user-defined functions). See
http://dev.mysql.com/doc/refman/5.0/en/adding-functions.html. A UDF can
be regarded as an external stored function. However, do note that
stored functions share their namespace with UDFs. See
http://dev.mysql.com/doc/refman/5.0/en/function-resolution.html, for
the rules describing how the server interprets references to different
kinds of functions.

When the routine is invoked, an implicit USE db_name is performed (and
undone when the routine terminates). The causes the routine to have the
given default database while it executes. USE statements within stored
routines are disallowed.

When a stored function has been created, you invoke it by referring to
it in an expression. The function returns a value during expression
evaluation. When a stored procedure has been created, you invoke it by
using the CALL statement (see [HELP CALL]).

As of MySQL 5.0.3, to execute the CREATE PROCEDURE or CREATE FUNCTION
statement, it is necessary to have the CREATE ROUTINE privilege. By
default, MySQL automatically grants the ALTER ROUTINE and EXECUTE
privileges to the routine creator. See also
http://dev.mysql.com/doc/refman/5.0/en/stored-routines-privileges.html.
If binary logging is enabled, the CREATE FUNCTION statement might also
require the SUPER privilege, as described in
http://dev.mysql.com/doc/refman/5.0/en/stored-programs-logging.html.

The DEFINER and SQL SECURITY clauses specify the security context to be
used when checking access privileges at routine execution time, as
described later.

If the routine name is the same as the name of a built-in SQL function,
a syntax error occurs unless you use a space between the name and the
following parenthesis when defining the routine or invoking it later.
For this reason, we suggest that it is better to avoid using the names
of existing SQL functions for your own stored routines.

The IGNORE_SPACE SQL mode applies to built-in functions, not to stored
routines. It is always allowable to have spaces after a stored routine
name, regardless of whether IGNORE_SPACE is enabled.

The parameter list enclosed within parentheses must always be present.
If there are no parameters, an empty parameter list of () should be
used. Parameter names are not case sensitive.

Each parameter is an IN parameter by default. To specify otherwise for
a parameter, use the keyword OUT or INOUT before the parameter name.

*Note*: Specifying a parameter as IN, OUT, or INOUT is valid only for a
PROCEDURE. (FUNCTION parameters are always regarded as IN parameters.)

An IN parameter passes a value into a procedure. The procedure might
modify the value, but the modification is not visible to the caller
when the procedure returns. An OUT parameter passes a value from the
procedure back to the caller. Its initial value is NULL within the
procedure, and its value is visible to the caller when the procedure
returns. An INOUT parameter is initialized by the caller, can be
modified by the procedure, and any change made by the procedure is
visible to the caller when the procedure returns.

For each OUT or INOUT parameter, pass a user-defined variable in the
CALL statement that invokes the procedure so that you can obtain its
value when the procedure returns. If you are calling the procedure from
within another stored procedure or function, you can also pass a
routine parameter or local routine variable as an IN or INOUT
parameter.

The RETURNS clause may be specified only for a FUNCTION, for which it
is mandatory. It indicates the return type of the function, and the
function body must contain a RETURN value statement. If the RETURN
statement returns a value of a different type, the value is coerced to
the proper type. For example, if a function specifies an ENUM or SET
value in the RETURNS clause, but the RETURN statement returns an
integer, the value returned from the function is the string for the
corresponding ENUM member of set of SET members.

Parameter types and function return types can be declared to use any
valid data type, except that the COLLATE attribute cannot be used.

The routine_body consists of a valid SQL procedure statement. This can
be a simple statement such as SELECT or INSERT, or it can be a compound
statement written using BEGIN and END. Compound statements can contain
declarations, loops, and other control structure statements. The syntax
for these statements is described in
http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-compound-statements.h
tml.

MySQL allows routines to contain DDL statements, such as CREATE and
DROP. MySQL also allows stored procedures (but not stored functions) to
contain SQL transaction statements such as COMMIT. Stored functions may
not contain statements that perform explicit or implicit commit or
rollback. Support for these statements is not required by the SQL
standard, which states that each DBMS vendor may decide whether to
allow them.

Statements that return a result set cannot be used within a stored
function. This includes SELECT statements that do not have an INTO
var_list clause and other statements such as SHOW, EXPLAIN, and CHECK
TABLE. For statements that can be determined at function definition
time to return a result set, a Not allowed to return a result set from
a function error occurs (ER_SP_NO_RETSET). For statements that can be
determined only at runtime to return a result set, a PROCEDURE %s can't
return a result set in the given context error occurs
(ER_SP_BADSELECT).

*Note*: Before MySQL 5.0.10, stored functions created with CREATE
FUNCTION must not contain references to tables, with limited
exceptions. They may include some SET statements that contain table
references, for example SET a:= (SELECT MAX(id) FROM t), and SELECT
statements that fetch values directly into variables, for example
SELECT i INTO var1 FROM t.

For additional information about statements that are not allowed in
stored routines, see
http://dev.mysql.com/doc/refman/5.0/en/stored-program-restrictions.html
.

The following example shows a simple stored procedure that uses an OUT
parameter:

mysql> delimiter //

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t;
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a;
+------+
| @a   |
+------+
| 3    |
+------+
1 row in set (0.00 sec)

The example uses the mysql client delimiter command to change the
statement delimiter from ; to // while the procedure is being defined.
This allows the ; delimiter used in the procedure body to be passed
through to the server rather than being interpreted by mysql itself.
See
http://dev.mysql.com/doc/refman/5.0/en/stored-programs-defining.html.

For information about invoking stored procedures from within programs
written in a language that has a MySQL interface, see [HELP CALL].

The following example function takes a parameter, performs an operation
using an SQL function, and returns the result. In this case, it is
unnecessary to use delimiter because the function definition contains
no internal ; statement delimiters:

mysql> CREATE FUNCTION hello (s CHAR(20))
mysql> RETURNS CHAR(50) DETERMINISTIC
    -> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+
1 row in set (0.00 sec)

MySQL stores the sql_mode system variable setting that is in effect at
the time a routine is created, and always executes the routine with
this setting in force, regardless of the server SQL mode in effect when
the routine is invoked.

URL: http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

