Procédures stockées

Une procédure stockée est un groupe d'instructions SQL qui forme une unité logique et effectue une tâche particulière. Les procédures stockées sont utilisées pour encapsuler un jeu d'opérations ou de requêtes à exécuter sur un serveur de base de données. Par exemple, les opérations sur une base de données d'employées (embauche, augmentation etc...) peuvent être codées en procédures stockées puis exécutées. Elles peuvent être compilées puis exécutées avec différents paramètres et différents résultats, et elles possèdent plusieurs combinaisons d'entrées, de sorties et de paramètres entrées/sorties.
Les procédures Stockées sont supportées par la plupart des SGBD, mais il y a quand même quelques variations dans leur syntaxe. Pour cette raison, nous vous montrerons un simple exemple de ce à quoi une procédure stockée ressemble et comment l'invoquer avec JDBC, mais cet exemple n'est pas fait pour être exécuté.

11. Instructions SQL pour créer des procédures Stockées.

Cette section traite des procédures stockées très simplistes sans paramètres. Même si les procédures stockées effectuent généralement des taches bien plus complexes que celles présentées dans cet exemple, il sert tout de même à illustrer certains points à leurs propos. Comme nous l'avons dit précédemment, la syntaxe est différente pour chaque SGBD. Par exemple, certains utilisent begin . . . end ou d'autres mots clés pour indiquer le commencement et la fin de la définition de la procédure. Dans certain SGBD, cette instruction SQL créera une procédure stockée :

create procedure SHOW_FOURNISSEURS
as
  SELECT FOURNISSEURS.NOM_FO, CAFE.NOM_CAFE
        FROM FOURNISSEURS, CAFE
        WHERE FOURNISSEURS.FO_ID = CAFE.FO_ID
       order by NOM_FO

Le code qui suit met l'instruction SQL dans une chaîne de caractères et l'assigne à la variable createProcedure, que nous utiliserons plus tard :

   
 String createProcedure =
       "create procedure SHOW_FOURNISSEURS" +
        "as" +
        "select FOURNISSEURS.NOM_FO, CAFE.NOM_CAFE" +
        "from FOURNISSEURS, CAFE" +
        "where FOURNISSEURS.FO_ID = CAFE.FO_ID" +
        "order by NOM_FO";

Le fragment de code suivant utilise l'objet Connexion conn pour créer un objet Statement, qui sera utilisé pour envoyer l'instruction SQL afin de créer la procédure stockée sur la base de données :

  Statement stmt = conn.createStatement();
        stmt.executeUpdate(createProcedure);

La procédure SHOW_FOURNISSEURS sera compilée et stockée dans la base de données comme un objet pouvant être appelé de façon similaire à l'appel d'une méthode.

11.1 Appeler une procédure stockée avec JDBC

JDBC vous permet d'appeler une procédure stockée sur la base de données depuis une application écrite en Java. La première étape est de créer un objet CallableStatement. Comme avec les objets Statement et PreparedStatement, ceci est fait avec une connexion ouverte. Un objet CallableStatement contient l'appel d'une procédure, il ne contient pas la procédure elle-même. La première ligne de code ci-dessous crée un appel à la procédure stockée SHOW_FOURNISSEURS en utilisant la connexion conn. La partie qui est entre accolade est la syntaxe pour la procédure stockée. Quand le driver rencontre "{call SHOW_FOURNISSEURS}", il traduira cette syntaxe en SQL natif utilisé par la base de données pour appeler la procédure stockée nommée SHOW_FOURNISSEURS : 

 CallableStatement cs = conn.prepareCall("{call SHOW_FOURNISSEURS}");
        ResultSet rs = cs.executeQuery();

Notez que la méthode pour exécuter cs est executeQuery car cs appel une procédure stockée qui contient une requête et produit un resultset. Si la procédure avait contenue une mise à jour ou une des instructions DDL, la méthode executeUpdate aurait été utilisée. Comme c'est parfois le cas, une procédure stockée contient plus d'une instruction SQL, qui pourrait produire plus d'un résultset, plus d'une mise à jour, ou une combinaison de result set et de mise à jour. Dans ce cas, lorsqu'il y a de multiples résultats, la méthode execute devra être utilisé pour exécuter CallableStatement.
La classe CallableStatement est une classe dérivée de PrepareStatement, donc un objet CallableStatement peut avoir des paramètres d'entrées tout comme l'objet PreparedStatement. En plus, un objet CallableStatement peut avoir des paramètres de sorties ou des paramètres qui sont fait pour l'entrée et la sortie. Les paramètres INOUT et la méthode execute sont rarement utilisées