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 replacePACKAGE 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 replacePACKAGE BODY pac_fun_PIPELINED AS

FUNCTION fun_tabuada(num_tabuada    number, qtd_resultados number)RETURN tabuada
  PIPELINED ISaux 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 255STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)TABLESPACE "USERS"  ENABLE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGSTORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 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 1type numeros IS TABLE OF NUMBER;

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

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

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

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

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

END pac_fun_PIPELINED;

create o
r replacePACKAGE BODY pac_fun_PIPELINED AS

FUNCTION fun_gera_numeros(qtd_num in NUMBER) RETURN numeros PIPELINED ISBEGIN  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 ISaux 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, resultadoFROM 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,