> Tech > UDTF SQL, première partie

UDTF SQL, première partie

Tech - Par iTPro.fr - Publié le 24 juin 2010
email

par Michael Sansoterra - Mis en ligne le 23/02/2005 - Publié en Avril 2004

Les UDTF (User-Defined Table Functions) offrent trop d'avantages pour qu'on les ignore

Pour le plus grand bien de la communauté iSeries, IBM améliore continuellement les UDF (User-Defined Functions) introduites dans SQL V4R4 pour iSeries. Ces UDF permettent aux programmeurs de construire des fonctions scalaires personnalisées ...La V5R2 leur donne le moyen de construire un autre genre de fonctions appelé UDTF (User- Defined Table Function). Contrairement à  une UDF scalaire, qui accepte des paramètres de zéro ou plus et renvoie une valeur unique, l'UDTF peut accepter des paramètres zéro ou plus et renvoyer une table de résultats à  part entière. Comme une UDTF renvoie une table de résultats, les programmeurs peuvent l'utiliser comme une table normale ou la visualiser dans une instruction Select, dans la clause From d'un subselect, ou dans une déclaration de curseur. (Une UDTF ne peut pas être référencée comme cible d'une instruction Update, Insert ou Delete.)

Vous pouvez utiliser soit SPL (SQL procedural language), soit un langage évolué, comme ILE RPG, pour mettre en oeuvre une UDTF. Les UDTF écrites en SPL sont appelées « UDTF SQL » et celles qui sont écrites dans un langage évolué (HLL) sont appelées « UDTF externes ». En général, les UDTF SQL sont utiles pour extraire des données d'une base de données DB2, tandis que elles conviennent bien pour extraire des données d'autres sources, même si des UDTF externes peuvent aussi extraire des données de DB2. Pour l'instant, nous n'examinerons que les UDTF SQL. Rappelons qu'à  partir de la V5R2, le kit de développement SQL 5722ST1 n'est plus nécessaire pour écrire du code SPL.
Comme avec les UDTF scalaires SQL, l'instruction Create Function mène une double action : elle compile le code de l'UDTF et elle enregistre le nom, les paramètres et la valeur de renvoi de l'UDTF avec la base de données. La figure 1 montre une UDTF simple qui extrait toutes les commandes d'un client des tables Orders et OrdersArchive.
Pour invoquer une UDTF dans une instruction SELECT, spécifiez le nom de la fonction et ses paramètres de la manière suivante :

Select *
From Table(GetCustomerOrders
('ACME_HDW'))
As CustOrders
Order By OrderValue
Si l'interrogation concerne une UDTF, vous devez placer le nom UDTF entre parenthèses dans le mot-clé Table. Vous devez aussi indiquer un nom de corrélation en utilisant le motclé As pour donner à  l'UDTF un nom unique dans l'instruction.
La figure 1 montre comment, pour des UDTF SQL, vous pouvez diviser l'instruction Create Function en deux parties : la définition de la fonction et l'implémentation de la fonction. La définition de la fonction va des mots-clés Create Function jusqu'à  la fin de la phrase Set Option. L'implémentation de la fonction est spécifiée entre les mots-clés Begin et End.
Pour démarrer la partie définition, l'UDTF reçoit le nom GetCustomer- Orders, qui est référencé dans la clause From d'une instruction Select. Après le nom, spécifier les éventuels paramètres d'entrée dont l'UDTF a besoin pour extraire les données désirées. Dans cet exemple, le CustomerID est déclaré comme un paramètre d'entrée appelé parmCustomerID.
Le mot-clé Returns Table distingue une UDTF d'une UDF scalaire parce qu'une UDTF peut renvoyer des colonnes multiples ainsi que des lignes. Vous devez spécifier les colonnes de la table de résultats en utilisant une syntaxe identique à  celle qui permet de définir des colonnes sur l'instruction Create Table. Une instruction SQL qui référence l'UDTF utilise les noms des colonnes de la table de résultats de la même manière que les noms de colonnes provenant d'une table ou d'une vue normale.
Le fait de spécifier Language SQL signifie que l'UDTF est écrite en SPL. Comme les UDTF ne peuvent pas s'exécuter en parallèle, la clause Disallow Parallel est toujours requise. Utilisez la clause Set Option pour définir une variété de paramètres incluant le format date, le format heure, le niveau de contrôle de commitment et l'autorité adoptée. Pour obtenir plus d'informations sur les options disponibles, voir la commande Set Option dans le guide IBM DB2 UDB for iSeries SQL Reference à  http://publib.boulder. ibm.com/iseries/v5r2/ic2924/info/db2/rbafzmst02.htm (ou la même URL se terminant par rbafsmst.pdf pour

Un avantage des UDTF est de simplifier
les instructions Select en plaçant des
jointures et des unions complexes
dans l’UDTF. C’est comparable à  l’utilisation
de vues, mais, en plus, une
UDTF peut accepter des paramètres
à  l’exécution. Autres avantages : la
réutilisabilité et un seul point de maintenance.
Vous pouvez désormais remplacer
par une UDTF des instructions
Select qui sont réutilisées dans de
multiples requêtes. De plus, si vous devez
modifier cette instruction Select
commune dans l’UDTF, il suffit de la
modifier à  un endroit parce que cette modification se répercutera sur toutes
les instructions Select utilisant l’UDTF.
Vous estimez peut-être, à  juste
titre, que des procédures stockées (SP,
stored procedures) qui renvoient un
jeu de résultats offrent les mêmes
avantages. Cependant, vous ne pouvez
pas utiliser des SP dans des instructions
SQL (autres qu’un Call), ce qui limite
l’utilisation des jeux de résultats
SP principalement aux clients ODBC et
JDBC (bien que CLI et DRDA supportent
aussi des jeux de résultats SP).

Téléchargez gratuitement cette ressource

Comment cerner la maturité digitale de votre entreprise ?

Comment cerner la maturité digitale de votre entreprise ?

Conçu pour les directions IT et Métiers, ce guide vous permettra d'évaluer précisément vos processus de communication client, d'identifier vos lacunes et points d'inflexion pour établir un plan d’actions capable de soutenir durablement votre évolution. Bénéficiez maintenant d'une feuille de route complète.

Tech - Par iTPro.fr - Publié le 24 juin 2010