EXEMPLOS - CREATE FUNCTION-MySQL

Conceitualmente, uma função é uma rotina SQL que aceita parâmetros, realiza cálculos lógicos e complexos, como calcular a nota final de um aluno, e retorna o resultado dessa ação como um valor.

No MySQL, uma função armazenada tem a seguinte estrutura:

DELIMITER //

CREATE FUNCTION nome_função(parametro1_da_função, parametro2_da_função, ... )

RETURNS tipo_de_dado

[NOT] DETERMINISTIC

BEGIN

 -- instruções 

END //

DELIMITER ;

Onde:

• nome_função: É o nome pelo qual a função armazenada é chamada.
• parametro_da_função: É o argumento cujo valor é usado pela função dentro do seu corpo(de Begin a End;).
• tipo de dados: é o tipo de dados do valor retornado pela função.
• [NOT] DETERMINISTIC: uma função armazenada pode ser DETERMINISTIC ou NOT DETERMINISTIC, na qual é especificada em sua declaração.
• Uma função é considerada “determinística” se sempre produzir o mesmo resultado para os mesmos parâmetros de entrada. Caso contrário, será “não determinística”. Se você não usar DETERMINISTIC ou  NOT DETERMINISTIC,  o MySQL usará por padrão a opção NOT DETERMINISTIC.
• O DELIMITER serve para indicar o final do conjunto de instruções. Por muito tempo, o delimitador sempre foi um ponto e vírgula. Isso causa um problema, porque em uma função armazenada, pode-se ter muitas declarações, e cada uma deve terminar com um ponto e vírgula.

Galera! Pra deixar esse conceito de DETERMINISTIC e NOT DETERMINISTIC mais fácil e claro de compreender saiba que, por exemplo, se você tem uma rotina que contém a função NOW(permite obter a data e a hora atual ) ou RAND(para gerar números randômicos/aleatórios), essa função não é determinística porque sempre que você a chamar, ela não vai retornar o mesmo resultado para os mesmos parâmetros de entrada. Como rand () é uma função não determinística, isso significa que não sabemos o que ela retornará com antecedência e, na verdade, esse é exatamente o propósito de rand () - retornar um valor aleatório.

Observação: o corpo da função(de Begin a End) deve conter uma instrução RETURN. Assim como as funções internas do MySQL, as funções criadas pela gente, podem ser chamadas de dentro de uma instrução MySQL.

Pra gente trabalhar com funções, vamos criar uma tabela e inserir dados nela.
A seguir, temos o exemplo de uma tabela de funcionários. A tabela foi criada utilizando a IDE MySQL Workbench.

Observação: vou utilizar a palavra reservada AUTO INCREMENT na chave primaria id_funcionario pra gerar um id_funcionario especifico sempre que for cadastrar um novo funcionário. Pra saber mais sobre o AUTO_INCREMENT, CLIQUE AQUI.

CREATE TABLE funcionarios(

id_funcionario int PRIMARY KEY  NOT NULL  AUTO_INCREMENT,

nome varchar(40) NOT NULL,

sobrenome varchar(40) NOT NULL,

telefone varchar(40) NOT NULL,

data_nascimento DATE,

endereco varchar(80) NOT NULL,

data_contratacao DATE,

        salario DECIMAL(7,2),

cargo varchar(40) NOT NULL

);

Quando inserimos um novo registro na tabela "funcionarios", NÃO precisamos especificar um valor para a coluna "id_funcionarios" (um valor único será adicionado automaticamente):

INSERT INTO funcionarios (nome,sobrenome,telefone,data_nascimento,endereco,data_contratacao,salario,cargo) VALUES ('Araújo','Catumbela','1432222222',STR_TO_DATE( "11/02/1994","%d/%m/%Y" ),'Rua dos Bandeirantes 3-33',STR_TO_DATE( "17/02/2013","%d/%m/%Y"),7200,'Diretor Geral');

Pra gente visualizar os dados inseridos, vamos fazer um SELECT * FROM, para  trazer todos os registros da tabela  funcionarios.

SELECT * FROM funcionarios;

Após a execução do comando, teremos o resultado, conforme exibido na imagem a seguir:

Exemplo nº1

A seguir, temos o exemplo de uma função que recebe um parâmetro de entrada, executa uma operação usando uma função SQL e retorna o resultado.

Solução

DELIMITER //

CREATE FUNCTION ola (frase_entrada CHAR(70)) 

RETURNS CHAR(70) 

DETERMINISTIC 

BEGIN

   RETURN CONCAT('Olá, ',frase_entrada,'!');  

END //

DELIMITER ;

Pra demonstrar que deu tudo certo, ou seja, que a função ola está nos conformes, vamos chamá-la. 

SELECT ola('Angola, minha terra natal');

Pode-se notar que a função retornou o que era esperado mas, o titulo não está muito elegante. Por isso, vou usar o alias(apelido) na instrução SELECT pra deixar  o resultado com um pouquinho mais de charme.

SELECT ola('Angola, minha terra natal') as Mensagem; 

Após a execução do comando, teremos o resultado, conforme exibido na imagem a seguir:

Exemplo nº2

Utilizando a tabela funcionários(que a gente criou no começo desse artigo), vamos  descobrir o tempo de serviço dos funcionários da empresa.

Solução

DELIMITER //

CREATE FUNCTION tempo_servico(data_inicio date) 

RETURNS int 

DETERMINISTIC

BEGIN

    DECLARE data_atual DATE;

    Select current_date()into data_atual;

    RETURN year(data_atual)-year(data_inicio);

END //

DELIMITER ;

Pra demonstrar que deu tudo certo, ou seja, que a função tempo_servico() está nos conformes, vamos chamá-la.

SELECT id_funcionario, nome, sobrenome, tempo_servico(data_contratacao) as 'tempo de serviço' FROM funcionarios;

Após a execução do comando, teremos o resultado, conforme exibido na imagem a seguir:

Inserindo novos registros  

A seguir, vou inserir mais dois registros na tabela funcionários, pra gente explorar de forma mais abrangente o assunto e deixar nossa experiência o mais incrível possível.

INSERT INTO funcionarios (nome,sobrenome,telefone,data_nascimento,endereco,data_contratacao,salario,cargo) VALUES ('Moisés','Mendrote','144562222',STR_TO_DATE( "26/07/1999","%d/%m/%Y" ),'Rua 12 de julho 5-30',STR_TO_DATE( "15/02/2015","%d/%m/%Y"),4500,'Diretor Analista');

INSERT INTO funcionarios (nome,sobrenome,telefone,data_nascimento,endereco,data_contratacao,salario,cargo) VALUES ('Ana','Juliana','14824222',STR_TO_DATE( "2/03/2000","%d/%m/%Y" ),'Rua 15 de outubro 6-12',STR_TO_DATE( "17/05/2017","%d/%m/%Y"),3700,'Diretor de Marketing');

Agora, vamos chamar novamente a função criada anteriormente, para  trazer as colunas  id_funcionario, nome e sobrenome de todos os  funcionários com os respectivos tempo de serviço.

SELECT id_funcionario, nome, sobrenome, tempo_servico(data_contratacao) as 'tempo de servico' FROM funcionarios;

Após a execução do comando, teremos o resultado, conforme exibido na imagem a seguir:

Exemplo nº3
Criar uma função armazenada que receba um ID(um código identificador) que corresponde ao código de um funcionário. A função deve retornar o nome do funcionário, quando a mesma existir, caso não exista retorne a frase 'usuário inexistente'.

Solução

A instrução CREATE FUNCTION  a seguir cria uma função que retorna o nome do funcionário com base no seu id_funcionario:

DELIMITER //

CREATE FUNCTION pegar_funcionario( pid INT) 

RETURNS varchar(120)

DETERMINISTIC

BEGIN

    Declare retorno varchar(120);

    Declare quantidade int(1);

      select count(*) into quantidade from funcionarios where pid=id_funcionario;

        if quantidade=1 then

            select nome INTO retorno from funcionarios where id_funcionario=pid;

        else

          SET retorno='usuário inexistente';

        end if;

        return (retorno);

END //

DELIMITER ;

Pra demonstrar que deu tudo certo, ou seja, que a função pegar_funcionario está nos conformes, vamos chamá-la. 

SELECT pegar_funcionario(3);

Após a execução do comando, teremos o resultado, conforme exibido na imagem a seguir:

Vamos fazer um SELECT * FROM funcionarios; pra gente ver a quantidade de registros da nossa tabela. 

De acordo com a imagem anterior, a gente tem três funcionários cadastrados. Então, pra testar a função pegar_funcionario(), vou inserir o id_funcionario igual a 4 pra ver se vai retornar a mensagem 'usuario inexistente'.

SELECT pegar_funcionario(4);

Após a execução do comando, teremos o resultado, conforme exibido na imagem a seguir:

Ótimo <3 , nossa função está redondinha, funcionando conforme o esperado.

DROP FUNCTION

Para deletar uma FUNCTION usamos o comando:
DROP FUNCTION IF EXISTS nome_da_função;

Lembre-se: a cláusula IF EXISTS previne que um erro ocorra, caso a função não exista.

FUNCTION X PROCEDURE

De acordo com os exercícios anteriores, vimos que a principal diferença entre uma Função Armazenada e um  Procedimento Armazenado  , está na chamada.

Outras diferenças são listadas abaixo:

• No Stored Procedure(Procedimento Armazenado) temos o comando CALL, já a Stored Function(Função Armazenada) é chamada através do próprio SELECT, ou seja, nos solicitamos um retorno.
• Um procedimento não retorna um valor. Em vez disso, é invocado com uma instrução CALL para executar uma operação como modificar uma tabela .
• Uma função é chamada dentro de uma expressão e retorna um único valor diretamente ao chamador a ser usado na expressão.

FOI ÚTIL? Comente com a gente <3