ORACLE Option procédurale ORACLE Option procédurale

SOMMAIRE LES OBJETS APPLICATIFS

1. Définitions .......................................................................................................................... 2
2. Procédures et fonctions ...................................................................................................... 2 1. Définitions
2.1 Procédure.................................................................................................................... 2
2.2 Fonction...................................................................................................................... 3 Les options procédurales d’Oracle permettent de construire quatre types d’objets
2.3 Appel de sous-programme.......................................................................................... 4 applicatifs :
2.4 Gestion des dépendances............................................................................................ 4
3. Les packages ...................................................................................................................... 4 • Les procédures
3.1 Partie spécification ..................................................................................................... 5 • Les fonctions
3.2 Partie Body ................................................................................................................. 6 • Les packages
3.3 Exemples: ................................................................................................................... 6 • Les triggers
3.4 Utilisation d’un package............................................................................................. 6
4. Les triggers bases de données ............................................................................................ 7
4.1 Définition ................................................................................................................... 7 Ces objets écrits en PL/SQL sont définis et stockés une seule fois dans la base
4.2 Caractéristique d’un trigger........................................................................................ 7 Ils sont partageables par plusieurs applications et gérés de façon centralisée pour
4.3 Syntaxe de création .................................................................................................... 8 toutes les applications :
4.4 Exemple...................................................................................................................... 8 • Mise au point ;
4.5 Utilisation ................................................................................................................... 9
5. Conclusion........................................................................................................................ 11
• Evolution et maintenance
5.1 Procédures et fonctions ............................................................................................ 11 • Utilisation (« shared SQL »)
5.2 Packages ................................................................................................................... 12
5.3 Trigger base de données ........................................................................................... 12 2. Procédures et fonctions
6. Procédures du package DBMS_OUPUT d’Oracle 8 ....................................................... 13
7. EXERCICE ...................................................................................................................... 13
2.1 Procédure

Une procédure est un programme écrit avec le langage PL/SQL, pour réaliser
une ou plusieurs tâches élémentaires. Un seul exemplaire de la procédure est
stocké dans la base, et il est partageable par une multitude de programmes
d’applications
Syntaxes :

CREATE PROCEDURE nom_proc(para1 [mode] type1,…) AS/IS

[déclarations de variables locales]
BEGIN
instructions
END;

Mode:
• IN (valeur par défaut) qui permet de passer une valeur à la
procédure ou fonction.
• OUT, qui permet à la procédure de retourner une valeur à
l’environnement.

Professeur : ASSALE Adjé Louis 1/14 INP-HB Professeur : ASSALE Adjé Louis 2/14 INP-HB
ORACLE Option procédurale ORACLE Option procédurale

• IN OUT le même paramètre sert à passer une valeur à la procédure 2.3 Appel de sous-programme
puis à obtenir une valeur de la procédure.
EXEMPLES : Syntaxe d’appel dans SQL*PLUS :

CREATE PROCEDURE rendre_livre EXE[CUTE] nom_proc(liste de paramètres effectifs);

(N_livre IN NUMBER(5), date_retour IN DATE) EXE[CUTE] :variable := nom_fonc(liste de paramètres effectifs);
IS
BEGIN
UPDATE emprunt 2.4 Gestion des dépendances
SET retour = date_retour
WHERE N_liv# = N_livre and retour is NULL; Effets de la modification de la structure d’un objet sur les procédures ou
END; fonctions
Chaque procédure ou fonction est à recompiler
• Si les objets dépendants (procédures et fonctions) et les objets référencés
2.2 Fonction sont localisés sur le même site, ORACLE recompilera automatiquement
les objets dépendants.
Une fonction se comporte comme une procédure sauf qu’elle retourne une
valeur en résultat • Si les objets dépendants (procédures et fonctions) et les objets référencés
sont localisés sur des sites différents, il faudra recompiler manuellement
Syntaxe : les objets dépendants.
CREATE FUNCTION nom_fonc(para1 [mode] type1,…)
RETURN type_de_données AS/IS 3. Les packages
[déclarations de variables locales]
BEGIN - Définition
instructions Un package est un ensemble d’objets applicatifs ayant des liens logiques entre
RETURN(expression) eux
END;
Procédure fonction

Exemple:
CREATE FUNCTION lecture_prix
(N_livre IN NUMBER(5,2))
RETURN number (5,2)
IS
Variable curseur
Let_prix number(5,2); Constante exception
BEGIN
SELECT prix
INTO lect_prix
FROM Livre Différents éléments peuvent constituer un package
WHERE N_liv # = N_livre; - Procédure - Fonction - Variable
RETURN (let_prix); - Curseur - Constante - Exception
END; Un package est construit en deux parties :
- une partie spécification et une partie BODY

Professeur : ASSALE Adjé Louis 3/14 INP-HB Professeur : ASSALE Adjé Louis 4/14 INP-HB
ORACLE Option procédurale ORACLE Option procédurale

Package [déclaration de variable, curseur et/ou exception ;] …}

END nom_package ;

Déclaration de Procédure
la procédure A
publique 3.2 Partie Body

CREATE [OR REPLACE] PACKAGE BODY nom_package [IS/AS]

variable Package
publique {[définition de procédure et/ou fonction déjà déclarées; ]
Spécification
[définition de procédure et/ou fonction; ]
[déclaration de variable, curseur et/ou exception ;] …}
END nom_package ;

Définition de la Procédure
procédure A
publique 3.3 Exemples:

Variable CREATE PACKAGE rendement AS

l l FUNCTION moyenne(N_pers IN chercheurs.#pers%TYPE) RETURN number;
FUNCTION moyenne(N_grpe IN groupes.#grpe%TYPE, N_dom IN
Procédure privée domaines.#dom%TYPE) RETURN number ;
Déclaration de
la procédure A END rendement;
Package
Body CREATE PACKAGE BODY rendement AS
Variable
FUNCTION moyenne(N_pers IN chercheurs.#pers%TYPE) RETURN
i
Number IS
BEGIN…
END moyenne;
On a 2 types de déclarations à l’intérieur d’un PACKAGE FUNCTION moyenne(N_grpe IN groupes.#grpe%TYPE, N_dom IN
domaines.#dom%TYPE) RETURN number IS
- Déclaration de type public : BEGIN…
* accessibles par tous les utilisateurs autorisés, END moyenne ;
* déclarées dans la partie SPECIFICATION Err Exception;
* définies dans la partie BODY END rendement ;
- Déclaration de type privé :
* accessible uniquement par les composants du PACKAGE
* déclarées et définis uniquement dans la partie BODY 3.4 Utilisation d’un package

Syntaxes : - Appel des éléments d’un package, on préfixe le nom de l’objet appelé dans le
package du nom du package par exemple nom_package.nom_procedure.

3.1 Partie spécification

CREATE [OR REPLACE] PACKAGE nom_package [IS/AS]

{[déclaration de procédure et/ou fonction; ]

Professeur : ASSALE Adjé Louis 5/14 INP-HB Professeur : ASSALE Adjé Louis 6/14 INP-HB
ORACLE Option procédurale ORACLE Option procédurale

- UPDATE
Package Pk Package
Spécification
- DELETE
Procédure A Procédure X • Des restrictions éventuelles
Variable C …. - WHEN
…. • Les ordres du traitement
Pk.A;
Variable D Package X:=Pk.C;
Body …. Les combinaisons possibles
Procédure A
Procédure B ….
INSERT
…. Par ordre UPDATE
Y:=D; DELETE
Z:=C;
… AFTER/BEFORE
INSERT
Par ligne UPDATE
DELETE
• Dans un package, les procédures et fonctions peuvent être surchargées
• On supprime un package par : DROP PACKAGE nom_package ; ou
DROP PACKAGE BODY nom_package ; 4.3 Syntaxe de création

4. Les triggers bases de données CREATE TRIGGER nomtrigger séquence événement [OR événement]
ON nomtable
[REFERENCING {[OLD AS ancien] | [NEW AS nouveau]}
4.1 Définition [FOR EACH ROW]
[WHEN condition]
• Un trigger base de données est un ensemble de traitements PL/SQL Bloc_traitements
• Un trigger base de données est déclenché implicitement et
automatiquement par un ou plusieurs évènements prédéfinis. -Pour l’événement UPDATE on peut limiter la mise en oeuvre du traitement à la
• Un trigger base de données est attaché à une seule table modification de certaines colonnes : UPDATE OF nom_col [,nom_col…]
• Si une table est supprimée, les triggers de base de données qui sont -Un même déclencheur peut répondre à plusieurs événements. Dans ce cas il est
associés sont automatiquement supprimés possible d’utiliser les prédicats :
IF {INSERTING|DELETING|UPDATING} [(nom_col)] THEN … END IF
-On peut faire référence, dans la condition de la clause WHERE ou dans le corps
4.2 Caractéristique d’un trigger du traitement associé au déclencheur, la valeur d’une colonne avant
modification en préfixant le nom de colonne par OLD, et/ou à la valeur après
Un trigger base de données est identifié par cinq caractéristiques : modification en préfixant le nom de colonne par NEW. On évite d’utiliser OLD
• Un séquencement : et NEW en précisant la clause REFERENCING.
- BEFORE
- AFTER 4.4 Exemple
• Un type:
- par ordre
CREATE TRIGGER maj_livre
- par ligne
BEFORE
• Un événement qui le déclenche : DELETE OR INSERT OR UPDATE
- INSERT

Professeur : ASSALE Adjé Louis 7/14 INP-HB Professeur : ASSALE Adjé Louis 8/14 INP-HB
ORACLE Option procédurale ORACLE Option procédurale

ON Livre IFDELETING..
DECLARE
... EXEMPLE
BEGIN CREATE TRIGGER maj_livre
… BEFORE
END; DELETE OR INSERT OR UPDATE
Séquence de déclenchement des triggers ON Livre
Trigger before par ordre
Declare
N_Liv# Titre Auteur ...
Trigger par ligne before BEGIN
14 Ah les hommes Biton IF INSERTING OR UPDATING
Trigger par ligne after
THEN...
Trigger par ligne before
IF DELETING THEN...
17 Ah les femmes Biton Trigger par ligne after
END;
Trigger par ligne before
20 Tribaliques Lopez - Un trigger base de données peut être activé ou inhibé
Trigger par ligne after

Il est parfois utile d’inhiber un trigger base de données pour :

Trigger after par ordre • changer un nombre important de données,
• mettre à jour des tables référençant d’autres objets non accessibles
(réseau non opérationnel, crash disque, tablespace offline…).
4.5 Utilisation
SQL*Loader (en mode direct) inhibe automatiquement tous les triggers base de
• on utilisera un trigger par ordre si le traitement doit s’exécuter données.
globalement pour l’ensemble des lignes
• on utilisera un trigger par ligne si le traitement doit s’exécuter pour EXEMPLE
chaque ligne de la table ALTER TRIGGER maj_livre DISABLE
Pour référencer l’ancienne et/ou la nouvelle valeur d’une colonne on utilise les
deux variables OLD et NEW - Utilisation des triggers base de données
• elles ne sont utilisables que dans un trigger base de données par ligne • Maintenance de champs annexes
• elles sont initialisées automatiquement par ORACLE • Implémentation de règles de sécurité complexe comme par exemple :
- Le contrôle de l’heure et de la date de connexion
OLD NEW - Le contrôle du terminal à partir duquel la commande est lancée
INSERT NULL nouvelle valeur • Implémentation d’un système d’audit particulier
UPDATE Ancienne valeur Nouvelle valeur • Maintenance de plusieurs tables « miroir » sur différents sites d’un
DELETE Ancienne valeur NULL réseau - On assure ainsi le fait que touts les modifications soient
répercutées sur tous les sites en même temps.
- 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 : - Trigger base de données ou procédures stockées ?

IF INSERTING… - Ce qui est semblable :

IF UPDATING … * Les ordres SQL et PL/SQL

Professeur : ASSALE Adjé Louis 9/14 INP-HB Professeur : ASSALE Adjé Louis 10/14 INP-HB
ORACLE Option procédurale ORACLE Option procédurale

* L’utilisation de la zone de partage des ordres SQL. Les droits d’accès ne sont plus donnés explicitement sur les objets mais
- Ce qui les différencie : sur les procédures stockées
* Un trigger base de données est associé à une et une seule table, - Intégrité
* Un trigger base de données est appelé implicitement, assure que les traitements dépendants sont exécutés simultanément
* Les ordres COMMIT, ROLLBACK et SAVEPOINT (marque le - Performance :
début d’une sous – transaction: savepoint nom_soustransaction, fin de la * Réduction du nombre d’appels à la base
sous-transaction par: Commit/Rollback to nom_soustransaction) sont * Utilisation de la zone de partage des ordres SQL
interdits dans les triggers base de données * Une seule copie du code pour plusieurs utilisateurs
* Un trigger base de données sera opérationnel jusqu’à la suppression - Productivité
de la table sur laquelle il est défini ou jusqu’à ce qu’il soit inhibé * Evite les redondances de procédures dans plusieurs applications
* Réduit les erreurs de programmation
- Trigger base de données ou trigger de transaction SQL*Forms ?
- Le trigger base de données : 5.2 Packages
* Se déclenche uniquement sur les ordres INSERT, UPDATE, et - Sécurité
DELETE * Accès uniquement aux déclarations du type public
* Se déclenche en supplément des triggers SQL*Forms * Les droits d’exécutions ne sont donnés que sur un package et non
* Se déclenche à partir de n’importe quel outil. plus individuellement sur les composants du package.
* Distingue les traitements par ordre et par lignes - Etat persistant :
- Le trigger de transaction SQL*Forms : * Conservation des valeurs des variables pour toute une session
* est associé à une seule application * Conservation des contextes des curseurs pour toute une session
* se déclenche uniquement quand l’application s’exécute - Performance :
*ne distingue pas les traitements par ordre de ceux qui concernent * Réduction du nombre d’appels à la base
toutes les lignes * Seul le premier appel charge en mémoire tout le package
* ne s’exécute qu’à la demande d’un COMMIT - Productivité
* Stockage dans la même entité de fonction et de procédure
- Les restrictions * Gestion facile de l’organisation des développements

- On ne peut pas utiliser un trigger pour contourner une contrainte

d’intégrité référentielle 5.3 Trigger base de données
Exemple
Table source Dept (deptno , nom) - Sécurité :
Table dépendante Empl(noemp, nomemp, deptno,) * Permet d’implémenter des règles complexes
Une requête telle que: * Permet de construire des audits personnalisés
UPDATE Dept SET deptno = 1WHERE depno = 10 - Intégrité:
Avec un trigger AFTER UPDATE ROW pour agir sur la table dépendante Emp * Permet de construire des règles de validation complexe
échouera - Performance :
* Réduction du nombre d’appels à la base
5. Conclusion * Utilisation de la zone de package des ordres SQL
* Une seule copie du code pour plusieurs utilisateurs
- Productivité
5.1 Procédures et fonctions * Une seule copie du code permet une maintenance plus aisée.
- Sécurité :

Professeur : ASSALE Adjé Louis 11/14 INP-HB Professeur : ASSALE Adjé Louis 12/14 INP-HB
ORACLE Option procédurale ORACLE Option procédurale

6. Procédures du package DBMS_OUPUT d’Oracle 8 • Ecrire un trigger modif_effectif qui met à jour l’effectif des classes après
une insertion, destruction et modification de la classe d’un étudiant.
Oracle8 offre des procédures standardisées réunies dans le package
DBMS_OUTPUT qui peuvent être utilisées en mode procédural dans un bloc
PL/SQL.

Procédure Action
GET_LINE GET_LINE(ligne OUT, statut OUT)
Extrait une ligne du tampon de sortie.
GET_LINES GET_LINES(lignes OUT, n IN OUT)
Extrait un tableau de n lignes du tampon de sortie.
NEW_LINE NEW_LINE
Affiche la ligne générée par PUT et place un marqueur de
fin de ligne dans le tampon de sortie.
PUT PUT (variable / constante)
Place la valeur spécifiée dans le tampon de sortie.
PUT_LINE PUT_LINE (variable/constante)
Combinaison de PUT et de NEW_LINE
ENABLE ENABLE (taille_tampon)
Permet de mettre en route le mode trace dans une
procédure ou fonction.
DISABLE DISABLE
Permet de désactiver le mode trace dans une procédure ou
une fonction.

7. EXERCICE

• Ecrire une fonction numero qui renvoie le numéro d’un étudiant.

• Ecrire une procédure augmente qui augmente la note d’un étudiant de
nom x d’une valeur y. on utilisera la fonction numero.
• Ecrire un package rendement qui contient les fonctions suivantes :
moyenne(personne)
moyenne(classe, matière)
• Ecrire un package gestion_etudiant qui contient les procédures suivantes :
inscrire(étudiant, classe, sexe)
suivre(étudiant, matière, date_s)
avoir_note(étudiant, matière, valeur)
renvoi(étudiant)
modif_classe(étudiant, nouv_classe)
• Ecrire un trigger modif_note qui à la modification d’une note met à jour le
champ date_ob à la date système (SYSDATE).

Professeur : ASSALE Adjé Louis 13/14 INP-HB Professeur : ASSALE Adjé Louis 14/14 INP-HB