Ultimo aggiornamento: 12-Gen-2019

4 Luglio 2018MarkOne

Query ricorsive

Le query ricorsive consentono di risolvere diversi casi di lettura dati che implicano l’esecuzione ciclica della medesima query partendo ogni volta dal risultato della precedente esecuzione.

Alcuni contesti in cui può tornare utile scrivere una query ricorsiva sono:

  • esplosione componenti di una distinta base: nell’ambito della produzione una distinta base elenca per un certo prodotto finito l’elenco dei suoi componenti. A loro volta uno o più componenti possono essere anch’essi formati da altri componenti e così via
  • visualizzazioni delle relazioni di dipendenza di una tabella: data una tabella si desidera visualizzare p.es. l’elenco delle viste dipendenti. Ogni vista potrebbe avere a sua volta altre dipendenze.
  • concatenazione in un unico campo risultato di un campo descrittivo contenuto su più record: p.es. una tabella di annotazioni legate ad una tabella anagrafica (clienti, impiegati, articoli…) in cui la tabella ha un campo nota da 100 byte alfanumerico e per ogni soggetto possono esistere più di un record
  • … e molti altri

Dalla V5R4 è possibile scrivere delle query ricorsive con l’istruzione with... che è usata per definire le CTE (Common Table Expression).

Le CTE sono uno strumento molto potente per gestire uno o più resultset volatili in una sola istruzione SQL.

Le CTE sono sempre da prediligere rispetto ad altre soluzioni che utilizzano tabelle temporanee. Questo per ragioni di perfomance.

ATTENZIONE: se i dati contenuti nella tabella possono prevedere delle relazioni cicliche è necessario utilizzare la clausola CYCLE per prevenire query senza fine.

Esempio n.1: concatenazione di più record

Ipotizziamo di creare una tabella di annotazioni collegata alla tabella EMPLOYEE (presente nel database di esempio di IBM).
Creiamo la tabella in questo modo

create or replace table EMP_NOTE
  (
    EMPNO char(6) not null with default,
    EMPROW int not null with default,
    EMPNOTA char(80) not null with default
  );
  
label on table EMP_NOTE is 'Employee annotations';

label on column EMP_NOTE
  (
    EMPNO is 'employee id',
    EMPROW is 'progressivo',
    EMPNOTA is 'testo nota'
  );

alter table EMP_NOTE
  add constraint EMPNO00P primary key (EMPNO, EMPROW);

E popoliamola con alcuni dati di esempio

insert into EMP_NOTE
  values ('000010', 1, 'nota riga 1'), ('000010', 2, 'nota riga 2');

insert into EMP_NOTE
  values ('000020', 1, 'nota riga 1'), ('000020', 2, 'nota riga 2'), ('000020', 3, 'nota riga 3');

Se volessimo ottenere come risultato un record per ogni impiegato con la concatenazione del campo nota di tutti i suoi record possiamo scrivere questa query ricorsiva.
La query assume che il campo EMPROW contenga un numero progressivo di riga consecutivo senza “buchi” (p.es., 1, 2, 3, 4… In caso si incontri un “buco” nella sequenza la query ricorsiva si “ferma”)

with NOTE (EMPNO, EMPROW, EMPNOTA) as (
  -- inizializzazione della query ricorsiva: ovvero ottiene il primo record (quello con num.riga più piccolo) di ogni impiegato
  select C.EMPNO, C.EMPROW, varchar(C.EMPNOTA, 3000)
    from EMP_NOTE as C
    where C.EMPNO in('000010', '000020') and C.EMPROW = (select min(M.EMPROW) from EMP_NOTE as M where (M.EMPNO) = (C.EMPNO))
  union all
  -- viene eseguita quindi una union ricorsiva al livello successivo ovvero al numero di riga della query precedente + 1 
  -- dove ad ogni passo concateno il campo della descrizione del record precedente con quello corrente
  select Next.EMPNO, Next.EMPROW, trim(O.EMPNOTA) concat ' ' concat trim(Next.EMPNOTA)
    from EMP_NOTE as Next, NOTE as O
    where Next.EMPNO = O.EMPNO and Next.EMPROW = O.EMPROW + 1)
 -- alla fine viene mostrato il risultato finale filtrando per ogni impiegato solo il numero di riga maggiore
 -- che contiene la concatenazione di tutte le descrizioni
 select P.EMPNO, P.EMPNOTA
   from NOTE as P
   where P.EMPROW = (select max(N.EMPROW) from NOTE as N where (N.EMPNO) = (P.EMPNO))
   order by P.EMPNO;

Esempio n. 2 elenco dipendenze di una tabella

Eseguendo una query ricorsiva sulla vista SYSVIEWDEP del catalogo del DB2 è possibile elencare tutte le viste che fanno riferimento a una certa tabella. A loro volta la vista potrebbe essere stata utilizzata in un’altra vista e così via.
Ipotizziamo di aver creato il database di esempio di IBM con il nome DB2SAMPLE la query seguente elenca tutte le viste basate sulla tabella EMPLOYEE. Per ogni vista la query cicla al livello successivo cercando eventuali altre viste basate su di essa e così via.

with VISTEDEP (LEVEL, VIEW, OBJECT) as          
     (select 1, T1.VIEW_NAME, T1.OBJECT_NAME
        from QSYS2/SYSVIEWDEP AS T1
        where T1.OBJECT_NAME = 'EMPLOYEE' and T1.OBJECT_SCHEMA = 'DB2SAMPLE'
      union ALL                                                          
      select  O.LEVEL + 1, T2.VIEW_NAME, T2.OBJECT_NAME
        from QSYS2/SYSVIEWDEP  AS T2 join VISTEDEP AS O ON O.VIEW = T2.OBJECT_NAME)
      select distinct LEVEL, VIEW, OBJECT
        from VISTEDEP
        order by LEVEL DESC, VIEW;

Esempio n. 3 Visualizzazione gerarchica delle relazioni tra impiegati

Creiamo una tabella in cui per ogni impiegato esiste un campo che contiene l’ID impiegato del manager. In una struttura aziendale complessa, a sua volta il manager potrebbe avere un altro manager di livello superiore.

create or replace table EMP_REL
  (EMPNO char(6) not null with default primary key,
   NAME varchar(20) not null with default,
   MGRNO char(6) not null with default);
   
label on table EMP_REL is 'Employee relations';   

label on column EMP_REL
  (
    EMPNO is 'employee id',
    NAME is 'nome',
    MGRNO is 'manager id'
  );

E popoliamola con i seguenti dati:

insert into EMP_REL
  values ('100000', 'Topolino', ' '), 
         ('100010', 'Pippo', '100000'),
         ('100020', 'Pluto', '100000'),
         ('100030', 'Tip', '100010'),
         ('100040', 'Tap', '100010'),
         ('100050', 'Qui', '100030'),
         ('100060', 'Quo', '100030'),
         ('100070', 'Qua', '100030'),
         ('100080', 'Cip', '100040'),
         ('100090', 'Ciop', '100040');

Dall’impiegato ‘Pippo’ vogliamo elencare tutti gli impiegati a lui subordinati come rappresentati nell’organigramma:

with EMP_LIST (LEVEL, EMPNO, NAME) as
   (select 1, EMPNO, NAME from EMP_REL
         where NAME = 'Pippo'
    union all
    select O.LEVEL + 1, NEXT_LAYER.EMPNO, NEXT_LAYER.NAME
     from EMP_REL as NEXT_LAYER, EMP_LIST as O
     where O.EMPNO = NEXT_LAYER.MGRNO)
select LEVEL, NAME from EMP_LIST;

Una variante della query precedente ci consente di visualizzare il risultato ordinato gerarchicamente. Abbiamo aggiunto la clausola search depth first by.

with EMP_LIST (LEVEL, EMPNO, NAME) as
   (select 1, EMPNO, NAME from EMP_REL
         where NAME = 'Pippo'
    union all
    select O.LEVEL + 1, NEXT_LAYER.EMPNO, NEXT_LAYER.NAME
     from EMP_REL as NEXT_LAYER, EMP_LIST as O
     where O.EMPNO = NEXT_LAYER.MGRNO)
search depth first by EMPNO set SEQCOL
select LEVEL, NAME from EMP_LIST order by SEQCOL;