18.2.1 Stored Routine Syntax

A stored routine is either a procedure or a function. Stored routines are created with the CREATE PROCEDURE and CREATE FUNCTION statements (see Section 13.1.9, “CREATE PROCEDURE and CREATE FUNCTION Syntax”). A procedure is invoked using a CALL statement (see Section 13.2.1, “CALL Syntax”), and can only pass back values using output variables. A function can be called from inside a statement just like any other function (that is, by invoking the function's name), and can return a scalar value. The body of a stored routine can use compound statements (see Section 13.6, “MySQL Compound-Statement Syntax”).

Stored routines can be dropped with the DROP PROCEDURE and DROP FUNCTION statements (see Section 13.1.16, “DROP PROCEDURE and DROP FUNCTION Syntax”), and altered with the ALTER PROCEDURE and ALTER FUNCTION statements (see Section 13.1.3, “ALTER PROCEDURE Syntax”).

As of MySQL 5.0.1, a stored procedure or function is associated with a particular database. This has several implications:

  • When the routine is invoked, an implicit USE db_name is performed (and undone when the routine terminates). USE statements within stored routines are not permitted.

  • You can qualify routine names with the database name. This can be used to refer to a routine that is not in the current database. For example, to invoke a stored procedure p or function f that is associated with the test database, you can say CALL test.p() or test.f().

  • When a database is dropped, all stored routines associated with it are dropped as well.

(In MySQL 5.0.0, stored routines are global and not associated with a database. They inherit the default database from the caller. If a USE db_name is executed within the routine, the original default database is restored upon routine exit.)

Stored functions cannot be recursive.

Recursion in stored procedures is permitted but disabled by default. To enable recursion, 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. See Section 5.1.4, “Server System Variables”, for more information.

MySQL supports a very useful extension that enables the use of regular SELECT statements (that is, without using cursors or local variables) inside a stored procedure. The result set of such a query is simply sent directly to the client. Multiple SELECT statements generate multiple result sets, so the client must use a MySQL client library that supports multiple result sets. This means the client must use a client library from a version of MySQL at least as recent as 4.1. The client should also specify the CLIENT_MULTI_RESULTS option when it connects. For C programs, this can be done with the mysql_real_connect() C API function. See Section, “mysql_real_connect()”, and Section 20.6.16, “C API Support for Multiple Statement Execution”.

Download this Manual
User Comments
  Posted by Graham Wideman on July 7, 2014
Functions and Procedures clarification

An alternative summary of functions versus procedures:

Inputs: Takes a list of arguments, all are inputs.
Could also get input from running a query?

Outputs: Produces a single return value -- see RETURN clause. Can be any valid MySQL data type.

(Can that return value be a result set? Other docs say that "functions cannot return a result set", but this probably means "cannot return a result set direct to the client", which is something _procedures_ can do. So far as I can tell, functions cannot return a cursor, which would be a related concept.)

Calling method: Can be used where a built-in function like ABS() might be used, such as in an assignment, expression, or where a field value is expected.

Inputs: Takes a list of arguments. Procedure definition specifies whether these arguments are for input and/or output, via IN, OUT or INOUT.

Could also get input from running a query.

Outputs: Unlike a Function, does not produce a return value per se. However, can send values back to caller via OUT or INOUT arguments.

Also, procedures can contain a bare SELECT statement, which sends a result set to the client. A procedure can contain multiple such SELECT statements, if other details enable the client to receive multiple result sets.

Calling method: Caller uses CALL statement.

Syntax within Functions and Procedures
The current doc page does mention "the body of a stored routine can use compound statements" . Here "compound statement" encompasses all the syntax you might expect relating to procedural programming, like variables, assignments, flow control and so forth.
Sign Up Login You must be logged in to post a comment.