Table Function su iSeries – esempi di come incapsulare le logiche di business

Le funzioni tabella su iSeries sono una funzionalità potente che consente di incapsulare le logiche di business restituendo in forma tabellare il risultato di estrazioni utilizzabili all’interno di altri comandi sql.

Le funzioni tabella su iSeries possono essere utilizzate per una varietà di scopi, tra cui:

• Incapsulare logiche di business: Le funzioni tabella possono essere utilizzate per incapsulare logiche di business complesse, rendendole più facili da comprendere e mantenere.
• Effettuare estrazioni dati: Le funzioni tabella possono essere utilizzate per effettuare estrazioni dati da una o più tabelle, rendendole più efficienti e facili da utilizzare.
• Crea report: Le funzioni tabella possono essere utilizzate per creare report complessi, rendendoli più flessibili e facili da personalizzare.

In altri post abbiamo già parlato dell’argomento:

• Table Function in IBM iSeries: come ti incapsulo il sistema rendendolo facilmente accessibile con SQL
• Table Function DISPLAY_JOURNAL – Swiss Army Knife (strumento versatile per molte situazioni)
• Le Tabelle Definite dall’Utente (UDTF) in IBM iSeries: Un Approccio Innovativo per la Gestione dei Dati

In questo post si vuole enfatizzare l’utilizzo di funzionalità SQL che incapsulano le logiche di business restituendo in forma tabellare il risultato di estrazioni utilizzabili all’interno di altri comandi sql.

L’utilizzo delle Function Table in SQL su IBM iSeries (AS/400) infatti può arricchire notevolmente le query, permettendo di incorporare dati dinamici e calcolati in tempo reale all’interno di select più complesse, inclusi join, subquery, condizioni where e altre costruzioni SQL.

Best Practices

• Le Function Table devono essere progettate in modo da essere efficienti, soprattutto se utilizzate in query complesse o su grandi set di dati.
• La complessità della funzione e la quantità di dati che restituisce possono influenzare le prestazioni della query.
• È importante testare e ottimizzare le query, specialmente quando si combinano più tabelle e function table in un’unica query.

Di seguito alcuni esempi significativi di funzioni tabella su iSeries

Nota: gli esempi presentati in questo post hanno scopo puramente didattico e illustrativo. Essi forniscono un’idea di come le funzioni possano essere utilizzate, ma non considerano tutte le possibili casistiche e situazioni che si possono verificare in un contesto reale.

• Function Table per generare un report sulle vendite (uso in INNER JOIN)
• Function Table per Log Utenti (uso in INNER JOIN)
• Function Table per Date Festive (uso in WHERE)
• Function Table per Calcolare Sconti sui Prodotti (uso in inner query)

Function Table per generare un report sulle vendite (uso in INNER JOIN)

La seguente funzione tabella genera un report sulle vendite per un periodo di tempo specificato:

CREATE FUNCTION sales_report (
    start_date DATE,
    end_date DATE
) RETURNS TABLE (
    product_id INT,
    quantity INT,
    amount DECIMAL(10,2)
)
LANGUAGE SQL
READS SQL DATA
  RETURN
    SELECT
        product_id,
        SUM(quantity),
        SUM(amount)
    FROM
        sales
    WHERE
        date >= start_date
        AND date <= end_date
    GROUP BY product_id;

Questa Funzione può essere utilizzata per generare un report vendite:

SELECT p.product_id,
       p.product_desc,
       s.quantity,
       s.amount
FROM product_id, product_desc FROM products p
INNER JOIN TABLE(sales_report('2023-01-01 00:00:00','2023-12-31 59:59:99')) AS s
ON s.product_id = p.product_id
ORDER BY 1;

Function Table per Log Utenti (uso in INNER JOIN)

La seguente funzione tabella restituisce i log relativi ad un determinato utente

CREATE FUNCTION FunzioneLogUtenti (
UserID INT
) RETURNS TABLE (
    UserID INT,
    InfoLog VARCHAR(255)
)
LANGUAGE SQL
READS SQL DATA
  RETURN
    SELECT
        UserID,
        InfoLog
    FROM TabellaLog
    WHERE UserID = FunzioneLogUtenti.UserID; 

Questa Funzione può essere utilizzata per generare un report dei log degli utenti:

SELECT u.Nome,
       u.Cognome,
       l.InfoLog
 FROM TabellaUtenti AS u
 INNER JOIN TABLE(FunzioneLogUtenti(u.UserID)) AS l
 ON u.UserID = l.UserID; 

Function Table per Date Festive (uso in WHERE)

La seguente funzione tabella restituisce un elenco di date festive (supponiamo di avere una tabella avente le date festive a livello aziendale):

CREATE FUNCTION FunzioneDateFestive (
)
 RETURNS TABLE (
    Data DATE
)
LANGUAGE SQL
READS SQL DATA
  RETURN
    SELECT
        DataFestiva AS Data
    FROM TabellaFestività;

La funzione tabella così creata può essere utilizzata ad esempio un una condizione WHERE:

SELECT *
FROM TabellaOrdini
WHERE exists (SELECT 1
                FROM TABLE(FunzioneDateFestive())
                WHERE DataOrdine = Data
              );

Function Table per Calcolare Sconti sui Prodotti (uso in inner query)

La seguente funzione tabella calcola lo sconto applicato a un prodotto identificato in base al suo ID

CREATE FUNCTION CalcolaSconto (
    ProdottoID INT
) RETURNS TABLE (
    Sconto DECIMAL(5, 2)
)
LANGUAGE SQL
READS SQL DATA
    RETURN
      SELECT
          CASE WHEN Categoria = 'Elettronica' THEN Prezzo * 0.1
               WHEN Categoria = 'Libri'       THEN Prezzo * 0.05
               ELSE 0
           END AS Sconto
      FROM TabellaProdotti
      WHERE ID = CalcolaSconto.ProdottoID;

La funzione tabella così creata può essere utilizzata ad esempio un una inner query (o subquery):

SELECT p.Nome,
       p.Prezzo,
       (SELECT Sconto FROM TABLE(CalcolaSconto(p.ID))) AS ScontoApplicato
FROM TabellaProdotti AS p

In questo esempio, la inner query calcola lo sconto per ogni prodotto basandosi sulla Function Table CalcolaSconto

Note Importanti

• Le definizioni delle Function Table qui mostrate sono semplificate e potrebbero richiedere adattamenti in base alle specifiche dell’ambiente iSeries e alla struttura dei dati.
• La sintassi SQL e le funzionalità possono variare a seconda della versione del DB2 e del sistema operativo iSeries.
• È essenziale testare le Function Table e le query associate per assicurarsi che siano ottimizzate per le prestazioni, soprattutto in ambienti di produzione con grandi volumi di dati.