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.
[sourcecode lang="sql"]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;[/sourcecode]
Para executar a function use a instrução SELECT abaixo:
[sourcecode lang="sql"]SELECT * FROM TABLE(pac_fun_PIPELINED.fun_gera_numeros(9));[/sourcecode]
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).
[sourcecode lang="sql"]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;[/sourcecode]
Para calcular a tabuada do 7, a function será executada com o comando abaixo:
[sourcecode lang="sql"]SELECT * FROM TABLE(pac_fun_PIPELINED.fun_tabuada(7, 10));[/sourcecode]
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
[sourcecode lang="sql"]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" ;[/sourcecode]
Inserindo dados na tabela.
[sourcecode lang="sql"]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');[/sourcecode]
Abaixo a package com tudo o que foi criado até agora para os exemplos.
[sourcecode lang="sql"]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;[/sourcecode]
Para executar a function use a instrução abaixo:
[sourcecode lang="sql"]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)))[/sourcecode]
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,
