UDF User Defined Function
Présentation :
Les User Defined Function SQL (UDF) sont des fonctions SQL définies par les développeurs.
La compilation d’une UDF 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 SQL qui sera alors très facilement utilisable par la suite, ce qui évitera d’avoir à réécrire la même fonctionnalité plusieurs fois.
Ce sont des fonctions scalaires : elles ne retournent donc qu’un et un seul paramètres en sortie, et sont utilisables dans les requêtes SQL (dans les SELECT ou dans les WHERE, HAVING…).
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 UDF disponibles sur la machine est accessible avec la requête suivante :
SELECT * FROM QSYS2.SYSFUNCS;
Pour lister toutes les fonctions UDF 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 UDF SQL écrites en PL/SQL:
Voici un exemple très simple de UDF SQL avec 0 paramètres en entrée.
Cette fonction retourne l’ID du job en cours sous la forme d’une chaine de 28 caractères : JOB_NUMBER/USER/JOB_NAME
Code de la fonction :
CREATE OR REPLACE FUNCTION GET_JOB_ID
(
— Description
— Cette fonction SQL retourne l’ID du job en cours
)
— Déclaration du Paramètre en sortie
RETURNS
VARCHAR(28) — id job : JOB_NUMBER/USER/JOB_NAME
— Options
LANGUAGE SQL — UDF de type SQL
READS SQL DATA — UDF sans modification de données
DETERMINISTIC — Non Réexécutée à chaque appel
SPECIFIC GET_JOB — Définition du nom court de la fonction
SET OPTION
COMMIT = *None,
USRPRF = *User,
DYNUSRPRF = *User,
DBGVIEW = *Source
— Début du Traitement
BEGIN
— Déclarations des Variables SQL
DECLARE SQLCODE INTEGER DEFAULT 0; — obligatoire
DECLARE SQLSTATE CHAR(5) DEFAULT ‘00000’; — Obligatoire
RETURN
(SELECT CAST(JOB_NAME AS VARCHAR(28))
FROM TABLE(QSYS2.GET_JOB_INFO(JOB_NAME)));
— Fin du Traitement
END ;
LABEL ON SPECIFIC ROUTINE GET_JOB IS ‘UDF Retourne JOB ID’;
Exemple d’utilisation :
SELECT GET_JOB_ID() FROM SYSIBM.SYSDUMMY1;
Retourne l’ID du job en cours => 205127/QUSER/QZDASSINIT
Voici un second exemple de UDF SQL avec 3 paramètres en entrée et un Handler permettant de gérer les erreurs.
Cette fonction teste si l’objet passé en paramètre a déjà été utilisé ou non.
Elle reçoit en entrée les 3 paramètres suivant :
- La Bibliothèque de l’objet à tester sur 10 caractères alphanumériques.
- Le nom de l’objet à tester sur 10 caractères numériques.
- Le type de l’objet à tester sur 8 caractères numériques.
- ‘O‘ si l’objet à déjà été utilisé.
- ‘N‘ si l’objet n’a jamais été utilisé.
- NULL si l’objet n’a pas été trouvé ou si les paramètres en entrée sont invalides.
Code de la fonction :
CREATE OR REPLACE FUNCTION IS_OBJECT_USED
(
— Description
— Cette fonction SQL contrôle si un objet a déjà été utilisé ou non
— Elle retourne ‘O’ si l’objet a déjà été utilisé
— ‘N’ si l’objet n’a jamais été utilisé
— NULL si les paramètres en entrée sont incorrects
— Déclarations des Paramètres en entrée
BIBLIOTHEQUE CHAR(10),
OBJET CHAR(10),
TYPE CHAR(8)
)
— Déclaration du Paramètre en sortie
RETURNS CHAR(1)
— Options
LANGUAGE SQL
NOT DETERMINISTIC — Réexécutée à chaque appel
READS SQL DATA — UDF sans modification de données
SPECIFIC IS_USED — Définition du nom court de la fonction
SET OPTION
COMMIT = *None,
USRPRF = *User,
DYNUSRPRF = *User,
DBGVIEW = *Source,
— Début du Traitement
BEGIN
— Déclarations des Variables
DECLARE SQLCODE INTEGER DEFAULT 0; — Obligatoire
DECLARE SQLSTATE CHAR(5) DEFAULT ‘00000’; — Obligatoire
DECLARE RESULTAT CHAR(1) DEFAULT NULL; — Zone de travail
— Déclarations d’un Handler SQL (Continue si erreur interceptée)
DECLARE ERREUR_SQL CONDITION FOR ‘42616’; — SQLSTAT 42616
DECLARE CONTINUE HANDLER FOR ERREUR_SQL SET RESULTAT = NULL;
— Traitement
SET RESULTAT = (
SELECT CASE WHEN DAYS_USED_COUNT > 0 THEN ‘O‘
WHEN DAYS_USED_COUNT = 0 THEN ‘N‘
ELSE NULL
END
FROM TABLE(QSYS2.OBJECT_STATISTICS(BIBLIOTHEQUE, TYPE, OBJET)));
RETURN RESULTAT;
END;
LABEL ON SPECIFIC ROUTINE IS_USED IS ‘UDF Test si objet utilisé’;
Exemple d’utilisation :
SELECT IS_OBJECT_USED(‘ma_bibliotheque‘,‘mon_objet‘, ‘type_de_mon_objet‘) FROM SYSIBM.SYSDUMMY1;
Retourne ‘O’, ‘N’ ou NULL en fonction des paramètres transmis en entrée.
SELECT OBJLIB AS BIBLIOTHEQUE,
OBJNAME AS OBJET,
OBJTYPE AS TYPE,
IS_OBJECT_USED(OBJLIB, OBJNAME, OBJTYPE) AS UTILISE
FROM TABLE(QSYS2.OBJECT.STATISTICS(‘ma_bibliotheque‘, ‘*ALL’));
Retourne la liste des objets de la bibliothèque ma_bibliotheque avec la colonne UTILISE correspondant au résultat de la fonction IS_OBJECT_USED :
