PL/SQL
Mehdi HAJJI
SGBD – II2
Plan du cours
Introduction
Structure d’un programme
Les variables
Les structures de contrôles
Interactions simples avec une Base de données
Les curseurs
Les exceptions
Les procédures et les fonctions
Les triggers
Les packages
PL/SQL
-M. HAJJI-
2
Introduction
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
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 (les boucles et
les alternatives)
Extension de SQL : des requêtes SQL cohabitent avec les
structures de contrôle habituelles de la programmation
structurée (blocs, alternatives, boucles)
PL/SQL
-M. HAJJI-
3
Introduction
Un programme est constitué de procédures et de
fonctions
Des variables permettent l’échange d’information entre
les requêtes SQL et le reste du programme
PL/SQL
-M. HAJJI-
4
Structure d’un programme
Blocs
Un programme est structuré en blocs d’instructions de 3
types:
procédures anonymes
procédures nommées
fonctions nommées
Un bloc peut contenir d’autres blocs
PL/SQL
-M. HAJJI-
5
Structure d’un programme
Structure d’un bloc
DECLARE
-- définitions de variables
BEGIN
END;
/
-- Les instructions à exécuter
EXCEPTION
-- La récupération des erreurs
PL/SQL
-M. HAJJI-
6
Les variables
7
PL/SQL
-M. HAJJI-
Variables
Identificateur Oracle:
30 caractères au plus
Commence par une lettre
Peut contenir chiffres, lettres, _, $ et #
Pas sensible à la casse
Portée habituelle des langages à blocs
Doivent être déclarées avant d’être utilisées
PL/SQL
-M. HAJJI-
8
Commentaires
-- pour une fin de ligne
/* pour plusieurs
lignes */
PL/SQL
-M. HAJJI-
9
Déclaration d’une variable
identifiant [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr];
Declare
v_hiredate DATE;
v_deptno NUMBER(2) NOT NULL := 10;
v_location VARCHAR2(13) := 'Atlanta';
v_deptno NUMBER(2) NOT NULL := 10;
v_location VARCHAR2(13) := 'Atlanta';
c_comm CONSTANT NUMBER := 1400;
c_comm CONSTANT NUMBER := 1400;
Déclarations multiples interdites :
i, j integer;
PL/SQL
-M. HAJJI-
10
Variables: L’attribut %TYPE
Déclarer une variable à partir de :
la définition d’une colonne de la base de données
la définition d’une variable précédemment déclarée
Préfixer %TYPE avec :
La table et la colonne de la base
Le nom de la variable précédemment déclarée
...
v_ename emp.ename%TYPE;
v_balance NUMBER(7,2);
v_min_balance v_balance%TYPE := 10;
...
PL/SQL
-M. HAJJI-
11
Variables: L’attribut %ROWTYPE
Une variable peut contenir toutes les colonnes d’une
ligne d’une table
...
employe emp%ROWTYPE;
...
déclare que la variable employe contiendra une ligne de la
table emp
PL/SQL
-M. HAJJI-
12
Entrée/Sortie
Une alternative pour l’affichage de données à partir d’un
bloc PL/SQL
DBMS_OUTPUT.PUT_LINE
dbms_output.put_line('nb = ' || nb);
Doit être autorisée sous SQL*Plus à l’aide de :
SET SERVEROUTPUT ON
Pour saisir des données
Accept
Accept nom_variable PROMPT ‘Saisir variable : ‘;
PL/SQL
-M. HAJJI-
13
Entrée/Sortie
Exemple
set serveroutput on
accept vstring prompt "Please enter your name: ";
declare
v_line varchar2(40);
begin
v_line := 'Hello '||'&vstring';
dbms_output.put_line(v_line);
end;
Advertisement
/
PL/SQL
-M. HAJJI-
14
Exemple
employe emp%ROWTYPE;
nom emp.nome%TYPE;
…
SELECT * INTO employe
FROM emp
WHERE matr = 900;
nom := employe.nome;
employe.dept := 20;
…
INSERT INTO emp
VALUES employe;
PL/SQL
-M. HAJJI-
15
Type RECORD
Equivalent à struct du langage C
TYPE nomRecord IS RECORD (
champ1 type1,
champ2 type2,
…);
Exemple
TYPE emp2 IS RECORD (
matr integer,
nom varchar(30));
employe emp2;
employe.matr := 500;
PL/SQL
-M. HAJJI-
16
Affectation
Plusieurs façons de donner une valeur à une variable :
n :=
n par la directive INTO de la requête SELECT
Exemples :
dateNaissance := ‘10/10/2004’;
SELECT nome INTO nom
FROM emp
WHERE matr = 509;
PL/SQL
-M. HAJJI-
17
Exercices
Ecrire un programme PL/SQL qui permet d’afficher les
données d’un joueur dont le numéro est saisi par
l’utilisateur.
Ecrire un programme PL/SQL qui permet d’afficher les
informations d’un joueur, la prime et le sponsor pour un
numéro, un tournoi et une année saisis.
PL/SQL
-M. HAJJI-
18
Les structures de contrôles
19
PL/SQL
-M. HAJJI-
Structures conditionnelles
IF condition THEN
instructions;
END IF;
IF condition THEN
instructions1;
ELSE
END IF;
instructions2;
IF condition1 THEN
instructions1;
ELSEIF condition2 THEN
instructions2;
ELSEIF …
…
ELSE
instructionsN;
END IF;
PL/SQL
-M. HAJJI-
20
Structure à choix multiples
CASE expression
WHEN expr1 THEN instructions1;
WHEN expr2 THEN instructions2;
…
ELSE instructionsN;
END CASE;
PL/SQL
-M. HAJJI-
21
Structures répétitives
Boucle « Tant que »
WHILE condition LOOP
instructions;
END LOOP;
Boucle générale
LOOP
instructions;
EXIT [WHEN condition];
instructions;
END LOOP;
Boucle « pour »
FOR compteur IN [REVERSE] inf..sup LOOP
FOR i IN 1..100 LOOP
instructions;
END LOOP;
somme := somme + i;
END LOOP;
PL/SQL
-M. HAJJI-
22
Interactions simples avec une Base
de données
23
PL/SQL
-M. HAJJI-
Interactions avec la base de données
select expr1, expr2,… into var1, var2,… met des valeurs
de la BD dans une ou plusieurs variables
Le select ne doit renvoyer qu’une seule ligne
Avec Oracle il n’est pas possible d’inclure un select sans «
into » dans une procédure ;
PL/SQL
-M. HAJJI-
24
Exemple
DECLARE
BEGIN
v_nom emp.nome%TYPE;
v_emp emp%ROWTYPE;
SELECT nome INTO v_nom
FROM emp
WHERE matr = 500;
SELECT * INTO v_emp
FROM emp
WHERE matr = 500;
PL/SQL
-M. HAJJI-
25
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
Plus de détails pour l’insertion de données:
PL/SQL
-M. HAJJI-
26
Exemple
DECLARE
Advertisement
v_emp emp%ROWTYPE;
v_nom emp.nome%TYPE;
BEGIN
v_nom := 'Dupond';
insert into emp (matr, nome)
values (600, v_nom);
v_emp.matr := 610;
v_emp.nome := 'Durand';
insert into emp (matr, nome)
values (v_emp.matr, v_emp.nome);
commit;
END;
PL/SQL
-M. HAJJI-
27
Exemple
DECLARE
v_emp emp%rowtype;
BEGIN
SELECT * INTO v_emp FROM emp
WHERE nome = 'LEROY';
v_emp.matr := v_emp.matr + 5;
v_emp.nome := 'Toto';
INSERT INTO emp
VALUES v_emp;
END;
PL/SQL
-M. HAJJI-
28
Extraction des données: Erreurs
Si le select renvoie plus d’une ligne, une exception «
TOO_MANY_ROWS » (ORA-01422) est levée.
Si le select ne renvoie aucune ligne, une exception «
NO_DATA_FOUND » (ORA-01403) est levée.
PL/SQL
-M. HAJJI-
29
Les curseurs
30
PL/SQL
-M. HAJJI-
Fonctionnalités
Toutes les requêtes SQL sont associées à un curseur
Ce curseur représente la zone mémoire utilisée pour
parser et exécuter la requête
Le curseur peut être implicite (pas déclaré par
l’utilisateur) ou explicite
Les curseurs explicites servent à retourner plusieurs
lignes avec un select
PL/SQL
-M. HAJJI-
31
Attributs des curseurs
Tous les curseurs ont des attributs que l’utilisateur peut
utiliser
%ROWCOUNT : nombre de lignes traitées par le curseur
%FOUND : vrai si au moins une ligne a été traitée par la
requête ou le dernier fetch
%NOTFOUND : vrai si aucune ligne n’a été traitée par la
requête ou le dernier fetch
%ISOPEN : vrai si le curseur est ouvert (utile seulement pour
les curseurs explicites)
PL/SQL
-M. HAJJI-
32
Curseurs implicites
Les curseurs implicites sont tous nommés SQL
DECLARE
nb_lignes integer;
BEGIN
DELETE FROM emp
WHERE dept = 10;
nb_lignes := SQL%ROWCOUNT;
. . .
PL/SQL
-M. HAJJI-
33
Curseurs explicites
Pour traiter les select qui renvoient plusieurs lignes
Ils doivent être déclarés
Le code doit les utiliser explicitement avec les ordres
OPEN, FETCH et CLOSE
Le plus souvent on les utilise dans une boucle dont on
sort quand l’attribut NOTFOUND du curseur est vrai
PL/SQL
-M. HAJJI-
34
Curseurs explicites
DECLARE
CURSOR salaires IS
SELECT sal
FROM emp
WHERE dept = 10;
salaire numeric(8, 2);
total numeric(10, 2) := 0;
BEGIN
OPEN salaires;
LOOP
FETCH salaires INTO salaire;
EXIT WHEN salaires%NOTFOUND;
IF salaire IS NOT NULL THEN
total := total + salaire;
DBMS_OUTPUT.put_line(total);
END IF;
END LOOP;
CLOSE salaires; -- Ne pas oublier
DBMS_OUTPUT.put_line(total);
END;
PL/SQL
-M. HAJJI-
35
Curseurs explicites
On peut déclarer un type « row » associé à un curseur
DECLARE
CURSOR c IS
SELECT matr, nome, sal FROM emp;
employe c%ROWTYPE;
BEGIN
OPEN c;
FETCH c INTO employe;
IF employe.sal IS NULL THEN …
PL/SQL
-M. HAJJI-
36
Curseurs explicites
Elle simplifie la programmation car elle évite d’utiliser
explicitement les instruction OPEN, FETCH, CLOSE
En plus elle déclare implicitement une variable de type «
row » associée au curseur
DECLARE
CURSOR c IS
SELECT dept, nome FROM emp
WHERE dept = 10;
BEGIN
FOR employe IN c LOOP
dbms_output.put_line(employe.nome);
END LOOP;
END;
PL/SQL
-M. HAJJI-
37
Curseurs paramétrés
Un curseur paramétré peut servir plusieurs fois avec des
valeurs des paramètres différentes
On doit fermer le curseur entre chaque utilisation de
paramètres différents (sauf si on utilise « FOR » qui ferme
automatiquement le curseur )
DECLARE
CURSOR c(p_dept integer) IS
SELECT dept, nome FROM emp
WHERE dept = p_dept;
Advertisement
BEGIN
FOR employe in c(10) LOOP
dbms_output.put_line(employe.nome);
END LOOP;
FOR employe in c(20) LOOP
dbms_output.put_line(employe.nome);
END LOOP;
END;
PL/SQL
-M. HAJJI-
38
Ligne courante d’un curseur
La ligne courante d’un curseur est déplacée à chaque
appel de l’instruction FETCH
On est parfois amené à modifier la ligne courante
pendant le parcours du curseur
Pour cela on peut utiliser la clause « WHERE CURRENT
OF » pour désigner cette ligne courante dans un ordre
LMD (insert, update, delete)
Il est nécessaire d’avoir déclaré le curseur avec la clause
FOR UPDATE pour que le bloc compile
PL/SQL
-M. HAJJI-
39
La clause FOR UPDATE
FOR UPDATE [OF col1, col2,…]
Cette clause bloque toute la ligne ou seulement les
colonnes spécifiées
Les autres transactions ne pourront modifier les valeurs
tant que le curseur n’aura pas quitté cette ligne
PL/SQL
-M. HAJJI-
40
La clause FOR UPDATE
Exemple
DECLARE
CURSOR c IS
SELECT matr, nome, sal
FROM emp
WHERE dept = 10
FOR UPDATE OF emp.sal;
…
IF salaire IS NOT NULL THEN
total := total + salaire;
ELSE -- met 0 à la place de null
UPDATE emp SET sal = 0
WHERE CURRENT of c;
END IF;
PL/SQL
-M. HAJJI-
41
Les exceptions
42
PL/SQL
-M. HAJJI-
Introduction
Une exception est une erreur qui survient durant une
exécution
2 types d’exception :
prédéfinie par Oracle
définie par le programmeur
Structure d’un bloc:
DECLARE
-- définitions de variables
BEGIN
-- Les instructions à exécuter
EXCEPTION
-- La récupération des erreurs
END;
PL/SQL
-M. HAJJI-
43
Saisi des exceptions
Une exception ne provoque pas nécessairement l’arrêt du
programme si elle est saisie par un bloc (dans la partie «
EXCEPTION »)
Une exception non saisie remonte dans la procédure
appelante (où elle peut être saisie)
Exceptions prédéfinies
NO_DATA_FOUND
TOO_MANY_ROWS
VALUE_ERROR (erreur arithmétique)
ZERO_DIVIDE
…
PL/SQL
-M. HAJJI-
44
Traitement des exceptions
BEGIN
…
EXCEPTION
WHEN NO_DATA_FOUND THEN
. . .
WHEN TOO_MANY_ROWS THEN
. . .
WHEN OTHERS THEN -- optionnel
. . .
END;
PL/SQL
-M. HAJJI-
45
Exception utilisateur
Elles doivent être déclarées avec le type EXCEPTION
On les lève avec l’instruction RAISE
DECLARE
salaire numeric(8,2);
salaire_trop_bas EXCEPTION;
BEGIN
SELECT sal INTO salaire FROM emp
WHERE matr = 50;
IF salaire < 300 THEN
raise salaire_trop_bas;
END IF;
-- suite du bloc
EXCEPTION
WHEN salaire_trop_bas THEN . . .;
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;
PL/SQL
-M. HAJJI-
46
Exercices
Ecrire un bloc PL/SQL qui permet d’afficher les joueurs nais
après 1970.
Ecrire un bloc PL/SQL qui permet d’afficher toutes les
rencontres qui se sont déroulées à Roland Garros en 1992.
L’affichage doit se faire sous la forme:
« Gagnant » VS « Perdant »
Ecrire un bloc PL/SQL qui permet d’afficher la moyenne des
gains pour chaque tournoi.
Ecrire un bloc PL/SQL « sponsorJoueurs.sql » qui permet
d’afficher les joueurs de chaque Sponsor.
Ecrire un bloc PL/SQL qui permet d’insérer dans une Table
« primeTournoi » le total des primes pour chaque tournoi et
chaque année.
PL/SQL
-M. HAJJI-
47
Exercices
Ecrire un bloc PL/SQL qui permet d’insérer dans une
table « palmares » le numéro, nom et prénom d’un
joueur, le nombre de match joué dans sa carrière, le
nombre de match gagné, le nombre de match perdu et le
total de ses gains.
Ecrire un bloc PL/SQL qui permet de mettre à jour les
informations nombre de match joués, nombre de matchs
gagnés, nombre de matchs perdus et total des gains.
PL/SQL
-M. HAJJI-
48
Advertisement
Les procédures et les fonctions
49
PL/SQL
-M. HAJJI-
Bloc anonyme ou nommé
Un bloc anonyme PL/SQL est un bloc «DECLARE –
BEGIN –END» comme dans les exemples précédents
Dans SQL*PLUS on peut exécuter directement un bloc
PL/SQL anonyme en tapant sa définition
Le plus souvent, on crée plutôt une procédure ou une
fonction nommée pour réutiliser le code
PL/SQL
-M. HAJJI-
50
Création d’une procédure
CREATE OR Replace
PROCEDURE(<liste params>) IS
-- déclaration des variables
BEGIN
-- code de la procédure
END;
Pas de DECLARE ; les variables sont déclarées entre IS et
BEGIN
Si la procédure ne nécessite aucune déclaration, le code
est précédé de « IS BEGIN »
PL/SQL
-M. HAJJI-
51
Création d’une fonction
CREATE OR Replace
FUNCTION(<liste params>)
RETURN <type retour> IS
-- déclaration des variables
BEGIN
-- code de la procédure
END;
PL/SQL
-M. HAJJI-
52
Passage de paramètres
Dans la définition d’une procédure on indique le type de
passage que l’on veut pour les paramètres :
IN pour le passage par valeur
IN OUT pour le passage par référence
OUT pour le passage par référence mais pour un paramètre
dont la valeur n’est pas utilisée en entrée
Pour les fonctions, seul le passage par valeur (IN) est
autorisé
PL/SQL
-M. HAJJI-
53
Utilisation des procédures et fonctions
Sous SQL*PLUS, il faut taper une dernière ligne contenant
« / » pour compiler une procédure ou une fonction
Les procédures et fonctions peuvent être utilisées dans
d’autres procédures ou fonctions ou dans des blocs
PL/SQL anonymes
Les fonctions peuvent aussi être utilisées dans les
requêtes SQL
Exemple
CREATE OR REPLACE
FUNCTION euro_to_fr(somme IN number)
RETURN number IS
taux CONSTANT NUMBER := 6.55957;
BEGIN
return somme * taux;
END;
PL/SQL
-M. HAJJI-
54
Utilisation des procédures et fonctions
Dans un bloc anonyme
DECLARE
CURSOR c(p_dept integer) IS
SELECT dept, nome, sal FROM emp
WHERE dept = p_dept;
BEGIN
FOR employe IN c(10) LOOP
dbms_output.put_line(employe.nome
|| ' gagne '
|| euro_to_fr(employe.sal)
|| ' francs');
END LOOP;
END;
Dans une requête
SELECT nome, sal, euro_to_fr(sal)
FROM emp;
PL/SQL
-M. HAJJI-
55
Exécution d’une procédure
Sous SQL*PLUS on exécute une procédure PL/SQL avec
la commande EXECUTE :
EXECUTE nomProcédure(param1, …);
Sous SQL*PLUS on exécute une fonction PL/SQL avec la
commande select:
SELECT nomFonction(param1, …) FROM dual;
PL/SQL
-M. HAJJI-
56
Exercices
Ecrire une fonction MaxPS qui permet de retourner le
maximum entre deux valeurs « a » et « b ».
Exécuter la fonction MaxPS directement sous SQL*PLUS,
en définissant les paramètres.
Dans un programme principal, afficher pour un tournoi et
une année donnés, le Nom et Prénom du joueur ayant eu
la plus haute prime, en faisant appel à la fonction MaxPS.
Dans un programme principal, afficher le Nom et Prénom
du joueur ayant eu le cumul le plus important durant une
année donnée, en utilisant la fonction MaxPS.
PL/SQL
-M. HAJJI-
57
Exercices
Ecrire une fonction RatePS qui permet de calculer le
pourcentage d’une valeur par rapport à une valeur
globale.
Dans un programme principal, afficher pour un tournoi et
une année donnés, le Nom, Prénom et le pourcentage de
la prime de chaque joueur par rapport à la prime globale.
Dans un programme principal, afficher le Nom et le
Prénom du joueur, ainsi que le pourcentage de sa prime
par rapport au cumul de toutes les primes dans une
année donnée, pour le joueur ayant eu le plus important
pourcentage. On utilisera les fonctions RatePS et MaxPS.
Exécuter la fonction RatePS sous SQL*Plus.
PL/SQL
-M. HAJJI-
58
Exercices
Ecrire une procédure WinnersPS qui prend en paramètre le
nom d’un tournoi, et qui affiche la liste des vainqueurs de ce
tournoi (Nom et Prénom) ainsi que l’année.
Exécuter la procédure WinnersPS à partir de SQL*Plus.
Dans un programme PL/SQL, faire appel à la procédure
WinnersPS pour afficher la liste des vainqueurs des tournois
qui se sont passés dans un intervalle de dates donné.
Ecrire une procédure UpdatePalmPS qui permet de mettre à
jour les champs nombre de match joué, nombre de match gagné,
nombre de match perdu et total des gains de la table
« palmares » (TP précèdent). Les valeurs sont passées en
paramètres.
PL/SQL
-M. HAJJI-
59
Merci…
PL/SQL
-M. HAJJI-
60