partie 2 - le langage procedural d'oracle - Free.fr
Exercice n°12 : deux pages : GET 9 ... Exercice n°2 : gestion des erreurs SQL 12
... (13 exercices corrigés) vous pouvez consulter http://fr.php.net/tut.php ......
Informix, Oracle, MS SQL 7, Foxpro, Access, ADO, SAP DB, SQLite, Sybase, DB2
...
part of the document
mp where deptno=10 order by sal ;
Begin
... ...;
End ;
2. L'ouverture du curseur
Louverture du curseur réalise :
l'allocation mémoire du curseur
l'analyse sémantique et syntaxique de l'ordre
le positionnement de verrous éventuels (si select for update...)
Cest seulement à louverture du curseur que la requête SQL séxécute.
L'ouverture du curseur se fait dans la section Begin du Bloc.
OPEN nomcurseur ;
Declare
Cursor DEPT10 is
select ename, sal from emp where deptno=10 order by sal ;
Begin
...Open DEPT10;
.....
End ;
Traitement des lignes
Après l'exécution du Select les lignes ramenées sont traitées une par une, la valeur de chaque colonne du Select doit être stockée dans une variable réceptrice définie dans la partie Declare du bloc. Le fetch ramène une seule ligne à la fois, pour traiter n lignes il faut une boucle.
FETCH nomcurseur INTO liste_variables ou Nom_enregistrement;
create table resultat (nom1 char(10), sal1 number(7,2))
/
Declare -- programme plsql_ex5.sql
Cursor DEPT10 is select ename, sal from emp where deptno=20 order by sal ;
-- variables réceptrices
nom emp.ename%TYPE; -- Variable locale de même type que le champ ename
salaire emp.sal%TYPE;
Begin
Open DEPT10;
Fetch DEPT10 into nom, salaire ; -- Lecture 1° tuple
WHILE DEPT10%found loop -- Tant quon trouve une ligne
If salaire > 2500 then
insert into resultat values (nom,salaire);
end if;
Fetch DEPT10 into nom,salaire ; -- Lecture tuple suivant
end loop;
Close DEPT10;
End ;
/
select * from resultat
/
drop table resultat
/
SQL> @ ../gautier/plsql_ex5
Table créée.
Procédure PL/SQL terminée avec succès.
NOM1 SAL1
---------- ---------
JONES 2975
SCOTT 3000
FORD 3000
Table supprimée.
Attributs :Explication : Nomcurseur%FoundVrai si exécution correcte de lordre SQL Nomcurseur%NotfoundVrai si exécution incorrecte de lordre SQL Nomcurseur%IsopenVrai si curseur ouvert Nomcurseur%RowcountDonne la nième ligne traitéeLes attributs d'un curseur sont des indicateurs sur l'état d'un curseur. Ils nous fournissent des informations quant à l'exécution de l'ordre. Elles sont conservées par Pl/Sql après l'exécution du curseur.
Ces attributs permettent de tester directement le résultat de l'exécution. Tous les attributs ont un nom.
4. La fermeture du curseur
Après le traitement des lignes, l'étape de fermeture permet d'effectuer la libération de la place mémoire.
CLOSE nomcurseur Close dept10 ;
Complément : Utiliser une variable de type enregistrement
1° solution : Nom-de-variable nom_table%rowtype;
Correspond à la déclaration dune variable de même type que lenregistrement (= le tuple = la ligne) de la table.
DECLARE
LigFleur FLEURS%ROWTYPE ;
X number(10,3) ;
BEGIN
SELECT * INTO LigFleur where nofleur=10; -- 1 seule ligne
X := LigFleur.Prx *1.1 ; -- On peut accéder à chaque champ de lenregistrement
Dans un contexte curseur (résultat du select >1 tuple), lattribut rowtype permet la déclaration implicite dune structure dont les éléments sont d'un type identique aux colonnes ramenées par le curseur.
Dans la partie déclarative du bloc. Cursor nomcurseur is ordre_select ; nom_structure nomcurseur%ROWTYPE;
Les éléments de la structure sont identifiés par : nom_structure.nomcolonne
La structure est renseignée par le Fetch : Fetch nomcurseur into nom_structure;
Au préalable afin de bien tester le programme ci-dessous, sous SQL*PLUS
SQL> update pilote
set comm = null where nopilot='1243'
create table resultat (nom1 char(35), sal1 number(8,2))
/
-- Programme Plsql_ex6.sql
Declare
Cursor C1 is select * from pilote where adresse='Paris';
-- variable réceptrice
unpilot pilote%rowtype;
Begin
Open C1;
Fetch c1 into unpilot ; -- Lecture 1° tuple
WHILE C1%found
loop
If unpilot.comm is not null then
insert into resultat values (unpilot.nompilot, unpilot.salpilot);
end if;
Fetch c1 into unpilot ; -- Lecture tuple suivant
end loop;
Close c1;
End ;
/
select * from resultat
/
drop table resultat
/
2° solution : Déclarer un type enregistrement
TYPE nom_enregistrement IS RECORD
( Nom-de-champ1 type,
Nom-de-champ2 type,
.....) ;
-- Déclaration dune variable de ce type
Une-Variable nom_enregistrement ;
create table resultat(nom1 char(35), sal1 number(8,2))
/
-- Programme PLSQL_EX7.sql --
DECLARE
Type EngPilote IS Record
(nom_pilote pilote.nompilot%type,
revenu_pilote pilote.salpilot%type);
Unpilot EngPilote;
BEGIN
-- Exemple d'affectation
Unpilot.nom_pilote := 'DUPUY';
-- ou Recherche d'un pilote
-- 1 seule ligne pas de curseur
SELECT nompilot,salpilot INTO Unpilot from pilote
where nopilot = '7100';
if unpilot.nom_pilote is not NULL then
Insert into resultat
values(unpilot.nom_pilote, unpilot.revenu_pilote);
end if ;
END;
/
select * from resultat
/
drop table resultat
Exercices dapplication
Ecrire tous les programmes donnés dans cette partie et les tester. Adapter les tuples des tables afin de passer en revue les différentes possibilités
Ecrire le programme PLSQL_EX8.sql qui permet de retrouver tous les pilotes de Paris ayant une commission non null en déclarant le type denregistrement avec RECORD.
Vous avez ci-dessous un programme PL/SQL et les tables dorigine. Etudiez ce programme, expliquez succinctement son but et donnez les lignes affichées à la fin de son exécution ainsi que le contenu des deux tables dorigine.
II MODIFICATION DE DONNEES
Les modifications de données seffectuent normalement par les instructions SQL : INSERT, UPDATE et DELETE comme nous avons pu le remarquer dans le programme ci-dessus dexercice : majliv.sql.
PL/SQl permet la possibilité dutiliser loption CURRENT OF nom_curseur dans la clause WHERE des instructions UPDATE et DELETE. Cette option permet de modifier ou de supprimer la ligne distribuée par la commande FETCH. Pour utiliser cette option, il faut ajouter la clause FOR UPDATE à la fin de la définition du curseur.
-- Programme PLSQL_EX9.sql --
DECLARE
Cursor C1 is
select ename, sal from emp
for update of sal;
resC1 c1%rowtype;
BEGIN
Open C1;
Fetch C1 into resC1;
While C1%found Loop
If resC1.sal > 1500 then
update emp
set sal = sal * 1.1
where current of c1;
end if;
Fetch C1 into resC1;
end loop;
close C1 ;
END;
/
Explications :
( ... For update of nom_colonne )
Il faut se réserver la ligne lors de la déclaration du curseur par le positionnement d'un verrou d'intention .
( ... where current of c1 ; )
Il faut spécifier que l'on veut traiter la ligne courante au Fetch par la clause :
Exercice : Au préalable sous SQL*Plus ajouter une colonne BUDGET de type number à la table DEPT. Dans le programme SQL « Exo4_plsql.sql » mettre à jour cette colonne avec la somme totale des salaires des employés du département.
Résultat à obtenir :
DEPTNODNAMELOCBUDGET10ACCOUNTINGNEW-YORK875020RESEARCHDALLAS11139,830SALESCHICAGO940040OPERATIONBOSTON50INFORMATIQUENANTESIII GESTION DES ERREURS
La section EXCEPTION permet daffecter un traitement approprié aux erreurs survenues lors de lexécution du programme PLSQL.
On distingue 2 types derreur (ou dexceptions)
Les erreurs internes dOracle
Les anomalies déterminées par lutilisateur
Après exécution de la procédure derreur dans un programme dun seul bloc, le programme PLSQL est terminée .
a) Les erreurs internes dOracle
Une erreur interne est produite quand un bloc PL/Sql viole une règle d'Oracle ou dépasse une limite dépendant du système d'exploitation.
Les noms derreurs fournis par Oracle sont regroupées dans ce tableau :
CURSOR_ALREADY_OPENSTORAGE_ERRORDUP_VAL_ON_INDEX TIMEOUT_ON_RESOURCEINVALID_CURSORTOO_MANY_ROWS INVALID_NUMBER TRANSACTION_BACKED_OUTLOGIN_DENIED VALUE_ERRORNO_DATA_FOUND ZERO_DIVIDE NOT_LOGGED_ON OTHERS PROGRAM_ERROR
Exemple : Utilisation des erreurs prédéfinies
DECLARE wsal emp.sal%type; BEGIN select sal into wsal from emp; EXCEPTION WHEN TOO_MANY_ROWS then ... ; -- gérer erreur trop de lignes WHEN NO_DATA_FOUND then ... ; -- gérer erreur pas de ligne WHEN OTHERS then ... ; -- gérer toutes les autres erreurs END ;
b) Les erreurs utilisateurs (externes)
PL/Sql permet à l'utilisateur de définir ses propres exceptions.
La gestion des anomalies utilisateur peut se faire dans un bloc PL/Sql en effectuant les opérations suivantes :
1. Nommer l'erreur (type exception) dans la partie Declare du bloc.
DECLARE
Nom_ano Exception;
2. Déterminer l'erreur et passer la main au traitement approprié par la commande Raise.
BEGIN
If (condition_anomalie) then raise Nom_ano ;
3. Effectuer le traitement défini dans la partie EXCEPTION du Bloc.
EXCEPTION
WHEN (Nom_ano) then (traitement);
Syntaxe :
DECLARE ... Nom_ano EXCEPTION; BEGIN ... instructions ; IF (condition_anomalie) THEN RAISE Nom_ano ... EXCEPTION WHEN Nom_ano THEN (traitement); END ;
On sort du bloc après l'exécution du traitement d'erreur.
Exemple :
DECLARE
Erreur_comm exception ;
Res_pilot pilote%rowtype ;
BEGIN
Select * into Res_pilot From Pilote
Where nopilot = 7100 ;
If res_pilot.comm > res.pilot.sal Then
Raise erreur_comm ;
.......
EXECPTION
When erreur_comm then
Insert into erreur values(res_pilot.nom, Commission > salaire) ;
When NO_DATA_FOUND Then
Insert into erreur values(res_pilot.nopilot, non trouvé) ;
END ;
c) Visualiser les erreurs non prévues
Le développeur peut utiliser les fonctions propres à PL.SQL Sqlcode et Sqlerrm pour coder les erreurs Oracle en Exception.
Sqlcode : est une fonction propre à PL/Sql qui retourne une valeur numérique : le numéro (généralement négatif) de l'erreur courante.
Sqlerrm : renvoit le libellé de l'erreur courante ou
reçoit en entrée le numéro de l'erreur et renvoie en sortie le message, correspondant au code de l'erreur si spécifié, codé sur 196 octets.
Exemple : Utilisation des erreurs prédéfinies et nommées
Create table resultat(number, char(50) / DECLARE wsal emp.sal%type; sal_zero Exception; code number; lg number; mess char(50); BEGIN select sal into wsal from emp; if wsal=0 then raise sal_zero end if; EXCEPTION WHEN sal_zero then -- gérer erreur salaire WHEN TOO_MANY_ROWS then ... ; -- gérer erreur trop de lignes WHEN NO_DATA_FOUND then ... ; -- gérer erreur pas de ligne WHEN OTHERS then ... ; -- gérer toutes les autres erreurs code := sqlcode; mess := sqlerrm ; lg := length(mess); insert into resultat values (code,lg,mess); commit; END ;
IV EXERCICE DE SYNTHESE
Ecrire le programme PL/SQL qui permette de saisir une nouvelle affectation.
Les données à saisir sont :
Ville de départ
Ville darrivée
Numéro davion
Numéro de pilote
Date de vol
Avant lajout de la nouvelle affectation, les contrôles suivants sont à effectuer :
Un pilote ne peut être affecté à un vol ayant une date de vol antérieure à sa date dembauche.
Lavion affecté au vol doit être disponible sur laéroport de départ depuis au moins 6 heures .
Remarque : On suppose quil nexiste quun seul vol entre la ville de départ et la ville darrivée.
Vous devez créer une table erreur et une table résultat .
Vous trouverez sur la page suivante : 2 tests possibles. A vous de prévoir dautres tests afin de passer en revue lensemble des possibilités danomalies : date embauche > Date vol etc ...Test N° 1
SQL> @ ../gautier/exo5_plsql
"Donner la ville de départ" PARIS
"Donner la ville d'arrivée" ST MARTIN
"Donner le numéro d'avion" 8467
"Donner le numéro de pilote" 3452
"Donner la date du vol" 11/03/96
Table créée. ( Création dune table erreur
Table créée. ( Création dune table résultat
Procédure PL/SQL terminée avec succès.
aucune ligne sélectionnée ( Rien dans la table erreur
Table supprimée.
MSG2 ( Contenu table résultat
-----------------------------------------------
OK - la date embauche est antérieure à date vol
le vol IW433 a été trouvé
OK : avion non arrivé ce jour là
le tuple est ajouté dans affect
Table supprimée.
Test N° 2
SQL> @ ../gautier/exo5_plsql
"Donner la ville de départ" PARIS
"Donner la ville d'arrivée" ST MARTIN
"Donner le numéro d'avion" 8467
"Donner le numéro de pilote" 3452
"Donner la date du vol" 20/12/94
Table créée. ( Création dune table erreur
Table créée. ( Création dune table résultat
Procédure PL/SQL terminée avec succès.
MSG1 ( Table erreur
-----------------------------------------------------------
Le délai est trop court pour avion
Table supprimée.
MSG2 ( table résultat
-----------------------------------------------------------
OK - la date embauche est antérieure à date vol
le vol IW433 a été trouvé
heure arrivée avion 12 heures
Table supprimée.
Quelques indications à dire oralement :
( Comment tester pas de tuple trouvé en ayant à laffichage si le problème est dans la table pilote ou dans la table vol ?
2 blocs imbriqués = 2 zones exceptions différentes
DECLARE
..
BEGIN
Select .... From pilote
.....
BEGIN
Select .... from vol
....
EXCEPTION
When NO_DATA_FOUND then
Insert into erreur
Values (tuple vol non trouvé) ;
END ;
EXCEPTION
When NO_DATA_FOUND then
Insert into erreur
Values (tuple pilote non trouvé) ;
END ;
( On a une phase compilation avant la phase exécution.
Si on saisit un pilote inexistant ex : nopilot = 9999, il nous jette dès la compilation : problème de contrainte dintégrité référentielle sur le Insert into affect Values ...
( Ecrire define au lieu de accept pour la phase de test.
TM \o "1-3" \n
PARTIE 2 - LE LANGAGE PROCEDURAL DORACLE : LE LANGAGE PL/SQL
I LES CURSEURS EN PL/SQL
1. La déclaration d'un curseur
2. L'ouverture du curseur
3. Traitement des lignes
4. La fermeture du curseur
5. Complément : Utiliser une variable de type enregistrement
6. Exercices dapplication
II MODIFICATION DE DONNEES
III GESTION DES ERREURS
a) Les erreurs internes dOracle
b) Les erreurs utilisateurs (externes)
c) Visualiser les erreurs non prévues
IV EXERCICE DE SYNTHESE
Bibliographie :
Oracle 7 Editions Laser Roger CHAPUIS
Oracle 7 Langages Architecture Administration Eyrolles ABDELLATIF, LIMANE et ZEROUAL
Oracle (version 7) _ Editions ENI Manuel pratique MEGA +
Le langage PL/SQL Stage MAFPEN Christian FISCHER
CORRECTION DES EXERCICES
-- Programme EXO4_PLSQL.sql --
DECLARE
Cursor C1 is
select * from dept
for update of budget;
resC1 c1%rowtype;
tot dept.budget%type;
BEGIN
Open C1;
Fetch C1 into resC1;
While C1%found Loop
Select sum(sal) into tot from emp
where deptno = resC1.deptno;
update dept
set budget = tot
where current of c1;
Fetch C1 into resC1;
end loop;
CLOSE C1;
END;
/
select * from dept
/
SQL> @ ../gautier/exo4_plsql
Procédure PL/SQL terminée avec succès.
DEPTNO DNAME LOC BUDGET
--------- -------------- ------------- ---------
10 ACCOUNTING NEW YORK 8750
20 RESEARCH DALLAS 11139,8
30 SALES CHICAGO 9400
40 OPERATIONS BOSTON
50 INFORMATIQUE NANTES
Exercice de synthèse
Prompt "Donner la ville de départ"
Accept wvildep
Prompt "Donner la ville d'arrivée"
Accept wvilar
Prompt "Donner le numéro d'avion"
Accept wnuavion
Prompt "Donner le numéro de pilote"
Accept wnupilot
Prompt "Donner la date du vol"
Accept wdate
CREATE TABLE ERREUR(msg1 CHAR(200))
/
CREATE TABLE RESULTAT (msg2 char(200))
/
-- PROGRAMME EXO5_PLSQL.sql ---
DECLARE
err_date exception;
err_delai exception;
date_emb pilote.embauche%type;
num_vol vol.novol%type;
date_vol affect.datevol%type;
heure_dep vol.deph%type;
heure_arr vol.arh%type;
mess char(50);
Cursor c1 is
Select arh From vol, affect
where affect.novol = vol.novol
and datevol ='&wdate'
and vilar = '&wvildep'
and nuavion = '&wnuavion';
BEGIN /* début 1 Vérifier sur pilote */
-- Vérification pour la date du vol --
Select embauche into date_emb from pilote
where nopilot = '&wnupilot';
if '&wdate' < date_emb then
raise err_date;
end if;
insert into resultat
values ('OK - la date embauche est antérieure à date vol');
BEGIN /* début 2 sur vol */
-- récupération du numéro de vol et de l'heure de vol
select novol, deph into num_vol, heure_dep From vol
where vildep = '&wvildep'
and vilar = '&wvilar';
mess := 'le vol ' || num_vol || ' a été trouvé';
insert into resultat
values (mess);
-- Vérification pour les 6 heures de battement de l'avion --
Open C1;
Fetch C1 into heure_arr;
if C1%found then
mess := 'heure arrivée avion ' || heure_arr || ' heures';
insert into resultat
values (mess);
If heure_arr + 6 >= heure_dep then
raise err_delai;
else
insert into resultat
values ('avion arrivé ce jour mais délai OK');
end if;
else
insert into resultat
values ('OK : avion non arrivé ce jour là');
end if;
Close C1;
-- Tout va bien on fait l'insertion --
Insert into affect
values (num_vol, '&wdate','&wnupilot','&wnuavion',0);
insert into resultat
values ('le tuple est ajouté dans affect');
EXCEPTION
When NO_DATA_FOUND Then
insert into erreur
values ('tuple non trouvé dans la table vol');
When ERR_DELAI Then
insert into erreur
values ('Le délai est trop court pour avion');
END; /* fin de 2 */
EXCEPTION /* fin de 1 */
When NO_DATA_FOUND Then
insert into erreur
values ('tuple non trouvé dans la table pilote');
When ERR_DATE Then
insert into erreur
values ('Impossible date vol < date embauche');
mess := sqlerrm;
insert into erreur
values (mess);
END;
/
select * from erreur
/
drop table erreur
/
select * from resultat
/
drop table resultat
/
Cours - Le PL SQL Partie 2 Page PAGE 18
Geneviève Gautier
INCORPORER PBrush
