02 - Database DB2 for i02a - SQL02c - Varie Database DB2 for i

Funzioni SQL più “intelligenti” con le istruzioni di controllo

Spesso si scrivono delle function SQL con lo scopo di ottenere il risultato di una query semplice, oppure per interfacciare procedure di programmi di servizio RPG che contengono logica più complessa.
Ma l’SQL può fare molto più che non semplici SELECT o UPDATE… grazie ad una serie di particolari istruzioni è possibile implementare della logica procedurale anche all’interno di funzioni. Queste istruzioni sono dette “control statements” e non sono molto differenti dalle comuni istruzioni di controllo che troviamo nell’RPG. Con queste istruzioni possiamo aggiungere “intelligenza” e flessibilità alle nostre funzioni SQL, che possono quindi svolgere elaborazioni più complesse senza dover necessariamente mettere in mezzo un programma o un programma di servizio RPG.
Le istruzioni di controllo sono documentate nel manuale SQL Reference a questo link.
In questo elenco di istruzioni ne ritroviamo molte, per così dire, “familiari”… IF, FOR, LEAVE ed altre le ritroviamo identiche (o molto simili) alle corrispondenti istruzioni RPG o CL. Grosso modo questo elenco si può dividere in tre categorie:
Istruzioni di controllo del flusso: CASE, FOR, GOTO, IF, ITERATE, LEAVE, LOOP, REPEAT, RETURN, WHILE
Istruzioni informative e di segnalazione errori: GET DIAGNOSTICS, SIGNAL, RESIGNAL
Altre istruzioni: CALL, INCLUDE, PIPE

Le istruzioni di controllo si possono utilizzare all’interno di funzioni SQL, ma anche in altri oggetti tipici SQL come procedure e trigger.
Dopo la teoria, passiamo alla pratica… vediamo una function SQL che restituisce il saldo della giacenza di magazzino alla data per un articolo. La funzione legge da un file il saldo attuale, e da un altro file con i movimenti di magazzino legge a ritroso fino alla data desiderata ricostruendo il saldo.
Ecco il sorgente:

--
-- funzione getSaldo
--
--  Restituisce giacenza di magazzino ad una data per l'articolo
--
--  Parametri:
--  IN   codice articolo  CHAR(15)
--  IN   codice magazzino CHAR(2)
--  IN   data             DATE
--
--  OUT  giacenza        NUMERIC(10, 2)
--      
--
CREATE OR REPLACE FUNCTION getSaldo (
pCodItem    CHAR(40),
pCodWhs     CHAR(02),
pDateBal    DATE
)
RETURNS   NUMERIC(10, 2)

LANGUAGE SQL
READS SQL DATA
BEGIN

DECLARE   balance DECIMAL(10, 2) ;
SET       balance = 0 ;
-- Legge saldo attuale
SET  balance = (
 SELECT SUM(giacenza)
FROM    SalMag00f
WHERE   articolo = pCodItem
  AND   (codMag  = pCodWhs OR pCodWhs = '  ')
)
;

--- Se la data ricevuta è maggiore o uguale alla data currente,
---  non serve elaborare i movimenti
IF pDateBal < CURRENT_DATE THEN
--- Legge movimenti fino alla data
FOR c1 CURSOR FOR
SELECT
 CASE WHEN segno  = '+' THEN quantita ELSE -1 * quantita END quantita
FROM    MovMag00f
WHERE   articolo = pCodItem
  AND   dataMovimento >= pDateBal
  AND   (codMag = pCodWhs OR pCodWhs = '  ')
ORDER BY articolo, dataMovimento DESC

  DO
    SET  balance = balance - quantita ;

END FOR ;

END IF;

IF balance < 0 THEN
   SET balance = 0;
END IF ;

RETURN balance;

END;

Dopo la dichiarazione delle variabili, la funzione reperisce il saldo attuale dal file SALMAG00F. Se viene passato un codice magazzino blank, vengono considerati tutti i magazzini.
Subito dopo, entra in azione la prima istruzione di controllo… con una IF verifichiamo la data passata come parametro e proseguiamo le elaborazioni solo se è inferiore alla data attuale (in caso contrario, il saldo è già disponibile).
I movimenti di magazzino vengono letti con un cursore inserito all’interno di un ciclo FOR, che ha la interessante caratteristica di poter funzionare direttamente come “pilota” di un loop di lettura con cursore. L’istruzione DO racchiude tutte le istruzioni da eseguire per ogni singola riga restituita dal cursore. Nel nostro caso, sottraiamo dal saldo la quantità del movimento, il cui segno è già stato opportunamente impostato nello statement di lettura del file MOVMAG00F.
Alla fine, come ulteriore controllo, il saldo viene impostato a zero nel caso fosse negativo (parlando di giacenza di magazzino questo non dovrebbe mai capitare, ma non si sa mai…)

Abbiamo visto come creare una function che restituisce un singolo valore. E se volessimo restituire una tabella ? Nulla di più facile… se le logiche non sono così complesse da dover costruire tabelle di lavoro “fisiche” (anche se temporanee), l’SQL fornisce l’istruzione di controllo PIPE, che permette di popolare una tabella “virtuale”, che verrà poi restituita in output.
Sulla base della funzione appena vista, possiamo crearne un’altra che restituisce una riga di tabella con i saldi della giacenza, degli acquisti e delle vendite per un articolo a una certa data. Vediamo quali sono le differenze con la UDF vista prima.
Innanzitutto cambia la dichiarazione dell’interfaccia: riceve gli stessi parametri, ma trattandosi di una UDTF restituirà una tabella, la cui struttura viene specificata nel parametro di ritorno:

CREATE OR REPLACE FUNCTION getSaldoT (      
pCodItem    CHAR(40),                       
pCodWhs     CHAR(02),                       
pDateBal    DATE                            
)                                           
RETURNS TABLE (                             
balanceWhs      DECIMAL(10, 2),             
balanceBuy      DECIMAL(10, 2),             
balanceSell     DECIMAL(10, 2)              
)    

Dopo avere dichiarato le variabili che conterranno i tre saldi (chiamiamole balanceW, balanceP, balanceS ), e dopo aver eseguito le stesse istruzioni della function precedente, con due ulteriori loop di lettura sui file dei dettagli degli ordini di acquisto e di vendita otterremo anche gli altri due saldi desiderati.
Al termine, scriviamo una riga nella nostra tabella “virtuale” con PIPE:

-- Accoda i risultati in tabella                   
PIPE   (balanceW, balanceP, balanceS ) ; 
 
RETURN ;                                           
 
END;  

In questo caso la RETURN non deve contenere parametri. Con questo esempio abbiamo restituito una singola riga di tabella, ma nulla vieta di utilizzare PIPE per accodare più record e fornire in output una tabella multiriga.
Per comodità non abbiamo indicato tutto il source, che si può scaricare da GitHub, insieme al sorgente della prima UDF (vedi link in calce)

Le istruzioni di controllo ampliano notevolmente il campo di utilizzo delle functions SQL; grazie ad esse in diversi casi si potrà eseguire la logica in una funzione senza necessariamente dover scrivere un programma o una procedura di un programma di servizio RPG.

Documentazione:
https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/db2/rbafzsqlcontstmts.htm
Repository GitHub di FAQ400:
https://github.com/MD2706GIT/FAQ400

About author

Senior Analyst/Developer e divulgatore in ambito IBM i. Già collaboratore dell'edizione italiana della rivista "System i News" ed autore di diverse pubblicazioni su tools e tecniche di sviluppo in ambiente IBM i.

Rispondi

%d blogger hanno fatto clic su Mi Piace per questo: