SQL Statements for Managing Stored Programs

This section summarizes the syntax of the statements used to create, modify, and remove stored programs from the database. This section provides only an overview; we'll drill down into many of the details of these statements in other chapters.

7.3.1. CREATE PROCEDURE

The CREATE PROCEDURE statementyou guessed itcreates a stored procedure. The syntax for the statement is:

 CREATE PROCEDURE procedure_name ([parameter[,...])
 [LANGUAGE SQL]
 [ [NOT] DETERMINISTIC 
 ]
 [ {CONTAINS SQL|MODIFIES SQL DATA|READS SQL DATA|NO SQL} ]
 [SQL SECURITY {DEFINER|INVOKER} ]
 [COMMENT comment_string]
 procedure_statements

The procedure_name follows the normal conventions for the naming of database objects (see Chapter 3).

The parameter list consists of a comma-separated list of arguments that can be provided to the stored procedure. We spent quite a bit of time on parameters in Chapter 3, but to summarize, each parameter is of the form:

 [{IN|OUT|INOUT} ] parameter_name datatype

By default, parameters are of the IN type: this means that their values must be specified by the calling program and that any modifications made to the parameter in the stored program cannot be accessed from the calling program. OUT parameters, on the other hand, can be modified by the stored program, and the modified values can be retrieved from the calling program.

An INOUT parameter acts as both an IN and an OUT parameter: the calling program can supply a value and can see whatever changes are made to the parameter inside the stored procedure.

The following are descriptions of the other keywords you can specify in the CREATE PROCEDURE statement:

 

LANGUAGE SQL

Indicates that the stored procedure uses the SQL:PSM standard stored procedure language. Since MySQL currently supports only those stored procedures written in this language, specifying this keyword is unnecessary at present. However, in future versions, MySQL might support stored procedures written in other languages (Java, for instance), and if this occurs, you may need to specify this keyword.

 

SQL SECURITY {DEFINER|INVOKER}

Determines whether the stored procedure should execute using the permissions of the user who created the stored procedure (DEFINER) or the permissions of the user who is currently executing the stored procedure (INVOKER). The default is DEFINER. We look at the implications of these two security modes in Chapter 18.

 

[NOT] DETERMINISTIC

Indicates whether the stored procedure will always return the same results if the same inputs are provided. For instance, an SQRT function is deterministic because the square root of a number never changes, while an AGE function is nondeterministic because people are getting older all the time (sigh). By default, MySQL will assume that a stored procedure (or function) is NOT DETERMINISTIC .

In fact, the only time this keyword is critical is when you are creating a stored function (but because the CREATE PROCEDURE syntax allows you to specify it, we mention it here): when binary logging is enabled, you need to specify either DETERMINISTIC or one of NO SQL or READS SQL DATA to create your function. This issue is examined in depth in Chapter 10.

 

NO SQL|CONTAINS SQL|READS SQL DATA|MODIFIES SQL DATA

Indicates the type of access to database data that the stored procedure will perform. If a program reads data from the database, you may specify the READS SQL DATA keyword. If the program modifies data in the database, you could specify MODIFIES SQL DATA. If the procedure or function performs no database accesses, you may specify NO SQL.[*]

[*] A strict interpretation of the ANSI standard suggests that NO SQL is only applicable for non-SQL languages (PHP, Java, etc.). Although NO SQL is arguably only really intended for non-SQL stored procedures, the current behavior of MySQL makes the NO SQL clause the best choice when you must specify a SQL clause for a function that performs no database accesses.

 

COMMENT comment_string

Specifies a comment that is stored in the database along with the procedure definition. You can see these comments in the INFORMATION_SCHEMA.ROUTINES table, in the output of SHOW PROCEDURE/FUNCTION STATUS, and in a SHOW CREATE PROCEDURE or SHOW CREATE FUNCTION statement.

The procedure code consists of one or more SQL or stored program language statements. If there is more than one statementand there almost always will bethen the statements must be enclosed in a BEGIN-END block.

7.3.2. CREATE FUNCTION

The CREATE FUNCTION statement creates a stored function. This statement has a very similar syntax to CREATE PROCEDURE:

 CREATE FUNCTION function_name ([parameter[,...])
 RETURNS datatype
 [LANGUAGE SQL]
 [ [NOT] DETERMINISTIC ]
 [ { CONTAINS SQL|NO SQL|MODIFIES SQL DATA|READS SQL DATA} ]
 [SQL SECURITY {DEFINER|INVOKER} ]
 [COMMENT comment_string]
 function_statements

There are only a few fundamental differences between the syntax of CREATE PROCEDURE and that of CREATE FUNCTION:

• CREATE FUNCTION includes a mandatory RETURNS statement that specifies the data type that will be returned from the function call.
• With CREATE FUNCTION, you cannot specify the IN, OUT, or INOUT modifiers to parameters. All parameters are implicitly IN parameters.
• The function body must contain one or more RETURN statements, which terminate function execution and return the specified result to the calling program.

We look at stored functions in detail in Chapter 10.

7.3.3. CREATE TRIGGER

The CREATE TRIGGER statement creates a trigger. Its syntax follows:

 CREATE [DEFINER = { user|CURRENT_USER }] TRIGGER trigger_name
 {BEFORE|AFTER}
 {UPDATE|INSERT|DELETE}
 ON table_name
 FOR EACH ROW
 trigger_statements

As with other stored programs, the trigger name must conform to the general rules for naming objects, as outlined in Chapter 3. There are several differences between this statement syntax and that of CREATE PROCEDURE and CREATE FUNCTION:

 

DEFINER

This optional clause specifies the security privileges that the trigger code will assume when it is invoked. The default CURRENT_USER setting results in the trigger executing with the privileges of the account that executes the CREATE TRIGGER statement. Specifying a user allows the trigger to execute with the privileges of another account.

 

BEFORE or AFTER

These clauses control the sequence in which the trigger will fireeither before or after the triggering statement is executed.

 

UPDATE, INSERT, or DELETE

These clauses specify the type of DML statement that will cause the trigger to be invoked.

 

trigger_statements

This code can be one or more stored program language statements. If more than one statement is specified, they must all be contained within a BEGIN-END block.

Triggers are described in detail in Chapter 11.

7.3.4. ALTER PROCEDURE/FUNCTION

You can use the ALTER statement to change the SQL SECURITY characteristic of a stored procedure or stored function, or to change the comment associated with the procedure or function. This statement cannot currently be issued for triggers. The syntax of this statement is shown below:

 ALTER {PROCEDURE|FUNCTION} procedure_or_function_name
 [SQL SECURITY {DEFINER|INVOKER}]
 [COMMENT comment_string ]

 

7.3.5. DROP PROCEDURE/FUNCTION/TRIGGER

You can use the DROP statement to remove a stored procedure, function, or trigger from the database:

 DROP {PROCEDURE|FUNCTION|TRIGGER} [IF EXISTS] program_name

IF EXISTS is only valid for stored procedures and triggers, not for triggers.

We frequently include a DROP PROCEDURE IF EXISTS statement in the same source file as our CREATE statement to remove the previous definition of the procedure before creating the new version (see Figure 7-10 for an example of this).