MySQL Create Procedure and Function

Create Procedure and Function

Create Procedure

   CREATE
         [DEFINER = { user | CURRENT_USER }]
         PROCEDURE sp_name ([proc_parameter[,...]])
         [characteristic ...] routine_body

Create Function

   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



Ex Create Procedure
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)


Ex Create Function
mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
   -> RETURN CONCAT('Hello, ',s,'!');
      Query OK, 0 rows affected (0.00 sec)
mysql> SELECT hello('world');
      +----------------+
      | hello('world') |
      +----------------+
      | Hello, world!  |
      +----------------+
      1 row in set (0.00 sec)