/*
iSeries Navigator - Mes connexions > Ouvrir (déployer) la machine > id. Bases de données > id. nom de la base
> En bas à droite ("Tâches relatives aux bases de données") : Exécution d'un script SQL.

- Terminer les commandes par un point-virgule.
- lancement : sélectionner puis CTRL+Y
- On peut sélectionner et lancer plusieurs commandes SQL distinctes. 
- Les commandes SQL qu'on entre peuvent être sauvegardées dans un fichier.
- Rappeler le fichier de commandes SQL : Fichier > Ouverture...

- Attention : par defaut mettre un point et non pas un slash entre le nom de bibliothèque et le nom de fichier.
    (mais ceci peut être changé, voir plus bas "EXECUTION DIRECTE DE COMMANDE CL")
- Des exemples sont fournis par iSeries Navigator : en haut à droite choisir une commande > Insertion.
- Rappel : collection = bibliothèque ; vue = fichier logique.

      EXECUTION DIRECTE DE COMMANDE CL
Condition : il faut accepter le format AS/400 - iSeries dans iSeries Navigator (séparateur "/" au lieu de ".")
Pour cela : 
Mes connexions > Bases de données > [nom base] > Exécution de script SQL 
> Connexion > Configuration JDBC > Onglet "Format" > Convention de dénomination : 
choisir *SYS au lieu de *SQL
Commencer la ligne par "CL:" ; ne pas oublier le ";" final
Exemples :
                                            */

CL:wrkactjob ; /* cree le listing dans le spool utilisateur - comme en batch */

/* Exemple pour modifier la date de peremption de certains fichiers d'une bibliotheque 
- 1ere etape : */
CL:DSPOBJD OBJ(BIB_LUE/*ALL) OBJTYPE(*FILE) OUTPUT(*OUTFILE) OUTFILE(mabiblio/DIV)
;
/* 2e étape : sortie de commandes toutes pretes, il ne restera plus qu'a copier-coller puis executer 
Remarque : sur "ecran vert" le "|" ("pipe") doit etre remplace par un point d'exclamation */
select 'CL:CHGPF FILE('||trim(ODLBNM)||'/'||trim(ODOBNM)||') EXPDATE(''23/07/10'');'
    from mabiblio/DIV
    where ODOBNM like 'DG%'
;

-- Determiner dans une série de lignes de meme identification si un certain "top" N'EST PAS applique 
-- a au moins un enregistrement. Exemple :
-- données identifiées par une série de zones (QKT1CD...), il faut au moins un enregistrement 
-- ayant "3" dans deux zones TOP distinctes.
-- But : connaitre les codes n'ayant pas eu le "topage" à 3.
SELECT DISTINCT QKT1CD, QKAHCD, QKAWCD, QKAQST, QKBDCD, QKB7NB, QKBDST, QKKGCD, QKIENB
    FROM ytrcre
    WHERE (QKT1CD, QKAHCD, QKAWCD, QKAQST, QKBDCD, QKB7NB, QKBDST, QKKGCD, QKIENB)
        NOT IN
        (SELECT QKT1CD, QKAHCD, QKAWCD, QKAQST, QKBDCD, QKB7NB, QKBDST, QKKGCD, QKIENB 
            FROM ytrcre 
            WHERE qkrqs1='3' AND qkrrs1='3'
         )
    ORDER by QKT1CD, QKAHCD, QKAWCD, QKAQST, QKBDCD, QKB7NB, QKBDST, QKKGCD, QKIENB

-- But : ajouter dans le fichier b les enreg du fichier a qui n'y figurent pas 
-- D'abord voir les lignes qui seront ajoutees : EXCEPTION
SELECT * from ytablesv a 
    exception join ytable b      
        on a.e0jbcd = b.e0jbcd 
        AND a.e0ehs1 = b.e0ehs1 
        AND a.e0jccd = b.e0jccd                                   
;
-- Mise-a-jour (noter le "as")
INSERT INTO YTABLE (e0jbcd, e0ehs1, e0jccd)
    SELECT a.e0jbcd, a.e0ehs1, a.e0jccd
    from ytablesv AS a 
    exception join ytable AS b 
        on a.e0jbcd = b.e0jbcd 
        AND a.e0ehs1 = b.e0ehs1 
        AND a.e0jccd = b.e0jccd                                            
;
-- INNER JOIN : verification de l'ajout (affiche les enreg en commun dans les 2 fichiers)
SELECT * from ytablesv a
    inner join ytable b
        on a.e0jbcd = b.e0jbcd
        AND a.e0ehs1 = b.e0ehs1
        AND a.e0jccd = b.e0jccd
;
-- LEFT OUTER JOIN (INNER JOIN + enreg de a inexistants dans b)
SELECT * from ytablesv b left outer join ytable a
        on a.e0jbcd = b.e0jbcd                   
        AND a.e0ehs1 = b.e0ehs1                  
        AND a.e0jccd = b.e0jccd
;

-- SELECT utilisant une correlation avec un autre fichier
-- Enregistrement dont une zone correspond a un parametre donne dans un autre fichier
SELECT * 
    FROM fich1 F1
    WHERE (select zone2code from fich2 where zone2corr=F1.zone1corr)='E'
;

-- Indiquer les fichiers physiques (PF) auxquels accèdent les LF
-- D'abord : 
CL:DSPFD FILE(bibliotheq/*ALL) TYPE(*ACCPTH) OUTPUT(*OUTFILE) FILEATR(*LF) OUTFILE(MABIBL_WRK/cxficval);
-- puis :
SELECT APLIB, APFILE, APBOL, APBOF 
    FROM MABIBL_WRK.FXFICVAL 
    group BY APLIB, APFILE, APBOL, APBOF 
    order by APLIB, APFILE, APBOL, APBOF
;
    
-- Encore DSPFD - Sortir les Doublons
SELECT ATFILE, count(atfile) AS  nbre  
    FROM MABIBL_WRK.olcreste
    WHERE ATFTYP = 'P'
    GROUP BY ATFILE
    HAVING count(ATFILE) > 1
;

--Compare deux DSPFD
-- (montre seulement ceux qui ne sont pas dans les deux)
SELECT A.atfile, a.atftyp, b.atfile, b.atftyp 
    FROM MABIBL_WRK.tstlmmolc a 
    exception join MABIBL_WRK.olcdta b 
        on a.atfile = b.atfile 
        where a.atftyp = 'P' AND  b.atftyp = 'P'
;

--DSPPGMREF liste les objets references dans un programme
CL:DSPPGMREF PGM(QFSD) OUTPUT(*OUTFILE) OUTFILE(MABIBL_WRK/dsppgmref) ;
SELECT WHSNAM 
    FROM MABIBL_WRK.dsppgmref 
    WHERE whobjt = 'F' 
    GROUP BY whsnam 
    ORDER BY whsnam
;
--liste des programmes et fichiers
CL:DSPPGMREF PGM(NOMFIC) OUTPUT(*OUTFILE) OUTFILE(mabibl/div)
/* Equivalent en QSHELL :
   QSH CMD('db2 "select * from mabibl.div" > /home/monrepert/tmp/div.txt')
   wrklnk '/home/monrepert/tmp/*'
*/
SELECT WHLIB, WHPNAM, WHTEXT, WHSNAM 
    FROM MABIBL_WRK.dsppgmref 
    WHERE whobjt = 'F' 
    ORDER BY whsnam
;

--liste enreg dont une zone est non a blanc
SELECT OFPTPF.PTNCLI, OFPTPF.PTZNOM, OFPTPF.PTZNOMC, OFPTPF.PTZPRE,
OFPTPF.PTZPREC, OFPTPF.PTZNJF, OFPTPF.PTZNJFC
   FROM OLCDTA.OFPTPF AS OFPTPF
   WHERE PTZNOMC <> ''
;

--mise à blanc d'une zone
UPDATE OLCDTA.OFPTPF AS OFPTPF
   SET OFPTPF.PTZNOMC = '                                        '
   WHERE PTNCLI = 9789459
;

--modif selective nom et prenom
UPDATE OLCDTA.OFPTPF AS OFPTPF
   SET OFPTPF.PTZNOM = 'DJANGO             ',  
       OFPTPF.PTZNOMC = '                   ', 
       OFPTPF.PTZPRE = 'ALAIN           ',
       OFPTPF.PTZPREC = '                      '
   WHERE PTNCLI = 9789335
;

--Liste triee d'enregistrement dans une plage de dates avec affichage d'une zone
SELECT OFFHPF.FHNDOS, OFFHPF.FHDDOS, OFFHPF.FHNCLI, OFPTPF.PTZNOM,
ofptpf.ptznomc, OFPTPF.PTZPRE, ofptpf.ptzprec, OFPTPF.PTZAD1,
OFPTPF.PTZAD2,  OFPTPF.PTZCPO,  OFPTPF.PTZVIL
    FROM OLCDTA.OFFHPF AS OFFHPF, OLCDTA.OFPTPF AS OFPTPF
    WHERE OFFHPF.FHDDOS > '20061101' AND  OFFHPF.FHDDOS < '20061299'
        AND  OFPTPF.PTNCLI=OFFHPF.FHNCLI
    ORDER BY FHDDOS DESC
;

--resultat d'un DSPOBJD avec output(*file) sur une biblio de programmes
SELECT ODOBNM, ODOBAT, ODudat, ODobtx 
    FROM dgwrk.dgwrk AS dgwrk 
    where odobat LIKE 'RPG%' 
    ORDER BY dgwrk.odobnm ASC
;

--recherche une chaine dans les entetes rapports OFR1
SELECT R1NDOS, R1CDES, R1NCLI, R1ZA1P, R1ZA2P, R1ZLM1
   FROM OLCDTA.OFR1PF AS OFR1PF
   WHERE R1ZA1P LIKE 'Monsieur%' AND  R1ZA2P LIKE 'ROUDOUC%
;

--recherche un patient sur le nom (plage) dans OFPTPF
SELECT OFPTPF.PTNCLI, OFPTPF.PTZNOM, OFPTPF.PTZNOMC, OFPTPF.PTZPRE,
OFPTPF.PTZPREC, OFPTPF.PTZNJF, OFPTPF.PTZNJFC
   FROM OLCDTA.OFPTPF AS OFPTPF
   WHERE PTZNOM between 'LUTEY' AND  'LUTEYNZZ'
;

--bibliothèques classées par nombre de fichiers décroissant
SELECT TABLE_SCHEMA, count(*) AS "COUNT" 
    FROM QSYS2.SYSTABLES 
    GROUP BY TABLE_SCHEMA 
    ORDER BY "COUNT" DESC
;

--liste de fichiers
SELECT TABLE_SCHEMA, TABLE_NAME 
    FROM QSYS2.SYSTABLES
;

--liste de membres d'une bibliotheque - avec tailles 
--    (A noter aussi : DAYS_USED_COUNT, NUMBER_ROWS, LAST_CHANGE_TIMESTAMP...
SELECT  SYSTEM_TABLE_SCHEMA, SYSTEM_TABLE_NAME, SYSTEM_TABLE_MEMBER, DATA_SIZE 
    FROM qsys2/syspstat WHERE sys_dname = 'nom_bibl'
;

/* Affichage de fichiers dépendants */
CL:DSPDBR FILE(library1/table1) OUTPUT(*OUTFILE) OUTFILE(qtemp/dspdbr);
SELECT * FROM qtemp.dspdbr;

/* Affichage de références de programme */
CL: DSPPGMREF PGM(library1/(*ALL)) OUTPUT(*OUTFILE) OBJTYPE(*ALL) OUTFILE(qtemp/dsppgmref); 
SELECT * FROM qtemp.dsppgmref;

/* Affichage de postes de journal */
CL:DSPJRN JRN(library1/QSQJRN) OUTPUT(*OUTFILE) OUTFILFMT(*TYPE4) OUTFILE(qtemp/dspjrn);
SELECT * FROM qtemp.dspjrn;

-- SQL sur BD distante *************************************

-- Preliminaire : WRKRDBDIRE pour gérer les postes de BDR des systemes du reseau
-- (peut se faire aussi par iSeries Navigator)
-- Dans l'exemple on a ajouté une ligne "sysonnio" pour un autre AS/400
connect to sysonnio user guebey using '[psw]'
;

-- retour au système local (syscentr est le nom affiche par WRKRDBDIRE)
set connection syscentr
;

-- Chiffrement *************************************

--Test entree
SET ENCRYPTION PASSWORD='azerty'; 
    INSERT INTO MABIBLIO.XTEST VALUES(ENCRYPT('zone devinette'))
;

SELECT *  FROM MABIBLIO.XTEST
;

--Test sortie
SET ENCRYPTION PASSWORD='azerty';
SELECT DECRYPT_CHAR(CMPF) FROM MABIBLIO.XTEST
;

SELECT OFPTI0.PTNCLI, OFPTI0.PTZNOM, OFPTI0.PTZNOMC, OFPTI0.PTZPRE,
OFPTI0.PTZPREC, OFPTI0.PTZNJF, OFPTI0.PTZNJFC
   FROM OLCDTA.OFPTI0 AS OFPTI0
;

--Cryptage "XOR" - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
UPDATE MON_FICH.xor 
    SET odobtx=xor(odobtx,'dans un mois dans un an comment souffrirons nous seigneur que tant de mers me separent de vous')
;

-- **************************************************************************
Liste IBM d EXEMPLES  - - - - - - - - - - - - - - - - - - - - - - - - - - - -
SELECT * FROM table1
DELETE FROM table1 WHERE column1 = 0;
INSERT INTO table1 (column1) VALUES(0);
INSERT INTO table1 VALUES(0,'AAA',1);
INSERT INTO table1 (column1) SELECT column1 FROM table2 WHERE column1> 5;
INSERT INTO table1 (column1) VALUES(0);
UPDATE table1 SET column1 = 0 WHERE column1 < 0;
UPDATE table1 SET ROW = (column1, ' ',column3);
UPDATE table1 SET (column1, column2) = (SELECT column1, column2 FROM table2 WHERE table1.column3 = column3);
SELECT * FROM QSYS2.SYSTABLES;
SELECT * FROM QSYS2.SYSTABLES WHERE TABLE_NAME LIKE 'FILE%';
SELECT TABLE_SCHEMA, COUNT(*) AS "COUNT" FROM QSYS2.SYSTABLES GROUP BY  TABLE_SCHEMA ORDER BY "COUNT" DESC;

CALL QSYS.CREATE_SQL_SAMPLE('collection');
CALL procedure1 ('aaa',5,NULL);
LOCK TABLE table1 IN SHARE MODE;
LOCK TABLE table1 IN EXCLUSIVE MODE;
LOCK TABLE table1 IN EXCLUSIVE MODE ALLOW READ;
SET PATH = *LIBL;
SET PATH = collection1, collection2;
UPDATE table1 SET ROW = (column1, ' ',column3);
UPDATE table1 SET (column1, column2) = (SELECT column1, column2 FROM table2 WHERE table1.column3 = column3);

ALTER TABLE table1 ADD COLUMN column3 INTEGER;
ALTER TABLE table1 ALTER COLUMN column1 SET DATA TYPE DECIMAL(31,0);
ALTER TABLE table1 DROP COLUMN column3;
ALTER TABLE table1 ADD CONSTRAINT constraint1 FOREIGN PRIMARY KEY (column1);
ALTER TABLE table1 DROP FOREIGN PRIMARY KEY CASCADE;
ALTER TABLE table1 ADD CONSTRAINT constraint2 UNIQUE (column2);
ALTER TABLE table1 DROP UNIQUE constraint2 RESTRICT;
ALTER TABLE table1 ADD CONSTRAINT constraint3 FOREIGN KEY (column2) REFERENCES table2 ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE table1 DROP FOREIGN KEY constraint3 CASCADE;
ALTER TABLE table2 DROP CONSTRAINT constraint4 CASCADE;
ALTER TABLE table1 ADD CONSTRAINT constraint5 CHECK( column1 > 0);
ALTER TABLE table1 DROP CHECK constraint5 RESTRICT;
COMMENT ON COLUMN table1.column1 IS 'comment';
COMMENT ON COLUMN table1 (column2 IS 'comment', column3 IS 'comment);
COMMENT ON TABLE table1 IS 'comment';
CREATE TABLE table1 (column1 INTEGER NOT NULL, column2 VARCHAR(100) ALLOCATE(20));
CREATE TABLE table2 (column1 INTEGER NOT NULL CONSTRAINT constraint9 FOREIGN PRIMARY KEY, column2 DECIMAL(5,2));
DROP TABLE table3 RESTRICT;
GRANT SELECT, DELETE, INSERT, UPDATE ON TABLE table3 TO PUBLIC WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON table3 TO PUBLIC;
GRANT ALTER, INDEX ON table3 TO PUBLIC;
GRANT UPDATE (column1) ON table2 TO PUBLIC;
LABEL ON COLUMN table1.column1 IS 'label';
LABEL ON COLUMN table1 (column2 IS 'label', column3 IS 'label');
LABEL ON COLUMN table1.column1 TEXT IS 'label';
LABEL ON COLUMN table1 (column2 TEXT IS 'label', column3 TEXT IS 'label');
LABEL ON TABLE table1 IS 'label';
RENAME TABLE table1 TO table3;
REVOKE SELECT, DELETE, INSERT, UPDATE ON TABLE table3 FROM PUBLIC;
REVOKE ALL PRIVILEGES ON table3 FROM PUBLIC;
REVOKE ALTER, INDEX ON table3 FROM PUBLIC;
REVOKE UPDATE (column1) ON table2 FROM PUBLIC;

COMMENT ON COLUMN view1.column1 IS 'comment';
COMMENT ON COLUMN view1 (column2 IS 'comment', column3 IS 'comment');
COMMENT ON TABLE view1 IS 'comment';
CREATE VIEW view1 AS SELECT column1, column2, column3 FROM table2 WHERE column1 > 5;
CREATE VIEW view1 AS SELECT * FROM table2 WHERE column1 > 5 WITH CHECK OPTION;
DROP VIEW view3 CASCADE;
GRANT SELECT, DELETE, INSERT, UPDATE ON view3 TO PUBLIC WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON view3 TO PUBLIC;
GRANT ALTER ON view3 TO PUBLIC;
GRANT UPDATE (column1) ON view2 TO PUBLIC;
LABEL ON COLUMN view1.column1 IS 'label';
LABEL ON COLUMN view1 (column2 IS 'label', column3 IS 'label');
LABEL ON COLUMN view1.column1 TEXT IS 'label';
LABEL ON COLUMN view1 (column2 TEXT IS 'label', column3 TEXT IS 'label');
LABEL ON TABLE view1 IS 'label';
RENAME TABLE view1 TO view3;
REVOKE SELECT, DELETE, INSERT, UPDATE ON view3 FROM PUBLIC;
REVOKE ALL PRIVILEGES ON view3 FROM PUBLIC;
REVOKE ALTER ON view3 FROM PUBLIC;
REVOKE UPDATE (column1) ON view2 FROM PUBLIC;

COMMENT ON ALIAS alias1 IS 'comment';
CREATE ALIAS alias1 FOR table1;
CREATE ALIAS alias2 FOR table2(member1);
DROP ALIAS alias1;
LABEL ON ALIAS alias1 IS 'label';

CREATE COLLECTION collection1;
DROP COLLECTION collection1;

COMMENT ON PACKAGE package1 IS 'comment';
DROP PACKAGE package1;
LABEL ON PACKAGE package1 IS 'label';
GRANT EXECUTE ON PACKAGE package1 TO PUBLIC;

COMMENT ON INDEX index1 IS 'comment';
CREATE UNIQUE INDEX index1 ON table1 (column1,column2);
CREATE ENCODED VECTOR INDEX index2 ON table2 (column1) WITH 300 DISTINCT VALUES;
DROP INDEX index1;
RENAME INDEX index1 TO index3;

COMMENT ON FUNCTION function1 IS 'comment';
COMMENT ON PARAMETER function1.parameter1  IS 'comment';
COMMENT ON PARAMETER function1 (parameter1  IS 'comment', parameter2 IS 'comment');
CREATE FUNCTION function1 (parameter1 INTEGER) 
    RETURNS INTEGER LANGUAGE C EXTERNAL NAME 'lib1/pgm1(entryname)' PARAMETER STYLE GENERAL;
CREATE FUNCTION function2 (parameter1 INTEGER) 
    RETURNS INTEGER LANGUAGE SQL BEGIN DECLARE variable1 DECIMAL(5,2); 
    SELECT c1 INTO variable1 FROM table1 WHERE column1 = parameter1; RETURN variable1; END;
DROP FUNCTION function1;
GRANT EXECUTE ON FUNCTION function1 TO PUBLIC;
GRANT EXECUTE ON FUNCTION function1 (INTEGER) TO PUBLIC;
GRANT EXECUTE ON SPECIFIC FUNCTION specific1 TO PUBLIC;
GRANT EXECUTE ON SPECIFIC FUNCTION specific1 (INTEGER) TO PUBLIC;
REVOKE EXECUTE ON FUNCTION function1 FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION function1 (INTEGER) FROM PUBLIC;
REVOKE EXECUTE ON SPECIFIC FUNCTION specific1 FROM PUBLIC;

COMMENT ON PARAMETER procedure1.parameter1  IS 'comment';
COMMENT ON PARAMETER procedure1 (parameter1  IS 'comment', parameter2 IS 'comment');
COMMENT ON PROCEDURE procedure1 IS 'comment';
CREATE PROCEDURE procedure1 (INOUT parameter1 INTEGER) LANGUAGE C EXTERNAL PARAMETER STYLE GENERAL;
CREATE PROCEDURE procedure2 (INOUT parameter1 INTEGER) LANGUAGE SQL BEGIN DECLARE variable1 DECIMAL(5,2); 
SELECT column1 INTO variable1 FROM table1 WHERE column1 = parameter1; 
    IF variable1 > 5 THEN INSERT INTO table2 VALUES(100); END IF; END;
DROP PROCEDURE procedure1;
GRANT EXECUTE ON PROCEDURE procedure1 TO PUBLIC;GRANT EXECUTE ON PROCEDURE procedure1 (INTEGER) TO PUBLIC;
GRANT EXECUTE ON SPECIFIC PROCEDURE specific1 TO PUBLIC;
REVOKE EXECUTE ON PROCEDURE procedure1 FROM PUBLIC;
REVOKE EXECUTE ON PROCEDURE procedure1 (INTEGER) FROM PUBLIC;
REVOKE EXECUTE ON SPECIFIC PROCEDURE specific1 FROM PUBLIC;

COMMENT ON ROUTINE routine1 IS 'comment';
COMMENT ON PARAMETER routine1.parameter1  IS 'comment';
COMMENT ON PARAMETER routine1 (parameter1  IS 'comment', parameter2 IS 'comment');
DROP ROUTINE routine1;
GRANT EXECUTE ON ROUTINE routine1 TO PUBLIC;
GRANT EXECUTE ON ROUTINE routine1 (INTEGER) TO PUBLIC;
GRANT EXECUTE ON SPECIFIC ROUTINE specific1 TO PUBLIC;
REVOKE EXECUTE ON ROUTINE routine1 FROM PUBLIC;
REVOKE EXECUTE ON ROUTINE routine1 (INTEGER) FROM PUBLIC;
REVOKE EXECUTE ON SPECIFIC ROUTINE specific1 FROM PUBLIC;

COMMENT ON DISTINCT TYPE type1 IS 'comment';
CREATE DISTINCT TYPE type1 AS INTEGER WITH COMPARISONS;
DROP DISTINCT TYPE type1 CASCADE;
GRANT USAGE ON DISTINCT TYPE type1 TO PUBLIC WITH GRANT OPTION;
REVOKE USAGE ON DISTINCT TYPE type1 FROM PUBLIC;

/* Instructions de contrôle SQL PROCEDURE et SQL FUNCTION */
label1: BEGIN NOT ATOMIC ... END label1;
CASE WHEN column1 = 0 THEN ... WHEN column1 = 1 THEN ... ELSE ...  END CASE;
CASE column1 WHEN 0 THEN ... WHEN 1 THEN ... ELSE ... END CASE;
DECLARE variable1 INTEGER;
DECLARE cursor1 CURSOR FOR SELECT * FROM table1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION ... ;
DECLARE CONTINUE HANDLER FOR SQLWARNING ...;
DECLARE EXIT HANDLER FOR NOT FOUND ... ;
DECLARE EXIT HANDLER FOR SQLSTATE '42501' ...;
label2: FOR variable1 AS cursor2 CURSOR FOR SELECT * FROM table1 DO ... END FOR label2;
IF variable1 = 0 THEN ... ELSEIF variable1 < 0 THEN ... ELSE ... END IF;
LEAVE label2;
label3: LOOP ... END LOOP label3;
label4: REPEAT ... END REPEAT label4;
SET variable1 = 4;
SET parameter1 = NULL;
label5: WHILE variable < 4 DO ... END WHILE label5;