MySQL :: MySQL 8.4 Reference Manual :: 27.2.1 Stored Routine Syntax (original) (raw)

27.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 (seeSection 15.1.17, “CREATE PROCEDURE and CREATE FUNCTION Statements”). A procedure is invoked using a CALL statement (seeSection 15.2.1, “CALL Statement”), 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 (seeSection 15.6, “Compound Statement Syntax”).

Stored routines can be dropped with the DROP PROCEDURE and DROP FUNCTION statements (seeSection 15.1.29, “DROP PROCEDURE and DROP FUNCTION Statements”), and altered with theALTER PROCEDURE andALTER FUNCTION statements (seeSection 15.1.7, “ALTER PROCEDURE Statement”).

A stored procedure or function is associated with a particular database. This has several implications:

• When the routine is invoked, an implicit USE_`dbname`_ 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 procedurep or function f that is associated with the test database, you can say CALL test.p() ortest.f().
• When a database is dropped, all stored routines associated with it are dropped as well.

Stored functions cannot be recursive.

Recursion in stored procedures is permitted but disabled by default. To enable recursion, set themax_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 ofmax_sp_recursion_depth, it may be necessary to increase thread stack size by increasing the value ofthread_stack at server startup. See Section 7.1.8, “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 theCLIENT_MULTI_RESULTS option when it connects. For C programs, this can be done with themysql_real_connect() C API function. See mysql_real_connect(), andMultiple Statement Execution Support.

A user variable referenced by a statement in a stored procedure has its type determined the first time the procedure is invoked, and retains this type each time the procedure is invoked thereafter.