1. Vers les bases de données actives

Brevet de technicien supérieur
INFORMATIQUE DE GESTION
ORACLE 7 :
LES OPTIONS PROCÉDURALES

Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
Page 2 sur 36
Sommaire
1. VERS LES BASES DE DONNEES ACTIVES ............................... 3
1.1. Pourquoi des bases de données actives ? ....................... 3
1.2. Du bloc PL/Sql au sous-programme .............................. 4
2. LES PROCEDURES .................................................. 5
2.1. La création d'une procédure stockée ........................... 7
2.2. La modification d'une procédure .............................. 10
2.3. La suppression d'une procédure ............................... 10
2.4. L'exécution d'une procédure .................................. 10
2.5. La mise au point d'une procédure ............................. 11
3. LES FONCTIONS .................................................. 12
3.1. La création d'une fonction stockée ........................... 13
3.2. La modification d'une fonction ............................... 14
3.3. La suppression d'une fonction ................................ 14
2.4. L'exécution d'une fonction ................................... 14
4. LES PACKAGES ................................................... 15
4.1. Le principe .................................................. 15
4.2. La création d'un package ..................................... 17
4.3. L'encapsulation des sous-programmes dans des packages ........ 18
4.4. La surcharge (overloading) des sous-programmes ............... 21
4.5. L'appel des sous-programmes packagés ......................... 22
4.6. La modification d'un package ................................. 22
4.7. La suppression d'un package .................................. 22
5. LES TRIGGERS ................................................... 23
5.1. Définition ................................................... 23
5.2. Création d'un trigger ........................................ 24
5.3. Gestion des triggers ......................................... 26
5.3.1. Activation / Désactivation des triggers .................... 26
5.3.2. Suppression d’un trigger ................................... 26
5.3.3. Modification d’un trigger .................................. 26
5.4. Utilisation des triggers ..................................... 28
5.5. La gestion de l'intégrité référentielle par triggers ......... 29
5.5.1. Définition de l'interdiction avec AMC*Designor V4 .......... 30
5.5.2. Définition de la suppression en cascade .................... 31
5.5.3. Définition de la mise à nulle en suppression ............... 32
5.5.4. Définition de la vérification des clés étrangères .......... 33
5.6. La mise en place d'un audit par triggers ..................... 35
5.7. Différences entre triggers et contraintes d'intégrité
déclarative. ...................................................... 36

Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
Page 3 sur 36
1. Vers les bases de données actives
1.1. Pourquoi des bases de données actives ?
L'objectif est de disposer de systèmes de bases de
données qui intégrent la gestion de lois de comportement
dynamique sur les données.
Aujourd'hui, plusieurs SGBD commercialisés tel que RDB,
Oracle V7, Sybase et Ingres offrent des langages incluant ds
déclencheurs (appelés "triggers" en anglais) permettant
d'activer des procédures de contrôle sur la base de données.
Une raison de cette avancée des produits est la demande
de plus en plus pressante des utilisateurs à pouvoir décharger
les programmes d'application de la maintenance des contraintes
d'intégrité portant sur des données stockées.
Un atout majeur des langages comportant ces déclencheurs
est que justement la plupart des contraintes d'intégrité peut
s'exprimer sous forme de déclencheurs. Dés lors, c'est le SGBD
qui se charge automatiquement de garantir qu'elles seront
toujours respectées par les données de la base.
Les options procédurales d'Oracle V7 permettent de
construire quatres types d'objets applicatifs :
* les procédures,
* les fonctions,
* les packages,
* les triggers.
Ces objets écrits en PL/SQL sont définis et stockés une
seule fois dans la base.
Ils sont partageables par plusieurs applications et gérés
de façon centralisée pour toutes ces applications :
* mise au point,
* évolution et maintenance,
* utilisation ("Sql partagé").

Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
Page 4 sur 36
1.2. Du bloc PL/Sql au sous-programme
Les sous-programmes sont des blocs PL/SQL nommés.
Ils peuvent être paramétrés et être exécutés.
Le langage PL/SQL propose deux types de sous-programmes :
 les procédures et
 les fonctions.
Habituellement une procédure est utilisée pour réaliser une
action et une fonction pour réaliser un calcul.
Comme les blocs PL/SQL les sous-programmes se composent :
 d'une partie déclarative,
 d'une partie exécutable et
 d'une partie optionnelle.
Un sous-programme peut être déclaré dans un bloc PL/SQL, une
procédure, une fonction et un package.
Les sous-programmes peuvent être récursifs et surchargés.

Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
Page 5 sur 36
2. Les procédures
Une procédure est un sous-programme qui effectue un traitement
particulier.
Les procédures qui seront compilées et stockées dans la base de
données, comme tout autre objet (donnée) manipulé par le moteur du SGBD,
n'auront plus besoin d'être analysées une seconde fois à l'exécution. Un
gain de place en mémoire contribuera à cette amélioration de
performances car la procédure chargée en mémoire pour son exécution sera
partagée par tous les objets qui la demandent (applications)
Définition : Procédure
Une procédure est une unité de traitement qui peut contenir :
* des commandes Sql de manipulation des données (LMD),
* des instructions PL/Sql,
* des variables,
* des constantes,
* des curseurs,
* un gestionnaire d'erreurs.
Les procédures sont créées comme des objets de la base
appartenant à un utilisateur. Elles sont soumises donc à tous les
mécanismes de sécurité et de confidentialité.
Elles sont accessibles à travers les outils d'Oracle, tels que
Sql*Plus, Sql*Forms, Pro*C, etc.
Il en résulte que plusieurs applications peuvent faire appel à
la même procédure.
La notion de procédure a été conçue dans l'esprit de grouper un
ensemble de commandes SQL avec les instructions procédurales, pour
constituer une unité de traitement. Avec cette combinaison,
l'utilisateur peut ainsi résoudre des problèmes complexes tout en ayant
une flexibilité et une aisance dans son développement.