Procedimentos Armazenados em PostgreSQL (Stored Procedures)

Postado Em 03/09/2024 [data do post] por Fábio dos Reis [autor do post] em PostgreSQL [categoria do post]

Procedimentos Armazenados em PostgreSQL (Stored Procedures)

Procedimentos armazenados são uma forma de armazenar a lógica do banco de dados no lado do servidor e podem ser executados por vários clientes. Eles são uma ferramenta muito útil para administradores e programadores de banco de dados, pois permitem encapsular a lógica complexa em uma única unidade chamável, reduzindo o código duplicado, aumentando a segurança e melhorando o desempenho.

Um procedimento armazenado no PostgreSQL é um bloco nomeado de instruções PL/pgSQL que podem ser executadas com um único comando. A sintaxe para criar um procedimento armazenado no PostgreSQL é a seguinte:

CREATE [OR REPLACE PROCEDURE] nome_procedimento(arg1 tipo_dado1, arg2 tipo_dado2, ...)
RETURNS tipo_dado_retorno
LANGUAGE plpgsql
AS $$
DECLARE
-- declaração de variáveis locais, se houver
BEGIN
-- declarações PL/pgSQL
END;
$$;

Onde:

  • nome_procedimento é o nome do procedimento armazenado
  • arg1, arg2, etc. são os parâmetros do procedimento armazenado
  • tipo_dado1, tipo_dado2, etc. são os tipos de dados dos parâmetros
  • tipo_dado_retorno é o tipo de dados do valor de retorno

e a seção DECLARE é usada para declarar variáveis locais, se necessário. A lógica real do procedimento armazenado é escrita dentro de blocos BEGIN e END, usando instruções da linguagem PL/pgSQL.

A cláusula LANGUAGE especifica o idioma do procedimento armazenado, que é plpgsql neste caso.

O símbolo $$

O símbolo $$ usado na última linha de um procedimento PL/pgSQL é usado para definir o início e o fim do corpo do procedimento. É uma sintaxe especial usada para definir uma string literal que abrange várias linhas. Na prática, é possível usar qualquer string com essa finalidade, desde que não seja uma palavra reservada da linguagem.

Em um procedimento PL/pgSQL, o $$ é usado para envolver todo o corpo do procedimento, que inclui a declaração de variáveis, a definição de estruturas de controle e qualquer outra lógica que você queira implementar. O primeiro $$ marca o início do corpo do procedimento e o segundo $$ marca o fim do corpo do procedimento.

Outra opção é usar a estrutura “BEGIN ATOMIC” … “END” no lugar de “AS $$“.

O que é PL/pgSQL?

PL/pgSQL é uma linguagem procedural usada no PostgreSQL para escrever stored procedures, triggers e funções. É uma linguagem estruturada em blocos semelhante a outras linguagens procedurais, como PL/SQL no Oracle e T-SQL no Microsoft SQL Server. Essa linguagem fornece estruturas de controle, como instruções IF-THEN-ELSE, LOOP e EXIT, bem como a capacidade de declarar variáveis, realizar tratamento de exceções e usar cursores.

O PL/pgSQL oferece uma interface procedural de alto nível para o banco de dados que permite criar lógica de negócios complexa e automatizar as operações do banco de dados. Por exemplo, podemos usar PL/pgSQL para implementar regras de negócios, validar dados ou realizar cálculos complexos em dados armazenados no banco de dados.

Assim, podemos escrever lógica que pode ser facilmente testada e reutilizada, tornando-a uma maneira eficiente de gerenciar operações de banco de dados.

Trata-se de uma linguagem do lado do servidor, o que significa que o código é executado no servidor de banco de dados, e não no cliente. Isso o torna mais rápido e eficiente do que executar o script do lado do cliente, dentro de uma aplicação de usuário, além de fornecer melhor segurança para seus dados.

Outras opções de linguagens suportadas pelo Postgres incluem SQL padrão e linguagem C, além da possibilidade de trabalhar com extensões para outras linguagens como PL/Python, PL/Perl, etc.

A instrução LANGUAGE plpgsql é necessária ao criar procedimentos armazenados no PostgreSQL porque define a linguagem de programação que será usada para escrever o corpo do procedimento.

Como executar o procedimento armazenado

Podemos chamar um procedimento armazenado no PostgreSQL usando a seguinte sintaxe:

CALL nome_procedimento(arg1_valor, arg2_valor, ...);

Aqui, arg1_valor, arg2_valor, etc. são os valores dos parâmetros passados para o procedimento armazenado. O resultado do procedimento armazenado será retornado na forma de uma tabela.

Vejamos agora alguns exemplos de criação e execução de procedimentos armazenados em PostgreSQL.

Exemplo 01 – Inserir uma linha em uma tabela

Procedimento armazenado em PostgreSQL (somente SQL) que insere uma nova linha em uma tabela, composta por três colunas: nome, sobrenome e idade:

Criar a tabela de teste:

CREATE TABLE pessoas (
    nome varchar(20),
    sobrenome varchar(40),
    idade smallint
);

Vamos a um exemplo de procedimento armazenado em PL/pgSQL que insere uma nova linha em uma tabela com três colunas: nome, sobrenome e idade. O procedimento armazenado usa três parâmetros: p_nome, p_sobrenome e p_idade e insere esses valores na tabela.

CREATE OR REPLACE PROCEDURE cadastra_pessoa(p_nome varchar(20), p_sobrenome varchar(40), p_idade smallint)
  LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO pessoas (nome, sobrenome, idade)
    VALUES (p_nome, p_sobrenome, p_idade);
END
$$

Teste

Para executar o procedimento armazenado, basta chamá-lo com os valores desejados como parâmetros, assim:

CALL cadastra_pessoa('Fábio'::varchar, 'dos Reis'::varchar, 48::smallint);

E verificar se a tabela foi atualizada com o novo registro:

SELECT * FROM pessoas;

Procedimento armazenado no PostgreSQL

Especificar tipos de dados na chamada do procedimento

Ao chamar um procedimento armazenado no PostgreSQL, a necessidade de especificar explicitamente os tipos de dados para os argumentos (por exemplo, ‘Fábio’::varchar) geralmente surge devido a questões de desambiguação e coerção de tipos.

Os principais motivos para especificar tipos de dados ao chamar um procedimento armazenado no PostgreSQL são os seguntes:

  1. Desambiguação: Quando o PostgreSQL possui múltiplos procedimentos com o mesmo nome, mas com assinaturas diferentes (ou seja, com diferentes tipos de parâmetros ou números de parâmetros), ele precisa saber exatamente qual procedimento deve ser chamado. Especificar o tipo de dado ajuda o PostgreSQL a escolher a versão correta do procedimento.
  2. Coerção de Tipos: O PostgreSQL é uma linguagem fortemente tipada, o que significa que cada valor tem um tipo de dado associado, e algumas operações requerem tipos específicos. Quando se passa um literal (como ‘Fábio‘), ele é inicialmente interpretado como um text, a menos que se indique explicitamente um tipo diferente, como o varchar.
  3. Prevenção de Erros de Tipagem: Quando o tipo de dado esperado pelo procedimento não corresponde ao tipo inferido pelo PostgreSQL, o banco de dados pode não ser capaz de fazer a coerção automaticamente, resultando em erros. Especificar o tipo assegura que o valor será tratado corretamente.

Exemplo 02 – Inserir um novo produto na tabela

Vamos criar um procedimento armazenado que permita inserir um novo produto na tabela de produtos de nosso banco de dados.

CREATE OR REPLACE PROCEDURE inserir_produto (
    p_cod_produto INT,
    p_nome_produto VARCHAR(30),
    p_descricao TEXT,
    p_preco NUMERIC,
    p_qtde_estoque SMALLINT
)
language plpgsql
AS $$
BEGIN
    INSERT INTO Produtos(cod_produto, nome_produto, descricao, preco, qtde_estoque)
    VALUES (p_cod_produto, p_nome_produto, p_descricao, p_preco, p_qtde_estoque);
    RAISE NOTICE 'Produto % cadastrado com sucesso.', p_nome_produto;
END;
$$;

A instrução RAISE NOTICE emite uma mensagem para o console confirmando a inserção do registro.

Para executar o procedimento:

CALL inserir_produto(4,'Detergente'::varchar,'Detergente neutro 500 ml'::text, 2.35, 2::smallint);

E então podemos verificar se produto foi inserido corretamente realizando uma consulta simples ao banco de dados:

SELECT * FROM Produtos;

Exemplo 03 – Atualizar o preço de um produto.

Este procedimento atualiza o preço de um produto específico, identificado pelo seu código de produto:

CREATE OR REPLACE PROCEDURE atualizar_preco(
    p_cod_produto INT,
    p_novo_preco NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE Produtos
    SET preco = p_novo_preco
    WHERE cod_produto = p_cod_produto;
    RAISE NOTICE 'Preço do produto % atualizado para %', p_cod_produto, p_novo_preco;
END;
$$;

Para executar o procedimento:

CALL atualizar_preco(1, 3.80);

E então podemos verificar se produto teve seu preço corretamente atualizado realizando novamente uma consulta ao banco de dados:

SELECT * FROM Produtos;

Exemplo 04 – Procedimento para consultar produtos com estoque baixo

Este procedimento retorna todos os produtos com quantidade em estoque abaixo de um valor especificado na chamada da procedure.

CREATE OR REPLACE PROCEDURE consultar_estoque_baixo (
    p_limite_estoque INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE resultado RECORD;
BEGIN
    FOR resultado in
        SELECT cod_produto, nome_produto, qtde_estoque
        FROM Produtos
        WHERE qtde_estoque < p_limite_estoque
    LOOP
        RAISE NOTICE 'Produto: %, Estoque: % ', resultado.nome_produto, resultado.qtde_estoque;
    END LOOP;
END;
$$;

Para executar o procedimento:

CALL consultar_estoque_baixo(3);

Aqui, consultamos todos os produtos cujo estoque está abaixo de 3 unidades.

Devemos notar alguns detalhes interessantes sobre a consulta realizada pelo procedimento armazenado deste exemplo:

Destino dos dados

Quando se usa a instrução SELECT em um procedimento armazenado no PostgreSQL, o resultado deve ser direcionado para algum lugar, como uma variável, uma tabela temporária, ou ser retornado ao chamador da função ou procedimento.

Caso contrário, o PostgreSQL gera a mensagem “consulta não tem destino para os dados resultantes” (no inglês: “query has no destination for result data“).

Se for necessário apenas exibir os resultados no console em um procedimento armazenado, sem retorná-los em uma consulta, podemos usar um loop para iterar sobre os resultados e então exibi-los com RAISE NOTICE.

O tipo RECORD

O tipo RECORD é um tipo de dado genérico no PL/pgSQL que pode acomodar uma linha de dados com colunas de diferentes tipos. Ao contrário de outros tipos de variáveis, que têm um tipo de dado específico (como INTEGER, VARCHAR, etc.), o RECORD pode armazenar qualquer linha inteira de resultado, independentemente de quantas colunas ou de quais tipos de dados essas colunas têm.

A instrução DECLARE r RECORD; em PL/pgSQL é usada para declarar uma variável de tipo RECORD.

Quando usamos o RECORD dentro de um loop FOR IN que itera sobre os resultados de uma consulta, o RECORD é automaticamente “preenchido” com os valores das colunas para cada linha retornada. Isso permite acessar os valores dessas colunas dentro do corpo do loop.

Além disso, uma vez que a linha é armazenada no RECORD, podemos acessar as colunas individuais usando a notação r.coluna, onde r é o nome da variável RECORD e coluna é o nome da coluna que desejamos acessar.

Exemplo 05 – Inserir e atualizar valores no mesmo procedimento.

Exemplo: criar um procedimento armazenado que insira um novo produto na tabela de produtos, ao mesmo tempo em que aplica um desconto percentual especificado a todos os produtos logo após.

CREATE OR REPLACE PROCEDURE insere_atualiza (cod int, prod varchar(30), descr text, valor numeric, qtde smallint, desc_perc numeric)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO produtos(cod_produto, nome_produto, descricao, preco, qtde_estoque)
    VALUES (cod, prod, descr, valor, qtde);
    UPDATE produtos SET preco = preco * (100 - desc_perc) / 100;
END;
$$;

Executar o procedimento:

CALL insere_atualiza(6,'Alvejante'::varchar(30),'Alvejante de roupas'::text, 12.30, 12::smallint, 10);

Obs.: O tipo numeric geralmente não precisa ser informado (cast) na chamada do procedimento.

Como excluir um procedimento armazenado

Para excluir um procedimento armazenado no PostgreSQL devemos usar a declaração DROP PROCEDURE, como segue:

DROP PROCEDURE nome_procedimento;

Por exemplo, para excluir o procedimento consultar_estoque_baixo:

DROP PROCEDURE consultar_estoque_baixo;

Se houver mais de um procedimento armazenado como mesmo nome, mas com quantidade e tipos de parâmetros distintos (polimorfismo), devemos indicar os tipos de dados dos argumentos ao exclui-lo:

DROP PROCEDURE insere_atualiza(int,varchar,text,numeric,smallint,numeric);

Conclusão

Neste artigo vimos como começar a trabalhar com procedimentos armazenados no PostgreSQL. com alguns exemplos simples de criação de procedures simples. O recurso é muito útil para simplificar código e facilitar a programação de tarefas no banco de dados, principalmente quando as declarações SQL precisam ser executadas a partir de uma aplicação escrita em alguma linguagem de programação para acesso direto dos usuários.

Estudaremos recursos mais avançados de procedimentos armazenados nos próximos artigos, assim como outras formas de programação existentes para bancos de dados PostgreSQL, como Funções e Triggers.

Para saber mais sobre Procedimentos Armazenados no PostgreSQL, assista ao vídeo a seguir da Bóson Treinamentos no YouTube:

Sobre Fábio dos Reis (1207 Artigos)
Fábio dos Reis trabalha com tecnologias variadas há mais de 30 anos, tendo atuado nos campos de Eletrônica, Telecomunicações, Programação de Computadores e Redes de Dados. É um entusiasta de Ciência e Tecnologia em geral, adora Viagens e Música, e estuda idiomas, além de ministrar cursos e palestras sobre diversas tecnologias em São Paulo e outras cidades do Brasil.
Contato: WebsiteYouTube

Artigos Relacionados

Escreva um comentário

Seu e-mail não será divulgado


*


Copyright © 2024 | MH Magazine WordPress Theme by MH Themes