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 :

autant que de paramètres déclarés (ici 3)
+ autant que colonnes retour (ici 5)
des variables binaires (8) pour signaler la nullité(-1) ou pas (0) des paramètres
puis des paramètres standard SQL
- SQLSTATE
- nom qualifié de la fonction
- nom simple de la fonction
- une zone message (taille variable)
- call_type : type d'appel

 * 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é

une première fois pour réaliser un pseudo "Open", call_type est alors égal à -1
n fois avec call_type à 0, il doit retourner UNE ligne ou SQLSTATE à '02000' pour signaler la fin de fichier
une dernière fois représentant un pseudo "Close" avec call_type à 1

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 :

SQLUDTF

1/ en PL SQL

2/ Fonction EXTERNAL

©AF400