Procedimentos, Gatilhos e Funções no Banco de Dados

1. Procedimentos Armazenados (Stored Procedures)

1.2 Conceito

• Definição: Bloco de instruções SQL armazenado no banco de dados e executado repetidamente.

• Comparação: Semelhante a funções ou métodos em linguagens de programação, porém executados no contexto do DB.

• Parâmetros: Aceita parâmetros de entrada (IN) e saída (OUT), possibilitando receber dados e retornar resultados ou mensagens.

1.3 Vantagens

• Desempenho: Compilados uma vez, evitando recompilações a cada execução.

• Redução de Tráfego de Rede: Executa operações no servidor; apenas os resultados são transmitidos.

• Reutilização e Manutenção: Encapsula regras de negócio e operações repetitivas, facilitando atualizações (alteração única no procedimento).

• Isolamento de Transações: Agrupa operações em transações atômicas, garantindo a integridade dos dados.

• Otimização de Consultas: Pode utilizar índices e otimizações internas.

1.4 Desvantagens

• Portabilidade: Diferenças de sintaxe entre SGBDs (Oracle, SQL Server, MySQL, PostgreSQL).

• Bloqueio de Recursos: Procedimentos podem bloquear tabelas ou registros durante a execução, afetando o desempenho em sistemas de alta concorrência.

• Desacoplamento da Lógica de Negócio: A lógica distribuída entre aplicação e banco pode dificultar o entendimento do fluxo completo.

• Controle de Versão: Armazenados no banco, podem dificultar o versionamento e a colaboração entre desenvolvedores.

1.5 Estrutura e Sintaxe

• Uso do DELIMITER: Necessário para definir o início e fim do procedimento, permitindo o uso de ; dentro do bloco.

• Sintaxe Básica:

DELIMITER $$

CREATE PROCEDURE nome_procedure (param1 TIPO, param2 TIPO, ...)
BEGIN
    -- Declaração de variáveis (opcional)
    DECLARE var INT;
    
    -- Exemplos de comandos SQL:
    INSERT INTO tabela VALUES (null, valores);
    UPDATE tabela SET coluna = valor WHERE condicao;
    DELETE FROM tabela WHERE condicao;
    
    -- Estruturas de controle:
    IF (condicao) THEN
        -- Comandos
    ELSE
        -- Alternativa
    END IF;
    
    WHILE (condicao) DO
        -- Comandos
    END WHILE;
    
    -- Mensagem de confirmação:
    SELECT 'Operação realizada com sucesso!' AS Confirmacao;
END;

$$ DELIMITER ;

Exemplos de Comandos:

• INSERT: Criar um procedimento que adiciona novos registros

• UPDATE: Criar um procedimento que atualiza dados existentes

• DELETE: Criar um procedimento que remove registros

• SELECT: Criar um procedimento que consulta dados

• Parâmetros de Saída: Criar um procedimento e utilizar OUT quando quer apenas retornar valores

1.6 Chamada do Procedimento

CALL nome_procedure(valores);

1.7 Exemplos Práticos

• Inserir Endereço:

DELIMITER $$

CREATE PROCEDURE inserirEndereco (rua VARCHAR(100), numero INT, bairro 
VARCHAR(100), cidade INT)
BEGIN
    INSERT INTO Endereco VALUES (NULL, rua, numero, bairro, cidade);
    SELECT 'Insert realizado com sucesso!' AS Confirmacao;
END;

$$ DELIMITER ;

• Atualizar Renda de Cliente:

DELIMITER $$

CREATE PROCEDURE atualizarRendaCliente (codigo INT, renda FLOAT)
BEGIN
    UPDATE Hospede SET renda_familiar_hos = renda WHERE id_hos = codigo;
    SELECT 'Cliente atualizado com sucesso!' AS Confirmacao;
END; 

$$ DELIMITER ;

• Procedimento com Parâmetro de Saída:

DELIMITER $$

CREATE PROCEDURE MostrarConsumoCliente (data DATE, OUT total DOUBLE)
BEGIN
    SELECT SUM(venda.valor_total) INTO total
    FROM venda
    INNER JOIN hospedagem ON venda.id_hosp_fk = hospedagem.id_hosp
    WHERE data_checkin_hosp = data;
END; 

$$ DELIMITER ;

-- Chamada Procedimento:
CALL MostrarConsumoCliente('2022-05-12', @resultado);
SELECT @resultado;

• Estrutura de Decisão Encadeada (Exemplo Inserir Cliente):

DELIMITER $$

CREATE PROCEDURE InserirCliente (nome VARCHAR(100), cpf VARCHAR(20), 
telefone VARCHAR(50), celular VARCHAR(300), sexo INT, endereco INT)
BEGIN
    IF (nome <> '') THEN
        IF (cpf <> '') THEN
            INSERT INTO hospede VALUES (null, nome, cpf, telefone, celular, 
            sexo, endereco);
            SELECT 'Cliente inserido com sucesso!' AS Confirmacao;
        ELSE
            SELECT 'CPF deve ser fornecido para o cadastro!' AS Alerta;
        END IF;
    ELSE
        SELECT 'NOME deve ser fornecido para o cadastro!' AS Alerta;
    END IF;
END;

$$ DELIMITER ;

1.8 Exclusão de Procedimento

DROP PROCEDURE nome_procedure;

1.9 Notas

• Uso de Variáveis: Sempre declare variáveis com DECLARE e atribua valores com SET ou utilizando SELECT … INTO.

DECLARE contador INT;
SET contador = 0;

• Mensagens de Confirmação: É uma boa prática retornar mensagens para informar o sucesso ou falha de uma operação.

SELECT 'Operação realizada com sucesso!' AS Confirmacao;

2. Gatilhos (Triggers)

2.1 Conceito

• Definição: Objeto do banco de dados que executa automaticamente comandos SQL em resposta a eventos (INSERT, UPDATE, DELETE).

• Ponto de Execução:

  • BEFORE: Executado antes da ação.
  • AFTER: Executado após a ação.

• Utilização de NEW e OLD:

  • NEW acessa os dados novos (em INSERT/UPDATE).
  • OLD acessa os dados antigos (em DELETE/UPDATE).

2.2 Vantagens

• Automação: Reduz a necessidade de lógica na aplicação, deslocando o processamento para o banco.

• Integridade dos Dados: Garante que regras e validações sejam executadas automaticamente.

• Centralização: Toda a lógica de negócio pode ficar centralizada no banco, facilitando a manutenção.

2.3 Desvantagens

• Segurança:: Pode ser visualizado ou alterado por usuários com acesso ao banco.

• Complexidade de Depuração: Dificuldade em rastrear a origem de alterações quando os gatilhos são ativados.

• Sem Retorno de Mensagens: Não podem retornar mensagens ou alertas diretamente para o usuário.

2.4 Estrutura e Sintaxe

Sintaxe Básica:

DELIMITER $$ 

CREATE TRIGGER nome_trigger
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON nome_tabela
FOR EACH ROW
BEGIN
    -- Comandos SQL (utilizando NEW e OLD)
END;

$$ DELIMITER ;

Observação: Não é possível ter mais de um trigger para o mesmo evento e momento em uma mesma tabela.

2.5 Exemplos Práticos

• Baixar Estoque Após Inserção em Itens de Venda:

DELIMITER $$

CREATE TRIGGER baixarEstoque
AFTER INSERT ON itens_venda
FOR EACH ROW
BEGIN
    UPDATE produto
    SET quant_prod = quant_prod - NEW.quant_itv
    WHERE id_prod = NEW.id_prod_fk;
END;

$$ DELIMITER ;

• Cancelar Baixa de Estoque (em DELETE):

DELIMITER $$

CREATE TRIGGER cancelarBaixaEstoque
AFTER DELETE ON itens_venda
FOR EACH ROW
BEGIN
    UPDATE produto
    SET quant_prod = quant_prod + OLD.quant_itv
    WHERE id_prod = OLD.id_prod_fk;
END;

$$ DELIMITER ;

• Gatilho com Verificação de Estoque (antes do INSERT):

DELIMITER $$

CREATE TRIGGER baixarEstoque2
BEFORE INSERT ON itens_venda
FOR EACH ROW
BEGIN
    DECLARE estoque INT;
    SELECT quant_prod INTO estoque FROM produto WHERE id_prod = NEW.id_prod_fk;
    IF (estoque >= NEW.quant_itv) THEN
        UPDATE produto
        SET quant_prod = quant_prod - NEW.quant_itv
        WHERE id_prod = NEW.id_prod_fk;
    ELSE
        -- Limita a quantidade inserida ao estoque disponível
        SET NEW.quant_itv = estoque;
        UPDATE produto
        SET quant_prod = quant_prod - NEW.quant_itv
        WHERE id_prod = NEW.id_prod_fk;
    END IF;
END;

$$ DELIMITER ;

Observação: É comum usar procedimentos para validar (ex.: verificação de estoque com mensagens de confirmação) e gatilhos para executar a baixa automática sem retorno.

2.6 Exclusão de Gatilho

DROP TRIGGER nome_trigger;

2.7 Notas

• Ativação Automática: Triggers são acionados automaticamente pelo SGBD quando ocorre o evento definido (não é necessário — e nem possível — chamá-los manualmente).

• Uso de NEW e OLD: Utilize NEW para acessar valores que serão inseridos/atualizados e OLD para valores que serão removidos ou substituídos.

-- Em um trigger AFTER INSERT:
SELECT NEW.coluna;

-- Em um trigger AFTER DELETE:
SELECT OLD.coluna;

3. Funções (Functions)

3.1 Conceito

• Definição: Bloco de código reutilizável que processa parâmetros de entrada e retorna um único valor.

• Uso: Empregada em consultas SQL (SELECT, WHERE, ORDER BY, etc.) para realizar cálculos ou manipulações sem alterar o estado do banco.

3.2 Diferença entre Funções e Procedimentos

• Procedimentos: Podem realizar operações que alteram o banco (INSERT, UPDATE, DELETE) e são chamados via CALL.

• Funções: Retornam um valor único, não alteram dados e são usadas em expressões SQL.

3.3 Tipos e Natureza das Funções

• NO SQL: Não acessa ou modifica dados, apenas trabalha com parâmetros e variáveis locais.

• READS SQL DATA: Lê dados do banco, mas não realiza modificações.

• DETERMINISTIC: Sempre retorna o mesmo resultado para os mesmos valores de entrada.

• Combinações Possíveis: NO SQL DETERMINISTIC.

Observação: Para desabilitar a exigência de definir a natureza da função no MySQL, execute:

SET GLOBAL log_bin_trust_function_creators = 1;

3.5 Estrutura e Sintaxe

DELIMITER $$

CREATE FUNCTION nome_funcao (parametros)
RETURNS tipo_retorno
[NO SQL | READS SQL DATA | DETERMINISTIC]
BEGIN
    -- Comandos SQL para processamento
    RETURN valor;
END;

$$ DELIMITER ;

Observação: Diferente de procedimentos, funções são utilizadas diretamente em instruções SQL, como:

SELECT nome_pro, add10porcento(valor_pro) FROM produto;

3.6 Exemplos Práticos

• Calcular Imposto:

DELIMITER $$

CREATE FUNCTION calcular_imposto (renda FLOAT)
RETURNS FLOAT
DETERMINISTIC
BEGIN
    RETURN renda * 1.27;
END;

$$ DELIMITER ;

• Calculadora Simples:

DELIMITER $$

CREATE FUNCTION calculadora (num1 FLOAT, sinal VARCHAR(1), num2 FLOAT)
RETURNS FLOAT
NO SQL
BEGIN
    DECLARE resultado FLOAT;
    IF (sinal = '+') THEN
        SET resultado = num1 + num2;
    ELSEIF (sinal = '-') THEN
        SET resultado = num1 - num2;
    ELSEIF (sinal = '*') THEN
        SET resultado = num1 * num2;
    ELSEIF (sinal = '/') THEN
        SET resultado = num1 / num2;
    END IF;
    RETURN resultado;
END;

$$ DELIMITER ;

• Função com SELECT e Variável Local:

DELIMITER $$

CREATE FUNCTION calcular_total_entradas (controle INT)
RETURNS FLOAT
READS SQL DATA
BEGIN
    DECLARE total_entrada FLOAT;
    SELECT SUM(valor_ren) INTO total_entrada
    FROM renda
    WHERE cod_cont_fk = controle;
    RETURN total_entrada;
END; 

$$ DELIMITER ;

3.7 Exclusão de Função

DROP FUNCTION nome_funcao;

3.8 Notas

• Imutabilidade: Funções devem ser “puras” – isto é, não alteram o banco, apenas retornam um valor.

CREATE FUNCTION soma (a INT, b INT)
RETURNS INT
NO SQL
BEGIN
    RETURN a + b;
END;

• Utilização em Consultas: Funções são chamadas diretamente dentro de consultas, facilitando cálculos e transformações sem a necessidade de alterar dados.

SELECT nome, calcular_imposto(valor) FROM produto;

REVISÕES

4. Visões (Views)

4.1 Conceito

• Definição: Uma visão é uma consulta armazenada que atua como uma tabela virtual. Ela não guarda os dados, mas exibe o resultado do comando SELECT dinamicamente.

• Armazenamento: Visões armazenam apenas o comando SELECT e não os dados, o que garante que elas estejam sempre atualizadas.

4.2 Vantagens

• Reuso: Pode ser utilizada por vários usuários.

• Segurança: Permite ocultar determinadas colunas, limitando o acesso a dados sensíveis.

• Simplificação: Facilita a escrita de consultas complexas, encapsulando a lógica em um único objeto.

4.3 Estrutura e Sintaxe

CREATE VIEW nome_da_visao AS
SELECT coluna1, coluna2
FROM tabela1
WHERE condicao;

Observação: É fundamental evitar espaços nos nomes dos atributos na definição da visão, facilitando as consultas posteriores.

4.4 Exemplo Prático

• Criação de uma visão para armazenar os dados de vendas de 2019:

CREATE VIEW Vendas_2019 AS
SELECT cod_vend AS ID, data_vend AS Data_Venda, valortotal_vend AS Valor_Venda
FROM Venda
WHERE data_vend BETWEEN '2019-01-01' AND '2019-12-31';

4.5 Comandos Relacionados

Ver a estrutura da visão:

DESC nome_da_visao;

Consultar a visão:

SELECT * FROM nome_da_visao;

Excluir a visão:

DROP VIEW nome_da_visao;

5. Sub-Consultas

5.1 Conceito

• Definição: Uma subconsulta é uma consulta aninhada dentro de outra. Geralmente, ela retorna um valor que será utilizado pela consulta principal.

• Tipos:

  • Simples: Retorna um único valor (útil na cláusula WHERE).

  • Avançada: Pode retornar um valor para cada linha da consulta principal (usada no SELECT).

5.2 Estrutura e Sintaxe

Subconsulta Simples:

SELECT coluna1, coluna2
FROM Tabela1
WHERE atributo = (SELECT atributo FROM Tabela2 WHERE condicao);

Subconsultas Avançadas:

Utilizadas no SELECT para trazer, por exemplo, a soma de saques ou depósitos associados a cada conta.

SELECT 
    conta_corrente.cod_cc AS 'Código da Conta Corrente',  
    conta_corrente.numero_cc AS 'Número da Conta Corrente',
    (SELECT SUM(valor_saq) FROM saque WHERE 
    conta_corrente.cod_cc = saque.cod_cc_fk) AS 'Total de Saques'
FROM conta_corrente;

5.3 Exemplos Práticos

• Obter o Cliente com a Maior Renda:

SELECT nome_cli AS 'Cliente', renda_cli AS 'Maior Renda'
FROM Cliente
WHERE renda_cli = (SELECT MAX(renda_cli) FROM Cliente);

• Obter o Cliente com Renda Superior à Menor Renda Cadastrada:

SELECT nome_cli AS 'Cliente', renda_cli AS 'Renda'
FROM Cliente
WHERE renda_cli > (SELECT MIN(renda_cli) FROM Cliente);

• Comparação com a Soma de Valores:

SELECT nome_cli AS 'Cliente', renda_cli AS 'Renda'
FROM Cliente
WHERE renda_cli < (SELECT SUM(saldo_cc) FROM conta_corrente);

5.3 Subconsultas em UPDATE

É comum usar subconsultas em comandos de atualização para calcular novos valores.

• Atualizar Saldo com a Soma dos Depósitos:

UPDATE conta_corrente
SET saldo_cc = saldo_cc + (SELECT SUM(valor_dep) FROM deposito WHERE 
deposito.cod_cc_fk = conta_corrente.cod_cc);

6. Joins (Junções)

6.1 Conceito

• Definição: Joins são utilizados para combinar linhas de duas ou mais tabelas com base em uma condição relacionada (normalmente chaves primárias e estrangeiras).

• Principais Tipos:

  • INNER JOIN: Retorna apenas registros que possuem correspondência nas tabelas.

  • LEFT JOIN: Retorna todos os registros da tabela da esquerda e os correspondentes da tabela da direita (se existirem).

  • RIGHT JOIN: Retorna todos os registros da tabela da direita e os correspondentes da tabela da esquerda.

  • CROSS JOIN: Realiza o cruzamento completo entre as tabelas.

  • FULL JOIN: Não é nativo no MySQL, mas pode ser simulado com UNION ALL entre LEFT e RIGHT JOIN.

6.2 Estrutura e Sintaxe

Exemplo Básico com INNER JOIN

SELECT tabela1.coluna, tabela2.coluna
FROM tabela1
INNER JOIN tabela2 ON tabela1.chave_primaria = tabela2.chave_estrangeira;

6.3 Exemplos Práticos

• Inner Join: Exibindo Cliente e Número da Conta

SELECT cliente.nome_cli, conta_corrente.numero_cc
FROM cliente
INNER JOIN conta_corrente ON cliente.cod_cli = conta_corrente.cod_cli_fk;

• Left Join: Exibindo Todos os Clientes Mesmo Sem Conta

SELECT cliente.nome_cli, conta_corrente.numero_cc
FROM cliente
LEFT JOIN conta_corrente ON cliente.cod_cli = conta_corrente.cod_cli_fk;

• Rigth Join Exibindo Todas as Contas Mesmo Sem Cliente Associado

SELECT cliente.nome_cli, conta_corrente.numero_cc
FROM cliente
RIGHT JOIN conta_corrente ON cliente.cod_cli = conta_corrente.cod_cli_fk;

• Simulando Full Join com Union All

SELECT origem.nome_ori, heroi.nome_hero
FROM origem LEFT JOIN heroi ON origem.cod_ori = heroi.cod_ori_fk
UNION ALL
SELECT origem.nome_ori, heroi.nome_hero
FROM origem RIGHT JOIN heroi ON origem.cod_ori = heroi.cod_ori_fk;

6.4 Joins com Múltiplas Tabelas

Para juntar três ou mais tabelas, encadeie os JOINs, sempre utilizando a cláusula ON para definir as condições.

• Exemplo com Três Tabelas

SELECT 
    banco.nome_ban AS 'Banco', 
    conta_corrente.numero_cc AS 'Número da Conta', 
    cliente.nome_cli AS 'Nome do Cliente'
FROM cliente
INNER JOIN conta_corrente ON cliente.cod_cli = conta_corrente.cod_cli_fk
INNER JOIN banco ON conta_corrente.cod_ban_fk = banco.cod_ban;