17.2. Stored Routine Syntax

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

characteristic:
    { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

This statement can be used to change the characteristics of a stored procedure or function. As of MySQL 5.0.3, you must have the ALTER ROUTINE privilege for the routine. (That privilege is granted automatically to the routine creator.) If binary logging is enabled, this statement might also require the SUPER privilege, as described in Section 17.4, “Binary Logging of Stored Routines and Triggers”.

More than one change may be specified in an ALTER PROCEDURE or ALTER FUNCTION statement.

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

This statement is used to drop a stored procedure or function. That is, the specified routine is removed from the server. As of MySQL 5.0.3, you must have the ALTER ROUTINE privilege for the routine. (That privilege is granted automatically to the routine creator.)

The IF EXISTS clause is a MySQL extension. It prevents an error from occurring if the procedure or function does not exist. A warning is produced that can be viewed with SHOW WARNINGS.

CALL sp_name([parameter[,...]])

The CALL statement invokes a procedure that was defined previously with CREATE PROCEDURE.

CALL can pass back values to its caller using parameters that are declared as OUT or INOUT parameters. It also “returns” the number of rows affected, which a client program can obtain at the SQL level by calling the ROW_COUNT() function and from C by calling the mysql_affected_rows() C API function.

To get back a value from a procedure using an OUT or INOUT parameter, pass the parameter by means of a user variable, and then check the value of the variable after the procedure returns. (If you are calling the procedure from within another stored procedure or function, you can also pass a routine parameter or local routine variable as an IN or INOUT parameter.) For an INOUT parameter, initialize its value before passing it to the procedure. The following procedure has an OUT parameter that the procedure sets to the current server version, and an INOUT value that the procedure increments by one from its current value:

CREATE PROCEDURE p (OUT ver_param VARCHAR(25), INOUT incr_param INT)
BEGIN
  # Set value of OUT parameter
  SELECT VERSION() INTO ver_param;
  # Increment value of INOUT parameter
  SET incr_param = incr_param + 1;
END;

Before calling the procedure, initialize the variable to be passed as the INOUT parameter. After calling the procedure, the values of the two variables will have been set or modified:

mysql> SET @increment = 10;
mysql> CALL p(@version, @increment);
mysql> SELECT @version, @increment;
+------------+------------+
| @version   | @increment |
+------------+------------+
| 5.0.25-log | 11         | 
+------------+------------+

If you write C programs that execute stored procedures with the CALL SQL statement, you must set the CLIENT_MULTI_RESULTS flag when you call mysql_real_connect(), either explicitly, or implicitly by setting CLIENT_MULTI_STATEMENTS. This is because each CALL returns a result to indicate the call status, in addition to any results sets that might be returned by statements executed within the procedure. To process the result of a CALL statement, use a loop that calls mysql_next_result() to determine whether there are more results. For an example, see Section 22.2.9, “C API Handling of Multiple Statement Execution”.

[begin_label:] BEGIN
    [statement_list]
END [end_label]

BEGIN ... END syntax is used for writing compound statements, which can appear within stored routines and triggers. A compound statement can contain multiple statements, enclosed by the BEGIN and END keywords. statement_list represents a list of one or more statements. Each statement within statement_list must be terminated by a semicolon (;) statement delimiter. Note that statement_list is optional, which means that the empty compound statement (BEGIN END) is legal.

Use of multiple statements requires that a client is able to send statement strings containing the ; statement delimiter. This is handled in the mysql command-line client with the delimiter command. Changing the ; end-of-statement delimiter (for example, to //) allows ; to be used in a routine body. For an example, see Section 17.2.1, “CREATE PROCEDURE and CREATE FUNCTION Syntax”.

A compound statement can be labeled. end_label cannot be given unless begin_label also is present. If both are present, they must be the same.

The optional [NOT] ATOMIC clause is not yet supported. This means that no transactional savepoint is set at the start of the instruction block and the BEGIN clause used in this context has no effect on the current transaction.

The DECLARE statement is used to define various items local to a routine:

The SIGNAL and RESIGNAL statements are not currently supported.

DECLARE is allowed only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

Declarations must follow a certain order. Cursors must be declared before declaring handlers, and variables and conditions must be declared before declaring either cursors or handlers.

You may declare and use variables within a routine.

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

SET var_name = expr [, var_name = expr] ...

SELECT col_name[,...] INTO var_name[,...] table_expr

SELECT id,data INTO x,y FROM test.t1 LIMIT 1;

CREATE PROCEDURE sp1 (x VARCHAR(5))
  BEGIN
    DECLARE xname VARCHAR(5) DEFAULT 'bob';
    DECLARE newname VARCHAR(5);
    DECLARE xid INT;
    
    SELECT xname,id INTO newname,xid 
      FROM table1 WHERE xname = xname;
    SELECT newname;
  END;

Certain conditions may require specific handling. These conditions can relate to errors, as well as to general flow control inside a routine.

DECLARE condition_name CONDITION FOR condition_value

condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | mysql_error_code

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

handler_type:
    CONTINUE
  | EXIT
  | UNDO

condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
  | mysql_error_code

mysql> CREATE TABLE test.t (s1 int,primary key (s1));
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //

mysql> CREATE PROCEDURE handlerdemo ()
    -> BEGIN
    ->   DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
    ->   SET @x = 1;
    ->   INSERT INTO test.t VALUES (1);
    ->   SET @x = 2;
    ->   INSERT INTO test.t VALUES (1);
    ->   SET @x = 3;
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> CALL handlerdemo()//
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x//
    +------+
    | @x   |
    +------+
    | 3    |
    +------+
    1 row in set (0.00 sec)

DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;