Cours Bases de données 2ème année IUT
Cours 4 : PL/SQL : ou comment faire plus avec ORACLE 2ème partie
Anne Vilnat
1 Exceptions
Rappels bloc PL/SQL
Definition
Exemples d’exceptions
2 Fonctions et procédures stockées
Définition
Procédure stockée
Fonction stockée
Utilisation
Compilation et suppression
3 Exemple
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) |
| Syntaxe d’un bloc anonyme |
| DECLARE – Partie déclaration . BEGIN – Partie code . EXCEPTION – Partie gestion des exceptions levées dans le code. . END; |
DECLARE
nbRealAct NUMBER(5);
singulierException EXCEPTION;
BEGIN
SELECT COUNT(distinct A.numIndividu) INTO nbRealAct
FROM Film F, Acteur A
WHERE A.numIndividu = realisateur
AND F.numFilm=A.numFilm;
IF nbRealAct = 1 THEN RAISE singulierException; END IF;
DBMS LINE(nbRealAct||’ réalisateurs ont joué dans leur film’);
EXCEPTION
WHEN singulierException THEN
DBMS LINE(’Un seul réalisateur a joué dans son film’);
END;
| Définition d’une exception |
| Un exception est une erreur ou un avertissement, prédéfini par Oracle ou défini par le programmeur. 4 catégories d’exceptions : nommées prédéfinies par Oracle : Oracle les déclenche, l’utilisateur peut les récupérer (WHEN nomException) nommées définies par l’utilisateur : l’utilisateur les déclare, les lève dans un programme PL/SQL, et peut les récupérer (WHEN nom ) anonymes prédéfinies par Oracle : Oracle les déclenche sans les nommer, l’utilisateur peut quand même les récupérer (WHEN OTHERS ) anonymes définies par l’utilisateur : idem, mais c’est l’utilisateur qui les lève avec RAISE APPLICATION ERROR |
Beaucoup d’erreurs prédéfinies par Oracle, dont :
CURSOR ALREADY OPEN : tentative d’ouvrir un curseur déjà ouvert
LOGON DENIED : mauvais login/password lors de la connexion
ROWTYPE MISMATCH : types de paramètre incompatibles
TOO MANY ROWS : trop de lignes renvoyées par un
SELECT INTO
DECLARE nomException EXCEPTION;
BEGIN
RAISE nomException;
EXCEPTION
WHEN nomException THEN traitement1
[WHEN OTHERS THEN traitementN]
END;
DECLARE cpt NUMBER := 0; monException EXCEPTION;
BEGIN
IF cpt < 0 THEN
RAISE monException;
END IF;
EXCEPTION
WHEN monException THEN
DBMS LINE(’cpt ne doit pas être négatif’);
WHEN OTHERS THEN
DBMS LINE(’Je ne connais pas cette erreur’);
END;
EXCEPTION
WHEN OTHERS THEN
DECLARE – Bloc des autres erreurs codeErreur NUMBER := SQLCODE;
BEGIN
IF codeErreur = -02291 THEN
– Contrainte d’intégrité
RAISE APPLICATION ERROR (-20001, ’Client inexistant’); ELSIF
END IF;
END;– Bloc des autres erreurs
END;– Programme
DECLARE
monException EXCEPTION
PRAGMA EXCEPTION INIT (monException, -2400)
BEGIN
EXCEPTION
WHEN monException THEN
END;
| Ou` va une exception? |
| Une exception est déclenchée : ? PL/SQL cherche un gestionnaire dans la partie Exception du bloc courant (WHEN adéquat) ? Si pas de gestionnaire : on cherche dans le bloc englobant ? Si aucun : PL/SQL envoie un message d’exception non gérée à l’application appelante |
| 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 |
| 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
| 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!)
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 A.numIndividu) INTO nbRealAct
FROM Film F, Acteur A
WHERE A.numIndividu = realisateur
AND F.numFilm=A.numFilm;
IF nbRealAct > nbFilms THEN
DBMS LINE(nbRealAct||’ réalisateurs ont joué dans plus de ’||nbFilms||’de leurs films’);
ELSE DBMS LINE(’Aucun réalisateur n’a joué dans plus de ’||nbFilms||’de ses films’); END IF;
END;
| Syntaxe | ||||||||||||||||
| CREATE [OR REPLACE] FUNCTION nom fonction [(liste paramètres formels)] RETURN typeRetour AS | IS [partie déclaration] BEGIN RETURN valeurRetout [EXCEPTION ] END [nom fonction]; liste de paramètres : idem procédures, mais IN préférable dans les fonctions!!! Exemple de fonction stockéeOn cherche toujours les réalisateurs qui ont joué dans plus de nbFilms de leurs films CREATE FONCTION nbRealActeurFonc (nbFilms NUMBER) RETURN NUMBER IS nbRealAct NUMBER(5) := 0 ; BEGIN SELECT COUNT(distinct A.numIndividu) INTO nbRealAct FROM Film F, Acteur A WHERE A.numIndividu = realisateur AND F.numFilm=A.numFilm; RETURN nbRealAct; END; Appel de procédures et de fonctions stockées
Récursivité
La récursivité peut aussi être croisée. Compilation et suppression
ExempleOn cherche les acteurs ayant joué dans plus de N films Et on veut leurs noms! CREATE PROCEDURE nomsGdsActeurs (nbFilms NUMBER) Cursor lesActeurs IS SELECT nomIndividu FROM Individu WHERE numIndividu IN (SELECT numActeur GROUP BY numIndividu HAVING Count(numFilm)>nbFilms); ? Exemple? BEGIN DBMS LINE(’Voici les acteurs ayant joué dans plus de ’||nbFilms||’ films : ’) FOR ligneCurseur IN lesActeurs ; LOOP DBMS LINE(ligneCurseur.nomIndividu) END LOOP; DBMS LINE(’Voici le nombre d’acteurs ayant joué dans plus de ’||nbFilms||’ films : ’ || lesActeurs%rowCount); END; Exemple (suite alternative)? sonNom nomIndividu.Individu%type; BEGIN DBMS LINE(’Voici les acteurs ayant joué dans plus de ’||nbFilms||’ films : ’) OPEN lesActeurs ; LOOP FETCH lesActeurs INTO sonNom; EXIT WHEN lesActeurs%NOTFOUND; DBMS LINE(sonNom) END LOOP; DBMS LINE(’Voici le nombre d’acteurs ayant joué dans plus de ’||nbFilms||’ films : ’ || lesActeurs%rowCount); END; |
