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
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.
2.2.
2.3.
2.4.
2.5.
La création d'une procédure stockée ........................... 7
La modification d'une procédure .............................. 10
La suppression d'une procédure ............................... 10
L'exécution d'une procédure .................................. 10
La mise au point d'une procédure ............................. 11
3. LES FONCTIONS .................................................. 12
3.1.
3.2.
3.3.
2.4.
La création d'une fonction stockée ...........................
La modification d'une fonction ...............................
La suppression d'une fonction ................................
L'exécution d'une fonction ...................................
13
14
14
14
4. LES PACKAGES ................................................... 15
4.1.
4.2.
4.3.
4.4.
4.5.
4.6.
4.7.
Le principe ..................................................
La création d'un package .....................................
L'encapsulation des sous-programmes dans des packages ........
La surcharge (overloading) des sous-programmes ...............
L'appel des sous-programmes packagés .........................
La modification d'un package .................................
La suppression d'un package ..................................
15
17
18
21
22
22
22
5. LES TRIGGERS ................................................... 23
5.1. Définition ...................................................
5.2. Création d'un trigger ........................................
5.3. Gestion des triggers .........................................
5.3.1. Activation / Désactivation des triggers ....................
5.3.2. Suppression d’un trigger ...................................
5.3.3. Modification d’un trigger ..................................
5.4. Utilisation des triggers .....................................
5.5. La gestion de l'intégrité référentielle par triggers .........
5.5.1. Définition de l'interdiction avec AMC*Designor V4 ..........
5.5.2. Définition de la suppression en cascade ....................
5.5.3. Définition de la mise à nulle en suppression ...............
5.5.4. Définition de la vérification des clés étrangères ..........
5.6. La mise en place d'un audit par triggers .....................
5.7. Différences entre triggers et contraintes d'intégrité
déclarative. ......................................................
23
24
26
26
26
26
28
29
30
31
32
33
35
36
Page 2 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
1. Vers les bases de données actives
1.1. Pourquoi des bases de données actives ?
L'objectif est de disposer
données qui intégrent la gestion
dynamique sur les données.
de
de
systèmes de bases de
lois de comportement
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
construire quatres types d'objets applicatifs :
* les procédures,
* les fonctions,
* les packages,
* les triggers.
permettent
de
Ces objets écrits en PL/SQL sont définis et stockés une
seule fois dans la base.
de
*
*
*
Ils sont partageables par plusieurs applications et gérés
façon centralisée pour toutes ces applications :
mise au point,
évolution et maintenance,
utilisation ("Sql partagé").
Page 3 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
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.
Page 4 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
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.
Page 5 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
Les procédures sont utilisées pour augmenter la productivité.
Elles servent également à gérer la sécurité et l'intégrité des données
et à augmenter les performances.
1. Du point de vue de la sécurité, le développeur peut autoriser
l'accès à certaines tables seulement à travers des procédures. Les
utilisateurs bénéficiant du privilège d'accès aux tables à travers les
procédures ne possèdent aucune autorisation d'accès à ces mêmes tables
en dehors des procédures.
2. Au niveau de l'intégrité, les procédures développées et
testées assurent la même fonctionnalité indépendamment de la partie
appelante. De ce fait, la recompilation d'une procédure en cas de
modification n'exige pas la recompilation de l'ensemble du code de
l'application.
3. Pour les performances les facteurs suivants interviennent :
* réduction du trafic sur le réseau (soumission d'un bloc PL/Sql
au moteur au lieu d'une commande Sql),
* compilation des procédures cataloguées (le moteur ne recompile
pas les procédures au moment de l'exécution),
* exécution immédiate de la procédure si elle dans la SGA
(réduction des accès disque),
* partage de l'exécution d'une procédure par plusieurs
utilisateurs (notion de mémoire partagée).
La structure générale d'une procédure à partir du bloc PL/Sql
est la suivante :
PROCEDURE nomprocedure [ (parametres,...)] { IS | AS }
Déclaration des variables locales
BEGIN
Instructions SQL et PL/Sql
EXCEPTION
Traitement des exceptions (gestion des erreurs)
END [nomprocedure];
Le mot-clé Declare a été remplacé par le mot-clé Procedure.
Page 6 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
2.1. La création d'une procédure stockée
La commande qui permet de créer et de stocker une procédure dans
la base de données est la suivante :
CREATE [OR REPLACE] PROCEDURE [schema].nomprocedure
[ (parametres,...)]
{ IS | AS }
Déclaration des variables locales
/* bloc PL/Sql */
BEGIN
Instructions SQL et PL/Sql
EXCEPTION
Traitement des exceptions
END [nomprocedure];
Remarques :
 L'option OR REPLACE permet de spécifier au système le
remplacement de la procédure si elle existe déjà dans la base
de données.
 Les paramètres sont définis selon la syntaxe suivante :
nomparametre [IN | OUT | IN OUT] type [{:= default} valeur]
IN : indique que la variable est passée en entrée,
OUT : indique que la variable est renseignée par la procédure
puis renvoyée à l'appelant,
IN OUT : passage par référence
Le type du paramètre ne doit pas contenir d'indication sur la
longueur.
 La partie déclarative (interface) de la procédure se termine
par le nom de la procédure ou par la liste des paramètres.
 Le corps de la procédure commence par le mot-clé IS ou AS et se
termine par le mot-clé END suivi par un nom de procédure
optionnel.
Le corps d'une procédure comporte trois parties :
- une partie déclarative,
- une partie exécutable,
- une partie optionnelle pour la gestion des erreurs.
 La partie déclarative contient la déclaration des variables
locales. Le mot-clé DECLARE n'est pas utilisé.
 La partie exécutable doit au moins comporter une instruction.
Page 7 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
Exemple : AugmenterSalaire
CREATE PROCEDURE AugmenterSalaire
(NumSal integer, Montant Real) IS
SalaireActuel Real;
SalaireNull Exception;
BEGIN
Select sal into SalaireActuel From emp
Where empno = NumSal ;
If (SalaireActuel is NULL)
then raise SalaireNull ;
else Update emp set sal = sal + Montant
Where empno = NumSal ;
End If;
EXCEPTION
When no_data_found then
insert into emp_audit values (NumSal,'Inconnu');
When SalaireNull then
insert into emp_audit values (NumSal,'Salaire Null');
END AugmenterSalaire ;
/
Une procédure est appelée comme une instruction PL/SQL.
Par exemple pour augmenter le salaire d'un employé l'appel sera
le suivant :
AugmenterSalaire(NumeroSalarie, Augmentation) ;
Remarque :
PL/Sql exige la déclaration d'un sous-programme avant de pouvoir
l'utiliser. L'exemple ci-dessous illustre un usage erroné :
DECLARE
PROCEDURE AccorderRemise ( ...) IS
BEGIN
CalculerTaux ( ... ) ;
END ;
PROCEDURE CalculerTaux ( ...) IS
BEGIN
...
END ;
-- non déclaré
Le problème, dans notre exemple, peut être résolu simplement en
plaçant
la
procédure
CalculerTaux
avant
la
procédure
AccorderRemise. Cependant, cette démarche n'est pas toujours
possible.
PL/Sql permet de résoudre ce genre de problème grâce à une
déclaration spéciale de sous-programme : la déclaration en amont
(forward declaration).
Elle consiste à terminer la déclaration de l'interface du sousprogramme par une virgule.
PROCEDURE CalculerTaux ( paramètres,...) IS -- décla. en amont
PROCEDURE AccorderRemise ( paramètres ) IS
Page 8 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
PROCEDURE CalculerTaux ( paramètres ) IS
Page 9 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
2.2. La modification d'une procédure
Suite aux évolutions des applications vous pouvez être amené à
modifier vos procédures existantes.
Il faut les recompiler avec la commande suivante :
ALTER PROCEDURE [schema].nomprocedure COMPILE ;
Exemple :
ALTER PROCEDURE CalculerTaux COMPILE ;
ALTER PROCEDURE AccorderRemise COMPILE ;
2.3. La suppression d'une procédure
Comme tout objet manipulé par Oracle, les procédures peuvent
être supprimées si nécessaire.
Cette suppression est assurée par la commande suivante :
DROP PROCEDURE [schema].nomprocedure ;
2.4. L'exécution d'une procédure
L'appel des procédures stockées peut se faire à partir d'un
trigger, d'un autre sous-programme, d'une application développée
avec un précompilateur ou d'un outil comme Sql*Plus.
1. A partir d'un autre sous-programme.
L'appel suivant :
AugmenterSalaire(100, 1000)
peut apparaître dans un autre sous-programme.
2. A partir d'un programme applicatif
EXEC SQL EXECUTE
BEGIN
AugmenterSalaire(100, 1000);
END;
END-EXEC ;
3. A partir de Sql*Plus :
SQL> EXECUTE AugmenterSalaire(7788,1000) ;
SQL> EXECUTE AugmenterSalaire(8000,1000) ;
Vérifier dans la table emp;
SQL> select empno,sal from emp;
Vérifier dans la table emp_audit;
SQL> select * from emp_audit;
Page 10 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
2.5. La mise au point d'une procédure
Lorsque la syntaxe de votre procédure est incorrecte lors d'une
opération de création, de modification ou de compilation, une
erreur spécifique est retournée par Oracle.
Sql*Dba retourne le message suivant en cas d'erreur de
compilation :
DBA-00072 : Warning : object created with compilation errors.
Object : correspond au nom de la procédure compilée.
Pour afficher toutes les erreurs de la dernière opération que
vous menez d'effectuer utiliser, à partir de Sql*Dba la commande
SHOW ERRORS
Exemple :
CREATE PROCEDURE supprimer_emp (num_emp number) AS
BEGIN
delete from emp wher empno = num_emp;
END
/
Cette procédure comporte deux erreurs :
 wher : oubli de E
 end : oubli du ;
La commande
suivants :
ERRORS FOR
LINE/COL
-----------3/24
5/0
SHOW
ERRORS
permet
de
récupérer
les
messages
PROCEDURE supprimer_emp ;
ERROR
------------------------------------------PL/SQL-00103 : Encountered the symbol "EMPNO" when ...
PL/SQL-00103 : Encountered the symbol "END" when ...
Le développeur peut également interroger le dictionnaire de
données pour obtenir des informations sur les erreurs. Les vues
du dictionnaire s'appellent :
 USER_ERRORS
 ALL_ERRORS
 DBA_ERRORS
Le texte source peut être obtenu à partir à vues suivantes :
 USER_SOURCE
 ALL_SOURCE
 DBA_SOURCE
Le développeur peut également utiliser le
DBMS_OUTPUT pour mettre au point ses procédures.
package
public
Page 11 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
3. Les fonctions
Une fonction est un sous-programme qui calcule une valeur.
La structure d'une fonction est identique à celle
procédure, mais les fonctions possèdent une clause RETURN.
d'une
FUNCTION nomfonction [ (argument,...)]
RETURN type
{ IS | AS }
Déclarations de variables locales
BEGIN
Instructions SQL et PL/Sql
RETURN (valeur)
EXCEPTION
Traitement des exceptions
END [nomfonction];
Remarques :
Les arguments sont définis selon la syntaxe suivante :
(nomargument [IN] type [,nomargument [IN] type] ... )
Le type de l'argument ne doit pas contenir d'indication sur la
longueur.
Le nom de la fonction est valorisé par une valeur conforme au
type par l'instruction Return.
Il est important de ne pas confondre les deux clauses RETURN.
- Return type,
- Return (valeur)
Une fonction doit être déclarée avant d'être utilisée.
Page 12 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
3.1. La création d'une fonction stockée
CREATE [OR REPLACE] FUNCTION nomfonction [ (argument,.)]
RETURN type
{ IS | AS }
Déclarations de variables locales
BEGIN
Instructions SQL et PL/Sql
RETURN(Valeur)
EXCEPTION
Traitement des exceptions
END ;
Exemple : VerifierSalaire
CREATE FUNCTION VerifierSalaire
(Numgrade integer, Montant Real)
RETURN boolean IS
MinSalaire Real;
MaxSalaire Real;
BEGIN
Select losal, hisal into MinSalaire, MaxSalaire
From salgrad
Where grade = Numgrade ;
RETURN ( (Montant >= MinSalaire) and
(Montant <= MaxSalaire) );
END VerifierSalaire ;
/
Page 13 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
3.2. La modification d'une fonction
Suite aux évolutions des applications vous pouvez être amené à
modifier vos fonctions existantes.
Il faut les recompiler avec la commande suivante :
ALTER FUNCTION [schema].nomfonction COMPILE ;
Exemple :
ALTER FUNCTION VerifierSalaire COMPILE;
3.3. La suppression d'une fonction
Comme tout objet manipulé par Oracle, les fonctions peuvent être
supprimées si nécessaire.
Cette suppression est assurée par la commande suivante :
DROP FUNCTION [schema].nomfonction ;
2.4. L'exécution d'une fonction
L'appel des sous-programmes stockés peut se faire à partir d'un
trigger, d'un autre sous-programme, d'une application développée
avec un précompilateur.
1. A partir d'un autre sous-programme.
L'appel suivant :
a:=VerifierSalaire(100, 1000)
peut apparaître dans un autre sous-programme.
2. A partir d'un programme applicatif
EXEC SQL EXECUTE
BEGIN
a:=VerifierSalaire(100, 1000);
END;
END-EXEC ;
Page 14 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
4. Les Packages
4.1. Le principe
La notion de package est tout à fait nouvelle dans la version 7.
Elle permet d'encapsuler :
 des procédures,
 des fonctions ,
 des curseurs et
 des variables
comme une unité dans la base de données.
Elle apporte un certain nombre d'avantages
procédures et fonctions cataloguées.
par
rapport
aux
1. Meilleur moyen de structuration et d'organisation du
processus
de
développement.
Le
mécanisme
de
gestion
de
privilèges devient plus facile par rapport aux procédures et
fonctions cataloguées. En effet, l'attribution de privilèges
d'utilisation des composantes d'un package se fait par une seule
commande.
2. Meilleur mécanisme de gestion de la sécurité. L'utilisateur
peut spécifier au cours de la création d'un package des
composantes publiques et des composantes privées. La séparation
des déclarations des composantes d'un package de leur corps
permet une meilleure flexibilité.
3. Les performances peuvent être améliorées en utilisant plutôt
des packages que des procédures cataloguées. Le moteur charge en
mémoire le package entier quand une de ses procédures est
appelée. Une fois le package en mémoire, le moteur n'a plus
besoin d'effectuer des lectures (IO disque) pour exécuter les
procédures de ce même package.
Page 15 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
Un package se compose en général de deux parties :
 une spécification (interface),
 un corps (body) ou implémentation.
La spécification correspond à l'interface pour vos applications.
Elle correspond à la partie déclarative du package.
Le corps défini entièrement les curseurs, les sous-programmes et
ainsi implémente la spécification.
Schéma de principe :
Page 16 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
4.2. La création d'un package
La création d'un package se fait en deux étapes :
 création des spécifications du package,
 création du corps du package.
Les spécifications d'un package consistent à déclarer les
procédures, les fonctions, les constantes, les variables et les
exceptions qui peuvent être accessibles par le "public".
Il s'agit de la déclaration des objets de type PUBLIC du
package.
La commande qui permet de créer les spécifications est :
INTERFACE ou SPECIFICATION du package (visible)
CREATE [OR REPLACE] PACKAGE [schema.]nompackage
{ IS | AS }
Déclarations de variables globales
Interface des sous-programmes
END [nompackage] ;
Le corps d'un package permet de définir les procédures, les
fonctions, les curseurs et les exceptions qui sont déclarés dans
les spécifications du package.
Il permet également de définir d'autres objets de même type non
déclarés dans les spécifications. Ces objets sont alors "privés"
et ne peuvent en aucun cas être accédés en dehors du corps du
package.
La commande qui permet de créer le corps est :
IMPLEMENTATION ou CORPS du package (caché)
CREATE [OR REPLACE] PACKAGE BODY nompackage
{ IS | AS }
Déclarations de variables locales
Corps des sous-programmes publics et privés
BEGIN
...
END [nompackage] ;
Remarques :
Les packages ne peuvent pas être appelés, paramétrés ou
imbriqués. Par contre, le format d'un package est similaire à
celui d'un sous-programme.
Les packages sont créés de manière interactive avec Sql*Plus ou
Sql*Dba.
Page 17 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
4.3. L'encapsulation des sous-programmes dans des packages
La déclaration en amont des sous-programmes vous permet
également de regrouper des sous-programmes ayant un point commun
dans un package (librairie ou paquettage ou module).
La
spécification
du
sous-programme
apparaîtra
dans
la
spécification du package et le corps du sous-programme figurera
dans le corps du package.
Les corps des sous-programmes sont cachés aux applications.
Ainsi les packages vous permettent de cacher les détails de
l'implémentation.
Exemple :
CREATE PACKAGE GererSalarie AS
Function Embaucher (nom VARCHAR2, fonction VARCHAR2,
chef NUMBER, dateentree DATE, salaire NUMBER,
commission NUMBER, NumeroDept NUMBER) RETURN NUMBER ;
Procedure Supprimer (NumeroSal NUMBER) ;
Procedure Augmenter (NumeroSal NUMBER, montant NUMBER) ;
END GererSalarie ;
CREATE PACKAGE BODY GererSalarie AS
Function Embaucher (nom VARCHAR2, fonction VARCHAR2,
chef NUMBER, dateentree DATE, salaire NUMBER,
commission NUMBER, NumeroDept NUMBER) RETURN NUMBER
IS
NewMatricule NUMBER(8);
BEGIN
select emp_seq.NEXTVAL into NewMatricule from dual ;
insert into emp values (NewMatricule, nom, fonction, chef,
dateentree, salaire, commission, NumeroDept) ;
return (NewMatricule) ;
END Embaucher ;
Procedure Supprimer (NumeroSal NUMBER)
AS
BEGIN
delete from emp where Matricule=NumeroSal ;
If Sql%NOTFOUND then
raise_application_error(-20011,'Numéro incorrect') ;
End If;
END Supprimer ;
Procedure Augmenter (NumeroSal NUMBER, montant NUMBER) ;
AS
BEGIN
update emp set salaire=salaire + montant
where Matricule=NumeroSal ;
If Sql%NOTFOUND then
raise_application_error(-20011,'Numéro incorrect') ;
End If;
END Augmenter ;
Page 18 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
END GererSalarie ;
Page 19 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
Il est possible de déclarer des données et des procédures
regroupées dans un package de manière public ou privé.
La déclaration d'une procédure, d'une constante, d'une variable,
d'un curseur ou d'une exception dans l'interface du package la
rend publique.
Une procédure, une constante, une variable, un curseur ou une
exception sont privés lorsqu'ils ne sont pas déclarés dans
l'interface. De ce fait ils ne sont utilisables qu'à l'intérieur
du package.
Exemple de Procédure privée : Raise_Appplication_Error
CREATE PACKAGE BODY GererSalarie AS
/* implémentation des sous - programmes publics */
Function Embaucher (nom VARCHAR2, fonction VARCHAR2,
chef NUMBER, dateentree DATE, salaire NUMBER,
commission NUMBER, NumeroDept NUMBER) RETURN NUMBER
IS NewMatricule NUMBER(8);
BEGIN ... END Embaucher ;
Procedure Supprimer (NumeroSal NUMBER)
AS
BEGIN ... END Supprimer ;
Procedure Augmenter (NumeroSal NUMBER, montant NUMBER) ;
AS
BEGIN ... END Augmenter ;
/* implémentation d'un sous-programme privé */
Procedure Raise_Application_Error
(nummsg NUMBER, libmsg varchar2)
AS
BEGIN
insert into resultat
values (Nummsg, libmsg, NULL) ;
END Raise_Application_Error ;
END GererSalarie ;
Page 20 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
4.4. La surcharge (overloading) des sous-programmes
PL/Sql vous permet de surcharger les noms des sous-programmes.
Cela signifie, que vous pouvez utiliser le même nom pour deux
sous-programmes différents à condition que les paramètres
formels se différencient en nombre, ordre ou type.
Illustrant cette possibilité à partir de l'exemple dont
l'objectif est d'initialiser les premières lignes d'un tableau
de date et d'un tableau de réel.
DECLARE
TYPE DateTabTyp is table of date
index by binary_integer ;
TYPE ReelTabTyp is table of real
index by binary_integer ;
tdateachat DateTabTyp ;
tsalaire
ReelTabTyp;
indice binary_integer ;
...
PROCEDURE Initialiser (tab OUT DateTabTyp, n Integer) IS
BEGIN
For i IN 1..n LOOP
tab(i) := SYSDATE;
End Loop;
END Initialiser ;
PROCEDURE Initialiser (tab OUT ReelTabTyp, n Integer) IS
BEGIN
For i IN 1..n LOOP
tab(i) := 0.0;
End Loop;
END Initialiser ;
BEGIN
indice := 50 ;
initialiser(tdateachat, indice) ;
initialiser(tsalaire, indice) ;
-- premier appel
-- second appel
END ;
Le premier appel concerne la seconde procédure initialiser.
Le second appel concerne la première procédure.
Restrictions à la surcharge :
 Les sous-programmes surchargés ne peuvent être utilisés que
dans un bloc, un sous-programme ou un package. Une procédure
isolée ne peut pas être surchargée.
 La surcharge n'est pas possible si les paramètres formels ne se
distinguent que par le nom ou le mode de passage (IN, OUT).
 La surcharge de deux fonctions qui différent par le type
retourné n'est pas possible.
Page 21 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
4.5. L'appel des sous-programmes packagés
Les sous-programmes
manière suivante :
packagés
doivent
être
référencés
de
la
nompackage.nomprocédure[(paramètres, ...)]
Exemple :
GererSalarie.Augmenter(1256, 350) ;
Les sous-programmes packagés peuvent être appelés à partir des
triggers de la base de données, d'un autre sous-programme
packagé, d'un programme développé avec le précompilateur, d'un
outil comme Sql*Plus.
4.6. La modification d'un package
La modification d'un package concerne sa version compilée. Il
est important de recompiler le package afin que le noyau tienne
compte de l'évolution de la base et que l’on puisse modifier sa
méthode d'accès et son plan d'exécution.
ALTER PACKAGE [schema.]nompackage
COMPILE [PACKAGE | BODY] ;
En pratique, il est recommandé de sauvegarder les sources des
packages dans des fichiers pour les reprendre en vue d'une
modification de leur contenu. En cas de modification des
sources, l'utilisateur doit recréer le package avec l'option
REPLACE pour remplacer l'existant.
4.7. La suppression d'un package
Elle s'effectue avec la commande :
DROP PACKAGE [schema.]nompackage ;
Page 22 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
5. Les triggers
5.1. Définition
* Un trigger base de données est un ensemble de traitements
PL/Sql.
* Un trigger base de données est déclenché implicitement et
automatiquement par un ou plusieurs événements prédéfinis.
* Un trigger base de données est attaché à une et une seule
table.
* Si une table est supprimée, les triggers base de données qui
lui sont associés sont automatiquement supprimés.
Un
trigger
base
de
données
caractéristiques :
1. Un séquencement,
2. Un type,
3. Un événement qui le déclenche,
4. Des restrictions éventuelles,
5. Les ordres du traitement.
Le séquencement est défini par
Le type est défini par
Un événement qui le déclenche
Des restrictions éventuelles
Les ordres du traitement
:
:
:
:
:
est
identifié
par
cinq
BEFORE ou AFTER
par ORDRE ou par LIGNE
INSERT, UPDATE, DELETE
WHEN
un bloc PL/SQL.
Les combinaisons possibles sont les suivantes :
Séquencement
BEFORE
Type
Par ordre
Par ligne
AFTER
Par ordre
Par ligne
Evénement
INSERT
UPDATE
DELETE
INSERT
UPDATE
DELETE
INSERT
UPDATE
DELETE
INSERT
UPDATE
DELETE
Page 23 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
5.2. Création d'un trigger
Syntaxe SQL :
CREATE TRIGGER nom du trigger
séquencement
événement
ON TABLE nom de la table
[REFERENCING OLD AS old ]
[
NEW AS new ]
FOR type [WHEN condition]
DECLARE
...
BEGIN
ordres du traitement
END ;
séquencement : BEFORE ou AFTER.
événement : DELETE, INSERT, UPDATE
ou une combinaison avec OR : DELETE OR INSERT OR UPDATE
UPDATE avec l'option OF nomcolonne : UPDATE OF(nomcolonne)
Type : FOR EACH ROW : par ligne. Par défaut le trigger est par
ordre : aucune spécification n'est utilisée.
Condition : condition Sql qui doit être vérifiée pour exécuter
le trigger.
Ordres : Bloc PL/SQL qui peut se composer d'une partie
déclaration et d'un traitement délimité par Begin et End ;
Ce bloc ne peut pas contenir d'instructions pour gérer
transactions (Commit, Rollback et Savepoint).
les
Exemple :
CREATE TRIGGER maj_employes
BEFORE DELETE OR INSERT OR UPDATE ON TABLE employes
DECLARE
...
BEGIN
...
END ;
Page 24 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
Type :
Il faut utiliser un trigger par ordre si le traitement doit
s'exécuter globalement pour l'ensemble des lignes.
Il faut utiliser un trigger par ligne si le traitement doit
s'exécuter pour chaque ligne de la table.
Referencing :
Pour référencer l'ancienne et/ou la nouvelle valeur d'une
colonne on utilise deux variables prédéfinies OLD et NEW.
 Elles ne sont utilisables que dans un trigger base de
données par ligne.
 Elles sont initialisées automatiquement par Oracle.
INSERT
UPDATE
DELETE
OLD
NULL
ancienne valeur
ancienne valeur
NEW
nouvelle valeur
nouvelle valeur
NULL
L'accès à une colonne s'écrit :
:new.nomcolonne ou
:old.nomcolonne
Si un trigger base de données s'exécute à la fois en INSERT
et/ou en UPDATE et/ou en DELETE, on a la possibilité d'utiliser
les prédicats :
- IF INSERTING ....
- IF UPDATING ....
- IF DELETING ....
dans le bloc PL/Sql du trigger.
Exemple :
CREATE TRIGGER maj_employes
BEFORE DELETE OR INSERT OR UPDATE ON TABLE employes
DECLARE
...
BEGIN
IF INSERTING OR UPDATING
THEN ...
END IF;
IF DELETING
THEN ...
END IF ;
END ;
Page 25 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
5.3. Gestion des triggers
5.3.1. Activation / Désactivation des triggers
Un trigger base de données peut être activé ou inhibé.
Il est parfois utile d'inhiber un trigger base de données pour :
- charger un nombre important de données,
- mettre à jour des tables référençant d'autres objet non
accessibles (réseau non opérationneln crash disque, tablespace
offline, ...)
Sql*Loader (en mode direct)
triggers base de données.
inhibe
automatiquement
tous
les
Syntaxe Sql :
ALTER TRIGGER [schema.]nomtrigger
{ DISABLE | ENABLE | COMPILE };
Disable : inhiber le trigger ;
Enable : activer le trigger ;
Compile : recompiler le trigger ;
5.3.2. Suppression d’un trigger
Syntaxe Sql :
DROP TRIGGER [schema.]nomtrigger ;
5.3.3. Modification d’un trigger
Syntaxe Sql :
CREATE [OR REPLACE] TRIGGER [schema.]nomtrigger
séquencement
événement
ON TABLE nom de la table
[REFERENCING OLD AS old ]
[
NEW AS new ]
FOR type [WHEN restriction]
DECLARE
...
BEGIN
ordres du traitement
...
END ;
Page 26 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
L'option OR REPLACE permet de changer la définition d'un trigger
sans être obligé de d'abord le supprimer.
Page 27 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
5.4. Utilisation des triggers
 Implémentation d'un système d'audit particulier.
 Génération des valeurs des colonnes calculées.
 Vérification de l'intégrité référentielle :
- pour des actions non gérées par les contraintes déclaratives,
- dans une base de données distribuée.
 Prévention des transactions invalides
 Implémentation de règles de sécurité complexes comme :
- le contrôle de l'heure et de la date de connexion,
- le contrôle du terminal à partir duquel la commande est
lancée.
 Maintenance de plusieurs tables "miroir" sur différents sites
d'un réseau : on assure ainsi le fait que toutes les
modifications soient répercutées sur tous les sites en même
temps.
Les avantages d'utilisation :
* Sécurité :
- permet d'implémenter des règles complexe,
- permet de construire des audits personnalisés.
* Intégrité :
- permet de construire des règles de validation complexes
* Performance :
- réduction du nombre d'appels à la base,
- utilisation de la zone de partage des ordres Sql,
- une seule copie du code pour plusieurs utilisateurs.
* Productivité :
- une seule copie du code permet une maintenance plus aisée.
Page 28 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
5.5. La gestion de l'intégrité référentielle par triggers
Nous allons illustrer cette gestion sur la base de la compagnie
aérienne.
Le type d'opération de suppression, modification à appliquer à
une valeur de clé étrangère (par ex. N°Pil de VOL) doit être
déclaré dans la définition de la relation associée (ici VOL).
3 stratégies sont possibles :
1. propagation (delete en cascade):
Suppression d'un tuple de PILOTE avec suppression de tous les
tuples de VOL concernant le tuple de PILOTE.
2. interdiction (restrict) :
Suppression interdite dans PILOTE s'il existe des tuples dans
VOL ayant la même clé étrangère.
3. mise à nulle (set null) :
Les valeurs des clés étrangères sont mises à "nulles".
Cette stratégie impose que les colonnes n'ont pas été déclarées
en Not Null.
Page 29 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
5.5.1. Définition de l'interdiction avec AMC*Designor V4
Trigger d'interdiction généré par AMC Designer V4 :
-- ============================================================
-Trigger "td_avion" pour controler
-la suppression dans la table "AVION"
-- ============================================================
create trigger td_avion before delete
on AVION for each row
declare
integrity_error exception;
errno
integer;
errmsg
char(200);
dummy
integer;
found
boolean;
cursor cfk1_vol(var_numavion number) is
select 1
from
VOL
where NUMAVION = var_numavion;
begin
-- Suppression interdite s'il existe des fils dans "Vol"
open cfk1_vol(:old.NUMAVION);
fetch cfk1_vol into dummy;
found := cfk1_vol%FOUND;
close cfk1_vol;
if found then
errno := -20006;
errmsg := '"Vol" existe encore. Suppression de "Avion" interdite.';
raise integrity_error;
end if;
-- gestion des erreurs
exception
when integrity_error then
raise_application_error(errno, errmsg);
end;
/
Page 30 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
5.5.2. Définition de la suppression en cascade
Trigger de delete en cascade généré par AMC Designer V4 :
-- ============================================================
-Trigger "td_pilote" pour controler
-la suppression dans la table "PILOTE"
-- ============================================================
create trigger td_pilote before delete
on PILOTE for each row
declare
integrity_error exception;
errno
integer;
errmsg
char(200);
dummy
integer;
found
boolean;
begin
-- Suppression des fils dans "Vol"
delete VOL
where NUMPILOTE = :old.NUMPILOTE;
-- gestion des erreurs
exception
when integrity_error then
raise_application_error(errno, errmsg);
end;
/
Page 31 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
5.5.3. Définition de la mise à nulle en suppression
Trigger de delete Set Null généré par AMC Designer V4 :
-- ============================================================
-Trigger "td_pilote" pour controler
-la suppression dans la table "PILOTE"
-- ============================================================
create trigger td_pilote before delete
on PILOTE for each row
declare
integrity_error exception;
errno
integer;
errmsg
char(200);
dummy
integer;
found
boolean;
begin
-- Mettre code de "Pilote" a vide dans "Vol"
update VOL
set
NUMPILOTE = NULL
where NUMPILOTE = :old.NUMPILOTE;
-- gestion des erreurs
exception
when integrity_error then
raise_application_error(errno, errmsg);
end;
/
Remarque :
La colonne Numpilote dans la table vol doit être autorisée en
valeur nulle !
Page 32 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
5.5.4. Définition de la vérification des clés étrangères
-- ============================================================
-Trigger "ti_vol" pour controler
-l'insertion dans la table "VOL"
-- ============================================================
create trigger ti_vol before insert
on VOL for each row
declare
integrity_error exception;
errno
integer;
errmsg
char(200);
dummy
integer;
found
boolean;
cursor cpk1_avion(var_numavion number) is
select 1
from
AVION
where NUMAVION = var_numavion
and
var_numavion is not null
for update of NUMAVION;
cursor cpk2_pilote(var_numpilote number) is
select 1
from
PILOTE
where NUMPILOTE = var_numpilote
and
var_numpilote is not null
for update of NUMPILOTE;
begin
-- Code de "Avion" doit exister
if :new.NUMAVION is not null then
open cpk1_avion(:new.NUMAVION);
fetch cpk1_avion into dummy;
found := cpk1_avion%FOUND;
close cpk1_avion;
if not found then
errno := -20002;
errmsg := 'Code de "Avion" inconnu. Creation
interdite.';
raise integrity_error;
end if;
end if;
Page 33 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
-- Code de "Pilote" doit exister
if :new.NUMPILOTE is not null then
open cpk2_pilote(:new.NUMPILOTE);
fetch cpk2_pilote into dummy;
found := cpk2_pilote%FOUND;
close cpk2_pilote;
if not found then
errno := -20002;
errmsg := 'Code de "Pilote" inconnu. Creation
interdite.';
raise integrity_error;
end if;
end if;
-- gestion des erreurs
exception
when integrity_error then
raise_application_error(errno, errmsg);
end;
/
Page 34 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
5.6. La mise en place d'un audit par triggers
-- ============================================================
-Trigger "aud_pilotes" pour controler
-les mises à jour dans la table "PILOTES"
-- ============================================================
create trigger aud_pilotes
after insert or update or delete
on PILOTES for each row
declare
date_maj date;
terminal char(10);
begin
-- recupération de la date et du terminal
date_maj := SYSDATE ;
terminal := USERENV("TERMINAL");
IF INSERTING
THEN insert into audit_table
values (audit_seq.NEXTVAL, user, date_maj, terminal,
'Pilotes','INSERT',:new.numpilote);
ELSIF DELETING
THEN insert into audit_table
values (audit_seq.NEXTVAL, user, date_maj, terminal,
'Pilotes','DELETE',:old.numpilote);
ELSE
insert into audit_table
values (audit_seq.NEXTVAL, user, date_maj, terminal,
'Pilotes','UPDATE',:old.numpilote);
IF UPDATING ('nompilote')
THEN
insert into audit_table_values
values (audit_seq.CURRVAL, 'nompilote',
:old.nompilote, :new.nompilote);
END IF;
END IF;
end;
Ce trigger nécessite la création des tables :
AUDIT_TABLE,
AUDIT_TABLE_VALUES
et de la séquence :
AUDIT_SEQ sur la table AUDIT_TABLE.
Page 35 sur 36
Systèmes de gestion de bases de données
Oracle 7 : Options procédurales
5.7. Différences entre triggers et contraintes d'intégrité
déclarative.
Les triggers et les contraintes de tables peuvent être utilisés
tous les deux pour assurer la cohérence de la base de données.
Cependant les triggers et les contraintes d'intégrité de tables
ne fonctionnent pas tout-à-fait de la même manière.
Une contrainte d'intégrité déclarative est toujours vérifiée par
les données de la base de données (sauf en cas de
désactivation).
Une contrainte s'applique aux données existantes dans une table
et d'autre part doit être vérifiée par toutes les instructions
qui manipulent les données.
Les triggers ne s'appliquent qu'au contenu des différentes
transactions qui s'exécutent sur la base de données. Il ne
s'applique donc pas aux données qui se trouvent déjà dans une
table avant la définition du trigger. De ce fait, un trigger ne
peut pas garantir que toutes les données présentes dans une
table sont conformes aux règles définies par le trigger associé
à cette table.
Un trigger permet de gérer la transition des données. Il permet
de vérifier une contrainte particulière au moment où une donnée
change de valeur.
Page 36 sur 36