2ème année GLD

ENSEM

Bases de données avancées

PL/SQL d’oracle

DAOUDI Imane
[email protected]
 Plan
• PL/SQL d’oracle
1. Déclaration des variables PL/SQL
2. Les instructions exécutables
3. Les structures de contrôle
4. Les types de données composite
5. Les curseurs
6. Les procédures et fonctions stockées
7. Les exceptions
8. Les triggers

2
 Le langage PL/SQL d’oracle
 Principales caractéristiques de PL/SQL

 Extension de SQL : des requêtes SQL cohabitent avec les

structures de contrôle habituelles de la programmation
structurée (blocs, alternatives, boucles)

 Un programme est constitué de procédures et de fonctions

 PL/SQL peut être utilisé pour l’écriture des procédures

stockées et des déclencheurs (triggers)

 Il convient aussi pour écrire des fonctions utilisateurs qui

peuvent être utilisées dans les requêtes SQL
3
 Le langage PL/SQL d’oracle
 Structure d’un bloc PL/SQL
DECLARE
(déclaration des variables, des constantes, des exceptions
et des curseurs )
-- Permet de mettre en commentaire ce qui suit sur la ligne
/*… Permet de mettre en commentaire
plusieurs lignes…*/
BEGIN
instruction SQL, PL/SQL, structure de contrôle
EXECPTION
traitement des erreurs
END;

4
 Le langage PL/SQL d’oracle
 Types de blocs PL/SQL
Anonyme Procédure Fonction

[DECLARE] PROCEDURE name FUNCTION name

IS RETURN datatype
IS
BEGIN BEGIN BEGIN
--statements --statements --statements
RETURN value;
[EXCEPTION] [EXCEPTION] [EXCEPTION]

END; END; END;

5
 Le langage PL/SQL d’oracle
 Environnement de travail: isql*plus

6
 Le langage PL/SQL d’oracle
 Environnement de travail: isql*plus
• Cliquez sur le bouton Execute pour exécuter le bloc anonyme :

7
 Le langage PL/SQL d’oracle
 Tester la sortie d’un bloc PL/SQL
Activez la sortie dans iSQL*Plus avec la commande
SET SERVEROUTPUT ON.
Utilisez un package Oracle prédéfini et sa procédure :
DBMS_OUTPUT.PUT_LINE

SET SERVEROUTPUT ON
…
DBMS_OUTPUT.PUT_LINE(' The First Name of the
Employee is ' || f_name);
…

8
 Le langage PL/SQL d’oracle
 Tester la sortie d’un bloc PL/SQL

9
2. Déclaration des variables PL/SQL

10
 2. Déclaration des variables PL/SQL
 Utilisation des variables
• Les variables peuvent être utilisées pour :
– Le stockage temporaire de données
– La manipulation de valeurs stockées
– La réutilisation

SELECT
first_name,
department_id
Jennifer emp_fname
INTO
emp_fname,
emp_deptno
FROM …
10 emp_deptno

11
 2. Déclaration des variables PL/SQL
 identificateurs

• Les identificateurs sont utilisés pour :

– Nommer une variable
– Définir une convention pour les noms des variables :
• Doivent commencer par une lettre
• Peuvent inclure des lettres ou des chiffres
• Peuvent inclure des caractères spéciaux, tels que le signe
dollar, le caractère de soulignement et le signe dièse
• Doivent présenter une longueur maximale de 30 caractères
• Ne doivent pas être des mots réservés

12
 2. Déclaration des variables PL/SQL
 Traiter les variables en langage PL/SQL
• Les variables :
– Sont déclarées et initialisées dans la section déclarative
– Sont utilisées et font l'objet de l'affectation de nouvelles
valeurs dans la section exécutable
– Sont transmises en tant que paramètres aux sous-
programmes PL/SQL
– Sont utilisées pour contenir la sortie d'un sous-
programme PL/SQL

13
 2. Déclaration des variables PL/SQL
 Déclarer et initialiser des variables PL/SQL
Syntaxe :
identifier [CONSTANT] datatype [NOT NULL]
[:= | DEFAULT expr];

Exemples :
DECLARE
emp_hiredate DATE;
emp_deptno NUMBER(2) NOT NULL := 10;
location VARCHAR2(13) := 'Atlanta';
c_comm CONSTANT NUMBER := 1400;

14
 2. Déclaration des variables PL/SQL
 Déclarer et initialiser des variables PL/SQL
DECLARE
Myname VARCHAR2(20);
1 BEGIN
DBMS_OUTPUT.PUT_LINE('My name is: '||Myname);
Myname := 'John';
DBMS_OUTPUT.PUT_LINE('My name is: '||Myname);
END;
/

15
 2. Déclaration des variables PL/SQL
 Déclarer et initialiser des variables PL/SQL
DECLARE
Myname VARCHAR2(20):= 'John';
2 BEGIN
Myname := 'Steven';
DBMS_OUTPUT.PUT_LINE('My name is: '||Myname);
END;
/

16
 2. Déclaration des variables PL/SQL
 Déclarer et initialiser des variables PL/SQL
DECLARE
event VARCHAR2(15);
BEGIN
event := q'!Father's day!';
DBMS_OUTPUT.PUT_LINE('3rd Sunday in June is :
'||event);
event := q'[Mother's day]';
DBMS_OUTPUT.PUT_LINE('2nd Sunday in May is :
'||event);
END;
/

! Et [ : des délimiteurs

17
 2. Déclaration des variables PL/SQL
 Types de variables
– Variables PL/SQL :
• Scalaires
• Composites
• Référence
• LOB
– Variables non PL/SQL : variables attachées

18
 2. Déclaration des variables PL/SQL
 Types de variables
TRUE 25-JAN-01

L'âme du paresseux a des

désirs qu'il ne peut satisfaire;
Mais l'âme des hommes
diligents sera rassasiée.

256120.08 Atlanta

19
 2. Déclaration des variables PL/SQL
 Règles de déclaration et d'initialisation des variables PL/SQL
– Respectez les conventions d'appellation.
– Utilisez des noms évocateurs pour les variables.
– Initialisez les variables désignées comme NOT NULL
et CONSTANT.
– Initialisez les variables avec l'opérateur d'affectation (:=) ou le mot-
clé DEFAULT :
Myname VARCHAR2(20):='John';

Myname VARCHAR2(20) DEFAULT 'John';

– Déclarez un identificateur par ligne pour améliorer la lisibilité et

faciliter la maintenance du code.

20
 2. Déclaration des variables PL/SQL
 Règles de déclaration et d'initialisation des variables PL/SQL
– Evitez d'utiliser des noms de colonne comme identificateurs.
DECLARE
employee_id NUMBER(6);
BEGIN
SELECT employee_id
INTO employee_id
FROM employees
WHERE last_name = 'Kochhar';
END;
/

• Utilisez la contrainte NOT NULL lorsque la variable doit contenir

une valeur.

21
 2. Déclaration des variables PL/SQL
 Types de données scalaires de base .

– CHAR [(maximum_length)]
– VARCHAR2 (maximum_length)
– LONG
– LONG RAW
– NUMBER [(precision, scale)]
– BINARY_INTEGER
– PLS_INTEGER
– BOOLEAN
– BINARY_FLOAT
– BINARY_DOUBLE

22
 2. Déclaration des variables PL/SQL
 Types de données scalaires de base

– DATE
– TIMESTAMP
– TIMESTAMP WITH TIME ZONE
– TIMESTAMP WITH LOCAL TIME ZONE
– INTERVAL YEAR TO MONTH
– INTERVAL DAY TO SECOND

23
 2. Déclaration des variables PL/SQL
 Exemple

DECLARE
emp_job VARCHAR2(9);
count_loop BINARY_INTEGER := 0;
dept_total_sal NUMBER(9,2) := 0;
orderdate DATE := SYSDATE + 7;
c_tax_rate CONSTANT NUMBER(3,2) := 8.25;
valid BOOLEAN NOT NULL := TRUE;
...

24
 2. Déclaration des variables PL/SQL
 Attribut %TYPE
– Est utilisé pour déclarer une variable en fonction :
• D'une définition de colonne de base de données
• D'une autre variable déclarée
– Est préfixé avec :
• La table et la colonne de base de données
• Le nom de la variable déclarée

25
 2. Déclaration des variables PL/SQL
 Attribut %TYPE
• Syntaxe :
identifier table.column_name%TYPE;

• Exemples :
...
emp_lname employees.last_name%TYPE;
balance NUMBER(7,2);
min_balance balance%TYPE := 1000;
...

26
 2. Déclaration des variables PL/SQL
 Variables booléennes

– Seules les valeurs TRUE, FALSE et NULL peuvent être affectées à

une variable booléenne.

– Les expressions conditionnelles utilisent les opérateurs logiques AND

et OR, ainsi que l'opérateur unaire NOT, pour vérifier les valeurs des
variables.

– Les variables renvoient toujours TRUE, FALSE ou NULL.

– Des expressions de type arithmétique, caractère ou date peuvent

être utilisées pour renvoyer une valeur booléenne.

27
 2. Déclaration des variables PL/SQL
 Variables attachées

– Les variables attachées sont :

 Créés dans l'environnement
 Egalement appelées variables hôte
 Créées avec le mot-clé VARIABLE
 Utilisées dans les instructions SQL et les blocs PL/SQL
 Accessibles même après l'exécution du bloc PL/SQL
 Référencées avec un signe deux-points précédant la variable

28
 2. Déclaration des variables PL/SQL
 Variables attachées

– Exemple :

VARIABLE emp_salary NUMBER

BEGIN
SELECT salary INTO :emp_salary
FROM employees WHERE employee_id = 178;
END;
/
PRINT emp_salary
SELECT first_name, last_name FROM employees
WHERE salary=:emp_salary;

29
 2. Déclaration des variables PL/SQL
 Variables attachées

– Exemple :

VARIABLE emp_salary NUMBER

SET AUTOPRINT ON
BEGIN
SELECT salary INTO :emp_salary
FROM employees WHERE employee_id = 178;
END;
/

SET AUTOPRINT ON affiche automatiquement les variables attachées utilisées

dans un bloc PL/SQL correct.

30
 2. Déclaration des variables PL/SQL
 Variables de substitution
– Elles sont utilisées pour autoriser la saisie de l'utilisateur lors de
l'exécution.
– Elles sont référencées dans un bloc PL/SQL avec &.
– Elles sont utilisées afin d'éviter le codage en dur des valeurs pouvant
être obtenues lors de l'exécution.

VARIABLE emp_salary NUMBER

SET AUTOPRINT ON
DECLARE
empno NUMBER(6):=&empno;
BEGIN
SELECT salary INTO :emp_salary
FROM employees WHERE employee_id = empno;
END;
/

31
 2. Déclaration des variables PL/SQL
 Variables de substitution

3 32
 2. Déclaration des variables PL/SQL
 Invite pour les variables de substitution
SET VERIFY OFF
VARIABLE emp_salary NUMBER
ACCEPT empno PROMPT 'Please enter a valid employee
number: '
SET AUTOPRINT ON
DECLARE
empno NUMBER(6):= &empno;
BEGIN
SELECT salary INTO :emp_salary FROM employees
WHERE employee_id = empno;
END;
/

33
 2. Déclaration des variables PL/SQL
 Variables de type LOB
Livre
(CLOB)

Photo
(BLOB)

Film
(BFILE)

NCLOB

34
3. Les instructions exécutables

35
 3. Les instructions exécutables
 Syntaxe et règles relatives aux blocs PL/SQL
– Littéraux :
• Les littéraux de type caractère et date doivent être placés entre
apostrophes.
name := 'Henderson';

• Les nombres peuvent être des valeurs simples ou utiliser une

notation scientifique.

– Les instructions peuvent s'étendre sur plusieurs lignes.

36
 3. Les instructions exécutables
 Commenter le code
– Faites précéder les commentaires monolignes de deux traits
d'union (--).
– Placez les commentaires multilignes entre les symboles "/*"
et "*/".
• Exemple :
DECLARE
...
annual_sal NUMBER (9,2);
BEGIN -- Begin the executable section

/* Compute the annual salary based on the

monthly salary input from the user
*/
annual_sal := monthly_sal * 12;
END; -- This is the end of the block
/
37
 3. Les instructions exécutables
 Fonctions SQL dans le code PL/SQL
– Disponibles dans les instructions procédurales :
• Fonctions monolignes numériques
• Fonctions monolignes de type caractère
• Conversion de type de données
• Date
• Horodatage
• Fonctions GREATEST et LEAST
• Fonctions diverses
– Non disponibles dans les instructions procédurales :
• DECODE
• Fonctions de groupe

38
 3. Les instructions exécutables
 Fonctions SQL dans le code PL/SQL: Exemple
– Déterminer la longueur d'une chaîne :
desc_size INTEGER(5);
prod_description VARCHAR2(70):='You can use this
product with your radios for higher frequency';

-- get the length of the string in prod_description

desc_size:= LENGTH(prod_description);

– Convertir le nom d'un employé en minuscules :

emp_name:= LOWER(emp_name);

39
 3. Les instructions exécutables
 Fonctions SQL dans le code PL/SQL
– Conversion de données en types de données
comparables
– Deux types de conversion :
• Conversion implicite
• Conversion explicite
– Exemples de fonctions de conversion :
• TO_CHAR
• TO_DATE
• TO_NUMBER
• TO_TIMESTAMP

40
 3. Les instructions exécutables
 Fonctions SQL dans le code PL/SQL

Conversion implicite

1 date_of_joining DATE:= '02-Feb-2000';

Erreur

2 date_of_joining DATE:= 'February 02,2000';

Conversion explicite

date_of_joining DATE:= TO_DATE('February

3 02,2000','Month DD, YYYY');

41
 3. Les instructions exécutables
 Blocs imbriqués
Les blocs PL/SQL peuvent être
imbriqués.

• Une section exécutable (BEGIN …

END) peut contenir des blocs
imbriqués.

• Une section de traitement des

exceptions peut contenir des
blocs imbriqués.

42
 3. Les instructions exécutables
 Blocs imbriqués: exemple
DECLARE
outer_variable VARCHAR2(20):='GLOBAL VARIABLE';
BEGIN
DECLARE
inner_variable VARCHAR2(20):='LOCAL VARIABLE';
BEGIN
DBMS_OUTPUT.PUT_LINE(inner_variable);
DBMS_OUTPUT.PUT_LINE(outer_variable);
END;
DBMS_OUTPUT.PUT_LINE(outer_variable);
END;
/

43
 3. Les instructions exécutables
 Portée et visibilité des variables
DECLARE
father_name VARCHAR2(20):='Patrick';
date_of_birth DATE:='20-Apr-1972';
BEGIN
DECLARE
child_name VARCHAR2(20):='Mike';
date_of_birth DATE:='12-Dec-2002';
BEGIN
DBMS_OUTPUT.PUT_LINE('Father''s Name: '||father_name);
1 DBMS_OUTPUT.PUT_LINE('Date of Birth: '||date_of_birth);
DBMS_OUTPUT.PUT_LINE('Child''s Name: '||child_name);
END;
2 DBMS_OUTPUT.PUT_LINE('Date of Birth: '||date_of_birth);
END;
/

44
 3. Les instructions exécutables
 Qualifier un identificateur
<<outer>>
DECLARE
father_name VARCHAR2(20):='Patrick';
date_of_birth DATE:='20-Apr-1972';
BEGIN
DECLARE
child_name VARCHAR2(20):='Mike';
date_of_birth DATE:='12-Dec-2002';
BEGIN
DBMS_OUTPUT.PUT_LINE('Father''s Name: '||father_name);
DBMS_OUTPUT.PUT_LINE('Date of Birth: '
||outer.date_of_birth);
DBMS_OUTPUT.PUT_LINE('Child''s Name: '||child_name);
DBMS_OUTPUT.PUT_LINE('Date of Birth: '||date_of_birth);
END;
END;
/`

45
 3. Les instructions exécutables
 La portée d’une variable
<<outer>>
DECLARE
sal NUMBER(7,2) := 60000;
comm NUMBER(7,2) := sal * 0.20;
message VARCHAR2(255) := ' eligible for commission';
BEGIN
DECLARE
sal NUMBER(7,2) := 50000;
comm NUMBER(7,2) := 0;
total_comp NUMBER(7,2) := sal + comm;
BEGIN
message := 'CLERK not'||message;
1 outer.comm := sal * 0.30;
END;
message := 'SALESMAN'||message;
2
END;
/

46
 3. Les instructions exécutables
 Opérateurs en PL/SQL

– Opérateur logique
– Opérateur arithmétique Identiques en
– Opérateur de concaténation langage SQL
– Parenthèses permettant de
contrôler l'ordre des
opérations

– Opérateur exponentiel (**)

47
 3. Les instructions exécutables
 Exemple

– Incrémenter le compteur pour une boucle :

loop_count := loop_count + 1;

– Définir la valeur d'un indicateur booléen :

good_sal := sal BETWEEN 50000 AND 150000;

– Vérifier qu'un numéro d'employé contient une

valeur :
valid := (empno IS NOT NULL);

48
4. Les structures de contrôle

49
3. Les structures de contrôle

for
loop

while

50
 3. Les structures de contrôle
 Instruction IF
Syntaxe :

IF condition THEN
statements;
[ELSIF condition THEN
statements;]
[ELSE
statements;]
END IF;

51
 3. Les structures de contrôle
 Instructions IF simples
Exemple :

DECLARE
myage number:=31;
BEGIN
IF myage < 11
THEN
DBMS_OUTPUT.PUT_LINE(' I am a child ');
END IF;
END;
/

52
 3. Les structures de contrôle
 Instruction IF THEN ELSE
Exemple :
SET SERVEROUTPUT ON
DECLARE
myage number:=31;
BEGIN
IF myage < 11
THEN
DBMS_OUTPUT.PUT_LINE(' I am a child ');
ELSE
DBMS_OUTPUT.PUT_LINE(' I am not a child ');
END IF;
END;
/

53
 3. Les structures de contrôle
 Clause IF ELSIF ELSE
DECLARE
Exemple :
myage number:=31;
BEGIN
IF myage < 11
THEN
DBMS_OUTPUT.PUT_LINE(' I am a child ');
ELSIF myage < 20
THEN
DBMS_OUTPUT.PUT_LINE(' I am young ');
ELSIF myage < 30
THEN
DBMS_OUTPUT.PUT_LINE(' I am in my twenties');
ELSIF myage < 40
THEN
DBMS_OUTPUT.PUT_LINE(' I am in my thirties');
ELSE
DBMS_OUTPUT.PUT_LINE(' I am always young ');
END IF;
END;
/ 54
 3. Les structures de contrôle
 Valeurs NULL dans les instructions IF
Exemple
DECLARE:
myage number;
BEGIN
IF myage < 11
THEN
DBMS_OUTPUT.PUT_LINE(' I am a child ');
ELSE
DBMS_OUTPUT.PUT_LINE(' I am not a child ');
END IF;
END;
/

55
 3. Les structures de contrôle
 Expressions CASE
– Une expression CASE sélectionne un résultat et le renvoie.
– Pour sélectionner le résultat, l'expression CASE utilise des
expressions. La valeur renvoyée par ces expressions est
utilisée pour sélectionner une ou plusieurs alternatives.

CASE selector
WHEN expression1 THEN result1
WHEN expression2 THEN result2
...
WHEN expressionN THEN resultN
[ELSE resultN+1]
END;
/

56
 3. Les structures de contrôle
 Expressions CASE: exemple
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
grade CHAR(1) := UPPER('&grade');
appraisal VARCHAR2(20);
BEGIN
appraisal :=
CASE grade
WHEN 'A' THEN 'Excellent'
WHEN 'B' THEN 'Very Good'
WHEN 'C' THEN 'Good'
ELSE 'No such grade'
END;
DBMS_OUTPUT.PUT_LINE ('Grade: '|| grade || '
Appraisal ' || appraisal);
END;
/
57
 3. Les structures de contrôle
 instruction CASE : exemple
DECLARE
deptid NUMBER;
deptname VARCHAR2(20);
emps NUMBER;
mngid NUMBER:= 108;
BEGIN
CASE mngid
WHEN 108 THEN
SELECT department_id, department_name
INTO deptid, deptname FROM departments
WHERE manager_id=108;
SELECT count(*) INTO emps FROM employees
WHERE department_id=deptid;
WHEN 200 THEN
...
END CASE;
DBMS_OUTPUT.PUT_LINE ('You are working in the '|| deptname||
' department. There are '||emps ||' employees in this
department');
END;
/

58
 3. Les structures de contrôle
 Contrôle d'itération : instructions LOOP

– Les boucles permettent d'exécuter plusieurs fois

une instruction ou une séquence d'instructions.
– Il existe trois types de boucle :
• Boucle de base
• Boucle FOR
• Boucle WHILE

59
 3. Les structures de contrôle
 Boucles de base
• Syntaxe :
LOOP
statement1;
. . .
EXIT [WHEN condition];
END LOOP;

60
 3. Les structures de contrôle
 Boucles de base
• Exemple :

DECLARE
countryid locations.country_id%TYPE := 'CA';
loc_id locations.location_id%TYPE;
counter NUMBER(2) := 1;
new_city locations.city%TYPE := 'Montreal';
BEGIN
SELECT MAX(location_id) INTO loc_id FROM locations
WHERE country_id = countryid;
LOOP
INSERT INTO locations(location_id, city, country_id)
VALUES((loc_id + counter), new_city, countryid);
counter := counter + 1;
EXIT WHEN counter > 3;
END LOOP;
END;
/

61
 3. Les structures de contrôle
 Boucle while

• Syntaxe :
WHILE condition LOOP
statement1;
statement2;
. . .
END LOOP;

• Utilisez la boucle WHILE pour répéter des instructions tant

qu'une condition renvoie TRUE.

62
 3. Les structures de contrôle
 Boucle while
• Exemple :

DECLARE
countryid locations.country_id%TYPE := 'CA';
loc_id locations.location_id%TYPE;
new_city locations.city%TYPE := 'Montreal';
counter NUMBER := 1;
BEGIN
SELECT MAX(location_id) INTO loc_id FROM locations
WHERE country_id = countryid;
WHILE counter <= 3 LOOP
INSERT INTO locations(location_id, city, country_id)
VALUES((loc_id + counter), new_city, countryid);
counter := counter + 1;
END LOOP;
END;
/

63
 3. Les structures de contrôle
 Boucle For

– Utilisez une boucle FOR pour simplifier le contrôle du nombre

d'itérations.
– Ne déclarez pas le compteur (sa déclaration est implicite).
– La syntaxe 'lower_bound .. upper_bound' est
obligatoire.

FOR counter IN [REVERSE]

lower_bound..upper_bound LOOP
statement1;
statement2;
. . .
END LOOP;

64
 3. Les structures de contrôle
 Boucle For
• Exemple :

DECLARE
countryid locations.country_id%TYPE := 'CA';
loc_id locations.location_id%TYPE;
new_city locations.city%TYPE := 'Montreal';
BEGIN
SELECT MAX(location_id) INTO loc_id
FROM locations
WHERE country_id = countryid;
FOR i IN 1..3 LOOP
INSERT INTO locations(location_id, city, country_id)
VALUES((loc_id + i), new_city, countryid );
END LOOP;
END;
/

65
 3. Les structures de contrôle
 Boucles imbriquées et étiquettes

– Imbriquez des boucles à plusieurs niveaux.

– Utilisez des étiquettes pour différencier les blocs des boucles.

– Quittez la boucle externe en utilisant l'instruction EXIT qui

référence l'étiquette.

66
 3. Les structures de contrôle
 Boucles imbriquées et étiquettes
...
BEGIN
<<Outer_loop>>
LOOP
counter := counter+1;
EXIT WHEN counter>10;
<<Inner_loop>>
LOOP
...
EXIT Outer_loop WHEN total_done = 'YES';
-- Leave both loops
EXIT Inner_loop WHEN inner_done = 'YES';
-- Leave inner loop only
...
END LOOP Inner_loop;
...
END LOOP Outer_loop;
END;
/

67
TP 1

68
4. Les types de données composites

69
 4. Les types de données composites

– Ils peuvent contenir plusieurs valeurs,

contrairement aux types scalaires.
– Ils sont de deux types :
• Enregistrements PL/SQL
• Ensembles PL/SQL
» Tables INDEX BY ou tableaux associatifs
» Table imbriquée
» VARRAY

70
 4. Les types de données composites

– Utilisez des enregistrements PL/SQL lorsque vous

souhaitez stocker des valeurs de différents types de
données, mais une seule occurrence à la fois.

– Utilisez des ensembles PL/SQL lorsque vous souhaitez

stocker des valeurs de même type de données.

71
 4. Les types de données composites
 Créer un enregistrement PL/SQL
• Syntaxe :

1 TYPE type_name IS RECORD

(field_declaration, [field_declaration]…);

2 identifier type_name;

field_declaration :
field_name {field_type | variable%TYPE
| table.column%TYPE | table%ROWTYPE}
[[NOT NULL] {:= | DEFAULT} expr]

72
 4. Les types de données composites
 Structure d’un enregistrement PL/SQL:

Champ1 Champ2 Champ3

(type de données) (type de données) (type de données)

Exemple :
Champ1 Champ2 Champ3
(type de données) (type de données) (type de données)
employee_id number(6) last_name varchar2(25) job_id varchar2(10)

100 King AD_PRES

73
 4. Les types de données composites
 Attribut %ROWTYPE :
– Déclarez une variable correspondant à l’ensemble des colonnes
d'une table ou d'une vue d’une base de données.
– Faites précéder %ROWTYPE du nom de la table ou de la vue de
base de données.
– Les noms et types de données des champs de l'enregistrement
sont issus des colonnes de la table ou de la vue.

Syntaxe :
DECLARE
identifier reference%ROWTYPE;

74
 4. Les types de données composites
 L'attribut %ROWTYPE :
...
DEFINE employee_number = 124
DECLARE
emp_rec employees%ROWTYPE;
BEGIN
SELECT * INTO emp_rec FROM employees
WHERE employee_id = &employee_number;
INSERT INTO retired_emps(empno, ename, job, mgr,
hiredate, leavedate, sal, comm, deptno)
VALUES (emp_rec.employee_id, emp_rec.last_name,
emp_rec.job_id,emp_rec.manager_id,
emp_rec.hire_date, SYSDATE, emp_rec.salary,
emp_rec.commission_pct, emp_rec.department_id);
END;
/

75
 4. Les types de données composites
 Tables INDEX BY ou tableaux associatifs :

– Structures PL/SQL avec deux colonnes :

• Entier ou chaîne de type clé primaire
• Colonne de type scalaire ou RECORD
– Pas de contrainte de taille. La taille dépend des valeurs
pouvant être contenues dans le type de données.

76
 4. Les types de données composites
 Créer une table INDEX BY :
• Syntaxe :

TYPE type_name IS TABLE OF

{column_type | variable%TYPE
| table.column%TYPE} [NOT NULL]
| table%ROWTYPE
[INDEX BY PLS_INTEGER | BINARY_INTEGER
| VARCHAR2(<size>)];
identifier type_name;

Déclarez une table INDEX BY pour le stockage du nom des employés.

...
TYPE ename_table_type IS TABLE OF
employees.last_name%TYPE
INDEX BY PLS_INTEGER;
...
ename_table ename_table_type;

77
 4. Les types de données composites
 Structure des tables INDEX BY :

Clé unique Valeur

... ...

1 Jones
5 Smith
3 Maduro

... ...

PLS_INTEGER Scalaire

78
 4. Les types de données composites
 Créer une table INDEX BY :

DECLARE
TYPE ename_table_type IS TABLE OF
employees.last_name%TYPE
INDEX BY PLS_INTEGER;
TYPE hiredate_table_type IS TABLE OF DATE
INDEX BY PLS_INTEGER;
ename_table ename_table_type;
hiredate_table hiredate_table_type;
BEGIN
ename_table(1) := 'CAMERON';
hiredate_table(7) := SYSDATE + 7;
IF ename_table.EXISTS(1) THEN
INSERT INTO ...
...
END;
/

79
 4. Les types de données composites
 Les méthodes suivantes facilitent l'utilisation des tables INDEX BY :

– EXISTS – NEXT
– COUNT – TRIM
– FIRST et LAST – DELETE

– PRIOR

80
 4. Les types de données composites
 Table d'enregistrements INDEX BY :

• Définissez une variable de table INDEX BY contenant

une ligne entière d'une table.
• Exemple :
DECLARE
TYPE dept_table_type IS TABLE OF
departments%ROWTYPE
INDEX BY PLS_INTEGER;
dept_table dept_table_type;
-- Each element of dept_table is a record

81
 4. Les types de données composites
 Exemple de table d'enregistrements INDEX BY :
SET SERVEROUTPUT ON
DECLARE
TYPE emp_table_type IS TABLE OF
employees%ROWTYPE INDEX BY PLS_INTEGER;
my_emp_table emp_table_type;
max_count NUMBER(3):= 104;
BEGIN
FOR i IN 100..max_count
LOOP
SELECT * INTO my_emp_table(i) FROM employees
WHERE employee_id = i;
END LOOP;
FOR i IN my_emp_table.FIRST..my_emp_table.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(my_emp_table(i).last_name);
END LOOP;
END;
/

82
 4. Les types de données composites
 VARRAY :
Les tableaux de taille variable (VARRAY) sont
semblables aux tables PL/SQL, à ceci près que leur
taille fait l'objet d'une contrainte.
1 Bombay

Exemple : 2 Sydney
TYPE location_type IS 3 Oxford
VARRAY(3) OF
4 London
locations.city%TYPE;
offices location_type; .. ....
La taille du VARRAY est limitée à 3. 10 Tokyo

83
5. Les curseurs

84
 5. Les curseurs
 Opérations de curseur explicite :
Les curseurs explicites en langage PL/SQL sont déclarés lorsqu'une instruction
SELECT renvoie plusieurs lignes. Chaque ligne renvoyée par l'instruction
SELECT peut être traité séparément.
L'ensemble des lignes d'une interrogation renvoyant plusieurs lignes se
nomme l'ensemble actif. Sa taille est égale au nombre de lignes satisfaisant
les critères de recherche.
Table

100 King AD_PRES

Ensemble actif 101 Kochhar AD_VP
102 De Haan AD_VP
. . .
. . .
. . .
139 Seo ST_CLERK
140 Patel ST_CLERK
. . .
85
 5. Les curseurs
 Contrôler les curseurs explicites :

Non

Oui
DECLARE OPEN FETCH VIDE? CLOSE

• Créer une • Identifier • Charger • Tester • Libérer

zone SQL l'ensemble la ligne l'existence l'ensemble
nommée actif en cours de lignes actif
dans des
variables • Si des lignes
existent,
revenir à
FETCH

86
 5. Les curseurs
 Contrôler les curseurs explicites :

1 Ouverture du curseur
Pointeur de
curseur

2 Extraction (fetch)
d'une ligne
Pointeur de
curseur

Pointeur de
3 Fermeture du curseur
curseur

87
 5. Les curseurs
 Déclarer le curseur :
• Syntaxe :
CURSOR cursor_name IS
select_statement;

• Exemples :
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, last_name FROM employees
WHERE department_id =30;
DECLARE
locid NUMBER:= 1700;
CURSOR dept_cursor IS
SELECT * FROM departments
WHERE location_id = locid;
...

88
 5. Les curseurs
 Ouvrir le curseur :

DECLARE
CURSOR emp_cursor IS
SELECT employee_id, last_name FROM employees
WHERE department_id =30;
...
BEGIN
OPEN emp_cursor;

89
 5. Les curseurs
 Extraire des données du curseur:

SET SERVEROUTPUT ON
DECLARE
DECLARE
CURSOR
CURSOR emp_cursor
emp_cursor IS
IS
SELECT
SELECT employee_id,
employee_id, last_name
last_name FROM
FROM employees
employees
WHERE
WHEREdepartment_id
department_id=30;
=30;
...
empno employees.employee_id%TYPE;
BEGIN
lname employees.last_name%TYPE;
OPEN emp_cursor;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO empno, lname;
DBMS_OUTPUT.PUT_LINE( empno ||' '||lname);
...
END;
/

90
 5. Les curseurs
 Extraire des données du curseur:
SET SERVEROUTPUT ON
DECLARE
CURSOR emp_cursor IS
DECLARE
SELECTemp_cursor
CURSOR employee_id,
IS last_name FROM employees
WHERE employee_id,
SELECT department_idlast_name
=30; FROM employees
empno department_id
WHERE employees.employee_id%TYPE;
=30;
lname employees.last_name%TYPE;
...
BEGIN
BEGIN
OPEN emp_cursor;
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO empno, lname;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( empno ||' '||lname);
END LOOP;
...
END;
/
91
 5. Les curseurs
 Fermeture du curseur:

...
LOOP
FETCH emp_cursor INTO empno, lname;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( empno ||' '||lname);
END LOOP;
CLOSE emp_cursor;
END;
/

92
 5. Les curseurs
 Curseurs et enregistrements :
• Traitez les lignes de l'ensemble actif en extrayant (fetch) les
valeurs pour les placer dans un enregistrement PL/SQL.

DECLARE
CURSOR emp_cursor IS
SELECT employee_id, last_name FROM employees
WHERE department_id =30;
emp_record emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_record;
...

93
 5. Les curseurs
 Boucles FOR de curseur :
• Syntaxe :
FOR record_name IN cursor_name LOOP
statement1;
statement2;
. . .
END LOOP;

– La boucle FOR de curseur simplifie le traitement des curseurs

explicites.
– Des opérations d'ouverture, d'extraction (fetch), de sortie et
de fermeture ont lieu de manière implicite.
– L'enregistrement est déclaré implicitement.

94
 5. Les curseurs
 Boucles FOR de curseur :

SET SERVEROUTPUT ON
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, last_name FROM employees
WHERE department_id =30;
BEGIN
FOR emp_record IN emp_cursor
LOOP
DBMS_OUTPUT.PUT_LINE( emp_record.employee_id
||' ' ||emp_record.last_name);
END LOOP;
END;
/

95
 5. Les curseurs
 Attributs d'un curseur explicite :
• Obtenir les informations d'état concernant un curseur

Attribut Type Description

%ISOPEN Booléen Prend la valeur TRUE si le curseur est

ouvert
%NOTFOUND Booléen Prend la valeur TRUE si la dernière
extraction (fetch) ne renvoie pas de
ligne
%FOUND Booléen Prend la valeur TRUE si la dernière
extraction renvoie une ligne ;
complément de %NOTFOUND
%ROWCOUNT Nombre Prend la valeur correspondant au
nombre total de lignes renvoyées
jusqu'à présent

96
 5. Les curseurs
 Attribut %ISOPEN :
– Extrayez (fetch) les lignes uniquement lorsque le curseur est
ouvert.
– Utilisez l'attribut de curseur %ISOPEN avant de réaliser une
extraction pour déterminer si le curseur est ouvert.
• Exemple :

IF NOT emp_cursor%ISOPEN THEN

OPEN emp_cursor;
END IF;
LOOP
FETCH emp_cursor...

97
 5. Les curseurs
 Exemple d'utilisation des attributs %ROWCOUNT et %NOTFOUND :
SET SERVEROUTPUT ON
DECLARE
empno employees.employee_id%TYPE;
ename employees.last_name%TYPE;
CURSOR emp_cursor IS SELECT employee_id,
last_name FROM employees;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO empno, ename;
EXIT WHEN emp_cursor%ROWCOUNT > 10 OR
emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(empno)
||' '|| ename);
END LOOP;
CLOSE emp_cursor;
END ;
/

98
 5. Les curseurs
 Boucles FOR de curseur utilisant des sous-interrogations :

• Il n'est pas nécessaire de déclarer le curseur.

• Exemple :
SET SERVEROUTPUT ON
BEGIN
FOR emp_record IN (SELECT employee_id, last_name
FROM employees WHERE department_id =30)
LOOP
DBMS_OUTPUT.PUT_LINE( emp_record.employee_id ||'
'||emp_record.last_name);
END LOOP;
END;
/

99
 5. Les curseurs
 Curseurs avec paramètres :

• Syntaxe :
CURSOR cursor_name
[(parameter_name datatype, ...)]
IS
select_statement;

– Transmettez des paramètres au curseur au moment de son

ouverture et de l'exécution
de l'interrogation.
– Ouvrez un curseur explicite à plusieurs reprises, en renvoyant
un ensemble actif différent à chaque fois.

OPEN cursor_name(parameter_value,.....) ;

100
 5. Les curseurs
 Curseurs avec paramètres :
• Exemple :
SET SERVEROUTPUT ON
DECLARE
CURSOR emp_cursor (deptno NUMBER) IS
SELECT employee_id, last_name
FROM employees
WHERE department_id = deptno;
dept_id NUMBER;
lname VARCHAR2(15);
BEGIN
OPEN emp_cursor (10);
...
CLOSE emp_cursor;
OPEN emp_cursor (20);
...

101
TP2

102
6. Les exceptions

103
 6. Les exceptions

 Exemple :

SET SERVEROUTPUT ON
DECLARE
lname VARCHAR2(15);
BEGIN
SELECT last_name INTO lname FROM employees WHERE
first_name='John';
DBMS_OUTPUT.PUT_LINE ('John''s last name is : '
||lname);
END;
/

104
 6. Les exceptions

 Exemple :

SET SERVEROUTPUT ON
DECLARE
lname VARCHAR2(15);
BEGIN
SELECT last_name INTO lname FROM employees WHERE
first_name='John';
DBMS_OUTPUT.PUT_LINE ('John''s last name is : '
||lname);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE (' Your select statement
retrieved multiple rows. Consider using a
cursor.');
END;
/
105
 6. Les exceptions

 Traiter les exceptions en langage PL/SQL :

– Une exception est une erreur PL/SQL détectée pendant l'exécution du

programme.

– Une exception peut être générée :

• Implicitement par le serveur Oracle
• Explicitement par le programme

– Une exception peut être traitée :

• Par interception à l'aide d'un gestionnaire
• Par propagation vers l'environnement appelant

106
 6. Les exceptions

 Types d’exceptions :

– Exception prédéfinie
du serveur Oracle Exception déclenchée
– Exception non prédéfinie implicitement
du serveur Oracle

– Exception définie Exception déclenchée

par l'utilisateur explicitement

107
 6. Les exceptions

 Intercepter les exceptions :

• Syntaxe :

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

108
 6. Les exceptions

 Règles d'interception des exceptions :

– Le mot-clé EXCEPTION débute la section de traitement des

exceptions.

– Plusieurs gestionnaires d'exceptions sont autorisés.

– Un seul gestionnaire est traité avant la sortie du bloc.

– WHEN OTHERS est la dernière clause.

109
 6. Les exceptions

 Intercepter les erreurs prédéfinies du serveur Oracle :

– Utilisez le nom prédéfini à l'intérieur du sous-

programme de traitement des exceptions.
– Exemples d'exceptions prédéfinies :
• NO_DATA_FOUND
• TOO_MANY_ROWS
• INVALID_CURSOR
• ZERO_DIVIDE
• DUP_VAL_ON_INDEX

110
 6. Les exceptions

 Intercepter les erreurs prédéfinies du serveur Oracle :

Déclarer Associer Référencer

Section déclarative Section EXCEPTION

Nommer Coder PRAGMA Traiter l'exception

l'exception EXCEPTION_INIT déclenchée

111
 6. Les exceptions
 Erreur non prédéfinie :
• Intercepter l'erreur numéro –01400 du serveur Oracle,
impossible d'insérer la valeur NULL
SET SERVEROUTPUT ON
DECLARE
insert_excep EXCEPTION; 1
PRAGMA EXCEPTION_INIT
(insert_excep, -01400); 2
BEGIN
INSERT INTO departments
(department_id, department_name) VALUES (280, NULL);
EXCEPTION 3
WHEN insert_excep THEN
DBMS_OUTPUT.PUT_LINE('INSERT OPERATION FAILED');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

112
 6. Les exceptions
 Fonctions d'interception des exceptions :
– SQLCODE : renvoie la valeur numérique du code d'erreur
– SQLERRM : renvoie le message associé au code d'erreur

 Exemple
DECLARE
error_code NUMBER;
error_message VARCHAR2(255);
BEGIN
...
EXCEPTION
...
WHEN OTHERS THEN
ROLLBACK;
error_code := SQLCODE ;
error_message := SQLERRM ;
INSERT INTO errors (e_user, e_date, error_code,
error_message) VALUES(USER,SYSDATE,error_code,
error_message);
END;
/
113
 6. Les exceptions
 Intercepter les exceptions définies par l'utilisateur :

Déclarer Déclencher Référencer

Section Section Section de traitement

déclarative exécutable des exceptions

Nommer Déclencher Traiter l'exception

l'exception explicitement déclenchée
l'exception via
l'instruction
RAISE

114
 6. Les exceptions
 Intercepter les exceptions définies par l'utilisateur :
...
ACCEPT deptno PROMPT 'Please enter the department number:'
ACCEPT name PROMPT 'Please enter the department name:'
DECLARE
invalid_department EXCEPTION;
name VARCHAR2(20):='&name';
1
deptno NUMBER :=&deptno;
BEGIN
UPDATE departments
SET department_name = name
WHERE department_id = deptno;
IF SQL%NOTFOUND THEN
RAISE invalid_department;
END IF;
2
COMMIT;
EXCEPTION 3
WHEN invalid_department THEN
DBMS_OUTPUT.PUT_LINE('No such department id.');
END;
/

115
 6. Les exceptions
 Procédure RAISE_APPLICATION_ERROR :

• Syntaxe :
raise_application_error (error_number,
message[, {TRUE | FALSE}]);

– Vous pouvez utiliser cette procédure pour générer des

messages d'erreur définis par l'utilisateur.
– Elle permet de signaler les erreurs à l'application et d'éviter le
renvoi d'exceptions non traitées.

116
 6. Les exceptions
 Procédure RAISE_APPLICATION_ERROR :

– Elle peut être utilisée à deux endroits :

• Section exécutable

• Section de traitement des exceptions

– Elle renvoie à l'utilisateur les conditions de l'erreur de

manière cohérente par rapport aux autres erreurs du
serveur Oracle.

117
 6. Les exceptions
 Procédure RAISE_APPLICATION_ERROR :

Section exécutable :
BEGIN
...
DELETE FROM employees
WHERE manager_id = v_mgr;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20202,
'This is not a valid manager');
END IF;
...

• Section de traitement des exceptions :

...
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-20201,
'Manager is not a valid employee.');
END;
/

118
7. Les procédures et fonctions stockées

119
 7. Les procédures et fonctions stockées
 Procédures et fonctions :

– Blocs PL/SQL nommés

– Appelés sous-programmes PL/SQL

– Présentent des structures de bloc semblables à celle des blocs

anonymes :
• Section déclarative facultative (sans le mot-clé DECLARE)

• Section exécutable obligatoire

• Section facultative de traitement

des exceptions

120
 7. Les procédures et fonctions stockées
 Différences entre les blocs anonymes et les sous-programmes
(fonctions/procédures:

Blocs anonymes Sous-programmes

Blocs PL/SQL non nommés Blocs PL/SQL nommés

Compilés chaque fois Compilés une seule fois

Non stockés dans la base de Stockés dans la base de données

données
Pas d'appel possible par Appel possible par d'autres applications,
d'autres applications car nommés
Ne renvoient pas de valeurs Les sous-programmes appelés fonctions
doivent renvoyer des valeurs
Ne peuvent pas accepter de Peuvent accepter des paramètres
paramètres

121
 7. Les procédures et fonctions stockées
 Procédure : syntaxe

CREATE [OR REPLACE] PROCEDURE procedure_name

[(argument1 [mode1] datatype1,
argument2 [mode2] datatype2,
. . .)]
IS|AS
procedure_body;

122
 7. Les procédures et fonctions stockées
 Procédure : exemple

...
CREATE TABLE dept AS SELECT * FROM departments;
CREATE PROCEDURE add_dept IS
dept_id dept.department_id%TYPE;
dept_name dept.department_name%TYPE;
BEGIN
dept_id:=280;
dept_name:='ST-Curriculum';
INSERT INTO dept(department_id,department_name)
VALUES(dept_id,dept_name);
DBMS_OUTPUT.PUT_LINE(' Inserted '||
SQL%ROWCOUNT ||' row ');
END;
/

123
 7. Les procédures et fonctions stockées
 Appeler la procédure

BEGIN
add_dept;
END;
/
SELECT department_id, department_name FROM dept
WHERE department_id=280;

124
 7. Les procédures et fonctions stockées
 Fonction : syntaxe

CREATE [OR REPLACE] FUNCTION function_name

[(argument1 [mode1] datatype1,
argument2 [mode2] datatype2,
. . .)]
RETURN datatype
IS|AS
function_body;

125
 7. Les procédures et fonctions stockées
 Fonction : exemple
CREATE FUNCTION check_sal RETURN Boolean IS
dept_id employees.department_id%TYPE;
empno employees.employee_id%TYPE;
sal employees.salary%TYPE;
avg_sal employees.salary%TYPE;
BEGIN
empno:=205;
SELECT salary,department_id INTO sal,dept_id
FROM employees WHERE employee_id= empno;
SELECT avg(salary) INTO avg_sal FROM employees
WHERE department_id=dept_id;
IF sal > avg_sal THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
/
126
 7. Les procédures et fonctions stockées
 Appeler une fonction

SET SERVEROUTPUT ON
BEGIN
IF (check_sal IS NULL) THEN
DBMS_OUTPUT.PUT_LINE('The function returned
NULL due to exception');
ELSIF (check_sal) THEN
DBMS_OUTPUT.PUT_LINE('Salary > average');
ELSE
DBMS_OUTPUT.PUT_LINE('Salary < average');
END IF;
END;
/

127
 7. Les procédures et fonctions stockées
 Transmettre un paramètre à une fonction
DROP FUNCTION check_sal;
/
CREATE FUNCTION check_sal(empno employees.employee_id%TYPE)
RETURN Boolean IS
dept_id employees.department_id%TYPE;
sal employees.salary%TYPE;
avg_sal employees.salary%TYPE;
BEGIN
SELECT salary,department_id INTO sal,dept_id
FROM employees WHERE employee_id=empno;
SELECT avg(salary) INTO avg_sal FROM employees
WHERE department_id=dept_id;
IF sal > avg_sal THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
EXCEPTION ...
...
128
 7. Les procédures et fonctions stockées
 Appeler une fonction avec un paramètre

BEGIN
DBMS_OUTPUT.PUT_LINE('Checking for employee with id 205');
IF (check_sal(205) IS NULL) THEN
DBMS_OUTPUT.PUT_LINE('The function returned
NULL due to exception');
ELSIF (check_sal(205)) THEN
DBMS_OUTPUT.PUT_LINE('Salary > average');
ELSE
DBMS_OUTPUT.PUT_LINE('Salary < average');
END IF;
DBMS_OUTPUT.PUT_LINE('Checking for employee with id 70');
IF (check_sal(70) IS NULL) THEN
DBMS_OUTPUT.PUT_LINE('The function returned
NULL due to exception');
ELSIF (check_sal(70)) THEN
...
END IF;
END;
/

129
TP3

130
8. Les Triggers

131
 8. Les Triggers
 Objectifs
 Décrire un déclencheur
 Connaître les caractéristiques d’un déclencheur

132
 8. Les Triggers
 C’est quoi un déclencheur?
• Définit une action qui doit se déclencher lorsqu’un évènement survient sur la
base de données
• Objet stocké en base
• Associé à une table ou à une vue
• Pas de COMMIT/ROLLBACK dans un déclencheur

133
 8. Les Triggers
 Avantage du déclencheur
• Ajouter des contraintes sur les valeurs des colonnes d’une table
• Réaliser un audit des changements sur les données (logs)
• Ajout des règles de gestion
• Publier des informations concernant divers évènements

134
 8. Les Triggers
 Les niveaux d’exécutions
• Au niveau table
– Exécuté une seule fois quelque soit le nombre de ligne modifié
• Au niveau enregistrement
– Exécuté séparément pour chaque ligne modifiée: FOR EACH ROW

135
 8. Les Triggers
 Les types de déclencheurs
 Les déclencheurs LMD
 Avant ou après
 INSERT, UPDATE ou DELETE
 Les déclencheurs INSTEAD OF
 Au niveau ligne seulement
 S’exécute à la place de l’instruction qui a déclenché le trigger
 Les déclencheurs SYSTEM
 Démarrage ou arrêt d’une base
 A la création, suppression ou modification d’un objet (DDL)
 A la connexion ou déconnexion d’un utilisateur

136
 8. Les Triggers
 Les déclencheurs LMD
 Ils sont lancés par une opération insert ou update ou delete

 Le même déclencheur peut s'activer par les trois opérations

 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.

 Exemple

CREATE TRIGGER trigInsertCoureur before insert or delete on tdf_coureur

137
 8. Les Triggers
 Syntaxe de création
CREATE [OR REPLACE] TRIGGER nom_trigger

{ BEFORE/ AFTER/ -- Le trigger est exécuté avant/après la vérification des contraintes de

-- tables et la mise à jour des données dans la table
INSTEAD OF / --remplace le traitement associé à l’instruction qui a déclenché le trig.
INSERT/UPDATE [of col,…]/DELETE -- instructions associée au déclenchement du
-- triggers
}

ON nom_table [FOR EACH ROW] -- Le trigger doit être exécuté pour chaque ligne

[FOLLOWS nom_autre trigger[,…] -- le trigger doit être déclenché après les trig. mentionnées
[ENABLE/DISALBLE] -- trigger est actif ou non
[WHEN (condition)] -- doit être vérifiée pour que le code s’exécute

Bloc PL/SQL

138
 8. Les Triggers
 Déclenchement
 Ce qui déclenche le déclencheur
 INSERT
o AVANT ou APRES insertion

 UPDATE
o AVANT ou APRES mise à jour

 DELETE
o AVANT ou APRES suppression

139
 8. Les Triggers
 Caractéristiques
• Doit posséder un nom unique

• La définition du trigger porte sur:

 une est une seule table

 pas une vue.

140
 8. Les Triggers
 Nombre de triggers par table
• Possibilité de créer un nombre illimité de déclencheurs

• Possibilité de définir plusieurs déclencheurs d’un même type

• Exécution séquentielle des déclencheurs s’il en existe plusieurs

141
 8. Les Triggers
 Accès aux valeurs via les attributs :OLD et :NEW
• Possibilité d’accéder à la valeur des attributs avant et après modification

• :OLD.nom_attribut : permet d’accéder à la valeur avant modification

• :NEW.nom_attribut : permet d’accéder à la valeur après modification

142
 8. Les Triggers
 Quelques précisions
• INSERT
 Pas d’accès à l’élément OLD (n’existe pas)

• UPDATE
 Accès possible à l’élément OLD et NEW

• DELETE
 Pas d’accès à l’élément NEW (n’existe plus)

• Utilisable uniquement avec un déclencheur de niveau enregistrement

143
 8. Les Triggers
 Déclenchement conditionnel et les prédicats
• Clause WHEN
 Ne s’applique qu’aux déclencheurs de niveau enregistrement
 Syntaxe: WHEN <condition>
 Exemple

144
 8. Les Triggers
 Déclenchement conditionnel et les prédicats
• Clause WHEN
 Ne s’applique qu’aux déclencheurs de niveau enregistrement
 Syntaxe: WHEN <condition>
 Exemple

145
 8. Les Triggers
 Déclenchement conditionnel et les prédicats
• Clause WHEN
 Ne s’applique qu’aux déclencheurs de niveau enregistrement
 Syntaxe: WHEN <condition>
 Exemple

create table EMP_Log(

emp_id number,
date_evt date,
msg varchar(20)
);
/

146
 8. Les Triggers
 Déclenchement conditionnel et les prédicats
• Clause WHEN
 Ne s’applique qu’aux déclencheurs de niveau enregistrement
 Syntaxe: WHEN <condition>
 Exemple

create or replace trigger journal_emp

after update of salary on employees
for each row
when (new.salary<old.salary)
begin
insert into EMP_LOG(emp_id,date_evt,msg)values(:new.employee_id,sysdate,
'salaire diminue');
end;
/

147
 8. Les Triggers
 Déclenchement conditionnel et les prédicats
• Clause WHEN
 Ne s’applique qu’aux déclencheurs de niveau enregistrement
 Syntaxe: WHEN <condition>
 Exemple

>>update employees set salary=1000 where salary=9000;

>>select * from EMP_LOG;

148
 8. Les Triggers
 Déclenchement conditionnel et les prédicats
• Les fonctions prédicats

 INSERTING: Retourne TRUE si l’instruction LMD est un INSERT

 UPDATING : Retourne TRUE si l’instruction LMD est un UPDATE

 DELETING: Retourne TRUE si l’instruction LMD est un DELETE

149
 8. Les Triggers
 Déclenchement conditionnel et les prédicats
• Les fonctions prédicats: exemple1

150
 8. Les Triggers
 Déclenchement conditionnel et les prédicats
• Les fonctions prédicats: exemple2
• On veut enregistrer les opérations réalisées sur la table Employe

151
 8. Les Triggers
 Les déclencheurs INSTEAD OF
• Définis uniquement sur des objets vues

• S’exécute à la place de l’ordre LMD qui a déclenché le déclencheur

• La syntaxe d'un déclencheur sur vue est identique à celle du déclencheur sur
table, à la différence que la clause INSTEAD OF est ajoutée

152
 8. Les Triggers
 Les déclencheurs INSTEAD OF
• Exemple
Création de la vue VW_EMP_CLERK
CREATE OR REPLACE VIEW VW_EMP_CLERK AS
Select EMPLOYEE_ID "Numero", FIRST_NAME "Nom", DEPARTMENT_ID "Dept",
SALARY "Salaire" From employees Where JOB_ID = 'ST_CLERK';
/
Vue créée
>> select *from VW_EMP_CLERK;

153
 8. Les Triggers
 Les déclencheurs INSTEAD OF
• Exemple(suite)
Insertion des données dans la table EMP (à travers la vue)

La colonne job (inutile dans ce cas) ne fait pas partie de la vue et donc de l'insertion

Nous allons donc créer un déclencheur sur vue qui va résoudre ce problème
154
 8. Les Triggers
 Les déclencheurs INSTEAD OF
• Exemple(suite)
Insertion des données dans la table EMP (à travers la vue)

create or replace trigger trg_bir_vw_emp_clerk

INSTEAD OF insert
on vw_emp_clerk
for each ROW
begin
insert INTO EMPLOYEES (
EMPLOYEE_ID,LAST_NAME,DEPARTMENT_ID,SALARY,EMAIL,HIRE_DATE,JOB_ID)
values (:new."Numero",
:new."Nom",:new."Dept",:new."Salaire",:new."Nom",sysdate,'ST_CLERK');
end;

155
 8. Les Triggers
 Quelques caractéristiques
• Triggers en cascade
 Un trigger peut provoquer le déclenchement d'un autre trigger.

 ORACLE autorise jusqu'à 32 triggers en cascade à un moment donné.

• Limite
 Un trigger ligne ne peut pas lire et/ou modifier la table concernée (appelée table
mutante) par l'instruction (INSERT, UPDATE ou DELETE) qui a déclenché le trigger.

156
 8. Les Triggers
 Création de déclencheurs
• Conditions nécessaires pour créer un trigger
 Privilège CREATE TRIGGER
 Posséder la table sur laquelle on veut définir le déclencheur

• Modification de triggers
 CREATE TRIGGER suivie de OR REPLACE ou bien on supprime le trigger (DROP
TRIGGER nomtrigger) et on le crée à nouveau.

157
 8. Les Triggers
 Activation et désactivation
• Un trigger peut être activé ou désactivé.
 Si désactivé, ORACLE le stocke mais l’ignore.

• Possibilité de désactiver un trigger si :

 il référence un objet non disponible

 on veut charger rapidement un volume de données important ou recharger des

données déjà contrôlées.

• Par défaut, un trigger est activé dès sa création.

158
 8. Les Triggers
 Activation et désactivation
• Pour désactiver ou activer un trigger , on utilise l’instruction ALTER
 ALTER TRIGGER <nomtrigger> DISABLE;

 ALTER TABLE <nomtable> DISABLE ALL TRIGGERS;

 ALTER TRIGGER <nomtrigger> ENABLE;

 ALTER TABLE <nomtable> ENABLE ALL TRIGGERS;

159
 8. Les Triggers
 Les déclencheurs système ou utilisateur
• Utiliser pour suivre les changements d'état du système ainsi que les
connexions/déconnexions utilisateur et la surveillance des ordres DDL et
DML.

• Lors de l'écriture de ces déclencheurs, il est possible d'utiliser des attributs

pour identifier précisément l'origine des évènements et adapter les
traitements en conséquence

160
 8. Les Triggers
 Les déclencheurs système ou utilisateur
• Les attributs
ora_client_ip_adress Adresse IP du poste client qui se connecte

ora_database_name Nom de la base de données

ora_des_encrypted_password Description codée du mot de passe de l'utilisateur créé ou modifié

ora_dict_obj_name Nom de l'objet visé par l'opération DDL

ora_dict_obj_name_list Liste de tous les noms d'objets modifiés

ora_dict_obj_owner Propriétaire de l'objet visé par l'opération DDL

ora_dict_obj_owner_list Liste de tous les propriétaires d'objets modifiés

ora_dict_obj_type Type de l'objet visé par l'opération DDL

ora_grantee Liste des utilisateurs disposant du privilège

ora_instance_num Numéro de l'instance

ora_is_alter_column Vrai si la colonne en paramètre a été modifiée

ora_is_drop_column Modification ou non de la colonne en paramètre

161
 8. Les Triggers
 Les déclencheurs système ou utilisateur
• Les événements systèmes

CREATE TRIGGER nom_déclencheur

{BEFORE|AFTER} évènement_système
ON{DATABASE|SCHEMA}
bloc PL/SQL

STARTUP Evènement déclenché lors de l'ouverture de l'instance (AFTER

seulement)
SHUTDOWN Evènement déclenché avant le processus d'arrêt de l'instance (non
déclenché en cas d'arrêt brutal du serveur) (BEFORE seulement)
SERVERERROR Evènement déclenché lors d'une erreur Oracle (sauf ORA-1034,
ORA-1403, ORA-1422, ORA-1423 et ORA-4030) (AFTER seulement)

162
 8. Les Triggers
 Les déclencheurs système ou utilisateur
• Les événements utilisateurs
CREATE TRIGGER nom_déclencheur {BEFORE|AFTER}
évènement_utilisateur ON{DATABASE|SCHEMA} bloc PL/SQL
LOGON Après une connexion (AFTER seulement)
LOGOFF Avant une déconnexion (BEFORE seulement)
CREATE Lors de la création d'un objet
ALTER Lors de la modification d'un objet
DROP Lors de la suppression d'un objet
ANALYZE Lors de l'analyse d'un objet
ASSOCIATE Lors de l'association d'une statistique
STATISTICS
AUDIT Lors de la mise en place d'un audit
NOAUDIT Lors de l'annulation d'un audit
COMMENT Lors de l'insertion d'un commentaire
RENAME Lors de l'exécution d'une commande RENAME
GRANT Lors de l'exécution d'une commande GRANT
163
 8. Les Triggers
 Les déclencheurs système ou utilisateur
• Les événements utilisateurs
CREATE TRIGGER nom_déclencheur {BEFORE|AFTER}
évènement_utilisateur ON{DATABASE|SCHEMA} bloc PL/SQL
LOGON Après une connexion (AFTER seulement)
LOGOFF Avant une déconnexion (BEFORE seulement)
CREATE Lors de la création d'un objet
ALTER Lors de la modification d'un objet
DROP Lors de la suppression d'un objet
ANALYZE Lors de l'analyse d'un objet
ASSOCIATE Lors de l'association d'une statistique
STATISTICS
AUDIT Lors de la mise en place d'un audit
NOAUDIT Lors de l'annulation d'un audit
COMMENT Lors de l'insertion d'un commentaire
RENAME Lors de l'exécution d'une commande RENAME
GRANT Lors de l'exécution d'une commande GRANT
164