| 
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 appeler le compilateur. il faut donc le compilateur C sur votre machine jusqu'à la V4R50. (la version 4.4 de l'OS va amener 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 |
