UDF User Defined Table Function
Présentation :
Les User Defined Table Function SQL (UDTF) sont des fonctions SQL définies par les développeurs.
La compilation d’une UDTF génère un objet de type *SRVPGM.
Elles permettent d’encapsuler un traitement RPGLE ou CLLE ou une requête SQL dans une fonction Table SQL qui sera alors très facilement utilisable par la suite, ce qui évitera d’avoir à réécrire la même fonctionnalité plusieurs fois.
Contrairement aux UDF qui ne retournent qu’une et une seule valeur, les UDTF peuvent retourner plusieurs valeurs ou plusieurs ensembles de valeurs, ce qui s’apparente à plusieurs enregistrements d’une table.
Les UDTF sont utilisables dans la clause FROM TABLE des requêtes SQL.
Elles peuvent être créées :
- en SQL (PL SQL)
- à partir d’une procédure de service (RPGLE/CLLE)
Elles peuvent avoir 0, 1 ou n paramètres en entrée.
La liste des UDTF et des UDF disponibles sur la machine est accessible avec la requête suivante :
SELECT * FROM QSYS2.SYSFUNCS;
Pour lister toutes les fonctions UDF et UDTF présentes dans la bibliothèque ma_bibliotheque :
SELECT * FROM QSYS2.SYSFUNCS WHERE SPECIFIC_SCHEMA = ‘ma_bibliotheque‘;
Pour en savoir plus : SQL Procedures, Triggers, and User-Defined Functions on IBM DB2 for i
Exemples de fonctions UDTF SQL écrite en PL/SQL:
Voici un exemple très simple de UDTF SQL avec 1 paramètres en entrée.
Cette fonction retourne la liste des membres sources modifiés au cours du mois précédent trouvé dans la bibliothèque passée en paramètre.
Elle renvoie pour chacun d’eux les champs suivants :
- La Bibliothèque
- Le fichier source
- Le membre modifié
- Le nombre d’enregistrement contenu dans le membre source
- La date de mise à jour du membre source
Code de la fonction :
CREATE OR REPLACE FUNCTION TABLE_SOURCES_MIS_A_JOUR(
— Description
— Retourne la table des sources de la bibliothèque passée en
— paramètre ayant été modifiés au cours du mois précédent
— Paramètres en Entrée
BIBLIOTHEQUE CHAR(10)
)
— Tables en sortie
RETURNS TABLE(
BIB CHAR(10),
FICHIER CHAR(10),
MEMBRE CHAR(10),
NB_ENREG INTEGER,
DATE_MAJ DATE
)
— Options
LANGUAGE SQL
DISALLOW PARALLEL
READS SQL DATA
NOT DETERMINISTIC — Réexécutée à chaque appel
SPECIFIC TB_SRCMAJ — nom court
SET OPTION
COMMIT = *None,
USRPRF = *User,
DYNUSRPRF = *User,
DATFMT = *iso,
DBGVIEW = *Source,
CLOSQLCSR = *Endactgrp
— Traitement
BEGIN
— Déclaration
— HANDLER de sortie
DECLARE EXIT HANDLER FOR SQLEXCEPTION SIGNAL SQLSTATE ‘99999’
SET MESSAGE_TEXT = ‘ERREUR TB_SRCMAJ’;
— Retourne la table résultat de la requête suivante
RETURN
WITH LISTE_FICHIERS_SOURCE AS(
SELECT SYSTEM_TABLE_SCHEMA, SYSTEM_TABLE_NAME, FILE_TYPE
FROM QSYS2/SYSFILES
WHERE SYSTEM_TABLE_SCHEMA = BIBLIOTHEQUE
AND FILE_TYPE = ‘SOURCE’
)
SELECT B.SYSTEM_TABLE_SCHEMA AS BIB,
B.SYSTEM_TABLE_NAME AS FICHIER,
B.SYSTEM_TABLE_MEMBER AS MEMBRE,
B.NUMBER_ROWS AS NB_ENREG,
DATE(B.LAST_CHANGE_TIMESTAMP) AS DATE_MAJ
FROM LISTE_FICHIERS_SOURCE A
INNER JOIN QSYS2/SYSPARTITIONSTAT B
ON B.SYSTEM_TABLE_SCHEMA = A.SYSTEM_TABLE_SCHEMA
AND B.SYSTEM_TABLE_NAME = A.SYSTEM_TABLE_NAME
AND DATE(B.LAST_CHANGE_TIMESTAMP) > CURDATE() – 1 months
ORDER BY DATE_MAJ DESC, FICHIER, MEMBRE;
— Fin Traitement
END;
Exemple d’utilisation :
SELECT * FROM TABLE(TABLE_SOURCES_MIS_A_JOUR(‘ma_bibliotheque‘));
Retourne la liste des membres source modifiés durant le mois précédent de la bibliothèque : ma_bibliotheque
