Pause-Café Volubis

pause-café

rendez-vous technique
Pause-Café est une réunion technique
destinée aux informaticiens sur plateforme IBM i.
Elle a lieu 3 à 4 fois par an : en Bretagne et sur internet.

Pause-café #19

Novembre 1999






Rappels de quelques nouveautés SQL VRAIMENT importantes :


V4R20




 - Expressions conditionnelles


   une expression en SQL représente un traitement sur une/plusieurs zones
   (concaténation/extraction, calcul, ...)


   il s'agit ici, de pouvoir donner des conditions à ces traitements :


...................................................................
:  Select cours, texte, case dispo                                :
:                            when 1 then 'lecture'                :
:                            when 2 then 'Impression'             :
:                            when 3 then 'Les deux'               :
:                       end AS Disponible                         :
:       from AF4MBRP1                                             :
:.................................................................:


                          

...........................................................................
:  Select noart, qtecde, puart, case                                      :
:                                when codepays <> 'F' then puart          :
:                                when dep = 97        then puart *1,09    :
:                                else                     puart *1,206    :
:                               end as TTC                                :
:       from cdecli                                                       :
:.........................................................................:




- CAST = fonction de changement de type


         CAST(expression as type(lg))




  select CAST(codchar as DEC(6, 0) )


 Attention, CAST de numérique en Alpha élimine les zéros de gauche,


            DIGITS() les conserve .




                          



 - Sous sélection admise dans l'ordre UPDATE


  la notion de sous-sélection permettait de mettre un ordre SELECT dans la
     clause WHERE d'un ordre SQL.


  SUPPRIMER les clients sans commande :


            Delete from client C where not exists
               ( select * from commande where nocli = C.nocli )


            supprimer les commandes quand la clause SELECT ne trouve
             pas de ligne (NOT EXISTS)
             ayant le n° de client lu dans le fichier client (C.nocli)




  Cette synatxe est maintenant (V4R3) acceptée dans la clause SET de UPDATE 


  UPDATE command C set priha = (select pritarif from article
                                       WHERE codart = C.codart )
         WHERE  priha = 0


                          



 mettre à jour le fichier command, mettre dans priha la valeur retournée
   par la clause select qui dit


      (je vais chercher le pritarif dans le fichier article, 
          de la ligne ayant le code article lu dans le fichier commande )






  UPDATE recap R set SALAIRAN = (select sum(salair + primes)
                                  from personp1
                                  where matricule = R.matricule)


 mettre à jour le fichier recap, mettre dans salairAN la valeur retournée
   par la clause select qui dit


      (je vais chercher la somme du calcul (salair * primes)
          dans le fichier du personnel 
          des lignes ayant le matricule lu dans le fichier RECAP )






                          



 Nouveautés SQL en V4R40


 1/ les expressions sont maintenant admises sur les clauses


     GROUP BY


     ORDER BY


 à condition qu'elles soient indiquées aussi (et à l'identique) sur la
    clause SELECT.


 soit bdist dans le fichier clients contenant dep+bureau (44000 par ex.)




 SELECT substr(digits(bdist), 1, 2), COUNT(distinct NOCLI)


         from clients


        group by substr(digits(bdsit), 1, 2)


 donne le nombre de clients par département.
 



 SELECT bdist, COUNT(distinct NOCLI)


         from clients


        group by substr(digits(bdsit), 1, 2)


est invalide, l'expression n'étant pas présente sur le SELECT.




ORDER BY offre rigoureusement les mêmes possibilités.


 tout en conservant la possibilité d'indiquer un N° de colonne
      (N° dans le select bien sur)


 SELECT codcli, left(raisoc, 20)
         from clients
        GROUP BY 2


 2/ une nouvelle fonction LOWER ou LCASE


        transforme en minuscules (le contraire de UCASE donc)
 



3/ tables dérivées :


  on peut indiquer un ordre SELECT dans la clause from d'un ordre SELECT


     ou bien déclarer juste avant le SELECT une "vue temporaire" par WITH.




   cela permet d'éviter des créations de vues dans les cas complexes.




 deux écritures sont donc possibles
 ----------------------------------


 WITH correlation AS ( select ... from)


      SELECT ... from correlation


ou


 SELECT ... FROM   (select ... from ...)


                          



 exemple , soit un fichier des cours, chaque cours est enregistré sous un
             module de cours.


 je veux le nombre de cours du module qui en a le plus.
 ....................................................................
 : with  temp as                                                    :
 :  (select count(*) as nbr from Fmodules group by codmodul)        :
 :                                                                  :
 : select max(nbr)                                                  :
 :   from temp                                                      :
 :..................................................................:


est l'équivalent de :


 create view v1 as select count(*) as nbr .... from ...


puis


 select max(nbr) from v1


cela permet d'imbriquer des fonctions,  MAX(COUNT( ..)) étant invalide.
 



 deuxième écriture directement dans la clause FROM


 soit le fichier cours (vu plus haut),
  le fichier des modules (donnant un texte explicatif à chaque module)
  un fichier des exemples, permettant d'attacher x exemples à un cours.
                                                 (x pouvant être 0)
 je veux obtenir pour chaque module


................................................
: nom | texte | nbr de cours | nbr d'exemples  :
:..............................................:




Nous allons donc écrire


     un SELECT qui donne le nombre de cours par module   ( C )


     un SEELCT qui donne le nombre d'exemples par module ( E )


et réaliser une jointure entre le fichier des modules, C, et E.


                          



La jointure module -> C est interne (on doit avoir au moins un cours).


La jointure C -> E est externe, il peut ne pas y avoir d'exemples.


 select m.codmodul, texte, nbcours, nbexemples
  from Fmodules  m 


       join


 (select codmodul, count(*) as nbcours
         from Fcours
         group by codmodul) as C 
 on m.codmodul = c.codmodul


  left join


 (select codmodul, count(*) as nbexemples
         from Fexemples
         group by codmodul) as E 
 on C.codmodul = e.codmodul


                          

                             Affichage des données 
                                              Largeur des données  . . :  79
 Première ligne à afficher . .            Première colonne à afficher  .
 ....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+
 MODULE      TEXTE                                    NBCOURS      NBEXEMPLE


 £CURSUSTCP  Cursus TCP/IP et INTRANET                     13            14
 £CURSUS00   AF400 : présentation du produit                3              -
 £CURSUS01   Initiation à l'AS/400 & OS/400                19             5
 £CURSUS02   Base de données integrée et requête           21            13
 £CURSUS03   Développement en RPG (RPG,PRTF,DSPF           30            28
 £CURSUS04   Programmation système (CL et principes)       20            15
 £CURSUS05   Télécommunications sur AS/400 .               20             6
 £CURSUS06   Programmation COBOL/400                       28            25
 £CURSUS220  OS/400 - V2R20                                18            42
 £CURSUS230  OS/400 - V2R30                                32            66
 £CURSUS3PR  Préparation à la V3R10 (études/système)       18            15
 £CURSUS3PX  Préparation V3R10 (orienté exploitation)       9            10
 £CURSUS310  OS/400 - V3R10                                58            47
 £CURSUS320  OS/400 - V3R20                                19            20
 £CURSUS370  OS/400 - V3R60 / V3R70                        24            28
                                                                 A suivre...
 



 Vous pouvez maintenant manipuler vos variables HOST (dans un pgm RPG ...)


  par un ordre SQL :




  SET :variable = expression


  ou bien


  VALUE expression INTO :variable




 par exemple


         C/EXEC SQL
         C+  SET :Result = LEFT(:NOM, 10) CONCAT :PRENOM
         C/END-EXEC


         C/EXEC SQL
         C+  VALUE POSSTR(:RAISOC , 'Assurance') INTO :debut
         C/END-EXEC
 



 de nouveaux types de données apparaissent en V4R40 (ptf SF99104)


    les types de données LARGES ou LOB,(jusqu'à 15 Mo).


    les DATALINK (ou URL)






1/ les LOB (Large Object)




   ils sont de trois sortes


     + CLOB Chararcter Large Object, supportent la notion de CCSID


     + DBCLOB Double Byte CLOB, idem CLOB mais en DBCS


     + BLOB Binary Large Object, binaires, donc prévus pour les images
                                                            la video, etc...


     ex: CREATE TABLE VOITURE (image as BLOB 2M)
 



 > Une colonne de type LOB peut-être manipulée par


    -son contenu (si votre langage supporte des variables aussi grandes)


       vous devez déclarer en RPG par:


     DMYBLOB           S                   SQLTYPE(BLOB:500)


       ce qui génère :


     D MYBLOB          DS
     D MYBLOB_LEN                    10U 0
     D MYBLOB_DATA                  500A




L'exemple suivant montre comment utiliser les BLOB en JAVA:


     Blob   img = resultSet.getBlob ;
     long   lg  = blob.length ();
     byte[] R   = img.getBytes (0, (int) lg);


                          



    -vous pouvez utiliser un identifiant appelé "LOCATOR", qui permet :


                                       + de ne pas transférer les data
                                         dans le programme (sur le PC ...)


                                       + de manipuler quand même la colonne.


       vous devez déclarer en RPG par:


     D MYCLOB          S                   SQLTYPE(CLOB_LOCATOR)


       ce qui génère :


     D MYCLOB          S             10U 0


       vous pouvez utiliser l'identifiant en lieu et place de la colonne
         par les nouvelles instructions SET et VALUE.


    C/EXEC SQL
    C+   VALUE POSSTR(:MYCLOB, 'formation') INTO :debut
    C/END-EXEC
 



 -vous pouvez utiliser un fichier appelé "FILE LOCATOR", qui permet :


                                       + de copier le contenu d'un LOB
                                          dans un fichier IFS


                                       + de renseigner un LOB à partir
                                          du contenu d'un fichier IFS.


       vous devez déclarer en RPG par:


     D MYFILE          S                   SQLTYPE(CLOB_FILE)


       ce qui génère :


     D MYFILE          DS
     D  MYFILE_NL                    10U 0        [lg du nom]
     D  MYFILE_DL                    10U 0        [lg des Data]
     D  MYFILE_FO                    10U 0        [file permission]
      * SQL génère SQFRD (2), SQFCRT (8), SQFOVR(16) et SQFAPP(32)
     D  MYFILE_NAME                 255A          [nom du fichier]


                          



Exemple en COBOL


 en Working Storage Section :


     01 rapport USAGE IS SQL   TYPE IS CLOB-FILE


 puis en traitement


       move "RAPPORT.TXT"      to rapport-NAME.
       move  11                to rapport-LENGTH.
       move SQL-FILE-OVERWRITE to rapport-FILE-OPTIONS.


        EXEC SQL SELECT rapport INTO :rapport
                        FROM  ...
                        WHERE ...
         END-EXEC.


 ce traitement place copie du contenu de la colonne "rapport" dans le
     fichier "RAPPORT.TXT" qui est un fichier IFS.
 un ordre INSERT aurait renseigné la colonne par copie du fichier.


                          



 Autre Grande nouveauté : les types de colonne DATA LINK


  il s'agit de colonnes dont le contenu référence un fichier externe.


     a/ le nom du fichier est donné sous forme d'URL


     b/ le fichier reste à l'exterieur de la base de données
           (utilisable par votre serveur WEB, par exemple)


     c/ le serveur Base de données peut vous fournir un contrôle
           de type:
                     - je vérifie que le fichier existe lors de l'insertion


                     - je vérifie la présence du fichier tant qu'il est
                           référencé dans la base.


  vous devez lancer un serveur TCP/IP appelé DLFM
       (DATA LINK FILE MANAGER), pour gérer ces contrôles temps réel.


  la table doit être journalisée.


                          



Exemple


CREATE TABLE AF400/AF4URLP1 (AF4MDL CHAR ( 10) NOT NULL, AF4MBR
CHAR ( 10) NOT NULL, AF4URL DATALINK
                     LINKTYPE URL
                     FILE LINK CONTROL                 (1)
                     INTEGRITY ALL READ PERMISSION FS  (2)
                               WRITE PERMISSION FS
                               RECOVERY NO)            (3)




(1) : le contrôle d'exitence doit être fait.
      (sinon vous avez un contrôle de syntaxe uniquement, lors de l'INSERT)


(2) les droits sont ceux du fichier dans IFS


      (les droits de la colonne pour DB2 pourraient primer)


(3) restauration de l'intégrité lors de restauration de la table (oui/non)




                          

mise en place :
               PGM


               CRTJRNRCV  JRNRCV(AF400/AF4RCV0001) THRESHOLD(5000) +
                            TEXT('RECPTEURURNAL AF400')


               CRTJRN     JRN(AF400/AF4JRN) JRNRCV(AF400/AF4RCV0001) +
                            MNGRCV(*SYSTEM) DLTRCV(*YES) +
                            TEXT('JOURNAL POUR AF400')


               STRJRNPF   FILE(AF400/AF4URLP1) JRN(AF400/AF4JRN) +
                            IMAGES(*AFTER) OMTJRNE(*OPNCLO)




               WRKRDBDIRE  /* pour vérifier le nom RDB */


               STRTCPSVR  SERVER(*DLFM)


               ADDPFXDLFM PREFIX(('/AF4DIR'))


               ADDHDBDLFM HOSTDBLIB((AF400)) HOSTDB(S44R7480)
               ENDPGM
 



insertion de lignes


il faut utiliser la fonction DLVALUE('http://serveur/repertoire/fichier')


INSERT INTO AF400/AF4URLP1
 SELECT AF4MDL, AF4MBR,
  DLVALUE('HTTP://S44R7480/AF4DIR/'CONCAT TRIM(SRCLIB)
   CONCAT '/' CONCAT TRIM(SRCFIL) CONCAT '/' CONCAT
   TRIM(SUBSTR(SRCMBR, 2, 9)) CONCAT '.HTM', URL, 'commentaire')
 FROM AF400/AF4MBRP1
 WHERE AF4TYP = '*TXT' AND AF4MBR LIKE '£%'


.........................................................................
: dans cet exemple les noms des cours commencent par une livre (£)      :
:   et pas les pages HTML.                                              :
:.......................................................................:


Autres fonctions :
DLLINK(nom)   -> retourne l'URL d'un champ DATALINK
DLCOMMENT(nom)-> retourne le commentaire
DTYPE(nom)    -> retourne le type (seul URL est admis)
 



SQL , V4R40 admet maintenant


 les fonctions définies par l'utilisateur        [UDF]


 les types de données définies par l'utilisateur [UDT]


une fonction est un programme ou une procédure dans un programme de service
 enregistré(e) dans les catalogues SQL par CREATE FUNCTION.


 par exemple :


    CREATE FUNCTION AF4TEST/DT8_CHAR8 (DEC(8 , 0) )
                              RETURNS  CHAR(8)
           EXTERNAL NAME 'AF4TEST/DT_SQL(DT8_CHAR8)'    (1)
           PARAMETER STYLE GENERAL                      (2)
           RETURNS NULL ON NULL INPUT  ;                (3)


(1) fait référence à DT8_CHAR8 dans DT_SQL
(2) le passage de paramètres se fait sans gestion de la val. nulle
(3) la fonction retourne nulle si un des argument est nul
     (il n'y aura pas d'appel)
 



 voici le source RPG de la procédure associée à cette fonction :


 H nomain
  * prototype  =========================================================
 D dt8_char8       pr             8
 D                                8  0
  * fonction  ==========================================================
 Pdt8_char8        b                   export
 d                 pi             8
 d dt8_recue                      8  0


 d date            s               D   datfmt(*dmy/)
 c     *iso          test(d e)               dt8_recue


 c                   if        %error
 c                   return    '01/01/40'
 c                   else
 c     *iso          move      dt6_recue     date
 c                   return    %char(date)
 c                   endif
 p                 e
 



la création se réalise par :


   CRTRPGMOD MODULE(DT8_CHAR8)
 et
   CRTSRVPGM SRVPGM(DT_SQL) MODULE(DT8_CHAR8) EXPORT(*ALL)
___________________________________________________________________________


 cela permet maintenant de passer des ordres SQL comme :


     [soit un fichier client avec DATCRT DEC(8 , 0) ]


 SELECT * FROM  CLIENTS WHERE DT8_CHAR8(DATCRT) <> '01/01/40'


 SELECT * FROM CLIENTS WHERE DATE( DT8_CHAR8(DATCRT) ) = current date


  ..................................................................
  :                                                                :
  : la fonction SQL date attend une chaîne de caractères au format :
  :   du JOB, soit en interactif 'JJ/MM/AA'.                       :
  :                                                                :
  :................................................................:
 



 Deuxième exemple retournant une date au format caractère en ISO


d dt8_char10      pr            10
d                                8  0


Pdt8_char10       b                   export
d                 pi            10
d dt8_recue                      8  0


d date            s               D   datfmt(*iso)
c     *iso          test(d e)               dt8_recue


c                   if        %error
c                   return    '01/01/40'
c                   else
c     *iso          move      dt8_recue     date
c                   return    %char(date)
c                   endif
c
P                 e


                          



déclaration :




 CREATE FUNCTION AF4TEST/DT8_CHAR10 (DEC(8 , 0) )
                           RETURNS  CHAR(10)
        EXTERNAL NAME 'AF4TEST/DT_SQL(DT8_CHAR10)'
        PARAMETER STYLE GENERAL
        RETURNS NULL ON NULL INPUT  ;


 lors de la déclaration de la fonction vous pouvez convertir (caster)
   un/les argument(s) ou la valeur retour


 CREATE FUNCTION AF4TEST/DT8_DATE (DEC(8 , 0) )
                           RETURNS  DATE CAST FROM CHAR(10)
        EXTERNAL NAME 'AF4TEST/DT_SQL(DT8_CHAR10)'
        PARAMETER STYLE GENERAL
        RETURNS NULL ON NULL INPUT  ;


 ce qui permet directement :


  SELECT * FROM CLIENTS WHERE DT8_DATE(DATCRT) = current date
 



 Vous pouvez aussi créer des fonctions à l'aide du PL/SQL
    (ou full SQL) à condition d'avoir le compilateur C.


 En effet une fonction écrite en PL/SQL génère un source C
  contenant du SQL (dans QTEMP) et lance la commande CRTSQLCI afin
  de créer un programme de service.


exemple :


  create function af4test/dec_franc_euros (VALEUR decimal(9, 2))
                  returns decimal(9, 2)
  LANGUAGE SQL RETURNS NULL ON NULL INPUT


  BEGIN
  DECLARE VALEUROS DECIMAL ( 9 , 2 ) ;
   SET VALEUROS = VALEUR / 6.55957;
   RETURN VALEUROS ;
  END;


 [ C'est le même langage que pour l'ordre CREATE PROCEDURE vu en V4R20 ]


                          



 et bien évidement la fonction inverse :


 create function af4test/dec_euro_francs (VALEUR decimal(9, 2))
                 returns decimal(9, 2)
 LANGUAGE SQL RETURNS NULL ON NULL INPUT


 BEGIN (1)
(3) DECLARE VALFRANCS DECIMAL ( 9 , 2 ) ;(2)
(4) SET VALFRANCS= VALEUR * 6.55957;
(5) RETURN VALFRANCS ;
 END;


(1)  le corps de la fonction est encadré de BEGIN  - END;


(2) chaque ligne se termine par ;


(3) les déclarations se font par DECLARE (même types que CREATE TABLE)


(4) les manipulations se font par SET ou VALUES


(5) RETURN termine la fontion en indiquant la valeur retrounée.
 



 Pour terminer les types de données définis par l'utilisateur


 ils sont créés par :


     CREATE DISTINCT TYPE IMAGE AS BLOB(512K)


     CREATE DISTINCT TYPE EUROS AS DECIMAL(9, 2)


     CREATE DISTINCT TYPE FRANCS AS DECIMAL(9, 2)


vous pouvez maintenant créer une table par




     CREATE TABLE VOITURES


            (CODE CHAR(10) NOT NULL PRIMARY KEY,


             PRIXe FRANCS NOT NULL, PRIXf EUROS,


             PHOTO IMAGE)


                          



  la particularité de ces types est qu'ils sont fortement typés ,


    c'est à dire que l'on ne PEUT PAS COMPARER DES FRANCS et DES EUROS.




   WHERE PRIXF > PRIXE   est syntaxiquement invalide !!!


   WHERE PRIXF > 10000 aussi !


seules des FONCTIONS supportant ces types sont habilitées à les manipuler.


 on parle alors de SGBD-OO (ou orienté objet)


le système assure une convertion avec le type d'origine, par CAST.




 WHERE CAST(PRIXF as DECIMAL(9, 2)) > 10000 est admis


 ainsi que


 WHERE PRIXF > CAST(10000 AS FRANCS)
 



 pour la comparaison prixe / prixf le plus simple est d'écrire des fonctions


 une fonction de convertion francs/euros
  une fonction de convertion euros/francs






 create function af4test/franc_euros (VALEUR FRANCS)
                 returns EUROS
 LANGUAGE SQL RETURNS NULL ON NULL INPUT   STATIC DISPATCH


 BEGIN
 DECLARE VALEUROS DECIMAL ( 9 , 2 ) ;
  SET VALEUROS = CAST(VALEUR as decimal(9,2) ) / 6.55957;
  RETURN CAST(VALEUROS as euros);
 END;






L'option STATIC DISPATCH est obligatoire quand une fonction manipule des UDT


                          





  create function af4test/euro_francs (VALEUR EUROS)
                  returns FRANCS
  LANGUAGE SQL RETURNS NULL ON NULL INPUT   STATIC DISPATCH


  BEGIN
  DECLARE VALFRANCS DECIMAL ( 9 , 2 ) ;
   SET VALFRANCS= cast(VALEUR as decimal(9,2) ) * 6.55957;
   RETURN cast(VALFRANCS as FRANCS);
  END;


  > select * from voitures
    where prixe = prixf
    Opérandes de l'opérateur = incompatibles. 


  > select * from voitures
     where prixe <> franc_euros(prixf)
   donne, lui, un résultat , ainsi que :


  > update voitures set prixe = franc_euros(prixf)
     where prixe = cast(0 as euros)
 



 il vous faut aussi, refédinir toutes les fonctions : SUM, AVG, etc...


 on parle alors de fonctions sourcées.(voire de surcharge ??)


 il peut donc y avoir plusieurs fonctions ayant le même nom, si elles
   acceptent des types de données différents.


 SQL choisi la bonne fonction, suivant le type manipulé. (polymorphisme)


 create function af4test/SUM (francs)
                  returns francs
        source   QSYS2/SUM (decimal() ) ;
 create function af4test/AVG (francs)
                  returns francs
        source   QSYS2/AVG (decimal() ) ;
 create function af4test/MAX (francs)
                 returns francs
        source   QSYS2/MAX (decimal() ) ;
 create function af4test/MIN (francs)
                 returns francs
        source   QSYS2/MIN (decimal() ) ;
 



 Toutes les fonctions SQL sont réputées pour être stockées dans QSYS2.


   (ne cherchez pas un programme physique, il n'y en a pas)




 toutes vos fonctions, tous vos types :


 1/ peuvent être qualifiés.




 2/ seront recherchés dans *LIBL, sinon.


    (à condition que vous soyez en convention système)




 3/ vous pouvez préciser le chemin au préalable par


    SET PATH  .....


    et retrouver cette valeur par le registre CURRENT_PATH