Team LiB
Previous Section Next Section

Stored Routine Syntax

This section describes the syntax for statements that pertain to stored procedures and stored functions.

ALTER FUNCTION, ALTER PROCEDURE

ALTER {FUNCTION | PROCEDURE} routine_name [characteristic] ...

characteristic:
    [NOT] DETERMINISTIC
  | LANGUAGE SQL
  | SQL SECURITY {DEFINER | INVOKER}
  | COMMENT 'string'

These statements alter the characteristics of stored routines. The characteristics are as described in the entry for the CREATE FUNCTION and CREATE PROCEDURE statements.

These statements were introduced in MySQL 5.0.0. As of MySQL 5.0.3, they require the ALTER ROUTINE privilege for the given routine.

CALL

CALL routine_name([param_list])

Invokes the stored procedure that has the given name. The optional parameter list, param_list, consists of one or more parameter values separated by comma. If any of these are OUT or INOUT parameters, the procedure can return values through them.

When the stored routine returns, you can get the rows-affected value for its most recent statement that modifies rows by invoking the ROW_COUNT() function. From C, the same value can be obtained by calling mysql_affected_rows().

This statement was introduced in MySQL 5.0.0.

CREATE FUNCTION, CREATE PROCEDURE

CREATE FUNCTION routine_name ([func_param [, func_param] ...])
  RETURNS type
  [characteristic] ...
  body
CREATE PROCEDURE routine_name ([proc_param [, proc_param] ...])
  [characteristic] ...
  body

func_param:
  param_name type

proc_param:
  [IN | OUT | INOUT] param_name type

characteristic:
    [NOT] DETERMINISTIC
  | LANGUAGE SQL
  | SQL SECURITY {DEFINER | INVOKER}
  | COMMENT 'string'

body:
  A SQL statement

These statements create new stored functions and stored procedures.

By default, the routine is created in the current database. To create the routine in a specific database, give the name in db_name.routine_name format. There cannot be two functions or two procedures in the same database. However, there can be a function and a procedure with the same name.

Parameters for functions are defined by giving the function name and its type. The type is any valid MySQL data type. Parameters supply values to a function when it is invoked, but changes to the parameters are not visible to the caller when the function returns. (That is, they are treated as IN parameters.)

For a function, a RETURNS statement must follow the parameter list to indicate the data type for the return value.

Parameters for procedures also are defined with a name and type, but the name can be preceded by IN, OUT, or INOUT to indicate that the parameter is input-only, output-only, or both input and output:

  • An IN parameter supplies a value to the procedure. The parameter can be modified inside the procedure, but remains unchanged in the calling program after the procedure terminates.

  • An OUT parameter does not supply a value to the procedure. Its initial value inside the procedure is NULL and it can be modified inside the procedure. Its final value is visible to the calling program after the procedure terminates.

  • An INOUT parameter supplies a value to the procedure and any changes to its value within the procedure become visible to the caller.

If none of these keywords are given, the default is IN.

One or more characteristic values can be given, separated by spaces:

  • DETERMINISTIC, NOT DETERMINISTIC

    DETERMINISTIC indicates that a function always produces the same result when called with the same parameter values. NOT DETERMINISTIC indicates that it might not. Currently, these characteristics have no effect. They provide information that might be used by the query optimizer in the future.

  • LANGUAGE SQL

    Indicates the language of the routine. This is parsed and ignored currently. In MySQL, SQL is the only supported stored routine language, so this directive is unneeded. However, if you're thinking about porting your stored routines to another database system that supports multiple languages, you might want to include a LANGUAGE directive to specify SQL explicitly.

  • SQL SECURITY

    Indicates how to check privileges when the routine is invoked. For DEFINER (the default), MySQL checks privileges against the account of the user who created the routine. For INVOKER, MySQL checks privileges against the account of the user who invokes the routine. As of MySQL 5.0.3, the account against which privileges are checked must have the EXECUTE and ALTER ROUTINE privileges are granted automatically. The EXECUTE privilege is granted automatically to the routine creator.

  • COMMENT

    Associates a descriptive comment with the routine. The comment is displayed by the SHOW CREATE FUNCTION, SHOW CREATE PROCEDURE, SHOW FUNCTION STATUS, and SHOW PROCEDURE STATUS statements.

body represents the body of the routine. It should be a single SQL statement. If you need to use multiple statements, enclose them within the BEGIN … END compound statement construct.

Each statement in the body must be terminated by a semicolon character (';'). If you use the mysql program to create a stored routine that has a multiple-statement body, you should temporarily redefine the mysql statement delimiter so that it does not recognize ';' itself. You can do this with the delimiter command. Also, be sure to choose as your delimiter something that does not occur within the statements that define the routine. For example:

mysql> delimiter $
mysql> CREATE FUNCTION myfunc ()
    -> RETURNS INT
    -> BEGIN
    -> DECLARE i INT;
    -> DECLARE j INT;
    -> SET i = 2;
    -> SET j = 4;
    -> RETURN i * j;
    -> END$
mysql> delimiter ;
mysql> SELECT myfunc();
+----------+
| myfunc() |
+----------+
|        8 |
+----------+

The value of the sql_mode system variable that is in effect at routine creation time is saved and used when the routine executes later.

Stored procedures can refer to tables, but stored functions currently cannot.

The CREATE FUNCTION and CREATE PROCEDURE statements were introduced in MySQL 5.0.0. As of MySQL 5.0.3, they require the CREATE ROUTINE privilege for the given routine.

DROP FUNCTION, DROP PROCEDURE

DROP {FUNCTION | PROCEDURE} [IF EXISTS] routine_name

Removes the named stored function or stored procedure.

The IF EXISTS clause may be specified to suppress the error that normally results if the routine does not exist. In this case, a warning is generated instead.

These statements were introduced in MySQL 5.0.0. As of MySQL 5.0.3, they require the ALTER ROUTINE privilege for the given routine.

SHOW CREATE FUNCTION, SHOW CREATE PROCEDURE

SHOW CREATE {FUNCTION | PROCEDURE} routine_name

Displays the SHOW CREATE FUNCTION or SHOW CREATE PROCEDURE that creates the named routine.

These statements were introduced in MySQL 5.0.0.

SHOW FUNCTION STATUS, SHOW PROCEDURE STATUS

SHOW {FUNCTION | PROCEDURE} STATUS [LIKE 'pattern']

These statements display descriptive information about the stored functions or procedures in the default database. The LIKE clause may be included to display information only for routines with names that match the given pattern.

The output from these statements includes the following columns:

  • Db

    The database containing the stored routine.

  • Name

    The routine name.

  • Type

    The routine type, which is either FUNCTION or PROCEDURE.

  • Definer

    The account that created the routine, in 'user_name'@'host_name ' format.

  • Modified

    The time when the routine was last modified.

  • Created

    The time when the routine was created.

  • Security_type

    The SQL SECURITY characteristic, either DEFINER or INVOKER.

  • Comment

    The COMMENT characteristic.

These statements were introduced in MySQL 5.0.0.

Control Structure Statements

The statements in this section are used to group statements into blocks and provide flow-control constructs. Each occurrence of stmt_list in the syntax for these statements indicates a list of one or more statements, each terminated by a semicolon character (';').

Some of the constructs can be labeled (BEGIN, LOOP, REPEAT, and WHILE). Labels are not case sensitive but must follow these rules:

  • If a label appears at the beginning of a construct, a label with the same name may also appear at the end.

  • A label may not appear at the end without a matching label at the beginning.

BEGIN ... END
BEGIN [stmt_list] END

label: BEGIN [stmt_list] END [label]

The BEGIN … END construct creates a block within which multiple statements can be grouped. If a stored routine body needs to contain more than one statement, they must be grouped within a BEGIN block. Also, if the routine contains any DECLARE statements, they can appear only at the beginning of a BEGIN block.

This statement was introduced in MySQL 5.0.0.

CASE
CASE [expr]
  WHEN expr1 THEN stmt_list1
  [WHEN expr2 THEN stmt_list2] ...
  [ELSE stmt_list]
END IF

The CASE statement provides a branching flow-control construct. When the initial expression, expr, is present, CASE compares it to the expression following each WHEN. For the first one that is equal, the statement list for the corresponding THEN value is executed. This is useful for comparing a given value to a set of values.

When the initial expression, expr, is not present, CASE evaluates WHEN expressions. For the first one that is true (not zero and not NULL), the statement list for the corresponding THEN value is executed. This is useful for performing non-equality tests or testing arbitrary conditions.

If no WHEN expression matches, the statement list for the ELSE clause is executed, if there is one.

This statement was introduced in MySQL 5.0.0. Note that it differs from the CASE construct described in "Comparison Functions," in Appendix C, "Operator and Function Reference."

IF
IF expr1 THEN stmt_list1
  [ELSEIF expr2 THEN stmt_list2] ...
  [ELSE stmt_list]
END IF

The IF statement provides a branching flow-control construct. If the expression following the IF keyword is true, the statement list following the initial THEN is executed. Otherwise, expressions for any following ELSEIF clauses are evaluated. For the first one that is true, the corresponding statement list is executed. If no expression is true, the statement list for the ELSE clause is executed, if there is one.

This statement was introduced in MySQL 5.0.0. Note that it differs from the IF() function described in "Comparison Functions," in Appendix C.

ITERATE
ITERATE label

The ITERATE statement is used within looping constructs to begin the next iteration of the loop. It can appear within LOOP, REPEAT, and WHILE.

This statement was introduced in MySQL 5.0.0.

LEAVE
LEAVE label

The LEAVE statement is used to exit a labeled flow-control construct. The statement must appear within the construct that has the given label.

This statement was introduced in MySQL 5.0.0.

LOOP
LOOP stmt_list END LOOP

label: LOOP stmt_list END LOOP [label]

This statement sets up an execution loop. The statements within the loop are executed repeatedly until control is transferred out of the loop.

This statement was introduced in MySQL 5.0.0.

REPEAT
REPEAT stmt_list UNTIL expr END REPEAT

label: REPEAT stmt_list UNTIL expr END REPEAT [label]

This statement sets up an execution loop. The statements within the loop are executed repeatedly until the expression expr is true.

This statement was introduced in MySQL 5.0.0.

RETURN
RETURN expr

The RETURN statement is used only within stored functions, not stored procedures. When executed, it terminates execution of the function. The value of expr becomes the value returned to the statement that invoked the function. There can be multiple RETURN statements within a function, but there must be at least one.

This statement was introduced in MySQL 5.0.0.

WHILE
WHILE expr DO stmt_list END WHILE

label: WHILE expr DO stmt_list END WHILE [label]

This statement sets up an execution loop. The statements within the loop are executed repeatedly as long as the expression expr is true.

This statement was introduced in MySQL 5.0.0.

Declaration Statements

The DECLARE statement is used for declaring local variables, conditions, cursors, and handlers.

DECLARE
DECLARE var_name [, var_name] ... type [DEFAULT value]

DECLARE condition_name CONDITION FOR named_condition

named_condition: {SQLSTATE [VALUE] sqlstate_value | mysql_errno}

DECLARE cursor_name CURSOR FOR select_stmt

DECLARE handler_type
  HANDLER FOR handler_condition [, handler_condition] ...
  statement

handler_type: {CONTINUE | EXIT}

handler_condition:
  {
      SQLSTATE [VALUE] sqlstate_value
    | mysql_errno
    | condition_name
    | SQLWARNING
    | NOT FOUND
    | SQLEXCEPTION
  }

Declares local variables, conditions, cursors, and handlers. DECLARE can appear only at the beginning of a BEGIN block. If multiple declarations occur, they must appear in this order:

  • Variable and condition declarations

  • Cursor declarations

  • Handler declarations

DECLARE followed by a list of comma-separated variables declares local variables for use within the routine. A local variable is accessible within the BEGIN block where it is declared and any nested blocks, but not in any outer blocks.

A local variable can be initialized in the DECLARE statement with a DEFAULT clause. If there is no DEFAULT clause, the initial value is NULL. To assign a value to a local variable later in the routine, use a SET statement or a SELECT … INTO var_name statement.

DECLARE … CONDITION creates a name for a condition. The name can be referred to in a DECLARE … HANDLER statement. named_condition can be either an SQLSTATE value represented as a five-character quoted string or a numeric MySQL-specific error number.

DECLARE … CURSOR declares a cursor to be associated with the given SELECT statement, which should not contain an INTO clause. The cursor can be opened with an OPEN statement, used with FETCH to retrieve rows, and closed with CLOSE.

DECLARE … HANDLER associates one or more conditions with a statement to be executed when any of the conditions occur. The handler_type value indicates what happens after the condition statement executes. With CONTINUE, execution continues. With EXIT, the current BEGIN block terminates.

handler_condition can be any of the following types of values:

  • A SQLSTATE value represented as a five-character quoted string.

  • A numeric MySQL-specific error number.

  • A named condition previously declared with DECLARE … CONDITION.

  • SQLWARNING, which catches any SQLSTATE value that begins with 01.

  • NOT FOUND, which catches any SQLSTATE value that begins with 02.

  • SQLEXCEPTION, which catches any SQLSTATE value not caught by SQLEXCEPTION or NOT FOUND.

The DECLARE statement was introduced in MySQL 5.0.0.

Cursor Statements

The statements in this section enable you to open and close cursors, and to use them for fetching rows while open. Cursors currently are read-only and can be used only to move forward within a result set (that is, they are not scrollable).

CLOSE
CLOSE cursor_name

Closes the given cursor, which must be open. An open cursor is closed automatically when the BEGIN block within which the cursor was declared ends.

This statement was introduced in MySQL 5.0.0.

FETCH
FETCH [[NEXT] FROM] cursor_name INTO var_name [, var_name] ...

Fetches the next row from the given cursor into the named variable or variables. The cursor must be open. If no row is available, an error with an SQLSTATE value of 02000 occurs.

This statement was introduced in MySQL 5.0.0.

OPEN
OPEN cursor_name

Opens the given cursor so that it can be used with FETCH.

This statement was introduced in MySQL 5.0.0.

    Team LiB
    Previous Section Next Section