Stored Procedures in PL/SQL

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

1. 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.