Syntax - MySQL

As we mentioned already, to create a new stored procedure or stored function, you use the CREATE PROCEDURE or CREATE FUNCTION command, respectively. Let’s examine the syntax for these commands.

CREATE PROCEDURE | FUNCTION procedure_name (parameter1[, parameter2,...] )
[RETURNS type]
[ LANGUAGE SQL | [NOT] DETERMINISTIC |
  SQL SECURITY {DEFINER | INVOKER} | COMMENT string]
BEGIN
sql_statement(s)
END
delimiter

The CREATE keyword is followed by PROCEDURE or FUNCTION depending on which one you want to create. The procedure_name is simply the name by which you wish to refer to the stored procedure or stored function, and can be any legal MySQL identifier. This is followed by a list of zero or more parameters—even if there are none, the parentheses are required (think of a PHP or JavaScript func tion declaration here). For stored procedures, each parameter takes the form

[ IN | OUT | INOUT ] parameter_name type

where IN , OUT , or INOUT specifies whether the parameter is input, output, or both ( IN is the default value). This is followed by the name of the parameter and its type, which may be any valid MySQL datatype, just as you’d use as part of a column definition in a CREATE TABLE statement. The IN , OUT , and INOUT keywords are not used with stored functions, as stored functions can have input parameters only.

---

TIP  If you’ve had experience in programming in languages that make use of them, then you can think of INOUT parameters as being somewhat like passing values by reference or using pointers; the variable itself is changed by the procedure. We’ll provide an example of a stored procedure using INOUT parameters a bit later (see the “IF … ELSEIF … ELSE” section).

---

If you’re defining a stored function, you can specify a return type using RETURNS . Remember that only a stored function may return a value, and the body of the function must contain a RETURN statement. It’s important to remember that the parentheses are still required even in cases where your procedure doesn’t have any input or output parameters at all; this lets MySQL know not to expect any.

---

NOTE  From this point on in our discussion, we’ll use the term “procedure” to mean either a stored procedure or a stored function, and we’ll make it clear if what we’re saying applies only to one or the other.

---

Each of the next set of clauses, also known as the procedure’s “characteristics,” is optional. In MySQL 5.0.0 and 5.0.1, the LANGUAGE clause accepts only SQL as its argument, although this will probably change in the future, as there are plans to introduce support for additional languages in stored procedures and stored functions. PHP is a very strong candidate in this regard, and it’s likely to be the first external language to be supported. The DETERMINISTIC keyword means that the stored procedure or stored function is always supposed to produce the same result for the same input parameters, and so NOT DETERMINISTIC means that the result may be different for different invocations of the procedure (or function), even if the input is the same. This clause is accepted but not actually supported in MySQL 5.0.0.

In addition, the SQL SECURITY clause can be used to determine whether the privileges of the procedure’s creator or user should be in effect when the procedure is invoked. In the current MySQL 5.0 alpha versions, in order to access tables referenced in the procedure, the user calling the procedure must have the appropriate permissions on those tables. This is almost certain to be fixed by the time MySQL 5.0 is released for production use. MySQL also supports comments for procedures.

Once created, stored procedures and stored functions are stored in the mysql.proc table indefinitely. In order to delete a procedure, you can use DROP PROCEDURE and DROP FUNCTION along with the name of the procedure; for example, in order to delete a stored procedure named myproc, you would execute the statement

DROP PROCEDURE myproc;

To view the statement used to create a procedure, use the SHOW CREATE PROCEDURE or SHOW CREATE FUNCTION statement. To see the definition of a function named myfunc, you’d use

SHOW CREATE FUNCTION myfunc;

You can also alter some characteristics of a procedure using the ALTER PROCEDURE or ALTER FUNCTION command:

ALTER PROCEDURE | FUNCTION procname
  NAME newname | SQL SECURITY {DEFINER | INVOKER} | COMMENT comment

---

CAUTION  CREATE PROCEDURE , SHOW CREATE PROCEDURE, DROP PROCEDURE , and ALTER PROCEDURE work only with stored procedures. CREATE FUNCTION , SHOW CREATE FUNCTION , DROP FUNCTION, and ALTER FUNCTION work only with stored functions. For example, you can’t use DROP PROCEDURE to drop a stored function.

---

The body of the stored procedure or stored function comes between the BEGIN and END keywords, and may contain nearly any valid SQL statements, subject only to the limitation that in MySQL 5.0, only stored procedures (but not stored functions) may refer to tables. They may also contain some flow-control, looping, and variable-declaration constructs that are specific to stored procedures and stored functions. See the sections “Flow Control in Stored Procedures,” “Looping in Procedures,” and the sidebar “Declaring Variables Within Procedures” for more about these.

---

TIP  While some databases don’t permit you to create, alter, or drop tables within a stored procedure, MySQL does allow you to do so. You’ll see an example of creating a table inside a stored procedure when we discuss cursors later in this section. <

---