Programmation sur Oracle

PL/SQL
Procedural Language/SQL

1
Contenu
• Motivation
• Objectifs
• Structure d’un programme
• Variables
• Structures de contrôle
• Interactions avec la base
• Procédure et fonctions
• triggers

2
Motivation
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

3
Objectifs
Définir des « programmes » persistants .
Coté serveur:
– Procédures;
– Fonctions;
– Triggers.
Coté Client:
• Ecrire des programmes utilisateurs en SQL plus.
• Appeler les procédures à partir d’autres langages

4
Caractéristiques
• C’est une Extension de SQL : des requêtes SQL
avec les structures de contrôle habituelles de
la programmation structurée (blocs,
alternatives, boucles)
• Un programme est constitué de procédures
et de fonctions;
• Les variables permettent l’échange
d’information entre les requêtes SQL et le
reste du programme;
5
Normalisation
• PL/SQL n’est pas un langage normalisé
• PL/SQL est un langage propriétaire de Oracle
• Chaque SGBD possède son propre langage
procédural.
• Les langages des différents SGBDs se
ressemblent.
• PostgreSQL utilise PL/pgSQL, un langage très
proche du PL/SQL

6
Structure
Un programme PL/SQL est structuré en blocs
d’instructions. Chaque bloc comprend une section
de déclaration de variables, et un ensemble
d'instructions dans lequel les variables déclarées
sont visibles.
Un bloc peut contenir d’autres blocs. Chaque bloc
comporte trois sections :
1. déclaration
2. corps du bloc (Obligatoire)
3. traitement des erreurs

7
Structure
Structure d’un bloc
DECLARE
/*déclaration de variables: optionnelle */
BEGIN
/*instructions sql et pl/sql à exécuter */

EXCEPTION
/*Actions à faire en cas d’erreur: optionnel*/
END;
/  obligatoire lors de l’exécution du script

8
Structure
Un script écrit en PL/SQL se termine
obligatoirement par un /, sinon SQL+ ne
l'interprète pas.
S'il contient des erreurs de compilation, il est
possible d'afficher les messages d'erreur avec la
commande SQL+ : SHOW ERRORS

9
Structure
Structure d’un bloc
Un programme PL/SQL peut être structuré de 3
types, en:
• procédure anonyme;
• procédure nommée;
• fonction nommée.
Les procédures/fonctions nommées permettent
de réutiliser le code.

10
Variables
• PL/SQL n’est pas sensible à la casse tout
comme SQL
• Les commentaires
-- un commentaire monoligne
/* commentaire multilignes */

11
Variables
• Identificateurs :
– Doit avoir moins de 30 caractères ;
– commence tjrs par une lettre
– peut contenir lettres, chiffres, _, $ et #
• Portée habituelle des langages à blocs
• Les variables doivent être déclarées avant
d’être utilisées.

12
Variables
Types
Les variables peuvent être de type:
• Scalaire, recevant une seule valeur de
– type SQL (char, DATE, number, …)
– type PL/SQL (integer, Boolean, Real, …)
• Composé (adaptés aux colonnes, lignes et
tables SQL (RECORD, table, …)

13
 Variables
Déclaration
Identificateur [CONSTANT] type [NOT NULL] [:= valeur];
Exemple:
• num NUMBER(4) ;
• num2 NUMBER NOT NULL := 3.5 ;
• en_stock BOOLEAN := false ;
• limite CONSTANT REAL := 5000.00 ;
• Nom varchar(30);
• Age integer ;
La contrainte NOT NULL doit être suivie d’une
clause d’initialisation
14
Variables
Exemple
SET SERVEROUTPUT ON
DECLARE
x VARCHAR2(10);
BEGIN
x := 'Bonjour';
DBMS_OUTPUT.PUT_LINE(x);
END;
/
15
Variables
Déclaration de type implicites
Avec %TYPE, on peut déclarer qu’une variable
est du même type qu’une colonne d’une table
ou d’une autre variable.
Syntaxe:
nomVariable nomTable.nomAttribut%type:
nomAttribut%type Signifie du même type que
nomAttribut

16
Variables
Déclaration de type implicites
Exemple
• V1 vins.cru%type;
-- v1 du même type que la colonne cru de la
table vins,
• V2 v1%type;
-- v2 est de même type de v1

17
Variables
Déclaration du type record
• Déclaration du type enregistrement
TYPE nomDuType IS RECORD (
nomAttribut1 typeAttribut1,
nomAttribut2 typeAttribut2,
...);
• Déclaration de la variable de type record
nomVariable nomDuType;
18
Variables
Déclaration du type record
Quand tous les attributs sont d’un type SQL, une
variable de type record peut représenter une ligne
d’une table relationnelle.
Exemple
Type PERSONNE_REC is record(
nom VARCHAR2(15),
prenom VARCHAR2(20)
);
P PERSONNE_REC;
P.nom=‘TOE’
19
Variables
Déclaration du type record
Type implicites
Avec %rowtype On peut déclarer une variable
record de même type qu’une ligne d’une table.
Exemple
Emp_rec employe@rowtype;
Emp_rec est de type record et ses attributs
suivent le schéma de la table employe

20
21
Variables
Affectations
Plusieurs façons de donner une valeur à une
variable :
• Opérateur d’affectation :=
Exemple
Valeur := prix * taxe;
• par la directive INTO de la requête SELECT
Il affecte à la variable une valeur issue d’une
requête sur la base.
22
Variables
Affectations
Exemple
SELECT nom INTO MonClient
from Clients
where num= 509;
Pour une requête dont le résultat est constitué
d’une unique ligne, on peut utiliser la syntaxe
SELECT ... INTO....
Pour une requête qui ramène un nombre
quelconques de lignes, il faut utiliser un curseur.
23
 Exemple
DECLARE
Enreg_vin vin%rowtype;
degrvin number(3,1) := 12.5 ;
BEGIN
Enreg_vin.num := 5;
Enreg_vin.cru := ‘’Muscade’’;
Enreg_vin.annee :=‘’1995’’;
Enreg_vin.degre := degrvin;
INSERT INTO vin values Enreg_vin;
END;

24
Variables
Expressions et opérateurs
Les opérateurs de SQL sont valides en PL/SQL
Un opérande est une variable, une constante,
ou un appel de fonction.

25
Variables
Affichage
Dans SQL+, on peut activer ou désactiver
l’affichage avec la commande
SET SERVEROUTPUT ON (active)
SET SERVEROUTPUT OFF (désactive)

Par défaut, les fonctions d’affichage sont

26
 Variables
Affichage
Pour afficher le contenu d'une variable, on utilise
les procédures DBMS_OUTPUT.PUT() et
DBMS_OUTPUT.PUT_LINE() qui prennent en
argument une valeur à afficher ou une variable.
Syntaxe:
DBMS_OUTPUT.PUT_LINE(<chaîne>)
Le symbole I I permet de concaténer deux chaines
DBMS_OUTPUT.PUT_LINE(<chaîne1> || <chaîne2> )

27
Variables
Affichage
DECLARE
ma_variable SMALLINT := 1;
BEGIN
DBMS_OUTPUT.PUT_LINE('La valeur est '||
TO_CHAR(ma_variable));
END;
To_char : permet de convertir la type en chaine
de caractère
28
Variables
Pour passer à un bloc PL/SQL des variables définis sous
SQL*Plus :
SQL> ACCEPT nomcli PROMPT ‘Nom client : ’
SQL> ACCEPT prenomcli PROMPT ‘son prenom : ’
SQL> ACCEPT villecli PROMPT ‘sa ville : ’
DECLARE
numclient NUMBER := 3;
BEGIN
INSERT INTO client VALUES (‘numclient’,
‘&nomcli’, &prenomcli, &villecli);
END;
/
29
30
Structures de contrôle
Structure alternative
Traitement alternatif
IF /*condition * /
THEN
/* instructions1* /
ELSE
/*instructions2 */
END IF ;
31
 Structures de contrôle
Structure alternative
Exemple:
DECLARE
a number(3) := 20;
BEGIN
IF ( a = 10 )
THEN
dbms_output.put_line(‘La valeur est 10');
ELSE
dbms_output.put_line(‘La valeur est autre');
END IF;
dbms_output.put_line(‘Valeur exacte : '|| a );
END;
/

32
Structures de contrôle
Structure alternative
Ecrire un programme PL/SQL qui lit le degré du
vin numéro 5 et affiche « Vin alcoolisé » si le
degré > 2 ou « Vin non alcoolisé» si le degré est
inférieur à 5.

33
 Structures de contrôle
Structure alternative
IF condition1
THEN instructions1;
ELSIF condition2
THEN instructions2;
ELSE instructions3
END IF;
/
34
Structures de contrôle
Traitement itératif
LOOP
/*Instructions*/
END LOOP
On peut quitter la boucle à condition
LOOP
/*instructions*/
EXIT WHEN /*condition*/ ;
END LOOP;
35
Structures de contrôle
Traitements itératifs
Exemple
DECLARE
valeurEntiere INTEGER := 1;
produitSerie INTEGER := 1;
BEGIN
LOOP
produitSerie := produitSerie * valeurEntiere;
valeurEntiere := valeurEntiere + 1;
EXIT WHEN valeurEntiere >= 10;
END LOOP;
END;
36
Structures de contrôle
Traitements itératif
La boucle FOR existe aussi en PL/SQL
FOR /*variable*/ IN /* debut*/ .. /*fin*/
LOOP
/*instructions*/
END LOOP;
Exemple :
for i IN 1..100 LOOP
somme := somme + i;
end loop;
37
Structures de contrôle
Traitement itératif
La boucle while aussi
WHILE /*Condition*/
LOOP
/*instructions*/
END LOOP;

38
Langage PL/SQL
Exemple
DECLARE
valeurEntiere INTEGER := 1;
produitSerie INTEGER := 1;
BEGIN
WHILE (valeurEntiere <= 10) LOOP
produitSerie := produitSerie * valeurEntiere;
valeurEntiere := valeurEntiere + 1;
END LOOP;
END;

39
Tableaux
Définition
Les types tableau doivent être définis explicitement par
une déclaration de la forme:
TYPE /*nomype*/ IS VARRAY (/*taille*/ ) OF /* type
Elements*/ ;
• Type: nom du type tableau;
• Taille : le nombre maximal d’éléments
• TypeElements : type des éléments qui vont être
stockés dans le tableau.
Type numTab IS VARRAY (10) of NUMBER;
40
Tableaux
Tableau
DECLARE
TYPE numTab IS VARRAY (10) OF NUMBER;
t numTab ;
BEGIN
t = numTab();
/*instructions* /
END;
/
Le type d’un tableau peut être utilisé au même titre
que number ou varchar2.
41
Interactions avec la base
Les scripts PL/SQL permet d’interagir avec
une base de données.

42
 Interactions avec la base
Extraire des données
SELECT expr1, expr2,… INTO var1, var2,…
Il permet de mettre des valeurs expr1, expr2, …
exprn de la BD dans plusieurs variables, var1,
var2, …varn.

43
 Interactions avec la base
Extraire des données
Le select ne doit renvoyer qu’une seule ligne.
• Si le select renvoie plus d’une ligne, une
exception «TOO_MANY_ROWS » est levée;
• Si le select ne renvoie aucune ligne, une
exception «NO_DATA_FOUND» est levée.

44
 Interactions avec la base
Extraire des données
DECLARE
v_nom clients.nom%TYPE;
v_emp clients%ROWTYPE;
BEGIN
SELECT nom INTO v_nom
FROM clients
WHERE num = 5;
SELECT * INTO v_emp
FROM clients
WHERE num = 2;
END;
45
 Interactions avec la base
Modification de données
Les requêtes SQL (insert, update, delete) peuvent
utiliser les variables PL/SQL.
Les commit et rollback doivent être explicites ;
aucun n’est effectué automatiquement à la
sortie d’un bloc.

46
 Modification de données
Exemple1 :
DECLARE
v_cl clients%ROWTYPE;
v_nom clients.nom%TYPE;
BEGIN
v_nom := ‘HiEN';
INSERT INTO clients (num, nom) values(600, v_nom);
v_cl.num := 610;
v_cl.nom := ‘SAM';
INSERT INTO clients (num, nom) values(v_cl.num, v_cl.nom);
END;
/

47
 Modification de données
Exemple 2:
DECLARE
v_emp vins%ROWTYPE;
BEGIN
SELECT * INTO v_emp FROM vins
WHERE num =24;
v_emp.degre := v_emp.degre + 5;
v_emp.cru:= ‘Etoile';
INSERT INTO vins VALUES v_emp;
END;
48
 Gestions des Exceptions
Le mécanisme des exceptions implémenté dans
la plupart des langages récent, notamment
orientés objet a des avantages:
• obliger les programmeurs à traiter les erreurs
• Rattraper les erreurs en cours d’exécution
• Ecrire le traitement des erreurs à part

49
 Gestions des Exceptions
DECLARE
/∗ declarations ∗/
BEGIN
/∗ instructions ∗/
EXCEPTION
/∗ traitement des erreurs ∗/
END;
Une exception est une « erreur type » qui a un
nom. Certaines exceptions n’ont pas de nom. Elle
ont seulement un code d’erreur.

50
 Gestions des Exceptions
Il y a deux types d’exception:
• prédéfinie
• Définie par le développeur
Exemple d’exception prédéfinies
NO_DATA_FOUND
TOO_MANY_ROWS
VALUE_ERROR (erreur arithmétique)
ZERO_DIVIDE
51
 Gestions des Exceptions
Traitement
On énuméré les erreurs les plus pertinentes en utilisant leur
nom et en consacrant `a chacune d’elle un traitement
particulier pour rattraper l’erreur.
EXCEPTION
WHEN E1 THEN
/∗ traitement ∗/
WHEN E2 THEN
/∗ traitement ∗/
WHEN E3 THEN
/∗ traitement ∗/
WHEN OTHERS THEN
/∗ traitement . C’est optionnel ∗/
END; 52
Traitement
Exemple
DECLARE
vclt VARCHAR2(25);
nomclt VARCHAR2(25) := ’Pare’ ;
BEGIN
SELECT ville INTO vclt
FROM CLIENTS
WHERE nomclt = nom;
DBMSOUTPUT.PUT LINE( ’Le client ’ || nomclt || ’ vient de la ville de ’
|| vclt);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMSOUTPUT.PUT LINE( ’Aucun client ne vient de ’ || vclt);
WHEN TOO_MANY_ROWS THEN
DBMSOUTPUT.PUT LINE( ’Plusieurs clients viennent de nom ’|| vclt);
WHEN OTHERS THEN
DBMSOUTPUT.PUT LINE( ’ Il y a un gros problème ... ’ );
END;

53
 Gestions des Exceptions
Exception de l’utilisateur
Les exceptions sont déclenchées par le serveur
de base de données automatiquement chaque
fois qu'il y a une erreur, mais des exceptions
peuvent être soulevée explicitement par le
programmeur en utilisant la commande RAISE.
Voici la syntaxe simple de lever une exception

54
 Gestions des Exceptions
Exception de l’utilisateur
ECLARE
nom_exception EXCEPTION;
BEGIN
IF condition THEN
RAISE nom_exception;
END IF;
EXCEPTION
WHEN nom_exceptionTHEN
/*traitement*/
END;

55
 Gestions des Exceptions
Exception de l’utilisateur.
Pour déclencher une erreur qui n’est pas liée à
une exception, utiliser l’instruction :
raise_application_error(num_erreur, message);
• num_erreur est un entier négatif compris
entre −20999 et −20000 (codes réservés aux
erreurs de l’utilisateur)
• message est une chaine de caractères.
voir trigger
56
Curseur
L'intérêt du curseur est donc de pouvoir
récupérer (par un SELECT) plusieurs lignes
contrairement au SELECT normal en
PL/SQL.
La technique du curseur permet de
parcourir une à une les lignes du résultat
d'une requête.

57
Curseur
Principe de fonctionnement
1. déclarer le curseur (avec son SELECT)
2. ouvrir le curseur (on n'est pas obligé de
l'ouvrir tout de suite)
3. récupérer les lignes une à une, en
commençant par la première
4. Refermer le curseur

58
Curseur
1-Déclaration
CURSOR <nom_du_curseur> IS
SELECT ... ;

Le SELECT est ici identique au SELECT de

59
Curseur
2-Ouverture
OPEN <nom_du_curseur> ;
À l'issue de cette ouverture du curseur, on a
une ou plusieurs lignes de données
accessibles.

60
Curseur
3-Récupération des lignes dans des variables
FETCH <nom_du_curseur> INTO
<variable1>,<variable2> ;
Il doit y avoir autant de variables qu'on a de
champs interrogés par le SELECT.
FETCH <nom_du_curseur> INTO
<varRecord> ;

61
Curseur
3-Récupération des lignes dans des variables
A l’ouverture du curseur, c'est la première
ligne qui est pointée. Ensuite, à chaque
FETCH, on passe à la ligne suivante.
Si on veut parcourir toutes les lignes utiliser
la boucle FOR.
%ROWCOUNT permet de savoir le nombre
de lignes déjà récupérées par FETCH

62
Curseur
3-Récupération des lignes dans des variables
Il existe d’autres attributs du curseur:
%found (vrai si le dernier fetch a ramené une
ligne)
%notfound (vrai si le dernier fetch n’a pas
ramené de ligne)
%isopen (vrai si le curseur est ouvert)
%rowcount (le nombre de lignes déjà
ramenées)

63
Curseur
4-Fermeture
CLOSE <nom_du_curseur> ;
Cette instruction permet de fermer le
curseur

64
Curseur
Exemple
DECLARE
CURSOR salaires IS
select sal from emp where dept = 10;

salaire numeric(8, 2);

65
Curseur
BEGIN
open salaires;
loop
fetch salaires into salaire;
exit when salaires%notfound;
total := total + salaire;
DBMS_OUTPUT.put_line(total);
end loop;
close salaires;
DBMS_OUTPUT.put_line(total);
END;

66
Curseur
Déclaration implicite
Les curseurs implicites sont tous nommés SQL

67
Curseur
Déclaration implicite
Exemple:
DECLARE
nb_lignes integer;
BEGIN
delete from emp
where dept = 10;
nb_lignes := SQL%ROWCOUNT;
68
69
Procédure et fonctions
Le plus souvent, on crée une procédure ou une
fonction nommée pour réutiliser le code au lieu
d’un bloc PL/SQL anonyme.
Les procédures et fonctions peuvent être
utilisées dans d’autres procédures /fonctions /
blocs PL/SQL anonymes

70
Procédure et fonctions
Définition
Une procédure / fonction est un programme
contenant des instructions SQL précompilées et
enregistré dans la base (dictionnaire)
Comme une table ou une vue, elles peuvent être
utilisées par d'autres utilisateurs, s'ils ont les
droits voulus.

71
Procédure
Les procédures stockées offrent de gros
avantages pour les applications client/serveur:
• Réduction du trafic réseau car les clients
envoient juste le nom de la procédure et ses
paramètres au serveur
• La gestion/maintenance des procédures sont
facilitées car elle sont enregistrées sur le
serveur et pas dispersées chez les clients.

72
 Procédure
On définit une procédure de la façon suivante:
CREATE [OR REPLACE] PROCEDURE proc_name
[ (parameter [,parameter]) ] IS
/*Declaration de variable!*/
BEGIN
/*Instruction*/
END;
Les paramètres sont une simple liste de couples
nom type.
73
 Procédure
Exemples
create or replace procedure hello IS
BEGIN
dbms_output.put_line('Hello World!');
END;
Compilation
Sous SQL+, il faut taper une dernière ligne
contenant « / » pour compiler une
procédure ou une fonction.

74
 Procédure
Exemples
CREATEOR REPLACE PROCEDURE compteARebours
(n NUMBER) IS
BEGIN
IF n>= 0 THEN
DBMSOUTPUT.PUT LINE(n);
compteARebours(n− 1);
END IF;
END;
75
 Procédure
Utilisation
On appelle de la procédure avec son nom.
Dans sql+, on utilise le mot-clé call.
SQL> call compteARebours(20)

Supprimer
DROP PROCEDURE procedure_name;

76
 Procédure
Il y a trois types de paramètre:
IN: Le paramètre est passé par valeur ce qui
signifie la procédure ne peut le modifier (Par
défaut c’est le type IN).
OUT: Sert de valeur de retour,
IN OUT: Le paramètre est passé par
référence le procédure modifier sa valeur.

77
 Procédure
Exemple
CREATE OR REPLACE PROCEDURE nbProdtRegion(reg in
producteurs.region%type, nbprod out number) IS
BEGIN
select count(*) into nbprod
from producteurs
where region = reg;
dbms_output.put_line(’nombre de producteurs du la
region de ’ ||reg|| ’: ’ || nbprod );
END;

78
 Procédure
Exercice:
Ecrire une procédure trouverMin qui prend 3
paramètres : les 2 premiers par valeurs dont elle va
trouver le min et mettre le dans le 3e paramètre
passé par références.

79
 Fonction
Définition
On définit une fonction de la façon suivante:
CREATE [OR REPLACE] FUNCTION nom_funct [
(parameter [,parameter]) ]
RETURN type_retourne IS
/*Déclaration de variable*/
BEGIN
/*instructions*/
END [function_name];
80
 Fonction
Exemple
CREATE OR REPLACE FUNCTION module ( a NUMBER, b NUMBER)
RETURN NUMBER IS
BEGIN
IF a < b THEN
RETURN a ;
ELSE
RETURN module ( a - b , b ) ;
END IF;
END;

declare
m number(4);
Begin
m := module(21,12);
dbms_output.put_line('Modulo de 21 et 12'||m);
end;
81
 Fonction
Exercice
1. Ecrire une fonction qui retourne le max de deux
nombre donnés en paramètre.
2. Ecrire une fonction qui calcul et retourne le
pgcd de deux entier passé en paramètre
3. Ecrire une fonction qui affiche chaque cru avec
la quantité d’alcool par litre qu’il contient. Le
degré correspond au % d’accool dans le cru et
on sait qu’1litre d’alcool pure=800g.
82
 Fonction
Utilisation
Les procédures et fonctions peuvent être
utilisées dans d’autres procédures ou
fonctions ou dans des programmes PL/SQL.

83
 Fonction
Erreurs de compilation et sources
Si le programme ne compile pas, « show errors »
permet de voir les erreurs sous SQLPlus (Au cas où
vous feriez de la programmation sans environnement
adapté SQLDeveloper).
Certaines vues du dictionnaire donnent des
informations sur ces Objets(fonctions, procedures,
triggers):
• la vue USER_OBJECTS donne tous les objets et en
particulier les programmes stockés .
• la vue USER_SOURCE donne accès aux sources des
programmes
84
85
 Triggers (déclencheurs)
Principe
Un trigger est comme une procédure stockée dans
la base qui est exécuté automatiquement
lorsqu’un événement particulier sur la base se
produit.
La plupart du temps les triggers sont déclenchés
par la modification du contenu d’une table.

86
Triggers ou déclencheurs
Principe
Les triggers complètent les contraintes d’intégrité
en permettant des contrôles de traitement plus
complexes. Ils permettent également de générer
automatiquement certaines valeurs issues d’une
colonne, prévenir des transactions invalides;
Événements déclencheurs :
• Instructions LMD et LDD
• Démarrage ou arrêt de la base
• Connexion ou déconnexion d’utilisateur,…

87
 Triggers
Syntaxe
CREATE [ OR REPLACE ] TRIGGER trigger_name
{BEFORE |AFTER} {INSERT|INSERT|DELETE}
[OF col_name] ON table_name [ FOR EACH ROW ]
[WHEN (condition)]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION WHEN ...
-- exception handling
END;
88
 Triggers
Syntaxe
[ FOR EACH ROW ] : permet de spécifier le déclencheur au
niveau des lignes. Ainsi le trigger est exécuté pour chaque
ligne où l’évenement se produit.
Sans [ FOR EACH ROW ] le trigger sera exécuté juste une
fois quand l’évenement SQL se produit. On dit que le
trigger est niveau de la table.
WHEN (condition): fournit la condition sur les lignes pour
lesquels le trigger doit s’exécuter. Il n’est utilisé que pour
les triggers de niveau ligne.

89
 Triggers
Dans un trigger de type [FOR EACH ROW], il est
possible avant la modification de chaque ligne, de
lire l’ancienne(s) valeur(s) et la nouvelle (s)
valeur(s) par l’intermédiaire des deux variables
prédéfinies :old et :new.
Un Trigger peut être défini sur une table, une vue,
ou une base avec laquelle l’évènement est associé.

90
 Triggers
Exemple
CREATE OR REPLACE TRIGGER limite_Ajout_Vin
BEFORE INSERT on vins
begin
raise_application_error(-20555, 'Impossible limite d'ajout
de vin atteint');
end;

91
 Triggers
Activation/suppression
Par défaut, les triggers d’oracle sont activés dès leur
création.

• ALTER TRIGGER nom_trigger ENABLE

• ALTER TRIGGER nom_trigger DISABLE

• DROP TRIGGER nom_Trigger

92
 Triggers
Exemple
CREATEOR REPLACE TRIGGER afficheEvenement
BEFORE INSERTORUPDATEORDELETEON CLIENTS
FOR EACH ROW
BEGIN
IF INSERTING THEN
DBMSOUTPUT.PUT LINE( ’Insertion dans CLIENT’ );
ELSIF UPDATING THEN
DBMSOUTPUT.PUT LINE( ’Mise a jour dans CLIENT’ );
ELSE
DBMSOUTPUT.PUT LINE( ’Suppression dans CLIENT’ );
END IF;
END 93
 Triggers
Exemple
CREATE OR REPLACE TRIGGER Salaire_Variation BEFORE
DELETE OR INSERT OR UPDATE ON employe
FOR EACH ROW
WHEN (NEW.ID > 0)
DECLARE
sal_diff number;
BEGIN
sal_diff := :NEW.salary - :OLD.salary;
dbms_output.put_line(‘Ancien salaire: ' || :OLD.salary);
dbms_output.put_line(‘Nouv salaire: ' || :NEW.salary);
dbms_output.put_line(‘Augmentation: ' || sal_diff);
END;
/ 94
 Triggers
Exercices
• Ecrire un trigger promotion qui empêche toute
baisse de salaire.
• Ecrire un trigger qui empêche l’insertion de vins
doux (inférieur à 2%) dans la base.
• Ecrire un trigger qui empêche un client de
commander plus 3 bouteilles de vins par jour.

95
96
 Interaction avec BD
Curseur
Le curseur déclaré explicitement permet de
parcourir une à une les lignes du résultat
d'une requête.
L'intérêt est donc de pouvoir récupérer
(par un SELECT) plusieurs lignes, ce que ne
permet pas le SELECT normal en PL/SQL.

97
 Interaction avec BD
Curseur
Les curseurs sont des zones tampon
utilisées pour tout SELECT multiligne.
Le curseur peut être implicite (pas déclaré
par l’utilisateur) ou explicite.
Les curseurs implicites sont tous nommés
SQL
Les curseurs explicites servent à retourner
plusieurs lignes avec un select.
98
 Interaction avec BD
Curseur implicite
Avec un curseur implicite, on peut obtenir des
informations sur la requête réalisée, grâce aux
attributs :
SQL%attribut applique l’attribut sur la
dernière requête SQL exécutée.
SQL%rowcount : nombre de lignes.
SQL%found : TRUE si la dernière instruction a
au moins 1 ligne.
SQL%notfound: True si aucune ligne.
99
 Interaction avec BD
Curseur implicite
DECLARE
nb_lignes integer;
BEGIN
delete from emp
where dept = 10;
nb_lignes := SQL%ROWCOUNT;
Nb_lignes : contiendra le nombre d’employé
supprimés de la table.
100
 Interaction avec BD
Curseurs
A chaque ordre SELECT on associe un
curseur nommé et on y fait référence dans
la suite du programme pour traiter les
lignes les unes après les autres jusqu'à la
fin de la table.

101
 Interaction avec BD
Curseurs
Déclaration et utilisation
1. Déclaration du curseur (avec son select)
2. Ouverture
3. Récupération des lignes une à une, en
commençant par la première
4. Fermeture

102
 Interaction avec BD
Curseurs
Syntaxe
CURSOR <nom_du_curseur> IS SELECT ... ;
Le SELECT est ici identique au SELECT de
SQL.
Ouverture
• OPEN <nom_du_curseur> ;
Après cette ouverture du curseur, on a une
ou plusieurs lignes de données accessibles.
103
Curseurs
Récupération
• FETCH <nom_du_curseur> INTO
<variable1>,<variable2> ;
Il doit y avoir autant de variables qu'on a de
champs interrogés par le SELECT.
• FETCH <nom_du_curseur> INTO
<enregistrement> ;
Fermeture
• CLOSE <nom_du_curseur> ;
104
 Interaction avec BD
Exemple 1
DECLARE
CURSOR mesclients IS
SELECT * FROM clients
WHERE ville = ‘ouaga';
mon_client clients%ROWTYPE;
BEGIN
OPEN mesclients;
LOOP
FETCH mesclients INTO mon_client;
DBMS_OUTPUT.PUT_LINE(mon_client.nom);
EXIT WHEN mesclients%NOTFOUND;
END LOOP;
CLOSE mesclients;
END;

105
 Interaction avec BD
Exemple 2
La boucle FOR se charge de l’ouverture, de la lecture des
lignes du curseur et de sa fermeture. C’est plus simple?
DECLARE
CURSOR clientCur IS SELECT ∗ FROM clients;
ligne clients%rowtype;
BEGIN
FOR ligne IN clientCur LOOP
DBMSOUTPUT.PUT LINE(ligne .nom);
END LOOP;
END;

106
 Interaction avec BD
Attributs des curseurs
Tous les curseurs ont des attributs. On peut récupérer des
informations sur un curseur par l'intermédiaire des
attributs suivants :
<nom_du_curseur>%ISOPEN : retourne TRUE ou FALSE.
<nom_du_curseur>%FOUND : retourne TRUE ou FALSE,
vrai si au moins une ligne a été traitée par la requête
<nom_du_curseur>%NOTFOUND : retourne TRUE ou
FALSE à l'inverse de %FOUND
<nom_du_curseur>%ROWCOUNT : retourne le nombre
de lignes traitées par le curseur.

107
Attributs des curseurs
Exemple 1
IF NOT moncurseur%ISOPEN
OPEN moncurseur;
END IF;
FETCH moncurseur INTO variable1,variable2;
WHILE moncurseur%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(variable1)
||TO_CHAR(variable2));
FETCH moncurseur INTO variable1,variable2;
END LOOP;
CLOSE moncurseur;

108
Attributs des curseurs
Exemple 3
SET SERVEROUTPUT ON
DECLARE
CURSOR moncurseur IS
SELECT champ1,champ2 FROM table;
variable1 table.champ1%TYPE;
variable2 table.champ2%TYPE;
BEGIN
OPEN moncurseur;
LOOP
FETCH moncurseur INTO variable1,variable2;
EXIT WHEN NOT moncurseur%FOUND;
END LOOP;

DBMS_OUTPUT.PUT_LINE(TO_CHAR(moncurseur%ROWCOUNT));
CLOSE moncurseur;
END;

109
110
 Transaction
Le système des transactions est l’un des
mécanismes les plus puissants des SGBD
récents.
Une transaction est un ensemble d'opérations
« atomiques », c'est-a-dire indivisible.

111
 Transaction
Transaction
On considère qu'un ensemble d'opérations est
indivisible si une exécution partielle de ces
instructions poserait des problèmes d'intégrité
dans la base de données.
Par exemple, dans le cas d'une base de données
de gestion de comptes en banque, un virement
d'un compte a un autre se fait en deux temps.
Débiter et créditer.
112
 Transaction
Transaction
Les instructions COMMIT et ROLLBACK permet
d’assurer l’exécution d’un ensemble d’opérations
/*Instruction*/
IF /*Erreur*/ THEN
ROLLBACK;
ELSE ROLLBACK annule toutes les modifications faites
COMMIT; depuis le début de la transaction (depuis le
précèdent COMMIT),
END; COMMIT les enregistre définitivement dans la
base de données.
113
 Transaction
Transaction
La variable d'environnement AUTOCOMMIT,
permet d'activer la gestion des transactions.
AUTOCOMMIT ON: chaque instruction a des
répercussions immédiates dans la base.
AUTOCOMMIT OFF: les modifications ne sont
effectives qu'une fois qu'un COMMIT a été
exécute.

114
Exercices
Quel est le résultat de l’exécution suivante
Create table tab (A number );
Insert into tab (4);
Insert into tab (3);
Insert into tab (2);
Commit;
Insert into tab (7);
Insert into tab (6);
Rollback;
Select sum(a) from tab;

115