Chapitre 1 : Langage PL/SQL

Dr Konan Marcellin BROU

[email protected]

2O18-2019
 Sommaire
 Introduction
 Instructions de base
 Curseurs
 Tables PL/SQL
 Exceptions
 Sous-programmes stockés
 Paquetages
 Triggers
 Communication entre BD
 Bibliographie

K. M. BROU Langage PL/SQL 2

I. Introduction
 1.1. Présentation
 SQL
 Structured Query Language
 Langage ensembliste non procédurale
 Intègres plusieurs niveaux de langage :
 Interrogation de Données (LID) : SELECT
 Manipulation de Données (LMD) : UPDATE, INSERT, DELETE
 Définition des Données (LDD) : ALTER, CREATE, DROP
 Contrôle des Données et des utilisateurs (LCD) : GRANT, REVOKE
 Inconvénient
 Impossible de réaliser des traitements algorithmiques
 Pas de structures de contrôle, de fonctions personnelles…

K. M. BROU Langage PL/SQL 3

I. Introduction
 PL/SQL
 Procedural Language/Structured Query Language
 Langage procédural d'Oracle
 Intègre des ordres SQL
 Permet de réaliser des traitements algorithmiques
 Langage structuré en blocs, constitués d'un ensemble
d'instructions.

K. M. BROU Langage PL/SQL 4

I. Introduction
 1.2. Connexion et création d’utilisateur
 Connexion
 SQL> Login : system
 SQL> Password : brou
 Création d’un user
 SQL> CREATE USER fatou identified by fatou;
 SQL> GRANT DBA TO fatou;
 Connexion avec le compte de fatou
 SQL> CONNECT fatou/fatou;
 Visualiser les utilisateurs
 SQL> SHOW user --utilisateur connectés
 SQL> SELECT * FROM USER_USERS; --utilisateur connectés
 SQL> SELECT * FROM ALL_USERS; --liste de tous les
utilisateurs

K. M. BROU Langage PL/SQL 5

I. Introduction
 1.3. Mot de passe oublié
 Processus
 C:\> sqlplus /nolog

 SQL> connect / as sysdba ou

 SQL> connect /@BDORACLE as sysdba
 SQL> alter user nomUser identified by motDePasseUser;
 Exemple : SQL> alter user brou identified by broukm;

K. M. BROU Langage PL/SQL 6

I. Introduction
 Inconvénient
 Faille de la sécurité Oracle
 Impossible d'empêcher à un administrateur du système
d'exploitation de se connecter en tant qu’admnistrateur de la BD.
 Administrateur Windows ou Unix peut se connecter sans mot de
passe.
 C:\> sqlplus /nolog
 SQL> connect /as sysdba
 Avantage
 Départ inopiné ou décès du DBA.

K. M. BROU Langage PL/SQL 7

II. Instructions de base
 2.1. Structure d’un programme PL/SQL
 Composée de plusieurs blocs
-- Bloc de commandes SQLPLUS
[______ ;
______ ;]
--Début du Bloc PL/SQL
DECLARE
--Bloc de déclaration de variables et de curseurs
______ ;
______ ;
BEGIN
-- Bloc d'instructions
______ ;
______ ;
[EXCEPTION
Bloc de traitement des erreurs]
END; -- fin du bloc pl/sql
/ -- exécution du bloc PL/SQL, pas d’espace en début de ligne
-- autres commandes SQL pour sortir les résultats

K. M. BROU Langage PL/SQL 8

II. Instructions de base
 Fonctionnement de PL/SQL
 Bloc PL/SQL peut être :
 Externe : on dit alors qu'il est anonyme,
 Interne : stocké dans la BD sous forme de procédure, fonction ou
trigger.
 Moteur PL/SQL
 Traite chaque instruction PL/SQL et sous-traite les instructions
purement SQL au moteur SQL, afin de réduire le trafic réseau.

K. M. BROU Langage PL/SQL 9

II. Instructions de base
 2.2. BD exemple
 Schéma entité/Association
Produit
Client
0, n passe 1, 1 Commande 1, n LigneCom 0, n numProd
numCl designation
nom numCom qteCom
dateCom prixU
adresse qteStock
tel
Fax
email

 Schéma relationnel
 Client(numCl, nom, adresse, tel, fax, email)
 Commande(numCom, dateCom, #numCl)
 Produit(numProd, designation, prixUnit, qteStock)
 LigneCom(#numCom, #numProd, qteCom)

K. M. BROU Langage PL/SQL 10

II. Instructions de base
 Structure des tables
Tables Champ Type Taille
numCl NUMBER 5
nom VARCHAR2 15 caractères
adresse VARCHAR2 20 caractères
Client
tel NUMBER 10
fax NUMBER 10
email VARCHAR2 20 caractères
numCom NUMBER 5
Commande dateCom Date
numCl NUMBER 5
numProd NUMBER 5
designation VARCHAR2 12 caractères
Produit
prixUnit NUMBER 7,2
qteStock NUMBER 5
numCom NUMBER 5
LigneCom numProd NUMBER 5
qteCom NUMBER 9
 Script :
 /Ch3InstructionBase/ScriptBDscriptGSF.sql

K. M. BROU Langage PL/SQL 11

II. Instructions de base
 Exécution d’un script
 SQL> @C:\...\scriptGSk.sql
 @ ou START C:\...\scriptGSk.sql
 Visualiser les objets crées
 SQL> SELECT * FROM USER_CATALOG;
 SQL> SELECT OBJECT_NAME FROM USER_OBJECTS;
 Commande HOST
 Permet d’exécuter une commande de l’O.S.
 Syntaxe
 SQL> HOST [commande]
 Exemple : Effacer l’écran
 SQL> HOST CLS

K. M. BROU Langage PL/SQL 12

II. Instructions de base
 2.3. Exemple de programme PL/SQL
 Mise à jour de la quantité en stock d’un Produit donné
PROMPT nom du produit désiré
ACCEPT nomP 1. Saisir le code ci-dessus dans un
PROMPT quantité commandée (majQteStock.sql)
ACCEPT qtc 2. A l’invite de SQL*Plus, taper :
DECLARE @c:\…\majQteStock.sql et valider
3. Saisir le nom du produit et la qté
quantite NUMBER(5);
4. corriger les erreurs éventuelles et
BEGIN retourner à l’étape 2 jusqu’à ce
SELECT qteStock INTO quantite qu’il n’y ait plus d’erreurs.
FROM Produit WHERE designation = '&nomP'; 5. Vérifier que la mise à jour a été
--Contrôle et mise à jour du stock faite en saisissant la requête :
SELECT * FROM Produit ;
IF quantite > 0 THEN
UPDATE Produit SET qteStock=qteStock - &qtc
WHERE designation = '&nomP' ;
END IF ;
COMMIT;
END;
/
K. M. BROU Langage PL/SQL 13
II. Instructions de base
 Exécution

 ancien : instruction contenant un variable PLSQL

 nouveau : instruction où la variable PLSQL à été instanciée

K. M. BROU Langage PL/SQL 14

II. Instructions de base
 2.4. Commentaire
 --Texte du commentaire

K. M. BROU Langage PL/SQL 15

II. Instructions de base
 2.5. Types de donnée
 Types prédéfinis

Type de données Plage de valeurs

NUMBER(n) Réels de 1.0E-129 à 9.999E125
NUMBER(n, p) Réels de 1.0E-129 à 9.999E125
BINARY_INTEGER Entiers signés de -231-1 à 231-1
POSITIVE Entiers de 1 à 231
INT NUMBER(38)
INTEGER NUMBER(38)
VARCHAR(n) Chaîne de longueur variable de 1 à 32767 caractères
VARCHAR2(n) VARCHAR
BOOLEAN TRUE, FALSE ou NULL
DATE Format interne sur 7 octets

K. M. BROU Langage PL/SQL 16

II. Instructions de base
 Types définis par l'utilisateur
 Définition de sous-types
SUBTYPE TAge IS POSITIVE ;

age TAge;

 Définition d’enregistrement

TYPE Eleve IS RECORD

(nom VARCHAR2(20),
Age Tage
);

e Eleve ;
e.nom = 'Toto' ;

K. M. BROU Langage PL/SQL 17

II. Instructions de base
 Exemple : client.sql
 Afficher le nom et le tel d’un client de numéro donné.
 Initialiser le mode sortie écran : SET SERVEROUTPUT ON

SET SERVEROUTPUT ON
PROMPT numéro du client
ACCEPT num
DECLARE
TYPE TClient IS RECORD
(rNom VARCHAR2(20),
rTel NUMBER(10)
);
cl TClient;
BEGIN
SELECT nom, tel INTO cl
FROM Client
WHERE numCl = #
DBMS_OUTPUT.PUT_LINE('Nom : ' || cl.rnom);
DBMS_OUTPUT.PUT_LINE('Tel : ' || cl.rtel);
END;
/

K. M. BROU Langage PL/SQL 18

II. Instructions de base
 2.6. Déclaration de variables
 Variables locales :
 Variables de type prédéfinis
 quantite NUMBER(5) ;
 dateJour DATE ;
 Variables faisant référence au dictionnaire de données
 quantite Produit.qteStock%TYPE ;
 enreg Produit%ROWTYPE ;
 Variables de l’environnement extérieur à PL/SQL
 Champs d’écran en SQL*Forms
 Variables définies en langage hôte dans PRO* préfixé de ":"
 Variable définies dans SQL*Plus préfixées de "&"
 Initialisation de variables
 quantite NUMBER(5) := 0 ;
 nom CHAR(15) := 'Toto' ;
 reponse BOOLEAN := TRUE ;

K. M. BROU Langage PL/SQL 19

II. Instructions de base
 2.7. Déclaration de constantes
 Pi CONSTANT NUMBER(7, 2) := 2.14 ;

K. M. BROU Langage PL/SQL 20

II. Instructions de base
 2.8. L’ordre SELECT (select.sql)
DECLARE
telCl Client.tel%TYPE ;
nomCl Client.nom%TYPE ;
BEGIN
SELECT tel, nom INTO telCl, nomCl
FROM Client
WHERE numCl=1;
DBMS_OUTPUT.PUT_LINE('Nom : ' || nomCl);
DBMS_OUTPUT.PUT_LINE('Tel : ' || telCl);
END ;
/

K. M. BROU Langage PL/SQL 21

II. Instructions de base
 2.9. Corps du bloc PL/SQL
 Contient les instructions exécutables
 Délimité par BEGIN et END;
 Affectation
 nomVariable := expression ;

K. M. BROU Langage PL/SQL 22

II. Instructions de base
 Structure alternative
IF condition1 THEN
Traitement1 ;
[ELSE IF condition2 THEN
Traitement2 ;
…
ELSE
Traitementn ; ]
END IF ;

 Opérateurs utilisés dans les conditions :

Opérateur Explication
=, <, <=, >, >=, !=, Relationnels

IS NULL, IS NOT NULL attribut IS NULL, attribut IS NOT NULL,

BETWEEN attribut BETWEEN valeur1 and valeur2

LIKE, NOT LIKE attribut LIKE 'ko', attribut NOT LIKE 'ko'
AND, OR Attribut < valeur1 AND Attribut >= valeur1

K. M. BROU Langage PL/SQL 23

II. Instructions de base
 Exemple : alternative.sql
SET SERVEROUTPUT ON
DECLARE
quantite NUMBER(5);
BEGIN
SELECT qteStock INTO quantite
FROM Produit
WHERE designation = '&nomProd';
--Contrôle et mise à jour du stock
IF quantite > 0 THEN
UPDATE Produit
SET qteStock = qteStock - &qtc
WHERE designation = '&nomProd' ;
ELSE
DBMS_OUTPUT.PUT_LINE('Quantité en stock négative');
END IF ;
COMMIT;
END;
/

K. M. BROU Langage PL/SQL 24

II. Instructions de base
 Structures itératives ou boucles
 Trois sortes de boucle : Boucle simple ou infinie, Boucle FOR et
Boucle WHILE
 Boucle simple ou infinie : boucleSimpleEx1.sql, 2, 3
 create table resultat(n NUMBER);

DECLARE DECLARE SET SERVEROUTPUT ON

n NUMBER := 1 ; n NUMBER := 1 ; DECLARE
BEGIN BEGIN n NUMBER := 1 ;
LOOP LOOP BEGIN
INSERT INTO resultat INSERT INTO resultat LOOP
VALUES(n) ; VALUES(n) ; DBMS_OUTPUT.PUT_LINE(n) ;
n := n + 1 ; n := n + 1 ; n := n + 1 ;
IF n > 10 THEN EXIT WHEN n > 10 ; EXIT WHEN n > 10 ;
EXIT; END LOOP ; END LOOP ;
END IF ; END ; END ;
END LOOP ; / /
END ;
/
K. M. BROU Langage PL/SQL 25
II. Instructions de base
 Boucle FOR (nombre) : n!
 boucleForEx1.sql, boucleForEx2.sql

DECLARE DECLARE
N NUMBER := 10 ; N NUMBER := 10 ;
fact NUMBER := 1 ; fact NUMBER := 1 ;
BEGIN BEGIN
FOR i IN 1..N FOR i IN REVERSE 1..N
LOOP LOOP
fact := fact * i ; fact := fact * i ;
END LOOP ; END LOOP ;
DBMS_OUTPUT.PUT_LINE(N || '!=' || DBMS_OUTPUT.PUT_LINE(N || '!=' ||
fact) ; fact) ;
END ; END ;
/ /

K. M. BROU Langage PL/SQL 26

II. Instructions de base
 Boucle while : Quotient et reste de deux entiers positifs
 boucleWhileEx1.sql

DECLARE
a NUMBER := 13;
b NUMBER := 5;
q NUMBER := 0 ;
r NUMBER;
BEGIN
WHILE a > b
LOOP
q := q + 1;
a := a - b ;
END LOOP ;
r := a;
DBMS_OUTPUT.PUT_LINE('Quotient = ' || q) ;
DBMS_OUTPUT.PUT_LINE('Reste = ' || r) ;
END ;
/

K. M. BROU Langage PL/SQL 27

III. Les curseurs
 3.1. Définition
 Zone de mémoire de taille fixe qui contient des
informations utilisées par Oracle pour analyser et
interpréter tout ordre SQL :
 Texte source de l’ordre SQL ;
 Forme traduite de l’ordre ;
 Tampon correspondant à une ligne résultat ;
 Statut du curseur ;
 Information de travail ;
 Information de contrôle.
 Deux sortes de curseurs :
 Curseurs implicites : fabriqués par le compilateur lors de
chaque ordre SQL.
 Curseurs explicites : définis par le programmeur.

K. M. BROU Langage PL/SQL 28

III. Les curseurs
 3.2. Curseurs implicites
 Gérés automatiquement par le noyau
 Cas d’utilisation :
 Ordres SELECT exécutés sous SQL*Plus
 Ordres SELECT donnant une seule ligne résultat avec les
autres produits (PL/SQL, SQL*FORMS, PRO, etc.)
 Ordres UPDATE, INSERT et DELETE

K. M. BROU Langage PL/SQL 29

III. Les curseurs
 3.3. Curseurs explicites
 Décrits et gérés dans le programme par le développeur.
 Utilisés avec un ordre SELECT susceptible de produire
plusieurs tuples résultats.
 Erreur si on n’utilise pas de curseur et l’ordre SELECT
ramène plusieurs lignes.
 Exemple : curseurEx0.txt

K. M. BROU Langage PL/SQL 30

III. Les curseurs
 Exemple : curseurEx0.txt

SET SERVEROUTPUT ON
DECLARE
cnom Client.nom%TYPE ;
ctel Client.tel%TYPE ;
BEGIN
SELECT nom, tel INTO cnom, ctel
FROM Client ;
DBMS_OUTPUT.PUT_LINE(cnom || ' ' || ctel) ;
END ;
/

K. M. BROU Langage PL/SQL 31

III. Les curseurs
 Principe d’utilisation d’un curseur explicite :
1. Déclaration du curseur
2. Ouverture du curseur
3. Traitement des lignes
4. Fermeture du curseur
 Utilisé explicitement avec les ordres :
 OPEN, FETCH et CLOSE
 Utilisé dans une boucle infinie
 Sortie quand l’attribut NOTFOUND du curseur est vrai
 Attributs d’un curseur :
 Permettent de récupérer des informations sur un curseur

Attribut Explication
%FOUND Retourne TRUE lorsque une ligne est ramenée, sinon FALSE
%NOTFOUND Retourne FALSE lorsque une ligne est ramenée, TRUE
%ISOPEN Retourne TRUE lorsque le curseur indiqué est ouvert, sinon il FALSE
%ROWCOUNT retourne le nombre de lignes impactées par la dernière instruction SQL

K. M. BROU Langage PL/SQL 32

III. Les curseurs
 Curseur utilisant open, fetch, close :
 Ligne courante d’un curseur est déplacée à chaque appel de
l’instruction fetch.
 Exemple 1 : curseurEx1.txt

DECLARE LOOP
CURSOR C3 IS FETCH C3 INTO cnom, ctel ;
SELECT nom, tel FROM Client ; EXIT WHEN (C3%NOTFOUND) ;
cnom Client.nom%TYPE ; --Traitement ;
ctel Client.tel%TYPE ; DBMS_OUTPUT.PUT_LINE(cnom || ' ' || ctel ) ;
BEGIN END LOOP ;
OPEN C3 ; CLOSE C3 ;
END ;
/

K. M. BROU Langage PL/SQL 33

III. Les curseurs
 Exemple 2 : utilisation d’un enregistrement
 curseurEx2.txt

DECLARE LOOP
TYPE enreg IS RECORD FETCH C3 INTO e ;
(cnom Client.nom%TYPE , EXIT WHEN (C3%NOTFOUND) ;
ctel Client.tel%TYPE ) ; --Traitement ;
CURSOR C3 IS DBMS_OUTPUT.PUT_LINE(e.cnom || ' ' || e.ctel ) ;
SELECT nom, tel FROM END LOOP ;
Client ; CLOSE C3 ;
e enreg; END ;
BEGIN /
OPEN C3 ;

K. M. BROU Langage PL/SQL 34

III. Les curseurs
 Boucle FOR de type curseur (curseurEx3.txt)
 Évite d’utiliser explicitement les instructions : open, fetch,
close.
 Déclare implicitement une variable de type "row" liée au
curseur. DECLARE
CURSOR C3 IS
SELECT nom, tel FROM Client ;
cnom Client.nom%TYPE ;
ctel Client.tel%TYPE ;
BEGIN
FOR recC3 IN C3
LOOP
--Traitement ;
cnom :=recC3.nom ;
ctel := recC3.tel;
DBMS_OUTPUT.PUT_LINE(cnom || ' ' || ctel) ;
END LOOP ;
END ;
/
K. M. BROU Langage PL/SQL 35
III. Les curseurs
 Curseur paramétré
 Peut servir plusieurs fois avec des valeurs des paramètres
différentes.
 Fermer le curseur entre chaque utilisation de paramètres
différents.
 Sauf si on utilise "for" qui ferme automatiquement le curseur
 Exemples :

K. M. BROU Langage PL/SQL 36

III. Les curseurs
 Exemple 1 : open, fetch, close (curseurEx4.txt)
DECLARE
CURSOR C3(numCl_in IN NUMBER, adr_in IN Client.adresse%TYPE) IS
SELECT nom, tel
FROM Client
WHERE (numCl > numCl_in) AND (adresse LIKE adr_in);
cnom Client.nom%TYPE ;
ctel Client.tel%TYPE ;
BEGIN
OPEN C3(1, 'BP%') ;
LOOP
FETCH C3 INTO cnom, ctel ;
EXIT WHEN (C3%NOTFOUND) ;
--Traitement ;
DBMS_OUTPUT.PUT_LINE(cnom || ' ' || ctel) ;
END LOOP ;
CLOSE C3 ;
END ;
/
K. M. BROU Langage PL/SQL 37
III. Les curseurs
 Exemple 2 : boucle for (curseurEx5.txt)
SET SERVEROUTPUT ON
DECLARE
CURSOR C3(numCl_in IN NUMBER, adr_in IN Client.adresse%TYPE) IS
SELECT nom, tel
FROM Client
WHERE (numCl > numCl_in) AND (adresse LIKE adr_in);
cnom Client.nom%TYPE;
ctel Client.tel%TYPE;
BEGIN
FOR recC3 IN C3(1, 'BP%')
LOOP
--Traitement ;
cnom :=recC3.nom ;
ctel :=recC3.tel;
DBMS_OUTPUT.PUT_LINE(cnom || ' ' || ctel) ;
END LOOP ;
END ;
/
K. M. BROU Langage PL/SQL 38
III. Les curseurs
 Curseur avec la clause RETURN : curseurEx6.txt
 Le type de retour est imposé
DECLARE
CURSOR C3(numCl_in IN NUMBER, adr_in IN Client.adresse%TYPE) RETURN
Client%ROWTYPE IS
SELECT * FROM Client WHERE (numCl > numCl_in) AND (adresse LIKE adr_in);
e Client%ROWTYPE ;
BEGIN
OPEN C3(1, 'BP%') ;
LOOP
FETCH C3 INTO e;
EXIT WHEN (C3%NOTFOUND) ;
--Traitement ;
DBMS_OUTPUT.PUT_LINE(e.nom || ' ' || e.tel) ;
END LOOP ;
CLOSE C3 ;
Erreur : FETCH C3 INTO cnom, ctel;
END ; Correct : FETCH C3 INTO e;
/
K. M. BROU Langage PL/SQL 39
III. Les curseurs
 Exercice 1 :
 Exo1.1 : Ecrire une requête SQL permettant de calculer le
montant de la commande numéro 1 du client de numéro 1.
 Exo1.2 : Ecrire un programme PL/SQL permettant de calculer
le montant de la commande numéro 1 du client de numéro 1
en utilisant la fonction sum. On appliquera une TVA de 11% si
le montant dépasse 1000F.
 Exo1.3 : Ecrire un programme PL/SQL permettant de calculer
le montant de la commande numéro 1 du client de numéro 1
sans utiliser la fonction sum. on appliquera une TVA de 11% si
le montant dépasse 1000F.
 Exo1.4 : Idem Exo1.3 mais en utilisant un curseur paramétré.
Calculer dans le même programme les montants suivants :

numCom numCl
1 1
2 1

K. M. BROU Langage PL/SQL 40

III. Les curseurs
 Exercice 2 : Détail des produits
 Exo2.1 : Créer une table facture ayant la structure suivante :
Champ Type Explication
numProd NUMBER Numéro du produit
designation VARCHAR2(10) Désignation du produit
qteCom NUMBER Quantité commandée
prixUnit NUMBER Prix unitaire
montant NUMBER(6,2) Montant de la facture

Créer un programme PL/SQL permettant de remplir la table

Facture avec la commande 1 du client 1.

 Exo2.1 : Créer un programme PL/SQL permettant de remplir

la table Facture avec les commandes des clients. Chaque
commande d’un client donne lieu à une facture.
K. M. BROU Langage PL/SQL 41
IV. Les tables PL/SQL
 4.1. Définition
 Vecteurs non borné et inconsistant d'éléments
homogènes indexés par nombres entiers.
 Données composites comme les enregistrements.
 Différentes des tables SQL
 Cependant elles en reprennent quelques aspects.

K. M. BROU Langage PL/SQL 42

IV. Les tables PL/SQL
 4.2. Caractéristiques
 Une table PL/SQL ne peut avoir qu'une colonne.
 Identique à un tableau à une seule dimension (vecteur).
 Aucune limite au nombre de lignes
 Etendue dynamiquement suivant les besoins (non borné).
 Aucune ligne n'est allouée lors de sa déclaration.
 Une ligne n'existe que si elle est allouée.
 Si on alloue les cases 5 et 8, les cases 6 et 7 n'existe que si
elles ont été allouées (inconsistant).
 Les numéros de lignes n'ont pas besoin d’être séquentiels
 Plage : -231-1 à 231-1 (indexés par nombre entiers)

K. M. BROU Langage PL/SQL 43

IV. Les tables PL/SQL
 4.3. Tables PL/SQL et commandes LMD
 Moteur PL/SQL
 Lorsque le moteur PL/SQL exécute une commande LMD, il
passe la main au moteur SQL.
 Le code doit donc être conforme au SQL.
 La structure de données TABLE ne fait pas partie de manière
évidente de SQL.

K. M. BROU Langage PL/SQL 44

IV. Les tables PL/SQL
 Restrictions
 Concept d'intégrité des transactions
 N'existe pas avec les tables PL/SQL
 Pas possible d’effectuer de COMMIT ou de ROLLBACK
 Pas d’utilisation de SELECT
 Utiliser une boucle pour extraire l'ensembles de valeurs d'une
table PL/SQL
 Pas d’utilisation de commandes DML sur les tables PL/SQL

K. M. BROU Langage PL/SQL 45

IV. Les tables PL/SQL
 4.4. Syntaxe
 Déclaration
 TYPE <nom de type de la table> IS TABLE OF <typetable>
[NOT NULL] INDEX BY BINARY_INTEGER;
 Utilisation
 <nom de la table> <nom de type de la table>;
 Exemple
DECLARE
TYPE Vecteur IS TABLE OF NUMBER(5) INDEX BY BINARY_INTEGER;
v Vecteur;
BEGIN
…
END;

K. M. BROU Langage PL/SQL 46

IV. Les tables PL/SQL
 4.5. Accès aux lignes d'une table PL/SQL
 Une ligne d'une table est une valeur scalaire.
 Syntaxe
 <nom de la table> (<valeur de la cle primaire>)
 Exemple :
 v(1) := 5;
 Remarques :
 Référencer une ligne non définie déclenche l'exception
NO_DATA_FOUND.
 Exemple :
 v(1) := v(3);
 erreur car v(3) n’a pas été encore initialisé
 Les tables PL/SQL peuvent être passées en paramètres d’une
fonction.
 Lecture de toute la table
 Lecture en mode non séquentiel.

K. M. BROU Langage PL/SQL 47

IV. Les tables PL/SQL
 4.6. Fonctions de manipulation
Fonction Description
nomTable.EXISTS(x) Retourne TRUE si le xe élément de la collection existe
nomTable.COUNT() Retourne le nombre d’élément de la collection
nomTable.LIMIT() Retourne le nombre maximum d’éléments
nomTable.FIRST() Retourne le premier indice de l’élément de la collection
nomTable.LAST() Retourne le dernier indice de l’élément de la collection
nomTable.PRIOR(x) Retourne l’élément avant le xe élément de la collection
nomTable.NEXT(x) Retourne l’élément après le xe élément de la collection
nomTable.TRIM(x) supprime x éléments à partir de la fin de la collection
nomTable.EXTEND() Ajoute/insère un élément NULL dans la collection
nomTable.EXTEND(x) Ajoute/insère x éléments NULL dans la collection
nomTable.EXTEND(x,y) Ajoute/insère x copies du ye éléments dans la collection
nomTable.DELETE() Vide/Supprime tous les éléments de la collection
nomTable.DELETE(x) Réservé aux NESTED TABLE, supprime le xe éléments de la collection.
nomTable.DELETE(x,y) Réservé aux NESTED TABLE, supprime les éléments d’indice x à y de la
collection
K. M. BROU Langage PL/SQL 48
IV. Les tables PL/SQL
 Exemple 1 : table1.sql
SET SERVEROUTPUT ON
DECLARE
TYPE Vecteur IS TABLE OF NUMBER(5) INDEX BY BINARY_INTEGER;
v Vecteur;
BEGIN
v(1) := 5;
v(1) := v(3); --erreur car v(3) n'a pas ete initialise
DBMS_OUTPUT.PUT_LINE('v(1) = ' || v(1));
DBMS_OUTPUT.PUT_LINE('Nb elements = ' || v.COUNT());
END;
/

 Mettre la ligne 6 en
commentaire et réexécuter

K. M. BROU Langage PL/SQL 49

IV. Les tables PL/SQL
 Exemple 2 : table de multiplication par 10 (table2.sql)
SET SERVEROUTPUT ON
DECLARE
TYPE vecteur IS TABLE OF NUMBER(3) INDEX BY BINARY_INTEGER;
v Vecteur;
i NUMBER;
BEGIN
FOR i IN 1..10 LOOP
v(i) := i*10 ;
END LOOP;
FOR i IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(i || ' * 10 = ' || v(i));
END LOOP;
END;
/

K. M. BROU Langage PL/SQL 50

IV. Les tables PL/SQL
 Exemple 3 : Table SQL et table PL/SQL (table3.sql)
SET SERVEROUTPUT ON
DECLARE
TYPE vecteur IS TABLE OF NUMBER(5) INDEX BY BINARY_INTEGER;
v Vecteur;
i NUMBER := 1;
CURSOR curCl IS
SELECT numCl FROM Client;
BEGIN
FOR r IN curCl LOOP
v(i) := r.numCl ;
i := i + 1;
END LOOP;
FOR i IN 1..v.COUNT() LOOP
DBMS_OUTPUT.PUT_LINE('Numero : ' || v(i));
END LOOP;
END;
/

K. M. BROU Langage PL/SQL 51

V. Les exceptions
 5.1. Définition
 Toutes erreur est considérées comme une exception.
 Situations non prévues par le programme
 Plusieurs sortes d’erreurs :
 Erreurs systèmes :
 manque de mémoire, insertion d'un doublon dans un index...
 Erreurs induites par une action de l'utilisateur
 Avertissements de l'application à l'utilisateur
 Un seul gestionnaire pour traiter toutes les exceptions
 Exceptions déclarées en fin de bloc
 4 sortes d’exception :
 Exception nommées (prédéfinies) ;
 Exceptions utilisateur nommées ;
 Notification d'erreur entre Client et serveur ;
 Exception système anonymes.

K. M. BROU Langage PL/SQL 52

 V. Les exceptions
 5.2. Exception nommées (ou prédéfinies)
 Exceptions auxquelles PL/SQL a attribué des noms
 Déclenchées à la suite d'une erreur de traitement de
PL/SQL ou du SGBDR
 Quelques exemples :

Nom de l'exception Description

CURSOR_ALREADY_OPEN On a tenté d'ouvrir un curseur déjà ouvert
NO_DATA_FOUND déclenchée dans trois cas :
1. On exécute un ordre SELECT INTO qui ne ramène aucun
tuple
2. On référence une ligne non initialisée d'une table
3. On tente de lire après la fin d'un fichier avec le package
UTL_FILE
ZERO_DIVIDE Un programme a tenté de faire une division par zéro.

K. M. BROU Langage PL/SQL 53

V. Les exceptions
 Exemple : exceptionEx1.sql
SET SERVEROUTPUT ON
PROMPT Entrer numérateur
ACCEPT num
PROMPT Entrer dénominateur
ACCEPT den
DECLARE
q NUMBER(6,2);
r NUMBER(6,2);
BEGIN
q := &num / &den ;
r := &num - q * &den ;
DBMS_OUTPUT.PUT_LINE('Quotient = ' || q || ' Reste =
' || r);
EXCEPTION
WHEN ZERO_DIVIDE THEN
A cause de ACCEPT, exécuter le
DBMS_OUTPUT.PUT_LINE('Erreur division par zéro '); programme comme un script.
END;
/ Pas de copier coller.
K. M. BROU Langage PL/SQL 54
V. Les exceptions
 5.3. Exceptions utilisateur nommées
 Déclenchées à la suite d'erreurs dans le code applicatif
 Nommées lors de leur déclaration par l’utilisateur
 Déclenchées explicitement durant l'exécution du
programme
 Syntaxe
 Déclaration d’une exception
 nomException EXCEPTION ;
 Lever une exception
 RAISE nomException ;
 La section d'exception
EXCEPTION
WHEN nomException [ OR nomException ... ] THEN
<ordre executable>
[WHEN nomException [ OR nomException ... ] THEN
<ordre executable> ...]
[WHEN OTHERS THEN
<ordre executable> ...]
END

K. M. BROU Langage PL/SQL 55

V. Les exceptions
 Exemple : exceptionEx2.sql
SET SERVEROUTPUT ON IF &qtc > qS THEN
PROMPT Entrer numéro du produit RAISE err_qteCom;
ACCEPT numP ELSE
PROMPT quantité commandée UPDATE Produit
ACCEPT qtc SET qteStock = qteStock - &qtc
WHERE numProd = &numP ;
DECLARE END IF ;
qS NUMBER(5); EXCEPTION
err_qteCom EXCEPTION ; WHEN err_qteCom THEN
BEGIN DBMS_OUTPUT.PUT_LINE('Commande
SELECT qteStock INTO qS impossible car ');
FROM Produit DBMS_OUTPUT.PUT_LINE('qteCom = '
WHERE numProd = &numP ; || &qtc || ' > qteStock = ' || qS);
END;
/

K. M. BROU Langage PL/SQL 56

V. Les exceptions
 5.4. Notification d'erreur entre Client et serveur
 Procédure Oracle RAISE_APPLICATION_ERROR
 Sert à transmettre les erreurs des applications depuis le
serveur jusqu'au Client.
 Seul moyen de faire gérer une erreur survenant sur le serveur
par une application cliente.
 Effets semblables au déclenchement d'une exception par
la procédure RAISE.
 Code d'erreur compris entre -20000 et -20999
 pour ne pas entrer en conflit avec les codes d'erreurs d'Oracle
 Taille du message d'erreur est limitée à 2Ko
 message plus long est tronqué

K. M. BROU Langage PL/SQL 57

V. Les exceptions
 Exemple : exceptionEx3.sql
SET SERVEROUTPUT ON
PROMPT Entrer numérateur
ACCEPT num
PROMPT Entrer dénominateur
ACCEPT den
DECLARE
q NUMBER(6,2);
r NUMBER(6,2);
BEGIN
IF &den = 0 then
RAISE_APPLICATION_ERROR(-20001,'Erreur division par zéro');
ELSE
q := &num / &den ;
r := &num - q * &den ;
DBMS_OUTPUT.PUT_LINE('Quotient = ' || q || ' Reste = ' || r);
END IF ;
END;
/
K. M. BROU Langage PL/SQL 58
V. Les exceptions
 5.5. Exception système anonymes
 Exceptions déclenchées à la suite d'une erreur de
traitement de PL/SQL ou du SGBDR mais auxquelles
PL/SQL n'a pas attribué de nom.
 Seules les erreurs les plus courantes sont nommées, Les
autres ont un numéro d'identification.
 On peut leur attribué des noms avec la primitive pragma
EXCEPTION_INIT
 Primitive pragma
 Instruction spéciale compilateur qui est traité lors de la
compilation et non lors de l’exécution
 Lorsqu'un nom est associé à une exception, il est possible de
référencer l'exception n'importe où ensuite
 La pragma EXCEPTION_INIT doit apparaître dans la section
de déclaration d'un bloc

K. M. BROU Langage PL/SQL 59

V. Les exceptions
 Exemple : exceptionEx4.sql
 Supprimer un client alors qu’il possède des commandes.
 Exemple
 Delete from client where numcl=1;

K. M. BROU Langage PL/SQL 60

V. Les exceptions
SET SERVEROUTPUT ON
PROMPT Entrer numéro client
ACCEPT numC

DECLARE
err_reste_com EXCEPTION ;
PRAGMA EXCEPTION_INIT (err_reste_com, -02292);
BEGIN
DELETE FROM Client --On tente d'effacer le client
WHERE numCl = &numC;
EXCEPTION --Si des enregs enfants existent, elle se déclenche
WHEN err_reste_com THEN
DBMS_OUTPUT.PUT_LINE('Veuillez d''abord effacer les commandes
de ce client');
END ;
/

K. M. BROU Langage PL/SQL 61

V. Les exceptions
 Exercice :
 Modifier l’exemple ci-dessus afin de supprimer en
arrière plan les lignes de commande, les commandes du
client, puis de supprimer le client. (Sans utiliser ON
DELETE CASCADE)

 Voir fonction

K. M. BROU Langage PL/SQL 62

 VI. Les procédures stockées
 6.1. Définition
 Permet d’enregistrer et d’exécuter des traitements
fréquemment utilisé au niveau du noyau du SGBDR plutôt
que dans chaque application.
 procédures et fonctions

 Un seul exemplaire est défini et stocké dans la BD

 Exécutable en mode partagé, par toutes les applications qui y
font référence.
 Avantages :
 Rapidité : traitements contenus dans une procédure stockée est
exécutée d'un bloc, au sein même du SGBD.
 Economie d'un certain nombre d'aller-retours de données entre
l'application cliente et le serveur, et donc de trafic réseau.
 Analyse syntaxique des requêtes SQL effectuées à la création de la
procédure, donc plus nécessaires à l'exécution, qui s'en trouve
accélérée.

K. M. BROU Langage PL/SQL 63

VI. Les procédures stockées
 Sécurité : Contrôle des actions de l'utilisateur
 Il ne fait qu'appeler la procédure sans utiliser directement les
tables sous-jacentes.
 Ceci réduit le risque de mauvaises manipulations
 Facilité de maintenance et portabilité : Avec les procédures
stockées, une partie de la logique applicative est centralisée sur
le serveur de BD ;
 Cet emplacement unique facilite la création et la maintenance du
code. Toutes les applications clientes accèdent aux procédures de
manière uniforme, ce qui constitue un niveau d'abstraction
appréciable.
 Il en découle qu'on peut changer de technologie client sans réécrire
quoi que ce soit de cette partie du code.

K. M. BROU Langage PL/SQL 64

VI. Les procédures stockées
 Appel d’un sous-programme stocké :
 En mode interactif ;
 Dans une application ;
 Dans un programme hôte utilisant les ordres SQL intégrés
(PROx) ;
 Dans d’autres procédure ou fonction stockées ;
 Dans le corps d’un déclencheur.

K. M. BROU Langage PL/SQL 65

 VI. Les procédures stockées
 6.2. Etapes de développement d’un SP stocké
 Création
 CREATE PROCEDURE ou CREATE FUNCTION
 Exécution
 Déclenche les étapes suivantes au niveau du SGBDR :
 Compilation du code source avec génération d’un pseudo-code si
aucune erreur n’est détectée ;
 Stockage du code source dans la base, même si des erreurs sont
détectées ;
 Stockage du pseudo-code (P code) dans la base, évite la
recompilation du sous-programme à chaque appel.

K. M. BROU Langage PL/SQL 66

 VI. Les procédures stockées
 6.3. Syntaxe
CREATE [OR REPLACE] PROCEDURE nomProc[(paramètre1
[mode1] type1, paramètre2 [mode2] type2,…)] [IS | AS]
Déclaration d’objets locaux
BEGIN
…
END nomProc;
CREATE [OR REPLACE] FUNCTION nomFonc[(paramètre1
[mode1] type1, paramètre2 [mode2] type2,…)] RETURN type
[IS | AS]
Déclaration d’objets locaux
BEGIN
…
END nomFonc;

 REPLACE : remplacer le sp avec le même nom après

modification
 mode : définit le mode de passage du paramètre formel :
 IN : paramètre formel en entrée (par défaut) ;
 OUT : paramètre formel en sortie ;
 IN OUT : paramètre formel en entrée-sortie.
 Type : type du paramètre formel

K. M. BROU Langage PL/SQL 67

 VI. Les procédures stockées
 6.4. Création d’une procédure
 Saisie du programme
 Exemple 1 : désignation d’un produit connaissant son code
(spStockeEx1.sql)
 Saisir le sp à l’aide d’un éditeur de texte ASCII
 Directement
 Dans un script

CREATE OR REPLACE PROCEDURE libProduit1

(numP IN Produit.numProd%TYPE,
libelle OUT Produit.designation%TYPE
) IS
BEGIN
SELECT designation INTO libelle
FROM Produit
WHERE numProd = numP;
END libProduit1;

K. M. BROU Langage PL/SQL 68

VI. Les procédures stockées
 Compilation du programme
 L’exécuter sous SQL*Plus pour le compiler

 Erreur de syntaxe, le message suivant est affiché :

 Procédure créée avec erreurs de compilation

K. M. BROU Langage PL/SQL 69

VI. Les procédures stockées
 Correction des erreurs
 SQL> SHOW errors

 Rappeler la requête dans l’éditeur, corriger les erreurs et

refaire la compilation jusqu’à ce qu’il n’y ait plus d’erreurs.
 Vues du DD pour voir les erreurs :
 USER_ERRORS ;
 ALL_ERRORS ;
 DBA_ERRORS.
 Exemple :
SELECT line, position, text "texte erreur"
FROM USER_ERRORS
WHERE name = 'LIBPRODUIT1';
K. M. BROU Langage PL/SQL 70
VI. Les procédures stockées
 Informations sur la procédure
 Informations générales :
 USER_OBJECTS, ALL_OBJECTS, DBA_OBJECTS
 Code source stocké :
 USER_SOURCE, ALL_SOURCE, DBA_SOURCE
 Exemple
SELECT text
FROM USER_SOURCE
WHERE name = 'LIBPRODUIT1';

K. M. BROU Langage PL/SQL 71

 VI. Les procédures stockées
 6.4. Création d’une fonction
 Saisie et compilation
 idem saisie de procédure
 Exemple : désignation d’un produit connaissant son code
(spStockeEx2.sql)
CREATE OR REPLACE FUNCTION libProduit2
(numP IN Produit.numProd%TYPE) RETURN
Produit.designation%TYPE IS
libelle Produit.designation%TYPE ;
BEGIN
SELECT designation INTO libelle
FROM Produit
WHERE numProd = numP ;
RETURN(libelle) ;
END libProduit2 ;

K. M. BROU Langage PL/SQL 72

VI. Les procédures stockées
 Remarque
 Il ne faut pas indiquer la taille des types des paramètres
formels.

A ne pas faire A faire

CREATE OR REPLACE PROCEDURE CREATE OR REPLACE PROCEDURE
libProduit1 libProduit1
(numP IN NUMBER(4), (numP IN NUMBER,
libelle OUT VARCHAR2(20) libelle OUT VARCHAR2
) IS ) IS
BEGIN BEGIN
SELECT designation INTO libelle SELECT designation INTO libelle
FROM Produit FROM Produit
WHERE numProd = numP ; WHERE numProd = numP ;
END libProduit1 ; END libProduit1 ;

K. M. BROU Langage PL/SQL 73

 VI. Les procédures stockées
 6.5. Exécution d’un sous-programme stocké
 Se fait par référence à son nom avec transmission des
valeurs des paramètres en entrée.
 2 modes d’appel :
 Interactif : SQL*Plus
 Inclus dans une application cliente : SQL*Forms, Bloc PL/SQL
 3 façons de transmission les paramètres :
 Transmission par positionnement :
 Indiquer les paramètres effectifs dans l’ordre des paramètres
formels correspondants
 Transmission par nom :
 Associer explicitement un paramètre formel à un paramètres
effectif par : paramètre formel => paramètres effectif
 Transmission mixte :
 Transmettre certains paramètre par positionnement et d’autres
par nom.

K. M. BROU Langage PL/SQL 74

 VI. Les procédures stockées
 6.6. Appel d’un sous-programme
 Appel d’une procédure
 EXECUTE nomProc(liste de paramètres effectifs) ;
 Remarque :
 Variables déclarées par ACCEPT : précédées de &
 Variables déclarée par VARIABLE : précédée de :
 Exemples :

SQL> VARIABLE design VARCHAR2(20)

SQL> EXECUTE libProduit1(2, :design)
SQL> PRINT design
SQL> EXECUTE libProduit1(numP=>2, libelle=>:design)
SQL> PRINT design
Ou
SQL> EXECUTE libProduit1(libelle=>:design, numP=>2)
SQL> PRINT design

K. M. BROU Langage PL/SQL 75

VI. Les procédures stockées
 Appel d’une fonction
 VARIABLE variableLocale typeVariable
 EXECUTE :variableLocale := nomFonc(liste de paramètres
effectifs) ;
 Exemples :

SQL> EXECUTE :design := libProduit2(2)

SQL> PRINT design

SQL> EXECUTE :design := libProduit2(numP=>2)

SQL> PRINT design

K. M. BROU Langage PL/SQL 76

 VI. Les procédures stockées
 6.7. Suppression sous-programme
 Procédure
 DROP PROCEDURE nomProcedure ;
 Fonction
 DROP FUNCTION nomFonction ;

K. M. BROU Langage PL/SQL 77

VI. Les procédures stockées
 6.8. Exercices
 Exercice 1 : Créer la procédure montantTPOFC qui calcule
le montant total d’une commande d’un client donné.
 Arguments : numéro du client, numéro de la commande,
montant de la commande
 Exercice 2 : Créer la fonction montantTFOFC qui calcule le
montant total d’une commande d’un client donné.
 Arguments : numéro du client, numéro de la commande
 La fonction retourne le montant de la commande
 On utilisera : OPEN, FETCH, CLOSE
 Exercice 3 : Créer la fonction montantTFC idem Exercice 2
 On utilisera une boucle FOR de type curseur
 Exercice 4 : Créer la fonction montantTFCom qui appelle
montantTFC pour calculer la somme de toutes les
commandes d’un client donné.
 Arguments : numéro du client
 La fonction retourne la somme de toutes les commandes
 On utilisera une boucle for de type curseur
K. M. BROU Langage PL/SQL 78
VI. Les procédures stockées
 Exercice 5 : Ecrire une procédure supprClient permettant
de supprimer les lignes de commande, les commandes d’un
client, puis de supprimer le client (Sans utiliser ON DELETE
CASCADE).
 Arguments : numéro du client

K. M. BROU Langage PL/SQL 79

VII. Les paquetages
 7.1. Définition
 ou package
 Ensemble d’objets (types, variables, exceptions, curseurs,
sous-programmes) qui forment une application complète.

K. M. BROU Langage PL/SQL 80

VII. Les paquetages
 7.2. Structure d’un package
 Peut contenir deux types de sous-programmes :
 Sous-programme publics (PUBLIC) visibles de l’extérieur du
package (par défaut).
 Sous-programmes privés (PRIVATE), invisible de l’extérieur
 Possède deux parties distinctes :
 Partie déclaration et partie corps
 Créée et compilée séparément

K. M. BROU Langage PL/SQL 81

VII. Les paquetages
 7.3. Création de la partie déclaration

CREATE OR REPLACE PACKAGE nomPackage [IS | AS]

[Déclaration des objets publics ;]
END nomPackage ;

K. M. BROU Langage PL/SQL 82

VII. Les paquetages
 7.4. Création de la partie corps
CREATE OR REPLACE PACKAGE BODY nomPackage [IS | AS]
[Définition des objets privés ;]
[BEGIN
--partie initilisation]
END nomPackage ;

 Remarque
 Surcharge possible des sous-programmes dans un package
(même nom mais avec des signatures différentes).
 Dans l’exemple qui suit, libProduit à été surchargé

K. M. BROU Langage PL/SQL 83

VII. Les paquetages
 Création de la tête du package (packageHead.sql)
 Saisie et compilation du code
CREATE OR REPLACE PACKAGE manipProduit AS
PROCEDURE libProduit (numP IN Produit.numProd%TYPE,
libelle OUT Produit.designation%TYPE) ;
FUNCTION libProduit(numP IN Produit.numProd%TYPE)
RETURN Produit.designation%TYPE ;
END manipProduit;
/

 libProduit est surchargé

K. M. BROU Langage PL/SQL 84

VII. Les paquetages
 Création du corps du package (packageBody.sql)
 Saisie et compilation

CREATE OR REPLACE PACKAGE BODY manipProduit AS

PROCEDURE libProduit(numP IN Produit.numProd%TYPE, libelle OUT
Produit.designation%TYPE) IS
BEGIN
SELECT designation INTO libelle FROM Produit WHERE numProd = numP ;
END libProduit ;

FUNCTION libProduit(numP IN Produit.numProd%TYPE) RETURN

Produit.designation%TYPE IS libelle Produit.designation%TYPE ;
BEGIN
SELECT designation INTO libelle FROM Produit WHERE numProd = numP ;
RETURN(libelle) ;
END libProduit ;
End manipProduit ;
/

K. M. BROU Langage PL/SQL 85

VII. Les paquetages
 7.4. Appel d’un objet du package
 nomPackage.nomObjet ;

SQL> VARIABLE design VARCHAR2(12)

SQL> EXECUTE manipProduit.libProduit(2, :design) ;
SQL> PRINT design
SQL> VARIABLE design VARCHAR2(20)
SQL> EXECUTE :design := manipProduit.libProduit(2) ;
SQL> PRINT design

K. M. BROU Langage PL/SQL 86

VII. Les paquetages
 7.5. Suppression d’un package
 Package entier : DROP PACKAGE nomPackage ;
 Corps du package : DROP PACKAGE BODY nomPackage ;

K. M. BROU Langage PL/SQL 87

VII. Les paquetages
 Exercices : créer le package gestCom contenant les
fonctions suivantes :
 Procédure montantTPOFC qui calcule le montant total d’une
commande d’un client donné.
 Arguments : numéro du client, numéro de la commande, montant de la
commande
 Fonction montantTFOFC qui calcule le montant total d’une
commande d’un client donné.
 Arguments : numéro du client, numéro de la commande
 La fonction retourne le montant de la commande
 On utilisera : OPEN, FETCH, CLOSE
 Fonction montantTFC idem Exercice 2
 On utilisera une boucle FOR de type curseur
 Fonction montantTFCom qui appelle montantTFC pour calculer
la somme de toutes les commandes d’un client donné.
 Arguments : numéro du client
 La fonction retourne la somme de toutes les commandes
 On utilisera une boucle for de type curseur

K. M. BROU Langage PL/SQL 88

VII. Les paquetages
 Devoir à rendre Date
dateCompo

1,n
Etudiant UV
numEt 1,n Devoir 1,n codeUV
nom note libelle
nomClasse coeffNote coeffUV
 Un étudiant peut avoir plusieurs notes coefficientées dans
une matière à des dates différentes.
 Trouver le schéma relationnel.
 Créer un package appelé packageClasse contenant les fonctions
suivantes (on n’utilisera pas de fonctions prédéfinies).
 moyenneEtUV(nom1, libelle1) : moyenne d’un étudiant dans 1 UV.
 moyenneClUV(nomClasse1,libelle1) : moyenne d’une classe dans 1
UV.
 moyenneEtGen(nom1) : moyenne générale d’un étudiant.
 moyenneClGen(nomClasse1) : moyenne générale d’une classe.
 premierCl(nomClasse1) : nom du premier d’une classe.
 Donner les instruction SQL*Plus pour appeler la fonction
moyenneEtGen().
K. M. BROU Langage PL/SQL 89
 VIII. Les triggers
 8.1. Définition
 Trigger ou déclencheur
 Traitement déclenché par un événement.
 Evénement déclencheur
 Action de mise à jour sur une table :
 Insert ;
 Update ;
 Delete.

K. M. BROU Langage PL/SQL 90

 VIII. Les triggers
 8.2. Caractéristiques
 Un trigger est associé à une et une seule table.
 Il est opérationnel jusqu’à la suppression du trigger ou de la
table à laquelle il est lié.
 Un trigger peut être actif ou inactif.
 A chaque exécution d’un trigger, le noyau crée un curseur.
 2 sortes de trigger :
 Trigger par ordre :
 Traitement exécuté une fois pour la table;
 Trigger ligne
 Traitement exécuté pour chaque ligne de la table concernée par
l’événement.

K. M. BROU Langage PL/SQL 91

 VIII. Les triggers
 8.3. Typologie des déclencheurs
 Douze types de déclencheurs :
Type BEFORE Type AFTER
BEFORE UPDATE ligne AFTER UPDATE ligne
BEFORE DELETE ligne AFTER DELETE ligne
BEFORE INSERT ligne AFTER INSERT ligne
BEFORE UPDATE ordre AFTER UPDATE ordre
BEFORE DELETE ordre AFTER DELETE ordre
BEFORE INSERT ordre AFTER INSERT ordre

K. M. BROU Langage PL/SQL 92

 VIII. Les triggers
 8.4. Déclencheur par ordre
 Exécuté une seule fois pour l’ensemble des lignes
concernées par l’événement.
 Syntaxe
CREATE [OR REPLACE] TRIGGER [schéma.]nomDéclencheur
Séquence
Evénement [OR événement]
ON nomTable
Bloc PL/SQL ;
 Avec
 Séquence : BEFORE ou AFTER
 Evénement : INSERT, UPDATE ou DELETE
 nomTable : nom de la table à laquelle le déclencheur est lié
 Bloc PL/SQL : décrit le traitement à réaliser

K. M. BROU Langage PL/SQL 93

VIII. Les triggers
 Exemple 1 : Trigger qui donne à TOTO seul le droit
d’ajouter un produit (triggerOrdreEx1.sql)

CREATE OR REPLACE TRIGGER ajoutProduit

BEFORE INSERT ON Produit
BEGIN
IF USER != 'TOTO' THEN
RAISE_APPLICATION_ERROR(-20001,'Utilisateur non autorisé.');
END IF;
END;
/

 Exécuter ce trigger
 Ajouter un nouveau produit
 INSERT INTO PRODUIT VALUES(76, 'Serrure', 1999, 67);

K. M. BROU Langage PL/SQL 94

VIII. Les triggers
 Le SGBD vous affiche le message suivant :

 Exécuter les instructions suivantes :

1. CONNECT system/brou
2. CREATE USER toto IDENTIFIED BY toto;
3. GRANT CONNECT, RESOURCE TO toto;
4. GRANT SELECT, INSERT ON BROU.Produit TO toto;
5. CONNECT toto/toto@BDORACLE
6. INSERT INTO BROU.PRODUIT VALUES(76, 'Serrure', 1999, 67);
7. SELECT * FROM BROU.PRODUIT;
 Brou est le propriétaire de la table Produit
K. M. BROU Langage PL/SQL 95
 VIII. Les triggers
 8.5. Déclencheur ligne
 Exécuté pour chaque ligne de la table concernées par
l’exécution de l’événement.
 Syntaxe CREATE [OR REPLACE] TRIGER [schéma.]nomDéclencheur
Séquence
Evénement [OR événement]
ON nomTable
[REFERENCING {[OLD [AS] ancien] | [NEW [AS]
nouveau]}]
FOR EACH ROW
[WHERE condition]
Bloc PL/SQL ;

 Condition : prédicat exécuté pour chaque ligne manipulée.

 Condition vraie pour le traitement associé au déclencheur soit
exécuté
 Ne doit pas contenir de requête SQL
 utilisée pour restreindre l’action du trigger à certaines lignes de la
table
 REFERENCING
 Permet de changer l’indicatif de référence de OLD par ancien et/ou
de NEW par nouveau.
 Ancien et nouveau sont deux indicatifs choisis par le développeur
K. M. BROU Langage PL/SQL 96
VIII. Les triggers
 Se connecter avec le compte du propriétaire de la table Produit
 SQL> CONNECT brou/brou@BDORACLE
 Exemple 1 : avant toute MAJ d’un produit, vérifier que la
nouvelle quantité en stock est >0 (triggerligneEx1.sql)
CREATE OR REPLACE TRIGGER verifQteStock
BEFORE UPDATE OF qteStock ON Produit
FOR EACH ROW
BEGIN
IF :new.qteStock < 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Mise à jour refusee');
END IF;
END;
/

 Exécuter ce trigger

K. M. BROU Langage PL/SQL 97

VIII. Les triggers
 Saisir la requête suivante :
 SQL> UPDATE produit SET qteStock = -20 ;
 Le SGBD vous affiche le message suivant :

 Saisir la requête suivante :

 SQL> UPDATE produit SET qteStock = 12 WHERE numProd=1;

K. M. BROU Langage PL/SQL 98

 VIII. Les triggers
 8.6. Un même déclencheur pour plusieurs événements
 Prédicats prédéfinis INSERTING, UPDATING ou DELETING
pour exécuter une séquence particulière du traitement en
fonction du type d’événement
 Syntaxe CREATE OR REPLACE TRIGGER nomTrigger
BEFORE INSERT OR UPDATE OR DELETE ON …
BEGIN
IF INSERTING THEN … END IF
IF UPDATING(nomColonne) THEN … END IF
IF DELETING THEN … END IF
…
END ;

 On peut faire référence, dans la condition de la clause

WHERE ou dans le corps du traitement associé :
 à la valeur d’une colonne avant MAJ en préfixant le nom de la
colonne par :OLD
 et/ou à la valeur après MAJ en préfixant le nom de la colonne
par :NEW
K. M. BROU Langage PL/SQL 99
VIII. Les triggers
 Exemple 2 : mise à jour de la quantité en stock d’un
produit (triggerligneEx2.sql)

CREATE OR REPLACE TRIGGER modifCom IF INSERTING OR UPDATING THEN

AFTER INSERT OR DELETE OR UPDATE ON SELECT qteStock INTO qs 2
LigneCom FROM Produit p
FOR EACH ROW WHERE p.numProd = :NEW.numProd;
DECLARE IF qs > :NEW.qteCom THEN
qs NUMBER(4); UPDATE Produit p
BEGIN SET qteStock = qteStock - :NEW.qteCom
IF DELETING OR UPDATING THEN WHERE p.numProd = :NEW.numProd;
UPDATE Produit p ELSE
SET qteStock = qteStock + RAISE_APPLICATION_ERROR(-20001,
:OLD.qteCom 'Commande refusee car qteStock = ' || qs
WHERE p.numProd = :OLD.numProd; || ' < ' || 'qteCom = ' || :NEW.qteCom );
END IF; END IF;
1 END IF; 3
END;
/

K. M. BROU Langage PL/SQL 100

VIII. Les triggers
 Exécution
 Contenu des tables Produit et LigneCom

K. M. BROU Langage PL/SQL 101

VIII. Les triggers
 Insertion 1 : qteCom > qteStock
 SQL> INSERT INTO ligneCom VALUES(18, 1,
s_ligneCom_numLi.NEXTVAL, 200);

 Insertion 2 : qteCom <= qteStock

 SQL> INSERT INTO ligneCom VALUES(18, 1,
s_ligneCom_numLi.NEXTVAL, 2) ;

K. M. BROU Langage PL/SQL 102

VIII. Les triggers
 Mise à jour 1 : augmenter qteCom (passe de 3 à 13)
LigneCom :old.numCom :old.numProd :old.qteCom :new.qteCom
1 2 3 13

Produit :old.numProd :old.qteStock :new.qteStock

2 65 55

 SQL> UPDATE ligneCom SET qteCom = 13 WHERE numCom=1 and

1 numProd=2;

qteStock=qteStock+:OLD.qteCom
qteStock = 65 + 3 = 68

qteStock = qteStock - :NEW.qteCom

qteStock = 68 – 13 = 55

K. M. BROU Langage PL/SQL 103

VIII. Les triggers
 Mise à jour 2 : diminuer qteCom (passe de 13 à 3)
LigneCom :old.numCom :old.numProd :old.qteCom :new.qteCom
1 2 13 3

Produit :old.numProd :old.qteStock :new.qteStock

2 55 65

 SQL> UPDATE ligneCom SET qteCom = 3 WHERE numCom=1 and

1 numProd=2;

qteStock=qteStock+:OLD.qteCom
qteStock = 55 + 13 = 68

qteStock = qteStock - :NEW.qteCom

qteStock = 68 – 3 = 65

K. M. BROU Langage PL/SQL 104

VIII. Les triggers
 Suppression :
LigneCom :old.numCom :old.numProd :old.qteCom
1 2 3

Produit :old.numProd :old.qteStock :new.qteStock

2 65 68

 SQL> DELETE FROM ligneCom WHERE numCom=1 and numProd=2;

qteStock=qteStock+:OLD.qteCom
qteStock = 65 + 3 = 68

K. M. BROU Langage PL/SQL 105

VIII. Les triggers
 8.7. Un déclencheur ligne avec l’option AFTER
 Permet de faire des MAJ ou de gérer des historiques
 On peut faire référence, dans la condition de la clause
WHERE ou dans le corps du traitement associé :
 à la valeur d’une colonne avant MAJ en préfixant le nom de la
colonne par :OLD
 et/ou à la valeur après MAJ en préfixant le nom de la colonne
par :NEW
 La valeur prise en compte dépend de l’ordre SQL :

Ordre SQL :OLD :NEW

INSERT NULL Valeur créée
DELETE Valeur avant suppression NULL
UPDATE Valeur avant modification Valeur après modification

K. M. BROU Langage PL/SQL 106

VIII. Les triggers
 Exemple : trigerLigneEx3.sql
 Pour chaque modification de la table Produit, garder dans la
table un historique des lignes manipulées
CREATE TABLE HProduit
(utilisateur VARCHAR2(15),
dateM DATE,
numProd NUMBER(5),
designation VARCHAR2(12),
prixUnit NUMBER(7,2),
qteStock NUMBER(5)
);
CREATE OR REPLACE TRIGGER HistoProduit
AFTER UPDATE OR DELETE OR INSERT ON Produit
FOR EACH ROW
BEGIN
INSERT INTO HProduit VALUES
(user,SYSDATE,:OLD.numProd,:OLD.designation,:OLD.prixUnit,:OLD.qteStock);
END;
/
K. M. BROU Langage PL/SQL 107
VIII. Les triggers
 Exécuter les opérations suivantes :

Utilisateur Opération

brou UPDATE Produit SET qteStock= 20 WHERE numProd=1;

froto INSERT INTO Produit VALUES(76, 'Serrure',1990,67);

brou DELETE FROM Produit WHERE numProd=76

K. M. BROU Langage PL/SQL 108

 VIII. Les triggers
 8.8. Gestion des déclencheurs
 Suppression d’un déclencheur
 DROP TRIGGER nomDéclencheur ;
 Désactivation d’un trigger particulier
 ALTER TRIGGER nomDéclencheur DISABLE ;
 Désactivation de tous les triggers d’une table
 ALTER TABLE nomTable DISABLE ALL TRIGGER ;
 Activation
 à sa création, un triggeur est automatiquement activé
 Activation d’un trigger particulier
 ALTER TRIGGER nomDéclencheur ENABLE ;
 Activation de tous les triggers d’une table
 ALTER TABLE nomTable ENABLE ALL TRIGGER ;
 Vues du DD
 USER_TRIGGERS, ALL_TRIGGERS et DBA_TRIGGERS

K. M. BROU Langage PL/SQL 109

VIII. Les triggers
 Exercice :
 Modifier la table HProduit afin d’y ajouter le type d’opération
effectué :
 Type = INSERT ou DELETE OU UPDATE
 Modifier en conséquence le code du trigger HistoProduit
 Tester avec les opérations suivantes :
Utilisateur Opération
brou UPDATE Produit SET qteStock= 20 WHERE numProd=1;
brou DELETE FROM Produit WHERE numProd=76
toto INSERT INTO Produit VALUES(76, 'Serrure',1990,67);

K. M. BROU Langage PL/SQL 110

 IIX. Communication entre BD
 9.1. Présentation
 Manipuler les objets d’une BD à partir d’une autre BD.
 Envoi de données entre sites
 Nécessite une configuration réseau sur les 2 sites.
 Eléments à configurer :
 Connectivité réseau : fichier tnsname.ora
 Liens de BD : DATABASE LINK
 Commande CREATE DATABASE LINK
 Permet de créer un lien entre deux BD.
 Permet d’accéder aux objets de la 2ème BD : Tables, vues…
 Exemple :
 Accéder à la table Produit de la BD BDTEST à partir de la BD
BDORACLE.

DATABASE LINK
BDORACLE BDTEST

Table Produit
Poste1 : brou Poste2 : marco
K. M. BROU Langage PL/SQL 111
IX. Communication entre BD
 9.2. Principe
1. Fichier tnsname.ora de la BD source BDORACLE
 Doit contenir la chaîne de connexion de la BD cible BDTEST.
 Exemple

K. M. BROU Langage PL/SQL 112

IX. Communication entre BDD
2. Créer le database link
 Syntaxe 1 : Pour utilisateur fixe
CREATE DATABASE LINK <nomDeLaLiaison>
CONNECT TO <utilisateur> IDENTIFIED BY <motDePasse>
USING '<aliasChaineDeConnectionBDCible>';

 Syntaxe 2 : pour tous les utilisateurs

CREATE PUBLIC DATABASE LINK <nomDeLaLiaison>
USING '<aliasChaineDeConnectionBDCible>';

 Syntaxe 3 : pour utilisateur connecté

CREATE DATABASE LINK <nomDeLaLiaison>
CONNECT TO CURRENT_USER
USING '<aliasChaineDeConnectionBDCible>';

3. Connexion à la BD cible
 Opérations possibles :
 SELECT, INSERT, DELETE, UPDATE, LOCK TABLE.
 Tous les objets de la BD cible doivent être suivis de :
K. M. BROU
 @nomDeLaLiaison Langage PL/SQL 113
IX. Communication entre BD
 Exemple : database link avec utilisateur fixe
 Exemple 1 : A partir de BDORACLE, afficher le prix d’un produit
de BDTEST dans un programme PL/SQL.

CREATE DATABASE LINK BDTestLink

CONNECT TO marco IDENTIFIED BY marco
USING 'bdtest';

 BDTEST a un utilisateur appelé marco de mot de passe marco.

 nomDeLaLiaison et aliasChaineDeConnectionBDCible peuvent être
identiques.

K. M. BROU Langage PL/SQL 114

IX. Communication entre BD
 Test de sélection dans SQL*Plus
select * from produit@BDTestLink;

K. M. BROU Langage PL/SQL 115

IX. Communication entre BD
 Test dans PL/SQL (prixProduit.sql)

CREATE OR REPLACE FUNCTION prixProduit(numPr IN Produit.numProd%TYPE)

RETURN NUMBER IS
prix NUMBER;
BEGIN
SELECT prixUnit INTO prix
FROM produit@BDTestLink
WHERE numProd=numPr;
RETURN prix;
END prixProduit;
/
VARIABLE prix NUMBER
EXECUTE :prix : = prixProduit(2)
PRINT prix

K. M. BROU Langage PL/SQL 116

IX. Communication entre BD
 Exemple 2 : A partir de BDORACLE, modifier la quantité en stock
d’un produit de BDTEST dans un programme PL/SQL.
 Test de mise à jour dans SQL*Plus
update commit;
produit@BDTestLink select * from produit@BDTestLink where numProd=1;
set qteStock = 180
where numProd=1;

 La 2ème image écran montre bien que la MAJ a été faite dans la
BD BDTEST.
K. M. BROU Langage PL/SQL 117
IX. Communication entre BD
 Test de mise à jour dans PL/SQL (majStock.sql)
CREATE OR REPLACE PROCEDURE majQteStock(numPr IN Produit.numProd%TYPE,
qteS IN NUMBER) IS
BEGIN
update produit@bdtestLink
set qteStock = qteS
where numProd=numPr;
END majQteStock;
/
SELECT * FROM Produit@BDTestLink;
EXECUTE majQteStock(1,380)
SELECT * FROM Produit@BDTestLink;

K. M. BROU Langage PL/SQL 118

IX. Communication entre BD

K. M. BROU Langage PL/SQL 119

IX. Communication entre BD
 Exercice
 Ecrire une procédure stockée (Fonction) qui calcule le montant
total d’une commande d’un client donné.
 La table Produit est située dans la BD BDTest.

K. M. BROU Langage PL/SQL 120

Bibliographie
 Livres :
 "Objet-relationnel sous Oracle8, Modélisation avec UML", Christian
Soutou, Eyrolles.
 " Les bases de données Oracle 8i Développement Administration
Optimisation ", Roger Chapuis, DUNOD
 " Client-Serveur, moteur SQL, middleware et architectures
parallèles ", Serge MIRANDA et Anne RUOLS, Editions Eyrolles.
 "Cours d’introduction Modèles d’interactions pour le client serveur
et exemples d’architectures les implantant", Gérard Florin,
Conservatoire National des Arts et Métiers, Laboratoire CEDRIC
 "Oracle 10g sous Windows", Giles Briard, Edition Eyrolles 2006
 "Oracle 11g Administration", Olivier HEURTEL, Edition ENI.
 "Programmation Objet avec Oracle, Techniques et pratiques",
2e édition, Christian SOUTOU, Edition Vuibert.
 Supports de cours Oracle Formation :
 Programmer avec PL/SQL
 Le langage SQL & l’outil SQL*Plus

K. M. BROU Langage PL/SQL 121

IX. Bibliographie
 Webographie :
 http://lifc.univ-fcomte.fr/~lasalle/OracleV7/page1.htm de Marie-
France Lasalle : [email protected]
 http://www.hds.utc.fr/~crozatst/ftp/nf17/7.sgbdro.pdf#search=
%22h%C3%A9ritage%2BSQL3%22
 http://www2.lifl.fr/~durif/bdd/coursBD/html/developpement.ht
ml
 "Les aspects objet-relationnels, d’Oracle (de la V8 à la 11g)",
Christian Soutou, http://www.soutou.net/christian

K. M. BROU Langage PL/SQL 122