[PL/SQL] Esqueça tabelas temporárias: use funções PIPELINED!

Oi Pessoal,

Por várias vezes temos situações de relatórios, por exemplo, em que a consulta ficaria tão complexa que decidimos gerar uma tabela temporária para basear o relatório, ficando o preenchimento das informações a cargo de uma package PL/SQL que é chamada momentos antes do relatório. No entanto, o Oracle 9i em diante nos permite o uso de funções PIPELINED para atingir o mesmo fim, sem ter que criar tabela nenhuma, e sem necessitar controle sobre a transação (commit, usuários concorrentes etc).

Para fazê-lo, os quatro simples passos são:

1) Criar um type no banco do tipo OBJECT que contenha todos os campos necessários na sua query:
create or replace type test_rec as object (key number, value1 varchar2(100), value2 varchar2(200));

2) Criar um outro type, desta vez como uma tabela do type criado anteriormente:
create or replace type tab_rec as table of test_rec;

3) Criar uma função/package a ser chamada do relatório, com o modificador PIPELINED:

 create or replace function get_test(p_key IN NUMBER) return tab_rec pipelined is
  begin
    for i in 1..p_key LOOP
      pipe row( test_rec(i, 'Test'||i, 'This is test number '||i||' for pipelined functions' ));
    end loop;
    return;
  end;

4) Pronto! Agora é só selecionar os registros direto da função, assim:

  SELECT *
    FROM TABLE(get_test(2));

KEY             VALUE1            VALUE2
-----------     --------------    ---------------
1               Test1             This is test number 1 for pipelined functions
2               Test2             This is test number 2 for pipelined functions

Abraços pessoal, até a próxima!