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 , "CREATE PROCEDURE and CREATE FUNCTION Syntax"). A procedure is invoked using a CALL statement (see , "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 , "MySQL Compound-Statement Syntax").

Stored routines can be dropped with the DROP PROCEDURE and DROP FUNCTION statements (see , "DROP PROCEDURE and DROP FUNCTION Syntax"), and altered with the ALTER PROCEDURE and ALTER FUNCTION statements (see , "ALTER PROCEDURE Syntax").

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

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 , "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 MariaDB client library that supports multiple result sets. This means the client must use a client library from a version of MariaDB 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 , "mysql-real-connect()", and , "C API Support for Multiple Statement Execution".

Retornar