| 
Procédures cataloguées [STORED PROCEDURES] : ----------------------------------------------- SQL permet maintenant de demander l'exécution d'un programme sur un serveur distant (normalement devant réaliser une action sur la base de ce serveur) Cette fonction n'est pas encore normalisée (mais largement répandue). - certains SGBD n'autorisent que des procédures SQL. - DB2/400 autorise l'appel à n'importe quel programme. + avec passage de paramètres. + ces paramètres pouvant être renseignés par le pgm distant. Syntaxe CALL nom-procédure---------------------------------------------- | | |-(param1,[param2,...])-| |
|
nom-procédure = nom du pgm à appeler ou procédure telle que définie par DECLARE PROCEDURE (voir ci-dessous) la procédure est recherchée dans la collection en cours pour qualifier : BIB/PGM avec la convention système BIB.PGM avec la convention SQL voir: STRSQL paramètre NAMING CRTSQLxxx paramètre OPTION paramx = paramètre envoyé (ou reçu) qui peut être + une constante + une variable du pgm (:var) (par défaut :var est en entrée/sortie avec SQL statique en entrée (envoyée) avec SQL dynamique) + NULL + une valeur spéciale (CURRENT DATE/TIME/TIMEZONE , SERVER) (USER) |
|
Il est possible de déclarer une procédure afin de préciser certaines options DECLARE -nom-proc-PROCEDURE------------------------------------------------> | |-IN----| | |-(param---OUT-----type de données(lg)-----| |-INOUT-| |-EXTERNAL------------------| >------------------------------LANGUAGE--langage--------------------------- |-EXTERNAL NAME-nom-externe-| |-SIMPLE CALL-----------| |-SIMPLE CALL WITH NULL-| définition des paramètres : IN le paramètre est uniquement reçu par la procédure OUT le paramètre est renvoyé par la procédure INOUT le paramètre est utilisé dans les deux sens type de donnée(lg) : idem CREATE TABLE CHAR(xx) INT(lg, dec) etc ... |
|
EXTERNAL NAME permet de définir le nom réel du pgm sur le serveur, si cette clause n'est pas renseignée c'est le nom de procédure qui est utilisé. LANGUAGE permet de définir le langage d'origine du pgm à exécuter C, CL, COBOL, COBOLLE, RPG, RPGLE, REXX, ... ceci est obligatoire avec une procédure REXX si cette clause n'est pas utilisé le server recherche l'attribut du pgm et par défaut le considère comme un pgm C. SIMPLE CALL WITH NULL signifie que le pgm doit recevoir les indicateurs indiquant les valeurs nulles (254 param maxi) SIMPLE CALL le pgm ne reçoit pas ces indicateurs (255 param maxi) l'instruction DECLARE PROCEDURE doit être utilisée pour appeler des procédures REXX, et doit être écrite AVANT l'instruction CALL. |
|
Particularité de SQL : il ne reconnait pas les membres.
vous pouvez quand même outrepasser en utilisant les procédures :
EXEC SQL
CALL QSYS.QCMDEXC ('OVRDBF FIC MBR(xxx) OVRSCOPE(*JOB)',
0000000035,00000)
END-EXEC.
Attention
le point (dans QSYS.CMDEXC) doit être le qualifiant en cours
la virgule (dans 0000000035,00000) doit être la marque décimale.
Cette solution peut-être utilisée aussi avec ODBC/JDBC.
|
|
Vous pouvez même renvoyer un groupe d'enregistrements en une seule fois : Dans la procédure appelée, ajoutez : SET RESULT SETS ----------------------------------------------------- ! ! !-----CURSOR nomcurseur-----------------! ! ! ! ! !--ARRAY nomdetableau -----FOR x ROWS---- Le curseur indiqué (il doit être ouvert) ou l'ensemble des enregistrements placés dans le tableau (ou la table en COBOL) représentent les valeurs en retour retournées au micro. Cet ordre n'est pas valide en RPG, il n'y a pas d'invite SQL interactif. |
|
Vous pouvez enfin préparer tout cela à l'avance en déclarant une fois pour toute la procédure et ses particularités ( ce qui est conseillé ) CREATE PROCEDURE --------(même syntaxe que DECLARE -------)----> ----------------------------------------------. ! ! !--DYNAMIC RESULT SETS---n-----------! n définit le nombre de "result sets" retournés La définition est enregistrée dans les fichiers SYSPROCS et SYSPARMS du catalogue. |
|
Exemple (retourne *LIBL)
1/ enregistrement dans las catalogues
CREATE PROCEDURE AF4TOOL/DSPLIBL LANGUAGE RPGLE NOT DETERMINISTIC
CONTAINS SQL EXTERNAL NAME AF4TOOL/DSPLIBL PARAMETER STYLE GENERAL ;
2/ le pgm (ici le RPG principal)
DDSlibl ds occurs(265)
D unebib 10
DUSRlibl ds
d tblibl 11 dim(265)
D i S 5I 0
D R S 5I 0
Drtvlibl PR EXTPROC('DSPLIBLC')
D 2750
|
|
/free rtvlibl(USRLIBL); for i=1 to 265; if tblibl(i) = *blanks and i > 15; // on est dans USRLIBL leave; else; if tblibl(i) <> *blanks; R = R + 1; %occur(DSlibl) = R; unebib = tblibl(i); endif; endif; endfor; /end-free C/EXEC SQL C+ SET RESULT SETS ARRAY :DSLIBL FOR :R ROWS C/END-EXEC /free return; /end-free |
|
et enfin le CL, utilisant RTVJOBA (ce CL est linké avec le RPG) PGM PARM(&LIBL) DCL VAR(&LIBL) TYPE(*CHAR) LEN(2915) DCL VAR(&USRLIBL) TYPE(*CHAR) LEN(2750) DCL VAR(&SYSLIBL) TYPE(*CHAR) LEN(165) RTVJOBA USRLIBL(&USRLIBL) SYSLIBL(&SYSLIBL) CHGVAR VAR(&LIBL) VALUE(&SYSLIBL *CAT &USRLIBL) ENDPGM pour utiliser cette procédure, lancez comme ordre SQL : "CALL DSPLIBL" et traitez (suivant le langage) comme si vous aviez demandé un SELECT |
|
Il est possible de créer des procédures cataloguées SQL en SQL PSM (même langage que les bases Oracle avec PL/SQL) ATTENTION : cela va créer un programme OS/400 qui sera référencé dans les catalogues SQL (SYSPROCS et SYSPARMS dans QSYS2) mais pour créer ce programme OS, SQL va générer un source C contenant du SQL et appeller le compilateur. il faut donc le compilateur C sur votre machine jusqu'à la V4R50. (la version 4.4 de l'OS va ammener les fonctions, qui pourrons elles aussi être écrites en SQL ==> mêmes contraintes, jusqu'en V4r50) |
|
Exemple général : CREATE PROCEDURE xxxxxxx/P1 (in nouveau_module CHAR(10) , out probleme integer ) LANGUAGE SQL BEGIN ATOMIC DECLARE UNDO HANDLER FOR SQLEXCEPTION SET probleme = -1; FOR v1 as C1 cursor for SELECT module, nomcours FROM COURS DO IF nomcours <> ' ' THEN UPDATE COURS SET module = nouveau_module WHERE CURRENT OF C1 ; END IF ; END FOR; END |
|
I/ syntaxe générale --ATOMIC------ BEGIN --- ------------------> --NOT ATOMIC-- déclaratives .../... instructions SQL .../... .../... END ATOMIC ==> l'ensemble de la procédure doit être réalisé comme un tout, vous ne pouvez pas placer d'ordres COMMIT/ROLLBACK NOT ATOMIC pas de validation/invalidation automatique, vous définissez vos transactions par COMMIT/ROLLBACK. |
|
II/ déclaratives : on peut déclarer des variables (sauf les paramètres qui sont déja déclarés) ---DEFAULT NULL----- DECLARE ---nom-variable----type-sql--- ----- ---DEFAULT val/dft-- on peut assigner des noms aux codes erreurs SQL : DECLARE ---nom-condition--- CONDITION FOR -----code-erreur code-erreur: '01234' = code 1234 SQLEXCEPTION = erreur <0 SQLWARNING = erreur >0 (sauf 100) NOT FOUND = erreur 100 |
|
déclaration de condition d'erreur (sorte de MONMSG ..) on déclare 1/ l'erreur (code ou nom-condition) 2/ le traitement 3/ la reprise après traitement. (1) DECLARE ------------------------ HANDLER FOR code-erreur---------> ! ! (3)!--CONTINUE----! ! ! !--EXIT--------! ! ! !--UNDO--------! >-------instruction SQL ; (2) |
|
la reprise : dans tous les cas on exécute le traitement (2) avant de gérer la reprise (3) CONTINUE = on continue le code en séquence. (si l'erreur a lieu dans un IF, CASE, WHILE, on sort et on se débranche à l'instruction qui suit le IF ...) EXIT = on va au END UNDO = on annule tous les changements (ROLLBACK) puis EXIT, ce qui implique BEGIN ATOMIC ! |
|
III/ Instructions : toutes les instructions SQL sont valides, aussi bien les ordres de gestion de la base (CREATE, GRANT, ...) que les ordres de manipulation. plus, en assignation : SET nom-variable = expression (même possibilités que SELECT) et en structuration : IF --test----THEN--instruction;----------> >-----------------------------------------> ! ! !-ELSEIF test2 THEN instruction2;-! >--------------------------END IF ;-------> ! ! !-ELSE instruction3;-! |
|
CASE 1ere forme CASE--nom-variable-----------------------> >---WHEN valeur1 THEN instruction1;------> >-----------------------------------------> ! ! !-WHEN valeur2 THEN instruction2;! >------------------------END CASE; ! ! !-ELSE instruction3;-! chaque test se fait par égalité et sur la même variable. |
|
CASE 2eme forme CASE-------------------------------------> >---WHEN test1---THEN instruction1;------> >-----------------------------------------> ! ! !-WHEN test2 THEN instruction2;! >------------------------END CASE; ! ! !-ELSE instruction3;-! chaque test peut utiliser : - un autre opérateur que l'égalité. - une variable différente. |
|
LOOP (boucle) -----------------LOOP----------> !-label:--! série d'instructions SQL;-----> ----END LOOP; le seul moyen de sortir de la boucle est LEAVE WHILE (boucle conditionnée, tant que) -----------------WHILE--condition pour boucler--- DO ----> !-label:--! série d'instructions SQL;-----> ----END WHILE; la condition est testée AVANT de faire. |
|
REPEAT (boucle conditionnée, jusqu'à) -----------------REPEAT ----> !-label:--! série d'instructions SQL;-----> --UNTIL (condition d'arret) ----END REPEAT; la condition est testée en fin de boucle (après). LEAVE ----LEAVE label--------> sortie de la boucle qui s'appelle "label" |
|
FOR (exécution d'une série d'instructions pour chaque ligne d'un curseur) -------------FOR nomf --------------------------------------> !-label:--! ! ! !--AS nomcurseur CURSOR FOR-------! ----------clause SELECT------------- DO --------------> série d'instructions SQL;-----> END FOR; si vous utilisez un nom de curseur il doit être unique (non déclaré par DECLARE CURSOR) sinon, il sera déclaré implicitement. toutes les variables retournées par le SELECT doivent être unique (jointure) si vous utilisez AS [nocli AS codeclient] c'est "codeclient" que vous devez manipuler dans la boucle. et enfin "nomf" n'est pas utilisable dans la série d'instructions. |
|
l'ordre FOR remplace la série suivante : .............................................................. : DECLARE ... pour chaque variable du SELECT : : : : DECLARE C1 CURSOR FOR (votre SELECT) : : : : OPEN C1; : : : : FETCH C1 INTO (les variables déclarées plus haut); : : : : WHILE (fin de fichier non rencontrée) : : : : ---vos instructions SQL;--- : : : : FETCH C1 INTO (les variables déclarées plus haut); : : : : END WHILE; : : : : CLOSE C1 ; : :............................................................: |
|
Quelques exemples :
FOR :
BEGIN
DECLARE nomlong CHAR(40);
FOR vl AS
c1 CURSOR FOR
SELECT prenom , init , nom FROM personp1
DO
SET nomlong =
nom concat ', ' concat prenom concat
' (' concat init concat ')';
INSERT INTO TNOMS VALUE ( nomlong );
END FOR;
END
|
|
avec des curseurs : ins_loop: LOOP CALL RETURN_serviveINFO (SERVICE, SERVNOM, SERVADM); IF SERVICE IS NULL THEN LEAVE ins_loop; ELSE INSERT INTO DEPARTMENT (SERVICE , SERVNOM, SERVADM) ; END LOOP; fetch_loop: LOOP FETCH c1 INTO v_prenom, v_init, v_nom IF SQLCODE <> 0 THEN LEAVE fetch_loop; END LOOP fetch_loop; CLOSE c1; |
|
BEGIN DECLARE n_salaire DECIMAL(9,2); DECLARE v_nopers CHAR(6) DEFAULT '123456'; SELECT salaire INTO n_salaire FROM personp1 WHERE nopers = v_nopers; IF code = 1 THEN SET n_salaire = n_salaire + (n_salaire * 0,10); ELSEIF code = 2 THEN SET n_salaire = n_salaire + (n_salaire * 0,05); END IF; UPDATE personp1 SET salaire = n_salaire WHERE nopers = v_nopers; END |
|
Et enfin, : CREATE PROCEDURE PROC1 () LANGUAGE SQL BEGIN DECLARE v_prenom VARCHAR(12); DECLARE v_init CHAR(1); DECLARE v_nom VARCHAR(15); DECLARE v_coef SMALLINT; DECLARE v_salaire DECIMAL(9,2); DECLARE wEOF INT DEFAULT 0; DECLARE not_found CONDITION FOR '02000'; DECLARE c1 CURSOR FOR SELECT prenom, init, nom, coef, salaire FROM personp1; DECLARE CONTINUE HANDLER FOR not_found SET wEOF = 1; |
|
OPEN c1; FETCH c1 INTO v_prenom, v_init, v_nom, v_coef, v_salaire; WHILE wEOF = 0 DO (traitement de la ligne lue) FETCH c1 INTO v_prenom, v_init, v_nom, v_coef, v_salaire; END WHILE; CLOSE c1; END |
|
Fonctions Définies par l'utilisateur (V4R40) : ------------------------------------------------ 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) |
|
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 dt8_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 '0001-01-01' 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 create function af4test/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. |
