Nesse artigo, você irá aprender o que são procedimentos armazenados(Stored Procedures) em Oracle PL/SQL e quais suas vantagens e desvantagens, respectivamente.
Primeiramente, para entendermos como funcionam as Stored Procedures é crucial que saibamos o que é PL/SQL e o que são Procedimentos.
PL/SQL(Procedural Language/Structured Query Language), é uma linguagem procedural que estende o pré-existente SQL do SGBD Oracle, que por sua vez, permite a manipulação de variáveis, controle do fluxo de execução, definição de exceções, criação de laços de repetição e entre outras funcionalidades.
Procedimentos baseiam-se em subprogramas(blocos de código em PL/SQL), que normalmente tem como objetivo executar uma ação específica e geralmente não retornam um valor diretamente. Eles podem receber parâmetros, serem acionados de diferentes aplicações, serem chamados mais de uma vez e serem armazenados em forma compilada em um banco de dados tornando-se em Procedimentos Armazenados.
Para criar um procedimento, é necessário:
1. O nome do procedimento
2. O seu tipo de parâmetro, que são subdividos em:
-IN: que permite o subprograma do procedimento receba um valor, ao qual o mesmo age como uma constante podendo apenas ser lido.
-OUT: é a variável de retorno do subprograma, podendo ter seu valor alterado durante a execução do procedimento.
-IN OUT: é um parâmetro inicial no subprograma onde retorna também a mesma varíavel atualizada para que o invoca.
3. O corpo do Procedimento em si.
abaixo temos um exemplo de um procedimento:
CREATE OR REPLACE PROCEDURE MinhaProcedure(
valorInicial IN NUMBER,
parametro IN OUT NUMBER,
resultado OUT VARCHAR2 ) IS
BEGIN
IF valorInicial > 0 THEN
resultado := 'O número recebido é positivo.';
ELSIF valorInicial < 0 THEN
resultado := 'O número recebido é negativo.';
ELSE
resultado := 'O número recebido é zero.';
END IF;
parametro := parametro + 1;
END MinhaProcedure;
/
DECLARE
valor NUMBER := 10;
parametro NUMBER := 1;
resultado VARCHAR2(100);
BEGIN
MinhaProcedure(valor,parametro,resultado);
DBMS_OUTPUT.PUT_LINE(resultado);
DBMS_OUTPUT.PUT_LINE('Valor modificado: ' || parametro);
END;
/
Uma vez compreendida toda a composição de um procedimento, conclui-se que toda vez que um procedimento não está em um pacote, ele é chamado de autônomo(Standalone Procedure), e quando o mesmo é armazenado em pacotes, denomina-se Procedimentos Armazenados(Stored Procedures).
As Stored Procedures no uso do dia a dia podem nos fornecer uma melhor performance, uma vez que são armazenados em um formato executável, trazendo mais eficiência. Evita a redundância de código nas aplicações que venham a utilizar um conjunto de Stored Procedures, podendo aumentar a produtividade. É possível também replicá-las em mais de uma instância de banco de dados Oracle. E por fim podem agregar diretamente na manutenção do código, sendo que ao serem utilizadas em diversos lugares diferentes, alterá-las não afetaria diretamente quem está usando-a.
As Stored Procedures podem facilitar muito o trabalho do desenvolvedor, entretanto elas podem consumir muita memória e também são um pouco mais difícies de serem debbugadas, podendo trazer uma maior dificuldade na hora de tratar erros e exceções.
Contudo, as Stored Procedures estão disponíveis em muitos SGBDS(Oracle Database, SQL Server, PostgreSQL, MySQl...) e podem ser úteis em diversos modelos de negócios. Desde aplicações web ou sistemas de contabilidade onde consultas complexas podem ser realizadas para poder devolver informações filtradas e organizadas, como também podem ser usadas em operações mais sistemáticas, como sistemas da área da saúde ou CRMs para gerar relatórios médicos e cálculos de métricas de saúde.
Exemplos práticos de Stored Procedures
- No Cenário de hospitais, onde sempre é necessário gerar relatórios com as informações de pacientes, ou buscar a ficha médica do mesmo, podemos gerar uma procedure que por sua vez é realizada com um parâmetro IN sendo o CPF do paciente e buscar todos os dados do mesmo.
CREATE OR REPLACE PROCEDURE RelatorioPacientePorCPF(
CPF in VARCHAR2,
Nome Out VARCHAR2,
Peso Out NUMBER,
Idade out NUMBER,
TipoSanguineo out VARCHAR2
) IS BEGIN
IF CPF = '45398750695' THEN
Nome := 'John Doe';
Peso := 70;
Idade := 23;
TipoSanguineo := 'O+';
ELSIF CPF ='54398012654' THEN
Nome := 'Jane Doe';
Peso := 65;
Idade := 21;
TipoSanguineo := 'O-';
END IF;
END RelatorioPacientePorCPF;
/
DECLARE
CPF varchar2(11) := '45398750695';
Nome varchar2(20);
Peso NUMBER;
Idade NUMBER;
TipoSanguineo varchar2(4);
BEGIN
RelatorioPacientePorCPF(CPF,Nome,Peso,Idade,TipoSanguineo);
DBMS_OUTPUT.PUT_LINE('Nome : ' || Nome);
DBMS_OUTPUT.PUT_LINE('Peso : ' || Peso || 'Kg');
DBMS_OUTPUT.PUT_LINE('Idade: ' || Idade );
DBMS_OUTPUT.PUT_LINE('Tipo Sanguíneo: ' || TipoSanguineo);
END;
/
2 . As Stored Procedures também podem realizar cálculos complexos e devolver os resultados com apenas a chamada do mesmo, e podendo ser utilizados com diferentes valores. O cálculo do Índice de Massa Corporal é um exemplo simples de cálculo que com apenas 2 parâmetros consegue ser realizado, de acordo com o exemplo abaixo:
CREATE OR REPLACE PROCEDURE CalculoIMC(
Peso IN NUMBER,
Altura IN NUMBER,
Resultado OUT NUMBER,
EstadoCorporal OUT VARCHAR2
) IS BEGIN
Resultado := Peso / (Altura * Altura);
IF Resultado < 18.5 THEN
EstadoCorporal := 'Magreza';
ELSIF Resultado >= 18.5 AND Resultado < 24.9 THEN
EstadoCorporal := 'Normal';
ELSIF Resultado >= 25 AND Resultado < 29.9 THEN
EstadoCorporal := 'Sobrepeso';
ELSIF Resultado >= 30 AND Resultado < 39.9 THEN
EstadoCorporal := 'Obesidade';
ELSE
EstadoCorporal := 'Obesidade Grave';
END IF;
END CalculoIMC;
/
DECLARE
Peso NUMBER := 68.7;
Altura NUMBER := 1.79;
Resultado NUMBER;
EstadoCorporal VARCHAR2(50);
BEGIN
CalculoIMC(Peso,Altura,Resultado,EstadoCorporal);
DBMS_OUTPUT.PUT_LINE('Estado Corporal : ' || EstadoCorporal || ' - ' || Resultado);
END;
/
3 . As Stored Procedures também podem realizar consultas filtradas em bases de Log(Histórico ou vida útil de uma tabela), como por exemplo o número de compras realizadas em uma e-commerce e seus respectivos valores e quais itens foram comprados, de acordo com o exemplo abaixo:
CREATE OR REPLACE PROCEDURE HistoricoCompras(
Pedido IN VARCHAR2,
QtdItens OUT NUMBER,
ValorItens OUT NUMBER,
Skus OUT NUMBER,
ValorTotal OUT NUMBER
) IS BEGIN
IF Pedido = 'PEDIDO#12345' THEN
QtdItens := 5;
ValorItens := 232.09;
ValorTotal := QtdItens * ValorItens;
Skus := '4211';
ELSIF Pedido = 'PEDIDO#4321' THEN
QtdItens := 9;
ValorItens := 181;
ValorTotal := QtdItens * ValorItens;
Skus := '3421';
END IF;
END HistoricoCompras;
/
DECLARE
Pedido VARCHAR2(20) := 'PEDIDO#12345';
QtdItens NUMBER;
ValorItens NUMBER;
Skus VARCHAR2(10);
ValorTotal NUMBER;
BEGIN
HistoricoCompras(Pedido,QtdItens,ValorItens,Skus,ValorTotal);
DBMS_OUTPUT.PUT_LINE('O número do Pedido é: ' || Pedido);
DBMS_OUTPUT.PUT_LINE('O valor total do Pedido é ' || ValorTotal || 'R$') ;
DBMS_OUTPUT.PUT_LINE('A quantidade de itens desse Pedido são: ' || QtdItens);
DBMS_OUTPUT.PUT_LINE('Os Skus desse pedido são: ' || Skus);
END;
/
4 . É possível também criar Procedures que retornam uma certa quantidades de informações baseadas em um período de tempo. Por exemplo, ao registrar o nome de clientes que já receberam ligações de telemarketing em um determinado mês, é possível criar uma procedure que busca essas informações. de acordo com o exemplo abaixo:
Obs: Os Valores são singulares mas podem ser substituídos por selects em tabelas que fornecerão o mesmo resultado e em mais linhas.
Top comments (1)
I Hope It seems to be understandable, and i accept opinions about it! Thanks everyone!