Artigo: SQL Stored Procedures e Fucntions com Mysql - Gigasystems

MySql

Em por

sql-stored-procedures-e-fucntions-com-mysql
SQL Stored Procedures e Fucntions com Mysql

As procedures ou procedimentos são scripts visando alguma modificação no banco de dados ou simplesmente uma busca de dados do mesmo.

As Stored Procedures são armazenadas e pré-compiladas diretamente no SGBD, isso torna sua execução mais rápida.

Eu particularmente gosto de usar procedures, mesmo que seja para um simples select, uns dos motivos é organização e fácil manutenção dos códigos.

Se temos uma aplicação que faz um select por diversas vezes durante um código, ou seja se você escreve esse select mais de uma vez, fica muito mais simples fazer uma futura mudança apenas na procedure, porque durante o código você só a chamou. Caso o contrário você ou teria uma amarração por variável em seu código, não acho essa solução elegante, ou teria que percorrer todo ele para fazer as trocas.

Estou utilizando como exemplo o SGBD MySql, lembrando que a sintaxe muda um pouco para cada Gerenciador de Banco, mas o conceito é sempre o mesmo.

Vamos criar nosso banco de dados:

 -- Verificar se existe o banco pizzaria, se sim nós o deletamos DROP DATABASE IF EXISTS pizzaria; -- Agora estamos criando o banco CREATE DATABASE IF NOT EXISTS pizzaria; -- Aqui estamos dizendo que vamos usar o banco pizzaria USE pizzaria; -- Criamos a tabela de pizza CREATE TABLE pizzas ( idpizzas int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, pizzas varchar(45) NOT NULL, valor double DEFAULT NULL ) ENGINE=InnoDB; 

Com a tabela criada, povoamos a tabela com alguns inserts:

 INSERT INTO pizzas (pizzas, valor) VALUES('Calabresa', 14.56); INSERT INTO pizzas (pizzas, valor) VALUES('Quatro Queijos', 20.56); INSERT INTO pizzas (pizzas, valor) VALUES('Portuguesa', 17.56); 

Pronto, agora vamos fazer uma PROCEDURE. Ela terá a função de retornar o ID da pizza, o nome e valor, de acordo com um parâmetro, o possível pedaço do nome da pizza:

 -- Verificar se existe já existe a procedure, se sim nós a deletamos DROP PROCEDURE IF EXISTS pesquisaPizza; -- O delimiter serve para indicar o final, final mesmo, do conjunto de instruções, já que usamos por diversas vezes o ; (ponto e virgula) ele não serve para essa função DELIMITER $$ -- Criamos a procedure com o comando CREATE PROCEDURE [nome da procedure] -- Repare que depois entre parênteses temos um parâmetro que recebemos -- A palavra reservada IN indica que este é apenas um parâmetro de Entrada, ele poderia ser também um de saída, a palavra reservada nesse caso seria OUT -- Após o IN [colocar um nome para o parâmetro] [tipo, pode ser INT, DATE, VARCHAR] CREATE PROCEDURE pesquisaPizza(IN parametro VARCHAR(50)) -- Aqui colocamos o a comando para iniciarmos o que a stored procedure devera fazer quando a solicitarmos BEGIN -- Aqui vamos fazer um teste antes para saber se parâmetro num veio vazio, é isso mesmo aqui podemos fazer IFs -- Os testes são feitos com o comando IF ([condição]) THEN IF(parametro= "") THEN SELECT "Nome Vazio" AS 'ERRO'; ELSE -- Aqui o procedimento que a procedure deverá fazer REPARE QUE TEMOS QUE TERMINAR A INSTRUÇÃO COM ponto e vírgula (;) SELECT a.idpizzas, a.pizzas AS 'Nomes Pizzas', a.valor AS 'Valor de Venda' FROM pizzas a WHERE pizzas LIKE CONCAT('%',parametro,'%') ORDER BY a.pizzas ASC ; -- FINAL DO IF END IF; -- Indicamos o fim das instruções da procedure END; -- e para finalizar vamos colocar delimiter, aqui dizemos que é o fim mesmo. rsrsrsr $$ 

Pronto, procedure feita é só executar esse código e o banco o compilará e deixará armazenado, só esperando você o chamar.... Aaaah e como o chamamos?

Simples faça o seguinte:

 -- Para chamada de Stored Procedure, usamos o comando CALL [nome da procedure]([parâmetros se houver]) CALL pesquisaPizza("Cala"); 

Agora vamos ver a FUNCTION

Você já deve ter usado alguma função interna do MySql, por exemplo  a SUM(), que soma um determinado campo.

A principal diferença entre um Function e uma Procedure, está em como a chamamos. Na procedure temos o comando CALL, já a Function é chamada através do próprio SELECT, ou seja, nos solicitamos um retorno, por exemplo, SELECT SUM([campo]).

Portanto a Function é usada para fazer algum procedimento durante um select, ela deve ter um retorno, vejamos mais a seguir, no exemplo.

Vamos criar um outro banco de dados. Este será de notas de alunos durante bimestre:

 -- Verificar se existe o banco escola, se sim nós o deletamos DROP DATABASE IF EXISTS escola; -- Agora estamos criando o banco CREATE DATABASE IF NOT EXISTS escola; -- Aqui estamos dizendo que vamos usar o banco pizzaria USE escola; -- Criamos a tabela de alunos CREATE TABLE alunos ( idAluno int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, nome varchar(45) NOT NULL, notaP1 double DEFAULT NULL, notaP2 double DEFAULT NULL, notaT1 double DEFAULT NULL, notaT2 double DEFAULT NULL ) ENGINE=InnoDB;

Com a tabela criada, novamente vamos povoar a tabela com alguns inserts:

 INSERT INTO alunos (nome, notaP1, notaP2, notaT1, notaT2) VALUES('Everton J Paula',5,8,10,10); INSERT INTO alunos (nome, notaP1, notaP2, notaT1, notaT2) VALUES('Leonardo B Martins',8,5,8,5); INSERT INTO alunos (nome, notaP1, notaP2, notaT1, notaT2) VALUES('Ana Carolina',7.5,8.5,7.5,5.5); 

Pronto, agora vamos fazer uma função para calcular a média final dos alunos de acordo com a REGRA DE NEGÓCIOS, da seguinte forma. 

P1 tem peso de 70% e T1 30%, enquanto a P2 tem peso de 60 % e T2 40%, devemos somar (P1 + T1) + (P2  + T2) e dividir o resultado por 2.

Vamos dar uma olhada como ficaria essa função no exemplo abaixo:

 -- Primeiro verificamos se a function ja existe, caso exista a excluimos DROP FUNCTION IF EXISTS calcMedia; -- declaramos o delimiter DELIMITER $ -- Criamos a function passando o nome seus parametros e dizendo qual seu tipo de retorno -- Neste caso usamos o CREATE FUNCTION [nome da função] ([Nome do parametro] [tipo]) RETURNS[tipo esperado do retorno] CREATE FUNCTION calcMedia (id INT) RETURNS DOUBLE -- Este comando indica que esta função faz uso de leitura de dados, ou seja, faz SELECT READS SQL DATA -- Iniciar comando da function BEGIN -- Declaramos uma variavel do tipo double DECLARE media DOUBLE; -- Dizemos que a variavel vale o que retornar do SELECT SET media = (SELECT ((notaP1 * 0.7 + notaT1 * 0.3) + (notaP2 * 0.6 + notaT2 * 0.4))/2 FROM alunos WHERE idAluno = id); -- retorno da função é o valor armazena em media RETURN media; -- Fim das instruções da função END; -- Final final, indicado pelo delimiter $

 Agora é só a solicitarmos, vamos fazer isso através de um SELECT:

 SELECT nome AS 'Nome do Aluno', calcMedia(idAluno) AS 'Media Final' FROM alunos;

O retorno deste select mostrara todos os alunos e sua média final.

Este artigo falou um pouco sobre PROCEDURES e FUNCTION em SGBD MySql, você pode aprender mais olhando as documentações em //dev.mysql.com/doc/refman/5.0/en/create-procedure.html

Outro artigo interessante é triggers.

Dê uma conferida e bons estudos!

Olá, deixe seu comentário para SQL Stored Procedures e Fucntions com Mysql

Já temos 3 comentário(s). DEIXE O SEU :)
Jean Carvalho

Jean Carvalho

Excelente artigo, didática fantástica. Gratidão.
★★★★★DIA 08.05.21 18h55RESPONDER
Leonardo Martins
Enviando Comentário Fechar :/
Luiz Otávio

Luiz Otávio

Ola Boa noite, tudo bem? Estou aprendendo agora sobre a existência dassas questões de procedures, e você explicou de uma forma bem bacana Leonardo, parabéns mesmo!!! Pode não parecer, mas explicar com simplicidade e de forma objetiva é só para os que realmente entendem!!!

Parabéns
★★★★★DIA 25.06.20 21h29RESPONDER
Marcos Martins, Jean Carvalho
Enviando Comentário Fechar :/
Cosme Daniel

Cosme Daniel

Boa noite, muito bom este artigo estou com uma necessidade que estou tentando usar Trigger oi Procedures, ou as duas juntas, mais não estou conseguindo pode me ajudar com esse problema:

Tenho vários bancos de dados BANCO_1(Principal) BANCO_2, _3, _4...(Empresas). Em todos os banco existe uma tabela empresa, quem possuem a mesma estrutura, no BANCO_1 tenho o cadastro de todas as empresa dos BANCO_2, _3, _4...! Gostaria de ao atualizar qualquer informação da Empresa no BANCO_1, os dados fossem atualizado na tabela empresa do BANCO_2 ou _3 ou _4.

Ex.: Atualizei a Empresa Cod 1 no BANCO_1, as informações desta empresa fossem atualizadas no banco dela.

Tentei fazer desta forma:
DELIMITER $$

CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER `projects`.`empresas_iguais` AFTER UPDATE
ON `projects`.`cs_empresas`
FOR EACH ROW BEGIN
IF ((NEW.empresa_razao_social OLD.empresa_razao_social)
OR(NEW.empresa_nome_fantasia OLD.empresa_nome_fantasia)
OR(NEW.empresa_db_host OLD.empresa_db_host)
OR(NEW.empresa_db_user OLD.empresa_db_user)
OR(NEW.empresa_db_password OLD.empresa_db_password)
OR(NEW.empresa_db_nome OLD.empresa_db_nome)
OR(NEW.empresa_tipo OLD.empresa_tipo)
OR(NEW.empresa_cnpj_cpf OLD.empresa_cnpj_cpf)
OR(NEW.empresa_contat OLD.empresa_contat)
OR(NEW.empresa_email OLD.empresa_email)
OR(NEW.empresa_telefone OLD.empresa_telefone)
OR(NEW.empresa_celular OLD.empresa_celular)
OR(NEW.empresa_logo OLD.empresa_logo)
OR(NEW.empresa_registro OLD.empresa_registro)
OR(NEW.empresa_level OLD.empresa_level)
OR(NEW.empresa_status OLD.empresa_status))
THEN
SET @DB_EMPRESA = (SELECT empresa_db_nome FROM cs_empresas WHERE empresa_id = OLD.empresa_id);
IF @DB_EMPRESA != ""
THEN SET @DB_EMPRESA.cs_empresas.empresa_razao_social = NEW.empresa_razao_social,
@DB_EMPRESA.cs_empresas.empresa_nome_fantasia = NEW.empresa_nome_fantasia,
@DB_EMPRESA.cs_empresas.empresa_db_host = NEW.empresa_db_host,
@DB_EMPRESA.cs_empresas.empresa_db_user = NEW.empresa_db_user,
@DB_EMPRESA.cs_empresas.empresa_db_password = NEW.empresa_db_password,
@DB_EMPRESA.cs_empresas.empresa_db_nome = NEW.empresa_db_nome,
@DB_EMPRESA.cs_empresas.empresa_tipo = NEW.empresa_tipo,
@DB_EMPRESA.cs_empresas.empresa_cnpj_cpf = NEW.empresa_cnpj_cpf,
@DB_EMPRESA.cs_empresas.empresa_contato = NEW.empresa_contato,
@DB_EMPRESA.cs_empresas.empresa_email = NEW.empresa_email,
@DB_EMPRESA.cs_empresas.empresa_telefone = NEW.empresa_telefone,
@DB_EMPRESA.cs_empresas.empresa_celular = NEW.empresa_celular,
@DB_EMPRESA.cs_empresas.empresa_logo = NEW.empresa_logo,
@DB_EMPRESA.cs_empresas.empresa_registro = NEW.empresa_registro,
@DB_EMPRESA.cs_empresas.empresa_level = NEW.empresa_level,
@DB_EMPRESA.cs_empresas.empresa_status = NEW.empresa_status
WHERE @DB_EMPRESA.cs_empresas.empresa_id = OLD.empresa_id;

END IF;
END IF;
END$$
DELIMITER ;

Porém estou recebendo este erro:

1 queries executed, 0 success, 1 errors, 0 warnings

Query: CREATE TRIGGER `projects`.`empresas_iguais` AFTER UPDATE ON `projects`.`cs_empresas` FOR EACH ROW BEGIN IF ((NEW.empresa_razao_s...

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE @DB_EMPRESA.cs_empresas.empresa_id = OLD.empresa_id;

END IF;
' at line 41

Execution Time : 0 sec
Transfer Time : 0 sec
Total Time : 0.002 sec
Não encontro como resolver. Pode me ajudar?
★★★★★DIA 10.09.18 23h34RESPONDER
N/A
Enviando Comentário Fechar :/
Enviando Comentário Fechar :/

Veja Também:

Artigos Relacionados a categoria: MySql

Soluções

...CURTIU? AINDA NÃO VIU NOSSO PORTFÓLIO?


Se ainda não viu nosso Portfólio e quer conhecer um pouco mais... aproveite, veja agora mesmo nossos Cases de Sucesso e tenha seu site nos padrões atuais preparado para maioria dos dispositivos e navegadores.

Usamos cookies para manter o site seguro, registrar e personalizar sua navegação. Para mais detalhes sobre essa atividade, acesse nossa Politica de Privacidade

Entendi