Cours Bases de données 2ème année IUT : R307,

SQL dans un langage de programmation

Rappels PL/SQL : ou comment faire plus avec ORACLE

Sahar Ghannay
Plan

1 Introduction
2 Structure d’un programme
3 Les curseurs
Pourquoi et comment
Exemple
Curseurs pour mise à jour
4 Fonctions et procédures stockées
Définition
Procédure stockée
Fonction stockée
Utilisation
5 Déclencheurs
Définition
Syntaxe
Différents types : table, ligne
Exemples
Organisation de cours

7 cours et 17 TD
2 interros
1 DS
Les supports de cours sont sur Moodle: Cours/Département
Informatique/BUT 2/S3/R307-SQL dans un langage de
programmation/Programmer avec SQL
La clé d’inscription est BDS32025
Pourquoi PL/SQL?

L’utilisation de PL/SQL est indiquée :

Pour effectuer des traitements de données qui impliquent des
transactions complexes (plusieurs requêtes et manipulations
de données liées).
Pour effectuer un contrôle d’intégrité des données (dans des
triggers par exemple).
Pour stocker dans la base de données les opérations
fréquentes. (Packages et procédures stockées.)
Pour minimiser le temps d’interaction entre la base de
données et la portion interface. → en S3
Pour minimiser l’impact des mises à jour sur une application
avec un grand nombre de postes clients.
Structure d’un programme

Un programme ou une procédure PL/SQL est constitué d’un ou

plusieurs blocs.
Description d’un bloc
3 sections :
Déclaration des structures et des variables utilisées dans le
bloc (facultative)
Corps qui contient les instructions (obligatoire)
Traitement des erreurs : pour gérer les erreurs. (facultative)
Exemple d’un bloc

DECLARE
nbRealAct NUMBER(5);
singulierException EXCEPTION;
BEGIN
SELECT COUNT(distinct [Link]) INTO nbRealAct
FROM Film F JOIN Acteur A ON (realisateur=numIndividu)
WHERE [Link]=[Link];
IF nbRealAct = 1 THEN RAISE singulierException; END IF;
DBMS [Link] LINE(nbRealAct||’ réalisateurs ont joué dans
leur film’);
EXCEPTION
WHEN singulierException THEN
DBMS [Link] LINE(’Un seul réalisateur a joué dans son
film’);
END;
Les curseurs : pourquoi?

Une instruction SELECT peut renvoyer plusieurs colonnes et

plusieurs lignes!
Mais comment faire avec un SELECT...INTO?
pour plusieurs colonnes : SELECT...INTO sait le gérer
SELECTnomIndividu, prenomIndividu INTO leNom, lePrenom
FROM [Link]
WHERE numIndividu = 10;
→ pas de problème
pour plusieurs lignes : ... on ne peut pas!
SELECTnomIndividu, prenomIndividu INTO leNom, lePrenom
FROM [Link]
WHERE numIndividu < 10;
Exception...
→ la solution : une nouvelle structure de données, les curseurs.
Les curseurs : définition

Un curseur est une structure qui contient les informations

suivantes :
le texte source de l’instruction SQL,
le texte compilé de l’instruction SQL,
un tampon qui contiendra une ligne du résultat,
le statut du curseur (cursor status),
des informations de travail,
des informations de contrôle.
Les curseurs : usage

Déclaration
CURSOR nomCurseur IS <texte requete>;

Ouverture
OPEN nomCurseur ;

Utilisation
FETCH nomCurseur INTO <liste variable> | <nomStructure> ;

Fermeture
CLOSE nomCurseur ;
Les curseurs : exemple

On veut afficher les titres des comédies.

La déclaration :

DECLARE
monNumero [Link]%type;
monTitre [Link]%type;
Cursor monCurseur IS
SELECT numfilm, titre
FROM [Link] NATURAL JOIN [Link]
WHERE codegenre=’CO’;

→ Le SELECT qui trouve tous ces titres : juste déclaré.

Les curseurs : exemple suite

DECLARE
monNumero [Link]%type;
monTitre [Link]%type;
Cursor monCurseur IS
SELECT numfilm, titre
FROM [Link] NATURAL JOIN [Link]
WHERE codegenre=’CO’;

BEGIN
OPEN monCurseur ;
LOOP
FETCH monCurseur INTO monNumero, monTitre;
EXIT WHEN monCurseur%NOTFOUND;
DBMS [Link] LINE(’ Son Numéro : ’ ||
monNumero||’ SonTitre : ’ ||monTitre) ;
END LOOP;

→ On traite chaque ligne à chaque passage dans la boucle

Les curseurs : exemple suite

DECLARE
monNumero [Link]%type;
monTitre [Link]%type;
Cursor monCurseur IS
SELECT numfilm, titre
FROM [Link] NATURAL JOIN [Link]
WHERE codegenre=’CO’;
BEGIN
OPEN monCurseur ;
LOOP
FETCH monCurseur INTO monNumero, monTitre;
EXIT WHEN monCurseur%NOTFOUND;
DBMS [Link] LINE(’ Son Numéro : ’ ||
monNumero||’ SonTitre : ’ ||monTitre) ;
END LOOP;

DBMS [Link] LINE(’Voici le nombre total de comédies : ’

|| monCurseur%rowCount);
CLOSE MonCurseur;
END;

→ On utilise les informations associées au curseur

Les curseurs : exemple suite, autre formulation

DECLARE
cpt NUMBER:=0;
Cursor monCurseur IS
SELECT numfilm, titre
FROM [Link] NATURAL JOIN [Link]
WHERE codegenre=’CO’;

BEGIN
FOR ligneCurseur IN monCurseur ;
LOOP
DBMS [Link] LINE(’ Son Numéro : ’ ||
[Link] || ’
SonTitre : ’ || [Link]) ;
cpt:=cpt+1;
END LOOP;
DBMS [Link] LINE(’Voici le total des comédies : ’||cpt)
END;
Les curseurs : exemple suite, autre formulation

DECLARE
cpt NUMBER:=0;
Cursor monCurseur IS ...

BEGIN
FOR ligneCurseur IN monCurseur ;
→ Le FOR ouvre le curseur, ligneCurseur est une variable de
boucle : inutile de la déclarer
LOOP
DBMS [Link] LINE(’ Son Numéro : ’ ||
[Link] || ’
SonTitre : ’ || [Link]) ;
→ On accède aux différents éléments de ligneCurseur...
cpt:=cpt+1;
END LOOP;
→ le curseur est fermé à la fin de la boucle...
Les curseurs : les 2 formulations

la formulation avec le FETCH est encore couramment utilisée

la formulation avec le FOR est plus “économique”... et
permet de ne pas oublier ouverture et fermeture, et évite de
devoir déclarer des variables!
→ la seconde est à préférer, mais la première est encore
fréquemment utilisée!
Curseurs et mise à jour : un exemple pour commencer...

On a une table d’employés, et on veut augmenter les primes...

Le problème
On veut augmenter de 10% la prime des employés gagnant moins
de 3000 ?, et de 20% celle des employés gagnant moins de 500 ?.
Comment faire?
Curseurs et mise à jour : plus simple!

En déclarant le curseur FOR UPDATE

CURSOR inc prime IS
SELECT * FROM employe
WHERE emp sal < 3000
FOR UPDATE;

et en utilisant la clause CURRENT OF

IF r [Link] < 500 THEN
UPDATE employe
SET prime = prime * 1.2
WHERE CURRENT OF inc prime; → accès direct
ELSE UPDATE employe
SET prime = prime * 1.1
WHERE CURRENT OF inc prime; → accès direct
END IF;
Curseurs et mise à jour, en dessin

La clause WHERE CURRENT OF placera le verrou exclusif sur la

ligne une fois l’instruction UPDATE exécutée.
La clause WHERE CURRENT OF pointera vers la ligne du curseur
la plus récemment récupérée.
Curseurs et mise à jour, en dessin

Table Employe

... Salaire Prime ...

Curseur
... Salaire Prime ...

SELECT, fonction du salaire

UPDATE de la prime, en fonction du salaire

NB : Ne le faire que quand c’est nécessaire...

Fonctions et procédures stockées

Pourquoi?
Pour enregistrer des programmes dans le noyau d’Oracle
Comme une table ou une vue, elles peuvent être utilisées par
d’autres utilisateurs, s’ils ont les droits voulus.
Stockées sous forme de pseudo-code : pas de nouvelle
compilation → efficace
Déclaration procédure stockée

Syntaxe
CREATE [OR REPLACE] PROCEDURE nom procedure
[(liste paramètres formels)]
AS | IS
[partie déclaration]
BEGIN
...
[EXCEPTION
...]
END [nom procedure];

partie déclaration: similaire à celle d’un bloc PL/SQL

Déclaration procédure stockée : paramètres

Syntaxe
nom paramètre [IN |
OUT [NOCOPY]|
IN OUT [NOCOPY]] type paramètre
[ := | DEFAULT expression ]

type paramètre : un type PL/SQL

IN : paramètre en entrée, non modifié par la procédure
OUT : paramètre en sortie, peut être modifié par la procédure,
transmis au programme appelant
IN OUT : à la fois en entrée et en sortie
par défaut : IN
NOCOPY : pour passer des références et non des valeurs (mais le
compilateur décide!)
Exemple de procédure stockée

On cherche les réalisateurs qui ont joué dans un certain nombre de

leur film...

CREATE PROCEDURE realActeursProc (nbFilms NUMBER) IS

nbRealAct NUMBER(5);
singulierException EXCEPTION;
BEGIN
SELECT COUNT(distinct [Link]) INTO nbRealAct
FROM Film F JOIN Acteur A ON (realisateur=numIndividu)
WHERE [Link]=[Link];
IF nbRealAct > nbFilms THEN
DBMS [Link] LINE(nbRealAct||’ réalisateurs ont joué
dans plus de ’||nbFilms||’de leurs films’);
ELSE DBMS [Link] LINE(’Aucun réalisateur n’a joué
dans plus de ’||nbFilms||’de ses films’);
END IF;
END;
Déclaration fonction stockée

Syntaxe
CREATE [OR REPLACE] FUNCTION nom fonction
[(liste paramètres formels)]
RETURN typeRetour AS | IS
[partie déclaration]
BEGIN
...

RETURN valeurRetour
...

[EXCEPTION ...]
END [nom fonction];

partie déclaration: similaire à celle d’un bloc PL/SQL

typeRetour : le type PL/SQL de valeurRetour retournée par la
fonction.
liste de paramètres : idem procédures, mais IN préférable dans les
fonctions!!!
Exemple de fonction stockée

On cherche toujours les réalisateurs qui ont joué dans plus de

nbFilms de leurs films...

CREATE FUNCTION nbRealActeurFonc (nbFilms NUMBER)

RETURN NUMBER IS
nbRealAct NUMBER(5) := 0 ;
BEGIN
SELECT COUNT(distinct [Link]) INTO nbRealAct
FROM Film F JOIN Acteur A ON (realisateur=numIndividu)
WHERE [Link]=[Link];
RETURN nbRealAct;
END;
Appel de procédures et de fonctions stockées

Appel à une procédure dans un programme PL/SQL

nom procedure [(liste de paramètres effectifs)];

Appel à realActeursProc
nbFilms:=20;
...

realActeursProc (nbFilms);

Appel à une fonction dans un programme PL/SQL

nomVariable := nom fonction [(liste de paramètres effectifs)];

Appel à nbRealActeurFonc
nbFilms:=20;
...

nbGdActeursReals := nbRealActeurFonc (nbFilms);

Déclencheurs : définition

Définition
Un déclencheur définit une action qui doit s’exécuter quand
survient un événement dans la base de données.
Il peut servir à :
avertir d’un événement,
ajouter des contraintes sur les valeurs des attributs d’une
table,
enregistrer des changements (suivi)
ajouter des règles de gestion,...
Déclencheurs : définition

Quand et comment les utiliser

Un déclencheur peut être déclenché :
à toute modification concernant un objet (INSERT, UPDATE,
DELETE)
à la création, suppression ou modification d’un objet
(CREATE, DROP, ALTER),
à la connexion, déconnexion d’un utilisateur,
démarrage ou arrêt d’une instance...

Durée d’activité
L’action associée à un déclencheur est un bloc PL/SQL enregistré
dans la base.
Un déclencheur est opérationnel jusqu’à la suppression de la table
à laquelle il est lié.
Syntaxe de la création d’un déclencheur

Création
CREATE [OR REPLACE] TRIGGER [schema.]nomTrigger
{ BEFORE | AFTER |INSTEAD OF } listeEvenement
ON nomTable
[FOR EACH ROW]
[ WHEN condition]
corpsTrigger

listeEvenement : liste d’événements séparés par un OR.

événement : DELETE, INSERT ou UPDATE.
Si UPDATE on peut préciser les attributs concernés (UPDATE OF
listeAttributs).
Une exemple simple pour commencer....

Une table d’étudiants (numero, nom, prénom...) avec leur moyenne.

Table etudiant
NumEtudiant Nom Prenom Moyenne
123 DUPONT JULES 4
234 DUPOND ALFRED 5
567 DURAND JULIE 14
598 DURANT ALFREDINE 16

Un trigger qui alerte...

create or replace trigger trigEtud1
before update on etudiant
begin
DBMS [Link] LINE(’Mise à jour de la table etudiant’);
end;
→ pas très intéressant, juste pour commencer!
Trigger de niveau ligne ou de niveau table

Granularité
Le traitement mentionné dans un trigger peut s’effectuer :
pour chaque ligne concernée par l’événement,(trigger de
niveau ligne)
une seule fois pour l’ensemble des lignes concernées par
l’événement(trigger de niveau table).

Ligne
FOR EACH ROW → trigger de niveau ligne, sinon de niveau table.
WHEN condition : pour chaque ligne, trigger déclenché si vraie.
Sur notre exemple....

Un trigger de niveau table qui alerte...

create or replace trigger trigEtud1
before update on etudiant
begin
DBMS [Link] LINE(’Mise à jour de la table etudiant’);
end;

à noter : l’affichage ”Mise à jour de la table...” s’est déclenché

automatiquement lors du ”UPDATE...”.
Le déclencheur trigEtud1 n’est pas appelé explicitement... c’est un espion!
Sur notre exemple....

Un trigger de niveau ligne qui alerte...

create or replace trigger trigEtud2
before update on etudiant FOR EACH ROW
begin
DBMS [Link] LINE(’Mise à jour une ligne de la table etudiant’);
end;

à noter : 1 seul déclenchement de trigEtud1, 2 de trigEtud2, toujours

automatique
Quand se déclenche-t-il et que fait-il...

Quand de déclenche-t-i automatiquement?

BEFORE | AFTER
pour trigger de niveau table : déclenché avant ou après
l’événement
pour trigger de niveau ligne : exécuté avant ou après la
modification de CHAQUE ligne concernée
INSTEAD OF : spécifique aux vues.

Que fait-il?
corpsTrigger : bloc PL/SQL effectué quand le trigger est déclenché
Quelques commandes en plus dans les déclencheurs :
IF INSERTING THEN ... END IF;
IF DELETING THEN ... END IF;
IF UPDATING THEN ... END IF;
Sur notre exemple....

On remplace trigEtud2 par un autre, plus complet :

Un trigger qui alerte un peu plus souvent...

create or replace trigger trigEtud
before insert or update on etudiant FOR EACH ROW
begin
if inserting then
if user!=’AVILNAT’ then
raise application error(-20001,’Utilisateur non autorisé’);
DBMS [Link] LINE(’Utilisateur autorisé : ’||user);
end if;
end if;
if updating then
DBMS [Link] LINE(’Mise à jour une ligne de la table etudiant’);
end if;
end;
Sur notre exemple ...
Sur notre exemple ...pour décortiquer!

On fait un INSERT sur la table etudiant :

→ ” trigEtud se déclenche : if inserting est vrai
→ c’est AVILNAT qui insère :
→ affichage de ”Utilisateur autorisé...”
On fait un UPDATE sur la table etudiant :
→ ” trigEtud1 se déclenche une fois :
→ affichage de ”Mise à jour de la table etudiant”
→ ” trigEtud se déclenche pour chacune des 3 mises à jour : if
updating est vrai
→ affichage de ”Mise à jour une ligne...”
à noter toujours : Aucun déclencheur n’est appelé explicitement...
ils surveillent les actions sur une table et se déclenchent quand
elles se produisent!
Valeurs des attributs

Quelles valeurs sont testées dans un déclencheur?

Dans la clause WHEN ou dans le corps, on peut se référer à la
valeur d’un attribut avant ou après que soit effectuée l’action
déclenchant le trigger :
:[Link] : la valeur avant la transaction UPDATE
ou DELETE
:[Link] : la valeur après la transaction UPDATE
ou INSERT
Exemple de déclencheur de niveau ligne pour garder trace

On crée une table auditEtudiant, mêmes attributs que Etudiant + date

de modification. Usage : garder des traces, pouvoir revenir en arrière...
On enregistre les modifs sur Etudiant avec la date
create or replace trigger traceEtudiant
after update or delete on etudiant FOR EACH ROW
begin
insert into auditEtudiant values
(SYSDATE, :[Link],:[Link],:[Link],:[Link]);
end;
Exemple de déclencheur avec clause WHEN pour alerter

On crée une table Alerte avec : date, numEtudiant et un message

Avec condition en plus...
create or replace trigger moyenneMax
after update on etudiant FOR EACH ROW
when ([Link] > 2∗[Link]) → ici pas de :
begin
insert into alerte values
(SYSDATE, :[Link],’Moyenne a plus que doublé’);
end;
Déclencheurs : exemples

Des déclencheurs sur la table Etudiants ...

Pour “surveiller” les mises à jour et
afficher des messages d’erreur en cas de problème (trigEtud),
bloquer la mise à jour (trigEtud),
alimenter d’autres tables pour soit garder des traces (reportEtudiant),
soit regrouper des alertes (moyenneMax)...
Remerciment

Merci à Prof. Anne Vilnat pour le cours