Aggregazione di stringhe, funzioni pipelined ed oggetti

Volevo ripartire dal post precedente per mostrare come una table function può essere utilizzata per risolvere il problema della string aggregation che ho già affrontato in un altro articolo.

Cominciamo con un esempio semplice e poi cerchiamo di generalizzarlo.
Vogliamo aggregare i nomi dei dipendenti (ENAME) per dipartimento (DEPTNO):

SQL> create or replace type t_strings is table of varchar2(100);
  2  /

Type created.

SQL>
SQL> create or replace type t_dept is object (deptno number, emps t_strings);
  2  /

Type created.

SQL>
SQL> create or replace type t_depts is table of t_dept;
  2  /

Type created.

SQL>
SQL> create or replace function dept_aggr
  2  return t_depts PIPELINED is
  3  s t_strings;
  4  dr t_dept;
  5  begin
  6    for r in (select * from dept) loop
  7       select ename
  8         bulk collect into s
  9         from emp
 10        where deptno=r.deptno;
 11       dr := t_dept(r.deptno,s);
 12       pipe row (dr);
 13    end loop;
 14    return;
 15  end;
 16  /

Function created.

SQL> select * from table(dept_aggr);

    DEPTNO EMPS
---------- -------------------------------------------------------------------------
        10 T_STRINGS('CLARK', 'KING', 'MILLER')
        20 T_STRINGS('SMITH', 'JONES', 'SCOTT', 'ADAMS', 'FORD')
        30 T_STRINGS('ALLEN', 'WARD', 'MARTIN', 'BLAKE', 'TURNER', 'JAMES')
        40 T_STRINGS()

La funzione non fa altro che aggegare i nomi in una collezione e restituire un record formato da codice dipartimento e collezione dei nomi dei dipendenti.
Cerchiamo adesso di generalizzare:

Innanzi tutto droppiamo tutto per ricominciare daccapo:

drop function dept_aggr;
drop type t_depts;
drop type t_dept;
drop type t_strings;

Poi creiamo dei tipi un po’ più generici:

SQL> create or replace type t_strings is table of varchar2(100);
  2  /

Type created.

SQL> create or replace type t_stragg is object
  2  (strings t_strings,
  3   MEMBER FUNCTION tostring(sep varchar2 default ',') RETURN varchar2,
  4   MEMBER FUNCTION contains(val varchar2) RETURN pls_integer
  5  ) NOT FINAL;
  6  /

Type created.

SQL> create or replace type body t_stragg is
  2   MEMBER FUNCTION tostring(sep varchar2 default ',') RETURN varchar2  IS
  3    retstr varchar2(32000);
  4    BEGIN
  5      if strings.count>0 then
  6        for i in strings.first..strings.last loop
  7          retstr := retstr||strings(i)||sep;
  8        end loop;
  9      end if;
 10      RETURN rtrim(retstr,sep);
 11    END;
 12   MEMBER FUNCTION contains(val varchar2) RETURN pls_integer is
 13    BEGIN
 14      if strings.count>0 then
 15        for i in strings.first..strings.last loop
 16          if strings(i) = val then
 17             return i;
 18          end if;
 19        end loop;
 20      end if;
 21      return 0;
 22    END;
 23  end;
 24  /

Type body created.

Questi tipi possono essere utilizzati per qualunque problema di aggregazione di stringhe ed offrono un paio di funzionalità in più che utilizzeremo più avanti.

Creiamo gli oggetti specifici di questo problema estendendo quello generico:

SQL> create or replace type t_dept under t_stragg
  2  (deptno number);
  3  /

Type created.

SQL>
SQL> create or replace type t_depts is table of t_dept;
  2  /

Type created.

La parola chiave UNDER consente di creare un oggetto come estensione di un altro creato prima.
Attenzione: l’oggetto che si intende estendere deve essere stato dichiarato NOT FINAL.

La funzione resta praticamnete identica alla precedente, a parte il fatto che nel tipo T_DEPT è cambiato l’ordine delle proprietà:

SQL> create or replace function dept_aggr
  2  return t_depts PIPELINED is
  3  s t_strings;
  4  dr t_dept;
  5  begin
  6    for r in (select * from dept) loop
  7       select ename
  8         bulk collect into s
  9         from emp
 10        where deptno=r.deptno;
 11       dr := t_dept(s,r.deptno);
 12       pipe row (dr);
 13    end loop;
 14    return;
 15  end;
 16  /

Function created.

Rispetto a prima abbiamo maggiore flessibilità perché possiamo:

Facilmente ottenere l’aggregazione di stringhe:

SQL> select deptno, r.tostring() names
  2  from table(dept_aggr) r;

    DEPTNO NAMES
---------- ----------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
        40

Utilizzando qualunque separatore:

SQL> select deptno, r.tostring('#') names
  2  from table(dept_aggr) r
  3  ;

    DEPTNO NAMES
---------- --------------------------------------------
        10 CLARK#KING#MILLER
        20 SMITH#JONES#SCOTT#ADAMS#FORD
        30 ALLEN#WARD#MARTIN#BLAKE#TURNER#JAMES
        40

SQL> select deptno, r.tostring(' ') names
  2  from table(dept_aggr) r;

    DEPTNO NAMES
---------- --------------------------------------------
        10 CLARK KING MILLER
        20 SMITH JONES SCOTT ADAMS FORD
        30 ALLEN WARD MARTIN BLAKE TURNER JAMES
        40

Facilmente ricercare nelle stringhe aggregate:

SQL> select deptno, r.tostring() names
  2  from table(dept_aggr) r
  3  where r.contains('JONES')>0;

    DEPTNO NAMES
---------- ---------------------------------------
        20 SMITH,JONES,SCOTT,ADAMS,FORD

Possiamo comunque ancora estrarre i nomi dei dipendenti sotto forma di oggetto:

SQL> select deptno, strings
  2  from table(dept_aggr) r;

    DEPTNO STRINGS
---------- --------------------------------------------------------------------
        10 T_STRINGS('CLARK', 'KING', 'MILLER')
        20 T_STRINGS('SMITH', 'JONES', 'SCOTT', 'ADAMS', 'FORD')
        30 T_STRINGS('ALLEN', 'WARD', 'MARTIN', 'BLAKE', 'TURNER', 'JAMES')
        40 T_STRINGS()
        

Oppure il recordset originale disaggregato:

SQL> select deptno, column_value name
  2* from table(dept_aggr) r, table(r.strings)

    DEPTNO NAME
---------- -----------------------------------------
        10 CLARK
        10 KING
        10 MILLER
        20 SMITH
        20 JONES
        20 SCOTT
        20 ADAMS
        20 FORD
        30 ALLEN
        30 WARD
        30 MARTIN
        30 BLAKE
        30 TURNER
        30 JAMES

14 rows selected.

Alla prossima,
Massimo

Concatenare i valori di più record

This article is also available in English here.

Tutti sappiamo come funziona la clausola GROUP BY e quando utilizzarla. Anche le funzioni di gruppo che vengono utilizzate insieme alla GROUP BY sono generalmente molto note: SUM, AVG, MIN, MAX e soprattutto la COUNT…
Capita però spesso di dover risolvere un problema “di gruppo” a cui queste funzioni non danno una risposta: Utilizzando la tabella EMP dei dipendenti (ENAME è il nome del dipendente e DEPTNO il numero del dipartimento) immaginiamo di voler estrarre un record per ogni DEPTNO che evidenzi l’elenco dei nomi dei dipendenti impiegati nel dipartimento separati da virgola.
Ecco gli impiegati presenti nella tabella EMP:

SQL> select deptno, ename
  2  from emp
  3* order by deptno

    DEPTNO ENAME
---------- ----------
        10 CLARK
        10 KING
        10 MILLER
        20 JONES
        20 FORD
        20 ADAMS
        20 SMITH
        20 SCOTT
        30 WARD
        30 TURNER
        30 ALLEN
        30 JAMES
        30 BLAKE
        30 MARTIN

E vogliamo ottenere qualcosa del genere :

    DEPTNO ENAMES
---------- ----------
        10 CLARK,KING,MILLER
        20 JONES,FORD,ADAMS,SMITH,SCOTT
        30 WARD,TURNER,ALLEN,JAMES,BLAKE,MARTIN

L’approccio più naturale è probabilmente la scrittura di una funzione PL/SQL:

create or replace function getNames(p_deptno in number) return varchar2 is
retval varchar2(32000);
begin
  for r in (select ename from emp where deptno = p_deptno) loop
    retval := retval||r.ename||',';
  end loop;
  return rtrim(retval,',');
end;
/

SQL> select deptno,getNames(deptno) enames
  2  from emp
  3* group by deptno

    DEPTNO ENAMES
---------- -------------------------------------------
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        10 CLARK,KING,MILLER

Quest’approccio ha però il difetto di effettuare una select (quella definita nel cursore implicito del loop, nel PL/SQL) per ogni record di EMP. Ciò può essere molto grave su grandi moli di dati.

Il secondo metodo che voglio presentare è basato su una funzione di gruppo non documentata: WM_CONCAT.
Questa funzione è definita nello schema WMSYS. Questo schema contiene gli oggetti di sistema per la funzionalità “Workspace Manager” su cui potete trovare maggiori info a questa pagina.
Si tratta di una funzione non documentata, utilizzata da Oracle per scopi interni e dunque è assolutamente sconsigliabile utilizzarla in produzione.
Però esiste e funziona una meraviglia:

SQL> select deptno, wm_concat(ename) enames
  2  from emp
  3  group by deptno;

    DEPTNO ENAMES
---------- -------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

Terzo metodo che vi presento è l’utilizzo della funzione SYS_CONNECT_BY_PATH.
Questa funzione può essere utilizzata solo in query gerarchiche (CONNECT BY) prende in input una colonna ed un carattere e restituisce il path completa di ogni elemento della colonna data a partire dalla root utilizzando il carattere passato in input come separatore.
Un esempio ci chiarirà le idee:

 SELECT deptno, ltrim(SYS_CONNECT_BY_PATH(ename, ','),',') enames
  FROM (select deptno, ename, rank() over(partition by deptno order by rownum) num from emp)
where connect_by_isleaf=1
START WITH num=1
CONNECT BY PRIOR num+1 = num and prior deptno=deptno;

    DEPTNO ENAMES
---------- ---------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

Non è chiaro? Ok parliamone:
Prima di tutto sono partito da una query che mi mostra l’elenco dei dipendenti ordinati con un progressivo (num) che si incrementa ad ogni riga e si resetta al cambio di dipartimento.

SQL> select deptno, ename, rank() over(partition by deptno order by rownum) num
  2         from emp
  3  ;

    DEPTNO ENAME             NUM 
---------- ---------- ---------- 
        10 CLARK               1 
        10 KING                2 
        10 MILLER              3 
        20 SMITH               1 
        20 JONES               2 
        20 SCOTT               3 
        20 ADAMS               4 
        20 FORD                5 
        30 ALLEN               1 
        30 WARD                2 
        30 MARTIN              3 
        30 BLAKE               4 
        30 TURNER              5 
        30 JAMES               6 

Per ottenere questo risultato ho utilizzato le funzioni analitiche, disponibili in Oracle dalla versione 9i di cui ho parlato in un articolo pubblicato anni fa su Computer Programming.

A questo punto sono passato alla query gerarchica: chi fosse a digiuno di CONNECT BY può dare uno sguardo sulla documentazione.

Collego ogni ename a quello che ha lo stesso deptno e num maggiore di uno (il successivo) mediante la clausola

CONNECT BY PRIOR num+1 = num and prior deptno=deptno


Elimino a priori i rami incompleti, utilizzando la condizione

WHERE connect_by_isleaf=1

che ci permette di selezionare solo le foglie dell’albero.

Infine mi interesso solo ai rami che partono dal primo ename per ogni deptno mediante la clausola


START WITH num=1


Giocate un po’ modificando questa query, ne apprezzerete la potenza un po’ alla volta!

Quarto approccio: Query XML.

SQL> select deptno, rtrim(
  2                   xmlagg(
  3                     xmlelement("x",ename||',')
  4                          ).extract('//text()'),',') enames
  5    from emp
  6  group by deptno;

    DEPTNO ENAMES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

Per ogni dipartimento ottengo con XMLAGG un documento XML che aggrega tutti i dipendenti che lavorano in quel dipartimento e poi estraggo solo il contenuto degli elementi utilizzando la funzione extract.

Quinto approccio: la funzione LISTAGG.
Si tratta di una nuova funzione disponibile in Oracle11g release 2. Ecco la documentazione.

SQL >SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) enames
  2    FROM emp
  3  group by deptno;

    DEPTNO ENAMES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

Funziona alla meraviglia!!

Alla prossima,
Massimo