programmer avec pl sql 250306

44
Programmer avec PL/SQL Institut Supérieur d’Informatique et de Gestion (ISIG) Année académique 2005 - 2006 Classe : IT3 Enseignant : Roland Olivier KYEDREBEOGO, Oracle Certified Profesional (c) 2006, KROS, nextStep Technologies Sommaire 0. Présentation du langage PL/SQL 1. Déclarer des variables 2. Ecrire des instructions exécutables 3. Interagir avec le serveur ORACLE 4. Ecrire des structures de contrôle 5. Ecrire des curseurs explicites 6. Traiter les exceptions Compléments 2 Présentation de PL/SQL 0 0 3

Upload: severin-compaore

Post on 03-Jul-2015

741 views

Category:

Documents


1 download

TRANSCRIPT

Page 1: Programmer Avec PL SQL 250306

<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

Page 2: Programmer Avec PL SQL 250306

<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

Page 3: Programmer Avec PL SQL 250306

<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

Page 4: Programmer Avec PL SQL 250306

<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

Page 5: Programmer Avec PL SQL 250306

<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

Page 6: Programmer Avec PL SQL 250306

<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

Page 7: Programmer Avec PL SQL 250306

<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

Page 8: Programmer Avec PL SQL 250306

<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

Page 9: Programmer Avec PL SQL 250306

<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

Page 10: Programmer Avec PL SQL 250306

<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

Page 11: Programmer Avec PL SQL 250306

<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

Page 12: Programmer Avec PL SQL 250306

<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

Page 13: Programmer Avec PL SQL 250306

<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

Page 14: Programmer Avec PL SQL 250306

<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

Page 15: Programmer Avec PL SQL 250306

<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

Page 16: Programmer Avec PL SQL 250306

<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

Page 17: Programmer Avec PL SQL 250306

<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

Page 18: Programmer Avec PL SQL 250306

<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

Page 19: Programmer Avec PL SQL 250306

<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

Page 20: Programmer Avec PL SQL 250306

<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

Page 21: Programmer Avec PL SQL 250306

<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

Page 22: Programmer Avec PL SQL 250306

<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

Page 23: Programmer Avec PL SQL 250306

<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

Page 24: Programmer Avec PL SQL 250306

<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

Page 25: Programmer Avec PL SQL 250306

<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

Page 26: Programmer Avec PL SQL 250306

<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

Page 27: Programmer Avec PL SQL 250306

<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

Page 28: Programmer Avec PL SQL 250306

<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

Page 29: Programmer Avec PL SQL 250306

<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

Page 30: Programmer Avec PL SQL 250306

<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

Page 31: Programmer Avec PL SQL 250306

<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

Page 32: Programmer Avec PL SQL 250306

<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

Page 33: Programmer Avec PL SQL 250306

<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

Page 34: Programmer Avec PL SQL 250306

<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

Page 35: Programmer Avec PL SQL 250306

<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

Page 36: Programmer Avec PL SQL 250306

<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

Page 37: Programmer Avec PL SQL 250306

<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

Page 38: Programmer Avec PL SQL 250306

<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

Page 39: Programmer Avec PL SQL 250306

<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

Page 40: Programmer Avec PL SQL 250306

<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

Page 41: Programmer Avec PL SQL 250306

<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

Page 42: Programmer Avec PL SQL 250306

<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

Page 43: Programmer Avec PL SQL 250306

<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

Page 44: Programmer Avec PL SQL 250306

<Course name> <Lesson number>-44

Annexes

130