Oracle dispose d’un langage appelé PL/SQL pour compiler des procédures et des fonctions sur le serveur. Ces procédures et fonctions peuvent être appelées directement en SQL. Quand elles sont écrites correctement, elles permettent en général un gain de performances non négligeable, en plus d’être pratiques et agréables à utiliser. En gros, les procédures et fonctions sont un excellent moyen d’apporter une couche d’intelligence supplémentaire à votre serveur de bases de données, en lui permettant d’exécuter des actions complexes sans avoir recours à des scripts extérieurs. On économise donc le protocole de communication entre base de données et application. Et bien… C’est possible aussi sous MySQL !

Mini rappel : Procédure, ou fonction ?

Si vous hésitez entre créer une procédure stockée ou une fonction, rappelez vous bien que la seule différence entre les deux est qu’une fonction va chercher un résultat (quitte à passer par des tonnes d’étapes intermédiaires), alors qu’une procédure va faire une action. En gros, si vous voulez avoir une valeur de retour, il vous faut une fonction. Dans le cas contraire, préférez une procédure.

Dans quel cas les utiliser, et comment ?

Vous pouvez vous tourner vers les ProcStock (pour “procédures stockées”, le terme étant souvent utilisé aussi pour les fonctions) partout où vous exécutez des traitements de calculs lourds et/ou sur de gros volumes de données. L’avantage énorme est que vous n’aurez pas à rapatrier des resultsets de grande taille, pour les traiter en PHP (par exemple), puis les insérer en base: tout se fera directement en une seule requête très simple, qui appellera la fonction/procédure.
Les fonctions MySQL que vous allez définir s’utilisent exactement comme les fonctions prédéfinies (bien que celles-ci soient en général écrites en C et compilées avec le serveur… c’est faisable aussi pour un gain maximal en performances MySQL, mais ceci est une autre histoire), comme par exemple AVG (qui calcule une moyenne sur les valeurs d’un champ). Sans AVG (syntaxe: SELECT AVG(champ) FROM table), il faudrait récupérer les résultats concernés, les ajouter, puis les diviser par leur nombre : (1+5+6)/3 = 4. AVG fait ça toute seule et renvoie directement 4. Bien évidemment, il n’est pas bien grave d’avoir à récupérer 3 lignes. Mais avec 20.000 enregistrements, c’est différent, et les performances seront affectées, notamment en raison de l’utilisation de RAM nécessaire à l’exécution du script.
Il peut être aussi très intéressant d’utiliser des procédures et fonctions sur le serveur de bases de données quand plusieurs applications frontend dans des langages différents peuvent avoir à réaliser les mêmes actions: plutôt que d’écrire (et maintenir…) les actions communes en plusieurs langages, autant déporter leur exécution sur le serveur SQL, et demander aux clients de seulement interagir avec les fonctions stockées.

Application concrète

(je déplore mon manque d’imagination, qui m’oblige à chaque post à sortir des exemples totalement improbables…)
Imaginons un site de vente en ligne. Chaque jour est généré un rapport, enregistré en base, qui, en fonction du détail des ventes de la journée, calcule des indicateurs comme le chiffre d’affaires global et le panier moyen.
On aurait donc une table “commandes” avec un champ “montant” et un champ “date” (je simplifie, hein).
En PHP, sans procstock, il faut :

• Envoyer une requête qui prend les ventes de la journée passée
• Récupérer dans un tableau le détail des transactions
• Faire les calculs nécessaires (nombre de lignes, moyenne des montants, total des montants)
• Stocker ces résultats en base

Au bas mot, cela représente une vingtaine de lignes de PHP, avec deux communications depuis/vers la base de données, une boucle qui parse le tableau, des variables temporaires…
Dans cette situation, c’est d’une procédure stockée que nous avons besoin. On ne récupère pas les infos (pas de valeur retournée) mais on les stocke en base. Dans le cas contraire, on aurait créé une fonction.
La procédure stockée en question, que nous appellerons “genere_rapport”, va s’occuper de tout cela pour nous. Voici son code :

MySQL devrait répondre “Query OK, 0 rows affected (0.01 sec)” pour signaler que la procstock a bien été créée.
Première remarque, on change le délimiteur de fin de commande MySQL. La création d’une procédure/fonction doit se faire en une seule instruction MySQL, même si la procstock comporte plusieurs instructions à exécuter. J’avoue que ça surprend au début mais c’est un coup à prendre. Pensez bien à remettre le délimiteur normal (le point-virgule) après la création de votre procstock.
Ensuite, on lance la création de la procédure. On déclare d’abord les variables dont on aura besoin pour stocker les données (même si ici, on aurait pu directement faire le SELECT dans une sous-requête de l’INSERT, mais ce n’est pas le but): la liste des types disponibles est la même que les types des champs. On utilise ensuite SELECT INTO avec le nom de nos variables pour dire à MySQL dans quelle variable stocker quelle valeur, variables qu’on utilise ensuite dans une requête INSERT classique pour stocker le rapport.
Pour appeler la procédure, on fera CALL genere_rapport();. Pour une fonction, ça sera SELECT nom_de_la_fonction();

Conclusion

Penchez-vous sur vos applications PHP, il y a certainement des tas d’actions que vous pourrez déporter vers des procédures stockées. Les gains de performances devraient être rapidement ressentis, pour un effort d’apprentissage minimal.
Dans un prochain post, nous irons plus loin avec les procédures stockées, en utilisant notamment des paramètres, des curseurs, des handlers, et autres joyeusetés. Le plus difficile sera de trouver un exemple intéressant…

Ce post vous a été utile ? Re-Twittez le ! ReTwittez ce post

MySQL