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 DEFINIDOSAgora 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 VERDADEPara 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 FUNCTIONSAs 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 MAIShttp://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 novoBlue-rayMonitorNotebooksPlaystation 3CelularMúsica
