Branchements et boucles en pur SQL

SQL est pratique mais il est compliqué de produire un script SQL qui s’adapte à toutes les versions de la base oracle ou qui réagit en fonction de certains résultats de requêtes. Cet article pour donner des astuces de scripting afin de gérer des branchements et boucles en pur SQL et de pouvoir créer des scripts adaptifs.

Avant de commencer: petits rappels

Gestion des variables en SQL

Variables typées passées par référence

Définition de la variable :

variable ma_var varchar2(42)

Affectation de la variable :

begin
 select 'valeur qui va bien' into :ma_var from sys.dual ;
end;
/

Ou encore :

exec :ma_var := 'valeur qui va bien'

Lecture de la variable :

select :ma_var from sys.dual ;

Ou encore :

print :ma_var

Exemple complet

variable ma_var varchar2(42)

begin
 select 'valeur qui va bien' into :ma_var from sys.dual ;
end;
/

PL/SQL procedure successfully completed.

select :ma_var from sys.dual ;
:MA_VAR
--------------------------------------------------------------------------------------------------------------------------------
valeur qui va bien

print :ma_var
MA_VAR
--------------------------------------------------------------------------------------------------------------------------------
valeur qui va bien


variable
variable   ma_var
datatype   VARCHAR2(42)

variable ma_var number

variable
variable   ma_var
datatype   NUMBER

SQL&amp;amp;amp;amp;amp;gt; exec :ma_var:=42

PL/SQL procedure successfully completed.



select :ma_var from sys.dual ;
   :MA_VAR
----------
        42

Variables passées par valeur

Les variables passées par valeur sont propres à SQL*Plus, elles sont considérées comme du texte et leur utilisation est paramétrable. Ainsi, elles peuvent être permanentes ou volatiles et sont construites sur le modèle suivant :
[Caractère identifiant de variable][Nom de variable]

Traditionellement les variables sont identifiées par une esperluette ( & ) mais cet identifiant est piloté par le paramètre ‘define’ de sqlplus

set define=&
set def=&

Définition de la variable et affecter :

define ma_var=toto

Ou encore en utilisant un paramètre de script :

define ma_var=&1

Ou encore via un ordre SQL:

col lolo new value ma_var 
select 'toto' lolo from dual ;
select 'Valeur de la variable : &ma_var' resultat from sys.dual ;

Lecture de la variable :
Avec reset immédiat :

select '&ma_var' from sys.dual ;

Ou encore en conservant sa valeur

select '&&ma_var' from sys.dual ;

Il est important de noter que la variable est remplacée par son contenu avant exécution de l’ordre SQL.

Concaténation de varaibles :
On utilise le point ( . ) pour concaténer des variables

def out=foo

spool &out..lst

...

spool off

host cat foo.lst

Les branchements ( if et switch/case )

On va s’appuyer sur les variables SQLPLUS pour effectuer des branchements en créant des scripts SQL :

if.sql
comment.sql
null.sql

script null.sql

C’est un script qui ne fait rien. Il a la même utilité que le zéro en mathématique, on ne la voit pas immédiatement mais il sert à tout :

-- rien dans le script

script comment.sql

Ce script est là pour débuter un commentaire.

-- début de commentaire dans le script
/*

script if.sql

C’est un script qui selon la condition passée en paramètre va appeler le script null.sql ou comment.sql

col rslt new_value to_do
col not_rslt new_value to_do_else
select case when &1 then 'null.sql' else 'comment.sql' end; rslt
     , case when &1 then 'comment.sql' else 'null.sql' end; not_rslt 
from sys.dual ;
@@&to_do

Utilisation de branchement

col date new_value param
select to_number(to_char(sysdate,'DD')) date from sys.dual ;

@if mod(&param,2)=0
prompt Aujourd'hui est un jour pair
/* end of if */

@&to_do_else
prompt Aujourd'hui est un jour impair
/* end of else */

col date new_value param
select to_number(to_char(sysdate+1,'DD')) date from sys.dual ;

@if mod(&param,2)=0
prompt Demain sera un jour pair
/* end of if */

@&to_do_else
prompt Demain sera est un jour impair
/* end of else */

Boucles

Parce qu’une boucle c’est par définition, refaire des choses pour des paramètres qui évoluent tant qu’une contition de sortie n’est pas vérifiée, la mise en place devient simple. Un exemple avec le fichier boucle.sql qui utilise les branchements définis plus hauts.

-- faire des trucs
select sysdate from sys.dual

-- vérifier la condition de sortie
promt Sortie de boucle ? 
&sortie

@if sortie='y'
-- condition de sortie vérifiée, on ne fait rien
/* end of if */
@&to_do_else
-- condition de sortie non vérifiée, on boucle
@@boucle.sql
/* end of else */

Conclusion

L’utilisation de branchements et boucles en pur SQL est avant tout du bricolage mais peut nous aider à sortir de situations usuelles sans avoir recours à un langage de scripting externe ou à du PL/SQL ce qui augment l’efficacité des scripts et, le cas échéant, leur portabilité entre windows et “le monde ouvert”.