SQL propose de créer vos propres fonctions -> UDF ou User Defined Fonction.
ces fonctions ne retournent qu'une valeur sous la forme date2 = findemois(date1)
Pour retourner plusieurs valeurs vous pouvez écrire des fonctions tables, qui retourne des lignes (donc une série de colonnes) comme une table (ou un fichier)
cela consiste á placer un SELECT sur la clause RETURN.
IBM fourni un exemple avec la fonction USERS() dont voici le source
CREATE FUNCTION SYSIBM.USERS ( ) RETURNS TABLE ( ODOBNM CHAR(10), ODOBTX CHAR(50) ) LANGUAGE SQL READS SQL DATA BEGIN DECLARE CMD CHAR ( 300 ) DEFAULT ' ' ; DECLARE WARN CONDITION FOR '01HII' ; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SIGNAL WARN SET MESSAGE_TEXT = 'SOME USERS NOT AVAILABLE' ; SET CMD = 'QSYS/DSPOBJD qsys/*ALL *USRPRF OUTPUT(*OUTFILE) ' CONCAT ' OUTFILE(qtemp/q_users) ' ; CALL QSYS . QCMDEXC ( CMD , 0000000300.00000 ) ; RETURN SELECT ODOBNM , ODOBTX FROM QTEMP.Q_USERS ; END ; |
Utilisation : SELECT * from TABLE( udtf() ) as alias :
Mais vous pouvez utiliser TOUTE la puissance de la syntaxe SQL
Dans ce cadre nous vous proposons un exemple basé sur DSPPGMREF
CREATE FUNCTION FORMATION0.PGMREF ( |
Dans cet exemple, nous utiliserons l'API QUSLMBR qui permet d'obtenir la liste des membres dans un User Space
-> la fonction est définie de la manière suivante
CREATE FUNCTION AF4TOOL/LISTMBR ( |
Paramètres en entrée :
* Interface de procédure sur le pgm en cours = paramètres en entrée * on peut se passer du prototype uniquement depuis la V7 |
le pgm est appellé
Autres déclarations (variables + prototypes des Api)
* prototype pour API création/destruction User Space dQUSCRTUS PR EXTPGM('QUSCRTUS') d space 20 CONST d USattribut 50 CONST d UStaille 10I 0 CONST d UScontenu 1 CONST d USdroits 10 CONST d UStexte 50 CONST d USreplace 10 CONST d USerrcode likeds(errcodeDS) dQUSDLTUS PR EXTPGM('QUSDLTUS') d space 20 CONST d USerrcode likeds(errcodeDS) * prototype pour API qui retrouve pointeur de début dQUSPTRUS PR EXTPGM('QUSPTRUS') d space 20 CONST d ptr * Dusrspc s 20 inz('LISTMBR QTEMP') * API litse des membres d'un fichier dQUSLMBR PR EXTPGM('QUSLMBR') d space 20 CONST d format 8 CONST d ficlib 20 CONST d membre 10 CONST d OVRDBF N CONST * va contenir l'adresse de début du User Space Dpointeur s * DI s 10i 0 * l'entête Dptrinfos s * DRTVINF ds based(ptrinfos) D offset 10i 0 D taille 10i 0 D nbpostes 10i 0 D lgposte 10i 0 * la liste dptrliste s * Dmembre ds based(ptrliste) qualified d nom 10 d type 10 d DatCrt 7 d HeurCrt 6 d DatChg 7 d HeurChg 6 d texte 50 DerrcodeDS ds qualified d tailleDS 10i 0 inz(%size(errcodeDS)) d taille 10i 0 d msgID 7 d reserve 1 d errdta 50 |
en free form
**free dcl-pi *n; //parametres dans CREATE FUNCTION (INPUT) INbib char(10); inFile char(10); INmbr char(10); //parametres dans CREATE FUNCTION (OUTPUT) OUTnom CHAR(10); OUTtype CHAR(10); OUTdatcrt Date; OUTdatchg Date; OUTtext char(50); // indicateurs valeur nulle (IN) INbib_i int(5); INfile_i int(5); INmbr_i int(5); // indicateurs valeur nulle (OUT) OUTnom_i int(5); OUTtype_i int(5); OUTdatcrt_i int(5); OUTdatchg_i int(5); OUTtext_i int(5); // parameters STYLE SQL SQLSTATE CHAR(5); function_qual varchar(571); function_name varchar(128); message_diag_msg varchar(80); call_type int(10); end-pi; // Create USer Space dcl-s USRSPC CHAR(20) inz('LISTMBR QTEMP'); dcl-pr QUSCRTUS EXTPGM('QUSCRTUS'); space CHAR(20) CONST; USattribut CHAR(50) CONST; UStaille INt(10) CONST; UScontenu CHAR(1) CONST; USdroit CHAR(10) CONST; UStexte CHAR(50) CONST; USreplace CHAR(10) CONST; USerrcode likeds(errcodeDS); end-pr; // Delete USer Space dcl-pr QUSDLTUS EXTPGM('QUSDLTUS'); space CHAR(20) CONST; USerrcode likeds(errcodeDS); end-pr; // Retreive Pointer dcl-pr QUSPTRUS EXTPGM('QUSPTRUS'); space CHAR(20) CONST; ptr Pointer; END-PR; // API liste des membres dcl-pr QUSLMBR EXTPGM('QUSLMBR'); space CHAR(20) CONST; Format CHAR(8) CONST; ficlib CHAR(20) CONST; Membre CHAR(10) CONST; OVRDBF ind CONST; end-pr; // autres variables Dcl-s pointer Pointer; dcl-s i Int(10); // entete dcl-s ptrinfos Pointer; dcl-ds RTVINF based(ptrinfos); offset int(10); taille int(10); nbpostes int(10); lgposte int(10); end-ds; // liste dcl-s ptrlist Pointer; Dcl-ds member based(ptrlist) qualified; nom CHAR(10); type CHAR(10); DatCrt CHAR(7); heurCrt CHAR(6); DatChg CHAR(7); heurChg CHAR(6); texte CHAR(50); End-DS; Dcl-ds errcodeDS qualified; tailelDS Int(10) inz(%size(errcodeDS)); taille Int(10); msgID CHAr(7); reserved CHAR(1); errdta CHar(50); End-ds |
le code
if call_type < 0 ; // premier appel, constitution de la liste SQLSTATE = '00000' ; QUSCRTUS(usrspc: *Blanks: 1024: x'00': '*USE': 'Liste des membres': '*YES' : errcodeDS); QUSLMBR(usrspc: 'MBRL0200': INfile + INbib : INmbr: *ON); // recpération pointeur de début QUSPTRUS(usrspc : pointeur); ptrinfos = pointeur + 124; // position sur 1er poste ptrliste = pointeur + offset; return; elseif call_type = 0 ; i+=1; if i<=nbpostes; // appel "normal", retour d'un membre OUTnom = membre.nom; OUTtype = membre.type; Monitor; |
Utilisation :
en SQL toute constante est réputée de type VARCHAR. La fonction ayant été créée avec des paramètres CHAR il faut caster
(ce n'est plus vrai en 7.2)
résultat
là aussi la fonction aurait pu être utilisée de la manière suivante :