Muitas vezes temos a necessidade de converter múltiplas linhas em uma linha simples e o envio de email é um bom exemplo disso. O que temos normalmente é um cadastro com várias pessoas e precisamos criar uma lista de email geralmente separado por “Vírgula” ou “Ponto e Vírgula” para enviar o email. Abaixo um exemplo, no Oracle, de como isso pode ser feito de forma mais simples:
Criando a tabela de exemplo
create table plz_grupo (id number primary key ,ds varchar2(30)); create table plz_funcionario (id number ,grupo_id number references plz_grupo (id) ,nome varchar2(30) ,email varchar2(30));
Inserindo um conjunto de registros para testes
BEGIN Insert into plz_grupo values (1,'Operacional'); Insert into plz_grupo values (2,'Gerentes'); Insert into plz_grupo values (3,'Diretores'); Insert into plz_grupo values (4,'Vice-Presidentes'); --- insert into plz_funcionario values (01,1,'Func01','FuncOper01@email.com'); insert into plz_funcionario values (02,1,'Func02','FuncOper02@email.com'); insert into plz_funcionario values (03,1,'Func03','FuncOper03@email.com'); insert into plz_funcionario values (04,1,'Func04','FuncOper04@email.com'); insert into plz_funcionario values (05,1,'Func05','FuncOper05@email.com'); insert into plz_funcionario values (06,1,'Func06','FuncOper06@email.com'); insert into plz_funcionario values (07,1,'Func07','FuncOper07@email.com'); insert into plz_funcionario values (08,1,'Func08','FuncOper08@email.com'); insert into plz_funcionario values (09,1,'Func09','FuncOper09@email.com'); --- insert into plz_funcionario values (10,2,'Ger001','FuncGer001@email.com'); insert into plz_funcionario values (11,2,'Ger002','FuncGer002@email.com'); insert into plz_funcionario values (12,2,'Ger003','FuncGer003@email.com'); insert into plz_funcionario values (13,2,'Ger004','FuncGer004@email.com'); insert into plz_funcionario values (14,2,'Ger005','FuncGer005@email.com'); --- insert into plz_funcionario values (15,3,'Diret1','FuncDiret1@email.com'); insert into plz_funcionario values (16,3,'Diret2','FuncDiret2@email.com'); insert into plz_funcionario values (15,3,'Diret3','FuncDiret3@email.com'); --- insert into plz_funcionario values (15,4,'VP1' ,'FuncVP1@email.com'); insert into plz_funcionario values (15,4,'VP2' ,'FuncVP2@email.com'); --- commit; END; /
A query abaixo mostra o resultado de uma query simples para obter os EMails:
select * from PLZ_FUNCIONARIO b order by b.grupo_id;
O plano de acesso desta query é o seguinte:
-------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19 | 1140 | 4 (25) | 00:00:01 | | 1 | SORT ORDER BY | | 19 | 1140 | 4 (25) | 00:00:01 | | 2 | TABLE ACCESS FULL| PLZ_FUNCIONARIO | 19 | 1140 | 3 (0) | 00:00:01 | --------------------------------------------------------------------------------------
Agora, vamos utilizar a função LISTAGG (Disponível somente no 11g) para gerar uma lista de email pronta para ser utilizada com a função UTL_MAIL para enviar email:
select LISTAGG(email,'; ') within group (order by grupo_id) from plz_funcionario;
O plano de acesso desta query é o seguinte
-------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 30 | 3 (0) | 00:00:01 | | 1 | SORT GROUP BY | | 1 | 30 | | | | 2 | TABLE ACCESS FULL| PLZ_FUNCIONARIO | 19 | 570 | 3 (0) | 00:00:01 | --------------------------------------------------------------------------------------
Perceba que o custo da query foi levemente menor, e com a função LISTAGG você já tem o resultado final desejado, sem a necessidade de nenhum outro tipo de processamento.
Agora, vamos considerar que você precise da informação por grupo, o que torna necessário a utilização do grupo na query. Então temos a seguinte query que seria necessária para montar a lista:
select a.ds nome_grupo, b.email from PLZ_GRUPO a, PLZ_FUNCIONARIO b where a.id = b.grupo_id order by a.id;
O plano de acesso dessa query é o seguinte:
--------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19 | 1140 | 8 (25) | 00:00:01 | | 1 | SORT ORDER BY | | 19 | 1140 | 8 (25) | 00:00:01 | |* 2 | HASH JOIN | | 19 | 1140 | 7 (15) | 00:00:01 | | 3 | TABLE ACCESS FULL| PLZ_GRUPO | 4 | 120 | 3 (0) | 00:00:01 | | 4 | TABLE ACCESS FULL| PLZ_FUNCIONARIO | 19 | 570 | 3 (0) | 00:00:01 | ---------------------------------------------------------------------------------------
Agora vamos fazer o mesmo com a função LISTAGG:
select Y.ds nome_grupo, Z.lista from PLZ_GRUPO y , (select x.grupo_id, LISTAGG(x.email,'; ') WITHIN GROUP (order by x.grupo_id) lista from PLZ_FUNCIONARIO X group by X.grupo_id) Z where y.id = z.grupo_id
O plano de acesso dessa query é o seguinte:
---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19 | 38855 | 8 (25) | 00:00:01 | |* 1 | HASH JOIN | | 19 | 38855 | 8 (25) | 00:00:01 | | 2 | TABLE ACCESS FULL | PLZ_GRUPO | 4 | 120 | 3 (0) | 00:00:01 | | 3 | VIEW | | 19 | 38285 | 4 (25) | 00:00:01 | | 4 | SORT GROUP BY | | 19 | 570 | 4 (25) | 00:00:01 | | 5 | TABLE ACCESS FULL | PLZ_FUNCIONARIO | 19 | 570 | 3 (0) | 00:00:01 | ----------------------------------------------------------------------------------------
O plano de acesso tem o mesmo custo que o anterior, porém a função LISTAGG trabalhou com um volume maior de Bytes. Essa diferença está basicamente no processamento que você vai ter que fazer depois para transformar o resultado da primeira query numa lista.
Resultado da comparação entre os planos de acesso:
Os planos de acesso são similares em relação a custo e percentual de utilização da CPU.
Conclusão
A utilização da função LISTAGG neste caso simplifica o desenvolvimento, pois não será necessária a criação de uma função que converta registros em lista, a ainda oferece o mesmo custo de acesso que a forma tradicional de resolver este problema sem considerar todo o processamento que deveria ocorrer depois para montar a lista.
[Crédito da Imagem: Oracle – ShutterStock]