Tenho uma tabela de permissões que tem a seguinte forma:
CREATE TABLE PERMISSOES (
OBJETO VARCHAR2(100),
USUARIO VARCHAR2(100)
)
Eu preciso de um relatório que enumere todos os usuários para cada objeto, colocando todos os autorizados numa só linha. A minha solução, um pouco complicada, foi juntar as facilidades de consultas hierárquicas do Oracle com um pouco de funções analíticas.
O cerne da consulta é o seguinte SELECT:
select objeto,
row_number() over (partition by objeto order by usuario) ordem,
row_number() over (partition by objeto order by usuario desc) medro,
usuario
from permissoes
order by 1, 2, 3 desc
Isso produz uma lista de todos os objetos com os respectivos usuários numerados alfabeticamente tanto crescentemente como decrescentemente. Considere as seguintes linhas:
| OBJETO | USUARIO |
| COFRE | Heráclito |
| COFRE | Xenofonte |
| COMPUTADOR | Heródoto |
| COMPUTADOR | Heráclito |
| COMPUTADOR | Platão |
A consulta produzirá o resultado abaixo:
| OBJETO | ORDEM | MEDRO | USUARIO |
| COFRE | 1 | 2 | Heráclito |
| COFRE | 2 | 1 | Xenofonte |
| COMPUTADOR | 1 | 3 | Heráclito |
| COMPUTADOR | 2 | 2 | Heródoto |
| COMPUTADOR | 3 | 1 | Platão |
Envolvendo essa consulta com um SELECT hierárquico, podemos agregar os helenos.
select objeto, substr(sys_connect_by_path(usuario, ','),2) autorizados
from (
select objeto,
row_number() over (partition by objeto order by usuario) ordem,
row_number() over (partition by objeto order by usuario desc) medro,
usuario
from permissoes
)
where medro=1
connect by prior ordem=ordem-1 and prior objeto=objeto
start with ordem=1
Então, uso a coluna ORDEM para simular uma hierarquia e a coluna MEDRO para eliminar todas as linhas exceto a última (que possui todos os usuarios relacionados a um objeto).
O resultado é o seguinte:
| OBJETO | AUTORIZADOS |
| COFRE | Heráclito,Xenofonte |
| COMPUTADOR | Heráclito,Heródoto,Platão |
A função sys_connect_by_path() aglutina toda a hierarquia e neste caso indiquei uma vírgula como separador. No entanto, ela coloca uma vírgula também no início e para resolver isso adicionei a função substr().
