Oracle - PL / SQL

(Procedural Language / Structured Query

Language)
 Pourquoi PL/SQL ?

 SQL est un langage non procédural

🞑 Les traitements complexes sont parfois difficiles à écrire si on
ne peut utiliser des variables et les structures de
programmation comme les boucles et les alternatives
 On ressent vite le besoin d’un langage procédural pour
lier plusieurs requêtes SQL avec des variables et dans
les structures de programmation habituelles
 Présentation du langage PL/SQL

PL/SQL

PROCEDURAL STATEMENT EXECUTOR

SQL STATEMENT EXECUTOR
Intégréau noyau et aux outils
Intégréau noyauRDBMS.
(Forms, Rports, OAS, …)

REGLES

Déclarations possibles des variables.

Intégration de la majorité desordresSQL..

Traitements séquentiels.

Exécution conditionnelle de blocs d’instructions.

Traitements répétitifs (boucles).

Gestion desexceptions.
 Avantages de PL/SQL

 Intégration
 Amélioration des performances
 Portabilité
 Développement modulaire
DÉVELOPPER UN BLOC
SIMPLE PL/SQL PL
Structure d'un Block PL/SQL

[ DECLARE ]
- Variables, constantes, curseurs,
exceptions utilisateurs
BEGIN
- Ordres SQL
- Instructions de Contrôle PL/SQL [
EXCEPTION ]
- Traitements à effectuer lors d'erreurs
END ;
 STRUCRURED’UN
BLOCPL/SQL

BLOC PL/SQL

Partie déclarative Partie exécutable Partie exception

(optionnelle) (obligatoire) (optionnelle)

DECLARE BEGIN … END EXCEPTION

• Chaque instruction se termine par un point virgule.

• Lescommentairessont possibles/ * * / .

• Possibilité d’imbrication desblocs.

Règles Syntaxiques d'un Bloc PL/SQL

 Identifiants :
🞑 Peuvent contenir jusqu'à 30 caractères.
🞑 Ne peuvent pas contenir de mots réservés à moins qu'ils

soient encadrés de guillemets.

🞑 Doivent commencer par une lettre.

🞑 Doivent avoir un nom distinct de celui d'une table de la base

ou d'une colonne.
Règles Syntaxiques d'un Bloc PL/SQL

 Utiliser un slash (/) pour exécuter un boc PL/SQL

anonyme dans PL/SQL.
 Placer un point virgule (;) à la fin d’une instruction SQL ou
SQL*PLUS
 Les chaînes de caractères et les dates doivent être
entourées de simples quotes ( ' ' ).
 Les commentaires peuvent être
- sur plusieurs lignes avec :
/* début et
fin de commentaire*/
- sur une ligne précédée de :
-- début et fin de commentaire
 Les variables

En PL/SQL, une variable est le nom explicite d’un emplacement de

stockage temporaire prenant en charge un type de données particulier
dans un programme. Avant d'utiliser une variable, vous devez d'abord
la déclarer dans la section de déclaration d'un bloc PL / SQL.
 Les variables

Il existe quatre catégories de types de variables :

Les variables - scalaire
 Les variables

Convention de nommage des variables PL / SQL:

Il est vivement recommandé de suivre les conventions de
dénomination dans le tableau suivant pour rendre les variables
évidentes dans les programmes PL / SQL:
Prefix Data Type
v_ VARCHAR2
n_ NUMBER
t_ TABLE
r_ ROW
d_ DATE
b_ BOOLEAN
 Les variables
Déclaration de variables PL / SQL
Pour déclarer une variable, vous utilisez un nom de variable suivi du type
de données et terminé par un point-virgule (;). Vous pouvez également
ajouter explicitement une contrainte de longueur au type de données
entre parenthèses.
DECLARE
v_last_name varchar2(20);
n_employee_id number;
BEGIN
NULL;
END;
Name [CONSTANT] datatype [NOT NULL] [:= DEFAULT value|expression];
 Les variables -référence
Déclaration d’un variable de référence PL / SQL
Dans le programme PL/SQL, l’une des tâches les plus courantes
consiste à sélectionner les valeurs des colonnes d’un tableau dans un
ensemble de variables. Si les types de données des colonnes de la
table changent, vous devez modifier le programme PL / SQL pour que
les types des variables soient compatibles avec les nouvelles
modifications.

PL/SQL fait référence à l’utilisation du mot clé %TYPE pour

déclarer une variable dont le type de données est associé au type
de données d’une colonne d’une colonne donnée dans une table.

Variable_name table.column_name%TYPE | another_variable%TYPE ;

Les variables -référence

Déclaration d’une variable de référence PL / SQL

DECLARE
v_first_name EMPLOYEES.FIRST_NAME%TYPE;
v_last_name EMPLOYEES.LAST_NAME%TYPE;
n_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE;
d_hire_date EMPLOYEES.HIRE_DATE%TYPE;
BEGIN
NULL;
END;
/
Les variables

Affectation de variable PL / SQL

pour affecter une valeur ou une variable à une autre, vous utilisez
l'opérateur d'affectation (: =).
DECLARE
v_first_name EMPLOYEES.FIRST_NAME%TYPE;
v_last_name EMPLOYEES.LAST_NAME%TYPE;
n_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE;
d_hire_date EMPLOYEES.HIRE_DATE%TYPE;
BEGIN
v_first_name := 'Mary';
v_last_name := 'Jane';
d_hire_date := to_date('19700101','YYYYMMDD');
END;
/
Les variables

Affectation de variable PL / SQL

Vous pouvez utiliser INTO dans l'instruction SELECT pour affecter une
valeur à une variable. La clause INTO déplace les valeurs de la liste
des colonnes de SELECT query dans les variables PL / SQL
correspondantes.
 Les variables

Affectation de variable PL / SQL

SET SERVEROUTPUT ON; d_hire_date
DECLARE FROM employees
v_first_name EMPLOYEES.FIRST_NAME%TYPE; WHERE employee_id = 200;
v_last_name EMPLOYEES.LAST_NAME%TYPE;
n_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE; DBMS_OUTPUT.PUT_LINE(v_first_name);
d_hire_date EMPLOYEES.HIRE_DATE%TYPE; DBMS_OUTPUT.PUT_LINE(v_last_name);
BEGIN DBMS_OUTPUT.PUT_LINE(d_hire_date);
SELECT employee_id, END;
first_name, /
last_name,
hire_date
INTO n_employee_id,
v_first_name,
v_last_name,
Les variables

délimiteurs et commentaires de votre code

les délimiteurs sont des symboles ayant une signification
Les variables - Record

Un enregistrement PL / SQL est une structure de données composite

qui est un groupe de données liées stockées dans des champs.
Chaque champ de l'enregistrement PL / SQL a son propre nom et son
propre type de données.

TYPE type_name IS RECORD

(field1 data_type1 [NOT NULL] := [DEFAULT VALUE],
field2 data_type2 [NOT NULL] := [DEFAULT VALUE],
...
fieldn data_type3 [NOT NULL] := [DEFAULT VALUE]
);
Les variables - Record

DECLARE
table_based_record table_name%ROWTYPE;

DECLARE
r_emp employees%ROWTYPE;
n_emp_id employees.employee_id%TYPE := 200;
BEGIN
SELECT *
INTO r_emp
FROM employees
WHERE employee_id = n_emp_id;
-- print out the employee's first name
DBMS_OUTPUT.PUT_LINE(r_emp.first_name);
END;
/
Les variables - Record

SET SERVEROUTPUT ON SIZE 1000000;

DECLARE
TYPE t_name IS RECORD(
first_name employees.first_name%TYPE,
last_name employees.last_name%TYPE
);
r_name t_name; -- name record
n_emp_id employees.employee_id%TYPE := 200;
BEGIN
SELECT first_name,
last_name
INTO r_name
FROM employees
WHERE employee_id = n_emp_id;
-- print out the employee's name
DBMS_OUTPUT.PUT_LINE(r_name.first_name || ',' || r_name.last_name );
END;
/
Les variables - Record

Au niveau de l'enregistrement, vous pouvez effectuer les opérations suivantes:

• Vous pouvez affecter un enregistrement PL / SQL à un autre enregistrement

PL / SQL. La paire d'enregistrements PL / SQL doit avoir le même nombre de
champs et le type de données de chaque champ doit être convertible.
• Vous pouvez affecter une valeur NULL à un enregistrement PL / SQL en
affectant un enregistrement non initialisé.
• Un enregistrement PL / SQL peut être utilisé comme argument de paramètre
dans une fonction
• Vous pouvez renvoyer un enregistrement PL / SQL à partir d'une fonction
• Pour vérifier si l'enregistrement est NULL, vous devez vérifier chaque champ
individuel de l'enregistrement.
• Pour comparer deux enregistrements, vous devez comparer chaque champ
individuel de chaque enregistrement.
 Traitements
Conditionnels et
Traitements Répétitifs PL
Les structures de contrôle

IF statement:
IF condition THEN
sequence_of_statements;
END IF;

IF-THEN-ELSE statement:
IF condition THEN
sequence_of_statements;
ELSE
sequence_of_else_statements;
END IF;
Les structures de contrôle

IF-THEN-ELSIF Statement
IF condition1 THEN
sequence_of_statements1
ELSIF condition2 THEN
sequence_of_statements2
ELSE
sequence_of_statements3
END IF;
 Exemple

IF salaire < =1000 THEN nouveau_salaire := ancien_salaire + 100;

ELSEIF salaire > 1000 AND emp_id_emp = 1 THEN
nouveau_salaire := ancien_salaire + 500;
ELSE nouveau_salaire := ancien_salaire + 300;
END IF;

216
Les structures de contrôle

CASE Statement
CASE [TRUE | selector]
WHEN expression1 THEN
sequence_of_statements1;
WHEN expression2 THEN
sequence_of_statements2;
...
WHEN expressionN THEN
sequence_of_statementsN;
[ELSE sequence_of_statementsN+1;]
END CASE [label_name];
NB :PL / SQL ajoutera la clause ELSE implicite suivante: ELSE RAISE CASE_NOT_FOUND;
Les structures de contrôle

PL / SQL fournit une instruction CASE spéciale appelée instruction CASE

recherchée. La syntaxe de l'instruction CASE recherchée par PL / SQL est la
suivante:
Searched CASE Statement
CASE
WHEN search_condition_1 THEN sequence_of_statements_1;
WHEN search_condition_2 THEN sequence_of_statements_2;
...
WHEN search_condition_N THEN sequence_of_statements_N;
[ELSE sequence_of_statements_N+1;]
END CASE
Les structures de contrôle

LOOP Statement
LOOP
sequence_of_statements;
END LOOP;
Il doit y avoir au moins une instruction exécutable entre les mots clés LOOP et
END LOOP. La séquence d'instructions est exécutée à plusieurs reprises jusqu'à
atteindre une sortie de boucle. PL / SQL fournit des instructions EXIT et EXIT-
WHEN pour vous permettre de terminer une boucle.
Les structures de contrôle

LOOP Statement
Une boucle peut avoir une étiquette facultative qui est un identifiant non
déclaré entouré par des crochets doubles «étiquette». L'étiquette de la boucle
apparaît au début et à la fin de l'instruction PL / SQL LOOP. Une étiquette de
boucle est utilisée pour qualifier le nom de la variable de compteur de boucle
lorsqu'une boucle est imbriquée dans une autre boucle.
<<label>>
LOOP
sequence_of_statements;
END LOOP label;
Les structures de contrôle

FOR LOOP Statement

FOR loop_counter IN [REVERSE] lower_bound .. higher_bound LOOP
sequence_of_statements;
END LOOP;
Les structures de contrôle

WHILE LOOP Statement

WHILE condition LOOP
sequence_of_statements;
END LOOP;

NB: Si la condition est évaluée à FALSE ou à NULL, la boucle se termine et le

contrôle est passé à la prochaine instruction exécutable suivant les mots clés
END LOOP.
Exercice:

Ecrire un programme PL/SQL qui affiche les multiples de 3, 4

et 5 qui sont entre 4 et 32.

223
 Exercice:
SET SERVEROUTPUT ON -- sous SQL pLUS
DECLARE i NUMBER:= 4;
BEGIN
LOOP
IF (MOD(i,3)=0) THEN
DBMS_OUTPUT.PUT_LINE (i || ‘ est un multiple de 3’);
END IF;
IF (MOD(i,4)=0) THEN
DBMS_OUTPUT.PUT_LINE (i || ‘ est un multiple de 4’);
END IF;
IF (MOD(i,5)=0) THEN
DBMS_OUTPUT.PUT_LINE (i || ‘ est un multiple de 5’);
END IF;
i := i+1;
EXIT WHEN i>32;
END LOOP; 224
END;
Les structures de contrôle

set serveroutput on;

DECLARE
n_counter NUMBER := 4;
n_factorial NUMBER := 1;
n_temp NUMBER;
BEGIN
n_temp := n_counter;
WHILE n_counter > 0
LOOP
n_factorial := n_factorial * n_counter;
n_counter := n_counter - 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('factorial of ' || n_temp ||' is ' || n_factorial);
END;
Exceptions & Curseurs

PL
 Exceptions

• En PL / SQL, tout type d'erreur est traité comme une exception.

• Une exception est définie comme une condition spéciale
modifiant le flux d'exécution du programme.
• PL / SQL vous offre un moyen souple et puissant de gérer de
telles exceptions.
Exceptions
 Exceptions

Il existe deux types d'exceptions:

Exception système: l’exception système est générée par
l’exécution de PL / SQL lorsqu’elle détecte une erreur. Par exemple,
une exception NO_DATA_FOUND est déclenchée si vous
sélectionnez un enregistrement non existant dans la base de
données.
Exception définie par le programmeur: l'exception définie par le
programmeur dans une application spécifique. on peut mapper des
noms d'exception avec des erreurs Oracle spécifiques à l'aide de
EXCEPTION_INIT pragma. On peut également affecter un numéro et
une description à l'exception à l'aide de
RAISE_APPLICATION_ERROR.
 Exceptions
Pour définir une exception, on utilise le mot EXCEPTION
comme ci-dessous:
EXCEPTION_NAME EXCEPTION;
Pour déclencher cette exception, on utilise le mot RAISE
RAISE EXCEPTION_NAME;
 Exceptions

Exception Oracle Error SQLCODE Description

Il est déclenché lorsqu'un objet null reçoit
ACCESS_INTO_NULL 06530 -6530
automatiquement une valeur.

Il est déclenché lorsqu'aucun des choix de la clause WHEN

CASE_NOT_FOUND 06592 -6592 d'une instruction CASE n'est sélectionné et qu'il n'y a pas de
clause ELSE.

s'il tente d'attribuer des valeurs aux éléments d'une table

COLLECTION_IS_NULL 06531 -6531
ou d'un tableau imbriqué non initialisé.
 Exceptions

Exception Oracle Error SQLCODE Description

Il est déclenché lorsque l'on tente de stocker les valeurs en
DUP_VAL_ON_INDEX 00001 -1
double dans une colonne à index unique.

Il est déclenché lorsque vous tentez de créer une

INVALID_CURSOR 01001 -1001 opération de curseur non autorisée, telle que la fermeture
d'un curseur non ouvert.

Il est déclenché lorsque la conversion d'une chaîne de

INVALID_NUMBER 01722 -1722 caractères en un nombre échoue car cette chaîne ne
représente pas un nombre valide.
 Exceptions

Oracle
Exception SQLCODE Description
Error
Il est déclenché lorsqu'une instruction SELECT INTO ne
NO_DATA_FOUND 01403 +100
renvoie aucune ligne.

Il est déclenché lorsqu'un curseur extrait une valeur d'une

ROWTYPE_MISMATCH 06504 -6504
variable dont le type de données est incompatible.

Il est déclenché lorsqu'une instruction SELECT INTO

TOO_MANY_ROWS 01422 -1422
renvoie plusieurs lignes.
Les curseurs - cursor

Un curseur PL / SQL est un pointeur qui pointe sur le jeu de résultats

d'une requête SQL par rapport à des tables de base de données.

Non

Oui
DECLARE OPEN FETCH VIDE ? CLOSE

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

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

Exemple :
DECLARE
CURSOR c1 IS
SELECT employee_id, last_name
FROM employees;

CURSOR c2 IS
SELECT *
FROM departments
WHERE department_id = 10;
BEGIN
...

Il est important de noter que le nom du curseur n’est pas une variable. Par conséquent, vous ne pouvez pas l’utiliser
comme variable, telle que l’affecter à un autre curseur ou l’utiliser dans une expression.
 Ouvrir le curseur

Syntaxe :

OPEN cursor_name;

• Ouvrir le curseur pour exécuter l'interrogation et

identifier l'ensemble actif
• Si l'interrogation ne renvoie pas de ligne, aucune
exception n'est déclenchée
• Utiliser les attributs du curseur pour tester le
résultat après une extraction
Extraire les données à
partir du curseur
Exemples :
FETCH c1 INTO v_empid, v_ename;

DECLARE
v_empid employees.employee_id%TYPE;
... v_ename employees.last_name%TYPE;
OPEN i NUMBER := 1;
defined_cursor;
LOOP CURSOR c1 IS
FETCHSELECT employee_id,
defined_cursor INTOlast_name
defined_variables
EXIT FROM employees;
WHEN ...;
BEGIN
...
OPEN
-- c1; the retrieved data
Process
...FOR i IN 1..10 LOOP
END; FETCH c1 INTO v_empid, v_ename;
...
END LOOP;
END ;
 Fermer le curseur

Syntaxe :
CLOSE cursor_name;

• Fermer le curseur après avoir terminé le traitement

des lignes
• Rouvrir le curseur, si nécessaire
• Ne pas essayer d'extraire les données d'un curseur
s'il a été fermé
 Attributs d'un curseur explicite

Obtenir les informations d'état concernant un curseur

NomCurseur%attribut
Attribut Type Description
%ISOPEN BOOLEAN Prend la valeur TRUE si le curseur est
ouvert
%NOTFOUND BOOLEAN Prend la valeur TRUE si la dernière
extraction ne renvoie pas de ligne
%FOUND BOOLEAN Prend la valeur TRUE si la dernière
extraction renvoie une ligne ;
complément de %NOTFOUND
%ROWCOUNT NUMBER Prend la valeur correspondant au
nombre total de lignes renvoyées
jusqu'à présent
Boucles FOR de curseur
Procéder à l'extraction des employés, un par un, jusqu'au dernier
Exemple :

DECLARE
CURSOR c1 IS
SELECT employee_id, last_name
FROM employees;
BEGIN
FOR emp_record IN c1 LOOP

-- implicit open and implicit fetch occur

IF emp_record.employee_id = 134 THEN

...
END LOOP; -- implicit close occurs
END;
 Clause WHERE CURRENT OF

La clause CURRENT-OF permet d'accéder directement en écriture

à la ligne en cours de lecture
Exemple : DECLARE
CURSOR cursLivre IS
SELECT liv_num, liv_titre FROM livre
ORDER BY liv_num
FOR UPDATE OF liv_num;
BEGIN
FOR monLivre IN cursLivre LOOP
UPDATE livre SET liv_num = liv_num+200
WHERE CURRENT OF cursLivre;
END LOOP;
COMMIT; -- valide les modifications et lève le verrou
END;
Clause WHERE CURRENT OF
Exemple:
DECLARE
CURSOR c1 IS
SELECT salary FROM employees
FOR UPDATE OF salary NOWAIT;
BEGIN
...
FOR emp_record IN c1 LOOP
UPDATE ...
WHERE CURRENT OF c1;
...
END LOOP;
COMMIT;
END;
 Intercepter les erreurs prédéfinies
du serveur Oracle
 Utiliser le nom standard à l'intérieur du sous-programme
de traitement des exceptions
 Exemples d'exceptions prédéfinies (le package
STANDARD) :
• NO_DATA_FOUND
• TOO_MANY_ROWS
• INVALID_CURSOR
• ZERO_DIVIDE
• DUP_VAL_ON_INDEX
Exception prédéfinie

Syntaxe :
BEGIN SELECT ... COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
statement1;
statement2;
WHEN TOO_MANY_ROWS THEN
statement1;
WHEN OTHERS THEN
statement1;
statement2;
statement3;
END;
 Erreur non prédéfinie
Intercepter une violation de contrainte d'intégrité
(code d'erreur du serveur Oracle -2292)
DECLARE
e_products_invalid EXCEPTION; 1 1
PRAGMA EXCEPTION_INIT (
e_products_invalid, -2292); 2 2
v_message VARCHAR2(50);
BEGIN
. . . 3
EXCEPTION
WHEN e_products_invalid THEN
:v_message := 'Product ID specified is not valid.';
. . .
END;
Exception définie par l'utilisateur
Pour intercepter une exception définie par l'utilisateur, vous devez la déclarer et la
déclencher explicitement.
DECLARE
nom_erreur EXCEPTION; 1
. . .
BEGIN
. . .IF (Problème) THEN
RAISE nom_erreur; 2
. . .
EXCEPTION
WHEN nom_erreur THEN 3
(traitement de l’erreur);
. . .
END;
RAISE_APPLICATION_ERROR

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

Permet de déclencher des exceptions en leur attribuant un numéro

et un message.

Elle est appelée uniquement à partir d'un sous-programme stocké

en cours d'exécution
error_number est une valeur numérique définie par l'utilisateur pour
l'exception (-20000 … -20999).
 RAISE_APPLICATION_ERROR

Elle peut être utilisée à deux endroits :

section exécutable
section de traitement des exceptions
DECLARE
. . .
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;
. . .
END;
Procédures et Fonctions

PL
 Définition d'une procédure

 Une procédure est un type de sous-programme qui exécute une

action

 Une procédure peut être stockée en tant qu'objet de schéma dans

la base de données en vue d'exécutions répétées
 Syntaxe pour la création de procédures

CREATE [OR REPLACE] PROCEDURE procedure_name

[(parameter1 [mode1] datatype1,
parameter2 [mode2] datatype2,
. . .)]
IS|AS
PL/SQL Block;

• L'option REPLACE indique que, si la procédure existe, elle sera

supprimée et remplacée par la nouvelle version créée avec
l'instruction
• Le bloc PL/SQL commence par BEGIN ou par la déclaration de
variables locales et se termine par END ou par END
procedure_name
 Paramètres formels/réels

 Les paramètres formels sont des variables déclarées dans la

liste de paramètres d'une spécification de sous-programme
Exemple:
CREATE PROCEDURE raise_sal(p_id NUMBER, p_amount NUMBER)
...
END raise_sal;
 Les paramètres réels sont des variables ou des expressions
référencées dans la liste de paramètres d'un appel de sous-
programme
Exemple:
raise_sal(v_id, 2000)
Modes des paramètres des procédures
Procédure
Paramètre IN
Environnement
Paramètre OUT
appelant
Paramètre IN OUT

(DECLARE)
RQ : DATATYPE ne peut être que la
définition %TYPE ou %ROWTYPE, ou un BEGIN
type de données explicite sans
EXCEPTION
spécification de taille.
END;
Créer des procédures avec des paramètres

IN OUT IN OUT
Mode par défaut Doit être indiqué Doit être indiqué
La valeur est transmise au Est renvoyé à Est transmis à un sous-
sous-programme l'environnement programme ; est renvoyé à
appelant l'environnement appelant

Le paramètre formel se Variable non Variable initialisée

comporte en constante initialisée
Le paramètre réel peut être un Doit être une Doit être une
littéral, une expression, une variable variable
constante ou une variable
initialisée
Peut se voir affecter une valeur Ne peut pas se voir Ne peut pas se voir
par défaut affecter de valeur affecter de valeur par
par défaut défaut
 Exemples de paramètres IN

176 p_id

CREATE OR REPLACE PROCEDURE raise_salary

(p_id IN employees.employee_id%TYPE)
IS
BEGIN
UPDATE employees
SET salary = salary * 1.10
WHERE employee_id = p_id;
END raise_salary;

Par défaut, le paramètre IN est transmis par référence et les paramètres OUT et IN OUT sont transmis
par valeur.
Exemples de paramètres OUT

Environnement appelant Procédure

Environnement appelant QUERY_EMP
Procédure QUERY_EMP

171 p_id

SMITH p_name

7400 p_salary

0.15 p_comm
 Exemples de paramètres OUT

emp_query.sql
CREATE OR REPLACE PROCEDURE query_emp
(p_id IN employees.employee_id%TYPE,
p_name OUT employees.last_name%TYPE,
p_salary OUT employees.salary%TYPE,
p_comm OUT employees.commission_pct%TYPE)
IS
BEGIN
SELECT last_name, salary, commission_pct
INTO p_name, p_salary, p_comm
FROM employees
WHERE employee_id = p_id;
END query_emp;
 Visualiser des paramètres OUT

• Déclarer les variables, exécuter la procédure QUERY_EMP, puis

imprimer la valeur de la variable globale G_NAME
VARIABLE g_name VARCHAR2(25)
VARIABLE g_sal NUMBER
VARIABLE g_comm NUMBER

EXECUTE query_emp(171, :g_name, :g_sal, :g_comm)

PRINT g_name

RQ :
--N'indiquez pas de taille pour une variable hôte de type NUMBER.
--La longueur par défaut d'une variable de type CHAR ou VARCHAR2 est de 1, si
aucune valeur n'est indiquée entre parenthèses.
Paramètres IN OUT

Environnement appelant Procédure FORMAT_PHONE

'8006330575' '(800)633-0575' p_phone_no

CREATE OR REPLACE PROCEDURE format_phone

(p_phone_no IN OUT VARCHAR2)
IS
BEGIN
p_phone_no := '(' || SUBSTR(p_phone_no,1,3) ||
')' || SUBSTR(p_phone_no,4,3) ||
'-' || SUBSTR(p_phone_no,7,4);
END format_phone;
Visualiser des paramètres IN OUT

VARIABLE g_phone_no VARCHAR2(15)

BEGIN
:g_phone_no := '8006330575';
END;

PRINT g_phone_no
EXECUTE format_phone (:g_phone_no)
PRINT g_phone_no
 Méthodes de transmission des paramètres

Méthode positionnelle : répertorie les paramètres réels dans le même

ordre que les paramètres formels
Méthode de transmission de paramètres par association de noms :
répertorie les paramètres réels dans un ordre arbitraire en associant
chacun d'eux au paramètre formel correspondant
Méthode par combinaison : répertorie certains paramètres réels en tant
que paramètres positionnels et d'autres en tant que paramètres
nommés
 Option DEFAULT des paramètres

CREATE OR REPLACE PROCEDURE add_dept

(p_name IN departments.department_name%TYPE
DEFAULT 'unknown',
p_loc IN departments.location_id%TYPE
DEFAULT 1700)
IS
BEGIN
INSERT INTO departments(department_id,
department_name, location_id)
VALUES (departments_seq.NEXTVAL, p_name, p_loc);
END add_dept;

RQ : Les paramètres OUT et IN OUT n’acceptent pas de valeur par défaut.

Exemples de transmission de paramètres

BEGIN
add_dept; ----????
add_dept ('TRAINING', 2500);
add_dept ( p_loc => 2400, p_name =>'EDUCATION');
add_dept ( p_loc => 1200) ;
END;

SELECT department_id, department_name, location_id

FROM departments;

…
Déclarer des sous-programmes

leave_emp2.sql

CREATE OR REPLACE PROCEDURE leave_emp2

(p_id IN employees.employee_id%TYPE)
IS
PROCEDURE log_exec
IS
BEGIN
INSERT INTO log_table (user_id, log_date)
VALUES (USER, SYSDATE);
END log_exec;
BEGIN
DELETE FROM employees
WHERE employee_id = p_id;
log_exec;
END leave_emp2;
/
Appeler une procédure depuis un bloc PL/SQL anonyme

DECLARE
v_id NUMBER := 163;
BEGIN
raise_salary(v_id); --invoke procedure
COMMIT;
...
END;
Appeler une procédure depuis une
autre procédure

CREATE OR REPLACE PROCEDURE process_emps

IS
CURSOR emp_cursor IS
SELECT employee_id
FROM employees;
BEGIN
FOR emp_rec IN emp_cursor
LOOP
raise_salary(emp_rec.employee_id);
END LOOP;
COMMIT;
END process_emps;
/
Exceptions traitées

Procédure appelée
Procédure appelante PROCEDURE
PROC2 ...
PROCEDURE IS
PROC1 ... ...
IS BEGIN
... ... Exception déclenchée
BEGIN EXCEPTION
... ... Exception traitée
PROC2(arg1); END PROC2;
...
EXCEPTION La procédure
...
END PROC1; appelante reprend
le contrôle
Exceptions non traitées

Procédure appelée
Procédure appelante
PROCEDURE
PROCEDURE PROC2 ...
PROC1 ... IS
IS ...
... BEGIN
BEGIN ... Exception déclenchée
... EXCEPTION
... Exception non traitée
PROC2(arg1); END PROC2;
...
EXCEPTION
...
END PROC1; La section de traitement
des exceptions de la
procédure appelante a
repris le contrôle
 Supprimer des procédures
Supprimer une procédure stockée dans la
base de données.
Syntaxe:
DROP PROCEDURE procedure_name

Exemple:
DROP PROCEDURE raise_salary;
Présentation des fonctions stockées

Une fonction est un bloc PL/SQL nommé qui renvoie

une valeur
Une fonction peut être stockée en tant qu'objet de
schéma dans la base de données en vue
d'exécutions répétées
Une fonction est appelée dans une expression
Syntaxe pour la création de fonctions

CREATE [OR REPLACE] FUNCTION function_name

[(parameter1 [mode1] datatype1,
parameter2 [mode2] datatype2,
. . .)]
RETURN datatype -- ne doit pas inclure de spécification de taille
IS|AS
PL/SQL Block;

Le bloc PL/SQL doit comporter au moins une

instruction RETURN.
 Exécuter des fonctions

Appeler une fonction dans une expression PL/SQL

Créer une variable destinée à recevoir la valeur renvoyée
Exécuter la fonction. La valeur renvoyée par l'instruction
RETURN sera placée dans la variable

RQ : Évitez d'utiliser les modes OUT et IN OUT avec les

fonctions
 Exemple d'exécution de fonctions

Environnement appelant Fonction GET_SAL

117 p_id

RETURN v_salary

1. Charger et exécuter le fichier get_salary.sql pour créer

la fonction
2 VARIABLE g_salary NUMBER

3 EXECUTE :g_salary := get_sal(117)

4 PRINT g_salary
Exemple d'appel de fonctions dans des expressions SQL

CREATE OR REPLACE FUNCTION tax(p_value IN NUMBER)

RETURN NUMBER IS
BEGIN
RETURN (p_value * 0.08);
END tax;

SELECT employee_id, last_name, salary, tax(salary)

FROM employees
WHERE department_id = 100;
Exemple de création d'une fonction stockée

get_salary.sql
CREATE OR REPLACE FUNCTION get_sal
(p_id IN employees.employee_id%TYPE)
RETURN NUMBER
IS
v_salary employees.salary%TYPE :=0;
BEGIN
SELECT salary
INTO v_salary
FROM employees
WHERE employee_id = p_id;
RETURN v_salary;
END get_sal;
Supprimer des fonctions
Supprimer une fonction stockée.
Syntaxe :
DROP FUNCTION function_name

Exemple:
DROP FUNCTION get_sal;

• Tous les privilèges accordés à une fonction sont

annulés lorsque celle-ci est supprimée.
• Lorsque la syntaxe CREATE OR REPLACE est utilisée,
la fonction est supprimée et recréée, mais dans ce
cas, les privilèges accordés sur la fonction ne sont
pas affectés.
 Procédure ou fonction ?

Procédure Fonction
Paramètre IN Paramètre IN
Environnement Environnement
Paramètre OUT
appelant appelant
Paramètre IN OUT
(DECLARE) (DECLARE)

BEGIN BEGIN

EXCEPTION EXCEPTION

END; END;
Comparer les procédures et les fonctions

Procédures Fonctions
S'exécutent en tant Sont appelées dans une
qu'instruction PL/SQL expression
Ne contiennent pas de Doivent contenir une clause
clause RETURN dans l'en-tête RETURN dans l'en-tête
Peuvent transférer zéro, une Doivent renvoyer une seule
ou plusieurs valeurs valeur
Peuvent contenir une Doivent contenir au moins
instruction RETURN une instruction RETURN
Avantages liés aux procédures et aux
fonctions stockées

 Performances améliorées
 Facilité de maintenance
 Sécurité et intégrité accrues des données
 Clarté améliorée du code
Triggers PL/SQL

PL
 Types de déclencheur

Un déclencheur :
 est une procédure ou un bloc PL/SQL associé à la base de
données, à une table, à une vue ou à un schéma
 s'exécute de façon implicite lorsqu'un événement donné se
produit
il peut s'agir d'un :
 déclencheur applicatif, qui s'exécute lorsqu'un événement se produit dans une
application donnée
 déclencheur de base de données, qui s'exécute lorsqu'un événement de type
données (LMD) ou système (connexion ou arrêt) se produit dans un schéma ou
une base de données
 Règles relatives à la conception de
déclencheurs
• Il est conseillé de concevoir des déclencheurs pour :
exécuter des actions associées
centraliser des opérations globales

• Si le code PL/SQL est très long, créer des procédures stockées et les
appeler dans un déclencheur

• L'utilisation excessive de déclencheurs peut entraîner des

interdépendances complexes dont la gestion peut s'avérer difficile
dans les applications volumineuses
Exemple de déclencheur de base de données

Application

INSERT INTO EMPLOYEES

. . .;

Table EMPLOYEES Déclencheur CHECK_SAL

…
 Créer des déclencheurs LMD

Une instruction de déclenchement comporte les

éléments suivants :
1. moment du déclenchement
pour une table : BEFORE, AFTER
pour une vue : INSTEAD OF
2. événement déclencheur : INSERT, UPDATE ou DELETE
3. nom de la table : sur la table ou la vue
4. type de déclencheur : ligne ou instruction
5. clause WHEN : condition restrictive par ligne
6. corps du déclencheur : bloc PL/SQL
 Composants des déclencheurs LMD

Moment du déclenchement : à quel moment le

déclencheur doit-il s'exécuter ?
BEFORE : exécution du corps du déclencheur avant le déclenchement de
l'événement LMD sur une table
AFTER : exécution du corps du déclencheur après le déclenchement de
l'événement LMD sur une table
INSTEAD OF : exécution du corps du déclencheur au lieu de l'instruction
de déclenchement. Ce déclencheur est utilisé pour les vues qui ne
peuvent pas être modifiées autrement
 Composants des déclencheurs LMD

Evénement utilisateur déclencheur : quelle instruction LMD

entraîne l'exécution du déclencheur ?

Vous pouvez utiliser les instructions

suivantes :
INSERT
UPDATE
DELETE
 Composants des déclencheurs LMD (Type)

Type de déclencheur : le corps du déclencheur doit-il

s'exécuter une seule fois ou pour chaque ligne
concernée par l'instruction ?
Instruction : le corps du déclencheur s'exécute une seule fois pour
l'événement déclencheur. Il s'agit du comportement par défaut. Un
déclencheur sur instruction s'exécute une fois, même si aucune ligne
n'est affectée
Ligne : le corps du déclencheur s'exécute une fois pour chaque ligne
concernée par l'événement déclencheur. Un déclencheur sur ligne ne
s'exécute pas si l'événement déclencheur n'affecte aucune ligne
Composants des déclencheurs LMD(Corps)

Corps du déclencheur : quelle action le déclencheur

doit-il effectuer ?
Le corps du déclencheur est un bloc PL/SQL ou un
appel de procédure (PL/SQL ou Java).

RQ
Les déclencheurs sur ligne utilisent des noms de corrélation pour accéder aux
anciennes ou nouvelles valeurs de colonne de la ligne en cours de traitement.
La taille d'un déclencheur est limitée à 32 Ko.
Séquence d'exécution

Lorsque la manipulation concerne une seule ligne, utilisez la

séquence d'exécution suivante pour un déclencheur sur une table :
Instruction LMD
INSERT INTO departments (department_id,
department_name, location_id)
VALUES (400, 'CONSULTING', 2400);

Action de déclenchement
Déclencheur sur
instruction BEFORE

…
Déclencheur sur ligne BEFORE
Déclencheur sur ligne AFTER
Déclencheur sur instruction
AFTER
Séquence d'exécution

Lorsque la manipulation concerne plusieurs lignes, utilisez la

séquence d'exécution suivante pour un déclencheur sur une table :
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 30;

Déclencheur sur instruction BEFORE

Déclencheur sur ligne BEFORE

Déclencheur sur ligne AFTER

...
Déclencheur sur ligne BEFORE
Déclencheur sur ligne AFTER
...
Déclencheur sur instruction AFTER
Syntaxe pour la création de déclencheurs sur instruction LMD

Syntaxe :

CREATE [OR REPLACE] TRIGGER trigger_name

timing - - BEFORE ou AFTER
event1 [OR event2 OR event3] - - INSERT, UPDATE, DELETE
ON table_name
trigger_body

RQ: Les noms des déclencheurs doivent être uniques

au sein d'un même schéma
Créer des déclencheurs sur instruction LMD

Exemple :
CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT ON employees
BEGIN
IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR
(TO_CHAR(SYSDATE,'HH24:MI')
NOT BETWEEN '08:00' AND '18:00')
THEN RAISE_APPLICATION_ERROR (-20500,'You may
insert into EMPLOYEES table only
during business hours.');
END IF;
END;
/

RQ : En cas d'échec d'un déclencheur de base de données, l'instruction de

déclenchement est annulée.
Tester SECURE_EMP

INSERT INTO employees (employee_id, last_name,

first_name, email, hire_date,
job_id, salary, department_id)
VALUES (502, ‘Adil', ‘KAMAL', ‘kemail', SYSDATE,
'IT_PROG', 4500, 60);
 Utiliser des prédicats conditionnels
CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT OR UPDATE OR DELETE ON employees
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,'You may delete from
EMPLOYEES table only during business hours.');
ELSIF INSERTING THEN
RAISE_APPLICATION_ERROR (-20500,'You may insert into
EMPLOYEES table only during business hours.');
ELSIF UPDATING ('SALARY') THEN
RAISE_APPLICATION_ERROR (-20503,'You may update
SALARY only during business hours.');
ELSE
RAISE_APPLICATION_ERROR (-20504,'You may update
EMPLOYEES table only during normal hours.');
END IF;
END IF;
END;
Créer un déclencheur sur ligne LMD
Syntaxe :
CREATE [OR REPLACE] TRIGGER trigger_name
timing
event1 [OR event2 OR event3]
ON table_name
[REFERENCING OLD AS old | NEW AS new]
FOR EACH ROW
[WHEN (condition)]
trigger_body
Créer des déclencheurs sur ligne LMD

CREATE OR REPLACE TRIGGER restrict_salary

BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary > 15000
THEN
RAISE_APPLICATION_ERROR (-20202,'Employee
cannot earn this amount');
END IF;
END;

UPDATE employees
SET salary = 15500
WHERE last_name = 'Russell';
Utiliser les qualificatifs OLD et NEW

CREATE OR REPLACE TRIGGER audit_emp_values

AFTER DELETE OR INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO audit_emp_table (user_name, timestamp,
id, old_last_name, new_last_name, old_title,
new_title, old_salary, new_salary)
VALUES (USER, SYSDATE, :OLD.employee_id,
:OLD.last_name, :NEW.last_name, :OLD.job_id,
:NEW.job_id, :OLD.salary, :NEW.salary );
END;
/
Utiliser les qualificatifs OLD et NEW : exemple de la table
Audit_Emp_Table

INSERT INTO employees

(employee_id, last_name,email,hire_date,job_id, salary)
VALUES
(777,'Temp
emp7','[email protected]',SYSDATE,'SA_REP',1000);

UPDATE employees
SET salary = 5000, last_name = 'Smith'
WHERE employee_id = 999;

SELECT user_name, timestamp, ... FROM audit_emp_table

Restreindre l'action d'un déclencheur sur ligne

CREATE OR REPLACE TRIGGER derive_commission_pct

BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.job_id = 'SA_REP')
BEGIN
IF INSERTING
THEN :NEW.commission_pct := 0;
ELSIF :OLD.commission_pct IS NULL
THEN :NEW.commission_pct := 0;
ELSE
:NEW.commission_pct := :OLD.commission_pct + 0.05;
END IF;
END;
/
Déclencheurs INSTEAD OF

Application

INSERT INTO my_view

. . .;

INSERT
TABLE1
Déclencheur
INSTEAD OF

UPDATE
MY_VIEW TABLE2
Créer un déclencheur INSTEAD OF

Syntaxe :
CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF
event1 [OR event2 OR event3]
ON view_name
[REFERENCING OLD AS old | NEW AS new]
[FOR EACH ROW]
trigger_body

RQ : Les déclencheurs INSTEAD OF ne peuvent être écrits que pour des vues. Les options
BEFORE et AFTER ne sont pas valides.
Créer un déclencheur INSTEAD OF Exemple

CREATE TABLE new_emps AS

SELECT employee_id, last_name,
salary, department_id,
email, job_id, hire_date
FROM employees;

CREATE TABLE new_depts AS

SELECT d.department_id, d.department_name,
d.location_id, sum(e.salary) tot_dept_sal
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_id, d.department_name,
d.location_id;

CREATE VIEW emp_details AS

SELECT e.employee_id, e.last_name, e.salary,
e.department_id, e.email, e.job_id,
d.department_name, d.location_id
FROM employees e, departments d
WHERE e.department_id = d.department_id;
Créer un déclencheur INSTEAD OF (suite)

CREATE OR REPLACE TRIGGER new_emp_dept

INSTEAD OF INSERT ON emp_details
FOR EACH ROW
BEGIN

INSERT INTO new_emps

VALUES (:NEW.employee_id, :NEW.last_name,
:NEW.salary, :NEW.department_id,
:NEW.email, :New.job_id, SYSDATE);
UPDATE new_depts
SET tot_dept_sal = tot_dept_sal + :NEW.salary
WHERE department_id = :NEW.department_id;

END;
 Créer un déclencheur INSTEAD OF

Exécution d'une instruction INSERT dans la vue EMP_DETAILS

basée sur les tables EMPLOYEES et DEPARTMENTS

1 INSERT INTO emp_details

(employee_id, last_name, salary,department_id,email,
job_id,department_name,location_id)
VALUES
(9002,'ABBOTT',3000,10,'abbott.mail.com',
'HR_MAN‘,'Finance',1700);

Opération INSERT d'un

déclencheur INSTEAD …
OF dans EMP_DETAILS
 Créer un déclencheur INSTEAD OF

Exécution d'une instruction INSERT dans la vue EMP_DETAILS

basée sur les tables EMPLOYEES et DEPARTMENTS

INSERT INTO emp_details(employee_id, ... )

1 VALUES(9001,'ABBOTT',3000,10,'abbott.mail.com','HR_MAN');

Opération INSERT d'un

déclencheur INSTEAD
OF dans EMP_DETAILS
…

INSERT dans UPDATE

2 3
NEW_EMPS NEW_DEPTS

…
…
 Différences entre les déclencheurs de base de données et les
procédures stockées

Déclencheurs Procédures

Définis via la commande CREATE Définis via la commande

TRIGGER CREATE PROCEDURE
Le dictionnaire de données Le dictionnaire de données contient
contient le code source dans le code source dans
USER_TRIGGERS USER_SOURCE
Appel implicite Appel explicite
Les instructions COMMIT, Les instructions COMMIT,
SAVEPOINT et ROLLBACK ne sont SAVEPOINT et ROLLBACK sont
pas autorisées autorisées
Différences entre les déclencheurs de base de données et les
déclencheurs Form Builder

INSERT INTO EMPLOYEES

. . .;

Table EMPLOYEES Déclencheur CHECK_SAL

BEFORE
…
INSERT
ligne
 Gérer les déclencheurs

Désactiver ou réactiver un déclencheur de base de données :

ALTER TRIGGER trigger_name DISABLE | ENABLE

Désactiver ou réactiver tous les déclencheurs d'une table :

ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS

Recompiler un déclencheur pour une table :

ALTER TRIGGER trigger_name COMPILE
Syntaxe DROP TRIGGER

Pour supprimer un déclencheur de la base de

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

DROP TRIGGER secure_emp;

Remarque : Lorsqu'une table est supprimée, tous ses

déclencheurs sont également supprimés
 Tests des déclencheurs

 Tester toutes les opérations sur les données qui provoquent

un déclenchement, ainsi que celles n'en produisent pas
 Tester chaque cas de la clause WHEN
 Provoquer une exécution directe du déclencheur via une
opération de base sur les données, et une exécution
indirecte via une procédure
 Tester l'impact du déclencheur sur les autres déclencheurs
 Tester l'impact des autres déclencheurs sur le déclencheur
Exemple de modèle d'exécution des
déclencheurs et de vérification des contraintes

UPDATE employees SET department_id = 999

WHERE employee_id = 170;
-- Integrity constraint violation error
CREATE OR REPLACE TRIGGER constr_emp_trig
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO departments
VALUES (999, 'dept999', 140, 2400);
END;
/

UPDATE employees SET department_id = 999

WHERE employee_id = 170;
-- Successful after trigger is fired