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:
Top comments (0)