Banco de Dados

Ξ Deixe um comentário

Oracle – Utilizando Operadores Customizados

publicado por Paulo Planez

Figura - Oracle - Utilizando Operadores CustomizadosOs operadores são fundamentais em qualquer linguagem de Programação e até mesmo no dia a dia. Como viver sem os operadores de Adição (+), igualdade (=), etc..

Porém, a Oracle disponibiliza desde a versão 8i o conceito de Operadores customizados.

Basicamente, um operador customizado é uma forma de esconder a complexidade de uma regra de negócio através de um Operador (Similar ao que é feito com a utilização das views). Para quem já estava acostumado a utilizar funções como operadores justamente com o objetivo de garantir a utilização das regras de negócio definidas como padrão, não vai ter muitas dificuldades em se adaptar a este conceito.

A utilização do Operador (Ou da função simulando o operador) exige um certo nível de maturidade do desenvolvimento, pois a correta documentação e utilização dos operadores definidos é fundamental para que o recurso gere ganhos no projeto, especialmente, os ganhos relacionados a manutenibilidade do aplicativo.

Bom, vamos à demonstração do recurso. Imagine o seguinte cenário:

Um processo onde um pedido, depois de inserido, precisa ser validado para se considerado um pedido valido e precisa pertencer a uma pessoa Jurídica e estar registrado no dia de Ontem (O processo valida automaticamente todos os pedidos depois da meia noite);

Então vamos criar esta estrutura para atender o requisito:

create table clientes
(id       number   primary key
,nome     varchar2(30)
,endereco varchar2(100)
,tipo     varchar2(1));

create table pedido
(id         number
,data       date
,valor      number
,cliente_id number
,constraint fk_ref foreign key (cliente_id) references clientes (id));

create sequence pedido_seq;

Agora, vamos alimentar esta estrutura com uma massa de dados para testes:

DECLARE
   v_tipo varchar2(1);
   v_data date;
BEGIN
   <<loop_pedido>>
   FOR x IN 1..17 LOOP
   ----------------------------------
   --Definindo o tipo
   ----------------------------------
   IF MOD(x,2) = 0 THEN
      v_tipo := 'F'; --Pessoa Fisica
   ELSE
      v_tipo := 'J'; --Pessoa Juridica
   END IF;
   ----------------------------------
   --Inserindo o registro
   ----------------------------------
   insert into clientes (id,nome,endereco,tipo)
   values (x,'Cliente '||x,'Endereço do cliente '||x, v_tipo);
   ----------------------------------
   --Inserindo o pedido
   ----------------------------------
      <<loop_data>>
      FOR y IN 1..5 LOOP
         -------------------------------
         --Definindo a data do pedido
         -------------------------------
         v_data := TRUNC(sysdate-(y-1));
         -------------------------------
         --Definindo a data do pedido
         -------------------------------
         <<loop_cliente>>
         FOR z IN REVERSE 1..5 LOOP
            insert into PEDIDO (id,data,valor,cliente_id)
            values (PEDIDO_SEQ.nextval,v_data,X*Y*Z*10,x);
         END LOOP loop_pedido;
      END LOOP loop_data;
   END LOOP loop_cliente;
   -------------------------------------
   --Efetivando a transação
   -------------------------------------
   commit;
END;
/

Para Identificar os Pedidos válidos de acordo com a regra acima, poderia ser utilizada a seguinte query:

select * from CLIENTES a, PEDIDO   b
 where b.cliente_id = a.id
   and a.tipo = 'J'
   and b.data = TRUNC(sysdate-1)

Veja abaixo como fica o custo desta query:
-------------------------------------------------------------------------------
| Id | Operation         | Name     | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |          |   45 |  5940 |      7 (15)| 00:00:01 |
|* 1 | HASH JOIN         |          |   45 |  5940 |      7 (15)| 00:00:01 |
|* 2 |  TABLE ACCESS FULL| CLIENTES |    9 |   756 |      3  (0)| 00:00:01 |
|* 3 |  TABLE ACCESS FULL| PEDIDO   |   85 |  4080 |      3  (0)| 00:00:01 |
-------------------------------------------------------------------------------

Observe o volume de bytes que ela retornou, além do custo.

Agora, vamos criar a função que será a base do nosso trabalho:

create or replace function pedido_valido (p_ref in varchar2) return number is
BEGIN
   DECLARE
      v_tipo   varchar2(1);
      v_data   date;
      v_cliente number;
      v_ret     number;
   BEGIN
      ------------------------------------------------------------------------
      --Identificando o Pedido
      ------------------------------------------------------------------------
      select data, cliente_id
        into v_data, v_cliente
        from PEDIDO
        where rowid = p_ref;
      ------------------------------------------------------------------------
      --checando o cliente
      ------------------------------------------------------------------------
      IF v_data = TRUNC(sysdate-1) THEN
         BEGIN
            ------------------------------------------------------------------
            --Identificando o cliente
            ------------------------------------------------------------------
            select tipo into v_tipo
              from CLIENTES
             where id = v_cliente;
            ------------------------------------------------------------------
            --Checando o tipo
            ------------------------------------------------------------------
            IF v_tipo = 'F' THEN
               v_ret := 0;
            ELSE
               v_ret := 1;
            END IF;
         EXCEPTION
            WHEN others THEN
               v_ret := 0;
         END;
      ELSE
         v_ret := 0;
      END IF;
      ------------------------------------------------------------------------
      --Retornando o resultado
      ------------------------------------------------------------------------
      RETURN (v_ret);
   END;
END;
/

Você poderia utilizar diretamente a função em sua query. Ela já serviria para ocultar a regra de negócio que envolve a definição de um pedido válido, conforme abaixo:

select * from PEDIDO
 where PEDIDO_VALIDO (rowid) = 1

O custo desta operação ficaria assim:

----------------------------------------------------------------------------
| Id | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |        |     4 |   240 |     3   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL | PEDIDO |     4 |   240 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Observe que, além da redução de custo obtido, o volume de bytes trabalhado foi menor.

Ainda existe a possibilidade de se criar um operador específico para isto, conforme abaixo:

CREATE OPERATOR ped_e_valido
BINDING (VARCHAR2)
RETURN NUMBER
USING PEDIDO_VALIDO;

A query para consultar os pedidos validos utilizando o operador ficaria assim:

select * from PEDIDO
 where PED_E_VALIDO (rowid) = 1

Observe que o custo se mantém o mesmo que quando utilizado diretamente a função:

----------------------------------------------------------------------------
| Id | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0  SELECT STATEMENT  |        |     4 |   240 |     3   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL | PEDIDO |     4 |   240 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Uma outra forma de utilizá-lo seria na query conforme abaixo:

select id pedido_id
     , case
          when PED_E_VALIDO (rowid) = 1 then 'Sim'
          else 'Não'
        end status
from PEDIDO

O custo desta query ficaria exatamente assim:

----------------------------------------------------------------------------
| Id | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |   425 | 10625 |     3   (0)| 00:00:01 |
|   1 | TABLE ACCESS FULL| PEDIDO |   425 | 10625 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Na prática, o que ele faz é justamente chamar a função, porém, você consegue ter um melhor nível de controle sobre o que é utilizado como operador, através da tabela USER_OBJECTS, conforme query abaixo:
select * from user_objects
 where object_type = 'OPERATOR'

Desta forma você saberia facilmente quais são os operadores que aplicam alguma regra de negócio ao sistema.

[Crédito da Imagem: Operadores Customizados – ShutterStock]

Autor

Graduado em administração com especialização em Finanças, atua desde 1990 com sistemas de informação, em sua maioria focado em sistemas de gestão financeira. ♪ Atuou com sistemas de informação proprietários dos mais variados como Sistemas de gestão de Grãos, Sistemas de controle financeiro para transações eletrônicas e, no segmento de governo, com Sistemas Financeiros e de Sanidade Animal e Vegetal e com sistemas para processamento de Malha Fiscal. ♪ Atua por mais de 15 anos com sistemas de gestão (ERP) cobrindo ciclos operacionais como "Quote to Cash" e "Purchase to Pay" utilizando software da Oracle Corporation (Oracle EBS) em empresas como Globo, Motorola, Alcoa e Dell. ♪ Possui como linha de estudo e pesquisa a economicidade dos sistemas de informação, de modo a extrair destes o máximo de benefícios com o mínimo de recursos.♪ Contato: paulo.planez@gmail.com

Paulo Planez

Comentários

You must be logged in to post a comment.

Busca

Patrocínio

Publicidade



Siga-nos!

Newsletter: Inscreva-se

Para se inscrever em nossa newsletter preencha o formulário.

Artigos Recentes