Oracle - PIPELINED Table Function

Olá!


Este é um pequeno artigo sobre o uso de um recurso do banco de dados Oracle que poucas pessoas conhecem, o nome desse recurso é PIPELINED Table Function ou simplesmente PIPELINED Function.
As ferramentas utilizadas nas implementações foram o Oracle SQL Developer e o Oracle 10g Express Edition rodam em Windows Vista.

Smartphone VoIP Livros de Java Apartamentos

###############
1. INTRODUÇÃO

Uma PIPELINED Table Function é uma função capaz de devolver várias linhas de resultados e são chamadas na cláusula FROM de uma instrução SELECT como se fosse uma tabela.
Nada melhor para aprender um novo recurso do que um exemplo bem simples.

O exemplo abaixo implementa um PIPELINED function que devolve a quantidade de linhas informada no parametro qtd_num.

CREATE OR REPLACE PACKAGE pac_fun_PIPELINED AS

type numeros IS TABLE OF NUMBER;

FUNCTION fun_gera_numeros(qtd_num number) RETURN numeros PIPELINED;

END pac_fun_PIPELINED;

CREATE OR REPLACE PACKAGE BODY pac_fun_PIPELINED AS

FUNCTION fun_gera_numeros(qtd_num NUMBER) RETURN numeros PIPELINED IS
BEGIN
  FOR cont IN 1 .. qtd_num
  LOOP
    pipe ROW(cont * cont); -- Eleva ao quadrado
  END LOOP;

  RETURN;

END fun_gera_numeros;

END pac_fun_PIPELINED;

Para executar a function use a instrução SELECT abaixo:

SELECT * FROM TABLE(pac_fun_PIPELINED.fun_gera_numeros(9));

O resultado é exibido na figura abaixo.

Viu porque essa function pode ser chamada de "virtual table"?


Três itens nesse exemplo são caracteristicas de uma PIPELINED function:
1. A palavra-chave PIPELINED;
2. O comando PIPE ROW que joga o registro no result set;
3. E o comando RETURN; em "branco" apesar do tipo de retorno da function indicar numeros.

Geralmente os types e as functions são implementadas dentra de uma package, nada impede que você as crie fora. Só a titulo de curiosidade, quando uma package é solicitada, tudo o que foi implementada dentro dela sobe para a memória. Então, dependendo das suas necessidades não é interessante implementar uma package.

Apesar de ser permitido, no exemplo não foi indicado se o paramêtro da function é de entrada (IN) ou de saída (OUT).

A vantagem entre uma função PIPELINED e outra não PIPELINED é que a função PIPELINED processa os dados e joga os resultados imediatamente para o result set do SELECT, assim o chamador pode usar os dados mais rapidamente sem ter que esperar todo o processamento.
por outro lado a função que não é PIPELINED processa os dados e vai acumulando os resultados para depois devolver(usando a cláusula RETURN ou usando paramêtro de OUT) os resultados para chamador da função, só então ele poderá usar os dados.

###############
2. USANDO TIPOS DEFINIDOS

Agora será implementado um exemplo usando tipos definidos pelo programador.
Neste exemplo será criado uma function que calcula a tabuada de um número (num_tabuada).

create or replace
PACKAGE pac_fun_PIPELINED AS

type registro is record (
  indice number,
  multiplicador number,
  resultado number
);

type tabuada IS TABLE OF registro;

FUNCTION fun_tabuada(num_tabuada    number,
                     qtd_resultados number)
RETURN tabuada PIPELINED;

END pac_fun_PIPELINED;

create or replace
PACKAGE BODY pac_fun_PIPELINED AS

FUNCTION fun_tabuada(num_tabuada    number,
                     qtd_resultados number)
RETURN tabuada PIPELINED IS
aux registro;
BEGIN

  FOR cont IN 1 .. qtd_resultados
  LOOP
    aux.indice := cont;
    aux.multiplicador := num_tabuada;
    aux.resultado := cont * num_tabuada;
    pipe ROW(aux);
  END LOOP;

  RETURN;

END fun_tabuada;

END pac_fun_PIPELINED;

Para calcular a tabuada do 7, a function será executada com o comando abaixo:

SELECT * FROM TABLE(pac_fun_PIPELINED.fun_tabuada(7, 10));

O resultado é exibido na figura abaixo.


#############################
3. USANDO JUNTO COM TABELAS DE VERDADE

Para este exemplo será necessário criar uma tabela, dois tipo, uma função e uma package

CREATE TABLE "CAD_PEDIDO_TABUADA"
( "NUM_TABUADA" NUMBER NOT NULL ENABLE,
 "DATA_PEDIDO" DATE NOT NULL ENABLE,
 "QTD_LINHAS" NUMBER,
 "CODIGO" NUMBER,
  CONSTRAINT "PK_CAD_PEDIDO_TABUADA" PRIMARY KEY ("CODIGO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"  ENABLE
 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ;

Inserindo dados na tabela.

INSERT INTO "CAD_PEDIDO_TABUADA" (NUM_TABUADA, DATA_PEDIDO, QTD_LINHAS, CODIGO) VALUES ('7', TO_DATE('05/05/08', 'DD/MM/RR'), '5', '2');
INSERT INTO "CAD_PEDIDO_TABUADA" (NUM_TABUADA, DATA_PEDIDO, QTD_LINHAS, CODIGO) VALUES ('2', TO_DATE('05/05/08', 'DD/MM/RR'), '11', '1');

Abaixo a package com tudo o que foi criado até agora para os exemplos.

CREATE OR REPLACE PACKAGE pac_fun_PIPELINED AS

-- Exemplo 1
type numeros IS TABLE OF NUMBER;

-- Exemplo 2
type registro IS record(indice NUMBER,   multiplicador NUMBER,   resultado NUMBER);
type tabuada IS TABLE OF registro;

-- Exemplo 3
type pedido_tabuada IS TABLE OF cad_pedido_tabuada % rowtype;
TYPE tab_refcursor IS REF CURSOR RETURN cad_pedido_tabuada%ROWTYPE;

----------------------------------------------------------  
-- Exemplo 1
FUNCTION fun_gera_numeros(qtd_num IN NUMBER) RETURN numeros PIPELINED;

-- Exemplo 2
FUNCTION fun_tabuada(num_tabuada NUMBER,   qtd_resultados NUMBER) RETURN tabuada PIPELINED;

-- Exemplo 3
FUNCTION fun_cad_tabuada(cursor_tabuada in tab_refcursor) RETURN tabuada PIPELINED;

END pac_fun_PIPELINED;

create or replace
PACKAGE BODY pac_fun_PIPELINED AS

FUNCTION fun_gera_numeros(qtd_num in NUMBER) RETURN numeros PIPELINED IS
BEGIN
  FOR cont IN 1 .. qtd_num
  LOOP
    pipe ROW(cont * cont); -- Eleva ao quadrado
  END LOOP;

  RETURN;

END fun_gera_numeros;

FUNCTION fun_tabuada(num_tabuada    number,
                     qtd_resultados number)
RETURN tabuada PIPELINED IS
aux registro;
BEGIN

  FOR cont IN 1 .. qtd_resultados
  LOOP
    aux.indice := cont;
    aux.multiplicador := num_tabuada;
    aux.resultado := cont * num_tabuada;
    pipe ROW(aux);
  END LOOP;

  RETURN;

END fun_tabuada;

FUNCTION fun_cad_tabuada(cursor_tabuada in tab_refcursor) RETURN tabuada PIPELINED IS
  temp cad_pedido_tabuada%rowtype;
  aux registro;
  erro varchar2(500) := 'ERRO: ';
BEGIN

  loop
      fetch cursor_tabuada into temp;
      exit when cursor_tabuada%notfound;
     
        -- Calcula a tabuada até a quantidade de linhas cadastradas.
        for contador IN 1..temp.qtd_linhas loop
          aux.indice := contador;
          aux.multiplicador := temp.NUM_TABUADA;
          aux.resultado := contador * temp.NUM_TABUADA;

          PIPE ROW (aux);
        end loop;
                      
  end loop;

EXCEPTION
  WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(erro || sqlerrm);

END fun_cad_tabuada;

END pac_fun_PIPELINED;

Para executar a function use a instrução abaixo:

SELECT indice, multiplicador, resultado
FROM TABLE(pac_fun_PIPELINED.fun_cad_tabuada(
cursor(select NUM_TABUADA,DATA_PEDIDO,DATA_PROCESS,QTD_LINHAS,CODIGO
       from CAD_PEDIDO_TABUADA
       where codigo = 1)
))

O resultado é exibido na figura abaixo.

Não foi possível percorrer todos os registros da tabela cad_pedido_tabuada retirando a claúsula WHERE, o Oracle sempre processa o primeiro registro somente. Não descobri porque é assim e o motivo dessa limitação.
Não foi possível dar commit dentro da function, também não descobri por que razão a Oracle limitou o uso desse recurso.

###############
4. PARALLEL PIPELINED FUNCTIONS

As funções PIPELINED podem se tornar ainda mais poderosas, pois são paralelizáveis, com isso o processamento dos dados ficam mais rápidos.
Veja nesse link como funciona as PIPELINED and parallel functions.
Para utilizar os recursos de paralelização é preciso seguir algumas regras.
a) A função deve ser assinada com a palavra-chave PARALLEL_ENABLE;
b) A função tem de ser alimentada com um ref cursor.
Esse tópico é apenas para divulgação, até porque eu não possuo conhecimento sobre esse recurso do Oracle.
Para saber mais recomendo acessar o link: Parallel Table Functions.


###############
5. LINKS PARA SABER MAIS

http://www.codeguru.com/cpp/data/mfc_database/oracle/article.php/c4285/
http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10800/dcitblfns.htm

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28425/pipe_paral_tbl.htm
http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10800/dcitblfnxemp.htm

Até mais,

Carro novo
Blue-ray
Monitor
Notebooks
Playstation 3
Celular
Música