Tutorial: Utilizando Stored Procedures no Oracle

Uma Stored procedure é que um código SQL que salvamos para reutilizarmos várias vezes, então ao invés de escrevermos o mesmo código muitas vezes, nós apenas salvamos o código como uma stored procedure e chamamos ele para a execução quando for necessário. Para ter uma explicação mais detalhada visite SQL Stored Procedures - Na visão de um estudante.red

As Stored procedures oferecem uma série de vantagens sobre o código SQL tradicional. Elas podem melhorar o desempenho, a segurança e a reusabilidade do código.

Criação de uma stored procedure

Vamos para o passo a passo para criar uma stored procedure:

CREATE [OR REPLACE] PROCEDURE nome_da_procedure [(lista_de_parametros)]
AS
BEGIN
  -- Corpo da stored procedure
END;

A lista de parâmetros é opcional. Se não houver parâmetros, o bloco de código da stored procedure será executado diretamente.

Os parâmetros podem ser passados para a stored procedure como IN, OUT ou IN OUT.

IN - Os parâmetros IN são usados para passar valores para a stored procedure.

OUT - Os parâmetros OUT são usados para retornar valores da stored procedure.

IN OUT - Os parâmetros IN OUT podem ser usados para passar valores para a stored procedure e retornar valores dela

Segue abaixo alguns exemplos na prática do uso de stored procedures:

Exemplo 1: Inserir um novo registro

Criamos a procedure inserir_cliente que recebe 5 parametros e os insere na tabela clinte.

CREATE PROCEDURE insere_cliente (
  p_nome IN VARCHAR2,
  p_cpf IN VARCHAR2,
  p_endereco IN VARCHAR2,
  p_cidade IN VARCHAR2,
  p_estado IN VARCHAR2
)
AS
BEGIN
  INSERT INTO clientes (nome, cpf, endereco, cidade, estado)
  VALUES (p_nome, p_cpf, p_endereco, p_cidade, p_estado);
END;

Exemplo 2: Atualizar um registro existente

CREATE PROCEDURE atualiza_cliente (
  p_id IN NUMBER,
  p_nome IN VARCHAR2,
  p_cpf IN VARCHAR2,
  p_endereco IN VARCHAR2,
  p_cidade IN VARCHAR2,
  p_estado IN VARCHAR2
)
AS
BEGIN
  UPDATE clientes
  SET nome = p_nome,
    cpf = p_cpf,
    endereco = p_endereco,
    cidade = p_cidade,
    estado = p_estado
  WHERE id = p_id;
END;

Exemplo 3: Excluir um registro

CREATE PROCEDURE exclui_cliente (
  p_id IN NUMBER
)
AS
BEGIN
  DELETE FROM clientes
  WHERE id = p_id;
END;

Exemplo 4: Realizar uma consulta

SQL
CREATE PROCEDURE consulta_clientes (
  p_nome IN VARCHAR2
)
AS
BEGIN
  SELECT *
  FROM clientes
  WHERE nome LIKE '%' || p_nome || '%';
END;

Exemplo 5: Calcular um valor

CREATE PROCEDURE calcula_valor_total (
  p_id_venda IN NUMBER
)
AS
BEGIN
  -- Obtém os valores dos itens da venda
  SELECT
    qtd,
    preco_unitario
  FROM itens_da_venda
  WHERE id_venda = p_id_venda;

  -- Calcula o valor total da venda
  v_valor_total := 0;
  FOR i IN 1..v_qtd_itens LOOP
    v_valor_total := v_valor_total + v_qtd[i] * v_preco_unitario[i];
  END LOOP;

  -- Retorna o valor total
  RETURN v_valor_total;
END;

Cenários e casos de uso

Inserir, atualizar ou excluir dados de tabelas: Stored procedures podem ser usadas para automatizar tarefas comuns de gerenciamento de dados, como inserir novos registros, atualizar registros existentes ou excluir registros.

Alguns exemplos:

Gerenciamento de dados: Automatizam tarefas comuns como inserir novos registros, atualizar registros existentes ou excluir registros. Por exemplo, podem ser usadas para inserir novos clientes em uma tabela de clientes ou atualizar os dados de um cliente existente.

Realização de cálculos: Executam cálculos complexos, como calcular o valor total de uma venda ou o saldo de uma conta. Por exemplo, podem ser usadas para calcular o valor total de uma venda, somando o preço de cada item vendido.

Execução de consultas: Realizam consultas complexas. Por exemplo, podem ser usadas para recuperar todos os clientes que moram em uma determinada cidade.

Exemplos de cenários e casos de uso

Sistema de e-commerce: Uma stored procedure pode processar um pedido de compra, inserindo um novo registro na tabela de pedidos, atualizando os estoques dos produtos vendidos e enviando um e-mail ao cliente confirmando o pedido.

Sistema de gerenciamento de recursos humanos: Uma stored procedure pode calcular o salário de um funcionário, considerando o salário base, as horas extras trabalhadas e os bônus recebidos.

Sistema de gerenciamento de estoque: Uma stored procedure pode alertar os funcionários quando os níveis de estoque de um produto estiverem baixos, consultando a tabela de estoque para determinar os níveis de cada produto e enviando um e-mail aos funcionários quando os níveis de um produto estiverem abaixo de um determinado limite.

Vantagens das stored procedures

Stored procedures possuem várias vantagens, como:

Melhoria de desempenho: Elas são armazenadas e executadas diretamente pelo servidor do banco de dados, o que pode melhorar o desempenho do código.

Aumento da segurança: Podem ser usadas para restringir o acesso a dados e recursos do banco de dados.

Redução da complexidade: Agrupam tarefas relacionadas em um único lugar, tornando o código mais fácil de entender e manter.

No entanto, as stored procedures também têm desvantagens:

Dependência do banco de dados: Elas são dependentes do banco de dados em que foram criadas, o que dificulta a portabilidade para outro banco de dados.

Dificuldade de depuração: Podem ser difíceis de depurar, pois os erros podem ocorrer no servidor de banco de dados ou no cliente.

Conclusão

Stored procedures é uma ferramenta poderosa que pode ser usada para automatizar tarefas comuns de gerenciamento de dados, realizar cálculos complexos e executar consultas complexas. Ela oferecem uma série de vantagens sobre o código SQL tradicional, incluindo melhor desempenho, segurança e reusabilidade.

Esse post foi escrito para o trabalho da matéria de Gerenciamento de Banco de Dados, 6° Semestre de Eng. da computação, UNASP-SP.

Alunos:

Guilherme Montalbano
Henrique Sartorti Soares
Mateus Henrique