Last Updated on 1 Dicembre 2019 by Roberto De Pedrini
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
Introduzione Spesso, nelle nostre applicazioni, abbiamo la necessità di lavorare con le stringhe di testo e l'SQL del DB2 può Read more
Database DB2 e SQL ... forse lo strumento più potente e completo che abbiamo sulla piattaforma IBM i: ecco una Read more
Arriva direttamente con l'uovo di Pasqua questo annuncio IBM per le novità della versione IBM i 7.4, versione iNext secondo Read more
Introduzione "Generated Always Column": sono colonne, campi, di una tabella il cui contenuto è controllato direttamente dal sistema ... e Read more
