Burkina Faso

La Patrie ou la Mort Nous Vaincrons

Université Aube Nouvelle Programmer avec Oracle PL/SQL

Année Académique : 2024-2025

Partie I : SQL Sous Oracle

Durée : 20 Heures
Enseignante : Madame IMA RASMATA
Spécialiste Oracle Database & Developpement ORACLE Période :
Oracle PL/SQL Developer Certified Associate
Février 2025
Téléphone : 00226 77 55 96 62/ 0 3 38 41
Partie I : Cours SQL ORACLE

Rappel
Par définition un système de gestion des bases de données est un ensemble de
programmes destinés à gérer les fichiers Oracle est constitué essentiellement des
couches suivantes :
1. Le noyau : dont le rôle est de
a. Optimisation dans l’exécution des requêtes
b. Gestion des accélérateurs (index et Clusters)
c. Stockage des données
d. Gestion de l’intégrité des données
e. Gestion des connexions à la base de données
f. Exécution des requêtes

2. Le dictionnaire de données : le dictionnaire de données d’oracle est unemétabase qui décrit

d’une façon dynamique la base de données. Il permet ainsi de décrire les objets suivants :
a. Les objets de la base de données (Tables, SYNONYMES, VUES, COLONNES, …)
b. Les utilisateurs accédant à la base de données avec leurs privilèges (CONNECT,
RESOURCE et DBA).

c. Ainsi toute opération qui affecte la structure de la base de données provoque

automatiquement une mise à jour du dictionnaire.
2. La couche SQL : cette couche joue le rôle d’interface entre le noyau et les différents

outils d’oracle. Ainsi tout accès à la base de données est exprimé en langage SQL. Le
rôle de cette couche est d’interpréter les commandes SQL , de faire la vérification
syntaxique et sémantique et de les soumettre au noyau pour exécution

3. La couche PL/SQL : cette couche est une extension de la couche SQL puis que

le PL/SQL est une extension procédurale du SQL.

 I. LES REQUETES DML (SELECT, INSERT, UPDATE ET
DELETE).

Dans ce cours nous allons voir les différentes requêtes qui manipulent les
données. Ces requêtes s’appellent les DML (Data Manipulation Language).
Ils existent deux types de DML :
–Les requêtes de sélection (SELECT)
–Les requêtes de modification (INSERT, UPDATE et DELETE)

1-Les requetes de selection : SELECT

SELECT est le mot clé utilisé pour sélectionner les données des colonnes
depuis une ou plusieurs tables (FROM) selon des critères de recherche
(WHERE) et selon un ordre précis (ORDER BY).
Reprenons notre table employe du cours précèdent. Nous voulons afficher son
contenu sans aucun critère de recherche :
SELECT id, nom, prenom, date_embauche, solde_conge FROM employe;
Sélectionner le contenu des champs id, nom, prénom, date_embauche et solde_conge depuis
la table employe.
Nous avons sélectionné dans cet exemple toutes les colonnes de la table employe. Nous
pouvons donc réécrire cette requête en remplaçant le nom de toutes les colonnes par *
SELECT * FROM employe;
Dans ce cas, l’ordre d’affichage des colonnes est le même que lors de la création de la table.
Dans ce deuxième exemple, nous avons besoin des prénoms, nom et du solde de congé de
toutes les personnes qui ont été embauchées avant 2015. Nous désirons que le résultat soit
trié par le nom, d’une manière croissante, et par solde de congé d’une maniéré
décroissante:
SELECT prenom, nom, solde_conge FROM employe WHERE date_embauche < '01/01/2015'
ORDER BY nom ASC, solde_conge DESC;

Ce qu’il faut retenir de ces exemples:

–Si on désire afficher toutes les colonnes nous pouvons utiliser *
–Les conditions de filtre sont ajoutées après le mot clé WHERE. Seules les données qui
respectent ces conditions vont être affichées.
–Le mot clé ASC, qui signifie ascendant, est facultatif. Le tri sera toujours ascendant même si on
ne le met pas. Par contre si on désire faire un tri descendant, il faut absolument ajouter le mot
clé DESC.
Prenons notre dernier exemple, mais nous voulons ajouter une autre condition.
En plus de la date d’embauche, nous souhaitons afficher les employé dont le prénom
commence par un A ou par un B

SELECT prenom, nom, solde_conge FROM employe WHERE date_embauche <

'01/01/2015' AND (prenom LIKE 'A%' OR prenom LIKE 'B%') ORDER BY nom ASC,
solde_conge DESC;

Nous pouvons ajouter dans la clause WHERE les opérateurs logiques pour appliquer
le filtre. L’opérateur AND vérifie que les deux conditions sont vraies, alors que
l’opérateur OR ne vérifie que l’une des deux conditions.
Nous utilisons les parenthèses pour prioriser les conditions. Dans l’exemple ci-dessus, les
conditions du filtre sont touts les employés qui sont embauchés avant le 01/01/2015 et
que leurs prénoms commencent par un A ou bien les employés qui sont embauchés
avant le 01/01/2015 et que leurs prénoms commencent par un B.

Il existe d’autre opérateur logique qu’on utilise dans la clause WHERE qu’on
va voir en détails dans d’autre cours. On va voir aussi dans d’autre cours la
sélection de plusieurs tables et les jointures.
2- Les requetes de modification : INSERT, UPDATE et DELETE
Pour modifier les données d’une table nous utilisons ces trois requêtes :
INSERT : Pour insérer une nouvelle ligne dans
une table. La syntaxe est comme suit :
INSERT INTO nom_de_la_table (col_1, col_2, … col_n) VALUES (val_1, val_2, … val_n);

nom_de_la_table : est le nom de la table dont on va insérer les données.

col_n : le nom de la nième colonne de la table. Les noms des colonnes sont facultatifs. L’ordre
peut ne pas être le même que celui lors de la création de la table.
val_n : la valeur de la colonne col_n. Cette valeur doit être du même type que la colonne
correspondante.
Au cas où nous voulons insérer une ligne en spécifiant toute les colonnes,
nous pouvons écrire directement : INSERT INTO nom_de_la_table VALUES
(val_1, val_1, … val_n);
Passons aux exemples, insérons une nouvelle ligne dans la table emp :
INSERT INTO emp VALUES (1, 'Nom 1', 'Travail', 1234, '01/01/2015', 1500.20, NULL, NULL);
Nous avons insérer dans cette table une ligne qui comporte:
EMPNO : 1
ENAME : Nom 1
JOB : Travail
MGR : 1234
HIREDATE : 01/01/2015
SAL : 1500,20
COMM : Vide
DEPTNO : Vide

Dans le deuxième exemple nous allons insérer un autre employé mais nous ne disposons que de
son matricule et son nom : INSERT INTO emp (EMPNO, ENAME) VALUES (2, 'Nom 2');
Les autres colonnes seront insérées comme vide.

b) UPDATE : Pour modifier les informations d’une

table. La syntaxe est comme suit :
UPDATE nom_de_la_table SET col_1 = val_1, col_2 = val_2, … col_n = val_n WHERE condition;

nom_de_la_table : est le nom de la table dont on va insérer les données.

col_n : le nom de la nième colonne de la table. L’ordre peut ne pas être le même que celui lors
de la création de la table.
val_n : la nouvelle valeur de la colonne col_n. Cette valeur doit être du même type que la
colonne correspondante.

Exemple :
UPDATE emp SET sal = 2000, hiredate = '01/02/2015' WHERE empno = 2;
Dans cet exemple nous avons modifié les données “sal” et “hiredate” par 2000 et 01/02/2015
pour l’employé numéro 2.
c) DELETE : Pour supprimer des lignes d’une
table. La syntaxe est comme suit :
DELETE FROM nom_de_la_table WHERE
condition;
Exemple :
Nous allons supprimer l’employé 2
DELETE FROM emp WHERE empno = 2;

NB: Si vous ne spécifier pas le WHERE dans les requêtes DELETE et UPDATE, alors toutes les
lignes de la table seront impactées par la MAJ.
Pour valider une MAJ dans la table il faut exécuter la
requête COMMIT COMMIT;
Dans le cas ou on veut annuler la MAJ, on exécute la
requête ROLLBACK ROLLBACK;
 c) REGLES :

Dans une requête utilisant des opérateurs ensemblistes :

 Tous les SELECT doivent avoir le même nombre de colonnes sélectionnées, et leur types
doivent être un à un identiques. Les conversions éventuelles doivent être faites à
l'intérieur du SELECT à l'aide des fonctions de conversion.
 Les doubles sont éliminés (DISTINCT implicite).
 Les noms de colonnes (titres) sont ceux du premier SELECT.
 La largeur des colonnes est la plus grande parmi tous les SELECT.
 Dans une requête on ne peut trouver qu'un seul ORDER BY. S'il est présent, il doit être mis
dans le dernier SELECT et il ne peut faire référence qu'aux numéros des colonnes et non pas à
leurs noms (car les noms peuvent être différents dans chacune des
interrogations).
L'on peut combiner le résultat de plus de deux SELECT au moyen des opérateurs
UNION, INTERSECT, MINUS. SELECT ... UNION SELECT ... MINUS SELECT ...
Dans ce cas l'expresion est évaluée de gauche à droite, mais on peut modifier l'ordre
d'évaluation en utilisant des parenthèses. SELECT ...
UNION (SELECT ...
MINUS SELECT ...)
Oracle PL/SQL: Partie II
Chapitre 1 : Introduction
1. INTRODUCTION
SQL :
est un langage ensembliste et non procédural

PL/SQL :
est un langage procédural qui intègre des ordres SQL de gestion de la base de données

Instructions SQL intégrées dans PL/SQL :

- SELECT
- INSERT, UPDATE, DELETE
- COMMIT, ROLLBACK, SAVEPOINT
- TO_CHAR, TO_DATE, UPPER,

Instructions spécifiques à PL/SQL :

- définition de variables
- traitements conditionnels
- traitements répétitifs
- traitement des curseurs
- traitement des erreurs

3
Chapitre 2 : Structure d’un
bloc PL/SQL
CHAPITRE 2 : Structure d'un bloc PL/SQL

Un bloc PL/SQL est divisé en 3 sections :

DECLARE

BEGIN [nom_du_bloc]
Instructions SQL et PL/SQL
EXCEPTION
Traitement des exceptions
(gestion des erreurs)
END [nom_du_bloc] ;

Remarques :
- les sections DECLARE et EXCEPTION sont facultatives.
- chaque instruction se termine par un ;
- Les commentaires :
-- sur une ligne
ou
/* sur plusieurs
lignes */
2. Structure d'un bloc PL/SQL (suite ...)
Exemple :
PROMPT nom du produit SQL
ACCEPT prod

DECLARE PL/SQL
qte NUMBER(5)

BEGIN
SELECT quantite INTO qte
FROM stock
WHERE produit= '&prod';
-- on contrôle le stock
IF qte > 0
THEN
UPDATE stock
SET quantite = quantite - 1
WHERE produit = '&prod'; 6
 INSERT INTO vente
VALUES('&prod' || 'VENDU' , SYSDATE);
ELSE INSERT INTO commande
VALUES('&prod' || 'DEMANDE' , SYSDATE);
END IF;
COMMIT;
END;
/

7
Chapitre 3 : Les variables utilisées
dans PL/SQL
CHAPITRE 3 : Les variables utilisées dans PL/SQL
3.1. Les différents types de variables locales

Les variables locales se déclarent dans la partie DECLARE du bloc PL/SQL.

Différents types de variables :

* Variables de types ORACLE

* Variables de type BOOLEAN

* Variables faisant référence au dictionnaire de données

Initialisation des variables

Visibilité des variables

3.1.1. Variables de type ORACLE

Syntaxe :
nom_var TYPE_ORACLE;

Exemple :

DECLARE
nom CHAR(20);
prenom CHAR(15);
age NUMBER(3);
BEGIN
...
END;

10
3.1.2. Variables de type BOOLEEN

Syntaxe :
nom_var BOOLEAN;

Exemple :
DECLARE
retour BOOLEAN;
BEGIN
...
END;

11
3.1.3. Variables faisant référence au dictionnaire de données

* Variable de même type qu'un attribut d'une table de la base

Syntaxe :
nom_var [Link]%TYPE;

Exemple :
DECLARE
nom [Link]%TYPE;
BEGIN
...
END;

12
3.1.3. Variables faisant référence au dictionnaire de données (suite ...)

- Variable de même structure qu'une ligne d'une table de la base

Syntaxe :
nom_var table%ROWTYPE;

Exemple :
DECLARE
ligne pilote%ROWTYPE;
BEGIN
...
END;

Remarque :
La structure ligne contient autant de variables que de colonnes de la table. Ces variables
portent le même nom et sont de même type que les colonnes de la table.

Pour y accéder :

13
ligne.<nom_col1>
ligne.<nom_col2>
...
ligne.<nom_coln>

14
3.1.3. Variables faisant référence au dictionnaire de données (suite ...)

- Variable de même type qu'une autre variable

Syntaxe :
nom_var2 nom_var1%TYPE;

Exemple :

DECLARE
ancien_sal NUMBER(5);
nouveau_sal ancien_sal%TYPE;--NUMBER(5);
BEGIN
...
END;

15
Type de
données
PL/SQL

17
18
20
3.1.4. Initialisation des variables
Avec :
opérateur :=
ou
SELECT ... INTO ...

Exemple :

DECLARE
var1CHAR(10) := 'DUPONT';
var2 NUMBER(5,2) := 100;
var3 CHAR(10);
var4DATE;
BEGIN
SELECT col1, col2
INTO var3, var4
FROM ... ;
..
END.;
Remarque : le SELECT doit ramener une et une seule ligne, sinon erreur. 16
3.1.5. Visibilité des variables
Une variable est visible dans le bloc où elle a été déclarée et dans les blocs imbriqués si elle n'a
pas été redéfinie.
DECLARE
var1NUMBER(3);
var2CHAR(10);
BEGIN
... var1 NUMBER(3)
... var2CHAR(10)
DECLARE
var1CHAR(10);
var3DATE;
BEGIN
... var1CHAR(10)
... var2
... var3
END;
...
DECLARE
17
 var4NUMBER(5,2);
BEGIN
... var1 NUMBER(3)
... var2
... var4
END;
... var1 NUMBER(3)
... var2CHAR(10)
END;

3.2. Variables de l'environnement extérieur à PL/SQL

Outre les variables locales vues précédemment, un bloc PL/SQL peut utiliser d'autres
variables :

- les champs d'écrans FORMS,

18
- les variables définies en langage hôte (préfixée de :)

- les variables définies dans SQL*Plus (préfixée de &)

19
Chapitre 4 : Les Traitements
CHAPITRE 4 : Les traitements
4.1. IF : traitement conditionnel
Exécution d'un traitement en fonction d'une condition.

IF condition1 THEN traitement 1;

ELSIF condition2 THEN traitement 2;

[ELSE traitement 3;]

END IF;

Les opérateurs utilisés dans les conditions sont les mêmes que dans SQL :

=, <, ... IS NULL, LIKE, ...

21
Dès que l'une des conditions est vraie, le traitement qui suit le THEN est exécuté.
Si aucune condition n'est vraie, c'est le traitement qui suit le ELSE qui est exécuté.

22
4.2. Boucle de base LOOP : traitement répétitif
Exécution d'un traitement plusieurs fois, le nombre n'étant pas connu mais dépendant d'une
condition.

BEGIN
LOOP [label]
instructions;
END LOOP [label];
END;

Pour sortir de la boucle, utiliser la clause :

EXIT [lable] WHEN condition

Exemple : insérer les 10 premiers chiffres dans la table result

DECLARE
nb NUMBER := 1;
BEGIN
LOOP
INSERT INTO result

23
 VALUES (nb);
nb := nb + 1;
EXIT WHEN nb > 10;
END LOOP;
END ;

4.3. Boucle FOR : traitement répétitif

Exécution d'un traitement un certain nombre de fois. Le nombre étant connu.

BEGIN
FOR indice IN [REVERSE] exp1 ... exp2
LOOP
instructions;
END LOOP;
END;

Remarques :
- inutile de déclarer indice 24
- indice varie de exp1 à exp2 de 1 en 1
- si REVERSE est précisé, indice varie de exp2 à exp1 avec un pas de -1.

Exemple : calcul de la factorielle 5

DECLARE
fact NUMBER := 1;
BEGIN
FOR i IN 1 .. 5
LOOP
fact := fact * i ;
END LOOP;
END;

4.4. Boucle WHILE : traitement répétitif

Exécution d'un traitement trant qu'une condition reste vraie.

25
BEGIN

WHILE condition
LOOP
instructions;
END LOOP;

END;
Exemple : reste de la division de 5432 par 5

DECLARE
reste NUMBER := 5432;
BEGIN
WHILE reste >= 5
LOOP
reste := reste -5;
END LOOP;
END;
26
Chapitre 5 : Les curseurs en
PL/SQL
CHAPITRE 5 : Les curseurs en PL/SQL

5.1. Définitions

Il existe 2 types de curseurs :

- CURSEUR IMPLICITE :

curseur SQL généré et géré par le noyau pour chaque ordre SQL d'un bloc.

- CURSEUR EXPLICITE :

curseur SQL généré et géré par l'utilisateur pour traiter un ordre SELECT qui ramène plus
d'une ligne.

28
5.2. Curseur explicite

4 étapes :

- déclaration du curseur

- ouverture du curseur

- traitement des lignes

- fermeture du curseur

29
5.2. Curseur explicite (suite ...)

Déclaration du curseur

déclaration dans la section DECLARE du bloc.

on indique le nom du curseur et l'ordre SQL associé

Syntaxe :

CURSOR nom_curseur IS ordre_select ;

Exemple :

DECLARE
CURSOR pl_niceIS
SELECT pl#, plnom , pilote
FROM WHERE adr='Nice';

BEGIN
... 30
END ;
5.2. Curseur explicite (suite ...)

Ouverture du curseur

L'ouverture du curseur lance l'exécution de l'odre SELECT associé au curseur.

Ouverture dans la section BEGIN du bloc.

Syntaxe :

OPEN nom_curseur ;

Exemple :

DECLARE
CURSOR pl_nice IS
SELECT pl#, plnom

32
 FROM pilote
WHERE adr='Nice';
BEGIN
...
OPENpl_nice;
...
END ;

33
5.2. Curseur explicite (suite ...)

Traitement des lignes

Après l'exécution du SELECT les lignes ramenées sont traitées une par une, la valeur de
chaque colonne du SELECT doit être stockée dans une variable réceptrice.
Syntaxe :
FETCH nom_curseur INTO liste_variables ;

Exemple :
DECLARE
CURSOR pl_nice IS
SELECT pl#, plnom, sal
FROM pilote
WHERE adr='Nice';
num [Link]#%TYPE;
nom [Link]%TYPE;
salaire [Link]%TYPE;
BEGIN
OPEN pl_nice;

34
LOOP
FETCH pl_nice INTO num, nom,salaire;
...
EXIT WHEN sal > 10000;
END LOOP;
END ;

35
Fermeture du curseur
Pour libérer la mémoire prise par le curseur, il faut le fermer dès qu'on n'en a plus besoin.

Syntaxe :
CLOSE nom_curseur ;

Exemple :
DECLARE
CURSOR pl_nice IS
SELECT pl#, plnom, sal
FROM pilote
WHERE adr='Nice';
num [Link]#%TYPE;
nom [Link]%TYPE;
salaire [Link]%TYPE;
BEGIN
OPEN pl_nice;
LOOP
FETCH pl_nice INTO num, nom,salaire;
...
EXIT WHEN sal > 10 000;
END LOOP;
CLOSE pl_nice;
END ;

21/02/2024 36
EXEMPLE: TYPE CURSOR
SET SERVEROUTPUT ON;
DECLARE
CURSOR CURSEUR1 IS SELECT * FROM etudiants;
ligne CURSEUR1%rowtype;
BEGIN
OPEN CURSEUR1;
LOOP
FETCH CURSEUR1 INTO ligne;
DBMS_OUTPUT.PUT_LINE('le nom est '|| [Link] || 'le
prenom est '||
[Link]);
EXIT WHEN curseur1%NOTFOUND;
END LOOP;
CLOSE CURSEUR1;
END; 21/02/2024 37
5.3. Les attributs d'un curseur

Pour tout curseur (implice ou explicite)

il existe des indicateurs sur leur état.

%FOUND
dernière ligne traitée
%NOTFOUND

%ISOPEN ouverture d'un curseur

%ROWCOUNT nombre de lignes déjà traitées

21/02/2024 38
5.3. Les attributs d'un curseur

Pour tout curseur (implice ou explicite) il existe des indicateurs sur leur état.
%FOUND

% found: contraire de %notfound:

Exemple:
CREATE OR REPLACE PROCEDURE test1insertion (NEMP IN VARCHAR2, PEMP IN VARCHAR2)AS CURSOR CURSEUR1 IS
SELECT * FROM TEST1;
NOMEMP VARCHAR2(30);
PRN VARCHAR2(30);
BEGIN
OPEN CURSEUR1;
LOOP
FETCH CURSEUR1 INTO NOMEMP,PRN;
IF CURSEUR1%FOUND THEN
INSERT INTO TEST1 VALUES(NEMP,PEMP);
COMMIT;
ELSE EXIT;
END IF;
END LOOP;
CLOSE CURSEUR1;
end test1insertion;
39
5.3. Les attributs d'un curseur

Pour tout curseur (implice ou explicite) il existe des indicateurs sur leur état.
%ROWCOUNT

%rowcount: retourne le nombre d’enregistrements trouvés

Exemple:
LOOP
FETCH emp_curseur INTO
emp_nom, dept_num
IF emp_cursor%rowcount>10 THEN
EXIT;
END IF;
END LOOP;

40
5.3.1. %FOUND

curseur implicite : SQL%FOUND

TRUE

* si INSERT, UPDATE, DELETE traite au moins une ligne

* si SELECT ... INTO ... ramène une et une seule ligne

curseur explicite : nom_curseur%FOUND

TRUE

* si le dernier FETCH a ramené une ligne.

21/02/2024 41
5.3.1. %FOUND (suite ...)

Exemple :

DECLARE
CURSOR pl_nice IS
SELECT pl#, plnom, sal
FROM pilote
WHERE adr='Nice';
num [Link]#%TYPE;
nom [Link]%TYPE;
salaire [Link]%TYPE;

BEGIN
OPEN pl_nice;
FETCH pl_nice INTO num, nom,salaire;
WHILE pl_nice%FOUND
LOOP
...
FETCH pl_nice INTO num,
nom,salaire;
END LOOP;
CLOSE pl_nice;
END ;

21/02/2024 42
5.3.2. %NOTFOUND

curseur implicite : SQL%NOTFOUND

TRUE

* si INSERT, UPDATE, DELETE ne traite aucune ligne

* si SELECT ... INTO ... ne ramène pas de ligne

curseur explicite : nom_curseur%NOTFOUND

TRUE

* si le dernier FETCH n'a pas ramené de ligne.

21/02/2024 43
5.3.3. %ISOPEN

curseur implicite : SQL%ISOPEN

toujours à FALSE car ORACLE referme les curseurs après utilisation.

curseur explicite : nom_curseur%ISOPEN

TRUE si le curseur est ouvert.

Exemple 1 :
DECLARE
CURSOR pl_nice IS
SELECT pl#, plnom, sal
FROM pilote
WHERE adr='Nice';
num [Link]#%TYPE;
nom [Link]%TYPE;
salaire [Link]%TYPE;
BEGIN
IF NOT(pl_nice%ISOPEN)
THEN
OPEN pl_nice;
END IF;
...
END ;
21/02/2024 44
5.3.3. %ISOPEN

curseur implicite : SQL%ISOPEN

%isopen: retourne vrai si le curseur est ouvert:

Exemple 2 :

IF emp_curseur%isopen THEN
FETCH ...
ELSE
OPEN emp_curseur
END IF

21/02/2024 45
5.3.4. %ROWCOUNT

curseur implicite : SQL%ROWCOUNT

nombre de lignes traitées par INSERT, UPDATE, DELETE

0 : SELECT ... INTO : ne ramène aucune ligne

1 : SELECT ... INTO : ramène 1 ligne
2 : SELECT ... INTO : ramène plus d'une ligne

curseur explicite : nom_curseur%ROWCOUNT

traduit la nième ligne ramenée par le FETCH

21/02/2024 46
5.4. Simplification d'écriture

5.4.1. Déclaration de variables

Au lieu de déclarer autant de variables que d'attributs ramenés par le SELECT du curseur, on peut utiliser une
structure.

Syntaxe :
DECLARE
CURSOR nom_curseur IS ordre_select;
nom_structure nom_curseur%ROWTYPE;

Pour renseigner la structure :

FETCH nom_curseur INTO nom_structure;

Pour accéder aux éléments de la structure :

nom_structure.nom_colonne

21/02/2024 47
5.4. Simplification d'écriture

5.4.1. Déclaration de variables

Au lieu de déclarer autant de variables que d'attributs ramenés par le SELECT du curseur, on peut utiliser une
structure.

Syntaxe :
DECLARE
CURSOR nom_curseur IS ordre_select;
nom_structure nom_curseur%ROWTYPE;

Pour renseigner la structure :

FETCH nom_curseur INTO nom_structure;

Pour accéder aux éléments de la structure :

nom_structure.nom_colonne

21/02/2024 48
5.4.2. Traitement du curseur

Au lieu d'écrire :

DECLARE
CURSOR nom_curseur IS SELECT ... ;
nom_struct nom_curseur%ROWTYPE;
BEGIN
OPEN nom_curseur;
LOOP
FETCH nom_curseur INTO nom_struct;
EXIT WHEN nom_curseur%NOTFOUND;
...
END LOOP;
CLOSE nom_curseur;
END;

il suffit d'écrire :

DECLARE
CURSOR nom_curseur IS SELECT ... ;
BEGIN
FOR nom_struct IN nom_curseur LOOP
...
END LOOP;
END;
21/02/2024 49
5.4.2. Traitement du curseur (suite ...)

ou encore :

FOR nom_struct IN (SELECT ...)

LOOP

...

END LOOP;

21/02/2024 50
Chapitre 6 : La Gestion des
exceptions en PL/SQL
I) Gestion des Erreurs (EXCEPTION)
1. - Exception

• Une exception est une erreur qui survient durant une exécution

• 2 types d’exception :

– Interne

– exception oracle pré-définie

– exception oracle non pré-définie

– Externe (exception définie par l’utilisateur)

• Les exceptions internes sont générées par le moteur du système (division par zéro, connexion non établie, table inexistante, privilèges
insuffisants, mémoire saturée, espace disque insuffisant, ...).

– Une erreur interne est produite quand un bloc PL/Sql viole une règle d'Oracle ou dépasse une limite dépendant du système
d'exploitation.

– Chaque erreur ORACLE correspond un code SQL (SQLCODE)

• Les exceptions externes sont générées par l’utilisateur (stock à zéro, ...).
I.2 - Intercepter les Exceptions - Syntaxe

EXCEPTION
WHEN exception1 [OR exception2 . . .] THEN
statement1; statement2;
...
 [WHEN exception3 [OR exception4 . . .] THEN
statement1; statement2;
. . .]
[WHEN OTHERS THEN
statement1; statement2;
. . .]

I.3 - Règles pour intercepter les Exceptions

• Le mot clé EXCEPTION débute la section de la gestion des exceptions

• Plusieurs exceptions sont permises (définie et pré-définie)

• Une seule exception est exécutée avant de sortir d’un bloc

• WHEN OTHERS est la dernière clause

– Intercepte toutes les exceptions non gérées dans la même section d’exception

– Utilisez le gestionnaire d’erreurs OTHERS et placez le en dernier lieu après tous les autres

gestionnaire d’erreurs, sinon il interceptera toutes les exceptions mêmes celle qui sont prédéfinies.

Les erreurs internes d’Oracle pré-définies

• Utiliser le nom standard à l’intérieur de la section Exception

• Les noms des exceptions pré-définies oracle sont regroupées dans ce tableau :
• CURSOR_ALREADY_OPEN : tentative d’ouverture d’un curseur déjà ouvert..
• DUP_VAL_ON_INDEX: insertion d’une ligne en doublon

• INVALID_CURSOR : opération incorrecte sur un curseur, comme par exemple la fermeture d’un curseur qui n’a pas été ouvert.

• LOGIN_DENIED : connexion à la base échouée car le nom utilisateur ou le mot de passe est invalide.

• NO_DATA_FOUND : déclenché si la commande SELECT INTO ne retourne aucune ligne ou si on fait référence à un enregistrement non
initialise d’un tableau PL/SQL.

• NOT_LOGGED_ON : tentative d’accès à la base sans être connecté.

• PROGRAM_ERROR : problème général dû au PL/SQL.

• ROWTYPE_MISMATCH : survient lorsque une variable curseur d’un programme hôte retourne une valeur dans une variable curseur d’un
bloc PL/SQL qui n’a pas le même type.

• STORAGE_ERROR : problème de ressources mémoire dû à PL/SQL.

• TIMEOUT_ON_RESOURCE : dépassement du temps dans l’attente de libération des ressources (lié aux paramètres de la base).
• TOO_MANY_ROWS : la commande SELECT INTO retourne plus d’une ligne.

• VALUE_ERROR : erreur arithmétique, de conversion, ou de contrainte de taille.

• ZERO_DIVIDE : tentative de division par zéro.

Exemple

DECLARE
v_sal [Link]%type;
BEGIN
SELECT sal INTO v_sal from emp;
EXCEPTION
WHEN TOO_MANY_ROWS then ... ;
-- gérer erreur trop de lignes WHEN
NO_DATA_FOUND then ... ;
-- gérer erreur pas de ligne
WHEN OTHERS then ... ;
-- gérer toutes les autres erreurs
END ;

I.4 - Exceptions Oracle Non Prédéfinies

Vous pouvez intercepter une erreur oracle non pré-définie en la déclarant au préalable, ou en utilisant
la commande OTHERS, L’exception
déclarée et implicitement déclenchée

1. Déclarer le nom de l’exception oracle non-prédéfinie

Syntaxe exception_nom EXCEPTION;

2. Associer l’exception déclarée au code standard de l’erreur oracle en utilisant l’instruction

Syntaxe PRAGMA EXCEPTION_INIT (exception_nom, erreur_number);

3. Traiter l’exception ainsi déclarée dans la section EXCEPTION

Exemple :Capturer l’erreur du Serveur Oracle numéro -2292 correspondant à la violation d’une contrainte d’intégrité.
DECLARE
e_emps EXCEPTION;
PRAGMA EXCEPTION_INIT (e_emps, -2292);
v_deptno [Link]%type:=&p_deptno;
BEGIN
Delete from dept
Where deptno=v_deptno;
Commit;
EXCEPTION
WHEN e_emps THEN
dbms_output.put_line(‘Suppression impossible du dept : ‘ || to_char(v_deptno) || ‘employés existant ‘);
END;
 I.5 - Exceptions Utilisateur (externes)

• PL/SQL permet à l'utilisateur de définir ses propres exceptions.

• La gestion des anomalies utilisateur peut se faire dans un bloc PL/SQL en effectuant les opérations suivantes :
• Sont définies dans la section DECLARE

• Sont déclenchées explicitement dans la section BEGIN par l'instruction RAISE

• Dans la section EXCEPTION, référencer le nom défini dans la section DECLARE.

1. Nommer l'erreur (type exception) dans la partie Declare du bloc. DECLARE

Nom_ano Exception;

2. Déterminer l'erreur et passer la main au traitement approprié par la commande Raise.

BEGIN
If (condition_anomalie) then raise Nom_ano ;
3. Effectuer le traitement défini dans la partie EXCEPTION du Bloc.

EXCEPTION
 WHEN (Nom_ano) then (traitement); DECLARE
...
Nom_ano EXCEPTION;
BEGIN
instructions ;
IF (condition_anomalie) THEN RAISE Nom_ano;
...
EXCEPTION
WHEN Nom_ano THEN (traitement);
END ;
On sort du bloc après l'exécution du traitement d'erreur.
Exemple :
DECLARE

Erreur_comm exception ;
v_pilot pilote%rowtype ;
BEGIN
Select * into v_pilot From PiloteWhere nopilot =‘7100’ ;
If v_pilot.comm > [Link] Then
Raise erreur_comm ;
END IF.
...... EXECPTION
When erreur_comm then
Insert into erreur values(v_pilot.nom, ‘ Commission> salaire’) ;
When NO_DATA_FOUND Then
Insert into erreur values(v_pilot.nopilot, ‘ non trouvé’) ;
END ;
I.6 - Fonctions d’interception des erreurs

• SQLCODE

– Renvoie la valeur numérique associé au code de l’erreur.

– Vous pouverz l’assigneer à une variable de type number

• SQLERRM

– Renvoie le message associé au code de l’erreur. Exemple de SQLCODE

I.7 - Fonctions d’interception des erreurs- Exemple

Lorsqu’une exception est intercetée par la clause WHEN OTHERS, vous pouvez utiliser un ensemble de fonctions standard pour identifier l’erreur.

DECLARE
v_error_code NUMBER;
v_error_message VARCHAR2(255);
BEGIN
EXCEPTION
...
WHEN OTHERS THEN
ROLLBACK;
v_error_code := SQLCODE;
v_error_message := SQLERRM;
insert into erreur values(v_error_code, v_error_message);
END;
 Chapitre 7 : Modularité
(Procédure, Fonction et Package)
PROCEDURES, FONCTIONS ET PACKAGES
1. - Généralité

• Une procédure est un bloc PL/SQL nommé.

• Une fonction est une procédure qui retourne une valeur.

• Un package est un agrégat de procédures et de fonctions.

• Les packages, procédures, ou fonctions peuvent être appelés depuis toutes les applications qui possèdent une interface avec ORACLE
(SQL*PLUS, Pro*C, SQL*Forms, ou un outil client particulier comme NSDK par exemple).

• Les procédures (fonctions) permettent de :

• Réduire le trafic sur le réseau (les procédures sont locales sur le serveur)

• Mettre en œuvre une architecture client/serveur de procédures et rendre indépendant le code client de celui des procédures (à
l’API près)

• Masquer la complexité du code SQL (simple appel de procédure avec passage d’arguments)

• Sécuriser l’accès aux données (accès à certaines tables seulement à travers les procédures)

• Optimiser le code (les procédures sont compilées avant l’exécution du programme et elles sont exécutées immédiatement si elles
se trouvent dans la SGA (zone mémoire gérée par ORACLE). De plus une procédure peut être exécutée par plusieurs utilisateurs.

• Les packages permettent :

• de regrouper des procédures ou des fonctions (ou les deux). On évite ainsi d’avoir autant de sources que de procédures.
• De travailler en équipes et l’architecture applicative peuvent donc plus facilement s’organiser du côté serveur, où les packages
regrouperont des procédures à forte cohésion intra (Sélection de tous les articles, Sélection d’un article, Mise à jour d’un article,
Suppression d’un article, Ajout d’un article).

• Les packages sont utilisés comme de simples librairies par les programmes clients (librairies distantes « sur le serveur »)
• Les équipes de développement doivent prendre garde à ne pas travailler chacune dans « leur coin »

• Les développeurs ne doivent pas perdre de vue la logique globale de l’application et les scénarios
d’activité des opérateurs de saisie.

• A l’extrême, on peut finir par coder une procédure extrêmement sophistiquée qui n’est
sollicitée qu’une fois par an pendant une seconde. Ou encore, une gestion complexe de
verrous pour des accès concurrent qui n’ont quasiment jamais lieu.
• .
 2- Procédures

Les procédures ont un ensemble de paramètres modifiables en entrée et en sortie.

CREATE [ OR REPLACE ] PROCEDURE [<user>].<nom_proc>

(arg1 IN type1 *DEFAULT val_initiale *, arg2 OUT type2 *, arg3 IN OUT type3, …+) AS
[ Déclarations des variables locales ]

EXCEPTION BEGIN
Procédure Exemple

Compter le nombre d'employés pour un département donné.

CREATE OR REPLACE PROCEDURE proc_dept (p_no IN [Link]%TYPE) IS
v_no NUMBER;
BEGIN
SELECT COUNT(deptno) INTO v_no
FROM emp
WHERE deptno=p_no;
DBMS_OUTPUT.PUT_LINE('Nombre d'employés : '||' '||v_no);
END;
/

• Exemple de procédure qui modifie le salaire d’un employé.

– Arguments : Identifiant de l’employée, Taux modifie_salaire.sql

create procedure modifie_salaire (id in number, taux in number) is
begin
update employe set salaire=salaire*(1+taux) where Id_emp= id;
exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE(‘Employé inconnu : ‘ ||to_char(id)); End;
/
 V.2.1) PROCEDURES / PARAMETRES
EXEMPLE IN OUT

CREATE OR REPLACE PROCEDURE format_phone(v_phone_no IN OUT VARCHAR2 (12)) IS

BEGIN
v_phone_no := ‘ (‘ ||substr(v_phone_no,1,3)|| ’) ’||substr(v_phone,4,7) END format_phone ;
/
Compilation de la procédure modifie_salaire

Il faut compiler le fichier sql qui s’appelle ici modifie_salaire.sql (attention dans cet exemple le nom du script correspond à celui de la procédure,
c’est bien le nom du script sql qu’il faut passer en argument à la commande start).
 SQL>start modifie_salaire
Procedure created.
Appel de la procédure modifie_salaire

begin

modifie_salaire (15,-0.5);
end;
/
• L’utilisation d’un script qui contient les 4 lignes précédentes est bien sûr également possible : [Link]

begin
modifie_salaire (15,-0.5); end;
/
• Lancement du script [Link] :

SQL> start demarre

 V.2.2) Correction des erreurs
• Si le script contient des erreurs, la commande show err permet de visualiser les erreurs.

• Pour visualiser le script global :

– commande l (lettre l)

– pour visualiser la ligne 4 : commande l4

SQL> startmodifie_salaire
Warning: Procedure created with compilation errors.

SQL> show err

Errors for PROCEDURE MODIFIESALAIRE:
LINE/COL ERROR
4/8 PLS-00103: Encountered the symbol "VOYAGE" whenexpecting one of the
following:
:= • ( @ \ i
Resuming parse at line 5, column 21.
SQL> 14
4* update employe set salaire=sala*ilrle+taux)
II.3 - Fonctions

• Une fonction est une procédure qui retourne une valeur. La seule différence syntaxique par rapport à une procédure se traduit par la
présence du mot clé RETURN.

• Une fonction précise le type de donnée qu’elle retourne dans son prototype (signature de la fonction).

• Le retour d’une valeur se traduit par l’instruction RETURN (valeur).

• CREATE [ OR REPLACE ] FUNCTION [<user>].<nom_proc>

(arg1 IN type1 *DEFAULT val_initiale *, arg2 IN type2, …+)
RETURN type_retour AS
[ Déclarations des variables locales ]
BEGI N

Contenu du bloc PLS RETURN

(var_retour );

Ex c e pt i o n

END [<nom_proc>];
/
Fonction -Exemple

Compter le nombre d'employés pour un département donné.

CREATE OR REPLACE FUNCTION proc_dept (p_no IN [Link]%TYPE) RETURN NUMBER AS v_no NUMBER;
BEGIN
SELECT COUNT(deptno) INTO
v_no FROMemp
WHEREdeptno=p_no; RETRUN (v_no);

EN D
;
/

LES FONCTIONS - Opérations de base

 II.4 - LES PACKAGES

V.4.1) La structure Générale d’un package

package_general.sql

CREATE OR REPLACE PACKAGE nom_package IS définitions des types utilisés dans le package;
 prototypes de toutes les procédures et fonctions du package;
END nom_package;
/

CREATE OR REPLACE PACKAGE BODY nom_package IS

déclaration de variables globales; définition de la première procédure; définition de la
deuxième procédure; etc. ...
END nom_package;
/

Un package est composé d’un en tête et d’un corps :

  L’en tête comporte les types de données définis et les prototypes de toutes les procédures (fonctions) du package.

 Le corps correspond à l’implémentation (définition) des procédures (fonctions).

• Le premier END marque la fin de l’en tête du package. Cette partie doit se terminer par / pour que l’en tête soit compilé.
• Le corps (body) du package consiste à implémenter (définir) l’ensemble des procédures ou fonctions qui le constitue. Chaque procédure
est définie normalement avec ses clauses BEGIN ... END.

Le package se termine par / sur la dernière ligne. exemple

• Package paquet1comportant deux procédures (augmentation de salaire et suppression de vendeur) :
create or replace package ges emp is
procedure augmente salaire (v Id emp in number,
v taux salaire in number);

function moyenne salaire (v_Id_employe IN NUMBER)

return NUMBER; -
end ges emp;
/
create or replace package body ges_emp is
procedure augmente salaire ( v Id
emp in number,
v taux salaire in number)
is
beQin
update employa set salaire= salaire• v taux salaire
where Id emp: V Id emp;
commit;
end augmente_J1alaire1

function moyenne salaire (v Id employe IN NUMBBR)

return NUMBER
18
valeur NUMBER;
begin
select avg(salaire)
into valeur
from employe
groupe by emp 1d emp;
return (valeur)1
end moyenne salaire;

end ges_emp;
/
Compilation du package paquet1

SQL> @paquet1 Package created. Package body created

V.4.2) Opérations sur les packages

Exécution de la procédure augmente_salaire du package ges_emp

SQL> begin
2 ges_emp.augmente_salaire(4,50);
3 end;
4/
PL/SQL procedure successfully completed.
• Exécution de la procédure augmente_salaire du package ges_emp SQL> begin
2 ges_emp.augmente_salaire(4,50);
3 end;
4/
PL/SQL procedure successfully completed.
Chapitre 8 : Triggers
Déclencheurs (TRIGGERs)
1. - Définition

Les triggers sont des simples procédures stockées qui s’exécutent implicitement lorsqu’une instruction INSERT, DELETE ou UPDATE porte sur la
table (ou dans certains, cas sur la Vue associée).

2. - Syntaxe

CREATE TRIGGER nom

BEFORE DELETE OR INSERT OR UPDATE ON tablename
[FOR EACH ROW WHEN (condition)]
DECLARE
<<<<déclarations>>>>
BEGIN
............ <<<< bloc d'instructions PL/SQL>>>>
END;

3. - Types de déclencheurs

• ORACLE propose deux types de triggers:

1. les triggers de table (STATEMENT)

– sont déclenchées une seule fois.

2. les triggers de ligne (ROW).

– se déclenchent individuellement pour chaque ligne de la table affectée par le trigger,

• Si l'option FOR EACH ROW est spécifiée, c'est un trigger ligne, sinon c'est un trigger de table.
• doit être unique dans un même schéma

1. peut être le nom d'un autre objet (table, vue, procédure) mais à éviter.
4- Option BEFOR/AFTER

• elle précise le moment quand ORACLE déclenche le trigger,

•les triggers AFTER row sont plus efficaces que les BEFORE row parce qu'ils ne nécessitent pas une double lecture des données.

Déclencheurs

• Elle comprend le type d'instruction SQL qui déclenche le trigger :

• DELETE, INSERT, UPDATE

• On peut en avoir une, deux ou les trois.

• Pour UPDATE, on peut spécifier une liste de colonnes. Dans ce cas, le trigger ne se déclenchera que si l'instruction UPDATE porte
sur l'une au moins des colonnes précisée dans la liste.

• S'il n'y a pas de liste, le trigger est déclenché pour toute instruction UPDATE portant sur la table.
Les triggers lignes

• Pour les triggers lignes, on peut introduire une restriction sur les lignes à l'aide d'une expression logique SQL : c'est la clause WHEN :

– Cette expression est évaluée pour chaque ligne affectée par le trigger.

– Le trigger n'est déclenché sur une ligne que si l'expression WHEN est vérifiée pour cette ligne.

– L'expression logique ne peut pas contenir une sous-question.

– Par exemple, WHEN (:[Link]>0) empêchera l'exécution du trigger si la nouvelle valeur de EMPNO est 0, négative ou NULL.

III.5 - Le corps du trigger

• Le corps du trigger est un bloc PL/SQL :

– Il peut contenir du SQL et du PL/SQL.

– Il est exécuté si l'instruction de déclenchement se produit et si la clause de restriction WHEN, le cas échéant, est évaluée à vrai.

Exemple1 de trigger table

CREATE TRIGGER log AFTER INSERT OR UPDATE

ON Emp
BEGIN
INSERT INTO log (table, date, username, action)
VALUES ('Emp', sysdate, sys_context ('USERENV','CURRENT_USER'), 'INSERT/UPDATE on Emp’)

END ;
;
Exemple2 de trigger table
CREATE OR REPLACETRIGGER PERSON_UPDATE_SALAIREBEFORE UPDATE
ON Employe
BEGIN

DBMS_OUTPUT.PUT_LINE(’ Avant la mise à jour de quelque

employé’);
END;
Maintenant, exécutant update...

UPDATE Employe

SET sal= sal= sal+(sal*0.1);

SQL> UPDATE Employe

SET sal=sal+(sal*0.1);

Avant la mise à jour de quelque

employé

2 rows updated.
 III.6 - Les noms de corrélation (OLD/New)
• Lors de la création de triggers lignes, il est possible d’avoir accès à la valeur ancienne et la valeur nouvelle grâce aux mots clés OLD et
NEW.

– Il n’est pas possible d’avoir accès à ces valeurs dans les triggers de table.

• Si l'instruction de déclenchement du trigger est INSERT, seule la nouvelle valeur a un sens.

• Si l'instruction de déclenchement du trigger est DELETE, seule l'ancienne valeur a un sens.

• La nouvelle valeur est appelée :[Link]

• L'ancienne valeur est appelée :[Link]

– Exemple : IF :[Link] < :[Link] then Exemple1 de trigger row

CREATE OR REPLACETRIGGER Employe_UPDATE_Salaire BEFORE UPDATE
ON Employe FOR EACH ROW BEGIN
DBMS_OUTPUT.PUT_LINE(’Avant la mise à jour ’ || TO_CHAR(:[Link]) || ’ vers ’ || TO_CHAR(:[Link]));
END;
Maintenant, exécutant update...
SQL> UPDATE Employe SET sal= sal+(sal*0.5); Avant la mise à jour 1000 vers 1500
Avant la mise à jour 2000 vers 3000 Avant la mise à jour 4000 vers 6000
 3 rows updated.
Exemple2 de trigger ligne

CREATE OR REPLACE TRIGGER difference_salaire BEFORE UPDATE ON Emp

FOR EACH ROW
WHEN (:[Link] > 0)
DECLARE
sal_diff number;
BEGIN
sal_diff := :[Link] - :[Link];
dbms_output.put(' Old : ' || :[Link] || 'New : ' || :[Link] || 'Difference : ' || sal_diff);
END ;
 III.7 - Les prédicats conditionnels INSERTING, DELETING et UPDATING

Quand un trigger comporte plusieurs instructions de déclenchement (par exemple INSERT OR DELETE OR UPDATE),

on peut utiliser des prédicats conditionnels (INSERTING, DELETING et UPDATING) pour exécuter des blocs de code

spécifiques pour chaque instruction de déclenchement.

Exemple :

CREATE TRIGGER ...

BEFORE INSERT OR UPDATE ON employe
....... BEGIN

......
IF INSERTING THEN ........END IF;
IF UPDATING THEN .........END IF;
...... END;

UPDATING peut être suivi d'un nom de colonne :

CREATE TRIGGER ...

BEFORE UPDATE OF salaire, commission ON employe
....... BEGIN
......
IF UPDATING ('salaire') THEN .........END IF;
...... END;
On peut avoir au maximum un trigger de chacun des types suivants
pour chaque table :
BEFORE UPDATE row BEFORE DELETE row BEFORE INSERT row

BEFORE INSERT statement BEFORE UPDATE statement

BEFORE DELETE statement AFTER UPDATE row
AFTER DELETE row AFTER INSERT row
AFTER INSERT statement AFTER UPDATE statement
AFTER DELETE statement.
Même pour UPDATE, on ne peut pas en avoir plusieurs avec des
noms de colonnes différents.
Création de triggers de ligne à l'aide de
SQL*Plus
SQL> CREATE OR REPLACE TRIGGER CHECK_SALARY
2 BEFORE INSERT OR UPDATE OF sal ON emp
3 FOR EACH ROW
4 BEGIN
5 IF NOT (:[Link] IN ('MANAGER' , 'PRESIDENT'))
6 AND :[Link] > 5000
7 THEN
8 RAISE_APPLICATION_ERROR
9 (-20202, 'EMPLOYEE CANNOT EARN THIS AMOUNT');
10 END IF;
11 END;

Developpement sous Oracle : Oracle SQL, PL/SQL & Oracle Forms Année Académique : 2023-2024 88
Création de triggers de ligne à l'aide de Procédure Builder

Developpement sous Oracle : Oracle SQL, PL/SQL & Oracle Forms Année Académique : 2023-2024 89
 Utilisation des qualificatifs OLD et NEW

SQL>CREATE OR REPLACE TRIGGER audit_emp_values

2 AFTER DELETE OR INSERT OR UPDATE ON emp
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO audit_emp_table (user_name,
6 timestamp, id, old_last_name, new_last_name,
7 old_title, new_title, old_salary, new_salary)
8VALUES (USER, SYSDATE, :[Link], :[Link], 9
10 :[Link], :[Link], :[Link],
:[Link], :[Link] );
11 END;
12 /

Developpement sous Oracle : Oracle SQL, PL/SQL & Oracle Forms Année Académique : 2023-2024 90
Restriction d'un trigger de ligne
SQL>CREATE OR REPLACE TRIGGER derive_commission_pct
2 BEFORE INSERT OR UPDATE OF sal ON emp
3 FOR EACH ROW
4 WHEN ([Link] = 'SALESMAN')
5 BEGIN
6 IF INSERTING
7 THEN :[Link] := 0;
8 ELSIF :[Link] IS NULL
9 THEN :[Link] := 0;
10 ELSE :[Link] := :[Link] * (:[Link]/:[Link]);
11 END IF;
12END;
13 /

Developpement sous Oracle : Oracle SQL, PL/SQL & Oracle Forms Année Académique : 2023-2024 91
 Démonstration : triggers
CREATE OR REPLACE TRIGGER audit_emp_trig
AFTER UPDATE or INSERT or DELETE on EMP
FOR EACH ROW
BEGIN
IF DELETING THEN var_pack.set_g_del(1);
ELSIF INSERTING THEN var_pack.set_g_ins(1);
ELSIF UPDATING ('SAL')
THEN var_pack.set_g_up_sal(1);
ELSE var_pack.set_g_upd(1);
END IF;
END audit_emp_trig;

CREATE OR REPLACE TRIGGER audit_emp_tab

AFTER UPDATE or INSERT or DELETE on EMP
BEGIN
audit_emp;
END audit_emp_tab;

Developpement sous Oracle : Oracle SQL, PL/SQL & Oracle Forms Année Académique : 2023-2024 92
Tableau comparatif
Triggers / Procédures stockées
Triggers Procédure

Utilisation de CREATE TRIGGER Utilisation de CREATE PROCEDURE

Le dictionnaire de données contient Le dictionnaire de données contient

le code source et le p-code le code source et le p-code

Appel implicite Appel explicite

Commandes COMMIT, SAVEPOINT, Commandes COMMIT, SAVEPOINT,

ROLLBACK non autorisées ROLLBACK autorisées

Developpement sous Oracle : Oracle SQL, PL/SQL & Oracle Forms Année Académique : 2023-2024 93
 Gestion des triggers
Désactivation ou réactivation d'un trigger de
base de données :
ALTER TRIGGER trigger_name DISABLE | ENABLE;

Désactivation ou réactivation de tous les triggers

d'une table :
ALTER TABLE table_name DISABLE | ENABLE ALL;
TRIGGERS;

Recompilation d'un trigger pour une table :

ALTER TRIGGER trigger_name COMPILE;

Developpement sous Oracle : Oracle SQL, PL/SQL & Oracle Forms Année Académique : 2023-2024 94
Syntaxe DROP TRIGGER

 Pour supprimer un trigger de la base de

données, utilisez la syntaxe DROP TRIGGER :
; DROP TRIGGER trigger_name;

Exemple
SQL> DROP TRIGGER secure_emp;
Trigger dropped

Developpement sous Oracle : Oracle SQL, PL/SQL & Oracle Forms Année Académique : 2023-2024 95
 Chapitre 9 : Concepts avancés
sur les curseurs explicites
 Objectifs
A la fin de ce chapitre,vous pourrez:

• Écrire un curseur utilisant des paramètres

• Déterminer dans quels cas une clause FOR UPDATE
doit être utilisée dans un curseur
• Déterminer dans quel cas la clause WHERE
CURRENT OF doit être utilisée
• Ecrire un curseur utilisant une sous-interrogation

7-
 Curseurs paramétrés
Syntaxe:
CURSOR cursor_name
[(parameter_name datatype, ...)]
IS
select_statement;

• Transmettre des paramètres au curseur au

moment de son ouverture et de l’exécution de
l’interrogation.
• Ouvrir un curseur explicite à plusieurs reprises, en
renvoyant un ensemble actif différent à chaque
fois.
OPEN cursor_name(parameter_value,.....) ;

7-
98
 Curseurs paramétrés
Transmettre le numero du service et l’intitulé du
poste à la clause WHERE, dans l’instruction SELECT
du curseur
DECLARE
CURSOR emp_cursor
(p_deptno NUMBER, p_job VARCHAR2) IS
SELECT employee_id, last_name
FROM employees
WHERE department_id = p_deptno
AND job_id = p_job;
BEGIN
OPEN emp_cursor (80, 'SA_REP');
. . .
CLOSE emp_cursor;
OPEN emp_cursor (60, 'IT_PROG');
. . .
END;

7-
99
 Clause FOR UPDATE

Syntaxe:

SELECT ...
FROM ...
FOR UPDATE [OF column_reference][NOWAIT];

• Utiliser un verrouillage pour interdire l’accès

pendant la durée d’une transaction.
• Verrouiller les lignes avant la mise à jour ou la
suppression.

7-
 Clause FOR UPDATE

Extraire les employés qui travaillent dans le service

80 et mettre à jour leur salaire.
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, last_name, department_name
FROM employees,departments
WHERE employees.department_id =
departments.department_id
AND employees.department_id = 80
FOR UPDATE OF salary NOWAIT;

7-
 Clause WHERE CURRENT OF

Syntaxe:
WHERE CURRENT OF cursor ;

• Utiliser les curseurs pour mettre à jour ou

supprimer la ligne en cours.
• Inclure la clause FOR UPDATE dans l’interogation
du curseur pour verrouiller au préalable les lignes.
• Utiliser la clause WHERE CURRENT OF pour
référencer la ligne en cours à partir d’un curseur
explicite.

7-
102
 Clause WHERE CURRENT OF
DECLARE
CURSOR sal_cursor IS
SELECT e.department_id, employee_id, last_name, salary
FROM employees e, departments d
WHERE d.department_id = e.department_id
and d.department_id = 60
FOR UPDATE OF salary NOWAIT;
BEGIN
FOR emp_record IN sal_cursor
LOOP
IF emp_record.salary < 5000 THEN
UPDATE employees
SET salary = emp_record.salary * 1.10
WHERE CURRENT OF sal_cursor;
END IF;
END LOOP;
END;
/

7-
103
 Curseurs contenant des sous-
interrogations
 Example:
 DECLARE
 CURSOR my_cursor IS
 SELECT t1.department_id,
t1.department_name, [Link]
FROM departments t1,
(SELECT department_id,COUNT(*) AS STAFF
FROM employees GROUP BY department_id) t2

WHERE t1.department_id = t2.department_id

AND [Link] >= 3;
...

7-
104
 Synthèse

Ce chapitre vous a permis d’apprendre à:

• Renvoyer des ensembles actifs différents à l’aide
de curseurs paramétrés.
• Définir des curseurs contenant des sous-
interrogations corrélés.
• Manipuler des curseurs explicites à l’aide de
commandes contenant les clauses:
– FOR UPDATE
– WHERE CURRENT OF

7-
 ORACLE PL/SQL

Merci pour votre attention

Questions

21/02/2024 147