PL / SQL

Les curseurs

4ème année Génie Informatique Noreddine Gherabi

 Contrôler les curseurs explicites

Les curseurs
Un curseur est une variable qui pointe vers le résultat d’une requête SQL. La
déclaration du curseur est liée au texte de la requête.

Select nom,pre, tel from client where ville =‘casa’;

Requête

oracle engine N Nom Pre tel

-- --- --- ---
-- --- --- ---
-- --- --- ---
Curseur
 Contrôler les curseurs explicites

N Nom Pre tel

Pointeur Curseur -- --- --- ---
-- --- --- ---
-- --- --- ---

Non
Variable

Oui
DECLARE OPEN FETCH VIDE ? CLOSE

• Libérer
• Créer une • Execute • Charger la • Tester l'ensemble actif
zone • Identifier ligne en l'existence de
mémoire l'ensemble cours dans lignes
SQL des variables
actif • Si des lignes
existent, revenir à
FETCH
 Déclarer le curseur

Syntaxe :

DECLARE CURSOR nom_curseur IS Requête_SQL

Exemple :
DECLARE
CURSOR c1 IS
SELECT employe_id, nom
FROM employe;
CURSOR c2 IS
SELECT *
FROM department
WHERE department_id = 10;
BEGIN
...
 Ouvrir le curseur

Syntaxe :

OPEN cursor_name;

v Ouvrir le curseur pour exécuter l'interrogation et identifier

l'ensemble actif
v Si l'interrogation ne renvoie pas de ligne, aucune exception n'est
déclenchée
v Utiliser les attributs du curseur pour tester le résultat après une
extraction
 Fermer le curseur

Syntaxe :

CLOSE cursor_name;

v Fermer le curseur après avoir terminé le traitement des

lignes
v Rouvrir le curseur, si nécessaire
v Ne pas essayer d'extraire les données d'un curseur s'il a
été fermé
 Extraire les données à partir du curseur

Syntaxe :
FETCH nom_curseur INTO var_curseur;

Exemple :
DECLARE
v_empid employe.employe_id%TYPE;
v_nom employe.nom%TYPE;
CURSOR c1 IS
SELECT employe_id, nom
FROM employe;
BEGIN
OPEN c1;
FOR i IN 1..10 LOOP
FETCH c1 INTO v_empid, v_nom;
...
END LOOP;
CLOSE c1;
END ;
 Les curseurs et les enregistrements

Exemple :
DECLARE
CURSOR c1 IS
SELECT nom, salaire FROM employees;
emp c1%ROWTYPE;
BEGIN
...
FOR emp IN c1 LOOP
DBMS_OUTPUT.PUT_LINE(‘Nom:’ || emp.nom);
DBMS_OUTPUT.PUT_LINE(‘salaire :’ || emp.salaire) ;
...
END LOOP;
COMMIT;
END;
 Attributs d'un curseur explicite

Obtenir les informations d'état concernant un curseur :

NomCurseur%attribut

Attribut Type Description

%ISOPEN BOOLEAN Prend la valeur TRUE si le curseur est
ouvert
%NOTFOUND BOOLEAN Prend la valeur TRUE si la dernière
extraction ne renvoie pas de ligne
%FOUND BOOLEAN Prend la valeur TRUE si la dernière
extraction renvoie une ligne ;
complément de %NOTFOUND
%ROWCOUNT NUMBER Prend la valeur correspondant au
nombre total de lignes renvoyées
jusqu'à présent
 PL / SQL
Les exceptions

4ème année Génie Informatique Noreddine Gherabi

 Gestion des erreurs

Le langage PL/SQL offre au développeur un mécanisme de gestion des

exceptions. Il permet de préciser la logique du traitement des erreurs
survenues dans un bloc PL/SQL. Il s’agit donc d’un point clé dans
l’efficacité du langage qui permettra de protéger l’intégrité du système.
Il existe deux types d’exception :
q Interne : ︎Les exceptions internes sont générées par le moteur du
système (division par zéro, connexion non établie, table inexistante,
privilèges insuffisants, mémoire saturée, espace disque
insuffisant, ...).

q Externe : Les exceptions externes sont générées par l’utilisateur .

 Gestion des erreurs

Les erreurs ORACLE générées par le noyau sont numérotées (ORA-xxxxx). Il a

donc fallu établir une table de correspondance entre les erreurs ORACLE et des
noms d’exceptions.

Voici quelques exemples d’exceptions prédéfinis et des codes correspondants :

 Gestion des erreurs

Signification des erreurs Oracles présentées ci-dessus :

o ︎C
CURSOR_ALREADY_OPEN
URSOR_ALREADY_OPEN : tentative d’ouverture d’un curseur déja ouvert..
o DUP_VAL_ON_INDEX
︎DUP_VAL_ON_INDEX : violation de l’unicité lors d’une mise à jour détectée au
niveau de l’index unique.
o INVALID_CURSOR
︎INVALID_CURSOR : opération incorrecte sur un curseur, comme par exemple la
fermeture d’un curseur qui n’a pas été ouvert.
o INVALID_NUMBER
︎INVALID_NUMBER : échec de la conversion d’une chaîne de caractères en numérique.
o LOGIN_DENIED
︎LOGIN_DENIED : connexion à la base échouée car le nom utilisateur ou le mot de
passe est invalide.
o NO_DATA_FOUND
︎NO_DATA_FOUND : déclenché si la commande SELECT INTO ne retourne aucune
ligne ou si on fait référence à un enregistrement non initialise d’un tableau PL/SQL.
o PROGRAM_ERROR
︎PROGRAM_ERROR : problème général dû au PL/SQL.
o ROWTYPE_MISMATCH
︎ROWTYPE_MISMATCH : survient lorsque une variable curseur d’un programme hôte
retourne une valeur dans une variable curseur d’un bloc PL/SQL qui n’a pas le même
type.
o TIMEOUT_ON_RESOURCE
︎TIMEOUT_ON_RESOURCE : dépassement du temps dans l’attente de libération des
ressources (lié aux paramètres de la base).
o TOO_MANY_ROWS
︎TOO_MANY_ROWS : la commande SELECT INTO retourne plus d’une ligne.
o ︎ ZERO_DIVIDE : tentative de division par zéro.
 Exception utilisateur

Syntaxe :
DECLARE
...
nom_erreur EXCEPTION; 1

…
BEGIN
...

IF (anomalie) THEN
RAISE nom_erreur; 2
END IF;
...
EXCEPTION
WHEN nom_erreur THEN traitement; 3

END;
 Exception utilisateur

Exemple : Exception utilisateur

DECLARE
Employe_age INT;
invalid_age EXCEPTION;
BEGIN
SELECT age INTO Employe_age FROM Employe WHERE nom=’Salami';

IF Employe_age <= 0 THEN

RAISE invalid_age;
ELSE
DBMS_OUTPUT.PUT_LINE('L age de l’employé est:'||Employe_age);
END IF;

EXCEPTION
WHEN invalid_age THEN dbms_output.put_line('L age est invalide');

END;
 Exception Oracle - prédéfinie

Syntaxe :
BEGIN SELECT ... COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
statement1;
statement2;
WHEN TOO_MANY_ROWS THEN
statement1;
WHEN OTHERS THEN
statement1;
statement2;
statement3;
END;
 Exception Oracle - prédéfinie

Exemple : Exception prédéfinie Oracle

DECLARE
Var_Nom EMPLOYE.nom%TYPE;
BEGIN
SELECT nom INTO Var_Nom FROM EMPLOYE WHERE numéro=1;

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘L’’employé n’’existe pas’) ;

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(‘Erreur’ ) ;
END;
/
 Exception Oracle - Non prédéfinie

Dans ce type nous créons une correspondance entre le code erreur ORACLE et le nom
de l’exception. Ce nom est choisi librement par le programmeur.
Pour affecter un nom à un code erreur, on doit utiliser une directive compilée (pragma)
nommée EXCEPTION_INIT.

Syntaxe :
DECLARE
champ_obligatoire EXCEPTION; 1

PRAGMA EXCEPTION_INIT (champ_obligatoire, -1400); 2

BEGIN ...
EXCEPTION
WHEN champ_obligatoire THEN traitement; 3

END;
Le code erreur -1400 correspond à l’absence d’un champ obligatoire.
 Exception Oracle - Non prédéfinie

Exemple : Exception Oracle – non prédéfinie

DECLARE
Erreur_data EXCEPTION;
Var_Nom EMPLOYE.nom%TYPE;
PRAGMA EXCEPTION_INIT (Erreur_data , -100);
BEGIN
SELECT nom INTO var_nom FROM Employé WHERE code=1;
EXCEPTION
WHEN Erreur_data THEN
DBMS_OUTPUT.PUT_LINE(‘L’emplyé n’existe pas’ ) ;
END;

Le code erreur -100 correspond aux données inexistantes .

 PL / SQL
Les procédures

4ème année Génie Informatique Noreddine Gherabi

 Objectifs

v Définition d’une procédure

v Créer une procédure
v Faire la distinction entre les paramètres formels et les paramètres
réels
v Répertorier les fonctions des différents modes des paramètres
v Créer des procédures avec des paramètres
v Appeler une procédure
v Traiter des exceptions dans les procédures
v Supprimer une procédure
 Définition d'une procédure

v Une procédure est un type de sous-programme qui

exécute une action

v Une procédure peut être stockée en tant qu'objet de

schéma dans la base de données en vue d'exécutions
répétées
 Les procédures

Pourquoi les procédures ?

v Réduire
︎Réduire le trafic sur le réseau (les procédures sont locales sur le
serveur)
v ︎Masquer la complexité du code SQL (simple appel de procédure avec
passage d’arguments)
v ︎Mieux garantir l’intégrité des données (encapsulation des données par
les procédures)
v ︎Sécuriser l’accès aux données (accès à certaines tables seulement à
Sécuriser
travers les procédures)
v ︎Optimiser le code (les procédures sont compilées avant l’exécution du
programme et elles sont exécutées immédiatement si elles se trouvent
dans la SGA (zone mémoire gérée par ORACLE). De plus une
procédure peut être exécutée par plusieurs utilisateurs.
 Syntaxe pour la création de procédures

CREATE [OR REPLACE] PROCEDURE procedure_name

[(parameter1 [mode1] datatype1,
parameter2 [mode2] datatype2,
. . .)]
IS
PL/SQL Block;

• L'option REPLACE indique que, si la procédure existe, elle

sera supprimée et remplacée par la nouvelle version créée avec
l'instruction
• Le bloc PL/SQL commence par BEGIN ou par la déclaration
de variables locales et se termine par END ou par END
procedure_name
 Paramètres Formels / Réels

§ Les paramètres formels sont des variables déclarées dans la liste de

paramètres d'une spécification de sous-programme
Exemple :
CREATE PROCEDURE Moy_sal ( Emp_id NUMBER, Emp_sal NUMBER)
….
END Moy_sal;

§ Les paramètres réels sont des variables ou des expressions

référencées dans la liste de paramètres d'un appel de sous-programme
Exemple :
Moy_sal(v_id, 2000)
 Modes des paramètres des procédures

Procédure
Paramètre IN
Environnement
appelant Paramètre OUT
Paramètre IN OUT

(DECLARE)
RQ : DATATYPE ne peut être que la
définition %TYPE ou %ROWTYPE, ou un BEGIN
type de données explicite sans spécification
de taille. EXCEPTION

END;
 Créer des procédures avec des paramètres

IN OUT IN OUT
Mode par défaut Doit être indiqué Doit être indiqué
La valeur est transmise au sous- Est renvoyé à Est transmis à un sous-
programme l ' e n v i r o n n e m e n t programme ; est renvoyé à
appelant l'environnement appelant

Le paramètre formel se comporte Va r i a b l e n o n Variable initialisée

en constante initialisée
Le paramètre réel peut être un
littéral, une expression, une Doit être une variable Doit être une
constante ou une variable variable
initialisée

Par défaut, le paramètre IN est transmis par référence et les paramètres OUT et IN
OUT sont transmis par valeur.
 Les procédures

Exemples de paramètres IN:

CREATE OR REPLACE PROCEDURE modifier_salaire

(Emp_id IN employees.employee_id%TYPE)
IS
BEGIN
UPDATE employees
SET salaire = salaire * 1.10
WHERE employee_id = Emp_id;
END modifier_salaire;
 Les procédures

Exemples de paramètres OUT

Environnement appelant Procédure QUERY_EMP

171 p_id

Sellami P_nom

7400 p_salaire

0.15 p_com
 Les procédures

Exemples de paramètres OUT

query_emp.sql
CREATE OR REPLACE PROCEDURE query_emp
(p_id IN employees.employee_id%TYPE,
p_nom OUT employees.nom%TYPE,
p_salaire OUT employees.salaire%TYPE,
p_comm OUT employees.commission%TYPE)
IS
BEGIN
SELECT nom, salaire, commission
INTO p_nom, p_salaire, p_comm
FROM employees
WHERE employee_id = p_id;
….
END query_emp;
 Les procédures

Visualiser des paramètres OUT

Déclarer les variables, exécuter la procédure QUERY_EMP, puis imprimer la
valeur de la variable globale G_NAME

VARIABLE g_name VARCHAR2(25);

VARIABLE g_sal NUMBER;
VARIABLE g_comm NUMBER;

EXECUTE query_emp(171, :g_name, :g_sal, :g_comm);

PRINT g_name;
 Les procédures

Exemple paramètre IN OUT

Environnement appelant

’2126330575' ’(212)633-0575' p_phone_no

CREATE OR REPLACE PROCEDURE format_phone

(p_phone_no IN OUT VARCHAR2)
IS
BEGIN
p_phone_no := '(' || SUBSTR(p_phone_no,1,3) ||
')' || SUBSTR(p_phone_no,4,3) ||
'-' || SUBSTR(p_phone_no,7,4);
END format_phone;
 Les procédures

Visualiser des paramètres IN OUT

VARIABLE phone VARCHAR2(15)

EXECUTE :phone := ’212668448477';

EXECUTE format_phone (:phone)

PRINT phone

Résultat :
 Les procédures

Déclarer des sous-programmes

CREATE OR REPLACE PROCEDURE leave_emp2

(p_id IN employees.employee_id%TYPE)
IS
PROCEDURE log_exec
IS
BEGIN
INSERT INTO log_table (user_id, log_date)
VALUES (USER, SYSDATE);
END log_exec;
BEGIN
DELETE FROM employees
WHERE employee_id = p_id;
log_exec;
END leave_emp2;
/
 Les procédures

Appeler une procédure depuis un bloc PL/SQL anonyme

DECLARE
v_id NUMBER := 163;
BEGIN
Modifier_salaire(v_id); --Appel procedure
COMMIT;
...
END;
 Les procédures

Appeler une procédure depuis une autre procédure

CREATE OR REPLACE PROCEDURE process_emps
IS
CURSOR emp_cursor IS
SELECT employee_id FROM employees;
Emp_rec emp_cursor %ROWTYPE;

BEGIN
FOR emp_rec IN emp_cursor
LOOP
Modifier_salaire(emp_rec.employee_id);
END LOOP;
COMMIT;
END process_emps;
/
 Les procédures

Exceptions traitées
Procédure appelée
Procédure appelante PROCEDURE
PROC2 ...
PROCEDURE IS
PROC1 ... ...
IS BEGIN Exception déclenchée
... ...
BEGIN EXCEPTION
... ... Exception traitée
PROC2(arg1); END PROC2;
...
EXCEPTION La procédure
...
END PROC1; appelante reprend
le contrôle
 Les procédures

Exceptions non traitées

Procédure appelée
Procédure appelante
PROCEDURE
PROCEDURE PROC2 ...
PROC1 ... IS
IS ...
... BEGIN
BEGIN ... Exception déclenchée
... EXCEPTION
... Exception non traitée
PROC2(arg1); END PROC2;
...
EXCEPTION
...
END PROC1; La section de traitement
des exceptions de la
procédure appelante a
repris le contrôle
 Les procédures

Supprimer une procédure dans la base de données

Syntaxe:
DROP PROCEDURE procedure_name

Exemple :

DROP PROCEDURE Modifier_salaire;

 Synthèse

v Une procédure est un sous-programme qui exécute une action

v Vous pouvez créer des procédures en utilisant la commande CREATE
PROCEDURE
v Vous pouvez compiler et enregistrer une procédure dans la base de
données
v Il existe trois modes de paramètre : IN, OUT et IN OUT
v Les procédures peuvent être appelées à partir de n'importe quel outil
ou langage prenant en charge le langage PL/SQL
v Vous devez être conscient de l'impact des exceptions traitées et non
traitées sur les transactions et les procédures appelantes
v Vous pouvez supprimer des procédures de la base de données en
utilisant la commande DROP PROCEDURE
 PL / SQL
Les fonctions

Noreddine Gherabi
 Objectifs

v Décrire les différentes utilisations des fonctions

v Créer des fonctions stockées
v Appeler une fonction
v Supprimer une fonction
v faire la distinction entre une procédure et une fonction
 Présentation des fonctions stockées

v Une fonction est un bloc PL/SQL nommé qui renvoie

une valeur
v Une fonction peut être stockée en tant qu'objet de
schéma dans la base de données en vue d'exécutions
répétées
v Une fonction est appelée dans une expression
 Les fonctions

Syntaxe pour la création de fonctions

CREATE [OR REPLACE] FUNCTION function_name

[(parameter1 datatype1,
parameter2 datatype2,
. . .)]
RETURN datatype -- ne doit pas inclure de spécification de taille
IS
PL/SQL Block;

Le bloc PL/SQL doit comporter au moins une instruction RETURN.

 Les fonctions
Création de la fonction Editeur file.sql
Code de création 1
de fonction
SQL*Plus
Chargement et exécution
2 du fichier file.sql

Oracle Code source

Compilation

Fonction
Pseudo-code
créée

Appel 3
 Les fonctions

Exemple de création d'une fonction stockée

CREATE OR REPLACE FUNCTION get_salaire

(p_id IN employees.employee_id%TYPE)
RETURN NUMBER
IS
v_salaire employees.salaire%TYPE :=0;
BEGIN
SELECT salaire
INTO v_salaire
FROM employees
WHERE employee_id = p_id;
RETURN v_salaire;
END get_salaire;
 Les fonctions

Exécuter des fonctions

v Appeler une fonction dans une expression PL/SQL

v Créer une variable destinée à recevoir la valeur renvoyée
v Exécuter la fonction.
v La valeur renvoyée par l'instruction RETURN sera placée dans la
variable

RQ : Évitez d'utiliser les modes OUT et IN OUT avec les fonctions

 Les fonctions

Exemple d'exécution de fonctions

Environnement appelant Fonction GET_SALAIRE

10 p_id

RETURN v_salaire

1 Charger et exécuter le fichier get_salary.sql pour créer la fonction

2 VARIABLE salaire NUMBER

3 EXECUTE :salaire := get_salaire(10)

4 PRINT salaire
 Les fonctions

Avantages des fonctions définies par l'utilisateur dans les expressions SQL

v Elles complètent le langage SQL en permettant de réaliser des

traitements qui seraient trop complexes, voire impossibles en SQL.
v Utilisées dans la clause WHERE pour filtrer les données, elles
peuvent s'avérer plus efficaces qu'un filtrage au sein de
l'application
v Elles permettent de manipuler les chaînes de caractères
 Les fonctions

Exemple d'appel de fonctions dans des expressions SQL

CREATE OR REPLACE FUNCTION tax(p_value IN NUMBER)

RETURN NUMBER IS

BEGIN
RETURN (p_value * 0.08);
END tax;

Appel fonction

SELECT employee_id, nom, salaire, tax(salaire)

FROM employees WHERE department_id = 100;
 Les fonctions

Emplacements d'appel des fonctions définies par l'utilisateur

v Liste de sélection d'une commande SELECT

v Condition des clauses WHERE et HAVING
v Clauses ORDER BY et GROUP BY
v Clause VALUES de la commande INSERT
v Clause SET de la commande UPDATE
 Les fonctions

Exemple appel fonction :

SELECT employee_id, tax(salaire)

FROM employees
WHERE tax(salaire)>(SELECT tax(salaire)
FROM employees
WHERE department_id=30
)
ORDER BY tax(salaire) DESC;
 Restrictions relatives à l'appel de fonctions à partir
d'expressions SQL

Pour pouvoir être appelée depuis des expressions SQL, une fonction définie
par l'utilisateur doit :

v être une fonction stockée (ce n'est pas le cas pour les procédures stockées.)
v Accepter uniquement des paramètres IN
v En tant que paramètres, accepter uniquement des types de données SQL
valides (et non des types spécifiques au langage PL/SQL)
v Renvoyer des types de données SQL valides et non des types spécifiques
au langage PL/SQL
v Les fonctions appelées depuis des expressions SQL(SELECT, UPDATE,
DELETE en parallèle ) ne peuvent modifier aucune table de la BDD
 Les fonctions

Supprimer une fonction stockée.

DROP FUNCTION function_name

Exemple :

DROP FUNCTION get_salaire;

• Tous les privilèges accordés à une fonction sont annulés lorsque celle-
ci est supprimée.
• Lorsque la syntaxe CREATE OR REPLACE est utilisée, la fonction
est supprimée et recréée, mais dans ce cas, les privilèges accordés sur
la fonction ne sont pas affectés.
 Comparer les procédures et les fonctions

Procédures Fonctions
S'exécutent en tant Sont appelées dans une
qu'instruction PL/SQL expression
Ne contiennent pas de clause Doivent contenir une clause
RETURN dans l'en-tête RETURN dans l'en-tête
Peuvent transférer zéro, une ou Doivent renvoyer une seule
plusieurs valeurs valeur
Peuvent contenir une Doivent contenir au moins une
instruction RETURN instruction RETURN
 Synthèse

v Une fonction est un bloc PL/SQL nommé qui doit renvoyer une valeur
v La syntaxe CREATE FUNCTION permet de créer une fonction
v Une fonction est appelée dans une expression
v Une fonction stockée dans la base de données peut être appelée dans des
instructions SQL
v La syntaxe DROP FUNCTION permet de supprimer une fonction de la
base de données
v En règle générale, une procédure permet d'exécuter une action tandis
qu'une fonction permet de calculer une valeur
 Exercice 1

1. Ecrire une fonction « air_carre » qui calcule l’air d’un carré

2. Ecrire une fonction « air_rectangle » qui calcule l’air d’un rectangle
3. Ecrire une procédure PLSQL qui calcule le produit de l’air d’un carré et
l’air d’un rectangle ( air_carre x air_rectangle) puis affiche un message
selon le résultat de calcul
v Si le produit est entre 1 et 20 afficher le message « Un mauvais rapport »
v Si le produit est entre 21 et 50 afficher le message « Un bon rapport »
v Si le produit est supérieur à 50 « Rapport Excellent »
 Exercice 2

Soit le schéma relationnel suivant :

v Client (Num_cl, Nom, prénom, age, ville)
v Produit (Num_pr, marque, prix, #Num_cl)

v Ecrire une procédure qui insère le total des achats de chaque client dans une table déjà
crée Table_CA_Client( numeroclient number, total number)
v Ecrire une fonction qui calcule le total des achats d’un client donnée.
v Ecrire une procédure qui supprime les clients qui n’ont pas réalisé un total achat >
3000dh
v Ecrire une fonction qui renvoie le nombre des produits achetés par un client donné 3 (le
numéro du client est entré comme paramètre).
v Ecrire une procédure qui stocke les noms des clients qui ont acheté au minimum 2
produits, dans un tableau indexé par des entiers.
La procédure doit utiliser :
§ un curseur pour parcourir les noms des clients
§ un tableau contenant les noms des clients qui ont acheté au minimum 2 produits
§ Les exceptions pour gérer les erreurs (des données inexistantes, curseur incorrect…) .