Função LISTAGG no Oracle PL/SQL
Olá, artistas de dados! Hoje vamos pintar o banco de dados com as cores vibrantes do LISTAGG, a função que transforma linhas solitárias em uma festa de strings agregadas. Prepare-se para exemplos mais extravagantes que um pavão no desfile!
Exemplos Hilários de Implementações Avançadas
Vamos criar a nossa massa de dados para os exemplos:
-- Criação da tabela de funcionários
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
employee_name VARCHAR2(100),
department_id NUMBER,
salary NUMBER
);
-- Inserção de dados na tabela de funcionários
INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES (1, 'Ana', 100, 3000);
INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES (2, 'Bruno', 100, 2800);
INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES (3, 'Carlos', 100, 2700);
INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES (4, 'Daniela', 100, 3200);
INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES (5, 'Eduardo', 100, 3300);
INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES (6, 'Fábio', 200, 3000);
INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES (7, 'Gabriela', 200, 3100);
INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES (8, 'Helena', 200, 2500);
INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES (9, 'Igor', 200, 2600);
INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES (10, 'Joana', 200, 2850);
-- Não esqueça de fazer commit para salvar as alterações
COMMIT;A Saga das Strings Sem Fim
Imagine que você está escrevendo um romance épico, mas o LISTAGG é aquele editor chato que diz: “Desculpe, só 4000 caracteres”. Não tema! Com ON OVERFLOW TRUNCATE, você pode dar um final abrupto à história:
SELECT department_id,
LISTAGG(employee_name, '; ') WITHIN GROUP (ORDER BY employee_name)
ON OVERFLOW TRUNCATE 'E eles viveram... ops, acabou o espaço' WITHOUT COUNT
AS employees
FROM employees
GROUP BY department_id;Executemos
SQL> SELECT department_id,
2 LISTAGG(employee_name, '; ') WITHIN GROUP (ORDER BY employee_name)
3 ON OVERFLOW TRUNCATE 'E eles viveram... ops, acabou o espaço' WITHOUT COUNT
4 AS employees
5 FROM employees
6 GROUP BY department_id;
DEPARTMENT_ID EMPLOYEES
------------- --------------------------------------------------
100 Ana; Bruno; Carlos; Daniela; Eduardo... ops, acabou o espaço
200 Fábio; Gabriela; Helena; Igor; Joana... ops, acabou o espaçoO Baile dos Valores Únicos
Quando você quer que cada nome brilhe uma única vez na pista de dança do LISTAGG, faça uma seleção VIP com DISTINCT:
SELECT department_id,
LISTAGG(DISTINCT employee_name, '; ') WITHIN GROUP (ORDER BY employee_name) AS employees
FROM employees
GROUP BY department_id;Executemos
SQL> SELECT department_id,
2 LISTAGG(DISTINCT employee_name, '; ') WITHIN GROUP (ORDER BY employee_name) AS employees
3 FROM employees
4 GROUP BY department_id;
DEPARTMENT_ID EMPLOYEES
------------- --------------------------------------------------
100 Ana; Bruno; Carlos; Daniela; Eduardo
200 Fábio; Gabriela; Helena; Igor; JoanaA Orquestra de CTEs e Funções de Janela
Quando LISTAGG se junta à orquestra de CTEs e funções de janela, prepare-se para uma sinfonia de dados:
WITH Salaries AS (
SELECT department_id, employee_name, salary,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dr
FROM employees
)
SELECT department_id,
LISTAGG(employee_name, '; ') WITHIN GROUP (ORDER BY dr) AS top_earners
FROM Salaries
WHERE dr = 1
GROUP BY department_id;Executemos
SQL> WITH Salaries AS (
2 SELECT department_id, employee_name, salary,
3 DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dr
4 FROM employees
5 )
6 SELECT department_id,
7 LISTAGG(employee_name, '; ') WITHIN GROUP (ORDER BY dr) AS top_earners
8 FROM Salaries
9 WHERE dr = 1
10 GROUP BY department_id;
DEPARTMENT_ID TOP_EARNERS
------------- --------------------------------------------------
100 Eduardo; Ana
200 Gabriela; FábioO Grande Truque de Mágica com FETCH FIRST
E para o nosso grande final, vamos fazer alguns nomes desaparecerem com um passe de mágica usando FETCH FIRST:
SELECT department_id,
LISTAGG(employee_name, '; ') WITHIN GROUP (ORDER BY employee_name) AS employees
FROM employees
WHERE ROWNUM <= 5
GROUP BY department_id
FETCH FIRST 3 ROWS ONLY;Executemos
SQL> SELECT department_id,
2 LISTAGG(employee_name, '; ') WITHIN GROUP (ORDER BY employee_name) AS employees
3 FROM employees
4 WHERE ROWNUM <= 5
5 GROUP BY department_id
6 FETCH FIRST 3 ROWS ONLY;
DEPARTMENT_ID EMPLOYEES
------------- --------------------------------------------------
100 Ana; Bruno; Carlos
200 Fábio; Gabriela; HelenaConclusão
E assim, com um toque de criatividade e um pouco de humor, transformamos o mundano em magnífico! O LISTAGG não é apenas uma função, é um convite para a inovação. Use-o sabiamente, e seus dados dançarão ao ritmo da eficiência.
Referências