Função armazenada MySQL

Resumo : neste tutorial, você aprenderá como criar uma função armazenada usando a CREATE FUNCTIONinstrução.

Introdução à função armazenada do MySQL

Uma função armazenada é um tipo especializado de programa armazenado projetado para retornar um único valor. Normalmente, você usa funções armazenadas para encapsular fórmulas ou regras de negócios comuns, tornando-as reutilizáveis ​​em instruções SQL ou outros programas armazenados.

Ao contrário de um procedimento armazenado , você pode usar uma função armazenada em instruções SQL sempre que usar uma expressão. Isso melhora a legibilidade e a capacidade de manutenção do código processual.

Para criar uma função armazenada, você usa a CREATE FUNCTIONinstrução. O seguinte ilustra a sintaxe básica para criar uma nova função armazenada:

DELIMITER $$

CREATE FUNCTION function_name(
    param1,
    param2,…
)
RETURNS datatype
[NOT] DETERMINISTIC
BEGIN
 -- statements
END $$

DELIMITER ;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Nesta sintaxe:

Primeiro, especifique o nome da função armazenada que deseja criar após CREATE FUNCTIONas palavras-chave.

Segundo, liste todos os parâmetros da função armazenada entre parênteses seguidos do nome da função.

Por padrão, as funções armazenadas consideram todos os parâmetros como INparâmetros. Você não pode especificar INou OUTmodificadores INOUTde parâmetros

Terceiro, especifique o tipo de dados do valor de retorno na RETURNSinstrução, que pode ser qualquer tipo de dados MySQL válido .

Quarto, determine se uma função é determinística ou não usando a DETERMINISTICpalavra-chave.

Uma função determinística sempre retorna o mesmo resultado para os mesmos parâmetros de entrada, enquanto uma função não determinística produz resultados diferentes para os mesmos parâmetros de entrada.

Se você não usar DETERMINISTICou NOT DETERMINISTIC, o MySQL usará a NOT DETERMINISTICopção como padrão.

Por fim, escreva o código no corpo da função armazenada no BEGIN...ENDbloco.

Dentro da seção do corpo, você precisa incluir pelo menos uma RETURNdeclaração. A RETURNinstrução envia um valor para os programas de chamada.

Ao chegar à RETURNinstrução, a função armazenada encerra a execução da função armazenada imediatamente.

Exemplo de CREATE FUNCTION do MySQL

Vejamos um exemplo de criação de uma função armazenada. Usaremos a customerstabela do banco de dados de exemplo para a demonstração.

A CREATE FUNCTIONinstrução a seguir cria uma função que retorna o nível do cliente com base no crédito:

DELIMITER $$

CREATE FUNCTION CustomerLevel(
	credit DECIMAL(10,2)
) 
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    DECLARE customerLevel VARCHAR(20);

    IF credit > 50000 THEN
		SET customerLevel = 'PLATINUM';
    ELSEIF (credit >= 50000 AND 
			credit <= 10000) THEN
        SET customerLevel = 'GOLD';
    ELSEIF credit < 10000 THEN
        SET customerLevel = 'SILVER';
    END IF;
	-- return the customer level
	RETURN (customerLevel);
END$$

DELIMITER ;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Depois que a função for criada, você poderá visualizá-la no MySQL Workbench na seção Funções :

Ou você pode visualizar todas as funções armazenadas no classicmodelsbanco de dados atual usando o SHOW FUNCTION STATUSseguinte:

SHOW FUNCTION STATUS WHERE db = 'classicmodels';Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Chamando uma função armazenada em uma instrução SQL

A instrução a seguir ilustra como chamar a CustomerLevelfunção armazenada:

SELECT 
    customerName, 
    CustomerLevel(creditLimit)
FROM
    customers
ORDER BY 
    customerName;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Chamando uma função armazenada em um procedimento armazenado

A instrução a seguir cria um novo procedimento armazenado que chama a CustomerLevel() função armazenada:

DELIMITER $$

CREATE PROCEDURE GetCustomerLevel(
    IN  customerNo INT,  
    OUT customerLevel VARCHAR(20)
)
BEGIN

	DECLARE credit DEC(10,2) DEFAULT 0;
    
    -- get credit limit of a customer
    SELECT 
		creditLimit 
	INTO credit
    FROM customers
    WHERE 
		customerNumber = customerNo;
    
    -- call the function 
    SET customerLevel = CustomerLevel(credit);
END$$

DELIMITER ;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

O seguinte ilustra como chamar o GetCustomerLevel() procedimento armazenado:

CALL GetCustomerLevel(131,@customerLevel);
SELECT @customerLevel;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+----------------+
| @customerLevel |
+----------------+
| PLATINUM       |
+----------------+
1 row in set (0.00 sec)Linguagem de código:  JavaScript  ( javascript )

É importante observar que se uma função armazenada contiver instruções SQL que recuperam dados de tabelas, você deverá evitar usá-la em outras instruções SQL; caso contrário, a função armazenada poderá diminuir a velocidade da consulta.

Resumo

• Uma função armazenada é um trecho de código reutilizável e encapsulado em um banco de dados que executa uma tarefa específica e retorna um único valor.
• Use a CREATE FUNCTIONinstrução para criar uma função armazenada.
• Use funções armazenadas para aprimorar a modularidade e a eficiência das instruções SQL.