[Débat] Utiliser les Procédures stockées ou pas ? Avantages et inconvénients ? [Résolu]

[pline]

Bonjour,

pour moi, soit on utilise les procédures stockées (et les triggers), soit on ne les utilise pas du tout. Si on les utilise un peu, on ne retire aucun avantage.

Si on ne les utilise pas, on utilise le SGBDR comme une simple prothèse mémoire. Cela signifie que l'on veut s'affranchir du langage spécifique à un SGBD. Cela part d'une bonne intention mais il faut se rendre compte de ce que cela signifie ---> il faudra également que les requêtes accédant à ce SGBDR ne contiennent pas de traces de ce langage. Par exemple, la gestion des dates sera alors impossible car spécifique à chaque SGBDR, l'utilisation des triggers pour les contraintes "compliquées" ne pourra pas être effectuée obligeant à mettre ces contraintes dans le code de (ou des) application(s), ce qui est loin d'être sûr, .... Toute évloution du modèle demandera également une reprise de toutes les parties du code qui sont impactées (ce qui demandera une cartographie très pointue voire "pointillarde").

Si on les utilise. Effectivement, on se lie au SGBDR mais en construisant les procédures stockées de façon intelligente (par exemple en faisant ressortir dans des fonctions tout particularisme du langage), on limite cette liaison. De plus, il est important que seuls les accès aux données soient présents dans les procédure stockées. les traitements doivent rester dans le code des applications. On peut ainsi utiliser toute la force d'un SGBDR et toute évolution de modèle n'impactera pas le code existant. Les procédures tockées joueront le rôle d'interface entre les traitements et les données comme peut le faire un framework tel que hibernate. Ces interfaces permettent aussi l'évolution du SGBDR sans que cela impacte le code.

Par expérience personnelle, j'ai remarqué qu'on changeait plus de langage de programmation que de SGBDR. Je crois que la documentation qu'on trouve contre les procédures stockées vient de personnes qui développent les SI sans avaoir à les administrer et les faire évoluer. Si j'étais une de ces personnes, je ne voudrais pas non plus entendre parler de procédures stockées mais pour maîtriser le SI et le faire évoluer, je pense qu'il est obligatoire d'utiliser les procédures stockées et dans tous les cas.

Voilà, j'espère vos réactions (pas trop violentes).

[Vladislav IV]

Bonsoir !

Envoyé par pline

tout particularisme du langage

Hmm... Tu voulais dire "toute particularité" ?

Pour ma part, la chose est simple :

• Des vues pour l'accès aux données. Les développeurs n'ont pas à faire de requêtes (juste SELECT *), et on limite le couple entre la structure de la base et les applications.
• Des fonctions et procédures stockées pour toute opération. Mêmes bénéfices que pour les vues.
• Des triggers pour contrôler la cohérence des entrées. Les développeurs n'ont encore une fois pas de requêtes à faire, et la base peut lever une exception avec message personnalisé, que les applications n'auront qu'à afficher pour avertir l'utilisateur.

Avec ça, opérer la base ne demande pas à opérer les applications (sauf changement extrême, mais alors aucune appli n'est plus valide ), et les applications se limitent à ça :

1. Piocher dans les vues,
2. Inscrire les données saisies,
3. Afficher l'éventuel message d'erreur généré par les triggers.

Si c'est pas la classe, je sais pas ce que c'est...

[pline]

Je partage tout à fait ton utilisation des messages d'erreurs qui sont aussi un outil pour séparer les données et les traitements.

je crois également que les vues peuvent être remplacées par des procédures stockées mais l'idée reste la même.

Effectivement, c'est la classe.

[Vladislav IV]

Envoyé par pline

je crois également que les vues peuvent être remplacées par des procédures stockées mais l'idée reste la même.

Attention, ce n'est pas ce que j'ai dit : les vues servent à réunir les données dont les applications auront besoin (affichage des produits, profils utilisateurs, statistiques, etc.). Les procédures stockées serviraient plutôt à lancer une opération sans avoir à programmer de requête dans les applications, par exemple : réinitialiser le mot de passe de l'utilisateur X. Plutôt que d'avoir à implémenter une requête "update password from utilisateur..." dans l'application, on appelle "call razPassword(X)" et la procédure se charge du traitement. Si demain on change la structure de la base, on met à jour la procédure stockée, mais les applications ne voient aucun changement.

Deux avantages :

1. Les aplications sont relativement indépendantes de la structure de la BDD, on peut donc à loisir changer cette structure sans perturber les applications.
2. La sécurité est accrue, en effet on ne peut pas déduire le schéma de la base à partir des applications (puisqu'elles n'ont accès qu'à des vues et procédures stockées).

[pline]

Dans ce cas là, pourquoi ne pas utiliser les procédures stockées à la place des vues ?

[Vladislav IV]

Ce n'est pas la même chose. Une vue est une aggrégation de colonnes, en provenance de différentes tables, pour former une "table virtuelle" en vue (pardon) d'être consultée.

Exemple : j'ai une table de vendeurs, une table de produits, je peux créer une vue qui affiche pour chaque vendeur le nombre de chaque produit qu'il a vendus. Au lieu de mettre la requête dans l'application, je crée une vue avec.

Les fonctions stockées peuvent être utilisées dans n'importe quelle requête. Imagine une fonction ventes(numvendeur, numproduit) qui retourne la quantité vendue par un vendeur donné, d'un produit donné. Je peux utiliser cette fonction dans ma vue pour en simplifier l'écriture. Mais l'application peut également appeller cette fonction, si besoin est.

Les procédures stockées permettent d'effectuer un traitement. Par exemple, on peut écrire une procédure "razVentes()" qui remet à zéro tous les compteurs de ventes, pour une nouvelle année par exemple. Au lieu de laisser l'application modifier les champs, elle appelle cette procédure.

[pline]

D'accord pour tout cela mais maintenant, imagine que la requête que tu définies dans ta vue, tu la mets dans une procédure stockée.

Tu me diras alors que tu ne pourras pas faire de clause WHERE avec cette procédure stockée. Mais rien ne t'empêche de rajouter les paramètres nécessaires à ta procédure stockée.

Tu peux également rajouter d'autres procédures stockées qui auraont la même requête mais qui auront des paramètres différents. Cela te permettra d'identifier les clauses WHERE les plus "utiles" et ainsi de créer les bons "index".

Des vues n'empêcheront pas de faire des requêtes qui sont gourmandes en ressources et qui n'ont pas été identifiées comme un besoin.

[Vladislav IV]

Tu n'obtiendrai rien : une procédure stockée ne retourne pas de résultat. Enfin, elles peuvent renvoyer des valeurs au travers de paramètres sortants (OUT), mais qui sont monovalués. Donc même en indiquant un paramètre sortant par colonne, ce qui est possible, on ne récupère qu'un seul tuple. Au contraire des vues, qui retournent tous les tuples retournés par la requête.

Le problème est le même avec les fonctions : même en considérant que le SGBD prenne les tuples comme types de données, une fonction ne pourrai en retourner qu'un seul.

Peut-être que certains SGBDR permettent de faire des vues paramétrées ? Mais en restant général, je ne crois pas que cela soit possible.

Edit : Oracle permet cela. On définit un ensemble de variables, que la vue utilise dans sa clause WHERE. Avant de regarder la vue (hmm...), on donne une valeur à ces variables, et elles se retrouvent dans la vue.

Envoyé par pline

Des vues n'empêcheront pas de faire des requêtes qui sont gourmandes en ressources et qui n'ont pas été identifiées comme un besoin.

Dans tous les cas, une vue doit répondre à un besoin précis de données. Et que la requête soit dans l'application, dans une vue ou dans une procédure, je ne pense pas que cela change quoi que ce soit au niveau des performances : la requête sera exécutée de la même manière, sur autant de données.

Envoyé par pline

Tu peux également rajouter d'autres procédures stockées qui auraont la même requête mais qui auront des paramètres différents.

Pour coller à ce que j'ai dit, oui, tu peux très bien créer plusieurs vues semblables, avec des clauses WHERE différentes, pour appliquer des filtres plus fins selon les besoins. Ou alors, faire une vue simple, et appliquer des filtres lorsque tu y fais appel. Mais je pense que cela engendrerai un léger surcoût.

[SQLpro]

une procédure stockée ne retourne pas de résultat.

Faux. La norme SQL considère qu'une routine (c'est le nom générique des procédures et fonctions) doit être capable de retourner un jeu de ligne consécutif à un SELECT.

C'est d'ailleurs ce que font la plupart des SGBDR comme Oracle ou SQL Server.

Si on ajoute en outre les trigger INSTEAD OF on peut alors greffer des INSERT DELETE et UPDATE sur des vues qui ne peuvent être en principe mise à jour (par exemple cue contenant des jointures) et déporter le traitement vers un procédure stockée qui fait un genre de mapping RO.
Seul inconvénient les trigger INSTEAD OF ne sont pas encore normalisés.

Bref si l'on veut un développement propre :
1) n'utiliser que des vues pour les SELECT INSERT, UPDATE et DELETE
2) ajouter des procédures stockée pour gérer le mapping RO
3) écrire des triggers INSTEAD OF

A +