stored-procedure et static sql

Bonjour,


j'ai 2 petite question donc je n'ai pas la réponse si quelqu'un peut m'aider s'il vous plait, merci.


1) Pourquoi certains utilisateurs de SGBD jugent-ils les stored-procedures indispensables et d'autres les jugent à éviter?

je sais que les utilisateurs d'oracle conseille l'utilisation des procedures la raison je ne sais pas pourquoi ,concernant les autres sgbd aucune idée.

2) Donnez un exemple de programme utilisant le embedded-SQL que nous ne pourrions pas écrire en nous contentant de STATIC SQL.

ici je n'ai aucune idée de ce que c'est le static sql, les liens que je trouves sont en anglais et je ne comprends pas un mot.

J'aurais tendance à dire que :
1/ Les goûts et les couleurs, ça se discute pas. Un peu comme ceux qui préfèrent le villageoise à un grand cru.
Plus exactement, il est très facile de faire de la 5 lettres avec une procédure stockée (par exemple, des curseurs, qui sont inutiles dans 99% des cas)

2/ Votre question est spécifique à un SGBD ou un cours. Pas de réponse absolue donc.
Sous SQL Server, par exemple, une colonne calculée ne peut contenir autre-chose que des instructions simples : donc soit ça se résume à a + b, soit on doit passer par un appel à une fonction.

Après, pour ce point, c'est un rare exemple, qui concerne une limitation d'un SGBD en particulier, sur une fonctionnalité qui n'est pas normée (les colonnes calculées n'existent pas dans la norme SQL).
Le SQL étant "turing-complet", il n'y a pas grand chose d'impossible à faire avec, du moment qu'on ne se limite pas à la norme de 89 (fonctions de fenêtrage, common table expression, etc.).

La seule chose que je vois, c'est l'encapsulation dans une instruction unitaire de traitements comportant plusieurs étapes.
Par exemple, en un seul appel à une procédure stockée, cette dernière sera capable de faire des création/mises à jour/suppression dans différentes tables, le tout dans des transactions avec des gestions d'exceptions.

Cependant, une bonne pratique de programmation, c'est de ne jamais exposer les tables d'une base de données au programme consommateur.
On doit donc passer par des vues/fonctions pour la consultation, et des vues/procédures stockées pour les mises à jour : ceci permet de faire évoluer le modèle des données sans devoir réécrire tous les programmes.
Mais à nouveau, on peut faire du CRUD sur une vue, aussi complexe soit-elle, en y apposant des triggers. Donc les procédures stockées en tant que telle perdent de leur intérêt.

merci pour votre réponse, concernant le static sql,quelqu'un pourrait m'aider s'il vous plait.

1) les procédures stockées sont beaucoup plus intéressantes pour des raisons de performances et de sécurité. Par exemple l'encapsulation de différentes commandes SQL dans une même procédure évite les round trip (aller et retour des trames réseau) entre l'application et le serveur. Il n'est pas rare d'avoir des gains de 20x et même jusqu'à 1000 fois lorsque l'on utlise de mauvais outils comme Hibernate !
Au niveau sécurité, on peut mieux contrôler dans une procédures les problématique d'injection de code, et on peut faire exécuter des procédures sous des contextes de sécurité différent de ce que l'utilisateur applicatif à droit.

2) si cette question vient de votre prof il devrait se recycler. Le langage SQL étant Turing complet depuis l'introduction de la récursivité avec la norme SQL de 1999, il n'existe aucun problème mathématique qui ne soit pas à la portée de résolution du langage SQL.

A +

Citation:

---

Envoyé par SQLpro

2) si cette question vient de votre prof il devrait se recycler. Le langage SQL étant Turing complet depuis l'introduction de la récursivité avec la norme SQL de 1999, il n'existe aucun problème mathématique qui ne soit pas à la portée de résolution du langage SQL.

---

Sans doute, mais à tempérer en fonction du contexte : ceux qui utilisent par exemple Access ou MySQL, n'ont malheureusement pas accès à toutes les fonctionnalités du langage SQL, et notamment aux requêtes récursives :aie:. Je serai très intéressé par la réponse du professeur à cette question !

En même temps, Access, y'a pas de procédure stockées, et MySQL... comment dire... c'est pas un SGBDR donc à partir de là... ;)

Sinon, j'ai une question à propos du "SQL static".
Qu'est-ce qu'on entend exactement par là ?

Lorsque j'interroge la MSDN, j'obtiens ça :
https://msdn.microsoft.com/en-us/lib...(v=vs.85).aspx

Donc pour moi, c'est du SQL "embarqué" dans un programme, ce qui est, généralement :
- des requêtes unitaires
- la plupart du temps littérales

La réponse de SQL Pro laisse à penser que pour lui il s'agit d'un script complet SQL, contenant éventuellement des curseurs, des transactions, des boucles, etc. c'est à dire des éléments qu'ont ne trouve généralement pas dans du SQL embarqué.

Qu'en est-il au juste ?

Car pour faire simple, en partant du principe qu'en parlant de SQL static, on parle de requêtes unitaires :
- tout ce qui est faisable avec une requête SQL, aussi complexe soit-il, doit être fait avec une requête SQL plutôt qu'un lot SQL complet (que ce soit un script ou une procédure).

Dans l'autre cas, entre exécuter un script complet SQL et une procédure stockée, je ne vois absolument pas le moindre intérêt à passer par un script SQL : la procédure stockée (qui ne contiendra rien d'autre qu'un copier/coller du script) sera infiniment plus performante qu'un script, ne serait-ce que pour la raison évoquée par SQL Pro : moins d'aller-retour à chaque instruction, pas de données qui transitent entre le serveur et le client, etc.

Citation:

---

2) Donnez un exemple de programme utilisant le embedded-SQL que nous ne pourrions pas écrire en nous contentant de STATIC SQL.

---

En fait, cette question est en totale contradiction avec la définition de la MSDN.

N'y aurait-t-il pas confusion entre "embedded-SQL" et "dynamic SQL" ?

A ce moment, un moteur de recherche multi-critère se basera généralement sur du SQL Dynamique plutôt que sur du SQL Static.


En effet, d'une recherche à l'autre, tous les critères et opérateurs peuvent changer, et par conséquent, couvrir tous les cas en SQL Static est si ce n'est impossible, particulièrement laborieux.

Exemple, un formulaire de recherche de patients :

NOM
PRENOM
DATE NAISSANCE
NUMERO SECU (pas bien, la loi interdit de faire des recherche dessus, donc on a grisé le champ)
PATHOLOGIE

Je recherche tous les "Mr TOTO" qui ont eu un "Rhume".

Si c'est du SQL Static, alors la requête sera :

Code:

---

1 2 3 4 5 6 7

select * from patient where nom = @nom and exists ( select * from consultation where id_patient = patient.id and diagnostique = @pathologie )

---

Maintenant, je veux toutes les ALICE qui sont nées le 13 juillet 1992 :

Code:

---

1 2 3 4 5

select * from patient where prenom = @prenom and datenaissance = @datenaissance

---

Les deux requêtes ne se ressemblent pas du tout.
Donc si on veut faire une requête statique qui couvre tous les cas :

Code:

---

1 2 3 4 5 6 7 8 9 10

select * from patient where (nom = @nom or @nom is null) and (prenom = @prenom or @prenom is null) and (datenaissance = @datenaissance or @datenaissance is null) and (exists ( select * from consultation where id_patient = patient.id and diagnostique = @pathologie ) or @pathologie is null)

---

Résultat, la requête est incompréhensible et contre-performance (lecture inutile de la table des consultations quand on ne recherche pas de pathologie, et index inutilisables dans la table des patients).

En revanche, avec du SQL Dynamique :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

StringBuilder sb = new StringBuilder(); sb.Append("select * from patient where 1 = 1"); if (nom != string.Empty) { sb.Append(" and nom = @nom"); cmd.CreateParameterWithValue("nom", nom); } if (prenom != string.Empty) { sb.Append(" and prenom = @prenom"); cmd.CreateParameterWithValue("prenom", prenom); } if (datenaissance != null) { sb.Append(" and datenaissance = @datenaissance"); cmd.CreateParameterWithValue("datenaissance", datenaissance ); } if (pathologie != string.Empty) { sb.Append("and exists (select * from consultation where id_patient = patient.id and diagnostique = @pathologie)"); cmd.CreateParameterWithValue("pathologie", pathologie); }   cmd.CommandText = sb.ToString();

---

Le code du programme est un peu plus complexe, mais les requêtes générées sont propres et optimisées.

Attention aux injections SQL dans ce cas !

Quant à faire du SQL Dynamique dans une procédure stockée, aucun souci, au détail près que personnellement je trouve ça hideusement crade, mais là on parle de goûts et de couleurs.

Toujours pour en revenir au contexte de la question qui est trop vague, si on parle de "procédure stockée" comme d'"embedded sql", alors un autre argument en faveur et en défaveur l'un de l'autre :

Si je souhaite supprimer une commande et toutes ses lignes, en procédure stockée, je vais pouvoir gérer dans un unique appel la suppression des lignes puis de l'entête.

Avec du Embedded SQL, en cas de défaillance, vu que jamais personne n'utilise de transactions, si pour une raison X l'entête ne veut pas se supprimer (par exemple, quelqu'un qui est sur la commande rajoute une ligne pendant que je supprimer les autres), alors je vais vider ma commande, mais garder l'entête dans la base.
Certains ERP, qui n'utilisent pas de clés étrangères, vont même réussir à laisser des lignes orphelines.

En contre-partie, avec une procédure stockée, on va pouvoir gérer les erreurs, mais pas gérer d’interactivité.
Si pendant la suppression des lignes (qu'on charge ligne à ligne comme un gros porc) on se rend compte qu'une ligne a été livrée, on est capable, avec du embedded SQL, de demander à l'utilisateur si on veut vraiment continuer à supprimer une commande livrée, ou si on annule tout. Avec la procédure stockée, on sera obligé d'annuler la transaction, poser la question à l'utilisateur, puis relancer la procédure avec un paramètre pour forcer la suppression : soit deux fois le bout.

Il existe bien d'autres meilleurs exemples, c'est juste vendredi après-midi ;)