Procedures e funções no MySQL

CREATE PROCEDURE sp_name ([parameter[,...]])
[characteristic ...] routine_body

CREATE FUNCTION sp_name ([parameter[,...]])
[RETURNS type]
[characteristic ...] routine_body

parameter:
 [ IN | OUT | INOUT ] param_name type

type:
 Any valid MySQL data type

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

routine_body:
 Valid SQL procedure statement(s)

A cláusula RETURNS pode ser especificada apenas por uma FUNCTION. É usada para indicar o tipo de retorno da função, e o corpo da função deve conter uma instrução RETURN value.

A lista de parâmetros entre parenteses deve estar sempre presente. Se não houver parâmetros, uma lista de parâmetros vazia de () deve ser usada. Cada parâmetro é um parâmetro IN por padrão. Para especificar outro tipo de parâmetro, use a palavra chave OUT ou INOUT antes do nome do parâmetro. Especificar IN, OUT ou INOUT só é valido para uma PROCEDURE.

A instrução CREATE FUNCTION é usada em versão novas do MySQL para suporte a UDFs (User Defined Functions - Funções Definidas pelo Usuário). As UDFs continuam a ser suportadas, mesmo com a existencia de stored functions. Uma UDF pode ser considerada como uma stored function externa. No entanto, note que stored functions compartilham os seus namespace com as UDFs.

Um framework para stored procedures externas serão introduzidas em um futuro próxima. Isto permitirá que você escreva stored procedures em outras linguagens além de SQL. Provavelmente, uma das primeiras linguagens a ser suportada sea PHP, já que o mecanismo do PHP é pequeno, seguro com threads e pode facilmente ser embutido. Como o framework será publico, é esperado que muitas outras linguagens também sejam suportadas.

Uma função é considerada "deterministica" se ela sempre retorna o mesmo resultado para os mesmos parâmetros de entrada, e "não deterministica" caso contrário. O otimizado pode usar este fato. Atualmente, a característica DETERMINISTIC é aceita, mas ainda não é usada.

A característica SQL SECURITY pode ser usada para especificar se a rotina deve ser executada usando as permissões do usuário que criou a rotina, ou o usuário que a chamou. O valor padrão é DEFINER. Este recurso é novo no SQL:2003.

O MySQL ainda não usa o privilégio GRANT EXECUTE. Assim ,por enquanto, se um procedimento p1() chama a tabela t1,o usuário deve ter privilégios na tabela t1 para chamar o procedimento p1() com sucesso.

MySQL stores the SQL_MODE settings in effect at the time a routine is created, 
and will always execute routines with these settings in force.

A cláusula COMMENT é uma extensão do MySQL, e pode ser usada para descrever o stored procedure. Esta informação é exibida pelas instruções SHOW CREATE PROCEDURE e SHOW CREATE FUNCTION.

O MySQL permite rotinas contendo instruções DDL (como CREATE e DROP) e instruções de transação SQL (como COMMIT). Isto não é exigido por padrão e depende de especificações de implementação.

A seguir temos um exemplo de uma stored procedure simples que usa um parâmetro OUT. O exemplo usa o comando delimiter do cliente mysql para alterar o delimitador de instrução para antes da definição do procedure. Isto permite que o delimitador ; usado no corpo de procedure seja passado para o servidor em vez de ser interpretado pelo mysql.

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> CALL simpleproc(@a)|
Query OK, 0 rows affected (0.00 sec)

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

A seguir esta um exemplo de uma função que utiliza um parametro, realiza uma operação usando uma função SQL e retorna o resultado:

mysql> delimiter |  
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) 

ALTERAR PROCEDURE E FUNCTION 

ALTER PROCEDURE | FUNCTION sp_name [characteristic ...]

characteristic:
 NAME newname
 | SQL SECURITY {DEFINER | INVOKER}
 | COMMENT string

Este comando pode ser usado para renomear uma stored procedure ou function, e para alterar suas características. Mais de uma mudança pode ser especificada em uma instrução ALTE PROCEDURE ou ALTER FUNCTION.

EXCLUIR PROCEDURE E FUNCTION
DROP PROCEDURE | FUNCTION [IF EXISTS] sp_name

Este comando é usado para deletar uma stored procedure ou function. Isto é, a rotina especificada é removida do servidor. A cláusula IF EXISTS é uma extensão do MySQL. Ela previne que um erro ocorra se o procedimento ou função não existe. Um aviso é produzido e pode ser vizualizado com SHOW WARNINGS.