Menu:
stored-routines-last-insert-id:: Stored Procedures, Functions, Triggers, and LAST_INSERT_ID()
MySQL supports stored routines (procedures and functions). A stored routine is a set of SQL statements that can be stored in the server. Once this has been done, clients don't need to keep reissuing the individual statements but can refer to the stored routine instead.
Stored routines require the 'proc' table in the 'mysql' database. This table is created during the MySQL installation procedure. If you are upgrading to MySQL 5.7 from an earlier version, be sure to update your grant tables to make sure that the 'proc' table exists. See *note mysql-upgrade::.
Stored routines can be particularly useful in certain situations:
When multiple client applications are written in different languages or work on different platforms, but need to perform the same database operations.
When security is paramount. Banks, for example, use stored procedures and functions for all common operations. This provides a consistent and secure environment, and routines can ensure that each operation is properly logged. In such a setup, applications and users would have no access to the database tables directly, but can only execute specific stored routines.
Stored routines can provide improved performance because less information needs to be sent between the server and the client. The tradeoff is that this does increase the load on the database server because more of the work is done on the server side and less is done on the client (application) side. Consider this if many client machines (such as Web servers) are serviced by only one or a few database servers.
Stored routines also enable you to have libraries of functions in the database server. This is a feature shared by modern application languages that enable such design internally (for example, by using classes). Using these client application language features is beneficial for the programmer even outside the scope of database use.
MySQL follows the SQL:2003 syntax for stored routines, which is also used by IBM's DB2. All syntax described here is supported and any limitations and extensions are documented where appropriate.
Additional Resources
You may find the Stored Procedures User Forum (https://forums.mysql.com/list.php?98) of use when working with stored procedures and functions.
For answers to some commonly asked questions regarding stored routines in MySQL, see *note faqs-stored-procs::.
There are some restrictions on the use of stored routines. See *note stored-program-restrictions::.
Binary logging for stored routines takes place as described in *note stored-programs-logging::.
File: manual.info.tmp, Node: stored-routines-syntax, Next: stored-routines-privileges, Prev: stored-routines, Up: stored-routines
A stored routine is either a procedure or a function. Stored routines are created with the note 'CREATE PROCEDURE': create-procedure. and note 'CREATE FUNCTION': create-function. statements (see note create-procedure::). A procedure is invoked using a note 'CALL': call. statement (see note call::), 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 note sql-compound-statements::).
Stored routines can be dropped with the note 'DROP PROCEDURE': drop-procedure. and note 'DROP FUNCTION': drop-function. statements (see note drop-procedure::), and altered with the note 'ALTER PROCEDURE': alter-procedure. and note 'ALTER FUNCTION': alter-function. statements (see note alter-procedure::).
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). *note 'USE': 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.
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 *note server-system-variables::, for more information.
MySQL supports a very useful extension that enables the use of regular note 'SELECT': 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 note 'SELECT': 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()' (https://dev.mysql.com/doc/c-api/5.7/en/mysql-real-connect.html) C API function. See mysql_real_connect() (https://dev.mysql.com/doc/c-api/5.7/en/mysql-real-connect.html), and Multiple Statement Execution Support (https://dev.mysql.com/doc/c-api/5.7/en/c-api-multiple-queries.html).
File: manual.info.tmp, Node: stored-routines-privileges, Next: stored-routines-metadata, Prev: stored-routines-syntax, Up: stored-routines
The MySQL grant system takes stored routines into account as follows:
The 'CREATE ROUTINE' privilege is needed to create stored routines.
The 'ALTER ROUTINE' privilege is needed to alter or drop stored routines. This privilege is granted automatically to the creator of a routine if necessary, and dropped from the creator when the routine is dropped.
The 'EXECUTE' privilege is required to execute stored routines. However, this privilege is granted automatically to the creator of a routine if necessary (and dropped from the creator when the routine is dropped). Also, the default 'SQL SECURITY' characteristic for a routine is 'DEFINER', which enables users who have access to the database with which the routine is associated to execute the routine.
If the 'automatic_sp_privileges' system variable is 0, the 'EXECUTE' and 'ALTER ROUTINE' privileges are not automatically granted to and dropped from the routine creator.
The creator of a routine is the account used to execute the 'CREATE' statement for it. This might not be the same as the account named as the 'DEFINER' in the routine definition.
The server manipulates the 'mysql.proc' table in response to statements that create, alter, or drop stored routines. Manual manipulation of this table is not supported.
File: manual.info.tmp, Node: stored-routines-metadata, Next: stored-routines-last-insert-id, Prev: stored-routines-privileges, Up: stored-routines
To obtain metadata about stored routines:
Query the note 'ROUTINES': information-schema-routines-table. table of the 'INFORMATION_SCHEMA' database. See note information-schema-routines-table::.
Use the note 'SHOW CREATE PROCEDURE': show-create-procedure. and note 'SHOW CREATE FUNCTION': show-create-function. statements to see routine definitions. See *note show-create-procedure::.
Use the note 'SHOW PROCEDURE STATUS': show-procedure-status. and note 'SHOW FUNCTION STATUS': show-function-status. statements to see routine characteristics. See *note show-procedure-status::.
Use the note 'SHOW PROCEDURE CODE': show-procedure-code. and note 'SHOW FUNCTION CODE': show-function-code. statements to see a representation of the internal implementation of the routine. See *note show-procedure-code::.
File: manual.info.tmp, Node: stored-routines-last-insert-id, Prev: stored-routines-metadata, Up: stored-routines
Within the body of a stored routine (procedure or function) or a trigger, the value of 'LAST_INSERT_ID()' changes the same way as for statements executed outside the body of these kinds of objects (see *note information-functions::). The effect of a stored routine or trigger upon the value of 'LAST_INSERT_ID()' that is seen by following statements depends on the kind of routine:
If a stored procedure executes statements that change the value of 'LAST_INSERT_ID()', the changed value is seen by statements that follow the procedure call.
For stored functions and triggers that change the value, the value is restored when the function or trigger ends, so following statements do not see a changed value.
File: manual.info.tmp, Node: triggers, Next: event-scheduler, Prev: stored-routines, Up: stored-objects