Section 16.0. Introduction

16.0. Introduction

This chapter discusses the following kinds of database objects:

Stored routines (functions and procedures)

A stored function performs a calculation and returns a value that can be used in expressions just like a built-in function such as RAND⁠(⁠ ⁠ ⁠), NOW⁠(⁠ ⁠ ⁠), or LEFT⁠(⁠ ⁠ ⁠). A stored procedure performs calculations for which no return value is needed. Procedures are not used in expressions, they are invoked with the CALL statement. A procedure might be executed to update rows in a table or produce a result set that is sent to the client program. One reason for using a stored routine is that it encapsulates the code for performing a calculation. This enables you to perform the calculation easily by invoking the routine rather than by repeating all its code each time.

Triggers

A trigger is an object that is defined to activate when a table is modified. Triggers are available for INSERT, UPDATE, and DELETE statements. For example, you can check values before they are inserted into a table, or specify that any row deleted from a table should be logged to another table that serves as a journal of data changes. Triggers are useful for automating these actions so that you don't need to remember to do them yourself each time you modify a table.

Events

An event is an object that executes SQL statements at a scheduled time or times. You can think of an event as something like a Unix cron job, but that runs within MySQL. For example, events can help you perform administrative tasks such as deleting old table records periodically or creating nightly summaries.

Stored routines and triggers are supported as of MySQL 5.0. Event support begins with MySQL 5.1.

These different kinds of objects have in common the property that they are user-defined but stored on the server side for later execution. This differs from sending an SQL statement from the client to the server for immediate execution. Each of these objects also has the property that it is defined in terms of other SQL statements to be executed when the object is invoked. The object has a body that is a single SQL statement, but that statement can use compound-statement syntax (a BEGIN ... END block) that contains multiple statements. This means that the body can range from very simple to extremely complex. The following stored procedure is a trivial routine that does nothing but set a user-defined variable and for which the body consists of a single SET statement:

CREATE PROCEDURE get_time() SET @current_time = CURTIME(); 

For more complex operations, a compound statement is necessary:

CREATE PROCEDURE part_of_day() BEGIN   CALL get_time();   IF @current_time < '12:00:00' THEN     SET @day_part = 'morning';   ELSEIF @current_time = '12:00:00' THEN     SET @day_part = 'noon';   ELSE     SET @day_part = 'afternoon or night';   END IF; END; 

Here, the BEGIN ... END block contains multiple statements, but is itself considered to constitute a single statement. Compound statements enable you to declare local variables and to use conditional logic and looping constructs. Note also that one stored procedure can invoke another: part_of_day⁠(⁠ ⁠ ⁠) calls get_time⁠(⁠ ⁠ ⁠). These capabilities provide you with considerably more flexibility for algorithmic expression than you have when you write inline expressions in noncompound statements such as SELECT or UPDATE.

The statements within a compound statement must each be terminated by a ; character. That requirement causes a problem if you use the mysql client to define an object that uses compound statements because mysql itself interprets ; to determine statement boundaries. The solution to this problem is to redefine mysql's statement delimiter while you're defining a compound-statement object. Section 16.1 covers how to do this; make sure that you read that recipe before proceeding to those that follow it.

Due to space limitations, this chapter illustrates by example but does not otherwise go into much detail about the extensive syntax for stored routines, triggers, and events. For complete syntax descriptions, see the MySQL Reference Manual.

The scripts for the examples shown in this chapter can be found in the routines, triggers, and events directories of the recipes distribution. Scripts to create some of the tables are in the tables directory.

In addition to the stored routines shown in this chapter, others can be found elsewhere in this book. See, for example, Recipes Section 5.7, Section 6.2, and Section 11.11.

Privileges for Stored Routines, Triggers, and Events

When you create a stored routine, the following privilege requirements must be satisfied or you will have problems:

• To create a stored routine, you must have the CREATE ROUTINE privilege.

• If binary logging is enabled for your MySQL server (which is common practice), there are some additional requirements for creating stored functions (but not stored procedures). These requirements are necessary to ensure that if you use the binary log for replication or for restoring backups, function invocations cause the same effect when reexecuted as they do when originally executed:

  • You must have the SUPER privilege, and you must declare either that the function is deterministic or does not modify data by using one of the DETERMINISTIC, NO SQL, or READS SQL DATA characteristics. (It's possible to create functions that are not deterministic or that modify data, but they might not be safe for replication or for use in backups.)

  • Alternatively, if you enable the log_bin_trust_function_creators system variable, the server waives both of the preceding requirements.

To create a trigger in MySQL 5.0, you must have the SUPER privilege. In MySQL 5.1, you must have the trIGGER privilege for the table associated with the trigger.

To create events, you must have the EVENT privilege for the database in which the events are created.