SQLUDTF




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)

 

1/ en PL SQL

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 ( 
PGMPARM VARCHAR(10),
LIBPARM VARCHAR(10) )
RETURNS TABLE (
PGM CHAR(10),
OBJRFF CHAR(11),
LIBREF CHAR(11),
OBJTYPE CHAR(10) )
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 = 'Liste en erreur' ;
SET CMD = 'QSYS/DSPPGMREF PGM(' CONCAT trim(LIBPARM) concat '/' CONCAT trim(pgmparm) CONCAT ') OUTPUT(*OUTFILE) OUTFILE(qtemp/dsppgm) ' ;
CALL QSYS . QCMDEXC ( CMD , 0000000300.00000 ) ;
RETURN SELECT WHPNAM, WHFNAM, WHLNAM, WHOTYP FROM qtemp.dsppgm ;
END ;

 

 

2/ Fonction EXTERNAL


(ici en RPG utilisant des API)

 

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 (
BIB CHAR(10),
FILE char(10) ,
MBR char(10) )
RETURNS TABLE (NOM CHAR(10) , TYPE CHAR(10), DATCRT DATE ,
DATCHG DATE, texte char(50)
)
EXTERNAL NAME 'AF4TOOL/LISTMBR'
PARAMETER STYLE DB2SQL
DISALLOW PARALLEL

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
dLISTMBR PI EXTPGM('LISTMBR') *paramètres définis dans CREATE FUNCTION (INPUT) d INbib 10 d INfile 10 d INmbr 10 *paramètres définis dans CREATE FUNCTION (OUTPUT) d OUTnom 10 d OUTtype 10 d OUTdatcrt D d OUTdatchg D d OUTtexte 50 * indicateurs valeur nulle (IN) d INbib_i 5I 0 d INfile_i 5I 0 d INmbr_i 5I 0 * indicateurs valeur nulle (OUT) d OUTnom_i 5I 0 d OUTtype_i 5I 0 d OUTdatcrt_i 5I 0 d OUTdatchg_i 5I 0 d OUTtexte_i 5I 0 * paramètres standards (STYLE SQL) DSQLSTATE 5 Dfonction_qual 517 VARYING Dfonction_name 128 VARYING Dmessage_diag 80 VARYING Dcall_type 10I 0

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;
OUTdatcrt = %date(membre.datcrt : *CYMD0);
on-error *all;
OUTdatcrt_i = -1;
Endmon;
Monitor;
OUTdatchg = %date(membre.datchg : *CYMD0);
on-error *all;
OUTdatchg_i = -1;
Endmon; OUTtexte = membre.texte; if i<nbpostes; //déplacement poste suivant ptrliste = ptrliste + lgposte; endif; else; // fin de liste SQLSTATE = '02000'; *INLR = *on; endif; return; else; // appel final, faire le ménage QUSDLTUS(usrspc: errcodeDS) ; *inlr = *on; endif;

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 :

 

©AF400