Oracle Italia by Massimo Ruocchio

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