| 
SQL , V4R40 admet maintenant les fonctions définies par l'utilisateur [UDF] les types de données définis 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 nul si un des argument est nul (il n'y aura pas d'appel) |
|
détail de la syntaxe : (1) external name : a/ bib/nom ==> *PGM b/ bib/nom(fonction) ==> fonction dans un *SRVPGM (2) type de paramètres a/ SQL convient pour les appels de PGM a1/ paramètres recus (x fois) a2/ val. retour en tant que paramètre en E/S a3/ indicateurs(binaires) pour val nulle de a1. a4/ indicateur (binaire) pour val. nulle de a2. |
|
a5/ SQLSTATE '00000' = fonction terminée sans erreur '01Hxx' = Warning (xx n'importe pas) '38Ixx'à '38Zxx'= fonction terminée en erreur ==> génère SQLCODe négatif a6/ nom qualifié de la fonction (139 car.) a7/ nom complet, non qualifié (128 car.) a8/ message de diagnostique (VARCHAR) b/ DB2SQL gère les appels succéssifs contient de a1 à a8 , plus b9/ scratchpad = zone de dialogue , conservé entre deux appels b10/ type d'appel -1= premier , 1 =dernier 0 = autre b11/ dbinfo (voir sqludf dans QSYSINC/H) |
|
C/ GENERAL appel d'une fonction (*SRVPGM) c1/ paramètres recus (la valeur resulta est la valeur retournée par la fonction) RETURNS NULL ON NULL INPUT évite un appel si l'un des paramètres est null, SQL assumant automatiquement un résultat NULL. D/ GENERAL WITH NULL idem GENERAL avec réception des indicateurs nulls et gestion par le programme (comme en a et b) |
|
voici le source RPG de la procédure associée à la fonction vue au début : H nomain * prototype ========================================================= D dt8_char8 pr 8 D 8 0 const * fonction ========================================================== Pdt8_char8 b export d pi 8 d dt8_recue 8 0 const 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 dt8_recue date c return %char(date) c endif pdt8_char8 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 const *--------------------------------------------------------- Pdt8_char10 b export d pi 10 d dt8_recue 8 0 const d date s D datfmt(*iso) c *iso test(d e) dt8_recue c if %error c return '0001-01-01' c else c *iso move dt8_recue date c return %char(date) c endif c Pdt8_char10 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 |
|
* deux autres exemples, l'un sous forme de *SRVPGM l'autre de *PGM : ........................................................................ : RAPPEL : un programme de service est l'équivalent d'une DLL Windows. : : : : c'est à dire un objet contenant PLUSIEURS routines autonomes, créé : : à partir du langage C ou RPG4. : : : : Pour le GAP4, le source commence par NOMAIN (pas de pgm principal) : : et chaque routine est encadrées de deux "spécifs" P (début/fin). : :......................................................................: CREATE FUNCTION AF4SRCT/NBCAR (CHAR(50) ) RETURNS INTEGER EXTERNAL NAME 'AF4SRCT/NBCAR(NBCAR)' PARAMETER STYLE GENERAL RETURNS NULL ON NULL INPUT ********************************************************************** * compte le nbr de caractères non blancs * ********************************************************************** |
|
h nomain D nbcar pr 10I 0 D chaine 50 const Pnbcar B export D nbcar pi 10I 0 D chaine 50 const D w s 10I 0 D i s 10I 0 c for i = 1 to %len(%trim(chaine)) c if %subst(chaine : i : 1) <> ' ' c eval w = w +1 c endif c endfor c return w Pnbcar E |
|
CREATE FUNCTION AF4SRCT/NBNUM (CHAR(50) ) RETURNS INTEGER EXTERNAL NAME 'AF4SRCT/NBNUM' LANGUAGE RPGLE NO SQL PARAMETER STYLE SQL RETURNS NULL ON NULL INPUT ........................................................................... : : : Dans le cas d'un programme (tout langage), vous recevez des paramètres : : de manière traditionnelle. Autant de paramètres qu'il y en a dans la : : fonctions, plus UN pour la valeur retour. : : : : Ainsi que ceux correspondant à votre choix (SQL, DB2SQL, ...) : :.........................................................................: ici, le programme va signaler un caractère invalide (lettre) en renseignant SQLSTATE (38I01) et le message de diagnostique : ********************************************************************** * compte le nbr de chiffre avant premier blanc * ********************************************************************** |
|
*paramètres D chaine s 50 D retour s 10I 0 *indicateurs SQL D chaine_ind s 5I 0 D retour_ind s like(chaine_ind) * D SQLSTATE s 5 D fonction_qual s 139 D fonction_nom s 128 D msg_diag s 70 varying * autres variables de travail D w s 10I 0 D i s 10I 0 C *entry plist C parm chaine C parm retour C parm chaine_ind C parm retour_ind C parm sqlstate C parm fonction_qual |
|
C parm fonction_nom C parm msg_diag * début du code C eval SQLSTATE = '00000' c for i = 1 to %len(%trim(chaine)) c if %subst(chaine : i : 1) < '0' c or %subst(chaine : i : 1) > '9' * car autre que blanc ==> erreur c if %subst(chaine : i : 1) <> ' ' c eval SQLSTATE = '38I01' c eval msg_diag = 'zone contient des car.+ c invalides' c endif c else c eval w = w +1 c endif c endfor c eval retour = w c eval *inlr = *on |
|
Il peut exister plusieurs versions d'une même fonction
par exemple CALECHEANCE( date) et CALECHEANCE( dec(8,0) ),
dans le fichier SYSFUNCS, cela s'appele une signature (PARM_SIGNATURE)
c'est le type qui fait référence, pas la longueur
(par exemple transmettre dec(8,0) ou dec(6,0) c'est pareil)
Donc si vous essayez
> SELECT NBNUM('123456') from QSQPTABL
NBNUM de type *N dans *LIBL non trouvé. (SQL0204)
SQL n'a pas trouvé la version de NBNUM attendant du VARCHAR
(en effet les constantes sont toujours traitées par SQL comme du VARCHAR)
le plus simple semble être de créer une deuxième version de la fonction :
CREATE FUNCTION AF4TEST/NBNUM (p1 VARCHAR(50) )
RETURNS INTEGER
LANGUAGE SQL
RETURN NBNUM( CAST(P1 as CHAR(50) ) )
|
|
Vous pouvez aussi créer des fonctions SQL à l'aide du PL/SQL (ou SQL PSM). ATTENTION, avant la V5R10, il faut avoir le compilateur C. En effet une fonction écrite complétement en 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. |
|
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 fonction en indiquant la valeur retournée. |
|
Et 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, PRIXf FRANCS NOT NULL, PRIXe 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és 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 > FRANCS(10000) |
|
pour la comparaison prixe / prixf le plus simple est d'écrire des fonctions une fonction de conversion francs/euros une fonction de conversion 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 un résultat , ainsi que > update voitures set prixe = franc_euros(prixf) where prixe = euros(0) |
|
il vous faut aussi, refédinir toutes les fonctions SUM, AVG, etc... on parle alors de fonctions dérivées.(parlerons nous de surcharge ??) il peut donc y avoir plusieurs fonctions ayant le même nom, si elles acceptent des types de données différents.(notion de polymorphisme) SQL choisi la bonne fonction, suivant le type manipulé. 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 en convention SQL [biblio.fonction] 2/ seront recherchés dans *LIBL, sinon. 3/ vous pouvez préciser le chemin au préalable par SET PATH ..... et retrouver cette valeur par le registre CURRENT_PATH |
