Le Langage Oracle PL/SQL

Préparé par :
Nadhem Bel Hadj

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 PLAN (1/2)
Introduction à PL/SQL,
Structure d’un bloc PL/SQL,
Gestion des variables PL/SQL,
Les tables PL/SQL,
Les records PL/SQL,
Le typage dynamique,
Opérateurs et fonctions PL/SQL,
Référence des variables non PL/SQL,
Nadhemb@[Link] 2

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 PLAN (2/2)
Blocs imbriqués,
La clause INTO
La manipulation des données,
Les transactions,
Les structures de contrôle PL/SQL,
Les curseurs,
La gestion des exceptions,
Les Triggers de base de données.
Nadhemb@[Link] 3

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 PL/SQL introduction

Qu’est ce que PL/SQL?

PL/SQL est une extension de SQL avec des

concepts de langage de programmation.
La manipulation de données et les requêtes
sont incluses dans le code PL/SQL.

Nadhemb@[Link] 4

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Avantages de PL/SQL

Tous ceux d’un langage procédural de

gestion, en particulier :

Gestion d’Erreurs
Portabilité
…
+ Intégration à la base
Nadhemb@[Link] 5

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 PL/SQL architecture
SQL
SQL
Application SGBD/R
SQL
SQL

SQL
IF...THEN
SQL
Application ELSE Oracle9i
SQL avec PL/SQL
END IF;
SQL

Nadhemb@[Link] 6

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Environnement PL/SQL
Moteur PL/SQL dans Oracle 9i
Oracle9i Server
SQL*Plus PL/SQL Engine

non-SQL Procedural
Block Block
PL/SQL Statement
PL/SQL SQL
Executor

SQL Statement Executor

Nadhemb@[Link] 7
I-8a
Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Environnement PL/SQL
Moteur PL/SQL dans les outils
Forms
Trigger PL/SQL Engine

non-SQL Procedural
Block Block
PL/SQL Statement
PL/SQL SQL
Executor

Oracle 9i Server
PL/SQL Engine

Procedural SQL Statement Executor

Statement
Executor

Nadhemb@[Link] 8
I-8b
Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Objectifs

Déclarer et utiliser des variables et des

constantes en PL/SQL.

Assigner de nouvelles valeurs aux

variables dans la section exécutable.

Créer et exécuter un bloc PL/SQL.

Nadhemb@[Link] 9

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Structure d'un Block PL/SQL

[ DECLARE ]
- Variables, constantes, curseurs,
& exceptions

BEGIN
- Ordres SQL
- Instructions de Contrôle PL/SQL
- Branchements conditionnels, etc.

[ EXCEPTION ]
- Traitements à effectuer si erreurs

END ;

Nadhemb@[Link] 10

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Règles Syntaxiques d'un Bloc
PL/SQL
Les instructions peuvent être écrites
sur plusieurs lignes.
Identifiants doivent :
Contenir jusqu'à 30 caractères.
Être encadrés de guillemets s’ils
contiennent un mot réservés.
Commencer par une lettre.
Avoir un nom distinct de celui d'une table
ou d'une colonne de la base.
Nadhemb@[Link] 11
1-6a
Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Règles Syntaxiques d'un Bloc
PL/SQL
Les chaînes de caractères et les dates doivent
être entourées de simples cotes ( ' ' ).
Les nombres peuvent avoir de simples
valeurs ou une notation scientifique
Les commentaires peuvent être
- sur plusieurs lignes avec :
/* début de commentaire
fin de commentaire*/
- sur une ligne précédée de :
-- début et fin de commentaire
Nadhemb@[Link] 12
1-6b
Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Gestion des Variables en
PL/SQL

Déclarer et initialiser les variables dans la

section déclarative.
Assigner une valeur à une variable.
Transmettre des valeurs dans les blocs
PL/SQL à l'aide de paramètres.
Visualiser les résultats à la console ou dans
un fichier.
DBMS_OUTPUT.PUT_LINE
UTL_FILE

Nadhemb@[Link] 13

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Déclaration de Variables et
Constantes - Syntaxe

NomVar [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr];

Adopter des conventions pour nommer des

objets.
Initialiser les constantes et les variables
déclarées NOT NULL.
Déclarer au plus un identifiant par ligne.
Le type peut être primitif ou objet.
Nadhemb@[Link] 14

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Principaux types primitifs

BINARY_INTEGER
NUMBER [ ( precision, scale ) ]
CHAR [ ( longueur_maximum ) ]
LONG
VARCHAR2 ( longueur_maximum )
DATE
BOOLEAN
ROWID (pseudo-colonne)
Etc…

Nadhemb@[Link] 15

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Déclaration de Variables
Exemples

c CHAR( 1 );
nom VARCHAR2(10) := ‘Scott’;
cpt BINARY_INTEGER := 0;
tot NUMBER( 9, 2 ) := 0;
Dat := SYSDATE + 7;
nb CONSTANT NUMBER ( 3, 2 ) := 8.25;
vrai BOOLEAN NOT NULL := TRUE;

Nadhemb@[Link] 16

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Exemple de prgm PL/SQL

SQL> SET SERVEROUTPUT ON

SQL> DECLARE
2 nom VARCHAR2(15) := 'SCOTT';
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE(nom);
5 END;
6 /
SCOTT

PL/SQL procedure successfully completed.

Nadhemb@[Link] 17

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Types de Données Composites

Deux Types de Données Composites:

• TABLES PL/SQL

• RECORDS PL/SQL

Nadhemb@[Link] 18

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Structure de Table PL/SQL

Clef Primaire Colonne

... ...

1 SCOTT

2 SMITH

3 KING

... ...

BINARY_INTEGER SCALAIRE
Nadhemb@[Link] 19

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Déclaration d'une TABLE
PL/SQL
DECLARE
TYPE nom_type IS TABLE OF type_scalaire
[ NOT NULL ] INDEX BY BINARY_INTEGER ;

id_var nom_type ;

DECLARE
TYPE nom_type
IS TABLE OF VARCHAR2 (25)
INDEX BY BINARY_INTEGER ;

toto1 nom_type;
toto2 nom_type;
.../...
Nadhemb@[Link] 20

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Record PL/SQL

Peuvent contenir un ou plusieurs

champs de type scalaire, RECORD ou
TABLE.
Sont similaires à la structure
d'enregistrements utilisée dans les L3G.
Traitent un ensemble de champs
comme une unité logique.
Pratiques pour récupérer et traiter les
données d'une table.
Nadhemb@[Link] 21

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Déclarer un RECORD PL/SQL

DECLARE
TYPE nom_type
nom_type IS RECORD
( champ1 type_att [NOT NULL] { := | DEFAULT expr}
expr}
[,champ2
[,champ2 type_att [NOT NULL] { := | DEFAULT expr }] );

id_var nom_type;

DECLARE
TYPE employe IS RECORD
( ename VARCHAR2( 25 ),
job VARCHAR2( 25 ),
sal NUMBER( 7,2 ) );

emp employe;
.../...
Nadhemb@[Link] 22

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Exemple

SQL> DECLARE
2 TYPE employe IS RECORD
3 ( ename VARCHAR2( 25 ),
4 job VARCHAR2( 25 ),
5 sal NUMBER( 7,2 )
6 );
7 empl employe;
8 BEGIN
9 SELECT ename, job, sal
10 INTO empl
11 FROM emp
12 WHERE empno = 7839;
13 DBMS_OUTPUT.PUT_LINE([Link]);
14 DBMS_OUTPUT.PUT_LINE([Link]);
15 DBMS_OUTPUT.PUT_LINE([Link]);
16 END;
17 /
KING PRESIDENT 5000
Nadhemb@[Link] 23

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Le typage dynamique :
l‘attribut %TYPE
Permet de déclarer une variable à partir :
Ø D'une autre variable déjà déclarée
Ø De la définition d’un attribut de la base de
données

à Préfixer %TYPE avec :

Le nom de la variable déclarée précédemment
Le nom [Link] de la base de données

à Dans ce cas PL/SQL évalue le type de donnée et sa taille (ce qui

peut nécessité un accès au dictionnaire de la base).
Nadhemb@[Link] 24

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 L'Attribut %TYPE - Exemple

DECLARE
nom [Link]%TYPE;
job [Link]%TYPE;
balance NUMBER( 7, 2 );
min_balance balance%TYPE := 10;

Ø Le type de données de la colonne peut être inconnu.

Ø Le type de données de la colonne peut changer en

exécution.
à Maintenance plus aisée.

Nadhemb@[Link] 25

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Exemple

SQL> SET SERVEROUTPUT ON

SQL> DECLARE
2 nom [Link]%TYPE;
3 BEGIN
4 SELECT ename
5 INTO nom FROM emp
6 WHERE ROWNUM = 1;
7 DBMS_OUTPUT.PUT_LINE(nom);
8 END;
SMITH

PL/SQL procedure successfully completed.

Nadhemb@[Link] 26

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 L'Attribut %ROWTYPE

Déclarer une variable à partir d'un

ensemble de colonnes d'une table ou
d'une vue.
Préfixer %ROWTYPE avec la nom de la
table de la base de données.
Les champs dans le RECORD ont les
mêmes noms et les mêmes types de
données que les colonnes de la table ou
de la vue associées.
Nadhemb@[Link] 27

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Exemple

SQL> DECLARE
2 empl emp%ROWTYPE;
3 BEGIN
4 SELECT *
5 INTO empl FROM emp
6 WHERE ROWNUM = 1;
7 DBMS_OUTPUT.PUT_LINE([Link]);
8 DBMS_OUTPUT.PUT_LINE([Link]);
9 END;
SMITH
800

Nadhemb@[Link] 28

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Intérêts

Le nombre de colonnes & leur type peuvent

être inconnus.
Le nombre de colonnes & leur type peuvent
changer.
à Utile (sinon indispensable) lorsqu'on recherche
Une ligne avec l'ordre SELECT.
Plusieurs lignes avec un curseur explicite.
DECLARE
dep dept%ROWTYPE;
empl emp%ROWTYPE;
.../...
Nadhemb@[Link] 29

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Opérateurs en PL/SQL

Valeurs de vérité
Arithmétiques
Identiques à SQL
Concaténation
Opérateur exponentiel ( ** )
Parenthèsage pour contrôler la priorité
des opérations

Nadhemb@[Link] 30

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Fonctions en PL/SQL

Intégrées :
Numériques
Caractères
Identiques à SQL
Conversion de type
Date
Non intégrées :
GREATEST
LEAST
Fonctions de groupe

Nadhemb@[Link] 31

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Fonctions en PL/SQL -
Exemples
SQL> DECLARE
2 enr [Link]%ROWTYPE;
3 adr VARCHAR2(64);
4 BEGIN
5 SELECT *
6 INTO enr FROM emp
7 WHERE ROWNUM = 1;
8 adr := UPPER([Link]) || CHR(10) ||
9 LOWER([Link]) || CHR(10) ||
10 TO_CHAR([Link],'DD/MM/YYYY');
11 DBMS_OUTPUT.PUT_LINE(adr);
12 END;
13 /
SMITH clerk 17/12/1980

Nadhemb@[Link] 32

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Conversion de Type de
Donnée
Convertir des données en type de données
comparables.
Des types de données hétérogènes peuvent
provoquer une erreur et/ou affecter les
performances.
Fonctions de Conversion :
TO_CHAR
TO_DATE
TO_NUMBER

Nadhemb@[Link] 33

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Conversion de Type de
Donnée - Exemple

Cette instruction è une erreur :

str := Le || ':' || SYSDATE ;

Pour corriger l'erreur on doit appliquer

la fonction de conversion TO_CHAR

str := Le || ':' || TO_CHAR( SYSDATE );

Nadhemb@[Link] 34

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Référencer des Variables Non
PL/SQL

Référencer des variables non PL/SQL

comme des variables « bind » (nouées).
Préfixer ces variables par deux points(:)

VAR [ variable [ NUMBER | CHAR | CHAR (n) | VARCHAR2 (n) |

NCHAR | NCHAR (n) | NVARCHAR2 (n) |
CLOB | NCLOB | REFCURSOR ] ]

Nadhemb@[Link] 35

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Exemple

SQL> VARIABLE income NUMBER

SQL> DECLARE
2 enr emp%ROWTYPE;
3 adr VARCHAR2(64);
4 BEGIN
5 SELECT *
6 INTO enr FROM emp
7 WHERE ROWNUM = 1;
8 :income := [Link]*12;
9 adr := [Link] || CHR(10) ||
10 :income|| CHR(10) ||
11 TO_CHAR([Link],'DD/MM/YYYY');
12 DBMS_OUTPUT.PUT_LINE(adr);
13 END;
14 /
SMITH
9600
17/12/1980
Nadhemb@[Link] 36

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Imbrication et Portée d'une
Variable
Les instructions peuvent être imbriquées là où les
instructions exécutables sont autorisées.
La section EXCEPTION peut contenir des blocs
imbriqués.
Les boucles possèdent leur propre portée
les incréments y sont définis
Un identifiant est visible dans les régions où on
peut référencer cet identifiant :
Un bloc voit les objets du bloc de niveau supérieur.
Un bloc ne voit pas les objets des blocs de niveau
inférieur.
Nadhemb@[Link] 37

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Blocs Imbriqués et Portée

DECLARE
x INTEGER;
BEGIN Portée de x
. . .
DECLARE
y NUMBER;
BEGIN Portée de y
. . .
END ;
. . .
END ;
/
Nadhemb@[Link] 38

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Interaction avec la base

Utiliser les commandes SELECT, INSERT,

UPDATE et DELETE dans un bloc PL/SQL.

Déterminer le résultat des ordres SQL en

utilisant les attributs du curseur implicite.

Contrôler les transactions avec PL/SQL.

Nadhemb@[Link] 39

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Clause INTO

SELECT list_attribut
INTO nom_var | nom_record
FROM table
WHERE condition;

Cette clause est obligatoire en PL/SQL.

La clause INTO convient si une seule ligne de

données est renvoyée.

Nadhemb@[Link] 40

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Exemple

SQL> SET SERVEROUTPUT ON

SQL> DECLARE
2 nom [Link]%TYPE;
3 BEGIN
4 SELECT ename INTO nom
5 FROM [Link]
6 WHERE empno = 7839; -- matricule unique & NON NULL
7 DBMS_OUTPUT.PUT_LINE(nom);
8 END;
9 /
KING

PL/SQL procedure successfully completed.

Nadhemb@[Link] 41

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Exemple
SQL> DECLARE
2 rec [Link]%ROWTYPE;
3 empl VARCHAR2(64);
4 BEGIN
5 SELECT *
6 INTO rec FROM emp
7 WHERE ROWNUM = 1;
8 empl := UPPER([Link]) ||’ ‘||
9 LOWER([Link]) ||’ ‘||
10 TO_CHAR([Link],'DD/MM/YYYY');
11 DBMS_OUTPUT.PUT_LINE(empl);
12 END;
13 /
SMITH clerk 17/12/1980

PL/SQL procedure successfully completed.

Nadhemb@[Link] 42

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Exceptions avec SELECT

Les ordres SELECT en PL/SQL doivent

ramener une et une seule ligne.
Si aucune ou plusieurs lignes sont
retrouvées une exception est
déclenchée.
On peut lever des exceptions avec
PL/SQL
Exceptions de la clause SELECT :
TOO_MANY_ROWS
Nadhemb@[Link] NO_DATA_FOUND 43

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Exception TOO_MANY_ROWS

SQL> DECLARE
2 nom [Link]%TYPE;
3 BEGIN
4 SELECT ename INTO nom
5 FROM [Link]
6 WHERE deptno = 10;
7 DBMS_OUTPUT.PUT_LINE(nom);
8 END;
9 /
DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested
number of rows
ORA-06512: at line 4
Nadhemb@[Link] 44

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Exception NO_DATA_FOUND

SQL> DECLARE
2 nom [Link]%TYPE;
3 BEGIN
4 SELECT ename INTO nom
5 FROM [Link]
6 WHERE deptno = 50;
7 DBMS_OUTPUT.PUT_LINE(nom);
8 END;
9 /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4

Nadhemb@[Link] 45

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Manipulation de Données

Modifier les tables de la base de données en

utilisant les commandes du LMD et du LCD :

INSERT
UPDATE
DELETE
COMMIT
ROLLBACK

Nadhemb@[Link] 46

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 UPDATE (idem. si
INSERT/DELETE)
SQL> DECLARE
2 nom [Link]%TYPE;
3 matricule [Link]%TYPE := 7839;
4 salaire [Link]%TYPE := 5500;
5 BEGIN
6 UPDATE [Link] SET sal = salaire
7 WHERE empno = matricule;
8 SELECT ename ,sal
9 INTO nom, salaire
10 FROM [Link]
11 WHERE empno = matricule;
12 DBMS_OUTPUT.PUT_LINE(nom||’ ‘||TO_CHAR(salaire));
13 END;
14 /
KING 5500

PL/SQL procedure successfully completed.

Nadhemb@[Link] 47

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Transaction

Une transaction est une unité de travail composé de

plusieurs actions élémentaires.
Chaque transaction est considérée comme atomique
et fait évoluer la base de données d'un état cohérent
vers un autre état cohérent.
Soit une transaction s'exécute correctement et les
modifications qu'elle a engendrées deviennent
publiques et définitives ("Commit"), soit tout se passe
comme si elle n'avait jamais existé ("Rollback").

Nadhemb@[Link] 48

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Exemple de curseur implicite
SQL> DECLARE
2 -- nombre de lignes mises à jour.
3 nb_ligne NUMBER := 0;
4 BEGIN
5 UPDATE [Link] SET sal = sal*1.1
6 WHERE deptno = 30;
7 nb_ligne := SQL%ROWCOUNT;
8 DBMS_OUTPUT.PUT_LINE(ligne||’ ligne(s)’);
9 ROLLBACK;
10 END;
11 /
6 ligne(s)

PL/SQL procedure successfully completed.

Nadhemb@[Link] 49

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Commandes COMMIT et
ROLLBACK

Une transaction démarre lors de la première

commande LMD qui suit un COMMIT ou un
ROLLBACK.

Une transaction se termine en utilisant l'une

des commandes SQL : COMMIT ou
ROLLBACK.

Commandes de verrouillage supplémentaires:

SELECT . . . FOR UPDATE .
Nadhemb@[Link] 50

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Flots de PL/SQL

Structures de contrôle conditionné

IF…THEN

Structures de Contrôle Itératif

Boucle de base LOOP
Boucle FOR
Boucle WHILE
Instruction EXIT

Nadhemb@[Link] 51

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Syntaxe de l’instruction IF

Les actions conditionnées sont déclenchées

par des tests :
IF condition THEN
instructions;
[ ELSIF conditions THEN
instructions; ]
[ ELSE
instructions; ]
END IF;
Ø ELSIF en un mot
Ø END IF; en deux mots
Nadhemb@[Link] 52

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Instruction IF-THEN-ELSE
Exemple : Fac(N)
SQL> CREATE OR REPLACE FUNCTION FAC (n POSITIVE)
2 RETURN INTEGER IS
3 BEGIN
4 IF n = 1 THEN
5 RETURN 1;
6 ELSE
7 RETURN n * FAC(n-1);
8 END IF;
9 END FAC;
10 /
Function created.
SQL> SELECT fac(5) FROM DUAL;
FAC(5)
---------
120
Nadhemb@[Link] 53

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Instruction IF-THEN-ELSIF
Exemple : Comb(n,p)
SQL> CREATE OR REPLACE FUNCTION COMB (n POSITIVE,
p POSITIVE)
2 RETURN INTEGER IS
3 BEGIN
4 IF n>p THEN
5 RETURN FAC(n)/(FAC(p)*FAC(n-p));
6 ELSIF n=p THEN
7 RETURN 1;
8 ELSE
9 RETURN 0;
10 END IF;
11 END COMB;
12 /
SQL> SELECT comb(5,2) FROM DUAL;
COMB(5,2)
---------
Nadhemb@[Link] 54
10
Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Valeur « NULL »

Vous devez comparer les valeurs nulles avec

l'opérateur relationnel IS NULL.

Une expression arithmétique contenant une

valeur NULL est évaluée à NULL.

Une expression de concaténation traite les

valeurs NULL comme des chaînes vides.
Nadhemb@[Link] 55

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Instructions LOOP

Les boucles réitèrent une instruction ou un

ensemble d'instructions.
Trois types de boucles
Boucle LOOP
Boucle FOR
Boucle WHILE
L'instruction EXIT permet de quitter la boucle

LOOP -- Début de boucle

instruction1 ; -- Instructions
.../...
EXIT [ WHEN condition ]; -- Sortie de la boucle
END LOOP ; -- Fin de boucle
Nadhemb@[Link] 56

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Boucle de Base - Exemple

SQL> DECLARE
2 ligne [Link]%ROWTYPE;
3 CURSOR c1 IS SELECT * FROM emp
4 ORDER BY sal DESC;
5 BEGIN
6 OPEN c1;
7 LOOP
8 FETCH c1 INTO ligne;
9 EXIT WHEN c1%ROWCOUNT > 5;
10 DBMS_OUTPUT.PUT_LINE([Link] || ' ' ||
11 TO_CHAR([Link]));
12 END LOOP;
13 CLOSE c1;
14 END;
15 /
KING 5000
Nadhemb@[Link] 57
.../...
Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Boucle FOR - Syntaxe

La boucle FOR évite de tester l’incrément

dans le corps de boucle.
FOR index IN [ REVERSE ] début . . fin LOOP
instruction1 ;
instruction2 ;
. . .
END LOOP;

L'incrément est déclaré implicitement

Nadhemb@[Link] 58

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Boucle FOR - Exemple

SQL> SET SERVEROUTPUT ON

SQL> DECLARE
2 ligne [Link]%ROWTYPE;
3 CURSOR c1 IS SELECT * FROM emp
4 ORDER BY sal DESC;
5 BEGIN
6 OPEN c1;
7 FOR i IN 1..5 LOOP
8 FETCH c1 INTO ligne;
9 DBMS_OUTPUT.PUT_LINE([Link] || ' ' ||
10 TO_CHAR([Link]));
11 END LOOP;
12 CLOSE c1;
13 END;
14 /
KING 5000
Nadhemb@[Link] 59
.../...
Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 IN REVERSE

SQL> DECLARE
2 factoriel NUMBER := 1;
3 BEGIN
4 FOR I IN REVERSE 1..4 LOOP
5 factoriel := factoriel * I;
6 DBMS_OUTPUT.PUT_LINE(
7 'La factorielle = ' || factoriel);
8 END LOOP;
9 END;
10 /
La factorielle = 4
La factorielle = 12
La factorielle = 24
La factorielle = 24

Nadhemb@[Link] 60

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Boucle WHILE - Syntaxe

On utilise une boucle WHILE pour répéter des instructions tant

qu'une condition est vraie (« TRUE »).

WHILE condition LOOP La condition est

instruction1 ; évaluée au
instruction2 ; début de chaque
. . . itération.
END LOOP;

Nadhemb@[Link] 61

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Boucle WHILE - Exemple

SQL> DECLARE
2 ligne [Link]%ROWTYPE;
3 CURSOR c1 IS SELECT * FROM emp
4 ORDER BY sal DESC;
5 BEGIN
6 OPEN c1;
7 WHILE (c1%ROWCOUNT < 5) LOOP
8 FETCH c1 INTO ligne;
9 DBMS_OUTPUT.PUT_LINE([Link] || ' ' ||
10 TO_CHAR([Link]));
11 END LOOP;
12 CLOSE c1;
13 END;
14 /
KING 5000
.../...
Nadhemb@[Link] 62

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Boucles Imbriquées et Labels

Boucles imbriquées à des niveaux multiples

Il faut utiliser des labels pour distinguer les

blocs et les boucles

Quitter la boucle désirée avec l'instruction

EXIT référençant le label.

Nadhemb@[Link] 63

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Boucles Imbriquées et Labels
Exemple
Quitter la boucle extérieure d’après les valeurs d'un bloc interne :
SQL> BEGIN
2 <<ext_loop>>
3 FOR i_ext IN 1..2 LOOP
4 <<int_loop>>
5 FOR i_int IN 1..4 LOOP
6 DBMS_OUTPUT.PUT_LINE(
7 ‘Ext Loop counter is ' || i_ext ||
8 ' Int Loop counter is ' || i_int);
9 EXIT ext_loop WHEN i_int = 2;
10 END LOOP int_loop;
11 END LOOP ext_loop;
12 END;
13 /
Ext Loop counter is 1 Int Loop counter is 1
Ext Loop counter is 1 Int Loop counter is 2
Nadhemb@[Link] 64

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Interaction avec
la base Oracle

SQL est un langage ensembliste et

PL/SQL est un langage procédural pour
gérer cette « distorsion » PL/SQL
permet de gérer des curseurs :

Ø Curseurs explicites : Déclarés et

nommés par le développeur.

Nadhemb@[Link] 65

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Objectifs

Curseurs explicites pour extraire plusieurs

enregistrements de la base de données

Attributs d’un curseur

ISOPEN, NOTFOUND, WHERE CURRENT OF...

Curseurs et boucle

Nadhemb@[Link] 66

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Qu’est ce qu’un Curseur ?

On utilise un curseur dès qu’on ignore combien

de lignes une requête peut rapporter.
Un curseur est une zone de travail privée SQL.
Toute requête SQL exécutée par le serveur
possède son propre curseur.
Deux sortes de curseur :
Curseurs implicites : déclarés pour toutes les
instructions LMD et les SELECT en PL/SQL
Curseurs Explicites : déclarés et nommés au sein du
code source

Nadhemb@[Link] 67

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Principale fonctionnalités d’un
Curseur Explicite

Permet de traiter plusieurs lignes

ramenées par une requête.
Connaître la ligne en cours de
traitement.
Contrôler les opérations au sein
d’un bloc PL/SQL.
Nadhemb@[Link] 68

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Contrôler les Curseurs
Explicites

NON

DECLARE OPEN FETCH VIDE? CLOSE

OUI

Créer une Ouvrir Extraire la ligne Test pour Fermer

zone nommée & exécuter courante dans des l’existence
SQL variables déclarées de lignes
dans le bloc
Reprise de
la phase
d’extraction
Nadhemb@[Link] 69

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Déclaration du Curseur -
Syntaxe

Ne pas inclure la clause INTO dans la

déclaration du curseur

DECLARE
CURSOR nom_curseur IS
clause_select;

Nadhemb@[Link] 70

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Exemple

SQL> DECLARE
2 ligne [Link]%ROWTYPE;
3 CURSOR c1 IS SELECT * FROM emp
4 ORDER BY sal DESC;
5 BEGIN
6 OPEN c1;
7 LOOP
8 FETCH c1 INTO ligne;
9 EXIT WHEN c1%ROWCOUNT > 5;
10 DBMS_OUTPUT.PUT_LINE([Link] || ' ' ||
11 TO_CHAR([Link]));
12 END LOOP;
13 CLOSE c1;
14 END;
15 /
KING 5000
SCOTT 3000
...
Nadhemb@[Link] 71

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Ouverture du Curseur -
Syntaxe

L’ouverture du curseur force l’exécution de la

requête
OPEN nom_cursor;

Si la requête retourne aucune ligne, aucune

exception n’est levée
* Il faut tester le résultat après un fetch en utilisant
les attributs du curseur

Nadhemb@[Link] 72

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Extraction des Données

Récupérer les valeurs de la ligne courante dans

des variables containers
FETCH cursor INTO container;

Par indentification, la position de chaque

variable doit correspondre exactement à celle
de la colonne correspondante
Tester s’il y a d’autres lignes de données

Nadhemb@[Link] 73

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Extraction des données

SQL> SET SERVEROUTPUT ON

SQL> DECLARE
2 nom [Link]%TYPE;
3 sal [Link]%TYPE;
4 CURSOR c1 IS SELECT ename,sal FROM emp
5 ORDER BY sal DESC;
6 BEGIN
7 OPEN c1;
8 WHILE (c1%ROWCOUNT < 5) LOOP
9 FETCH c1 INTO nom,sal;
10 DBMS_OUTPUT.PUT_LINE(nom || ' ' ||
TO_CHAR(sal));
11 END LOOP;
12 CLOSE c1;
13 END;
14 /
KING 5000
SCOTT 3000
Nadhemb@[Link] 74

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Fermeture du Curseur

Libérer la mémoire en fermant le curseur une

fois les lignes rapportées.

CLOSE nom_curseur;

Réouvrir le curseur au cas où on en a besoin

Il est inutile d’essayer de ramener des
données d’un curseur fermé.

Nadhemb@[Link] 75

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Attributs d’un Curseur

Attribut Type Description

%ISOPEN BOOLEAN TRUE si le curseur est

toujours ouvert.

%NOTFOUND BOOLEAN TRUE si le dernier fetch

n’a ramené aucune ligne.

%FOUND BOOLEAN TRUE tant que l’extraction

fetch peut se poursuivre.

%ROWCOUNT NUMBER Nombre total de lignes

ramenées jusqu’à présent.

Nadhemb@[Link] 76

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 L’Attribut %ISOPEN

SQL> DECLARE
2 nom [Link]%TYPE;
3 sal [Link]%TYPE;
4 CURSOR c1 IS SELECT ename,sal FROM emp ORDER BY sal
DESC;
5 BEGIN
6 IF NOT c1%ISOPEN THEN
7 OPEN c1;
8 END IF;
9 WHILE (c1%ROWCOUNT < 5) LOOP
10 FETCH c1 INTO nom,sal;
11 DBMS_OUTPUT.PUT_LINE(nom || ' ' || TO_CHAR(sal));
12 END LOOP;
13 IF c1%ISOPEN THEN
14 CLOSE c1;
15 END IF;
16 END;
Nadhemb@[Link] KING 5000 ... 77

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Clause WHERE CURRENT OF

Mise à jour d’un enregistrement en

conjonction avec l’emploi d’un curseur
Verrouiller en premier les lignes en incluant la
clause FOR UPDATE dans la requête
Référencer la ligne courante à partir d’un
curseur explicite en utilisant la clause WHERE
CURRENT OF
Pas de COMMIT tant que des FETCH ont lieu si
la clause FOR UPDATE est utilisée
Nadhemb@[Link] 78

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 WHERE CURRENT OF

DECLARE
CURSOR DEPT_CURS IS SELECT DNAME FROM DEPT
FOR UPDATE OF DNAME;
DEPT_NAME [Link]%TYPE;
BEGIN
OPEN DEPT_CURS;
LOOP
FETCH DEPT_CURS INTO DEPT_NAME;
EXIT WHEN DEPT_CURS%NOTFOUND;
IF DEPT_CURS%ROWCOUNT = 3 THEN
UPDATE DEPT SET DNAME = 'MIS'
WHERE CURRENT OF DEPT_CURS;
END IF;
END LOOP;
CLOSE DEPT_CURS;
Nadhemb@[Link]; 79

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Boucle FOR pour les Curseurs

Simplifie l’écriture pour gérer les curseurs

explicites
OPEN, FETCH et CLOSE implicites
FOR record_name IN cursor_name LOOP
query_1 ;
query_2 ;
.../...
END LOOP;

Ne pas déclarer l’enregistrement, il le sera

implicitement
Nadhemb@[Link] 80

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Exemple

SQL> DECLARE
revenu NUMBER;
CURSOR emp_curs IS
SELECT ename,
sal,
comm,job
FROM emp WHERE job = 'SALESMAN';
BEGIN
FOR emp_rec IN emp_curs LOOP
revenu := emp_rec.sal + NVL(emp_rec.comm,0);
DBMS_OUTPUT.PUT_LINE(emp_rec.ename||' '||
revenu||' '||emp_rec.job);
END LOOP;
END;
/
ALLEN 1900 SALESMAN …

Nadhemb@[Link] 81

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Gestion des Erreurs

Expliquer les Exceptions les plus courantes.

Décrire les trois principaux types

d’Exceptions.

Ecrire des routines de gestion des

Exceptions.
Nadhemb@[Link] 82

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Généralités

Qu’est ce qu’une Exception ?

Erreur PL/SQL déclenchée pendant l’exécution
du code
Deux modes
Implicitement, une erreur Oracle est apparue.
Explicitement, par l’utilisateur.
Comment la gérer?
Section EXCEPTION
La propager à l’environnement appelant.

Nadhemb@[Link] 83

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Capturer les Exceptions

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

Nadhemb@[Link] 84

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Capturer les Exceptions -
Règles

Le mot-clé EXCEPTION démarre la section de

gestion des exceptions.
Plusieurs EXCEPTION sont autorisées.
Une seule est exécutée avant de quitter le
bloc.
Le gestionnaire d’Exception WHEN OTHERS
capture toutes les erreurs qui ne sont pas
encore gérées.
WHEN OTHERS est la dernière clause.
Nadhemb@[Link] 85

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Exemple

SQL> DECLARE
2 X NUMBER;
3 BEGIN
4 X := 'YYYY';
5 DBMS_OUTPUT.PUT_LINE('IT WORKS');
6 EXCEPTION
7 WHEN VALUE_ERROR THEN
8 DBMS_OUTPUT.PUT_LINE(
9 'VALUE_ERROR EXCEPTION HANDLER');
10 END;
11 /
VALUE_ERROR EXCEPTION HANDLER
PL/SQL procedure successfully completed.

Nadhemb@[Link] 86

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Exceptions Oracle 9i prédéfinies

Ne sont pas définies dans la section DECLARE

Déclenchées automatiquement par Oracle 9i
Référencer le nom standard dans la section
EXCEPTION.

Exemples d’exceptions prédéfinies:

NO_DATA_FOUND
TOO_MANY_ROWS
INVALID_CURSOR
ZERO_DIVIDE
DUP_VAL_ON_INDEX
VALUE_ERROR
CURSOR_ALREADY_EXISTS
Nadhemb@[Link] 87

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Exception sur une erreur non
prédéfinie
SQL> DECLARE
2 BAD_ROWID EXCEPTION;
3 V_ROWID ROWID;
4 PRAGMA EXCEPTION_INIT(BAD_ROWID,-01445);
5 BEGIN
6 SELECT ROWID
7 INTO V_ROWID
8 FROM ALL_VIEWS
9 WHERE ROWNUM = 1;
10 EXCEPTION
11 WHEN BAD_ROWID THEN
12 DBMS_OUTPUT.PUT_LINE('CANNOT QUERY ROWID FROM THIS VIEW');
13 WHEN OTHERS THEN
14 DBMS_OUTPUT.PUT_LINE('SOME OTHER ERROR');
15 END;
16 /
CANNOT QUERY ROWID FROM THIS VIEW
PL/SQL procedure successfully completed.

Nadhemb@[Link] 88

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Exceptions Utilisateur

Définies dans la section DECLARE

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.

Nadhemb@[Link] 89

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Exceptions Utilisateur

SQL> DECLARE
2 DEPTNO_CODE NUMBER(2,0);
3 INVALID_DEPTNO_CODE EXCEPTION;
4 CURSOR c1 IS SELECT deptno FROM dept WHERE ROWNUM = 1;
5 BEGIN
6 OPEN c1;
7 FETCH c1 INTO DEPTNO_CODE;
8 CLOSE c1;
9 IF DEPTNO_CODE NOT IN (11,22,33) THEN
10 RAISE INVALID_DEPTNO_CODE;
11 END IF;
12 DBMS_OUTPUT.PUT_LINE('EVERYTHING IS OK');
13 EXCEPTION
14 WHEN INVALID_DEPTNO_CODE THEN
15 DBMS_OUTPUT.PUT_LINE('INVALID DEPTNO_CODE');
16 END;
17 /
Nadhemb@[Link] INVALID DEPTNO_CODE 90

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Fonctions de Gestion des
Erreurs

SQLCODE
Retourne la valeur numérique
correspondant au code de l’erreur.

SQLERRM
Retourne le message associé au numéro
d’erreur qui est survenu.

Nadhemb@[Link] 91

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Fonctions Propres à PL/SQL

SQL> DECLARE
2 DEPTNO_CODE NUMBER(2,0);
3 INVALID_DEPTNO_CODE EXCEPTION;
4 CURSOR c1 IS SELECT deptno FROM dept WHERE ROWNUM = 1;
5 BEGIN
6 OPEN c1;
7 FETCH c1 INTO DEPTNO_CODE;
8 CLOSE c1;
9 IF DEPTNO_CODE NOT IN (11,22,33) THEN
10 RAISE INVALID_DEPTNO_CODE;
11 END IF;
12 DBMS_OUTPUT.PUT_LINE(‘OK');
13 EXCEPTION
14 WHEN INVALID_DEPTNO_CODE THEN
15 DBMS_OUTPUT.PUT_LINE('SQLCODE = ' || SQLCODE || ' ' ||
16 'SQLERRMESSAGE = ' || SQLERRM);
17 END;
18 /
SQLCODE = 1 SQLERRMESSAGE = User-Defined Exception
Nadhemb@[Link] 92

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 RAISE_APPLICATION_ERROR

SQL> CREATE OR REPLACE PROCEDURE STORED_PROC10 AS

2 BEGIN
3 RAISE_APPLICATION_ERROR(-20001,'Fatal Error.');
4 END;
5 /

SQL> DECLARE
2 INVALID_SP_CALL EXCEPTION;
3 PRAGMA EXCEPTION_INIT(INVALID_SP_CALL,-20001);
4 BEGIN
5 STORED_PROC10;
6 DBMS_OUTPUT.PUT_LINE(‘Aucune erreur’);
7 EXCEPTION
8 WHEN INVALID_SP_CALL THEN
9 DBMS_OUTPUT.PUT_LINE('SQLCODE = ' || SQLCODE || ' ' ||
10 'SQLERRM = ' || SQLERRM);
11 END;
12 /
SQLCODE = -20001 SQLERRM = ORA-20001: Fatal Error.
Nadhemb@[Link] 93

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Les Triggers de base de
données

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Présentation des triggers
Un trigger est un bloc PL/SQL qui
s’exécute implicitement à chaque fois
qu’un événement spécifique se produit.
Il existe deux types de triggers : les
triggers de base de données et les
triggers d’application.

Nadhemb@[Link] 95

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Directives relatives à la
conception de triggers
Vous pouvez concevoir des triggers pour :
Exécuter des actions connexes.
Centraliser des opérations globales .
Leur conception est à proscrire :
Si la fonctionnalité existe déjà.
Si est triggers que vous comptez créer
font double emploi avec d’autres
triggers.
Nadhemb@[Link] 96

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Création du trigger
Temporisation du trigger
Pour la table : BEFORE, AFTER
Pour la vue : INSTEAD OF
Evénement déclencheur : INSERT,
UPDATE ou DELETE
Nom de la table : sur la table ou la vue
Type de trigger :ligne ou instruction
Clause "When" : condition restrictive
Corps du trigger bloc PL/SQL
Nadhemb@[Link] 97

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Composantes du trigger
Temporisation du trigger : quand le trigger doit-
il se déclencher ?
BEFORE :exécution du corps du trigger avant
le déclenchement de l’événement LMD sur la
table.
AFTER : exécution du corps du trigger après
le déclenchement de l’ événement LMD sur la
table.
INSTEAD OF : exécution du corps du trigger
au lieu de l'instruction de déclenchement.
Utilisé pour les VUES qui, autrement, ne
pourraient être modifiées.
Nadhemb@[Link] 98

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Composantes du trigger
Evénement utilisateur déclencheur :
Quelle est l’instruction LMD qui va
provoquer le déclenchement du trigger ?
INSERT
UPDATE
DELETE

Nadhemb@[Link] 99

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Composantes du trigger
Type de trigger :
Combien de fois le corps du trigger doit-il
s’exécuter lorsque survient l’ événement
déclencheur ?
Instruction : par défaut, le corps du trigger
s’exécute une seule fois pour l’événement
déclencheur .
Ligne : le corps du trigger s’exécute une
seule fois pour chaque ligne concernée par
l’événement déclencheur.
Nadhemb@[Link] 100

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Composantes du trigger
Corps du trigger :
Quelle action le trigger doit-il effectuer ?
Le corps du trigger est un bloc PL/SQL
Ou un appel vers une procédure.

Nadhemb@[Link] 101

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Syntaxe relative à la création des
triggers d'instruction
CREATE [OR REPLACE] TRIGGER trigger_name
timing
event1 [OR event2 OR event 3]
ON table_name
trigger_body
SQL> CREATE OR REPLACE TRIGGER secure_emp
2 BEFORE INSERT ON emp
3 BEGIN
4 IF ( TO_CHAR(sysdate,'DY') IN ('SAT','SUN')) OR
5 ( TO_CHAR(sysdate,'HH24') NOT BETWEEN '08' AND '18')
6
7 THEN RAISE_APPLICATION_ERROR (-20500,
8 'Vous ne pouvez insérer des employés que dans les heures de travail');
9 END IF;
10 END;

Nadhemb@[Link] 102

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Test de SECURE_EMP
SQL> INSERT INTO emp (empno, ename, deptno)
2 VALUES (7777,'NADHEM',45);
INSERT INTO emp (empno, ename, deptno)
*
ERREUR à la ligne 1 :
ORA-20500: Vous ne pouvez insérer des employés que
dans les heures de travail
ORA-06512: à "SCOTT.SECURE_EMP", ligne 5
ORA-04088: erreur lors d'exécution du déclencheur
'SCOTT.SECURE_EMP'
Nadhemb@[Link] 103

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Utilisation des prédicats
conditionnels
CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT OR UPDATE OR DELETE ON emp
BEGIN
IF ( TO_CHAR(sysdate,'DY') IN ('SAT','SUN')) OR
( TO_CHAR(sysdate,'HH24') NOT BETWEEN '08' AND '18')

THEN
IF DELETING
THEN RAISE_APPLICATION_ERROR (-20502,
'Vous ne pouvez supprimer des employés que dans les heures de travail');
ELSIF INSERTING
THEN RAISE_APPLICATION_ERROR (-20500,
'Vous ne pouvez insérer des employés que dans les heures de travail');
ELSIF UPDATING ('SAL')
THEN RAISE_APPLICATION_ERROR (-20503,
'Vous ne pouvez modifier les salaires des employés que dans les heures de travail');
ELSE
RAISE_APPLICATION_ERROR (-20504,
'Vous ne pouvez modifier des employés que dans les heures de travail');
END IF;
END IF;
END;

Nadhemb@[Link] 104

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 Création d'un triggers de ligne
CREATE [OR REPLACE] TRIGGER trigger_name
timing
event1 [OR event2 OR event 3]
ON table_name
[REFERENCING OLD AS old/ NEW as new]
FOR EACH ROW
[WHEN condition]
trigger_body

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 (-20502,
9 'Employé ne peut pas toucher ce salaire');
10 END IF;
11 END;

Nadhemb@[Link] 105

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 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_job, new_job, old_salary, new_salary)
8 VALUES (USER, SYSDATE, :[Link],
9 :[Link], :[Link],:[Link], :[Link], :[Link]);
10 END;

Nadhemb@[Link] 106

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 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 TRIGGER table_name DISABLE | ENABLE ALL TRIGGERS

Recompilation d’un trigger pour une table :

ALTER TRIGGER trigger_name COMPILE

Nadhemb@[Link] 107

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer
 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;
Déclencheur supprimé.

Nadhemb@[Link] 108

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer