PL/SQL
Procedural Language SQL
Introduction:
PL/SQL est un langage procedural structuré en BLOCS.
Extension du SQL: cohabite avec les structures de contrôle
habituelles (alternatives, répétitives )
Un programme PL/SQL est composé de fonctions, de procédures,
de triggers..
C’est un langage propriétaire d’ORACLE.
La syntaxe ressemble au langage Ada
C’est un langages de famille L4G
Utilisation de PL/SQL
PL/SQL peut être utilisé pour l’écriture des
• procédures stockées et des triggers (Oracle
accepte aussi le langage Java)
• fonctions utilisateurs qui peuvent être
utilisées dans les requêtes SQL (en plus des
fonctions prédéfinies)
PL/SQL est aussi utilisé dans des outils Oracle,
Forms et Report en particulier
Structure d’un programme
• 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
Structure d’un bloc
DECLARE -- section déclarative (optionnelle)
-- définitions de variables
BEGIN -- section de contrôle ou d’exécution (obligatoire)
-- Les instructions à exécuter
EXCEPTION -- section de gestion des exceptions(optionnelle)
-- La récupération des erreurs
END;
Les blocs, comme les instructions, se terminent par un « ; »
Seuls BEGIN et END sont obligatoires
Les Commentaires
• -- Pour une fin de ligne
• /* Pour plusieurs lignes */
Les variables
30 caractères au plus
commence par une lettre
peut contenir lettres, chiffres, _, $ et #
Pas sensible à la casse
Doivent être déclarées avant d’être utilisées
Déclaration d’une variable
identificateur [CONSTANT] type [:= valeur];
Types de variables
• type Oracle : integer, number ,varchar,
varchar2(n), date, char(n), Boolean…
• type variable %TYPE : permet de déclarer des
variable de même type que des variables déjà
déclarée.
• type rangée %ROWTYPE : pour déclarer un
enregistrement (une ligne).
• types RECORD
• type CURSOR (dynamique ou non)
• type référence : REF
Exemple de déclarations de variables
DECLARE
age integer;
ok boolean := true;
Numero NUMBER(4) := 5;
nom VARCHAR2 (30);
Salaire NUMBER (8,2);
Date_naissance DATE;
i, j integer; -- Déclarations multiples
interdites
Déclaration %TYPE et %ROWTYPE
%TYPE pour déclarer qu’une variable du même type
qu’une autre variable ou qu’une colonne d’une
table ou d’une vue
Salaire_MIN NUMBER(7,2);
Salaire_MAX Salaire_Min%TYPE;
Acteur VARCHAR2(30);
Realisateur Acteur%TYPE;
nom emp.nome%TYPE;
%ROWTYPE pour Déclarer une variable qui contient
toutes les colonnes d’une ligne d’une table
emp employes%ROWTYPE;
//déclarer que la variable emp contiendra une ligne de la table employes
Exemple d’utilisation
emp employes%ROWTYPE;
nom employes.nome%TYPE;
select * INTO emp
from employe
where matr = 900;
nom := emp.nome;
emp.dept := 20;
…
insert into employe
values emp;
Type RECORD
Type RECORD Equivalent à struct du langage C
TYPE nomRecord IS RECORD (
champ1 type1,
champ2 type2,
…);
Exemple :
TYPE recEmp IS RECORD (
matr integer,
nom varchar(30));
employe recEmp ;
employe.matr := 500;
Affectation
Plusieurs façons de donner une valeur à une
variable :
:=
par la directive INTO de la requête SELECT
Exemples :
dateNaissance := ’10/10/2004’;
select nome INTO nom
from employes
where matr = 509;
Conflits de noms
Si une variable porte le même nom qu’une
colonne d’une table, c’est la colonne qui l’emporte
DECLARE
nome varchar(30) := ‘Souguir';
BEGIN
delete from emp where nome = nome;
Pour éviter ça, le plus simple est de ne pas
donner de nom de colonne à une variable !
Structures de contrôle Alternative
• IF -THEN
IF condition THEN
instructions;
END IF;
• IF -THEN -ELSE
IF condition THEN
instructions1;
ELSE
instructions2;
END IF;
Structures de contrôle Alternative
IF-THEN -ELSIF
IF condition1 THEN
instructions1;
ELSIF condition2 THEN
instructions2;
ELSIF condition 3THEN
instructions3;
ELSIF condition4 THEN
instructions4;
ELSE
instructions5;
END IF;
Exemple
Declare
choix number;
begin
IF choix =1 THEN
Delete from commande where numarticle= 100 ;
ELSIF choix =2 THEN
Delete from commande where numarticle= 120;
ELSE
Delete from commande where numarticle= 130;
END IF;
END;
Exercice
écrire un bloc PL/SQL qui permet de lire la
variable vente et calculer la variable bonus pour
mettre à jour le salaire de l’employé comme
suit: salaire = salaire + bonus
• –Si vente est > 1000DT alors bonus =
vente*5%
• –Sinon bonus = vente *2%
Réponse
prompt 'Donner la Val eur des ventes : '
Accept vente;
Declare
Bonus number;
BEGIN
IF &VENTE > 1000 THEN BONUS := &VENTE*0.05;
ELSE
BONUS := &VENTE*0.02;
END IF;
UPDATE EMPLOYES SET SALAIRE = SALAIRE+BONUS;
Commit;
END;
CASE ---WHEN
L’instruction CASE: permet d’exécuter un bloc PL/SQL
selon la valeur d’une variable
CASE expression
WHEN expr1 THEN instructions1;
WHEN expr2 THEN instructions2;
…
ELSE instructionsN;
END CASE;
expression peut avoir n’importe quel type simple (ne
peut pas par exemple être un RECORD)
Les Boucles
WHILE condition LOOP
instructions;
END LOOP;
Exemple
set serveroutput on;
DECLARE
I NUMBER:=1;
BEGIN
WHILE I < 10 LOOP
I:= I+1;
DBMS_OUTPUT.PUT_LINE (TO_CHAR(I));
END LOOP;
END;
Les Boucles
Loop avec EXIT
Lors de l’exécution d’un loop, on peut décider de sortir
immédiatement de la boucle avec la clause EXIT
Exemple
DECLARE
CreditNUMBER := 0;
BEGIN
LOOP
Credit:= Credit+ 1;
IF Credit> 3 THEN
EXIT;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE ('Credit: ' || TO_CHAR(Credit));
END;
Les Boucles
Boucle « pour »
FOR compteur IN [REVERSE] inf..sup LOOP
instructions;
END LOOP;
Exemple :
for i IN 1..100 LOOP
somme := somme + i;
end loop;
Mise au point
Pour la mise au point il est utile de faire
afficher les valeurs des variables
On peut le faire en activant sous SQL*PLUS
la sortie sur l’écran et en utilisant le
paquetage DBMS_OUTPUT
Un paquetage est un regroupement de
procédures et de fonctions ;
Interactions simples avec la base
Extraire des 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 ;
pour ramener des lignes, voir la suite du
cours sur les curseurs
Extraire 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
Exemple
Exemple
DECLARE
v_nom client.nom%TYPE;
v_client client%ROWTYPE;
BEGIN
select nom into v_nom
from client
where nclt = 500;
select * into v_client
from client
where nclt = 500;
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 Voyons plus de détails pour
l’insertion de
données
Insertion
DECLARE
v_nom client.nom%TYPE;
v_client client%ROWTYPE;
BEGIN
v_nom := ‘Souguir ’;
insert into client (nclt nom)
values(600, v_nom);
v_client.nclt := 610;
v_client.nom := ‘Chouchen’;
insert into client (nclt, nom)
values(v_client.nclt, v_client.nom);
commit;
END;
Autre exemple
Declare
v_client client%ROWTYPE;
begin
select * into v_client
from client
where nom = ‘Souguir ’;
v_client.nclt := v_client .nclt+5;
v_client.nom :=‘BANNOUR’;
insert into client values v_client ;
commit;
END;
Curseurs
Le curseur est une zones mémoire associée à
une requête SELECT.
Le curseur peut être implicite (non déclaré
par l’utilisateur) ou explicite
Le curseur explicites sert à traiter plusieurs
lignes venant d’un select multiligne
Utilisation d’un curseur
Il faut quatre étapes:
1. Déclaration du curseur
2. Ouverture du curseur OPEN
3. Lecture du curseur FETCH
4. Fermeture du curseur CLOSE
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)
Curseur implicite
Le curseur implicite est nommés SQL
Exemple
DECLARE
nb_lignes integer;
BEGIN
delete from produit where qtestk = 0;
nb_lignes := SQL%ROWCOUNT;
dbms_output.put_line('nombre produits
supprimer = ' || nb);
end;
Curseur explicite
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
Exemple
SET SERVEROUTPUT ON;
DECLARE
vNom ETUDIANTS.NOM%TYPE;
vPrenom varchar2(20);
CURSOR C IS SELECT nom, Prenom from etudiants;
BEGIN
OPEN C;
LOOP
FETCH C INTO vNom, vprenom;
EXIT WHEN C%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('le nom est '|| vnom || 'le prenom est '||
vprenom);
END LOOP;
CLOSE C ;
END;
Type « row » associé à un curseur
On peut déclarer un type « row » associé à un curseur
Exemple :
declare
cursor c is
select matr, nome, sal from emp;
vc c%ROWTYPE;
begin
open c;
fetch c into vc ;
if vc .sal is null then….
Boucle FOR pour un curseur
Elle simplifie la programmation car elle évite
d’utiliser explicitement les instruction open,
fetch, close
Elle déclare implicitement une variable de type
« row » associée au curseur
Exemple
declare
cursor c is
select * from etudiants
begin
FOR vc IN c LOOP
dbms_output.put_line(vc.nom|| ’ ‘ || vc.age);
END LOOP;
end;
Curseur paramétré
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)
Exemple
declare
cursor c (p_ville varchar2(20) ) is
select * from clients
Where ville= p_ville;
begin
dbms_output.put_line(‘ Les clients de tunis : ’);
for vc in c (‘TUNIS’) loop
dbms_output.put_line(vc.nom || ‘ ‘ || vc.prenom) ;
end loop;
dbms_output.put_line(‘ Les clients de Sousse : ’);
for vc in c(‘SOUSSE’) loop
dbms_output.put_line(vc .nom || ‘ ‘ || vc.prenom);
end loop;
end;
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
FOR UPDATE
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
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;
Fonction qui renvoie un curseur
sous Oracle
Question : comment écrire une fonction qui
renvoie un curseur ?
1. Créer un type pour la référence de curseur
qu’on va renvoyer
2. Créer la fonction qui renvoie la référence de
curseur
Attention, solution propriétaire d’Oracle !
Créer le type référence de curseur
Pour utiliser ensuite le type, il faut le créer dans
un paquetage :
create or replace package Types AS
type curseur_type is ref cursor;
end Types;
Créer la fonction
create or replace
function listdept(num integer)
return Types.curseur_type
is
empcurseur Types.curseur_type;
begin
open empcurseur for
select dept, nomE
from emp where dept = num;
return empcurseur;
end;
Utiliser la fonction dans JDBC
CallableStatement cstmt =
conn.prepareCall("{ ? = call list(?) }");
cstmt.setInt(2, 10);
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.execute();
ResultSet rs = ((OracleCallableStatement)
cstmt).getCursor(1);
while (rs.next()) {
System.out.println(rs.getString("nomE")
+ ";" + rs.getInt("dept"));
}
Ne marche que sous Oracle !
Exceptions
Présentation
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
Rappel de la structure d’un bloc
DECLARE
-- définitions de variables
BEGIN
-- Les instructions à exécuter
EXCEPTION
-- La récupération des erreurs
END;
Saisir une exception
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
…
Traitement des exceptions
BEGIN
…
EXCEPTION
WHEN NO_DATA_FOUND THEN
...
WHEN TOO_MANY_ROWS THEN
...
WHEN OTHERS THEN -- optionnel
...
END;
Exceptions utilisateur
Elles doivent être déclarées avec le type
EXCEPTION
On les lève avec l’instruction RAISE
Exemple
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;
Procédures et fonctions
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 une procédure ou une
fonction nommée pour réutiliser le code
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 »
Création d’une fonction
create or replace FUNCTION(<liste params>)
RETURN <type retour> IS
-- déclaration des variables
BEGIN
-- code de la fonction
END;
Passage des paramètres
Passage des 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é
Compilation
Sous SQL*PLUS, il faut taper une dernière
ligne contenant « / » pour compiler une
procédure ou une fonction
Utilisation des procédures et fonctions
Les procédures et les fonctions peuvent être
utilisées dans d’autres procédures ou fonctions
ou dans des blocs PL/SQL anonymes
Fonctions
Les fonctions peuvent aussi être utilisées dans les
requêtes SQL
create or replace function conversion (somme IN
number) RETURN number IS
tauxE constant number := 3.3;
begin
return somme * tauxE;
end;
Utilisation dans un bloc anonyme
Utilisation 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 '||
conversion (employe.sal) || ' euro');
end loop;
end;
Utilisation dans une requête SQL
select nome, sal, conversion (sal)
from emp;
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, …);
Exemple (corps du bloc)
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;
