Oracle – Funções de grupo para Converter múltiplas linhas em uma única linha

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]