How to Create and execute stored routines in MySQL

Stored Routines

A stored routine is a named set of SQL statements that are stored on the server. Clients do not need to keep re-issuing the individual statements but can refer to the stored routine instead.

Stored routine types:

• Stored procedures: Invoke procedures with a CALL statement. They can pass back values using output variables or result sets.
• Stored functions: Call functions inside a statement. They return scalar values.

Uses of Stored Routines

Client Functionality

With stored routines, you can create a statement or series of statements centrally in the database to be used by multiple client applications written in different programming languages, or that work on different platforms.

Security

Stored routines provide an outlet for those applications that require the highest level of security. Banks, for example, use stored procedures and functions for all common operations. This provides a consistent and secure environment. Routines can be coded to ensure that each operation is properly logged. In such a setup, applications and users have no access to the database tables directly and can execute only specific stored routines.

Performance

Stored routines can improve performance because less information needs to be sent between the server and the client. The client invokes the stored routine by name rather than passing through all statements that comprise the stored routine.

Function Libraries

Stored routines allow for libraries of functions in the database server. These libraries act as an API to the database.

Stored Routines: Issues

Increased Server Load

Executing stored routines in the database itself can increase the server load and reduce the performance of the applications. Apply testing and common sense to ensure that the convenience of having logic in the database itself outweighs any performance issues that may arise.

Limited Development Tools

Development tools that support stored routines in MySQL are not as mature or well specified as in more general-purpose programming languages. This limitation can make writing and debugging stored routines a much more difficult process and needs to be considered in the decision process.

Limited Language Functionality and Speed

Even though having logic in the database itself is a very significant advantage in many situations, there are limitations on what can be accomplished in comparison to other programming languages. Stored routines execute in the context of the database and provide good performance when processing lots of data compared to routines in client applications, but client application languages may have stronger, more general-purpose processing, integration, or other library features. You must consider the range of functionality required to ensure that you are using the best possible solution for each routine.

Executing Stored Routines

The commands for calling stored routines are very similar to other commands in MySQL. Invoke stored procedures by using a CALL statement. Stored procedures pass back values using output variables or result sets. Invoke functions from inside a statement just like any other function (that is, by invoking the function’s name); functions return a scalar value. Each stored routine is associated with a particular database. This has several implications:

• USE [database]: When you invoke a routine, MySQL executes an implicit USE [database] for the duration of the routine. You cannot issue a USE statement within a stored routine.
• Qualify names: You can qualify a routine name with its database name. Do this 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, use CALL test.p() or test.f().
• Database deletions: When you drop a database, all stored routines associated with it are also dropped.

MySQL allows the use of regular SELECT statements inside a stored procedure. The result set of such a query is sent directly to the client.

Stored Procedure: Example

mysql> DELIMITER //
mysql> CREATE PROCEDURE record_count ()
    -> BEGIN
    -> SELECT 'Country count ', COUNT(*)
    -> FROM Country;
    -> SELECT 'City count ', COUNT(*) FROM
    -> City;
    -> SELECT 'CountryLanguage count',
    -> COUNT(*) FROM CountryLanguage;
    -> END//
mysql> DELIMITER ;

Compound Statements

You can use BEGIN…END syntax in stored routines and triggers to create a compound statement. The BEGIN…END block can contain zero or more statements. An empty compound statement is legal, and there is no upper limit on the number of statements within a compound statement

Delimiter

Within the BEGIN…END syntax, you must terminate each statement with a semicolon (;). Because the mysql client uses the semicolon as the default terminating character for SQL statements, you must use the DELIMITER statement to change this when using the MySQL command-line client interactively or for batch processing.

In the example above, the first DELIMITER statement makes the terminating SQL statement character two forward slashes (//). This change ensures that the client does not interpret the semicolons in the compound statement as statement delimiters, and ensures that the client does not prematurely send the CREATE PROCEDURE statement to the server. When the statement creating the stored routine terminates with //, the client sends the statement to the server before issuing the second DELIMITER statement to reset the statement delimiter to a semicolon.

Stored Function: Example

mysql> DELIMITER //
mysql> CREATE FUNCTION pay_check (gross_pay
    -> FLOAT(9,2), tax_rate FLOAT (3,2))
    -> RETURNS FLOAT(9,2)
    -> NO SQL
    -> BEGIN
    -> DECLARE net_pay FLOAT(9,2)
    -> DEFAULT 0;
    -> SET net_pay=gross_pay - gross_pay *
    -> tax_rate;
    -> RETURN net_pay;
 -> END//
mysql> DELIMITER ;

RETURNS Clause

The RETURNS clause identifies the type of value to be returned by this function.

Characteristics

Several characteristics provide information about the nature of data used by the routine. In MySQL, these characteristics are advisory only. The server does not use them to constrain what kinds of statements a routine is permitted to execute.

• CONTAINS SQL indicates that the routine does not contain statements that read or write data. This is the default if none of these characteristics are given explicitly.
• NO SQL indicates that the routine contains no SQL statements.
• READS SQL DATA indicates that the routine contains statements that read data (for example, SELECT) but not statements that write data.
• MODIFIES SQL DATA indicates that the routine contains statements that may write data (for example, INSERT or DELETE).

Note: When you create a function with binary logging enabled, MySQL produces an error if you do not specify one of the following: NO SQL, READS SQL DATA, or DETERMINISTIC.

DECLARE Statement

Use the DECLARE statement to declare local variables and to initialize user variables in stored routines. You can add the DEFAULT clause to the end of the DECLARE statement to specify an initial value for the user variable. If you leave out the DEFAULT clause, the initial value for the user variable is NULL.

SET Statement

The SET statement allows you to assign a value to defined variables by using either = or := as the assignment operator.

RETURN Statement

The RETURN statement terminates execution of a stored function and returns the value expression to the function caller.

Examine Stored Routines

‘SHOW CREATE PROCEDURE’ and ‘SHOW CREATE FUNCTION’

These statements are MySQL extensions and are similar to SHOW CREATE TABLE. These statements return the exact string that can be used to re-create the named routine. One of the main limitations of these statements is that you must know the name of the procedure or function and must know whether it is a procedure or function before you can attempt to view the information.

‘SHOW PROCEDURE STATUS’ and ‘SHOW FUNCTION STATUS’

These statements are specific to MySQL. They return characteristics of routines, such as the database, name, type, creator, and creation and modification dates. These statements have the advantage of being able to display specific routines based on a LIKE pattern. If no pattern is specified, the information for all stored procedures or all stored functions is listed, depending on which statement is used. For example, the following statement shows information about procedures that have a name beginning with “film”:

SHOW PROCEDURE STATUS LIKE 'film%'\G

INFORMATION_SCHEMA.ROUTINES

The INFORMATION_SCHEMA.ROUTINES table contains information about stored routines (both procedures and functions) and returns the majority of the details that can be found in both the SHOW CREATE … and SHOW … STATUS statements to include the actual syntax used to create the stored routines. Of the three options, this table holds the most complete picture of the stored routines available in the databases.

Example:

mysql> SELECT routine_name, routine_schema, routine_type, definer
     > FROM INFORMATION_SCHEMA.ROUTINES
     > WHERE routine_name LIKE 'film%';
+-------------------+----------------+--------------+----------------+
| routine_name      | routine_schema | routine_type | definer        |
+-------------------+----------------+--------------+----------------+
| film_in_stock     | sakila         | PROCEDURE    | root@localhost |
| film_not_in_stock | sakila         | PROCEDURE    | root@localhost |
+-------------------+----------------+--------------+----------------+
2 rows in set (0.00 sec)

Tables in the mysql System Database Associated with Programming Components

The mysql system database contains tables that provide information associated with the stored routine features of MySQL. The tables include:

• The mysql.event table, which contains information about the events stored on the MySQL server
• The mysql.proc table, which contains information about the stored procedures and functions on the MySQL server
• The mysql.procs_priv table, which provides the access control grant details for users in reference to stored procedures

Stored Routines and Execution Security

Several privileges apply to the use of stored procedures and functions.

Default Actions

When you create a stored routine, MySQL automatically grants the EXECUTE and ALTER ROUTINE privileges to your account for that routine. These privileges can be revoked or removed later by a user who has the privilege being revoked and who also has the GRANT OPTION privilege. You can verify these privileges by issuing a SHOW GRANTS statement after creating a routine.

Granting Privileges

The GRANT ALL statement, when granting all privileges at a global or database level, includes all stored routine privileges except GRANT OPTION.

GRANT examples:

mysql> GRANT ALL ON world_innodb.* TO
    -> 'magellan'@'localhost';
mysql> GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE
    -> world_innodb.record_count TO
    -> 'magellan'@'localhost';

Privilege	Operations Allowed
CREATE ROUTINE	Create stored routines.
ALTER ROUTINE	Alter or drop stored routines.
EXECUTE	Execute stored routines.
GRANT OPTION	Grant privileges to other accounts.

To grant the GRANT OPTION privilege, include the WITH GRANT OPTION clause at the end of the statement. You can grant the EXECUTE, ALTER ROUTINE, and GRANT OPTION privileges at the individual routine level, but only for routines that already exist. To grant privileges for an individual routine, qualify the routine with its database name, and provide the keyword PROCEDURE or FUNCTION to indicate the routine type, as in the following example: GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE world_innodb.record_count TO ‘magellan’@’localhost’ WITH GRANT OPTION;

SIGNAL and RESIGNAL

MySQL supports the use of SIGNAL and RESIGNAL to raise a specific SQLSTATE. It is similar to exception handling in other languages. It can be also used for advanced flow control when handling errors.

Use SIGNAL to throw an error or warning state and RESIGNAL to forward an error or warning state originated by a prior SIGNAL. These commands provide a way for stored routines and triggers to return an error to application programs or the end user. SIGNAL provides error information to a handler, to an outer portion of the application, or to the client. For example, use SIGNAL within a stored procedure to throw an error to the code that called the stored procedure. That code can handle the error in an appropriate way.

RESIGNAL passes on the error condition information that is available during execution of a condition handler. Use RESIGNAL within a compound statement inside a stored procedure or function, trigger, or event. For example, if stored procedure p calls stored procedure q, and if q uses SIGNAL to throw an error, p can declare a handler to process signals from q. The handler in p can use the RESIGNAL statement to throw the same error information to the code that calls p.