SQL - avancéLes requêtes préparées et les procédures

Avant toute chose, avec ce qu'on a vu jusque là vous pouvez vraiment vous en sortir sans souci en bases de données pour tout ce que vous aurez à faire dans votre vie de bioinformaticien. Mais il est intéressant de pousser un peu plus loin vos limites :-)

Les requêtes préparées

L'objectif des requêtes préparées est de sécuriser les requêtes. Comment fonctionnent-elles? Voilà le principe général:

La requête est stockée en mémoire à sa création

Il est possible de l’utiliser quand on veut

On peut également utiliser des variables stockées dans mysql, à tout moment. Elles sont valables pendant toute la durée de la session (tant qu'on ne se déconnecte pas).

Les variables stockées

Une variable stockée est une variable créée par l'utilisateur afin de lui permettre d'interagir et d'apporter du dynamisme à ses requêtes.

Voilà quelques principes de ces variables:

Elles sont toujours précédées d'un @
set @nom='reichstadt';
set @nom:='reichstadt';

Pour lister les variables stockées, il suffit de les sélectionner comme un attribut de table
select @nom;
select @nom, personnes.* from personnes;

Comme pour les insertions en masse, il est possible d’assigner une valeur à une variable stockée tout en la sélectionnant
select @nom:='McBlobby';

Ces variables peuvent être utilisées dans des requêtes, par exemple pour des conversions ou des calculs
set @prixKilo=3.6;
select animal,round(poids*@prixKilo) as 'prix Bete' from animal;

Les procédures

Elles ont pour but de regrouper plusieurs requêtes que l'utilisateur souhaite lancer de manière groupée. Elles possèdent un nom, un début et une fin, et sont stockées dans la base de données (comme les vues, et les tables). Elles se créent et se suppriment donc de la même manière.

Création

La syntaxe de la création d'une procédure est:
delimiter //
create procedure nom_procedure(liste_params)
begin
requete 1;
requete 2;
end//
delimiter;

Voici un exemple concret:

mysql> delimiter //
mysql> create procedure afficher_nb_dept(in in_dept varchar(20))
mysql> begin
mysql>    select count(*) from travaillent where dept=in_dept;
mysql> end//
mysql> 
mysql> create procedure afficher_nb_dept(in in_dept varchar(20))
mysql> begin
mysql>    select count(*) from travaillent where dept=in_dept;
mysql> end//
mysql> delimiter;

Appel

L’appel d’une procédure stockée se fait en utilisant la fonction call. La syntaxe de l'appel d'une procédure est:
call procedure_stockee(param);

Voici un exemple concret:

mysql> set @dept='maths';
mysql> set @nb_res=0;
mysql> 
mysql> call afficher_nb_dept(@dept);
mysql> call exporter_nb_dept(@dept,@nb_res);
mysql> 
mysql> select @nb_res as 'Nombre de resultats';
mysql> 
mysql> drop procedure exporter_nb_dept;

Une procédure peut déclarer des variables et incorporer des conditions et des boucles. Voilà comment:
declare nom_variable type valeur_par_defaut;

mysql> declare mon_nb int default 0;

On peut donc utiliser des conditions if...then...else...end if etcase.

mysql> if mon_nb =0 then
mysql>     ...
mysql> elseif mon_nb=1 then 
mysql>     ...
mysql> else
mysql>     ...
mysql> end if;
mysql> 
mysql> case mon_nb
mysql>     when '0' then ...
mysql>     when '1' then ...
mysql>     else ...
mysql> end case;

On peut également mettre en place des boucles avec while et repeat. Il est possible de sortie prématurément d'une boucle while en utilisant le mot-clé leave. Il faut alors que la boucle soit nommée.


mysql> while mon_nb<3 do
mysql>     ...
mysql>     set mon_nb=mon_nb+1;
mysql> end while;;
mysql> 
mysql> repeat mon_nb
mysql>     ...
mysql>     set mon_nb=mon_nb+1;
mysql> until mon_nb>3 end repeat;
mysql> 
mysql> while1: while mon_nb>0 do
mysql>     set mon_nb=mon_nb+1;
mysql>     if mon_nb>5 then
mysql>         leave while1;
mysql>     end if;
mysql> end while while1;;