4. Procédures stockées [3]

PLAN DU COUR
A. Les scripts SQL.
- Les variables ;
- les structures de contrôle ;
- les conditions ;
B. Les curseurs.
C. Les procédures et les fonctions.
- Programmation des procédures stockées sur le SGBD.
- Appel des fonctions à partir de requêtes SQL.
- Les EXCEPTIONS : prédéfinies et utilisateur.
D. Créer des packages sur le SGBD
- Description du formalisme à respecter.
- Présentation des packages standard.
- Programmation des packages.
E. Programmer des déclencheurs
- Événements qui déclenchent les triggers.
- Formalisme à respecter.
- Programmation des Triggers.
- Test du trigger et correction des erreurs.

Introduction
Pourquoi programmer côté serveur de bases de données ? [1]
Cette programmation a de multiples intérêts :
 La performance
 Le non redondance de code
 Le partage du code avec des applications de nature différente (Web, PDA,
Client/serveur, Reporting, etc.)
La performance
Des traitements qui manipulent les données sont naturellement plus rapides quand ils
s'exécutent dans la base de données.
En effet les gestionnaires de cache et de verrouillage peuvent alors donner tout leur potentiel,
par ailleurs, les aspects réseau sont bien sûr ignorés dans ce cas.
Les temps de réponse a été divisé de manière drastique : de 2 heures à 50 secondes par
exemple.
Le non redondance de code
C’est, au même titre que la redondance des données, un des ennemis du bon développeur.
La centralisation dans le moteur de bases de données de vues et procédures stockées permet
de diminuer cette redondance au minimum et d'augmenter ainsi la vitesse de développement
et la rapidité de maintenance.
A noter que SQLServer2005 avec son intégration du CLR permet d'aller encore plus loin dans
cette chasse à la redondance.
Le partage du code
Il est quasiment obligatoire de travailler avec des logiciels multiples et donc des appels aux
bases de données issus de différentes sources : Un générateur d'états, un serveur Web, une
application client léger, une application client lourd,...
Cette centralisation va là encore, permettre le partage de tout ce code entre différentes
applications.
Que voit-on dans ce cours :
C'est un cours orienté développement et il couvre donc tous les aspects liés à la
programmation du moteur de bases de données; Le langage TRANSACT SQL est décrit dans
tous ces aspects. Tous les types de requêtes sont également étudiés.

De plus un des apports majeurs de 2005 est SQL server a trop avancé: la gestion des erreurs
par try ... catch ; Les développeurs sérieux vont enfin pouvoir écrire du code robuste, avec,
par exemple, la mise en place de pré-conditions dans les procédures stockées.
Les fonctions étendues sont complètement décrites, avec en particulier, l'étude des fonctions
de types table qui sont très utile pour encapsuler des fonctions avancées.
Les triggers sont également étudiés : une nouveauté intéressante de 2005 concerne les triggers
DDL qui sont capables de surveiller les mises à jour du dictionnaire (create table, create
proc,...) je les utilise par exemple pour gérer de manière efficace et automatique la sécurité
des objets dans mes bases de données.
En conclusion
Je lis sur certains forums que le développement côté serveur serait obsolète et que les
nouvelles technologies amèneraient des solutions de remplacement.
Je pense qu'il n'en est rien car de plus en plus d'applications utiliseront des machines à très
faibles capacités de traitement et de stockage : les PDA, les téléphones numériques.
Par ailleurs le partage des données entre application multiples et hétérogènes devient une
évidence : serveur Intranet + Internet + client serveur 2 tiers, + PDA +....
A l'inverse, l'époque où les applications Internet étaient un monde à part me parait révolue et
on va donc bien devoir développer des applications rapides, robustes et à haute disponibilité.
D'ailleurs, la question que je me pose maintenant de plus en plus pour un logiciel est :
où vais-je développer cette partie ? Avec quel composant ?
Et il est clair que, pour une partie manipulant beaucoup de données, la réponse est souvent
dans la base de données.
A l'inverse des interactions homme-machine seront bien évidemment à l'aise côté client
(JavaScript, VBScript,...)
Donc un bon développeur doit utiliser : le bon outil au bon moment.
Bref historique : [2]
En juin 1970 Edgar Frank Codd publia l'article « A Relational Model of Data for Large
Shared Data Banks » ("Un modèle de données relationnel pour de grandes banques de
données partagées") dans la revue Communications of the ACM (Association for Computing
Machinery). Ce [modèle relationnel] basé sur la logique des prédicats du premier ordre a été
rapidement reconnu comme un modèle théorique intéressant pour l'interrogation des bases de
données, et a inspiré le développement du langage Structured English Query Language
("SEQUEL") (langage d'interrogation structuré en anglais), renommé ultérieurement SQL.
Développée chez IBM en 1970 par Donald Chamberlain et Raymond Boyce, cette première
version a été conçue pour manipuler et éditer des données stockées dans la base de données

relationnelle à l'aide du système de gestion de base de données IBM System R. Le nom
SEQUEL, qui était déposé commercialement par l'avionneur Hawker Siddeley pour un
système d'acquisition de données, a été abandonné et contracté en SQL en 1975. SQL était
censé alors devenir un élément clé du futur projet FS.
En 1979, Relational Software, Inc. (actuellement Oracle Corporation) présenta la première
version commercialement disponible de SQL, rapidement imité par d'autres fournisseurs.
SQL a été adopté comme recommandation par l'Institut de normalisation américaine (ANSI)
en 1986, puis comme norme internationale par l'ISO en 1987 sous le nom de ISO/CEI 9075 -
Technologies de l'information - Langages de base de données - SQL.
La norme internationale SQL est passée par un certain nombre de révisions :
Année
Nom
Appellation
Commentaires
1986
ISO/CEI 9075:1986
SQL-86 ou
SQL-87
Édité par l'ANSI puis adopté par l'ISO en 1987.
1989
ISO/CEI 9075:1989
SQL-89 ou
SQL-1
Révision mineure.
1992
ISO/CEI 9075:1992
SQL-92 ou
SQL2
Révision majeure.
1999
ISO/CEI 9075:1999
SQL-99 ou
SQL3
Expressions rationnelles, requêtes récursives,
déclencheurs, types non-scalaires et quelques
fonctions orientées objet (les deux derniers points
sont quelque peu controversés et pas encore
largement implémentés).
2003
ISO/CEI 9075:2003
SQL:2003
Introduction de fonctions pour la manipulation
XML,« window functions », ordres standardisés et
colonnes avec valeurs auto-produites (y compris
colonnes d'identité).
2008
ISO/CEI 9075:2008
SQL:2008
Ajout de quelques fonctions de fenêtrage (ntile,
lead, lag, first value, last value, nth value),
limitation du nombre de ligne (OFFSET / FETCH),
amélioration mineure sur les types distincts,
curseurs et mécanismes d'auto incréments.
Comme toute norme internationale publié par l'ISO, ISO/CEI 9075 est disponible à l'achat sur
le site de cette organisation : http://www.iso.org. Le dernier brouillon (working draft) de la
norme est disponible à cette adresse http://www.wiscorp.com/sql_2003_standard.zip sur le
site de http://www.wiscorp.com/about_wiscorp.html.
Rappel sur des définitions : [2]
SQL se décompose en 5 parties, à savoir :
 Ordres LDD (langage de définition des données, ou DDL, Data Definition Language) :
permet de modifier la structure de la base de données ;

Exemples :
- Création d'une table :
CREATE TABLE table1 ( colonne1 INTEGER,
colonne2 INTEGER,
colonne3 DATE,
colonne4 DATE);
- Modification d'une table :
ALTER TABLE table1 ADD COLUMN colonne5 INTEGER NULL;
ALTER TABLE table1 DROP COLUMN colonne5;
- Suppression d'une table :
DROP TABLE table1;
- Ajout d'une contrainte sur une table :
ALTER TABLE table1
ADD CONSTRAINT ck_jour
CHECK (colonneJour IN ('Lundi', 'Mardi', 'Mercredi', 'Jeudi',
'Vendredi', 'Samedi', 'Dimanche'));
 Ordres LMD (langage de manipulation des données, ou DML, Data Manipulation
Language) : permet de consulter / modifier le contenu de la base de données ;
Exemples :
- Requête de base :
SELECT prenom, telephone
FROM entrants
CROSS JOIN sortants
WHERE nom = 'Dupont';
- Requête plus générique :
SELECT name, service
FROM employees
WHERE statut='stagiaire'
ORDER BY name;
Exemples de requêtes pour afficher les lignes d'une table TABLE1 de clé primaire colonne1
non présents dans une seconde table TABLE2 :