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é #18

Juin 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