Por definição, uma função de pipelined é aquela que retorna uma coleção de dados, foi implementada pela Oracle a partir da versão 9i. Imagine ter que desenvolver uma query, com várias regras embutidas. Teríamos que criar, codificar várias linhas de código, talvez tenhamos que utilizar subquerys, minus, intersect, unions, decodes, cases, dentre outros recursos para que possamos obter o resultado esperado. Agora imagine a manutenção disso. Dentre outras utilidades, uma função de pipelined permite a simplificação do código, tornando mais rápido o desenvolvimento e ágil sua manutenção.
Muitas vezes, durante o desenvolvimento, torna-se necessário criar uma seqüência de números ou datas, que não estão em tabelas, ou então desejamos obter resultados de cálculos, fórmulas ou até expressões condicionais. Para estes casos podemos utilizar funções de pipelined. Além de facilitar o desenvolvimento, é muito mais rápida e de menor custo que uma query comum para retornar os dados.
É possível facilitar o desenvolvimento com uma função de pipelined. Veremos como.
Nosso cenário será um relatório de movimento de estoque de uma loja virtual. Um usuário deseja visualizar quantos itens de determinado produto foram movimentados a cada minuto. A estrutura da tabela de movimentos de estoque grava a data do movimento e a quantidade movimentada de um item. O resultado esperado deve estar no seguinte layout:
| De | Até | Quant. Movimentada |
|---|---|---|
| 5/7/2009 07:40:01 | 5/7/2009 07:41:00 | 0 |
| 5/7/2009 07:41:01 | 5/7/2009 07:42:00 | 0 |
| 5/7/2009 07:42:01 | 5/7/2009 07:43:00 | 3 |
| 5/7/2009 07:43:01 | 5/7/2009 07:44:00 | 3 |
Então vamos lá:
1 – Crie a estrutura da tabela onde serão inseridos os movimentos ocorridos no estoque.
create table TB_MOVIMENTO
(
ID_MOVIMENTO NUMBER primary key,
DT_MOVIMENTO DATE,
QTD_MOVIMENTADA NUMBER(4),
COD_ITEM VARCHAR2(6)
);2 – Iremos incluir algumas movimentações de estoque, algo em torno de 10 mil registros, que serão gerados aleatoriamente. Para isso utilizarei a package dbms_random, proveniente da própria instalação do banco de dados.
declare
v_dt dbms_sql.date_Table; --Cria vetor do tipo data
v_qtd dbms_sql.Number_Table; --Cria vetor do tipo numérico
begin
--Popular as datas geradas aleatoriamente
dbms_random.seed(to_date('05/07/2009 23:59:59','dd/mm/rrrr HH24:MI:SS'));
--Gera os registro de data de movimento em um dia.
for dd in 1..10000 loop
v_dt(dd) := ((to_date('05/07/2009 23:59:59','dd/mm/rrrr hh24:mi:ss')+dbms_random.value)-1);
end loop;
--Termina o processo
dbms_random.terminate;
--Popular as datas geradas aleatoriamente
dbms_random.seed(99999);
--Gera os registros de quantidade movimentada
for dd in 1..10000 loop
v_qtd(dd) := dbms_random.value(1,100);
end loop;
for dd in 1..v_dt.count loop
--Insere as movimentações
insert into tb_movimento
(id_movimento, dt_movimento, qtd_movimentada, cod_item)
values
(dd, v_dt(dd), v_qtd(dd), 'CDROM');
end loop;
commit;
end; 3 – Devemos criar a package que irá conter a função de pipelined, vamos chamá-la de pck_pipelined. Como uma função de pipelined retorna sempre uma coleção de dados, precisamos criar a estrutura de retorno, que pode ser um Record Type ou um Array. Utilizarei um Record Type. Vamos chamá-las de typ_periodo. Depois de criado, vamos criar uma tabela deste nosso novo Record.
Na declaração da assinatura da função de pipelined, devemos emitir o comando pipelined logo após a determinação do tipo de retorno, este comando é responsável por identificar a função como pipelined. Para retorno dos dados, vamos declarar a variável v_record do tipo do typ_periodo, declarado no spec da package.
Também e possível validar as informações da mesma forma que em uma procedure. Neste exemplo o parâmetro p_dt_ini não poderá ser maior que p_dt_fim e o parâmetro p_segundo não poderá ser menor que 60, caso seja, será emitido um erro na tela para o usuário. Dentro do comando while, emitimos o comando pipe row, este é utilizado para retornar a variável com o conteúdo que formará o registro.
Como toda função, ela precisa ter um return, que este é emitido no final da função.
--Spec da package
create or replace package pck_pipelined
is
type typ_periodo is record (de date,
ate date,
qtd_movimento number);
type typ_tb_periodo is table of typ_periodo;
function fnc_periodo(p_dt_ini in date,
p_dt_fim in date,
p_segundo in number,
p_item in varchar2)
return typ_tb_periodo pipelined;
end pck_pipelined;
/
--Body da package
create or replace package body pck_pipelined
is
function fnc_periodo(p_dt_ini in date,
p_dt_fim in date,
p_segundo in number,
p_item in varchar2)
return typ_tb_periodo pipelined
is
--Variável do tipo typ_periodo, declarado no spec da package anteriormente
v_record typ_periodo;
v_dt_base_ini date;
v_dt_base_fim date;
v_qtd_movimento number;
begin
--Se a data inicial for maior que a data final, interrompe o processamento
if(p_dt_ini > p_dt_fim)then
raise_application_error(-20001,'A data inicial não pode ser maior que a data final!');
end if;
--Se o período for menor que 60 segundos interrompe o processamento
if(p_segundo < 60)then
raise_application_error(-20002,'Os segundos não podem ser menores que 60');
end if;
v_dt_base_ini := to_date(p_dt_ini,'dd/mm/rrrr hh24:mi:ss') + (1/24/60/60);
v_dt_base_fim := to_date(p_dt_ini,'dd/mm/rrrr hh24:mi:ss') + ((1/24/60/60) * p_segundo);
while trunc(v_dt_base_fim) < trunc(p_dt_fim+1) loop
select nvl(sum(qtd_movimentada),0)
into v_qtd_movimento
from tb_movimento
where dt_movimento between v_dt_base_ini and v_dt_base_fim
and cod_item = p_item;
--Alimenta a variável do tipo record
v_record.qtd_movimento := v_qtd_movimento;
v_record.de := v_dt_base_ini;
v_record.ate := v_dt_base_fim;
--Retorna a variável
pipe row (v_record);
v_dt_base_ini := v_dt_base_fim + (1/24/60/60);
v_dt_base_fim := v_dt_base_fim + ((1/24/60/60) * p_segundo);
end loop;
v_dt_base_fim := v_dt_base_ini + ((1/24/60/60) * (p_segundo-2));
select nvl(sum(qtd_movimentada),0)
into v_qtd_movimento
from tb_movimento
where dt_movimento between v_dt_base_ini and v_dt_base_fim
and cod_item = p_item;
--Alimenta a variável do tipo record
v_record.qtd_movimento := v_qtd_movimento;
v_record.de := v_dt_base_ini;
v_record.ate := v_dt_base_fim;
--Retorna a variável
pipe row (v_record);
--Finaliza a função emitindo o comando de retorno, comum em toda função
return;
end fnc_periodo;
end pck_pipelined;4 – Vamos executar esta função.
Uma função de pipelined não pode ser executada como uma função comum. Para executá-la devemos fazer um select em sua estrutura, e transformar a função em uma tabela virtual utilizando o comando table:
SQL> select *
from (table(pck_pipelined.fnc_periodo(to_date('05/07/2009'),
to_date('05/07/2009'),
60,
'CDROM')))
;Logo o resultado será:
| De – Até | Quant. Movimento |
|---|---|
| 5/7/2009 00:00:01 – 5/7/2009 00:01:00 | 304 |
| 5/7/2009 00:01:01 – 5/7/2009 00:02:00 | 282 |
| 5/7/2009 00:02:01 – 5/7/2009 00:03:00 | 198 |
| 5/7/2009 00:03:01 – 5/7/2009 00:04:00 | 44 |
| 5/7/2009 00:04:01 – 5/7/2009 00:05:00 | 312 |
| 5/7/2009 00:05:01 – 5/7/2009 00:06:00 | 563 |
| 5/7/2009 00:06:01 – 5/7/2009 00:07:00 | 170 |
| 5/7/2009 00:07:01 – 5/7/2009 00:08:00 | 363 |
| 5/7/2009 00:08:01 – 5/7/2009 00:09:00 | 463 |
| 5/7/2009 00:09:01 – 5/7/2009 00:10:00 | 347 |
| 5/7/2009 00:10:01 – 5/7/2009 00:11:00 | 215 |
| 5/7/2009 00:11:01 – 5/7/2009 00:12:00 | 304 |
| 5/7/2009 00:12:01 – 5/7/2009 00:13:00 | 30 |
Conforme o layout esperado.
Também podemos relacionar a função de pipelined com outras tabelas em um select.
No oracle 9i, não é suportada a seleção de todas as tuplas utilizando o identificador select *, devendo ser declaradas as colunas manualmente, da seguinte forma:
SQL> select de, ate, qtd_movimento
2 from (table(pck_pipelined.fnc_periodo(to_date('05/07/2009','dd/mm/rrrr'),
3 to_date('05/07/2009','dd/mm/rrrr'),
4 60,
5 'CDROM')))
6 ;5 – Vamos simular o erro previsto em nossa função.
Passaremos a data final maior que a data inicial para induzir o erro:
SQL> select de, ate, qtd_movimento
2 from (table(pck_pipelined.fnc_periodo(to_date('05/07/2009','dd/mm/rrrr'),
3 to_date('01/07/2009','dd/mm/rrrr'),
4 60,
5 'CDROM')))
6 ;
select de, ate, qtd_movimento
from (table(pck_pipelined.fnc_periodo(to_date('05/07/2009','dd/mm/rrrr'),
to_date('01/07/2009','dd/mm/rrrr'),
60,
'CDROM')))
ORA-20001: A data inicial não pode ser maior que a data final!
ORA-06512: em "PROJETO.PCK_PIPELINED", line 176 – Também podemos ordernar a listagem:
SQL> select de, ate, qtd_movimento
2 from (table(pck_pipelined.fnc_periodo(to_date('05/07/2009','dd/mm/rrrr'),
3 to_date('05/07/2009','dd/mm/rrrr'),
4 60,
5 'CDROM')))
6 order by qtd_movimento desc
7 ;Como em toda função que é utilizada em selects, a função de pipelined também não permite utilização de comandos DML. Com a utilização das funções de pipelined podemos reduzir muito o tempo de desenvolvimento.
No próximo artigo abordarei sobre a velocidade de processamento na utilização de uma função de pipelined. Até lá!
De 0 a 10, o quanto você recomendaria este artigo para um amigo?