Arquivo da tag: oracle

Como agrupar várias linhas em apenas uma no Oracle ?

Olá gente, aqui estou eu mais uma vez para lhes dar outra dica simples, porém muito útil no dia-a-dia.

Por vezes me deparei com situações em que uma query retornava vários registros, mas eu precisava de apenas 1 registro, com os resultados separados por vírgula, por exemplo. Vou apresentar neste post as 3 maneiras mais simples que encontrei de realizar isto.

Para exemplificar, segue a query “CRUA”:

select ename from emp

ENAME
----------
KING
BLAKE
CLARK
JONES
MARTIN
ALLEN
TURNER
JAMES
FORD
SMITH
SCOTT
ADAMS
MILLER

13 linhas selecionadas

Bom, a primeira (e mais simples) maneira de agrupar essas linhas em uma única linha, é usando a função de grupo (não documentada) WM_CONCAT, que está presente a partir do Oracle 10g. Essa função agrupa a coluna e separa os resultados por uma vírgula (não tem como mudar). Ficaria então assim:

select wm_concat(ename) from emp

ENAME
----------
ADAMS,ALLEN,BLAKE,CLARK,FORD,JAMES,JONES,KING,MARTIN,MILLER,SCOTT,SMITH,TURNER

1 linha selecionada

Um dos problemas desta função é que não está presente em todos os bancos Oracle, e nem tem tem esse dever por não ser documentada. Outro ponto negativo é que os registros sempre são divididos por vírgulas.

A próxima saída para nosso problema seria fazer um autêntico “migué”, utilizando xml para resolver o problema.  MAS COMO ?!
A ideia é simples, criamos um xml com a função xmlelement, depois extraímos o texto do xml e aplicamos a função de grupo xmlagg que é a grande responsável por agrupar os resultados.

Segue código:

select rtrim(xmlagg(xmlelement(e,ename,';').extract('//text()')),';') from emp;

ENAME
----------
ADAMS;ALLEN;BLAKE;CLARK;FORD;JAMES;JONES;KING;MARTIN;MILLER;SCOTT;SMITH;TURNER

1 linha selecionada

Sucesso!
O ponto positivo desta técnica é que podemos agora escolher qual será o separador das strings (no caso eu utilizei um ponto e vírgula). Acredito que essa função xml também só está presente a partir do 10g.

A última solução (mas não menos importante) que irei abordar no tópico de hoje se refere ao comando LISTAGG que está presente nos bancos 11g a partir do release 2.
O grande ponto forte deste comando (além de sua simplicidade, e a capacidade de escolher o separador) é a possibilidade de ordenação dos registros da lista.

Segue código:

select LISTAGG(ename, '#') WITHIN GROUP (ORDER BY ename desc)  from emp;

ENAME
----------
TURNER#SMITH#SCOTT#MILLER#MARTIN#KING#JONES#JAMES#FORD#CLARK#BLAKE#ALLEN#ADAMS

1 linha selecionada

Bom é isso aí gente!
Se rolar alguma dúvida é só postar!

Até mais!

Não há como juntar o pó, se continuo insistindo em sentir a ventania.
–  Reynaldo Martins

Threads em PL/SQL ?

Boa Noite minha gente !

Ainda estou me acostumando com isso aqui, pegando no tranco… hehe

Hoje vou postar um pouco sobre Oracle PL/SQL (e com o tempo vocês vão perceber que eu gosto da coisa… rs)

A dica que vou dar é muito simples mas funcional. Vamos supor que temos um processo que demora muito tempo para ser executado, e não sabemos o porque. O primeiro passo, é claro, seria debugar o seu código. Um método convencional para tal ação seria inserir aluns “dbms_output.put_line” no código. Mas e se você quisesse gravar o resultado desse debug? Você poderia apenas inserir uma linha em alguma tabela temporária. Problema resolvido, certo??? ERRADO !

Para que o insert funcione, é necessário que exista um commit no código, o que pode não ser permitido dependendo do processamento que está sendo feito. Nesse caso, nosso processo iria inserir as linhas mas não conseguiríamos ver (caso o processo nao chegasse ao final) o que não é interessante. Para resolver tal questão, decidi criar uma procedure PRC_LOG que ao ser chamada insere um registro numa tabela de log e commita. Para que esse commit não interfira no “processo pai” que está sendo executado, é necessário que a procedure esteja com o pragma autonomous_transaction. Um pragma define o estado do código, é como se estivéssemos mandando uma mensagem para o compilador do oracle. Existem diversos tipos de pragma, e o que utilizamos (autonomous_transaction) serve para executar o processo em modo “autônomo”, ou seja de modo independente ao processamento atual (em outro contexto). Em programação, esse conceito é conhecido como Thread (ou processamento paralelo) e pode ser aplicado para qualquer coisa além do descrito acima, basta usar a criatividade! =]

Segue então, o código do exemplo acima:

-- create table log (a varchar2(4000));

create or replace procedure PRC_LOG (PARAM in varchar2) as
  pragma autonomous_transaction;
begin
  insert into LOG values ( PARAM );
  commit;
end PRC_LOG;
/

É isso ai! Simples mas funcional.

Espero que tenham gostado!

Até a próxima =]

Se você enxergar o que está por trás de todas as coisas sem exceção, então tudo se tornará transparente para você. Mas um mundo completamente transparente é um mundo invisível.
– C.S. Lewis