Post on 20-Oct-2020
Bases de Données AvancéesPL/SQL
Thierry Hamon
Bureau H202Institut Galilée - Université Paris 13
&LIMSI-CNRS
hamon@limsi.fr
https://perso.limsi.fr/hamon/Teaching/P13/BDA-INFO2-2018-2019/
INFO2 – BDA
1/185
hamon@limsi.frhttps://perso.limsi.fr/hamon/Teaching/P13/BDA-INFO2-2018-2019/
Communication avec un SGBD utilisant SQL
2/185
Solution complète Oracle
3/185
Clauses SQLSELECT Interrogation des données
INSERTUPDATE Langage de Manipulation de Données (LMD)DELETE
CREATEALTERDROP Langage de Définition de Données (LDD)RENAMETRUNCATE
GRANT Langage de Contrôle de Données (LCD)REVOKE
COMMITROLLBACK Contrôle de transactionSAVEPOINT
4/185
PL/SQLProcedural Language for Structured Query Language
Langage fournissant une interface procédurale au SGBDOracle
Intègration du langage SQL en lui apportant une dimensionprocédurale
Réalisation de traitements algorithmiques (ce que ne permetpas SQL)
Mise à disposition de la plupart des mécanismes classiques deprogrammation des langages hôtes tels que C, COBOL,PASCAL, C++, JAVA ...
5/185
Environnement PL/SQL
6/185
Avantages de PL/SQL
PL/SQL complément de SQL (qui n’est pas procédural)Mécanismes offerts par PL/SQL :
Structures itératives : WHILE *** LOOP, FOR *** LOOP,LOOP ***
Structures conditionnelles :IF *** THEN *** ELSE | ELSEIF *** ENDIF, CASE ***
Déclaration des curseurs et des tableaux
Déclaration de variables
Affectation de valeurs aux variables
Branchements : GOTO, EXIT
Exceptions : EXCEPTION
7/185
Avantages de PL/SQLIntégration
Meilleure cohérence du code avec les données
Utilisation de librairies standards prédéfinies (bibliothèquespartagées)
8/185
Avantages de PL/SQLBlocs SQL
Traitement par/de blocs SQL dans un énoncé PL/SQL(optimisation des transactions réseaux)
9/185
Avantages de PL/SQLExécution de programmes modulaires
Traitements complexes (cas particuliers, erreurs)Traitements des exceptions...
10/185
Avantages de PL/SQLRésumé
Langage portable
Utilisation de variable de stockage
Utilisation de type simple ou de type structurédynamiquement (%TYPE, %ROWTYPE, etc.)
Utilisation des structures de contrôle des langages procéduraux
Gestion et manipulation des erreurs
Création d’ordre SQL dynamique
11/185
Utilisation de PL/SQL
3 formes :
Bloc de code, executé comme une commande SQL (utilisationd’un interpréteur standard SQL+ ou iSQL*PLus)
Fichier de commandes PL/SQL
Programme stocké : procédure, fonction, package ou trigger
12/185
Structure d’un bloc PL/SQL−− S e c t i o n d é c l a r a t i v e , o p t i o n n e l l eDECLARE
V a r i a b l e s , c u r s e u r s , e x c e p t i o n s , . . .
−− S e c t i o n e x é c u t a b l e , o b l i g a t o i r eBEGIN
I n s t r u c t i o n s SQL e t PL/SQLP o s s i b i l i t é s de b l o c s f i l s ( i m b r i c a t i o n de b l o c s )
−− S e c t i o n de t r a i t e m e n t des e x c e p t i o n s , o p t i o n n e l l eEXCEPTION o p t i o n n e l l e
T r a i t e m e n t des e x c e p t i o n s ( g e s t i o n des e r r e u r s )
−− T e r m i n a i s o n du b loc , o b l i g a t o i r eEND ;
/
13/185
Type de blocs
Bloc anonyme
Procédure
Fonction
14/185
Type de blocsBloc anonyme
Structure classique (1 à 3 sections)
Un bloc ne peut être vide. Il doit contenir une instruction (ilpeut donc contenir l’instruction NULL)
[ DECLARE ]
BEGINI n s t r u c t i o n s
[ EXCEPTION ]END;/
15/185
Type de blocsBloc anonyme – Exemple 1
DECLARE x INTEGER ;BEGINx := 1 ;END;
16/185
Type de blocsBloc anonyme – Exemple 2
DECLAREv a r x VARCHAR2( 5 ) .
BEGINSELECT nom colonneINTO v a r xFROM nom tab le
EXCEPTIONWHEN n o m e x c e p t i o n THEN
. . .END ;
/
17/185
Type de blocsProcédure
Bloc PL/SQL nommé
puis compilé et stocké dans la base
PROCEDURE nom I S
BEGINI n s t r u c t i o n s
[ EXCEPTION ]END;/
18/185
Type de blocsProcédure – Exemple
PROCEDURE p r o c e x e m p l e I Sv a r x VARCHAR2( 5 ) ;
BEGINSELECT nom colonneINTO v a r xFROM nom tab le
EXCEPTIONWHEN n o m e x c e p t i o n THEN
. . .END ;
/
→ Commande SQL quicrée la procédure PL/SQL
compile et stocke dans la base le bloc PL/SQL compris entre le BEGIN etle END, référencé par ’proc exemple’
19/185
Type de blocsProcédure
Remarques :
Exécution (auto) de la procédure :
SQL> EXECUTE proc_exemple
Pas d’exécution de procédure (ou d’instructions) en fin detransaction (COMMIT, ROLLBACK, Ordre DDL)Décision d’enregistrement ou d’annulation de la transaction encours : à réaliser par le programme appelant la procédure
20/185
Type de blocsFonction
Fonction : procédure retournant une valeur
FUNCTION nom RETURN t y p e d o n n é e s I SBEGIN
I n s t r u c t i o n sRETURN v a l e u r ;[ EXCEPTION ]END;/
21/185
Type de blocsFonction – exemple
SQL> CREATE OR REPLACE FUNCTION s o l d e (no INTEGER)RETURN REAL I S l e s o l d e REAL ;BEGINSELECT s o l d e INTO l e s o l d e FROM c l i e n t sWHERE n o c l i = no ;RETURN l e s o l d e ;END;/
22/185
Type de blocsFonction
Remarques :
Utilisation de fonction au sein d’une requête SQLExemple :SQL> SELECT solde(1000) FROM dual ;
Solde(1000)
-----------
12024,50
Appel d’une fonction comme une procédure provoque une erreurExemple : fonction mdp(INTEGER)SQL> execute mdp(2);
BEGIN mdp(2); END;
*
ERREUR a la ligne 1 :
ORA-06550: line 1, column 7:
PLS-00221: ’MDP’ is not a procedure or is undefined
ORA-06550: line 1, column 7:
23/185
Types de variables
Variables locales
ConstantesCompositesRéférences
Variables de l’environnement extérieur à PL/SQL
Attachées (Bind)Hôtes (Host)
24/185
Déclaration des variables en PL/SQL
Syntaxe
I d e n t i f i c a t e u r [CONSTANT] t y p e d o n n é e s [NOT NULL ][ := e x p r e s s i o n ] ;
Exemple
DECLAREv d a t e n a i s s a n c e DATE;v d e p a r t e m e n t NUMBER( 2 ) NOT NULL := 1 0 ;v v i l l e VARCHAR2( 1 3 ) := ’ P a r i s ’ ;c cumul CONSTANT NUMBER := 1 0 0 0 ;
25/185
Assignation des variables
Syntaxe
I d e n t i f i c a t e u r := e x p r ;
Exemples
Affecter la date de naissance du fils d’un employés
v d a t e n a i s s a n c e := ’23−SEP−2004 ’ ;
Fixer le nom d’un employé à ’Clément’
v nom := ’ Cl ément ’ ;
26/185
Types de données scalairesTypes de base – châınes de caractères
VARCHAR2(n)/NVARCHAR2(n) : Châıne de n caractères(n < 4000octets) – longueur variable(préfixe N : prise en compte de la localisation – NLS/NationalLanguage Support)
CHAR(n)/NCHAR(n) : Châıne de n caractères(n < 2000octets) – longueur fixe, rempli par des espaces
LONG/RAW/LONG RAW : Châıne de caractère ou de donnéesbinaires (raw) de longueur variable ( < 2000octets)
27/185
Types de données scalairesTypes de base – nombres et date
NUMBER (n , m) : Réel avec n chiffres significatifs et m décimals
INTERGER/FLOAT : Nombre entier/réel signé
BINARY_INTEGER : Nombre entier signé sur 32 bits (utilisation de lalibrairie mathématique)
DATE : date entre le 1 janvier -4712 et le 21 décembre 9999
BOOLEAN : booléenNB : pas d’équivalent en SQL.Il n’est donc pas possible
de stocker un booléen dans une tablede sélectionner un élément de table dans un variable booléenned’utiliser un booléen dans une requête SQL ou une fonctionPL/SQL appelée dans une requête SQL (paramètre en entrée ouvaleur de retour)
28/185
Déclaration des variables scalairesExemples
v j o b VARCHAR2( 9 ) .v c o u n t BINARY INTEGER := 0 ;v s a l t o t a l NUMBER( 9 , 2 ) .v d a t e DATE := SYSDATE +7;v t a u x t a x e CONSTANT NUMBER( 3 , 2 ) := 8 . 2 5 ;v v a l i d e BOOLEAN NOT NULL := TRUE;
29/185
Attribut %TYPE
Définition : déclaration d’une variable associée à
Une colonne d’une table dans la BD
Une variable précédemment définie
Exemples :
v nom emp . nom%TYPE ;v s a l a n n u e l NUMBER( 7 , 2 ) ;v s a l m e n s u e l v s a l a n n u e l %TYPE := 2 0 0 0 ;
30/185
Types de données composites
Types :
Les tables PL/SQL
Les records PL/SQL
(voir plus loin)
31/185
Variables attachées/Bind variables
Variables de substitution dans les requêtes SQLVariable référençant des variables non-PL/SQL
Economie d’analyse de la requête, donc gain de temps pourOracle→ Mécanisme considéré par certains comme le point clé deperformance d’Oracle
32/185
Variables attachées/Bind variablesDéclaration
Déclaration, en SQL*Plus, d’une variable attachée : utilisation dela commande VARIABLEExemple :
VARIABLE n o m v a r i a b l e t y p e v a r i a b l e ;
VARIABLE g s a l m e n s u e l NUMBER( 7 , 2 ) ;
33/185
Variables attachées/Bind variablesRéférence aux variables non-PL/SQL
Exemple :
Stocker le salaire mensuel dans une variable globale SQL*Plus
: g s a l m e n s u e l := v s a l a n n u e l / 1 2 ;
Faire référence aux variables non-PL/SQL comme des variableshôtePrécéder la référence par un ’ : ’
34/185
Variables attachées/Bind variablesExemple d’utilisation – optimisation
Exemple de requête SELECT générées des milliers de fois :
SELECT fname , lname , pcode FROM c u s t WHERE i d = 6 7 4 ;SELECT fname , lname , pcode FROM c u s t WHERE i d = 2 3 4 ;SELECT fname , lname , pcode FROM c u s t WHERE i d = 3 3 2 ;. . .
A chaque soumission d’un requête,
Vérification si la requête a déjà été été soumise
Si oui, récupération du plan d’exécution de la requête, etexécution de la requête
35/185
Variables attachées/Bind variablesExemple d’utilisation – optimisation
Si non,analyse syntaxique de la requêtedéfinition des différentes possibilités d’exécutiondéfinition du plan d’exécution optimal
→ Processus coûteux en temps CPU, alors que seule la valeurde id change !
Solution : réutiliser le plan d’exécution existant→ Nécessite d’utiliser des variables attachées :
Substitution de la valeur par la variable attachéEnvoi de la même requête pour toutes les valeurs de idExemple :
SELECT fname , lname , pcode FROM c u s t WHERE i d = : c u s t n o ;
36/185
Bloc PL/SQLsyntaxe et directives
Les instructions peuvent être écrites sur plusieurs lignes.Les unités lexicales peuvent être séparées par des espaces :
DélimiteursIdentificateursLittéraux (ou constantes)Commentaires
37/185
Bloc PL/SQLsyntaxe et directives
Les littéraux
Les dates et les châınes de caractères délimitées par deuxsimples cotes
V nom := ’ T h i e r r y ’ ;v a n n e e := to number ( t o c h a r ( v D a t e F i n P e r i o d e , ’YY ’ ) ) ;
Les nombres peuvent être des valeurs simples ou desexpressions
V s a l a i r e := 1500 + 300 ;
38/185
Commentaires dans le code
Précéder un commentaire écrit sur une seule ligne par ’--’.
Placer un commentaire écrit sur plusieurs lignes entre lessymboles ’/*’ et ’*/’.
Exemple :
v s a l NUMBER( 9 , 2 ) ;BEGIN
/∗ C e c i e s t un commentaire q u i peute t r e e c r i t s u r p l u s i e u r s l i g n e s ∗/
ENd ; −− C e c i e s t un commentaire s u r une l i g n e
39/185
Les Fonctions SQL en PL/SQL
Les fonctions sur les nombres
Les fonctions sur les châınes de caractères
Les fonctions de conversion de type de données
Les fonctions de dates
40/185
Exemples de fonctions SQL en PL/SQLExemples :
Recomposer l’adresse d’un employé :
V AdrComplete : = V Rue | | CHR( 3 2 ) | | V V i l l e | | CHR( 3 2 ) | |V CodePosta l ;
Convertir le nom en majuscule
V Nom := UPPER (V Nom) ;
Extraction d’une partie de la châıne
V chr := Substr ( ’PL/SQL ’ , 4 , 3 ) ;
Replacement d’une châıne par une autre
V chr := R e p l a c e ( ’ Serv1 / Prod / t b c l i e n t ’ , ’ Prod , ’ V a l i d ’ ) ;
41/185
Blocs imbriqués et porté des variables
. . .x BINARY INTEGER ;BEGIN −− Debut de l a p o r t e e de x
. . .DECLARE
y NUMBER;BEGIN −− debut de l a p o r t e de y
. . .END; −− f i n de l a p o r t e e de y. . .
END; −− f i n de l a p o r t e e de x
42/185
Opérateurs dans PL/SQL
Identique à SQL
LogiqueArithmétiqueConcaténationParenthèses pour contrôler l’ordre des opérations
Opérateur supplémentaire : Opérateur d’exponentiation **
43/185
Utilisation des variables liées
Pour référencer une variable en PL/SQL, on doit préfixer sonnom par un ’:’
Exemple :
: c o d e r e t o u r := 0 ;IF v é r i f i e r c r é d i t o k ( compt no ) THEN
: c o d e r e t o u r := 1 ;END IF ;
44/185
Instructions SQL dans PL/SQL
Extraire une ligne de données à partir de la BD par lacommande SELECT. Un seul ensemble de valeurs peut êtreretourné
Effectuer des changements aux lignes dans la BD par lescommandes du LMD
Contrôler des transactions avec les commandes COMMIT,ROLLBACK et SAVEPOINT
Déterminer les résultats du LMD avec des curseurs implicites(voir plus loin)
45/185
Instruction SELECT dans PL/SQL
Récupérer une donnée de la BD avec SELECT.
Syntaxe
SELECT l i s t e s é l e c t i o nINTO { nom var [ , nom var ] . . .
| nom record }FROM tab leWHERE c o n d i t i o n ;
46/185
Instruction SELECT dans PL/SQL
La clause INTO est obligatoire
Exemple
DECLAREv d e p t n o NUMBER( 2 ) ;v l o c VARCHAR2( 1 5 ) ;
BEGINSELECT deptno , l o cINTO v deptno , v l o cFROM deptWHERE nom d = ’INFORMATIQUE ’ ;
. . .END;
47/185
Instruction SELECT dans PL/SQL
Retourne la somme des salaires de tous les employés d’undépartement donné.
Exemple
DECLAREv s o m s a l emp . s a l%TYPE ;v d e p t n o NUMBER NOT NULL := 1 0 ;
BEGINSELECT sum ( s a l ) −−f o n c t i o n d ’ a g r é g a tINTO v s o m s a lFROM empWHERE deptno = v d e p t n o ;
END;
48/185
Manipulation de données en PL/SQL
Effectuer des mises à jour des tables de la BD utilisant lescommandes du LMD :
INSERT
UPDATE
DELETE
49/185
Insertion de données
Ajouter les informations d’un nouvel employé à la table emp
Exemple
DECLAREv empno NUMBER NOT NULL := 1 0 5 ;
BEGININSERT INTO emp ( empno , emp nom , poste , deptno )VALUES ( v empno , ’ Cl ément ’ , ’ D i r e c t e u r ’ , 1 0 ) ;
END ;
50/185
Mise à jour de données
Augmenter le salaire de tous les employés dans la table empqui ont le poste d’enseignant.
Exemple
DECLAREv a u g m s a l emp . s a l%TYPE := 2 0 0 0 ;
BEGINUPDATE empSET s a l := s a l + v a u g m s a lWHERE j o b = ’ E n s e i g n a n t ’ ;
END; 1
51/185
Suppression de données
Suppression des lignes appartenant au département 10 de latable emp
Exemple
DECLAREv deptno emp . deptno%TYPE := 1 0 ;
BEGINDELETE FROM empWHERE deptno = v d e p t n o ;
END;
52/185
Structure de contrôle dans PL/SQL
IF conditionnel :
IF THEN END IF ;IF THEN ELSE END IF ;IF THEN ELSIF END IF ;
Les boucles :
LOOP END LOOP;FOR LOOP END LOOP;WHILE LOOP END LOOP;
53/185
Instruction IFSyntaxe
IF c o n d i t i o n THENé nonc é s ;
[ ELSIF c o n d i t i o n THENé nonc é s ; ]
[ELSEé nonc é s ; ]
END IF ;
54/185
Instruction IFExemple de IF simple
Mettre le ID de l’employé ’MARK’ à 101.
IF v nom = ’MARK’ THENv ID := 1 0 1 ;
END IF ;
55/185
IF simple
Si le nom de l’employé est ’CLEMENT’, lui attribuer le poste’Enseignant’, le département n◦ 102 et une commission de 25% sur son salaire actuel
Exemple
. . .IF v nom = ’ Cl ément ’ THENv p o s t e := ’ E n s e i g n a n t ’ ;v d e p t n o := 1 0 2 ;v nouv comm := s a l ∗ 0 . 2 5 ;END IF ;. . .
56/185
IF-THEN-ELSESi le nom de l’employé est ’CLEMENT’, lui attribuer le poste’Enseignant’, le département n◦ 102 et une commission de 25% sur son salaire actuel, sinon afficher le message ’Employéinexistant’Exemple
. . .IF v nom = ’ Cl ément ’ THEN
v p o s t e := ’ E n s e i g n a n t ’ ;v d e p t n o := 1 0 2 ;v nouv comm := s a l ∗ 0 . 2 5 ;
ELSEDBMS OUTPUT. PUT LINE ( ’ Employé i n e x i s t a n t ’ ) ;
END IF ;. . .
57/185
IF-THEN-ELSIF
Pour une valeur donnée en entrée, retourner une valeurcalculée
Exemple
. . .IF v d e b u t > 100 THEN RETURN ( 2 ∗ v d e b u t ) ;ELSIF v d e b u t >= 50 THEN RETURN ( 5 ∗ v d e b u t ) ;ELSE RETURN (1 ∗ v d e b u t ) ;END IF ;. . .
58/185
CASE
Exemple :
s e l e c t i d b c r e x p o , i d g e s t f i n , rownum ,case when rownum
Boucle de baseSyntaxe
LOOP −− d é l i m i t e u rénonc é 1 ; −− énonc é. . . .EXIT [ WHEN c o n d i t i o n ] ; −− énonc é EXIT
END LOOP;
When : condition est une variable booléenne ou expression (TRUE,FALSE, ou NULL);
60/185
Boucle de baseExemple
Insérer 10 articles avec la date d’aujourd’hui.
. . .v Date DATE;v compteur NUMBER( 2 ) := 1 ;
BEGIN. . .
v Date := SYSDATE ;LOOP
INSERT INTO a r t i c l e ( Artno , ADate )VALUES ( v compteur , v Date ) ;
v compteur := v compteur + 1 ;EXIT WHEN v compteur > 1 0 ;
END LOOP;. . .
61/185
Boucle FORSyntaxe
FOR i n d i c e IN [ REVERSE ]b o r n e i n f . . Borne sup LOOP
énonc é 1 ;énonc é 2 ;. . . . .
END LOOP;
Utiliser la boucle FOR pour raccourcir le test d’un nombred’itérations
Ne pas déclarer l’indice, il est déclaré implicitement
62/185
Boucle FORexemple
Insérer Nb articles indexés de 1 à Nb avec la date du système enutilisant la boucle FOR
ACCEPT Nb PROMPT ’ Donner l e nombre a r t i c l e : ’. . .
v Date DATE;BEGIN. . .
v Date := SYSDATE ;FOR i IN 1 . . &Nb LOOP
INSERT INTO a r t i c l e ( Artno , ADate )VALUES ( i , v Date ) ;
END LOOP;. . .
63/185
Boucle WHILESyntaxe
WHILE c o n d i t i o n LOOPénonc é 1 ;énonc é 2 ;. . . . .
END LOOP;
La condition est évaluée au début de chaque itération
Utiliser la boucle WHILE pour répéter des énoncés tant que lacondition est vraie
64/185
Boucle WHILEExemple
ACCEPT p i t e m t o t PROMPT ’ Donner l e t o t a l max de l ’ ’ a c h a td ’ ’ un a r t i c l e ’
DECLAREv Date DATE ;v compteur NUMBER( 2 ) := 1 ;
BEGIN. . .
v Date := SYSDATE ;WHILE v compteur
Boucles imbriquées et Labels
Imbriquer les boucles à niveaux multiples
Utiliser les labels pour distinguer les blocs et les boucles
Quitter la boucle extérieure avec un EXIT référençant le label
66/185
Boucles imbriquées et Labels. . . .BEGIN
>LOOP
v compteur := v compteur + 1 ;EXIT WHEN v compteur > 1 0 ;
LOOP
. . .EXIT b o u c e x t WHEN t o t a l f a i t = ’ OUI ’ ;−− q u i t t e r l e s deux b o u c l e sEXIT WHEN i n t f a i t = ’ OUI ’ ;−− q u i t t e r l a uniquement l a b o u c l e i n t e r n e. . .
END LOOP b o u c i n t ;. . .
END LOOP b o u c e x t ;END;. . .
67/185
Types de données complexes
Types :
RECORDSTABLES
Contiennent des composants internes
Sont réutilisables
68/185
Records PL/SQL
Contiennent des champs qui sont soit des scalaires, desrecords ou des tables PL/SQL
Structure similaire à des enregistrements dans les langages deprogrammation classiques
Très utiles pour rechercher des lignes de données dans unetable et les traiter
69/185
Création d’un record PL/SQL
Syntaxe
TYPE I S RECORD( Champ1 Type1
.
.ChampN TypeN ) ;
Exemple
TYPE TProd I S RECORD(
VRefPro NUMBER( 4 ) ,VDesPro VARCHAR2( 3 0 ) ,VPr iUni NUMBER( 7 , 2 )
) ;
70/185
Attribut %ROWTYPE
Déclaration d’une variable associée à une collection decolonnes dans une table ou une vue de la BD
le nom de la table doit précéder %ROWTYPE
Les champs dans le record prennent leurs noms et types descolonnes de la table ou la vue en question
71/185
Attribut %ROWTYPEExemples
Déclarer une variable pour stocker les mêmes informationsconcernant une personne telles qu’elles sont stockées dans latable PERS
p e r s o n n e Pers%ROWTYPE;
Déclarer une variable pour stocker les mêmes informationsconcernant un article telles qu’elles sont stockées dans la tableART
a r t i c l e Art%ROWTYPE;
72/185
Tables PL/SQL
Le type de données complexe TABLE offre au développeur unmécanisme pour traiter les tableaux
Il se compose de deux colonnes :
Une clé primaire de type BINARY_INTEGERUne colonne de type scalaire ou record
73/185
Création d’une table PL/SQL
Syntaxe
TYPE I S TABLE OF INDEX BY BINARY INTEGER ;
Exemple
TYPE type etud nom I S TABLE OF etud . nom%TYPEINDEX BY BINARY INTEGER ;
etud nom type etud nom ;
74/185
Structure d’une table PL/SQLen mémoire
Clé primaire Colonne
1 Ritchie
2 Marvin
3 Dennis
. . . . . .
. . . . . .
BINARY INTEGER Scalaire
75/185
Créer une table PL/SQLSQL> DECLARE
2 TYPE type etud nom I S TABLE OF va rcha r2 ( 1 0 )INDEX BY BINARY INTEGER ;
3 etud nom type etud nom ;4 BEGIN5 s e l e c t nom6 i n t o etud nom ( 1 )7 from etud8 where e t u d i d = 6 ;9 dbms output . p u t l i n e ( etud nom ( 1 ) ) ;
10 dbms output . p u t l i n e ( etud nom ( 2 ) ) ;11 end ;12 /
markDECLARE∗ERREUR à l a l i g n e 1 :ORA−01403: Aucune donnée t r o u v é eORA−06512: à l i g n e 10
76/185
TABLE de RECORDS en PL/SQL
Définit une table dont la deuxième colonne est unenregistrement au lieu d’un scalaire
Pour définir la deuxième colonne :
Soit en utilisant l’attribut %ROWTYPESoit en utilisant un record déjà défini
77/185
TABLE de RECORDS PL/SQLExemple 1
DECLARETYPE type etud nom I S TABLE OF etud%rowtype
INDEX BY BINARY INTEGER ;etud nom type etud nom ;
BEGINSELECT nomINTO etud nom ( 1 ) . nomFROM etud. . .
END ;
78/185
TABLE de RECORDS PL/SQLExemple 2
DECLARETYPE r e c e t u d I S RECORD( i d etud . e t u d i d%TYPE,
nom etud . nom%TYPE ) ;TYPE type etud nom I S TABLE OF r e c e t u d%ROWTYPE
INDEX BY BINARY INTEGER ;etud nom type etud nom ;
BEGINSELECT nomINTO etud nom ( 1 ) . nomFROM etud. . .
END ;
79/185
Les curseurs dans SQL
Un curseur est une zone de travail privée de SQL (zonetampon)
Il y a deux types de curseurs:
Curseurs implicitesCurseurs explicites
Oracle utilise les curseurs implicites pour analyser et exécuterles énoncés de SQL
Les curseurs explicites sont déclarés explicitement pas leprogrammeur
80/185
Les attributs des curseurs SQLLes attributs des curseurs SQL permettent de tester les résultatsdes énoncés SQL
SQL%ROWCOUNT Nombre de lignes affecté par l’énoncéSQL le plus récent (renvoie un entier).
SQL%FOUND attribut booléen qui prend la valeur TRUEsi l’énoncé SQL le plus récent affecte uneou plusieurs lignes.
SQL%NOTFOUND attribut booléen qui prend la valeur TRUEsi l’énoncé SQL le plus récent n’affecteaucune ligne.
SQL%ISOPEN Prend toujours la valeur FALSE parce quePL/SQL ferment les curseurs implicitesimmédiatement après leur exécution.
81/185
Les attributs des curseurs SQL
Supprimer de la table ITEM des lignes ayant un ordre spécifié.Afficher le nombre de lignes supprimées.
Exemple
DECLAREv o r d i d NUMBER := 6 0 5 ;
BEGINDELETE FROM i t emWHERE o r d i d = v o r d i d ;DBMS OUTPUT. PUT LINE (SQL%ROWCOUNT
| | ’ L i g n e s s u p p r i m é e s ’ ) ;END ;
Remarque : ne pas oublier Set ServerOutput on (sousSQLPLUS)
82/185
Les curseurs
Chaque énoncé SQL exécuté par Oracle a son propre curseur :
Curseurs implicites : déclarés pour tout énoncé SELECT duLMD ou PL/SQL
Curseurs explicites : déclarés et nommés par le programmeur
83/185
Contrôle des curseurs explicites
84/185
Contrôle des curseurs explicites
85/185
Déclaration des curseursSyntaxe
CURSOR n o m d u c u r s e u r I Sun énonc é SELECT ;
Ne pas inclure la clause INTO dans la déclaration du curseur
Si le traitement des lignes doit être fait dans un ordrespécifique, on utilise la clause ORDER BY dans la requête
86/185
Déclaration des curseursExemple
DECLARECURSOR C1 I S
SELECT RefArt , NomArt , QteArtFROM A r t i c l eWHERE QteArt < 5 0 0 ;
87/185
Ouverture du curseurSyntaxe
OPEN n o m d u c u r s e u r ;
Ouvrir le curseur pour exécuter la requête et identifierl’ensemble actif
Si la requête ne renvoie aucune ligne, aucune exception n’auralieu
Utiliser les attributs des curseurs pour tester le résultat duFETCH
88/185
Recherche des données dans le curseurSyntaxe
FETCH n o m d u c u r s e u rINTO [ v a r i a b l e 1 , [ v a r i a b l e 2 , . . . ]
| n o m d e r e c o r d ] ;
Rechercher les informations de la ligne en cours et les mettre dansdes variables.
89/185
Recherche des données dans le curseurExemples
FETCH c1 INTO v R e f A r t , v NomArt , v QteArt ;
. . . .OPEN Cur Etud ;LOOP
FETCH Cur Etud INTO Rec Etud ;{ t r a i t e m e n t s des donn ées r e c h e r c h é e s }. . .
END LOOP;. . .
90/185
Fermeture du curseurSyntaxe
CLOSE n o m d u c u r s e u r ;
Fermer le curseur après la fin du traitement des lignes
Rouvrir le curseur si nécessaire
On ne peut pas rechercher des informations dans un curseur sice dernier est fermé
91/185
Les attributs du curseur explicite
Obtenir les informations d’état du curseur (CUR EXP)
Attribut Type DescriptionCUR EXP%ISOPEN BOOLEAN Prend la valeur TRUE
si le curseur est ouvertCUR EXP%NOTFOUND BOOLEAN Prend la valeur TRUE
si le FETCH le plus récentne retourne aucune ligne
CUR EXP%FOUND BOOLEAN Prend la valeur TRUEsi le FETCH le plus récent
retourne une ligneCUR EXP%ROWCOUNT NUMBER Retourne le nombre de lignes
traitées jusqu’ici
92/185
Contrôle des recherches multiples
Traitement de plusieurs lignes d’un curseur en utilisant uneboucle
Rechercher une seule ligne à chaque itération
Utiliser les attributs du curseur explicite pour tester le succèsde chaque FETCH
93/185
Attribut %ISOPEN
La recherche des lignes n’est possible que si le curseur estouvert
Utiliser l’attribut %ISOPEN avant un FETCH pour tester si lecurseur est ouvert ou non
Exemple :
IF NOT C1%ISOPEN THENOPEN C1
END IF ;LOOPFETCH C1 . . .
94/185
Attributs %FOUND, %NOTFOUND et %ROWCOUNT
Utiliser l’attribut %ROWCOUNT pour fournir le nombre exact deslignes traitées
Utiliser les attributs %FOUND et %NOT FOUND pour formuler letest d’arrêt de la boucle
95/185
Attributs %FOUND, %NOTFOUND et NOTFOUND%ROWCOUNTExemple
LOOPFETCH c u r s 1 INTO v e t u d i d , v nom ;IF c u r s 1%ROWCOUNT > 20 THEN
. . .EXIT WHEN c u r s 1%NOTFOUND;. . .
END LOOP;
96/185
Obtenir les informations d’état du curseurExemple complet
DECLAREnom emp . ename%TYPE ;s a l a i r e emp . s a l%TYPE ;
CURSOR C1 I S SELECT ename , NVL( s a l , 0 ) FROM emp ;BEGIN
OPEN C1 ;LOOP
FETCH C1 INTO nom , s a l a i r e ;EXIT WHEN C1%NOTFOUND;DBMS OUTPUT. PUT LINE ( nom | | ’ gagne ’ | |
s a l a i r e | | ’ d o l l a r s ’ ) ;END LOOP;CLOSE C1 ;
END;
97/185
Les curseurs et les RecordsTraitement des lignes de l’ensemble actif par l’affectation desvaleurs à des records PL/SQL.Exemple
. . .CURSOR Etud Curs I S
SELECT etudno , nom , age , ardFROM etudWHERE age < 2 6 ;
Etud Record Etud Curs%ROWTYPE;BEGIN
OPEN Etud Curs ;. . .
FETCH Etud Curs INTO Etud Record ;. . .
END;
98/185
Les boucles FOR des curseursSyntaxe
FOR nom record IN n o m c u r s e u r LOOP−− t r a i t e m e n t des i n f o r m a t i o n s−− u t i l i s e r des o r d r e s SQL−− u t i l i s e r des o r d r e s PL / SQL
END LOOP;. . .
Un raccourci pour le traitement des curseurs explicites
OPEN, FETCH et CLOSE se font de façon implicite
Ne pas déclarer le record, il est déclaré implicitement
99/185
Les boucles FOR des curseursExemple
DECLARECURSOR Cur Etud I SSELECT ∗ FROM Etud ;BEGIN
FOR Rec Etud IN Cur Etud LOOPDBMS OUTPUT. PUT LINE ( Rec Etud . e t u d i d | | ’
’ | | Rec Etud . nom | | ’ ’ | | Rec Etud . adr ) ;END LOOP;END;/
100/185
Manipulation des exceptions en PL/SQL
Le traitement des exceptions PL/SQL : mécanisme pourmanipuler les erreurs rencontrées lors de l’exécution
Possibilité de continuer l’exécution si l’erreur n’est passuffisamment importante pour produire la terminaison de laprocédure
Décision de continuer une procédure après erreur : décisionque le développeur doit faire en fonction des erreurs possibles
101/185
Types des exceptions
Déclenchées implicitement
Exceptions Oracle prédéfiniesExceptions Oracle Non-prédéfinies
Déclenchées explicitement
Exceptions définies par l’utilisateur
102/185
Capture des exceptionsSyntaxe
EXCEPTIONWHEN e x c e p t i o n 1 [OR e x c e p t i o n 2 . . . ] THEN
énonc é1 ;énonc é2 ;. . .
[WHEN e x c e p t i o n 2 [OR e x c e p t i o n 4 . . . ] THENénonc é3 ;énonc é4 ;. . . ]
[WHEN OTHERS THENénonc é5 ;énonc é6 ;. . . ]
103/185
Capture des exceptions prédéfinies
Faire référence au nom dans la partie traitement desexceptions
Quelques exceptions prédéfinies :
NO DATA FOUND
TOO MANY ROWS
INVALID CURSOR
ZERO DIVIDE
DUP VAL ON INDEX
104/185
Exceptions prédéfiniesExemple
BEGIN. . .
EXCEPTIONWHEN NO DATA FOUND THENénonc é1 ; énonc é2 ;DBMS OUTPUT. PUT LINE (TO CHAR ( etudno ) | |
’ Non v a l i d e ’ ) ;WHEN TOO MANY ROWS THEN
énonc é3 ; énonc é4 ;DBMS OUTPUT. PUT LINE ( ’ Données i n v a l i d e s ’ ) ;
WHEN OTHERS THENénonc é5 ; énonc é6 ;
DBMS OUTPUT. PUT LINE ( ’ A u t r e s e r r e u r s ’ ) ;
105/185
Capture des exceptions définies par l’utilisateur
106/185
Capture des exceptions non-prédéfiniesExemple
Capture de l’erreur n◦ 2291 (violation de la contrainte intégrité).
DECLAREc o n t i n t e g r i t v i o l EXCEPTION ;
PRAGMA EXCEPTION INIT ( c o n t i n t e g r i t v i o l , −2291);. . .
BEGIN. . .
EXCEPTIONWHEN c o n t i n t e g r i t v i o l THENDBMS OUTPUT. PUT LINE ( ’ v i o l a t i o n de c o n t r a i n t e
d ’ i n t é g r i t é ’ ) ;. . .
END;
107/185
Capture des exceptions définies par l’utilisateur
108/185
Exceptions définies par l’utilisateurExemple
DECLAREx number : = . . . ;x t r o p p e t i t EXCEPTION ;. . .
BEGIN. . .IF x < 5 THEN RAISE x t r o p p e t i t ;END IF ;. . .
EXCEPTIONWHEN x t r o p p e t i t THEN
DBMS OUTPUT. PUT LINE ( ’ l a v a l e u r de xe s t t r o p p e t i t e ! ! ’ ) ;
. . .END;
109/185
Fonctions pour capturer les exceptions
SQLCODE : Retourne la valeur numérique du code de l’erreur
SQLERRM : Retourne le message associé au numéro de l’erreur
110/185
Fonctions pour capturer les exceptionsExemple
. . .v c o d e e r r e u r NUMBER;v m e s s a g e e r r e u r VARCHAR2( 2 5 5 ) ;
BEGIN. . .EXCEPTION. . .
WHEN OTHERS THEN. . .
v c o d e e r r e u r := SQLCODE ;v m e s s a g e e r r e u r := SQLERRM;INSERT INTO e r r e u r s VALUES ( v c o d e e r r e u r ,
v m e s s a g e e r r e u r ) ;END;
111/185
Les sous-programmes
Un sous programme est une séquence d’instruction PL/SQLqui possède un nomOn distingue deux types de sous programmes :
Les procéduresLes fonctions
112/185
Les sous-programmes
Une procédure : sous programme qui ne retourne des résultatsseulement dans ses paramètres
Une fonction : sous programme qui retourne des résultatsdans :
Le nom de la fonctionLes paramètres de la fonction
113/185
Les procéduresSyntaxe
DECLARE. . .
PROCEDURE [(P1 , . . . , Pn ) ] I S[ D é c l a r a t i o n s l o c a l e s ]
BEGIN. . .
EXCEPTION. . .
END;BEGIN
/∗ Appel de l a p r o c é d u r e ∗/. . .
EXCEPTION. . .
END ;/
114/185
Les procéduresSyntaxe des paramètres
P1,...,Pn suivent la syntaxe :
[ IN | OUT | IN OUT]
Où :
IN : Paramètre d’entrée
OUT : Paramètre de sortie
IN OUT : Paramètre d’entrée/Sortie
Par défaut le paramètre est IN
115/185
Les procéduresExemple
DECLAREPROCEDURE NouvSal (PNum IN Emp . Emp Id %Type , PAug NUMBER ) I S
VSal NUMBER ( 7 , 2 ) ;BEGIN
SELECT S a l INTO VSal FROM EmpWHERE emp Id=PNum;UPDATE Emp SET S a l = VSal+PAug WHERE Emp Id=PNum;COMMIT;
EXCEPTIONWHEN NO DATA FOUND THENDBMS OUTPUT. PUT LINE ( ’ Employé i n e x i s t a n t ’ ) ;
END ;BEGIN
NouvSal ( 7 5 5 0 , 5 0 0 ) ;EXCEPTION
WHEN OTHERS THEN DBMS OUTPUT. PUT LINE ( ’ E r r e u r ’ ) ;END ;
/
116/185
Les procéduresExemple
DECLAREVErr NUMBER ;PROCEDURE NouvSal (PNum Emp . Emp Id %TYPE, PAug NUMBER,
PErr OUT NUMBER ) I SVSal NUMBER ( 7 , 2 ) ;BEGIN
SELECT S a l INTO VSal FROM Emp WHERE emp Id=PNum;UPDATE Emp SET S a l = VSal+PAug WHERE Emp Id=PNum;COMMIT; PErr :=0
EXCEPTIONWHEN NO DATA FOUND THEN PErr :=1 ;
END ;
117/185
Les procéduresExemple
BEGINNouvSal ( 7 5 5 0 , 5 0 0 , VErr ) ;IF VErr = 0 THEN
DBMS OUTPUT. PUT LINE ( ’ O p é r a t i o n E f f e c t u é e ’ ) ;ELSE DBMS OUTPUT. PUT LINE ( ’ Employé i n e x i s t a n t ’ ) ;END IF ;
EXCEPTIONWHEN OTHERS THEN DBMS OUTPUT. PUT LINE ( ’ E r r e u r ’ ) ;
END ;/
118/185
Les fonctionsSyntaxe
DECLARE[ D é c l a r a t i o n s g l o b a l e s ]FUNCTION [(P1 , . . . , Pn ) ] RETURN Type I S
[ D é c l a r a t i o n s l o c a l e s ]BEGIN
. . .RETURN v a l e u r ;
EXCEPTION. . .
END ;BEGIN
−− Appel a l a f o n c t i o n. . . .
EXCEPTION. . .
END ;/
119/185
Les fonctionsExemple
DECLAREVNomComplet VARCHAR2( 4 0 ) ;FUNCTION NomComplet (PNum Emp . Emp I d%TYPE, PErr OUT NUMBER )
RETURN VARCHAR2 I SVLastName Emp . Last Name %Type ;VFirstName Emp . F i r s t N a m e %Type ;BEGIN
SELECT Last Name , F i r s t N a m eINTO VLastName , VFirstNameWHERE Emp Id=PNum; PErr :=0;
RETURN VLastName | | ’ ’ | | VFirstName ;EXCEPTION
WHEN NO DATA FOUND THEN PErr :=1; RETURN Nul l ;END ;
120/185
Les fonctionsExemple
BEGINVNomComplet:= NomComplet(&Num, VErr ) ;IF VErr = 0 THEN
DBMS OUTPUT. PUT LINE ( ’Nom Complet e s t : ’ | | VNomComplet ) ;ELSE DBMS OUTPUT. PUT LINE ( ’ Employé i n e x i s t a n t ’ ) ;
END IF ;EXCEPTIONWHEN OTHERS THEN DBMS OUTPUT. PUT LINE ( ’ E r r e u r ’ ) ;END ;
121/185
Le paramètre IN OUT
Paramètre jouant le rôle des deux paramètres IN et OUT
Obligatoire de le spécifier
Exemple :
SQL> Create or r e p l a c e p r o c e d u r e affnom ( v nom IN OUT va rcha r2 ) I S2 BEGIN3 v nom := UPPER ( v nom ) ;4 END affnom ;5 /
P r o c é d u r e c r é é e .
122/185
Appel de affnom de SQL*Plus
Définition d’une variable liée
Initialisation de la variable
SQL> v a r name varchar2 ( 1 0 ) ;SQL> begin : name := ’ mark ’ ; end ;
/P r o c é d u r e PL/SQL t e r m i n é e avec s u c c è sSQL> p r i n t nameNAME−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−mark
123/185
Appel de affnom de SQL*Plus
Exécution de la procédure avec un paramètre IN OUT
Affichage la nouvelle valeur de la variable
SQL> execute affnom ( : name ) ;P r o c é d u r e PL/SQL t e r m i n é e avec s u c c è s .SQL> p r i n t nameNAME−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−MARK
124/185
Passage de paramètres
Il y a plusieurs façons de passage de paramètres :
Appel de la procédure en spécifiant les paramètres
Appel de la procédure sans paramètre si ce dernier est unparamètre d’entrée initialisé
Appel de la procédure en changeant la position desparamètres (il faut spécifier le nom du paramètre)
125/185
Passage de paramètresExemple
CREATE OR REPLACE PROCEDURE r e n s e i g n e t u d( v nom IN etud . nom%t y p e de fau l t ’ i nconnu ’ ,
v a d r IN etud . adr%t y p e defau l t ’ i nconnu ’ )I SBEGIN
INSERT INTO etudVALUES ( e t u d e t u d i d . n e x t v a l , v nom , v a d r ) ;
END;
126/185
Passage de paramètresExemple
SQL> begin2 r e n s e i g n e t u d ( ’ mark ’ , ’ p a r i s ’ ) ;3 r e n s e i g n e t u d ;4 r e n s e i g n e t u d ( v a d r => ’ l y o n ’ ) ;5 end ;6 /
P r o c é d u r e PL/SQL t e r m i n é e avec s u c c è s .SQL> s e l e c t ∗ from etud ;
ETUDID NOM ADR−−−−−−−−−− −−−−−−−−−− −−−−−−−−−−
6 mark p a r i s7 inc onnu inconn u8 inc onnu l y o n
SQL>
127/185
Les procédures et les fonctions stockées
Sont des blocs PL/SQL qui possèdent un nom
Consistent à ranger le block PL/SQL compilé dans la base dedonnées (CREATE)
Peuvent être réutilisées sans être recompilées (EXECUTE)
Peuvent être appelées de n’importe bloc PL/SQL
Peuvent être regroupées dans un package
128/185
Les procédures stockéesSyntaxe
CREATE [ OR REPLACE ] PROCEDURE [(P1 , . . . , Pn ) ] I S[ D é c l a r a t i o n s des v a r i a b l e s l o c a l e s ]BEGIN. . .EXCEPTION. . .END;
/
Procedure Created : La procédure est correcteOu
Procedure Created with compilation errors : Corrigerles erreurs → SHOW ERRORS;
129/185
Les procédures stockéesExemple
CREATE [ OR REPLACE ] PROCEDUREAjoutProd ( P r e f P r o Prod . RefPro%TYPE , . . . PPr iUni
Prod . P r i U n i% TYPE, PErr OUT Number) I S
BEGININSERT INTO Prod VALUES( PrefPro , . . . , PPr iUni ) ;COMMIT;PErr :=0;
EXCEPTIONWHEN DUP VAL ON INDEX THENPErr :=1;
END;/
130/185
Appel des procédures stockéesSyntaxe
La procédure stockée est appelée par les applications soit :
En utilisant son nom dans un bloc PL/SQL (autre procédure)Par execute dans SQL*Plus
Dans un bloc PL/SQL :
DECLAREBEGIN
[ , . . . , ] ;END;
Sous SQL*PLUS :
EXECUTE [ , . . . , ] ;
131/185
Appel des procédures stockéesExemple
ACCEPT VRefPro . . .ACCEPT VPriUni . . .
DECLAREVErr NUMBER;
BEGINAjoutProd (&VRefPro , . . . , & VPriUni , VErr ) ;IF VErr=0 THEN
DBMS OUTPUT. PUT LINE ( ’ O p é r a t i o n E f f e c t u e r ’ ) ;ELSE DBMS OUTPUT. PUT LINE ( ’ E r r e u r ’ ) ;END IF ;
END ;/
132/185
Les fonctions stockéesSyntaxe
CREATE [ OR REPLACE ] FUNCTION [(P1 , . . . , Pn ) ]RETURN Type I S[ D é c l a r a t i o n s des v a r i a b l e s l o c a l e s ]BEGIN
I n s t r u c t i o n s SQL e t PL/ S q lRETURN( V a l e u r )
EXCEPTIONT r a i t e m e n t des e x c e p t i o n s
END;/
function Created : La fonction est correcteOufunction Created with compilation errors :Corriger les erreurs → SHOW ERRORS;
133/185
Les fonctions stockéesExemple
CREATE [ OR REPLACE ] FUNCTION NbEmp (PNumDep Emp . D e p t I d%Type ,PErr Out Number ) Return Number
I SVNb Number ( 4 ) ;
BEGINSe l e c t Count (∗ ) I n to VNb From Emp Where D e p t I d=PNumDep ;PErr :=0Return VNb ;
Except ionWhen No Data Found Then
PErr :=1;Return Nul l ;
END ;/
134/185
Appel des fonctions stockéesSyntaxe
La fonction stockée est appelée par les applications soit :
Dans une expression dans un bloc PL/SQL
Dans une expression dans par la commande EXECUTE (dansSQL*PLUS)
Dans un bloc PL/SQL :
DECLAREBEGIN
:= [ , . . . , ]END;
Sous SQL*PLUS :
EXECUTE :< var> := [ , . . . , ]
135/185
Appel des fonctions stockéesExemple
Accept VDep . . .Declare
VErr Number ;VNb Number ( 4 ) ;
BeginVNb :=NbEmp(&VDep , VErr ) ;I f VErr=0 Then
DBMS Output . P u t L i n e ( ’ Le nombred ’ employ ée s e s t : ’ | | VNb ) ;
E l s eDBMS Output . P u t L i n e ( ’ E r r e u r ’ ) ;
End I f ;End ;/
136/185
Appel des fonctions stockéesExemple
SQL> VARIABLE VNbSQL> EXECUTE : VNb:=NbEmp(&VDep , VErr ) ;P r o c é d u r e PL/SQL t e r m i n é e avec s u c c è s .SQL> PRINT VNb
VNB−−−−−−−−−−
300
137/185
Suppression des procédures et des fonctions stockéesSyntaxe
Comme tout objet manipulé par Oracle, les procédures et lesfonctions peuvent être supprimées si nécessaire
Cette suppression est assurée par la commande suivante :
DROP PROCEDURE nomprocedure ;DROP FUNCTION nomfonct ion ;
138/185
Suppression des procédures et des fonctions stockéesExemple
SQL> DROP PROCEDURE AjoutProd ;P r o c e d u r e dropped .
SQL> DROP FUNCTION NbEmp ;F u n c t i o n dropped .
139/185
Les procédures et les fonctions stockées
Quelques commandes utiles :
SELECT object name , o b j e c t t y p e from o b j ;
DESC nomprocedure
DESC nomfonct ion
140/185
Les packages
Un objet PL/SQL qui stocke d’autres types d’objet :procédures, fonctions, curseurs, variables, ...
Consiste en deux parties :
Spécification (déclaration)Corps (implémentation)
Ne peut pas être appelé, ni paramétré ni imbriqué
Permet à Oracle de lire plusieurs objets à la fois en mémoire
141/185
Développement des packages
Sauvegarder l’énoncé de CREATE PACKAGE dans deuxfichiers différents (ancienne/dernière version) pour faciliter deéventuelles modifications
Le corps du package ne peut pas être compilé s’il n’est pasdéclaré (spécifié)
Restreindre les privilèges pour les procédures à une personnedonnée au lieu de lui donner tous les droits sur toutes lesprocédures
142/185
La spécification du packageSyntaxe
Contient la déclaration des curseurs, variables, types, procédures,fonctions et exceptions
CREATE [OR REPLACE ] PACKAGE I S [ D é c l a r a t i o n des v a r i a b l e s e t Types ]
[ D é c l a r a t i o n des c u r s e u r s ][ D é c l a r a t i o n des p r o c é d u r e s e t f o n c t i o n s ][ D é c l a r a t i o n des e x c e p t i o n s ]
END[ ] ;/
143/185
La spécification du packageExemple
Create Or R e p l a c e Package PackProdI s C u r s o r CProd I s Select RefPro , DesPro
From P r o d u i t ;P r o c e d u r e AjoutProd ( P r e f P r o Prod . RefPro%Type ,
. . . , PErr Out Number ) ;P r o c e d u r e ModifProd ( P r e f P r o Prod . RefPro%Type ,
. . . , PErr Out Number ) ;P r o c e d u r e SuppProd ( P r e f P r o Prod . RefPro%Type ,
. . . , PErr Out Number ) ;P r o c e d u r e Af fProd ;
EndPackProd ;/
144/185
Le corps du packageSyntaxe
On implémente les procédures et fonctions déclarées dans laspécification
Create [Or R e p l a c e ] Package Body I s[ I m p l é m e n t a t i o n p r o c é d u r e s | f o n c t i o n s ]
End [ ] ;/
145/185
Le corps du packageSyntaxe
Create Or R e p l a c e Package Body PackProdI sP r o c e d u r e AjoutProd ( P r e f P r o Prod . RefPro%Type ,
. . . , PErr Out Number)I sBegin
In se r t Into Prod Values ( PrefPro , . . . , PPr iUni ) ;Commit ;PErr :=0;
ExceptionWhen Dup Val On Index Then PErr :=1;When Others Then PErr := 1 ;
End ;
146/185
Le corps du packageSyntaxe
P r o c e d u r e ModifProd ( P r e f P r o Prod . RefPro%Type ,. . . , PErr Out Number)
I s B Boolean ;Begin
. . .EndPackProd ;/
147/185
Appels des procédures / fonctions du packageSyntaxe
Les procédures et les fonctions définies dans un package sontappelées de la façon suivante :
.[( Paramèt res ) ] ;
Var := .[( Paramèt res ) ] ;
148/185
Appels des procédures / fonctions du packageExemple
Accept VRef Prompt ’ . . . . . . ’ ;Accept VPri Prompt ’ . . . . . . ’ ;Declare
VErr Number ;Begin
PackProd . ModifProd(&VRef , . . . , &VPri , VErr ) ;I f VErr= 0 Then
DBMS Output . P u t L i n e ( ’ T r a i t e m e n t e f f e c t u é ’ ) ;Else
DBMS Output . P u t L i n e ( ’ E r r e u r ’ ) ;End I f ;
End ;/
149/185
Packages : Exemples
Création le corps du package suivant en mode interactif :
SQL> create or r e p l a c e package body pack1 i s2 f u n c t i o n d o u b l e x ( x number ) r e t u r n number i s3 begin4 r e t u r n (2∗ x ) ;5 end ;6 end ;7 /
A v e r t i s s e m e n t : Corps de package c r é é avec e r r e u r sde c o m p i l a t i o n .
150/185
Packages : Exemples
Pour afficher les erreurs on utilise la commande SHOW ERRORS
SQL> show e r r o r sE r r e u r s pour PACKAGE BODY PACK1 :
LINE/COL ERROR−−−−−−−− −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−0/0 PL/SQL : C o m p i l a t i o n u n i t a n a l y s i s t e r m i n a t e d1/14 PLS−00201: l ’ i d e n t i f i c a t e u r ’PACK1 ’ d o i t ê t r e d é c l a r é1/14 PLS−00304: i m p o s s i b l e de c o m p i l e r l e c o r p s de ’PACK1 ’
s a n s sa s p é c i f i c a t i o n
SQL>
151/185
Les triggers (Déclencheurs)
Un trigger est un programme PL/SQL qui s’exécuteautomatiquement avant ou après une opération LMD(Insert, Update, Delete)
Contrairement aux procédures, un trigger est déclenchéautomatiquement suite à un ordre LMD
152/185
Evénement-Condition-Action
Un trigger est activé par un événement
Insertion, suppression ou modification sur une table
Si le trigger est activé, une condition est évaluée
Prédicat qui doit retourner vrai
Si la condition est vraie, l’action est exécutée
Insertion, suppression ou modification de la base de données
153/185
Composants du trigger
À quel moment se déclenche le trigger ?
BEFORE : le code dans le corps du trigger s’exécute avant lesévènements de déclenchement LMD
AFTER : le code dans le corps du trigger s’exécute avant lesévènements de déclenchement LMD
154/185
Composants du trigger
Les évènements du déclenchement :
Quelles sont les opérations LMD qui causent l’exécution dutrigger ?
INSERT
UPDATE
DELETE
La combinaison des ces opérations
155/185
Composants du trigger
Le corps du trigger est défini par un bloc PL/SQL anonyme
[DECLARE]BEGIN[ EXEPTION ]END;
156/185
Composants du triggerSyntaxe
Create [Or R e p l a c e ] Trigger [ B e f o r e | A f t e r ] On [ For Each Row ] [When ]DeclareBeginExceptionEnd ;/
157/185
Composants du triggerExemple
Création d’un trigger qui remplit la table statistique
(Nom Table → Nb Insert) lors d’une insertion dans la table factureCreate Or R e p l a c e Tr igge r S t a r t F a c t u r e
A f t e r I n s e r t On F a c t u r eFor Each Row
Dec lareVNbIn se r t Number ;
BeginSe l e c t N b I n s e r t I n to VNbIn se r t
From S t a t i s t i q u e Where Nom Table= ’ F a c t u r e ’ ;Update S t a t i s t i q u e
Set N b I n s e r t = VN bInse r t+1Where Nom Table= ’ F a c t u r e ’ ;
Except ionWhen No Data Found Then
I n s e r t In to S t a t i s t i q u e Values ( 1 , ’ F a c t u r e ’ ) ;End ;/
158/185
Manipulation des triggers
Activer ou désactiver un Trigger:
Alter Trigger [ Enab le | D i s a b l e ] ;
Supprimer un Trigger:
Drop Trigger ;
Déterminer les triggers de votreBD:
Select Trigger Name From U s e r T r i g g e r s
159/185
Les attributs :Old et :New
Ces deux attributs permettent de gérer l’ancienne et la nouvellevaleurs manipulées
Insert(...) ... → NewDelete ...
Where(...) ... → OldUpdate ...
Set ( ...) ... → NewWhere(...) ... → Old
160/185
Les attributs :Old et :NewExemple :
E t u d i a n t ( Matr Etu , Nom , . . . , Cod Cla )C l a s s e ( Cod Cla , Nbr Etu )
Trigger mettant à jour la table classe suite à une insertion d’unnouvel étudiant
Create or R e p l a c e Trigger MajNbEtudA f t e r I n s e r t On E t u d i a n tFor Each Row
BeginUpdate C l a s s e
Set Nbr Etud = Nbr Etud+1Where Cod Cla =:New . Cod Cla ;
End ;/
161/185
Les prédicats inserting, updating et deleting
Inserting:
True: Le trigger est déclenché suite à une insertionFalse: Sinon
Updating:
True: le trigger est déclenché suite à une mise à jourFalse: sinon
Deleting:
True: le trigger est déclenché suite à une suppressionFalse: sinon
162/185
Les prédicats inserting, updating et deletingExemple
Create Or R e p l a c e Trigger MajNbEtudA f t e r I n s e r t Or Delete On E t u d i a n tFor Each Row
BeginI f I n s e r t i n g Then
Update C l a s s e Set Nbr Etud = Nbr Etud+1Where Cod Cla =:New . Cod Cla ;
End I f ;I f D e l e t i n g Then
Update C l a s s e Set Nbr Etud = Nbr Etud−1Where Cod Cla =: Old . Cod Cla ;
End I f ;End ;/
163/185
SéquencesSyntaxe
Auto-incrémentation d’une colonne (évite les doublons)
Définition :
CREATE SEQUENCE sequence name[ INCREMENT BY #][START WITH #][MAXVALUE # | NOMAXVALUE][ MINVALUE # | NOMINVALUE][ CYCLE | NOCYCLE]
Suppression
DROP SEQUENCE sequence name
Pseudo-colonne CURRVAL : Valeur courante de la séquencePseudo-colonne NEXTVAL : Incrémentation de la séquence etretourne la nouvelle valeur
164/185
SéquencesExemple
drop s e q u e n c e SEQ ANNOTATION ;create s e q u e n c e SEQ ANNOTATION
s t a r t w i t h 1i n c r e m e n t by 1n o c y c l emaxvalue 30000
INSERT INTO p r o f ( prof num , prof nom , p r o f p r e n o m )VALUES (SEQ ANNOTATION . NEXTVAL, ’ Dupond ’ ’ M i c h e l ’ )
SELECT s e q a n n o t a t i o n . CURRVAL from d u a l ;
165/185
SQL dynamique
Construction dans un programme une requête SQL avant del’exécuter
Possibilité de création d’un code générique et réutilisable(sinon simple paramètrage de valeur de remplacement de laclause where)
Execute immediate chaine_de_caractères ;
chaine_de_caractères est une commande sql donnée entre’...’
166/185
SQL dynamique
Exemple 1 :
BeginExecute immediate ’ c r e a t e t a b l e t e s t ( c o l 1 : number ) ; ’ ;
End ;
Exemple 2 :
DeclareW req varchar2 ( 4 0 0 0 ) ;Begin
w req := ’ c r e a t e t a b l e t e s t ( c o l 1 : number ) ; ’ ;Execute immediate w req ;End ;
167/185
Gestion des transactions
Une transaction est une unité logique de travail, un ensembled’instructions de mise à jour des données que SQL traité commeune seule entité :
soit toutes les instructions sont exécutées ensemble,
soit aucune ne l’est.
Exemple :si une somme d’argent doit transiter d’un compte à un autre, lesdeux opérations (crédit et débit) de mise à jour doiventabsolument s’effectuer toutes les deux (ou ne pas s’effectuer dutout) pour que la cohérence des données soit respectée.
168/185
Gestion des transactions
Le mécanisme des transactions est nécessaire parce que diversévénements peuvent interrompre l’exécution d’une séquenced’instructions :
défaillance du système,
panne de courant, micro-coupure,
le désir de revenir en arrière et abandonner les modificationsfaites jusqu’alors.
169/185
Gestion des transactions
Dans Oracle, le début d’une transaction est implicitementdéfini par le début de la session, ou par la dernière instructionCOMMIT ou ROLLBACK.
Les instructions du DDL et du DCL exécutentautomatiquement des COMMIT.
Les modifications (INSERT, DELETE, UPDATE) d’une table nesont rendues permanentes que si l’on quitte la session ou sil’on exécute la commande COMMIT.
170/185
Gestion des transactions
Tant que les changements à la base n’ont pas eu lieu de façonpermanente :
l’utilisateur peut voir ces changements par des ordres SELECT,
les autres utilisateurs ne voient pas les modifications,
l’utilisateur peut défaire les modifications par l’instructionROLLBACK.
Un ROLLBACK automatique a lieu en cas de panne du système
171/185
Contrôle de transactionsCommandes COMMIT et ROLLBACK
Lancer une transaction avec la première commande du LMD àla suite d’un COMMIT ou un ROLLBACK
Utiliser le COMMIT ou le ROLLBACK de SQL pour terminer unetransaction
172/185
Commande ROLLBACK
173/185
Contrôle de transactionsExemple de traitement des transactions dans un bloc PL/SQL
BEGININSERT INTO temp ( num col1 , num col2 , c h a r c o l )
VALUES ( 1 , 1 , ’ROW 1 ’ ) ;SAVEPOINT a ;INSERT INTO temp ( num col1 , num col2 , c h a r c o l )
VALUES ( 2 , 2 , ’ROW 2 ’ ) ;SAVEPOINT b ;INSERT INTO temp ( num col1 , num col2 , c h a r c o l )
VALUES ( 3 , 3 , ’ROW 3 ’ ) ;SAVEPOINT c ;ROLLBACK TO SAVEPOINT b ;COMMIT;
END;
Quel est l’état de la table à la fin de l’exécution du bloc ?
174/185
Exemple de transactions en parallèle
CREATE TABLE T ( A NUMBER,
B NUMBER, PRIMARY KEY (A));
INSERT INTO T VALUES ( 1, 1);
INSERT INTO T VALUES ( 2, 2);
SELECT * FROM T ;
Le résultat obtenu est :
A B
-------- ---------
1 1
2 2
COMMIT ;
Validation effectuée.
175/185
Exemple de transactions en parallèleINSERT INTO T VALUES ( 3, 3);
INSERT INTO T VALUES ( 4, 4);
SELECT * FROM T ;
Le résultat obtenu est :
A B
--------- ---------
1 1
2 2
3 3
4 4
ROLLBACK ;
Annulation (ROLLBACK) effectuée.
SELECT * FROM T ;
Le résultat obtenu est :
A B
--------- ---------
1 1
2 2
176/185
Exemple de transactions en parallèle
INSERT INTO T VALUES ( 3, 3);
SAVEPOINT A ;
1 ligne créée.
Point de sauvegarde (SAVEPOINT) créé.
INSERT INTO T VALUES ( 4, 4);
1 ligne créée.
SELECT * FROM T ;
Le résultat obtenu est :
A B
--------- ---------
1 1
2 2
3 3
4 4
177/185
Exemple de transactions en parallèle
ROLLBACK TO SAVEPOINT A;
Annulation (ROLLBACK) effectuée.
SELECT * FROM T ;
Le résultat obtenu est :
A B
--------- ---------
1 1
2 2
3 3
COMMIT;
178/185
Blocage des tuples et des tables
Transaction T1
SELECT * FROM T ;
Le résultat obtenu est :
A B
--------- ---------
1 1
2 2
3 3
Transaction T2
SELECT * FROM T ;
Le résultat obtenu est :
A B
--------- ---------
1 1
2 2
3 3
179/185
Blocage des tuples et des tables
Transaction T1
update t set b = b - 2 ;
1 ligne mise à jour.
select * from t;
A B
--------- ---------
1 -1
2 0
3 1
Transaction T2
select * from t ;
A B
--------- ---------
1 1
2 2
3 3
update t set b = b - 2 ;
-- blocage tant que T1 n’a pas fait de COMMIT
180/185
Blocage des tuples et des tables
Transaction T1
COMMIT;
Validation effectuée.
Transaction T2
1 ligne mise à jour.
select * from t;
A B
--------- ---------
1 -3
2 -2
3 -1
COMMIT;
Validation effectuée.
181/185
Blocage des tuples et des tables
Transaction T1
select * from t;
A B
--------- ---------
1 -3
2 -2
3 -1
Transaction T2
select * from t;
A B
--------- ---------
1 -3
2 -2
3 -1
182/185
Blocage des tuples et des tables
Transaction T1
lock table t IN EXCLUSIVE MODE;
Table(s) verrouillée(s).
Mises à jour
Mises à jour
Commit ;
Transaction T2
En attente
En attente
lock table t IN EXCLUSIVE MODE;
etc...
183/185
Blocage des tuples et des tables
Le blocage de tuples et de tables se fait par la commande SQL :
LOCK TABLE LeNomDeLObjet IN LeMode MODE ;
LeNomDeLObjet : Le nom d’une table ou d’une vue.
Le mode partagé ou exclusif ... (SHARE, SHARE UPDATE,EXCLUSIVE)
184/185
Bilan
185/185
PL/SQLIntroductionStructure de blocs PL/SQLVariables attachéesBloc PL/SQLOpérateursInstructionsManipulation de donnéesStructure de contrôleTypes de données complexesTables PL/SQLCurseursExceptionsSous-programmesPackagesTriggersSéquencesSQL dynamiqueContrôle de transactions
Transaction