DEV Community: Guilherme

Tutorial: Utilizando Stored Procedures no Oracle

Guilherme — Tue, 05 Dec 2023 20:05:54 +0000

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

SQL Stored Procedures - Na visão de um estudante.

Guilherme — Mon, 13 Nov 2023 15:16:56 +0000

Neste post falaremos sobre SQL Stored Procedure. Descreveremos sobre sua sintaxe, vantagens e desvantagens e suas aplicações no mercado e etc.

Primeiramente devemos começar entendendo o que é Stored Procedure. Uma Stored procedure nada mais é que um código SQL que nós salvamos para reutilizarmos várias vezes, então ao invés de escrevermos o mesmo código diversas vezes, nós apenas salvamos o código como uma stored procedure e chamamos ele para a execução.

A sintaxe:

CREATE PROCEDURE nome_da_procedure
AS
BEGIN
  -- SQL statements go here
END nome_da_procedure;

E para executarmos:

EXECUTE my_procedure;

Dados para exemplo:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Joaquim Cordeiro	Joaquim	Avenida Esbertalina Barbos 84	São Mateus	29946490	Brazil
2	Gabriel Miranda	Gabriel	Avenida Governador José 1209	Belém	66055260	Brazil
3	Celina Duarte Serrano	Celina	Rua da Imprensa 395	Campo Grande	79002290	Brazil
4	Barbara Raiane Faro	Barbara	Rua Maria Luísa do Val Penteado 9952	São Paulo	03962040	Brazil
5	Breno Casanova Borba	Breno Borba	Avenida Afonso Pena 100	Belo Horizonte	30130005	Brazil

Exemplo:

O Exemplo a seguir cira uma stored procedure com o nome de "SelectALLCustomers" que seleciona todos os registros na tabela "Custumers":

CREATE PROCEDURE SelectAllCustomers
AS
BEGIN
SELECT * FROM Customers
END SelectAllCustomers;

E para sua execução:

  EXECUTE SelectALLCustomers;

Agora com a procedure funcionando toda vez que você precisar selecionar todos os clientes, basta chamar a procedure. Isso nos ajuda com tempo e redundância no código.
Este foi um exemplo simples, mas podemos criar Stored Procedures para executar tarefas mais complexas e ou especificas, como calculos ou atualização de dados.

Parâmetros (IN/OUT):

Stored Procedures também podem aceitar parametros para sua execução, podemos usar parâmetros para receber os valores da parte que chama a Stored Procedure (ENTRAS/IN) ou para retornar os valores para quem chamou (SAÍDA/OUT)
Aqui está um exemplo utilizando um parametro.
A Stored Procedure a seguir seleciona na tabela "Customers" o cliente de apenas um país especifico, que será passado como parametro.

Parametros de Entrada (IN):

CREATE OR REPLACE PROCEDURE SelectAllCustomers(country IN VARCHAR2) IS
BEGIN
  SELECT * FROM Customers WHERE Country = country;
END SelectAllCustomers;

E sua execução passa o país Brasil como parametro para seleção:

EXECUTE  SelectAllCustomers('Brasil');

Parametros de Saída(OUT):

CREATE OR REPLACE PROCEDURE multiplica_por_2(valor_entrada IN NUMBER, valor_saida OUT NUMBER) IS
BEGIN
  valor_saida := valor_entrada * 2; -- Calcula e define o valor de saída
END multiplica_por_2;

Quando a Procedure for chamada, forneceremos o tanto a variável de entrada quanto a de saída:

DECLARE
  valor_entrada NUMBER := 5;
  valor_saida NUMBER;

EXECUTE multiplica_por_2(valor_entrada, valor_saida);
DBMS_OUTPUT.PUT_LINE('Valor de entrada: ' || valor_entrada);
DBMS_OUTPUT.PUT_LINE('Valor de saída: ' || valor_saida);

Assim chamamos com o valor_entrada como parâmetro de entrada, e valor_saida como parâmetro de saída, após sua execução o valor de saída ira possuir o valor do valor_entrada * 2.

Vantagens e desvantagens:

Temos diversas vantagens utilizando as Stored Procedures:

Possuí facilidade de uso pois é possível criamos uma vez e depois pode ser implementado em qualquer parte do código ou programa, digamos que temos um site e que ele precisa calular e retornar o total do pedido com base nos itens do carrinho de compras, para isso podemos criar uma procedure para realizar o calculo e toda vez que qualquer parte do nosso site seja adicionado ao carrinho de compras, apenas chamamos a procedure para o calcular o preço total do carrinho.
Melhor desempenho e agilidade, chamando procedures, elas são mais rápidas e mais eficientes pois elas são armazenadas e compiladas apenas uma vez. Sendo assim seu tempo de resposta é muito mais rapido, em bancos que possuem uma grande escala, podemos encontrar momentos em que várias partes da aplicação precisam acessar e utilizar os mesmos dados. Ao usar as Stored Procedures para realizar operações mais comuns, você irá melhorar o desempenho do seu banco de dados, levando em conta que o código da Stored Procedure é compilado e armazenado em cache, reduzindo assim a necessidade de compilar novamente.
Segurança, no caso de acesso de dados restritos, quando definido o provilédio da stored procedure, o usuário pode manipular dados utilizando ela tranquilamente e seguindo 100% a query pré definidada. Por exemplo, possiuimos um sistema de gerenciamento de clubes em que os registros de desbravadores são armazenados. Com Stored Procedures, podemos garantir que apenas as Stored Procedures específicas podem acessar e manipular esses registros, proporcionando um nível adicional de segurança para os dados sensíveis dos desbravadores integrantes do clube.
Manutenção, Em um ambiente de desenvolvimento colaborativo, onde vários desenvolvedores trabalham em um aplicativo ou site, se uma parte da lógica de negócios for implementada em Stored Procedures, as atualizações ou correções necessárias podem ser feitas na Stored Procedure central. Dessa forma, todas as partes do aplicativo que utilizam ela se beneficiam das alterações sem a necessidade de modificar código em várias partes do aplicativo.

Exemplo de manutenção:

Aqui alteramos a seleção de cidade para selecionarmos o país.

CREATE OR REPLACE PROCEDURE SelectAllCustomers (p_country VARCHAR2) IS
BEGIN
  SELECT * FROM Customers WHERE Country = p_country;
END SelectAllCustomers;


EXECUTE SelectAllCustomers('Brasil');

Temos também desvantagem de sua utilização.

Testes, no cenário do mundo real, quando ocorre um erro em uma Stored Procedure, os erros não são sempre detectados até o tempo de execução. Isso pode dificultar a detecção e correção de problemas ou incidentes (BUGS), tornando o processo de teste e depuração mais complicados.
Portabilidade: As stored procedures não são sempre portáveis entre diferentes sistemas de gerenciamento de banco de dados (SGBD). Isso pode limitar a flexibilidade se você precisar realizar uma migration para um outro SGBD diferente no futuro.
Versionamento: Gerenciar as versões das stored procedures pode ser complicado, especialmente em um ambiente de desenvolvimento colaborativo. Mudanças nas stored procedures podem afetar outras partes da aplicação.

Exemplos de uso:

Cálculo do preço do carrinho:

Em um sistema de comércio eletrônico, você pode criar uma Stored Procedure para calcular o preço total de um pedido com base nos itens do carrinho de compras. Isso pode ser útil em várias partes da aplicação, seja como ao exibir o resumo do pedido para o cliente ou ao processar o pagamento. Abaixo segue o exemplo de uma Stored Procedure para esse caso:

CREATE OR REPLACE PROCEDURE CalculateTotalPrice(orderID IN NUMBER) AS
  totalPrice NUMBER;
BEGIN
  -- Calcula o preço total com base nos itens do pedido
  SELECT SUM(Price * Quantity)
  INTO totalPrice
  FROM OrderItems
  WHERE OrderID = orderID;

  -- Atualiza o preço total no pedido
  UPDATE Orders
  SET TotalPrice = totalPrice
  WHERE OrderID = orderID;

  COMMIT; -- Confirma a transação
END CalculateTotalPrice;

Neste exemplo, a Stored Procedure CalculateTotalPrice aceita o orderID como parâmetro e calcula o preço total com base nos itens do pedido. Isso pode ser chamado sempre que um item for adicionado ou removido do carrinho.

Registro de Histórico de Pedidos:

Podemos criar uma Stored Procedure para registrar detalhes do pedido em uma tabela de histórico sempre que um pedido for concluido. Isso mantém um registro completo dos pedidos feitos pelos clientes. Veja um exemplo:

CREATE PROCEDURE RecordOrderHistory (orderID INT, orderDate DATETIME)
AS
BEGIN
  INSERT INTO OrderHistory (OrderID, OrderDate, Status)
  VALUES (orderID, orderDate, 'Completed');
END;

Neste exemplo, a Stored Procedure RecordOrderHistory registra os detalhes do pedido, como o ID do pedido, a data do pedido e o status "Concluído", na tabela de histórico sempre que um pedido é concluído com sucesso.

Após o desenvolvimento e leitura desse post chegamos a conclusão que utilizar Stored Procedures pode ser uma grande vantagem se souber utilizá-la, sendo por segurança de código ou como citado na aplicação, se dois serviços precisam da mesma query ao invés de desenvolver duas querys diferentes ambos consultam na mesma Stored Procedure, vimos que em grande parte sua manutenção é fácil, pois alteramos diretamente seu código e não precisamos passar em diferentes partes dos códigos fazendo ajustes, e em nossa conclusão mesmo que existam desvantagens em seu uso se utilizada corretamente temos maior ganho de desempenho.

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

Referências:

Oracle Tutorial - Procedure
w3schools - Stored procedures