Views e Procedures

Avançado • 80 minutos • Estruturas avançadas

Aprenda a criar views para simplificar consultas complexas e procedures para automatizar operações no banco de dados.

Objetivos de Aprendizagem

Entender o conceito e benefícios das views
Criar e gerenciar views simples e complexas
Trabalhar com views materializadas
Criar e executar stored procedures
Usar functions para lógica de negócio

Views (Visões)

Uma view é uma "tabela virtual" baseada no resultado de uma consulta SQL. Ela não armazena dados fisicamente, mas apresenta dados de uma ou mais tabelas de forma organizada.

🎯 Por que usar Views?

• Simplificação: Transformam consultas complexas em "tabelas" simples

• Segurança: Controlam quais dados os usuários podem ver

• Reutilização: Evitam repetir a mesma consulta em vários lugares

• Abstração: Escondem a complexidade da estrutura do banco

Criando Views Simples

-- Sintaxe básica
CREATE VIEW nome_da_view AS
SELECT colunas
FROM tabelas
WHERE condições;

-- Exemplo: View de produtos ativos
CREATE VIEW produtos_ativos AS
SELECT 
    id,
    nome,
    preco,
    categoria_id
FROM produtos
WHERE ativo = true;

-- Usando a view
SELECT * FROM produtos_ativos WHERE preco > 100;

Views com JOINs

-- View com relacionamentos
CREATE VIEW vendas_detalhadas AS
SELECT 
    v.id,
    v.data_venda,
    v.valor_total,
    c.nome as cliente_nome,
    c.email as cliente_email,
    u.nome as vendedor_nome
FROM vendas v
JOIN clientes c ON v.cliente_id = c.id
JOIN usuarios u ON v.vendedor_id = u.id;

-- Consulta simplificada
SELECT * FROM vendas_detalhadas 
WHERE data_venda >= '2024-01-01';

Views com Agregações

-- View de estatísticas por cliente
CREATE VIEW estatisticas_clientes AS
SELECT 
    c.id,
    c.nome,
    COUNT(v.id) as total_vendas,
    SUM(v.valor_total) as valor_total_compras,
    AVG(v.valor_total) as ticket_medio,
    MAX(v.data_venda) as ultima_compra
FROM clientes c
LEFT JOIN vendas v ON c.id = v.cliente_id
GROUP BY c.id, c.nome;

-- Consulta de clientes VIP
SELECT * FROM estatisticas_clientes 
WHERE valor_total_compras > 5000;

✅ Vantagens

• Simplificam consultas complexas
• Melhoram a segurança dos dados
• Facilitam a manutenção
• Padronizam o acesso aos dados
• Não ocupam espaço adicional

⚠️ Limitações

• Performance pode ser impactada
• Nem sempre permitem UPDATE/INSERT
• Dependem das tabelas base
• Podem mascarar problemas de design
• Debugging mais complexo

Views Materializadas

Views materializadas armazenam fisicamente o resultado da consulta, oferecendo melhor performance para consultas complexas que não mudam frequentemente.

🔄 View Normal vs Materializada

View Normal

• Executa consulta a cada acesso
• Dados sempre atualizados
• Pode ser lenta para consultas complexas
• Não ocupa espaço

View Materializada

• Armazena resultado fisicamente
• Dados podem ficar desatualizados
• Acesso muito rápido
• Ocupa espaço em disco

Criando Views Materializadas

-- Criar view materializada
CREATE MATERIALIZED VIEW relatorio_vendas_mensais AS
SELECT 
    EXTRACT(YEAR FROM data_venda) as ano,
    EXTRACT(MONTH FROM data_venda) as mes,
    COUNT(*) as total_vendas,
    SUM(valor_total) as receita_total,
    AVG(valor_total) as ticket_medio
FROM vendas
GROUP BY 
    EXTRACT(YEAR FROM data_venda),
    EXTRACT(MONTH FROM data_venda)
ORDER BY ano DESC, mes DESC;

-- Consultar (muito rápido!)
SELECT * FROM relatorio_vendas_mensais 
WHERE ano = 2024;

Atualizando Views Materializadas

-- Atualização completa
REFRESH MATERIALIZED VIEW relatorio_vendas_mensais;

-- Atualização concorrente (não bloqueia leituras)
REFRESH MATERIALIZED VIEW CONCURRENTLY relatorio_vendas_mensais;

-- Remover view materializada
DROP MATERIALIZED VIEW relatorio_vendas_mensais;

Dica:

Use views materializadas para relatórios e dashboards que não precisam de dados em tempo real. Configure atualizações automáticas via cron jobs.

Stored Procedures

Stored procedures são blocos de código SQL que ficam armazenados no banco de dados e podem ser executados quando necessário. No PostgreSQL, usamos functions.

🚀 Benefícios das Functions

• Performance: Código compilado e otimizado

• Reutilização: Lógica centralizada no banco

• Segurança: Controle de acesso granular

• Transações: Operações atômicas complexas

• Validação: Regras de negócio no banco

Functions Simples

-- Function que calcula desconto
CREATE OR REPLACE FUNCTION calcular_desconto(
    valor_original DECIMAL,
    percentual_desconto DECIMAL
) RETURNS DECIMAL AS $$
BEGIN
    RETURN valor_original * (1 - percentual_desconto / 100);
END;
$$ LANGUAGE plpgsql;

-- Usando a function
SELECT 
    nome,
    preco,
    calcular_desconto(preco, 10) as preco_com_desconto
FROM produtos;

Functions com Lógica Complexa

-- Function para processar venda
CREATE OR REPLACE FUNCTION processar_venda(
    p_cliente_id INTEGER,
    p_produto_id INTEGER,
    p_quantidade INTEGER,
    p_vendedor_id INTEGER
) RETURNS INTEGER AS $$
DECLARE
    v_preco DECIMAL;
    v_estoque INTEGER;
    v_venda_id INTEGER;
    v_valor_total DECIMAL;
BEGIN
    -- Verificar se produto existe e obter preço
    SELECT preco, estoque INTO v_preco, v_estoque
    FROM produtos 
    WHERE id = p_produto_id AND ativo = true;
    
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Produto não encontrado ou inativo';
    END IF;
    
    -- Verificar estoque
    IF v_estoque < p_quantidade THEN
        RAISE EXCEPTION 'Estoque insuficiente. Disponível: %', v_estoque;
    END IF;
    
    -- Calcular valor total
    v_valor_total := v_preco * p_quantidade;
    
    -- Inserir venda
    INSERT INTO vendas (cliente_id, vendedor_id, data_venda, valor_total)
    VALUES (p_cliente_id, p_vendedor_id, NOW(), v_valor_total)
    RETURNING id INTO v_venda_id;
    
    -- Inserir item da venda
    INSERT INTO itens_venda (venda_id, produto_id, quantidade, preco_unitario)
    VALUES (v_venda_id, p_produto_id, p_quantidade, v_preco);
    
    -- Atualizar estoque
    UPDATE produtos 
    SET estoque = estoque - p_quantidade
    WHERE id = p_produto_id;
    
    RETURN v_venda_id;
END;
$$ LANGUAGE plpgsql;

Executando Functions

-- Executar function
SELECT processar_venda(1, 5, 2, 3) as venda_id;

-- Function em transação
BEGIN;
    SELECT processar_venda(1, 5, 2, 3);
    -- Se algo der errado, pode fazer ROLLBACK
COMMIT;

Functions que Retornam Tabelas

-- Function que retorna múltiplas linhas
CREATE OR REPLACE FUNCTION vendas_por_periodo(
    data_inicio DATE,
    data_fim DATE
) RETURNS TABLE(
    venda_id INTEGER,
    cliente_nome VARCHAR,
    valor_total DECIMAL,
    data_venda DATE
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        v.id,
        c.nome,
        v.valor_total,
        v.data_venda::DATE
    FROM vendas v
    JOIN clientes c ON v.cliente_id = c.id
    WHERE v.data_venda BETWEEN data_inicio AND data_fim
    ORDER BY v.data_venda DESC;
END;
$$ LANGUAGE plpgsql;

-- Usando
SELECT * FROM vendas_por_periodo('2024-01-01', '2024-01-31');

Triggers (Gatilhos)

Triggers são functions especiais que são executadas automaticamente em resposta a eventos no banco de dados (INSERT, UPDATE, DELETE).

Trigger de Auditoria

-- Criar tabela de auditoria
CREATE TABLE auditoria_produtos (
    id SERIAL PRIMARY KEY,
    produto_id INTEGER,
    operacao VARCHAR(10),
    dados_antigos JSONB,
    dados_novos JSONB,
    usuario VARCHAR(100),
    data_operacao TIMESTAMP DEFAULT NOW()
);

-- Function do trigger
CREATE OR REPLACE FUNCTION auditar_produtos()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO auditoria_produtos (
        produto_id,
        operacao,
        dados_antigos,
        dados_novos,
        usuario
    ) VALUES (
        COALESCE(NEW.id, OLD.id),
        TG_OP,
        CASE WHEN TG_OP = 'DELETE' THEN row_to_json(OLD) ELSE NULL END,
        CASE WHEN TG_OP != 'DELETE' THEN row_to_json(NEW) ELSE NULL END,
        current_user
    );
    
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

-- Criar trigger
CREATE TRIGGER trigger_auditoria_produtos
    AFTER INSERT OR UPDATE OR DELETE ON produtos
    FOR EACH ROW EXECUTE FUNCTION auditar_produtos();

Trigger de Validação

-- Function de validação
CREATE OR REPLACE FUNCTION validar_produto()
RETURNS TRIGGER AS $$
BEGIN
    -- Validar preço
    IF NEW.preco <= 0 THEN
        RAISE EXCEPTION 'Preço deve ser maior que zero';
    END IF;
    
    -- Validar estoque
    IF NEW.estoque < 0 THEN
        RAISE EXCEPTION 'Estoque não pode ser negativo';
    END IF;
    
    -- Normalizar nome
    NEW.nome := TRIM(UPPER(NEW.nome));
    
    -- Atualizar data de modificação
    NEW.data_atualizacao := NOW();
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Criar trigger
CREATE TRIGGER trigger_validar_produto
    BEFORE INSERT OR UPDATE ON produtos
    FOR EACH ROW EXECUTE FUNCTION validar_produto();

Exercício Prático

Vamos praticar views e functions!

Analise os cenários e escolha a melhor abordagem:

1. Você precisa de uma consulta que mostra produtos com suas categorias, usada em várias partes do sistema:

SELECT p.nome, p.preco, c.nome as categoria
FROM produtos p
JOIN categorias c ON p.categoria_id = c.id
WHERE p.ativo = true;

Qual a melhor solução?

Repetir a consulta em cada lugar Criar uma view Criar uma function

2. Qual é a principal diferença entre view normal e materializada?

View materializada é mais segura View materializada armazena dados fisicamente View materializada permite UPDATE

3. Quando usar uma stored procedure/function?

Para consultas simples de SELECT Para lógica de negócio complexa Para criar tabelas

4. O que são triggers?

Functions executadas manualmente Functions executadas automaticamente por eventos Views que se atualizam sozinhas

Resumo da Aula

Views simplificam consultas complexas e melhoram a segurança
Views materializadas oferecem melhor performance para relatórios
Functions centralizam lógica de negócio no banco de dados
Triggers automatizam operações baseadas em eventos
Use essas ferramentas para criar sistemas mais robustos

Aula Anterior: Índices e Performance Próxima Aula: Backup e Segurança