programmer avec pl sql 250306
TRANSCRIPT
<Course name> <Lesson number>-1
Programmer avec PL/SQL
Institut Supérieur d’Informatique et de Gestion (ISIG)Année académique 2005 - 2006
Classe : IT3Enseignant : Roland Olivier KYEDREBEOGO, Oracle Certified Profesional
(c) 2006, KROS, nextStep Technologies
Sommaire
0. Présentation du langage PL/SQL1. Déclarer des variables2. Ecrire des instructions exécutables3. Interagir avec le serveur ORACLE4. Ecrire des structures de contrôle5. Ecrire des curseurs explicites6. Traiter les exceptionsCompléments
2
Présentation de PL/SQL
003
<Course name> <Lesson number>-2
(c) 2006, KROS, nextStep Technologies
Objectifs
A la fin de ce chapitre, vous pourrez: Décrire la finalité du Langage PL/SQLDécrire l’utilisation du LPL pour le développeurd’applications et l’administrateur de bases de donnéesPrésenter les avantages liés à l’utilisation duLPL/SQL
4
(c) 2006, KROS, nextStep Technologies
Environnement PL/SQL
Bloc
PL/SQL
Bloc
PL/SQL
Prog. d’exécution des instructions
procédurales
Programme d’exécution des instructions SQL
Moteur
PL/SQL
Serveur ORACLE
SQL
PL/SQL
5
(c) 2006, KROS, nextStep Technologies
Avantages de PL/SQL
INTEGRATIONRôle essentiel aussi bien pour le serveur Oracle (procédures et fonctions stockées, packages, déclencheurs de base de données) et les outils de développement (déclencheurs d’applications)Les applications utilisent des bibliothèques partagéesaccessibles localement ou à distanceTypes de données SQL utilisablesExécution du code PL dans le moteur interne des outils et du code SQL sur le serveur Oracle
6
<Course name> <Lesson number>-3
(c) 2006, KROS, nextStep Technologies
Avantages de PL/SQL
AMELIORATION DES PERFORMANCESEnvoi des instructions SQL au serveur Oracle en un seul appel réduction du traffic réseau
Sans PL/SQL les instructions SQ sont envoyées uneà une au serveur
Possibilité d’utilisation des structures de contrôlepour identifier les blocs à exécuter
7
(c) 2006, KROS, nextStep Technologies
Avantages de PL/SQL
MODULARITEPORTABILITEDECLARATION DE VARIABLESSTRUCTURES DE CONTROLESTRAITEMENT DES EXCEPTIONS
DECLARE….
BEGIN….
EXCEPTION…
END ;
8
(c) 2006, KROS, nextStep Technologies
Avantages de PL/SQL
SQLIF…THEN
SQLELSESQL
END IF;SQL
Application
Application
AUTRESSGBD
Oracle et PL/SQL
SQL
SQL
9
<Course name> <Lesson number>-4
(c) 2006, KROS, nextStep Technologies
Synthèse
Le langage PL/SQL est une extension dulangage SQLLes blocs de code PL/SQL sont transmis à un moteur PL/SQL et sont traités par celui-ciAvantages liés à l’utilisation de PL/SQL :
IntégrationModularitéPortabilitéOptimisation du développement
10
Déclarer des variables
1111
(c) 2006, KROS, nextStep Technologies
Objectifs
A la fin de ce chapitre, vous pourrez: Reconnaître un bloc PL/SQL de base et sesdifférentes sectionsDécrire la signification des variables en PL/SQLDéclarer des variables PL/SQLExécuter un bloc PL/SQL
12
<Course name> <Lesson number>-5
(c) 2006, KROS, nextStep Technologies
Structure d’un bloc PL/SQL
DECLARE (facultatif)User defined variables, cursors, exceptions
BEGIN (obligatoire)SQL StatementsPL/SQL Statements
EXCEPTION (facultatif)What to do on when errors occur
END ; (obligatoire)
DECLARE….
BEGIN….
EXCEPTION…
END ;
13
(c) 2006, KROS, nextStep Technologies
Exécuter des blocs PL/SQL
DECLAREv_variable VARCHAR2(5);
BEGINSELECT column_nameINTO v_variableFROM table_name ;
EXCEPTIONWHEN exception_name THEN
…END ;
DECLARE….
BEGIN….
EXCEPTION…
END ;
14
(c) 2006, KROS, nextStep Technologies
Exécuter des blocs PL/SQL
Un point-virgule (;) à la fin d’une instruction (SQL, PL/SQL)Procedure PL/SQL exécutée avec succès ! DECLARE, BEGIN et EXCEPTION ne sont pas suivis de ;END est suivi de ;CONSEIL : Clarté et facilité d’édition de vos programmes une instruction par ligne !MODULARITE : Aller du simple au compliqué créer les blocs, sous-programmes, packages appropriés pour la résolution
15
<Course name> <Lesson number>-6
(c) 2006, KROS, nextStep Technologies
Types de blocs PL/SQL
[DECLARE]….BEGIN….[EXCEPTION]…END ;
[DECLARE]….BEGIN….[EXCEPTION]…END ;
PROCEDURE nameIS….BEGIN….[EXCEPTION]…END ;
PROCEDURE nameIS….BEGIN….[EXCEPTION]…END ;
FUNCTION nameRETURN datatypeIS….BEGIN….RETURN value ;[EXCEPTION]…END ;
FUNCTION nameRETURN datatypeIS….BEGIN….RETURN value ;[EXCEPTION]…END ;
AnonymeProcédure
-Applicative-Stockée
Fonction- Applicative- Stockée
16
(c) 2006, KROS, nextStep Technologies
Structures de programme
DECLARE….
BEGIN….
EXCEPTION…
END ;
OUTILSAnonymous
Types d’objet
Déclencheurs applicatifs
Packages applicatifs
Proc. Func. Applicatives
SERVEUR
Anonymous
Types d’objet
Déclencheurs de base de données
Packages Stockés
Proc. Func. Stockées
17
(c) 2006, KROS, nextStep Technologies
Using & processing variables
Stockage temporaire de donnéesManipulation de valeurs stockéesPossibilité de réutilisationFacilité de maintenance (%TYPE)
Déclaration - section déclarativeAffectation de valeurs – section exécutableTransmission aux blocs – paramètres (IN, OUT, IN OUT)Affichage – variables de sortie
U S I N G
P R O C E S S I N G 18
<Course name> <Lesson number>-7
(c) 2006, KROS, nextStep Technologies
Déclaration de variables
identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expression ];
DECLAREv_hiredate DATE := ’04/03/2006’;v_deptno NUMBER(2) NOT NULL := 4 ;v_location VARCHAR2(13) := ‘IT30506’;c_comm CONSTANT NUMBER := 336 ;
19
(c) 2006, KROS, nextStep Technologies
Déclaration de variables - Règles
Suivre les conventions d’appellationInitialiser les variables NOT NULL et CONSTANTDéclarer 1 identificateur par ligneInitialiser les identificateursDeux variables peuvent porter le même nom si dans deux blocs distinctsNom de variable <> Nom des colonnes des tables utilisées dans le bloc. Adopter la convention v_% Longueur max = 30 ; Premier caractère UNE LETTRE suivie de lettres, nombres ou car. spéciaux
20
(c) 2006, KROS, nextStep Technologies
Types de variables
Variables PL/SQLScalaires : reçoivent une valeur uniqueComposites : manipulent des groupes de champsRéférencées : pointeurs (NON TRAITEES)LOB (Large Object) : pointeurs indiquant l’emplacementdes objets volumineux stockés à l’extérieur des tables (cf. chapitre dédié)
Variables NON PL/SQLVariables déclarées dans les précompilateursChamps d’écran des formulairesVariables hôtes iSQL*Plus
21
<Course name> <Lesson number>-8
(c) 2006, KROS, nextStep Technologies
Types de données scalaires
Stockent une seule valeurNumériques, Caractères, Dates et Booléens
Types de données scalaires de base :CHAR [ (max_length)] Longueur par défaut = 1VARCHAR2 (max_length)LONGLONG RAW : données binaires (pas interprété par PL)NUMBER [ (precision, scale) ]BINARY_INTEGERPLS_INTEGERBOOLEAN > Cf. Paper-PLSQL-1-18-21
22
(c) 2006, KROS, nextStep Technologies
Types de données scalaires
Types de données scalaires de base :DATE TIMESTAMP Date + A+M+J+H+M+STIMESTAMP WITH TIME ZONE TIMESTAMP + décalage horaireTIMESTAMP WITH LOCAL TIME ZONEINTERVAL YEAR TO MONTHINTERVAL DAY TO SECOND
> Cf. Paper-PLSQL-1-18-21
23
(c) 2006, KROS, nextStep Technologies
Attribut %TYPE
Déclarer une variable dont le type se base :Sur celui d’une colonne de table de la BDSur celui d’une autre variable précédemment déclarée
Faire précéder %TYPEDes noms de la table et de la colonne concernéesDu nom de la variable précédente
QUEL PEUT ËTRE L’INTERET DE %TYPE ?24
<Course name> <Lesson number>-9
(c) 2006, KROS, nextStep Technologies
Déclarer des variables avec %TYPE
identifier Table.Column_name%TYPE ;
DECLAREv_name employees.last_name%TYPE;v_bidon1 NUMBER(5,3) ;v_bidon2 v_bidon1%TYPE := 10 ;
25
(c) 2006, KROS, nextStep Technologies
Déclarer des variables booléennes
Attributs autorisés : TRUE, FALSE, NULLVariables comparées avec AND, OR et NOTVariables renvoient TOUJOURS TRUE, FALSE ou NULLDes expressions peuvent être utilisées
26
(c) 2006, KROS, nextStep Technologies
Types de données composites
Encore appelées CollectionsPossèdent des composants internes pouvant êtremanipulés individuellement
TABLE : Réferencer des collections comme objetuniqueRECORD : Données liéés mais dissemblablesNESTED TABLES et VARRAY (Cf. Advanced PL/SQL)
27
<Course name> <Lesson number>-10
(c) 2006, KROS, nextStep Technologies
Variables de type LOB
Stockage de blocs de données non structurés dont la taillepeut atteindre 4 GoCLOB : Character LOB : grands blocs de caractères à simple octet dans la BD, à l’intérieur ou à l’extérieur de la ligne de la table BLOB : Binary LOB : Objets binaires volumineux dans la BD, à l’intérieur ou en dehors de la ligne de la tableBFILE : Binary File : Objets binaires volumineux dans les fichiers de l’OS, à l’extérieur de la BDNCLOB : NLS Character LOB : grands blocs de donnéesUnicode NCHAR à simple ou multiple octets de longueur fixedans la BD, à l’intérieur ou à l’extérieur ou en dehors de la ligne de la table
28
(c) 2006, KROS, nextStep Technologies
Variables attachées (bind)
Variables NON PL/SQL déclarées HORS DU BLOCUtiliser la fonction VARIABLE pour les initialiser !Utiliser : pour les réferencerUtiliser PRINT pour les afficher !
VARIABLE RESULTAT NUMBER ;-----BEGIN
SELECT (salary*12) INTO :RESULTATWHERE employee_id = 144 ;
END ;/PRINT RESULTAT
?29
(c) 2006, KROS, nextStep Technologies
DMBS_OUTPUT.PUT_LINE
Procédure de package fournie par Oracle2e méthode d’affichageVARIABLE v_result NUMBER----DECLARE
v_result NUMBER(9,2) := 150000 ;BEGIN
v_result := v_result/30 ;DBMS_OUTPUT.PUT_LINE(‘Le résultat est‘|| TO_CHAR(v_result));
END ;/----PRINT v_result ;
SET SERVEROUTPUT ONSET SERVEROUTPUT OFF
30
<Course name> <Lesson number>-11
(c) 2006, KROS, nextStep Technologies
Variables de substitution
Utilisées dans le passage d’argument à un bloc ou à un script PL/SQL Utiliser la fonction DEFINE pour les initialiser !Utiliser & pour les réferencerUtiliser DEFINE pour les afficher !
DEFINE p_id = 144 ;VARIABLE RESULTAT NUMBER ;-----BEGIN
SELECT (salary*12) INTO :RESULTATWHERE employee_id = &p_id ;
END ;/DEFINE p_idPRINT RESULTAT
SET VERIFY OFFSET VERIFY ON
31
(c) 2006, KROS, nextStep Technologies
Synthèse
Ce chapitre vous a appris les règles suivantes :Les identificateurs PL/SQL
Sont définis dans la section déclarativePeuvent être de type scalaire, composite, reférencéou LOBPeuvent être basés sur la structure d’une autrevariable ou d’un autre objet de BDPeuvent être initialisésVariables d’environnement externe = variables hôtes
DBMS_OUTPUT.PUT_LINE permet…
32
(c) 2006, KROS, nextStep Technologies
Exercice 1
1. Evaluer chacune des expressions suivantes. Déterminer celles qui NE SONT PAS valides et expliquer pourquoi
a. v_id1 NUMBER(4)b. v_id2 NUMBER(4, 2)c. v_x, v_y, v_z VARCHAR2(30)d. v_birthdate DATE NOT NULL ;e. v_in_stock BOOLEAN := 1 ;
2. Dans chacune des affectations suivantes, indiquez si l’instruction est valide et préciser le type de données du résultat
a. v_date2 := v_date1 + sysdate ;b. v_truc = “Bonjour” || TO_CHAR(v_numero);c. v_somme := 1000$ + 3.500$d. v_machin := ‘TRUE’e. v_n1 := v_n2 > (2*v_n3) ;
3. Créer un bloc PL/SQL qui affiche HELLO IT3. Enregistrer ce script sous et lancez le en ligne de commande avec RUN !- avec PRINT - avec DBMS_OUTPUT
33
<Course name> <Lesson number>-12
Ecrire des instructions exécutables
2234
(c) 2006, KROS, nextStep Technologies
Objectifs
A la fin de ce chapitre, vous pourrez: Comprendre l’utilité de la section exécutableUtiliser correctement les identificateursEcrire des instructions exécutablesDécrire les règles des blocs imbriquésExécuter et tester un bloc PL/SQLUtiliser les conventions de codage
35
(c) 2006, KROS, nextStep Technologies
Syntaxe et remarques
Barre / permet d’exécuter les blocs PL/SQLLes instructions des blocs PL/SQL peuvents’étendre sur plusieurs lignesLes unités lexicales se répartissent comme suit :
Délimiteurs (symboles simples et composés)Identificateurs (incluent les mots réservés)LittérauxCommentaires
Séparer les unités lexicales par des espacesNe pas scinder les mots clés !Mettre les littéraux entre apostrophes
36
<Course name> <Lesson number>-13
(c) 2006, KROS, nextStep Technologies
Délimiteurs PL/SQL
SYMBOLES SIMPLES : +, -, *, /, =, @, ;
SYMBOLS COMPOSES : <>, !=, ||, --, /*, */, :=
Les mots réservés ne peuvent pas être utilisés comme identificateurs, à moins de les mettre entre guillemets (exemple "SELECT")
37
(c) 2006, KROS, nextStep Technologies
Identificateurs PL/SQL
Jusqu’à 30 caractèresDoivent commencer par valeur alphabétiquePeuvent contenur des valeurs numériques, des traits de soulignement, des signes $ et #Ne doivent pas contenir de traits d’unions, de barres obliques ni d’espaces
Exemples valides : money$$$tree__, SN#, try_againExemples non valides : r&b, debit-credit, on/off, user id
38
(c) 2006, KROS, nextStep Technologies
Commentez votre code !
Faire précéder les commentaires monolignes de deux tirets (--)Placer les commentaires multilignes entre les symboles /* et */
39
<Course name> <Lesson number>-14
(c) 2006, KROS, nextStep Technologies
Fonctions SQL en PL/SQL
DisponiblesFonctions monolignes
NumériquesDe type caractère
Fonctions de conversion de type de donnéesFonctions de datesFonctions d’horodatage
Non-disponiblesFonction DECODEFonctions de groupe
40
(c) 2006, KROS, nextStep Technologies
Fonctions de conversion
Convertir des données en types de donnéescomparablesMélanger les types de données peut provoquerdes erreurs ou nuire aux performancesFonctions de conversion :
TO_CHAR(value, fmt)TO_DATE(value, fmt)TO_NUMBER(value, fmt)
Exemples :-v_date1 = TO_CHAR(sysdate, ‘DD/MM/YYYY’);- v_date2 = ‘8 Mars 2006’; 41
(c) 2006, KROS, nextStep Technologies
Blocs imbriqués – Portée des variables
Les blocs PL/SQL peuvent être imbriqués, même dans le traitement des exceptionsTout bloc imbriqué devient une instructionPortée d’un identificateur = région du programme dans laquelle il peut être référencé1 bloc peut réferencer le bloc englobant1 bloc ne peut référencer les blocs qu’il englobe
42
<Course name> <Lesson number>-15
(c) 2006, KROS, nextStep Technologies
Opérateurs en PL/SQL
Idems en SQLOpérateur logiqueOpérateurs arithmétiquesOpérateurs de concaténation
Parenthèses de contrôle de l’ordre
Opérateur exponentiel (**)
43
(c) 2006, KROS, nextStep Technologies
Conseils pour la programmation
Commenter le codeUtiliser les conventions typographiques ORACLE (convention de nommage pour l’utilisation de la casse, les identificateurs et autres objetsIndenter le code
DECLAREv_sal NUMBER(9,2); -- variable de récupération
BEGIN-- Sélectionner le salaire de l’employé 144-- dans ma variable v_salSELECT salaryINTO v_salWHERE employee_id = 144 ;
-- Voici la fin de mon blocEND ;
44
(c) 2006, KROS, nextStep Technologies
Conventions typographiques
SELECT last_nameFROM employees ;
Noms de colonne, noms de table, noms de fichiers, objets PL/SQL
Minuscules
When-Validate-ItemDéclencheurs Oracle Forms
Initiale en majuscule
CREATE ROLE role ;v_salaryNUMBER(5,2);
VariablesMinuscules
SELECT last_nameFROM employees ;
Commandes et fonctions
Majuscules
ExempleObjet ou termeConvention
45
<Course name> <Lesson number>-16
(c) 2006, KROS, nextStep Technologies
Conventions typographiques (2)
employees ; employee_id ; departments ; dept_no
Noms de colonne, noms de table,
Minuscules
v_salary ; c_nameIdentificateurs et paramètres
Minuscules
DECLARE, BEGIN, END ;Mots-clés PL/SQL, Majuscules
SELECT, INSERT, NUMBER,
Instruction SQL, types de données
Majuscules
ExempleCatégorieConvention
46
(c) 2006, KROS, nextStep Technologies
Synthèse
Ce chapitre vous a appris les règles suivantes : Syntaxe et remarques sur les blocs PL/SQLUtilisation correcte des identificateursStructure des blocs PL/SQL : imbrication des blocs et portée des identificateursProgrammation en PL/SQL
FonctionsFonctions de conversion de types de donnéesOpérateursConventions et remarques
Exo-PLSQL2 47
(c) 2006, KROS, nextStep Technologies
Exercice 21. Créez et exécutez un bloc PL/SQL qui accepte deux valeurs numériques
via des variables de substitution SQL*PLUS. Sauvegarder ce script en tant que script21.sql
Utilisez la commande DEFINE pour indiquer les deux valeursDEFINE p_num1 = 2DEFINE p_num1 = 4
Transmettez au bloc PL/SQL, via les variables de substitution les deux valeurs définies en a). Divisez la première valeur par la seconde, et ajouter cette dernière au résultat qui doit être stocké dans une variable PL/SQL qui s’affiche à l’écranRésultat attendu :
4.5Procédure PL/SQL terminée avec succès.
2. Modifiez script21.sql pour obtenir le résultat suivant(Enregistrer sous script22.sql):Le résultat est : 4.5Procédure PL/SQL terminée avec succès.
3. Modifiez script22.sql pour passer les deux valeurs en paramètre. L’appel du script doit être de la forme START script22.sql valeur1 valeur2
48
<Course name> <Lesson number>-17
Interagir avec le serveur Oracle
3349
(c) 2006, KROS, nextStep Technologies
Objectifs
A la fin de ce chapitre, vous pourrez: Ecrire une instruction SELECT correcte en PL/SQLEcrire des instructions LMD en PL/SQLContrôler des transactions en PL/SQLDéterminer le résultat de l’exécution d’instructionsSQL LMD
50
(c) 2006, KROS, nextStep Technologies
Instructions SQL en PL/SQL
Extraire des lignes de données à l’aide de SELECTUtiliser des commandes LMDContrôler les transactions à l’aide de COMMIT, ROLLBACK, SAVEPOINTExploiter des curseurs implicites
51
<Course name> <Lesson number>-18
(c) 2006, KROS, nextStep Technologies
Instructions SELECT en PL/SQL
SYNTAXESELECT select_listINTO {variable_name[, variable_name]… | record_name }FROM table[WHERE condition] ;
> La clause INTO est OBLIGATOIRE !> Indiquer une variable pour chaque expression renvoyée> Les interrogations doivent renvoyer UNE et UNE SEULE LIGNE !
52
(c) 2006, KROS, nextStep Technologies
Extraire des données (1)
Exemple : Extraire la date d’embauche et le salaire de l’employé N° 100 !
DECLAREv_date_emb employees.hire_date%TYPE ;v_salaire employees.salary%TYPE;
BEGINSELECT hire_date, salaryINTO v_date_emb, v_salaireFROM employeesWHERE employee_id = 100 ;
END ;/
53
(c) 2006, KROS, nextStep Technologies
Extraire des données (2)
Exemple : Renvoyer la somme des salaires des employés d’un service !
DECLAREv_sum NUMBER(10,2);v_dept_no NUMBER NOT NULL := 10 ;
BEGINSELECT SUM(salary)INTO v_sumFROM employeesWHERE department_id = v_dept_no;
DBMS_OUTPUT.PUT_LINE(‘Total des salaires =’ || TO_CHAR(v_sum));END ;/
54
<Course name> <Lesson number>-19
(c) 2006, KROS, nextStep Technologies
Manipuler des données
INSERT : ajout de nouvelles lignesUPDATE : maj de lignes existantesDELETE : suppression de lignesMERGE : maj de lignes si existantes et ajout dans le cas contraire*
MERGE Privilèges INSERT et UPDATE !
55
(c) 2006, KROS, nextStep Technologies
Insérer des données
Ajouter un nouvel employé :BEGIN
INSERT INTO employees(employee_id, first_name, last_name, email, hire_date, job_id, salary)
VALUES(221,'KYEDREBEOGO', 'Roland Olivier', '[email protected]', SYSDATE, 'IT_PROG', 1000) ; END ;
/Possibilités avec INSERT Dans un bloc PL/SQL :
-Utiliser des fonctions SQL (USER, SYSDATE, …)
-Générer des séquences (numérotation automatique)
56
(c) 2006, KROS, nextStep Technologies
Mettre à jour des données
Augmenter de 1000 le salaire l’employéque vous venez de créerDECLARE
v_sal_new NUMBER := 1000;BEGIN
UPDATE employeesSET salary = salary + v_sal_plusWHERE employee_id = VOTRE_ID ;
END ;/ SELECT last_name, salary
FROM hr.employeesWHERE employee_id = ID_DE_VOTRE_VOISIN ;
57
<Course name> <Lesson number>-20
(c) 2006, KROS, nextStep Technologies
Supprimer des données
Supprimez-vous de la table employés !
BEGINDELETE FROM hr.employeesWHERE employee_id = VOTRE_ID ;
END ;/
58
(c) 2006, KROS, nextStep Technologies
Fusionner des lignes : MERGE
MERGE permet d’insérer ou de mettre àjour des lignes dans une table, en utilisant les données d’une autre.Exemple :
Si copy_emp.employee_id = employees.employee_id Alors MAJSinon Insertion Finsi
Cf. Paper 3-1559
(c) 2006, KROS, nextStep Technologies
Conventions d’appellation
Éviter les ambiguïtés dans la clause WHERENoms des colonnes de BD <> Noms des identificateursNoms des variables locales prioritaires sur noms des tables de la BDNoms des colonnes de BD prioritaires sur noms des variables locales
60
<Course name> <Lesson number>-21
(c) 2006, KROS, nextStep Technologies
Conventions d’appellation 2)
g_year_salg_nameVariable hôte
p_job_idp_nameVariable de substitutiondept_recordname_recordEnregistrement
emp_record_typename_record_typeType d’enregistrementcountriesnameTable
total_table_typename_table_typeType de tablee_too_manye_nameExceptionemp_cursorname_cursorCurseurc_emp_namec_nameConstantev_salaryv_nameVariableExempleConventionIdentificateur
61
(c) 2006, KROS, nextStep Technologies
Notion de curseur SQL
Zone de travail réservée à SQL2 types :
Curseur implicites : utilisés par le serveur Oracle pour analyser et exécuter les instructions SQLCurseur explicite : déclarés de manière explicite par le programmeur
>> A chaque instruction SQL, le Serveur Oracle ouvre une zone deMémoire dans laquelle la commande est analysée et exécutée !
62
(c) 2006, KROS, nextStep Technologies
Attributs d’un curseur SQL
Prend toujours la valeur FALSE car PL/SQL ferme les curseurs implicites immédiatement après leur exécution
SQL%ISOPEN
Attribut booléen qui prend la valeur TRUE si la dernière instruction SQL n’affecte aucune ligne
SQL%NOTFOUND
Attribut booléen qui prend la valeur TRUE si la dernière instruction SQL affecte une ou plusieurs lignes
SQL%FOUND
Nombre de lignes affectées par la dernière instruction SQL
SQL%ROWCOUNT
DEFINITIONATTRIBUT
Attributs valides UNIQUEMENT dans PL/SQL et non SQL ! 63
<Course name> <Lesson number>-22
(c) 2006, KROS, nextStep Technologies
Attributs de curseur - Exemple
Supprimer les lignes possédant l’ID indiqué et renvoyer le nombre de lignes
VARIABLE v_msg_suppr VARCHAR2(30);- - -DECLARE
v_employee_id employees.employe_id%TYPE := 176;BEGIN
DELETE FROM employeesWHERE employee_id = v_employee_id;:v_msg_suppr := (SQL%ROWCOUNT || ‘ lignes supprimées !’);
END ;/PRINT v_msg_suppr ;
64
(c) 2006, KROS, nextStep Technologies
Gestion des transactions
Transaction : commence à la première commande qui suit COMMIT ou ROLLBACK et s’achève à la prochaine commande COMMIT ou ROLLBACK correctement exécutée !Pour définir des points intermédiaires, utiliser SAVEPOINT
65
(c) 2006, KROS, nextStep Technologies
Synthèse
Ce chapitre vous a permis de savoirIntégrer du code SQL dans un bloc PL/SQLIntégrer des instructions de gestion des transactionsLes fonctions des 2 types de curseurLes attributs des curseurs implicitesExo-PLSQL3
66
<Course name> <Lesson number>-23
(c) 2006, KROS, nextStep Technologies
Exercice 31.Créez un bloc PL/SQL qui sélectionne dans la table DEPARTMENTS le plus grand numéro de service et le stocke dans une variable SQL*Plus et l’affiche. Enregistrez ce script sous script31.sql
2. Modifiez script31.sql pour insérer un nouveau département dans la table DEPARTMENTS en utilisant la commande DEFINE pour définir son nom. Ajoutez 10 à la valeur maximale trouvée dans script31.sql et affectez cette valeur comme numéro de service de votre département. Affichez le numéro et le nom de votre nouveau département. Enregistrez ce script sous script32.sql
3. Modifiez script32.sql pour mettre à jour le numéro de location du nouveau département. Utilisez la commande DEFINE pour définir le nouveau numéro. Consultez les valeurs distinctes des locations disponibles pour respecter les contraintes. Affichez le numéro, le nom et la location de votre département. Enregistrez ce script sous script33.sql
4. Modifiez script33.sql pour supprimer le département que vous venez de créer. Enregistrez ce script sous script34.sql. Utilisez la commande DEFINE pour passer son numéro en argument et affichez à l’écran le message suivant : Département(s) que j’ai supprimé(s) : 1
67
Ecrire des structures de contrôle
4468
(c) 2006, KROS, nextStep Technologies
Objectifs
A la fin de ce chapitre, vous pourrez: Identifier les types de structure de contrôle et leursutilisationsEcrire une instruction IFUtiliser des expressions CASEEcrire et identifier les types d’instructions LOOPUtiliser des tables logiques
69
<Course name> <Lesson number>-24
(c) 2006, KROS, nextStep Technologies
Flux d’exécution PL/SQL
Utiliser des instructions conditionnellesIF – THEN – END IFIF – THEN – ELSE - END IFIF – THEN – ELSIF - END IF
Et des structures de contrôle LOOP
70
(c) 2006, KROS, nextStep Technologies
Instructions IF
IF SIMPLES
…
IF v_ename = ‘TOTO’ THEN
v_salary := 1000 ;
END IF ;
…
IF COMPOSES…IF v_ename = ‘TOTO’ AND v_salary < 1000 THEN
v_salary := 1000 ;END IF ;….
IF condition THEN
instructions ;
[ELSIF condition THEN
instructions; ]
[ELSE
instructions; ]
END IF ;
71
(c) 2006, KROS, nextStep Technologies
Flux d’exécution IF-THEN-ELSE
IF condition
THEN actions (incluant d’autres IF)
ELSE actions (incluant d’autres IF)
TRUE FALSE
72
<Course name> <Lesson number>-25
(c) 2006, KROS, nextStep Technologies
Flux d’exécution IF-THEN-ELSIF
IF condition
THEN actions
ELSIF condition
THEN actions
ELSEactions
TRUENOT TRUE
NOT TRUETRUE
73
(c) 2006, KROS, nextStep Technologies
Remarques sur ELSIF
Utiliser ELSIF autant que faire se peut :Pour éviter les instructions IF imbriquéesPour rendre votre code plus facile à lire et sa structure plus claireSi l’action associée à ELSE consiste en une autre instruction IFPour éviter les nombreux END IF imbriqués
74
(c) 2006, KROS, nextStep Technologies
Expression CASE
CASE selecteur
WHEN expression 1 THEN result1
WHEN expression 2 THEN result2
…
WHEN expression N THEN resultN
[ELSE resultN+1;]
END;
> CASE sélectionner un résultat et le renvoie
> Effectue un choix parmi plusieurs possibilités
75
<Course name> <Lesson number>-26
(c) 2006, KROS, nextStep Technologies
Exemple d’utilisation de CASE
DECLAREv_moyenne NUMBER(2,2) := &p_moyenne ;v_mention VARCHAR2(20);
BEGIN-- Afficher la mention d’un étudiant IF v_moyenne >= 10 THEN
v_mention :=CASE v_moyenne
WHEN < 12 THEN ‘Passable’WHEN < 14 THEN ‘Assez bien’WHEN < 16 THEN ‘Bien’WHEN < 18 THEN ‘Très bien’
ELSE ‘Excellent’END ;
DBMS_OUTPUT.PUT_LINE(‘La mention correspondante à une moyenne de ‘ || v_moyenne || ‘ est : ‘ || v_mention );END IF;
END;76
(c) 2006, KROS, nextStep Technologies
Traitement des valeurs NULL
Eviter les erreurs fréquentes avec NULL :Une comparaison simple impliquant NULL renvoie NULL !NOT appliqué à NULL renvoie NULL !Dans une instruction de contrôle conditionnel, si la condition est NULL, le bloc correspondant n’est PAS EXECUTE !
Cf Paper 4-16 (Tables logiques) !
77
(c) 2006, KROS, nextStep Technologies
Tables logiques
NULLFALSENULLNULL
FALSEFALSEFALSEFALSE
NULLFALSETRUETRUE
NULLFALSETRUEAND
NULLNULLTRUENULL
NULLFALSETRUEFALSE
TRUETRUETRUETRUE
NULLFALSETRUEOR
NULLNULL
TRUEFALSE
FALSETRUE
NOT REMARQUES REMARQUES :
FALSE est prioritaire dans une cond. AND
TRUE est prioritaire dans une cond. OR
78
<Course name> <Lesson number>-27
(c) 2006, KROS, nextStep Technologies
Contrôle d’itérations
Boucle : permet l’exécution à plusieurs reprises d’une instruction ou d’une séquence d’instructions3 types de boucles
Boucle de base : LOOPBoucle FORBoucle WHILE
79
(c) 2006, KROS, nextStep Technologies
Boucle de base : LOOP
LOOP -- délimiteur
instruction; -- instructions
….
EXIT [WHEN condition]; -- condition de sortie
END LOOP; -- délimiteur
Variable ou expression booléenne !
80
(c) 2006, KROS, nextStep Technologies
Boucle WHILE
WHILE condition LOOP
statement1;
statement2;
….
END LOOP ;
Condition est évalué au débutde chaque itération !
Utiliser la boucle WHILE pour répéterdes instructions TANT QU’UNE CONDITION RENVOIE TRUE !
81
<Course name> <Lesson number>-28
(c) 2006, KROS, nextStep Technologies
Boucle FOR
FOR cpt IN [REVERSE] mincpt..maxcptstatement1;
statement2;
….
END LOOP ;
-FOR simplifie le contrôle du nombre d’itérations
-La déclaration du compteur est IMPLICITE !
-PAY ATTENTION TO THE SYNTAX : mincpt..maxcpt !
-REVERSE indique que le compteur décroît à chaque itération !82
(c) 2006, KROS, nextStep Technologies
Exemples d’utilisation
Insérer la table de multiplication par 3 dans une tableCREATE TABLE multiplication3(
multipliant NUMBER(1),compteur NUMBER(2),resultat NUMBER(2)
);83
(c) 2006, KROS, nextStep Technologies
Exemple avec LOOP
DECLARE
v_cpt NUMBER NOT NULL := 1 ;
BEGIN
LOOP
INSERT INTO multiplication3 VALUES (3,v_cpt, 3*v_cpt);
v_cpt := v_cpt + 1 ;
EXIT WHEN v_cpt > 10 ;
END LOOP ;
END ;
/
Résultat attendu :
3 * 1 = 3 ;
3 * 2 = 6 ;
….
3 * 10 = 30 ;
84
<Course name> <Lesson number>-29
(c) 2006, KROS, nextStep Technologies
Exemple avec WHILE
DECLARE
v_cpt NUMBER NOT NULL := 1 ;
BEGIN
WHILE v_cpt < 11 LOOP
INSERT INTO multiplication3 VALUES (3,v_cpt, 3*v_cpt);
v_cpt := v_cpt + 1 ;
END LOOP ;
END ;
/
Résultat attendu :
3 * 1 = 3 ;
3 * 2 = 6 ;
….
3 * 10 = 30 ;
85
(c) 2006, KROS, nextStep Technologies
Exemple avec FOR
BEGIN
FOR v_cpt IN 1 .. 10 LOOP
INSERT INTO multiplication3 VALUES (3,v_cpt, 3*v_cpt);
END LOOP ;
END ;
/Résultat attendu :
3 * 1 = 3 ;
3 * 2 = 6 ;
….
3 * 10 = 30 ;
86
(c) 2006, KROS, nextStep Technologies
Remarques sur les boucles
Utiliser la boucle LOOP lorsque ses instructions doivent s’exécuter au moins une (01) fois !Utiliser la boucle WHILE si la condition doit être évaluée au début de chaque itérationUtiliser la boucle FOR si le nombre d’itérations est connu
87
<Course name> <Lesson number>-30
(c) 2006, KROS, nextStep Technologies
Synthèse
Ce chapitre vous a permis de savoir :Modifier l’enchaînement logique en utilisant des structures de contrôle
Instructions conditionnelles IFExpressions CASEBoucles
LOOPWHILEFOR
Exo-PLSQL488
(c) 2006, KROS, nextStep Technologies
Exo-PLSQL-4 (1)1. Créez un bloc PL/SQL qui calcule le montant de la commission d’un
employé donné, en fonction de son salaire•UTILISEZ la fonction DEFINE pour fournir le numéro d’employé.•Transmettez cette valeur au bloc PL/SQL à l’aide d’une variable de substitution : DEFINE p_empno = 100•Si le salaire de l’emloyé est inférieur à 5000$, affichez le montant de la prime de l’employé, soit 10% du salaire.•Si le salaire de l’employé est compris entre 5000$ et 10000$, affichez le montant de la prime de l’employé, soit 15% du salaire.•Si le salaire de l’employé est supérieur à 10000$, affichez le montant de la prime de l’employé, soit 20% du salaire.•Sil le salaire de l’employé a la valeur NULL, affichez la valeur 0 en tant que montant de la prime de l’employé.
Testez votre bloc PL/SQL avec chacun des cas ci-dessous, et vérifiez le montant de la prime !
Enregistrez ce script sous script41.sql et lancez-le à l’aide START :START c:\script41.sql 100START c:\script41.sql 144
89
(c) 2006, KROS, nextStep Technologies
Exo-PLSQL-4 (2)2. A FAIRE : Créez un bloc PL/SQL qui récompense les employés en leur attribuant un astérisque dans une colonne que vous appellerez STARS, par tranche de salaire de 1000 $.
UTILISEZ la fonction DEFINE pour fournir le numéro d’employé.Transmettez cette valeur au bloc PL/SQL à l’aide d’une variable de
substitution : DEFINE p_empno = 104Si l’employée touche 8000$, vous afficherez 8 astérisques ; si l’employé
touche 11300$, vous afficherez 11 astérisques ; si l’emloyé touche 12600$, vous afficherez 13 astérisques
Résultat attendu :EMPLOYEE_ID SALARY STARS104 6000 * * * * * *174 11300 * * * * * * * * * * *176 12600 * * * * * * * * * * * * *
Enregistrez ce script sous script42.sql et lancez-le à l’aide START :START c:\script41.sql 104 ou START c:\script41.sql 174
90
<Course name> <Lesson number>-31
Ecrire des curseurs explicites
5591
(c) 2006, KROS, nextStep Technologies
Objectifs
A la fin de ce chapitre, vous pourrez: Faire la différence entre un curseur implicite et un curseur expliciteSavoir quand et pourquoi utiliser un curseurexpliciteUtiliser une variable de type RECORD en PL/SQLEcrire une boucle FOR de curseur
92
(c) 2006, KROS, nextStep Technologies
A propos des curseurs…
Le serveur Oracle utilise des zones de travail (curseurs) pour analyser et exécuter les instructions SQL.Il est possible d’utiliser des curseurs PL/SQL pour nommer ces zones et traiter les informations qu’elles contiennent !On distingue les curseurs :
Implicites : déclarés par le serveur Oracle pour toute instruction LMD et PL/SQLExplicites : déclarés et nommés par les développeurs d’applications
93
<Course name> <Lesson number>-32
(c) 2006, KROS, nextStep Technologies
Les curseurs explicites
Fonction principale : Traiter individuellement chaque ligne renvoyée par une instruction SELECT multiligne
CURSEUR
2500Canard174………1500Poulet1013000Dindon100
Ensemble actif
Lignes de la table
94
(c) 2006, KROS, nextStep Technologies
Contrôler les curseurs explicites
DECLARE
OPEN
FETCH
CLOSE
VIDE ?
1
2
3
5
1 Créer une zone SQL nommée
2 Identifier l’ensemble actif
3 Charger la ligne en cours
4 Tester l’existence d’une autre ligne
5 Libérer l’ensemble actif
4OUI
NON
95
(c) 2006, KROS, nextStep Technologies
Contrôler les curseurs explicites
Utilisez les instructions :OPEN : exécute l’interrogation associée au curseur, identifie l’ensemble actif et positionne le curseur sur la PREMIERE LIGNE FETCH : extrait la ligne en cours et place le curseur sur la ligne suivante jusqu’à ce qu’il n y ait plus de lignes ou que la condition définie soit remplieCLOSE : fermer le curseur lorsque la dernière ligne a été traitée et désactive le curseur (Pour y faire de nouveau référence, il faut à nouveau l’ouvrir)
96
<Course name> <Lesson number>-33
(c) 2006, KROS, nextStep Technologies
Déclarer un curseur
Syntaxe : CURSOR nom_du_curseur ISinstruction_selectRemarques :
Ne pas inclure la clause INTO dans la déclaration du curseurS’il existe un ordre précis de traitement des lignes, utiliser la clause ORDER BY dans l’interrogation
97
(c) 2006, KROS, nextStep Technologies
Exemple de déclaration
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, last_name
FROM hr.employees ;
CURSOR dept_cursor IS
SELECT *
FROM hr.departments
WHERE location_id = 170 ;
BEGIN
…
END;98
(c) 2006, KROS, nextStep Technologies
Ouvrir un curseur
OPEN nom_du_curseur ;Ouvrir le curseur pour référencer l’ensemble actifSi l’interrogation ne renvoie aucune ligne, aucune exception n’est déclenchéeUtiliser les attributs de curseurs pour tester le résultat après une extraction.
99
<Course name> <Lesson number>-34
(c) 2006, KROS, nextStep Technologies
Extraire les données du curseur
Syntaxe : FETCH nom_du_curseur INTO [ variable1, variable2, …] | record_name ];Extraire les valeurs de la ligne en cours et les placer dans des vriablesAjuster la position des variables par rapport aux colonnesVérifier si le curseur contient des lignes !
100
(c) 2006, KROS, nextStep Technologies
Exemple d’extraction de données
DECLARE
v_empno hr.employees.employee_id%TYPE ;
v_ename hr.employees.last_name%TYPE;
CURSOR emp_cursor IS
SELECT employee_id, last_name
FROM hr.employees ;
BEGIN
OPEN emp_cursor ;
FOR i IN 1..10 LOOP
FETCH emp_cursor INTO v_empno, v_ename ;
DBMS_OUTPUT.PUT_LINE(v_empno || ‘ - ‘ || v_ename);
END LOOP ;
END;
101
(c) 2006, KROS, nextStep Technologies
Fermer le curseur
CLOSE nom_du_curseur ;Fermer le curseur après avoir terminé le traitement des lignesL’ensemble actif n’est donc plus définiRouvrir le curseur si nécessaireNe pas essayer d’extraire les données d’un curseur s’il a été fermé (exception INVALID_CURSOR)
102
<Course name> <Lesson number>-35
(c) 2006, KROS, nextStep Technologies
Remarques sur les curseurs
Prendre l’habitude de fermer les curseurs explicites ouverts, afin de LIBERER les ressourcesLa limite maximale du nombre de curseurs ouverts est définie par le paramètre OPEN_CURSORS (elle est de 50 par défaut)
103
(c) 2006, KROS, nextStep Technologies
Attributs d’un curseur explicite
Nombre total de lignes renvoyées jusqu’à présent
Nombre%ROWCOUNT
TRUE si la dernière extraction renvoie une ligne
Booléen%FOUND
TRUE si la dernière extraction ne renvoie aucune ligne
Booléen%NOTFOUND
TRUE si le curseur est ouvertBooléen%ISOPEN
DescriptionTypeAttribut
104
(c) 2006, KROS, nextStep Technologies
Utilisation des attributs de curseur
Utiliser %ISOPEN pour vérifier qu’un curseur est déjà ouvertUtiliser %ROWCOUNT pour extraire un nombre exact de lignesUtiliser %NOTFOUND pour déterminer les conditions de sortie de la boucle
105
<Course name> <Lesson number>-36
(c) 2006, KROS, nextStep Technologies
Exemples d’utilisation
LOOP
FETCH c1 INTO my_name, my_salary
EXIT WHEN c1%NOTFOUND ;
….
END LOOP ;
LOOP
FETCH c1 INTO my_name, my_salary
IF c1%ROWCOUNT > 10 THEN
……
END IF;
….
END LOOP ;
Remarque : Référencer un curseur fermé avec %ROWCOUNT déclenche l’exception INVALID_CURSOR 106
(c) 2006, KROS, nextStep Technologies
Synthèse
Ce chapitre vous a permis de savoirDifférencier les types de curseurs :
Curseurs implicites : LMD et interrogations monolignesCurseurs explicites : interrogations portant surZERO, UNE ou PLUSIEURS lignes
Manipuler les curseurs explicitesEvaluer l’état du curseur en utilisant des attributs de curseurUtiliser les boucles FOR de curseurExo-PLSQL5
107
Traiter les Exceptions
66108
<Course name> <Lesson number>-37
(c) 2006, KROS, nextStep Technologies
Objectifs
A la fin de ce chapitre, vous pourrez: Définir des exceptions PL/SQLReconnaître les exceptions non traitéesLister et utiliser les différents types de traitement des exceptions PL/SQLIntercepter les erreurs non prédéfiniesDécrire l’effet de la propagation des exceptionsPersonnaliser les messages d’erreur
109
(c) 2006, KROS, nextStep Technologies
Exceptions en PL/SQL
Une exception désigne un identificateur détecté durant l’exécution d’un blocElle est déclenchée :
A la suite d’une erreur OracleExplicitement par le programmeur
Elle est traitée :En l’interceptant avec le gestionnaireEn la propageant à l’environnement appelant
110
(c) 2006, KROS, nextStep Technologies
Exemple d’exception
Lorsque l’instruction SELECT n’extrait aucune ligne de la BD, l’erreur ORA-01403 survient, et l’exception NO_DATA_FOUND est déclenchée par PL/SQL.Vous pouvez déclencher explicitement une exception en utilisant l’instruction RAISE dans le bloc !
111
<Course name> <Lesson number>-38
(c) 2006, KROS, nextStep Technologies
Traiter les exceptions
Intercepter l’exception :Déclenchement de l’exceptionBranchement sur le gestionnaire d’exceptions dans la section EXCEPTION2 cas de figure se présentent :
Si Exception traitée par PL/SQL Alors Exécution normale du bloc
Sinon-- Absence de gestionnaire appropriéException propagée dans l’environnement appelant
Finsi112
(c) 2006, KROS, nextStep Technologies
Types d’exception
Déclarer ces exceptions dans la section déclarative
et déclenchez-les explicitement
Condition anormale définie par le développeur
Erreur définie par le programmeur
Déclarer ces exceptions dans la section déclarative
et autoriser le serveur Oracle à les déclencher
implicitement
Toute autre erreur standard du serveur
Oracle
Erreur non prédéfinie du Serveur Oracle
Ne pas déclarer ces exceptions, mais autoriser
le serveur Oracle à les déclencher implicitement
Une des 20 erreurs les plus fréquentes en PL/SQL
Erreur prédéfinie du Serveur Oracle
Que faire ?DescriptionException
113
(c) 2006, KROS, nextStep Technologies
Intercepter les exceptions
EXCEPTIONWHEN exception1 [or exception2…] THEN
instruction 1 ;instruction 2 ; …
[ WHEN exception3 [or exception4…] THENinstruction 3 ;instruction 4 ; …]
[ WHEN OTHERS THEN instruction 1 ;instruction 2 ; …]
114
<Course name> <Lesson number>-39
(c) 2006, KROS, nextStep Technologies
Règles d’interception
Le mot clé EXCEPTION débute la section de traitement des exceptionsPlusieurs gestionnaires d’exception sont permisUn seul gestionnaire est exécuté avant la sortie du blocWHEN OTHERS est la dernière clause
115
(c) 2006, KROS, nextStep Technologies
Interception : Erreur prédéfinie
Utiliser le nom standard à l’intérieur du sous-programme de traitement des exceptions des 20 erreurs les plus fréquentesExemples :
NO_DATE_FOUNDTOO_MANY_ROWSINVALID_CURSORZERO_DIVIDEDUP_VAL_ON_INDEX Paper-PLSQL-Exceptions
116
(c) 2006, KROS, nextStep Technologies
Interception : Erreur non prédéfinie
La déclarer ou utiliser WHEN OTHERSLorsqu’elle est déclarée, elle se déclenche implicitementUtiliser la clause PRAGMA EXCEPTION_INIT qui indique au compilateur d’associer un nom d’exception à un code d’erreur !
117
<Course name> <Lesson number>-40
(c) 2006, KROS, nextStep Technologies
Exemple de traitementDEFINE p_deptno = 10
-- -
DECLARE
e_emp EXCEPTION ;
PRAGMA EXCEPTION_INIT (e_emp, -2292);
BEGIN
DELETE FROM departments
WHERE department_id = &p_deptno ;
COMMIT ;
EXCEPTION
WHEN e_emp THEN
DBMS_OUTPUT.PUT_LINE(‘Impossible de supprimer le dépt’||&p_deptno || ‘. Des employés existent !’);
END;
118
(c) 2006, KROS, nextStep Technologies
Fonctions d’interception
SQLCODE : renvoie la valeur numérique du code d’erreurSQLERRM : renvoie le message associéau code d’erreurExemples de valeurs SQLCODE
0 Aucune exception détectée1 Exception définie par l’utilisateur+100 Exception NO_DATA_FOUND Nombre négatif : autre code d’erreur !
119
(c) 2006, KROS, nextStep Technologies
Exemple d’utilisation - Fonctions
DECLARE
v_error_code NUMBER ;
v_error_message VARCHAR2(255);
BEGIN
…
EXCEPTION
WHEN OTHERS THEN
ROLLBACK ;
v_error_code := SQLCODE ;
v_error_message := SQLERRM ;
INSERT INTO mes_erreurs VALUES(v_error_code, v_error_message);
END;
120
<Course name> <Lesson number>-41
(c) 2006, KROS, nextStep Technologies
Interception : Exception Utilisateur
La déclarer dans la section déclarative : mon_exception EXCEPTION ;Utiliser l’instruction RAISE pour déclencher explicitement l’exception dans la section exécutable : RAISE mon_exception ; Faites référence à l’exception déclarée dans le sous-programme correspondant de traitement des exceptions !
121
(c) 2006, KROS, nextStep Technologies
ExempleDEFINE p_dept_name = ‘Informatique de Gestion’
DEFINE p_id = 124500001459
- - -
DECLARE
e_department_invalide EXCEPTION ;
BEGIN
UPDATE departements
SET department_name = ‘&p_dept_name’
WHERE department_id = &p_dept_id ;
IF SQL%NOTFOUND THEN
RAISE e_employee_invalide ;
END IF;
COMMIT ;
EXCEPTION
WHEN e_department_invalide THEN
DBMS_OUTPUT.PUT_LINE(‘ Aucun département n’’a cet ID !’);
END;122
(c) 2006, KROS, nextStep Technologies
RAISE_APPLICATION_ERROR
Procédure permettant de délivrer des messages définis par l’utilisateur à partir de sous-programmes stockésPermet de signaler les erreurs à l’application et d’éviter le renvoi d’exceptions non traitées.Peut être utilisé dans la section exécutable ou dans la section de traitement des exceptionsRenvoie à l’utilisateur les conditions de l’erreur de manière cohérente par rapport aux autres erreurs du serveur Oracle !
123
<Course name> <Lesson number>-42
(c) 2006, KROS, nextStep Technologies
Syntaxe
Syntaxe : raise_application_error(error_number, message [, {TRUE | FALSE }]);
error_number : valeur numérique définie par l’utilisateur (entre -20000 et -20999)message : message défini pour l’exceptionTRUE | FALSE : si True, l’erreur est rangée dans la pile des erreurs précédentes !
124
(c) 2006, KROS, nextStep Technologies
Exemples
BEGIN
…
DELETE FROM hr.employees
WHERE manager_id = v_manager_id ;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR (-20202, ‘Id de manager invalide !’) ;
END IF;
…
END;
…
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-20202, ‘Id de manager invalide !’) ;
END;
125
(c) 2006, KROS, nextStep Technologies
Synthèse
Ce chapitre vous a permis de savoirReconnaître les <> types d’exception
Erreur prédéfinie du serveur OracleErreur non prédéfinie du serveur OracleErreur définie par l’utilisateur
Intercepter une exceptionTraiter une exception
Intercepter l’exception dans le bloc PL/SQLPropager l’exception
Exercice : Exo-8-25-26
126
<Course name> <Lesson number>-43
(c) 2006, KROS, nextStep Technologies
Exercice 6Créez un bloc PL/SQL qui affiche le nombre d’employés qui gagnent 100 $ de plus ou de moins que le montant du salaire défini par une variable de substitution SQL*Plus. UTILISEZ la fonction DEFINE pour fournir le salaireTransmettez cette valeur au bloc PL/SQL à l’aide d’une variable de substitution : DEFINE p_salaire = 6000
1. S’il n’existe pas d’employés dans cette tranche de salaire, affichez un message àl’utilisateur à l’aide d’une exception.2. S’il existe au moins un employé dans cette tranche de salaire, le message doit indiquer le nombre d’employé(s).3. Traitez toutes les autres exceptions à l’aide d’un gestionnaire approprié. Le message doit indiquer quel type d’erreur est survenu
Valeurs d’exécutionDEFINE p_salaire = 7000DEFINE p_salaire = 150000
Résultat attendu :Il y a 4 employés ayant un salaire compris entre 6900 et 7100 !Il n’y a pas employés ayant un salaire compris entre 1499000 et 150100 !Enregistrez ce script sous script61.sql et lancez-le à l’aide START :START c:\script41.sql 7000 START c:\script41.sql 2500
127
Compléments
> Procédures et fonctions PL/SQL
> Trucs et astuces
128
Credits - Sources
Sourceswww.oracle.comwww.labo-oracle.com
© 2006 - Roland Olivier KYEDREBEOGO, Oracle 9i Certified Professionnaln e x t S t e p t e c h n o l o g i e sEmail : [email protected]ère mise à jour le 25 mars 2006
129
<Course name> <Lesson number>-44
Annexes
130