5.5. Atividade¶

A fim de ilustrar os conceitos de tratamento de erros e manipulação de cursores, vamos apresentar um exemplo prático que envolve a criação de tabelas, a inserção de dados e a criação de uma função em PL/pgSQL. Essa função irá calcular a quantidade total de produtos em estoque, percorrendo um conjunto de registros usando um cursor e tratando exceções ao longo do processo.

Atividade de exemplo 1: Criação e Utilização de Funções¶

Nesta atividade de exemplo, vamos criar um cenário em que temos duas tabelas: "produtos" e "estoque". A tabela produtos armazena informações sobre os produtos, como "id", "nome" e "preço". A tabela estoque armazena as quantidades disponíveis de cada produto.

O objetivo dessa atividade é criar as tabelas, inserir alguns registros de exemplo e criar uma função em PL/pgSQL chamada "calcular_quantidade_total()". Essa função irá percorrer todos os registros da tabela "produtos", consultando a "quantidade" correspondente na tabela "estoque" e calcular a quantidade total de produtos disponíveis no estoque.

Ao final da atividade, poderemos utilizar a função "calcular_quantidade_total()" para obter o valor total da quantidade de produtos disponíveis no estoque.

Agora, vamos realizar a atividade passo a passo para entender como alcançar o objetivo proposto.

Criando as tabelas "produtos" e "estoque". A tabela produtos armazena informações sobre os produtos, como "id", "nome" e "preço". A tabela estoque armazena a "quantidade" disponíveis de cada produto:

-- Criação das tabelas
CREATE TABLE produtos (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(100),
    preco NUMERIC(10,2)
);

CREATE TABLE estoque (
    produto_id INTEGER REFERENCES produtos(id),
    quantidade INTEGER
);

Inserindo dados de exemplos nas tabelas "produtos" e "estoque":

-- Inserção de registros de exemplo
INSERT INTO produtos (nome, preco) VALUES ('Produto 1', 10.00);
INSERT INTO produtos (nome, preco) VALUES ('Produto 2', 15.00);
INSERT INTO produtos (nome, preco) VALUES ('Produto 3', 20.00);

INSERT INTO estoque (produto_id, quantidade) VALUES (1, 50);
INSERT INTO estoque (produto_id, quantidade) VALUES (2, 75);
INSERT INTO estoque (produto_id, quantidade) VALUES (3, 100);

Criando a função "calcular_quantidade_total()" para obter o valor total da quantidade de produtos disponíveis no estoque:
```
-- Criação da função em PL/pgSQL
CREATE OR REPLACE FUNCTION calcular_quantidade_total() RETURNS INTEGER AS $$
DECLARE
    total INTEGER := 0;
    produto RECORD;
BEGIN
    FOR produto IN (SELECT * FROM produtos) LOOP
        total := total + (SELECT quantidade FROM estoque WHERE produto_id = produto.id);
    END LOOP;

    RETURN total;
END;
$$ LANGUAGE plpgsql;
```
Neste exemplo, criamos as tabelas produtos e estoque, inserimos alguns registros de exemplo e criamos uma função chamada calcular_quantidade_total() em PL/pgSQL.

A função calcular_quantidade_total() percorre todos os registros da tabela produtos usando um loop FOR e, para cada produto, soma a quantidade correspondente na tabela estoque à variável total. No final, a função retorna o valor total.
Para utilizar a função, você pode simplesmente chamá-la em uma consulta SQL:
```
SELECT calcular_quantidade_total();
```
Isso retornará o valor total da quantidade de produtos disponíveis no estoque.

Esse exemplo ilustra o uso de variáveis e estruturas de controle em PL/pgSQL para realizar operações mais complexas no PostgreSQL. Você pode adaptar esse exemplo e explorar outras funcionalidades da linguagem PL/pgSQL de acordo com suas necessidades específicas.

Atividade de exemplo 2: Tratamento de erros e manipulação de cursores¶

Nesta atividade de exemplo, vamos aprimorar a função "calcular_quantidade_total()" criada na atividade anterior, incluindo o tratamento de exceções e a manipulação de cursores.

O objetivo dessa atividade é percorrer os registros da tabela "produtos" usando um cursor, obter a quantidade de estoque para cada produto e tratar exceções caso a quantidade de estoque não seja encontrada ou ocorra algum erro durante o processo.

Para realizar essa atividade, vamos fazer o seguinte:

Declaração do cursor: Vamos declarar um cursor chamado cur que irá selecionar os campos id e nome da tabela produtos.
Tratamento de exceções: Vamos incluir tratamento de exceções para verificar se a quantidade de estoque é nula. Se for nula, iremos lançar uma exceção personalizada.
Manipulação do cursor: Vamos percorrer os registros do cursor usando um loop e, para cada registro, obter a quantidade de estoque correspondente na tabela estoque. Em seguida, iremos incrementar o total com a quantidade de estoque.
Tratamento de exceções: Vamos capturar e tratar exceções caso ocorra algum erro durante o processo de manipulação do cursor.

Ao final da atividade, a função "calcular_quantidade_total()" estará aprimorada, permitindo percorrer os registros da tabela "produtos", obter a "quantidade" de estoque para cada produto e tratar exceções quando necessário.

Nota

Lembre-se de executar as etapas anteriores para criar as tabelas e inserir os registros de exemplo antes de prosseguir com esta atividade.

Agora, podemos acompanhar os ajustes feitos na função "calcular_quantidade_total()" de forma passo a passo através dos comentários, buscando assim alcançar o objetivo proposto.

-- Criação da função em PL/pgSQL
CREATE OR REPLACE FUNCTION calcular_quantidade_total() RETURNS INTEGER AS $$
DECLARE
    total INTEGER := 0;
    produto RECORD;
    quantidade_estoque INTEGER;
BEGIN
    -- Declaração do cursor
    DECLARE cur CURSOR FOR SELECT id, nome FROM produtos;

    -- Tratamento de exceções
    BEGIN
        -- Abrir o cursor
        OPEN cur;

        -- Loop para percorrer os registros do cursor
        LOOP
            -- Obtém o próximo registro do cursor
            FETCH cur INTO produto;

            -- Sai do loop se não houver mais registros
            EXIT WHEN NOT FOUND;

            -- Obter a quantidade de estoque para o produto atual
            SELECT quantidade INTO quantidade_estoque FROM estoque WHERE produto_id = produto.id;

            -- Tratamento de exceções
            BEGIN
                -- Verificar se a quantidade de estoque é nula
                IF quantidade_estoque IS NULL THEN
                    -- Lançar uma exceção personalizada
                    RAISE EXCEPTION 'Quantidade de estoque não encontrada para o produto %', produto.nome;
                END IF;

                -- Incrementar o total com a quantidade de estoque
                total := total + quantidade_estoque;
            EXCEPTION
                -- Capturar e tratar a exceção personalizada
                WHEN OTHERS THEN
                    -- Imprimir uma mensagem de erro personalizada
                    RAISE NOTICE 'Erro ao calcular quantidade de estoque para o produto %: %', produto.nome, SQLERRM;
            END;
        END LOOP;
    EXCEPTION
        -- Capturar e tratar outras exceções
        WHEN OTHERS THEN
            -- Imprimir uma mensagem de erro genérica
            RAISE NOTICE 'Erro ao percorrer o cursor: %', SQLERRM;
    END;

    -- Fechar o cursor
    CLOSE cur;

    -- Retornar o total
    RETURN total;
END;
$$ LANGUAGE plpgsql;

A linguagem PL/pgSQL oferece recursos poderosos para criar funções e procedimentos armazenados no PostgreSQL. Com ela, é possível desenvolver lógica de programação complexa, manipular dados eficientemente e lidar com exceções. Dominar a linguagem PL/pgSQL permitirá que você crie soluções personalizadas e avançadas dentro do PostgreSQL. Explore e pratique os recursos da PL/pgSQL para aprimorar suas habilidades e desenvolver funcionalidades sofisticadas. Continue aperfeiçoando seu conhecimento, para isso consulte a documentação oficial do PL/pgSQL clicando aqui.

Atividade de exemplo 3: Utilização de Triggers¶

Nesta atividade de exemplo, vamos criar uma trigger no PostgreSQL para automatizar uma tarefa específica quando ocorrer uma operação de atualização em uma tabela.

O objetivo dessa atividade é criar uma tabela "clientes" e uma trigger que será acionada após a atualização de um registro nessa tabela. A trigger irá atualizar a data de modificação do cliente para a data atual sempre que ocorrer uma atualização.

A seguir estão as etapas para realizar essa atividade:

Criar a tabela "clientes" com os seguintes campos: "id", "nome" e "data_modificacao":

-- Criação da tabela
CREATE TABLE clientes (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(100),
    data_modificacao DATE
);

Inserir alguns registros de exemplo na tabela "clientes":

-- Inserção de registros de exemplo
INSERT INTO clientes (nome, data_modificacao) VALUES ('Cliente 1', '2023-02-15');
INSERT INTO clientes (nome, data_modificacao) VALUES ('Cliente 2', '2023-02-20');
INSERT INTO clientes (nome, data_modificacao) VALUES ('Cliente 3', '2023-02-25');

Criar a função "atualizar_data_modificacao" que será executada pela trigger após a atualização de um registro na tabela "clientes":

-- Criação da função em PL/pgSQL
CREATE OR REPLACE FUNCTION atualizar_data_modificacao()
RETURNS TRIGGER AS $$
BEGIN
    NEW.data_modificacao := CURRENT_DATE;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Criar a trigger "trigger_atualizar_data_modificacao" que será acionada após a atualização de um registro na tabela "clientes":

-- Criação da trigger
CREATE TRIGGER trigger_atualizar_data_modificacao
AFTER UPDATE ON clientes
FOR EACH ROW
EXECUTE FUNCTION atualizar_data_modificacao();

Nesse exemplo, a função "atualizar_data_modificacao" atualiza o valor do campo "data_modificacao" do registro atual (representado pela variável NEW) para a data atual utilizando a função CURRENT_DATE. A trigger "trigger_atualizar_data_modificacao" é acionada após a atualização de um registro na tabela "clientes" e chama a função "atualizar_data_modificacao".

Realizar uma atualização em um registro da tabela "clientes":
```
-- Atualização de um registro
UPDATE clientes
SET nome = 'Cliente Atualizado'
WHERE id = 1;
```
Após executar essa atualização, a trigger será acionada automaticamente e atualizará a data de modificação do cliente para a data atual.

Essa atividade de exemplo demonstra a utilização de triggers para automatizar tarefas específicas em resposta a eventos de atualização em uma tabela. As triggers são acionadas automaticamente e permitem a execução de lógica personalizada em resposta a eventos específicos, como inserção, atualização ou exclusão de registros.

Ao utilizar triggers, é possível automatizar tarefas, aplicar lógica personalizada e manter a consistência dos dados de forma eficiente. Elas são particularmente úteis para implementar ações complexas, como atualizar outras tabelas, verificar restrições de integridade, auditar alterações de dados e muito mais.

Para aprimorar suas habilidades com triggers, você pode explorar outros recursos disponíveis, como a utilização das variáveis OLD e NEW para acessar os valores antigos e novos dos campos afetados pela operação que acionou a trigger. Além disso, você pode aprofundar seus conhecimentos consultando a documentação oficial do PostgreSQL sobre triggers.

Lembre-se de considerar o desempenho ao utilizar triggers, pois elas são executadas em resposta a eventos de alteração de dados e podem impactar o tempo de execução das operações no banco de dados. É importante projetar e otimizar suas triggers de forma adequada para evitar degradação do desempenho.

Com a combinação de funções, procedimentos armazenados e triggers, você tem um conjunto poderoso de recursos à sua disposição para criar soluções avançadas e personalizadas no PostgreSQL. Explore e pratique esses recursos para aprimorar suas habilidades e maximizar o potencial do PostgreSQL em seus projetos.

Continue aperfeiçoando seu conhecimento, consultando a documentação oficial do PL/pgSQL e do PostgreSQL. A documentação é uma fonte valiosa de informações e exemplos que podem ajudar você a compreender melhor os recursos e a sintaxe dessas tecnologias.

Dedique-se a praticar e desenvolver projetos reais utilizando as funcionalidades apresentadas neste guia. A prática é essencial para a consolidação do conhecimento e para a melhoria das suas habilidades como programador.

Parabéns por concluir este guia sobre funções, procedimentos armazenados e triggers no PostgreSQL. Espero que você tenha adquirido novos conhecimentos e esteja preparado para aplicar esses conceitos em seus projetos futuros.