Stored Routine Syntax

A stored routine is either a procedure or a function. Stored routines are created with CREATE PROCEDURE and CREATE FUNCTION statements. A procedure is invoked using a CALL statement, and can only pass back values using output variables. A function can be called from inside a statement just like any other function (that is, by invoking the function's name), and can return a scalar value. Stored routines may call other stored routines.
CREATE PROCEDURE and CREATE FUNCTION Syntax

CREATE

    [DEFINER = { user | CURRENT_USER }]

    PROCEDURE sp_name ([proc_parameter[,...]])

    [characteristic ...] routine_body

 

CREATE

    [DEFINER = { user | CURRENT_USER }]

    FUNCTION sp_name ([func_parameter[,...]])

    RETURNS type

    [characteristic ...] routine_body

proc_parameter:

    [ IN | OUT | INOUT ] param_name type

   

func_parameter:

    param_name type

 

type:

    Any valid MySQL data type

 

characteristic:

    LANGUAGE SQL

  | [NOT] DETERMINISTIC

  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

  | SQL SECURITY { DEFINER | INVOKER }

  | COMMENT 'string'

 

routine_body:

    Valid SQL procedure statement

delimiter used in the procedure body to be passed through to the server rather than being interpreted by mysql itself.

mysql> delimiter //

 

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)

    -> BEGIN

    ->   SELECT COUNT(*) INTO param1 FROM t;

    -> END;

    -> //

Query OK, 0 rows affected (0.00 sec)

 

mysql> delimiter ;

 

mysql> CALL simpleproc(@a);

Query OK, 0 rows affected (0.00 sec)

 

mysql> SELECT @a;

+------+

| @a   |

+------+

| 3    |

+------+

1 row in set (0.00 sec)

When using the delimiter command, you should avoid the use of the backslash ('\') character because that is the escape character for MySQL.
The following is an example of a function that takes a parameter, performs an operation using an SQL function, and returns the result. In this case, it is unnecessary to use delimiter because the function definition contains no internal ; statement delimiters:

mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)

    -> RETURN CONCAT('Hello, ',s,'!');

Next