CREATE FUNCTION

Sintaxe

CREATE [OR REPLACE]
    [DEFINER = {user | CURRENT_USER | role | CURRENT_ROLE}]
    FUNCTION [IF NOT EXISTS] func_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...]
    RETURN func_body
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'
func_body:
    Valid SQL procedure statement

Descrição

Use a instrução CREATE FUNCTION para criar uma nova função. Você deve possuir o privilégio CREATE ROUTINE para usar CREATE FUNCTION.

Uma função aceita varios argumentos e retorna um valor do corpo da função. O corpo da função pode ser qualquer expressão SQL válida, como você poderia usar por exemplo, em qualquer expressão select. Se você tem os privilégios apropriados, você pode chamar a função exatamente como você o faria com qualquer função incorporada. Veja Security abaixo para obter detalhes sobre privilégios.

Você também pode usar uma variante da instrução CREATE FUNCTION para instalar uma função definida pelo usuário (UDF), definida por um plugin. Veja CREATE FUNCTION (UDF) para obter detalhes.

Você pode usar uma instrução SELECT para o corpo da função, colocando ela entre parênteses, exatamente como você o faria ao usar um subselect para qualquer outra expressão. A instrução SELECT deve retornar um único valor. Se mais de uma coluna é retornada quando a função é chamada, ocorrerá o erro 1241. Se mais de uma linha é retornada quando a função é chamada, ocorrerá um erro 1242. Use uma cláusula LIMIT para garantir que somente irá ser retornada uma linha.

Você também pode substituir a cláusula RETURN com a instrução composta BEGIN...END#. A instrução composta deve conter uma instrução RETURN. Quando a função for chamada, a instrução RETURN imediatamente retornará seu resultado e quaisquer instrução após RETURN será ignorada efetivamente.

Por padrão, uma função está associada com o banco de dados padrão. Para associar uma função explicitamente com um banco de dados determinado, especifique o nome plenamente qualificado como db_name.func_name ao você criá-la. Se o nome da função é igual ao nome de uma função incorporada, você deve usar o nome plenamente qualificado ao chamá-la.

A lista de parâmetros colocada entre parênteses deve estar sempre presente. Se não tiver parâmetros , uma lista vazia de parâmetros de () deveria ser usada. Os nomes de parâmetro não são sensíveis a letras maiúsculas e minúsculas.

Cada parâmetro pode ser declarado usando qualquer tipo de dado válido, exceto que o atributo COLLATE não possa ser usado.

A cláusula RETURNS especifica o tipo de retorno da função. Se a cláusula RETURN retorna o valor de um tipo diferente. o valor é forçado para o tipo adequado. Por exemplo, se uma função especifica o valor ENUM ou o valor SET na cláusula RETURNS, mas a cláusula RETURN retorna um inteiro, o valor retornado pela função é o string para o membro ENUM correspondente do conjunto de membros SET.

O MySQL armazena a configuração da variável de sistema sql_mode que está em vigor no momento em que a rotina é criada, e sempre executa a rotina com esta configuração em vigor, independentemente do modo SQL do servidor em vigor quando a rotina é invocada.

MySQL stores the sql_mode system variable setting that is in effect at the time a routine is created, and always executes the routine with this setting in force, regardless of the server SQL mode in effect when the routine is invoked.

Segurança

Você deve possuir o privilégio EXECUTE em uma função para poder chamá-la. O MariaDB automaticamente concede os privilégios EXECUTE e ALTER ROUTINE à conta que chamou CREATE FUNCTION, mesmo que a cláusula DEFINER tenha sido usada.

Cada função tem uma conta associada como o definidor. Por padrão, o definidor é a conta que criou a função. Use a cláusula DEFINER para especificar uma conta diferente com o definidor. Você deve possuir o privilégio SUPER para usar a cláusula DEFINER. Veja Account Names para mais informação sobre especifiação de contas.

A cláusula SQL SECURITY especifica que privilégios são usados quando uma função é chamada. Se SQL SECURITY for INVOKER, o corpo da função será avaliado usando os privilégios do usuário que chama a função. Se SQL SECURITY for DEFINER, o corpo da função é sempre avaliado usando os privilégios da conta definidora. DEFINER é o padrão.

Isto lhe permite criar funções que concedem acesso limitado à certos dados. Por exemplo, digamos que você tem uma tabela que armazena informação dos empregados, e que você tem concedido privilégios SELECT somente à certas colunas para a conta de usuário roger.

CREATE TABLE employees (name TINYTEXT, dept TINYTEXT, salary INT);
GRANT SELECT (name, dept) ON employees TO roger;

Para permitir o usuário obter o salário máximo para um departamento, defina uma função e conceda o privilégio EXECUTE:

CREATE FUNCTION max_salary (dept TINYTEXT) RETURNS INT RETURN
  (SELECT MAX(salary) FROM employees WHERE employees.dept = dept);
GRANT EXECUTE ON FUNCTION max_salary TO roger;

Já que SQL SECURITY é DEFINER, sempre que o usuário roger chamar esta função, o subselect irá se executar com seus privilégios. Enquanto você tiver privilégios para selecionar o salário de cada empregado, quem tenha chamado a função poderá obter o salário máximo de cada departamento sem ser capaz de ver os salários individuais.

Exemplos

No seguinte exemplo, a função recebe um parâmetro, executa uma operação usando uma função SQL e retorna o resultado.

MariaDB [test]> CREATE FUNCTION hello (s CHAR(20))
    -> RETURNS CHAR(50) DETERMINISTIC
    -> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+
1 row in set (0.00 sec)

MariaDB [test]> 

Você pode usar uma instrução composta em uma função para manipular dados com instruções como INSERT e UPDATE. O exemplo a seguir cria uma função contador que usa uma tabela temporária para armazenar o valor atual. Dado que a instrução composta contém instruções encerradas com ponto e vírgula, primeiramente você tem que alterar o delimitador da instrução com a instrução DELIMITER para permitir que o ponto e vírgula seja usado no corpo da função

CREATE TEMPORARY TABLE counter (c INT);
INSERT INTO counter VALUES (0);
DELIMITER //
CREATE FUNCTION counter () RETURNS INT
  BEGIN
    UPDATE counter SET c = c + 1;
    RETURN (SELECT c FROM counter LIMIT 1);
  END //
DELIMITER ;