New Bases de Données Avancées PL/SQL - LIMSI · 2019. 2. 19. · Types de donn ees scalaires...

Bases de Données AvancéesPL/SQL

Thierry Hamon

Bureau H202Institut Galilée - Université Paris 13

&LIMSI-CNRS

[email protected]

https://perso.limsi.fr/hamon/Teaching/P13/BDA-INFO2-2018-2019/

INFO2 – BDA

1/185

[email protected]://perso.limsi.fr/hamon/Teaching/P13/BDA-INFO2-2018-2019/

Communication avec un SGBD utilisant SQL

2/185

Solution complète Oracle

3/185

Clauses SQLSELECT Interrogation des données

INSERTUPDATE Langage de Manipulation de Données (LMD)DELETE

CREATEALTERDROP Langage de Définition de Données (LDD)RENAMETRUNCATE

GRANT Langage de Contrôle de Données (LCD)REVOKE

COMMITROLLBACK Contrôle de transactionSAVEPOINT

4/185

PL/SQLProcedural Language for Structured Query Language

Langage fournissant une interface procédurale au SGBDOracle

Intègration du langage SQL en lui apportant une dimensionprocédurale

Réalisation de traitements algorithmiques (ce que ne permetpas SQL)

Mise à disposition de la plupart des mécanismes classiques deprogrammation des langages hôtes tels que C, COBOL,PASCAL, C++, JAVA ...

5/185

Environnement PL/SQL

6/185

Avantages de PL/SQL

PL/SQL complément de SQL (qui n’est pas procédural)Mécanismes offerts par PL/SQL :

Structures itératives : WHILE *** LOOP, FOR *** LOOP,LOOP ***

Structures conditionnelles :IF *** THEN *** ELSE | ELSEIF *** ENDIF, CASE ***

Déclaration des curseurs et des tableaux

Déclaration de variables

Affectation de valeurs aux variables

Branchements : GOTO, EXIT

Exceptions : EXCEPTION

7/185

Avantages de PL/SQLIntégration

Meilleure cohérence du code avec les données

Utilisation de librairies standards prédéfinies (bibliothèquespartagées)

8/185

Avantages de PL/SQLBlocs SQL

Traitement par/de blocs SQL dans un énoncé PL/SQL(optimisation des transactions réseaux)

9/185

Avantages de PL/SQLExécution de programmes modulaires

Traitements complexes (cas particuliers, erreurs)Traitements des exceptions...

10/185

Avantages de PL/SQLRésumé

Langage portable

Utilisation de variable de stockage

Utilisation de type simple ou de type structurédynamiquement (%TYPE, %ROWTYPE, etc.)

Utilisation des structures de contrôle des langages procéduraux

Gestion et manipulation des erreurs

Création d’ordre SQL dynamique

11/185

Utilisation de PL/SQL

3 formes :

Bloc de code, executé comme une commande SQL (utilisationd’un interpréteur standard SQL+ ou iSQL*PLus)

Fichier de commandes PL/SQL

Programme stocké : procédure, fonction, package ou trigger

12/185

Structure d’un bloc PL/SQL−− S e c t i o n d é 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 é 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 é 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

Procédure

Fonction

14/185

Type de blocsBloc anonyme

Structure classique (1 à 3 sections)

Un bloc ne peut ê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 blocsProcédure

Bloc PL/SQL nommé

puis compilé et stocké 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 blocsProcé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 quicrée la procédure PL/SQL

compile et stocke dans la base le bloc PL/SQL compris entre le BEGIN etle END, référencé par ’proc exemple’

19/185

Type de blocsProcédure

Remarques :

Exécution (auto) de la procédure :

SQL> EXECUTE proc_exemple

Pas d’exécution de procédure (ou d’instructions) en fin detransaction (COMMIT, ROLLBACK, Ordre DDL)Décision d’enregistrement ou d’annulation de la transaction encours : à réaliser par le programme appelant la procédure

20/185

Type de blocsFonction

Fonction : procédure retournant une valeur

FUNCTION nom RETURN t y p e d o n n é 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 requête SQLExemple :SQL> SELECT solde(1000) FROM dual ;

Solde(1000)

-----------

12024,50

Appel d’une fonction comme une procé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

ConstantesCompositesRéférences

Variables de l’environnement extérieur à PL/SQL

Attachées (Bind)Hôtes (Host)

24/185

Dé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́ 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 employés

v d a t e n a i s s a n c e := ’23−SEP−2004 ’ ;

Fixer le nom d’un employé à ’Clément’

v nom := ’ Cl ément ’ ;

26/185

Types de données scalairesTypes de base – châınes de caractères

VARCHAR2(n)/NVARCHAR2(n) : Châıne de n caractères(n < 4000octets) – longueur variable(préfixe N : prise en compte de la localisation – NLS/NationalLanguage Support)

CHAR(n)/NCHAR(n) : Châıne de n caractères(n < 2000octets) – longueur fixe, rempli par des espaces

LONG/RAW/LONG RAW : Châıne de caractère ou de donnéesbinaires (raw) de longueur variable ( < 2000octets)

27/185

Types de données scalairesTypes de base – nombres et date

NUMBER (n , m) : Réel avec n chiffres significatifs et m décimals

INTERGER/FLOAT : Nombre entier/réel signé

BINARY_INTEGER : Nombre entier signé sur 32 bits (utilisation de lalibrairie mathématique)

DATE : date entre le 1 janvier -4712 et le 21 décembre 9999

BOOLEAN : booléenNB : pas d’équivalent en SQL.Il n’est donc pas possible

de stocker un booléen dans une tablede sélectionner un élément de table dans un variable booléenned’utiliser un booléen dans une requête SQL ou une fonctionPL/SQL appelée dans une requête SQL (paramètre en entrée ouvaleur de retour)

28/185

Dé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

Définition : déclaration d’une variable associée à

Une colonne d’une table dans la BD

Une variable précédemment dé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 données composites

Types :

Les tables PL/SQL

Les records PL/SQL

(voir plus loin)

31/185

Variables attachées/Bind variables

Variables de substitution dans les requêtes SQLVariable référençant des variables non-PL/SQL

Economie d’analyse de la requête, donc gain de temps pourOracle→ Mécanisme considéré par certains comme le point clé deperformance d’Oracle

32/185

Variables attachées/Bind variablesDéclaration

Déclaration, en SQL*Plus, d’une variable attaché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 attachées/Bind variablesRéfé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 référence aux variables non-PL/SQL comme des variableshôtePrécéder la référence par un ’ : ’

34/185

Variables attachées/Bind variablesExemple d’utilisation – optimisation

Exemple de requête SELECT généré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 requête,

Vérification si la requête a déjà été été soumise

Si oui, récupération du plan d’exécution de la requête, etexécution de la requête

35/185

Variables attachées/Bind variablesExemple d’utilisation – optimisation

Si non,analyse syntaxique de la requêtedéfinition des différentes possibilités d’exécutiondéfinition du plan d’exécution optimal

→ Processus coûteux en temps CPU, alors que seule la valeurde id change !

Solution : réutiliser le plan d’exécution existant→ Nécessite d’utiliser des variables attachées :

Substitution de la valeur par la variable attachéEnvoi de la même requê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 être écrites sur plusieurs lignes.Les unités lexicales peuvent être séparées par des espaces :

DélimiteursIdentificateursLittéraux (ou constantes)Commentaires

37/185

Bloc PL/SQLsyntaxe et directives

Les littéraux

Les dates et les châınes de caractères délimité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 être des valeurs simples ou desexpressions

V s a l a i r e := 1500 + 300 ;

38/185

Commentaires dans le code

Précéder un commentaire écrit sur une seule ligne par ’--’.

Placer un commentaire é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 châınes de caractères

Les fonctions de conversion de type de données

Les fonctions de dates

40/185

Exemples de fonctions SQL en PL/SQLExemples :

Recomposer l’adresse d’un employé :

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 châıne

V chr := Substr ( ’PL/SQL ’ , 4 , 3 ) ;

Replacement d’une châı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 imbriqués et porté 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

Opérateurs dans PL/SQL

Identique à SQL

LogiqueArithmétiqueConcaténationParenthèses pour contrôler l’ordre des opérations

Opérateur supplémentaire : Opérateur d’exponentiation **

43/185

Utilisation des variables liées

Pour référencer une variable en PL/SQL, on doit préfixer sonnom par un ’:’

Exemple :

: c o d e r e t o u r := 0 ;IF v é r i f i e r c r é 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 données à partir de la BD par lacommande SELECT. Un seul ensemble de valeurs peut êtreretourné

Effectuer des changements aux lignes dans la BD par lescommandes du LMD

Contrôler des transactions avec les commandes COMMIT,ROLLBACK et SAVEPOINT

Déterminer les résultats du LMD avec des curseurs implicites(voir plus loin)

45/185

Instruction SELECT dans PL/SQL

Récupérer une donnée de la BD avec SELECT.

Syntaxe

SELECT l i s t e s é 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


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


Retourne la somme des salaires de tous les employés d’undépartement donné.

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 é g a tINTO v s o m s a lFROM empWHERE deptno = v d e p t n o ;

END;

48/185

Manipulation de données en PL/SQL

Effectuer des mises à jour des tables de la BD utilisant lescommandes du LMD :

INSERT

UPDATE

DELETE

49/185

Insertion de données

Ajouter les informations d’un nouvel employé à la table emp

Exemple

DECLAREv empno NUMBER NOT NULL := 1 0 5 ;

BEGININSERT INTO emp ( empno , emp nom , poste , deptno )VALUES ( v empno , ’ Cl ément ’ , ’ D i r e c t e u r ’ , 1 0 ) ;

END ;

50/185

Mise à jour de données

Augmenter le salaire de tous les employé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 données

Suppression des lignes appartenant au dé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 contrô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 THENé nonc é s ;

[ ELSIF c o n d i t i o n THENé nonc é s ; ]

[ELSEé nonc é s ; ]

END IF ;

54/185

Instruction IFExemple de IF simple

Mettre le ID de l’employé ’MARK’ à 101.

IF v nom = ’MARK’ THENv ID := 1 0 1 ;

END IF ;

55/185

IF simple

Si le nom de l’employé est ’CLEMENT’, lui attribuer le poste’Enseignant’, le département n◦ 102 et une commission de 25% sur son salaire actuel

Exemple

. . .IF v nom = ’ Cl é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’employé est ’CLEMENT’, lui attribuer le poste’Enseignant’, le département n◦ 102 et une commission de 25% sur son salaire actuel, sinon afficher le message ’Employéinexistant’Exemple

. . .IF v nom = ’ Cl é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 ( ’ Employé i n e x i s t a n t ’ ) ;

END IF ;. . .

57/185

IF-THEN-ELSIF

Pour une valeur donnée en entrée, retourner une valeurcalculé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 é l i m i t e u rénonc é 1 ; −− énonc é. . . .EXIT [ WHEN c o n d i t i o n ] ; −− énonc é EXIT

END LOOP;

When : condition est une variable booléenne ou expression (TRUE,FALSE, ou NULL);

60/185

Boucle de baseExemple

Insé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

énonc é 1 ;énonc é 2 ;. . . . .

END LOOP;

Utiliser la boucle FOR pour raccourcir le test d’un nombred’itérations

Ne pas déclarer l’indice, il est déclaré implicitement

62/185

Boucle FORexemple

Insérer Nb articles indexés de 1 à Nb avec la date du systè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 LOOPénonc é 1 ;énonc é 2 ;. . . . .

END LOOP;

La condition est évaluée au début de chaque itération

Utiliser la boucle WHILE pour répéter des énoncé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 imbriquées et Labels

Imbriquer les boucles à niveaux multiples

Utiliser les labels pour distinguer les blocs et les boucles

Quitter la boucle extérieure avec un EXIT référençant le label

66/185

Boucles imbriqué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 données complexes

Types :

RECORDSTABLES

Contiennent des composants internes

Sont réutilisables

68/185

Records PL/SQL

Contiennent des champs qui sont soit des scalaires, desrecords ou des tables PL/SQL

Structure similaire à des enregistrements dans les langages deprogrammation classiques

Très utiles pour rechercher des lignes de données dans unetable et les traiter

69/185

Cré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

Déclaration d’une variable associée à une collection decolonnes dans une table ou une vue de la BD

le nom de la table doit précé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

Déclarer une variable pour stocker les mêmes informationsconcernant une personne telles qu’elles sont stockées dans latable PERS

p e r s o n n e Pers%ROWTYPE;

Déclarer une variable pour stocker les mêmes informationsconcernant un article telles qu’elles sont stockées dans la tableART

a r t i c l e Art%ROWTYPE;

72/185

Tables PL/SQL

Le type de données complexe TABLE offre au développeur unmécanisme pour traiter les tableaux

Il se compose de deux colonnes :

Une clé primaire de type BINARY_INTEGERUne colonne de type scalaire ou record

73/185

Cré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 mémoire

Clé primaire Colonne

1 Ritchie

2 Marvin

3 Dennis

. . . . . .

. . . . . .

BINARY INTEGER Scalaire

75/185

Cré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 à l a l i g n e 1 :ORA−01403: Aucune donnée t r o u v é eORA−06512: à l i g n e 10

76/185

TABLE de RECORDS en PL/SQL

Définit une table dont la deuxième colonne est unenregistrement au lieu d’un scalaire

Pour définir la deuxième colonne :

Soit en utilisant l’attribut %ROWTYPESoit en utilisant un record déjà dé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 privée de SQL (zonetampon)

Il y a deux types de curseurs:

Curseurs implicitesCurseurs explicites

Oracle utilise les curseurs implicites pour analyser et exécuterles énoncés de SQL

Les curseurs explicites sont déclarés explicitement pas leprogrammeur

80/185

Les attributs des curseurs SQLLes attributs des curseurs SQL permettent de tester les résultatsdes énoncés SQL

SQL%ROWCOUNT Nombre de lignes affecté par l’énoncéSQL le plus récent (renvoie un entier).

SQL%FOUND attribut booléen qui prend la valeur TRUEsi l’énoncé SQL le plus récent affecte uneou plusieurs lignes.

SQL%NOTFOUND attribut booléen qui prend la valeur TRUEsi l’énoncé SQL le plus récent n’affecteaucune ligne.

SQL%ISOPEN Prend toujours la valeur FALSE parce quePL/SQL ferment les curseurs implicitesimmédiatement après leur exécution.

81/185

Les attributs des curseurs SQL

Supprimer de la table ITEM des lignes ayant un ordre spécifié.Afficher le nombre de lignes supprimé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́ e s ’ ) ;END ;

Remarque : ne pas oublier Set ServerOutput on (sousSQLPLUS)

82/185

Les curseurs

Chaque énoncé SQL exécuté par Oracle a son propre curseur :

Curseurs implicites : déclarés pour tout énoncé SELECT duLMD ou PL/SQL

Curseurs explicites : déclarés et nommés par le programmeur

83/185

Contrôle des curseurs explicites

84/185

Contrôle des curseurs explicites

85/185

Déclaration des curseursSyntaxe

CURSOR n o m d u c u r s e u r I Sun énonc é SELECT ;

Ne pas inclure la clause INTO dans la déclaration du curseur

Si le traitement des lignes doit être fait dans un ordrespécifique, on utilise la clause ORDER BY dans la requête

86/185

Dé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 exécuter la requête et identifierl’ensemble actif

Si la requête ne renvoie aucune ligne, aucune exception n’auralieu

Utiliser les attributs des curseurs pour tester le résultat duFETCH

88/185

Recherche des donné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 donné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 ées r e c h e r c h é 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 après la fin du traitement des lignes

Rouvrir le curseur si nécessaire

On ne peut pas rechercher des informations dans un curseur sice dernier est fermé

91/185

Les attributs du curseur explicite

Obtenir les informations d’é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 récentne retourne aucune ligne

CUR EXP%FOUND BOOLEAN Prend la valeur TRUEsi le FETCH le plus récent

retourne une ligneCUR EXP%ROWCOUNT NUMBER Retourne le nombre de lignes

traitées jusqu’ici

92/185

Contrôle des recherches multiples

Traitement de plusieurs lignes d’un curseur en utilisant uneboucle

Rechercher une seule ligne à chaque itération

Utiliser les attributs du curseur explicite pour tester le succè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 traitées

Utiliser les attributs %FOUND et %NOT FOUND pour formuler letest d’arrê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’é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 à 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 façon implicite

Ne pas déclarer le record, il est déclaré 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 : mécanisme pourmanipuler les erreurs rencontrées lors de l’exécution

Possibilité de continuer l’exécution si l’erreur n’est passuffisamment importante pour produire la terminaison de laprocédure

Décision de continuer une procédure après erreur : décisionque le développeur doit faire en fonction des erreurs possibles

101/185

Types des exceptions

Déclenchées implicitement

Exceptions Oracle prédéfiniesExceptions Oracle Non-prédéfinies

Déclenchées explicitement

Exceptions dé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

énonc é1 ;énonc é2 ;. . .

[WHEN e x c e p t i o n 2 [OR e x c e p t i o n 4 . . . ] THENénonc é3 ;énonc é4 ;. . . ]

[WHEN OTHERS THENénonc é5 ;énonc é6 ;. . . ]

103/185

Capture des exceptions prédéfinies

Faire référence au nom dans la partie traitement desexceptions

Quelques exceptions prédéfinies :

NO DATA FOUND

TOO MANY ROWS

INVALID CURSOR

ZERO DIVIDE

DUP VAL ON INDEX

104/185

Exceptions prédéfiniesExemple

BEGIN. . .

EXCEPTIONWHEN NO DATA FOUND THENénonc é1 ; énonc é2 ;DBMS OUTPUT. PUT LINE (TO CHAR ( etudno ) | |

’ Non v a l i d e ’ ) ;WHEN TOO MANY ROWS THEN

énonc é3 ; énonc é4 ;DBMS OUTPUT. PUT LINE ( ’ Données i n v a l i d e s ’ ) ;

WHEN OTHERS THENénonc é5 ; énonc é6 ;

DBMS OUTPUT. PUT LINE ( ’ A u t r e s e r r e u r s ’ ) ;

105/185

Capture des exceptions définies par l’utilisateur

106/185

Capture des exceptions non-prédéfiniesExemple

Capture de l’erreur n◦ 2291 (violation de la contrainte intégrité).

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 é g r i t é ’ ) ;. . .

END;

107/185

Capture des exceptions définies par l’utilisateur

108/185

Exceptions dé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 numérique du code de l’erreur

SQLERRM : Retourne le message associé au numé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 séquence d’instruction PL/SQLqui possède un nomOn distingue deux types de sous programmes :

Les procéduresLes fonctions

112/185

Les sous-programmes

Une procédure : sous programme qui ne retourne des résultatsseulement dans ses paramètres

Une fonction : sous programme qui retourne des résultatsdans :

Le nom de la fonctionLes paramètres de la fonction

113/185

Les procéduresSyntaxe

DECLARE. . .

PROCEDURE [(P1 , . . . , Pn ) ] I S[ D é 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 é d u r e ∗/. . .

EXCEPTION. . .

END ;/

114/185

Les procéduresSyntaxe des paramètres

P1,...,Pn suivent la syntaxe :

[ IN | OUT | IN OUT]

Où :

IN : Paramètre d’entrée

OUT : Paramètre de sortie

IN OUT : Paramètre d’entrée/Sortie

Par défaut le paramètre est IN

115/185

Les procé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 ( ’ Employé 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


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


BEGINNouvSal ( 7 5 5 0 , 5 0 0 , VErr ) ;IF VErr = 0 THEN

DBMS OUTPUT. PUT LINE ( ’ O p é r a t i o n E f f e c t u é e ’ ) ;ELSE DBMS OUTPUT. PUT LINE ( ’ Employé 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 é 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 é 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 ( ’ Employé 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 paramètre IN OUT

Paramètre jouant le rôle des deux paramètres IN et OUT

Obligatoire de le spé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 é d u r e c r é é e .

122/185

Appel de affnom de SQL*Plus

Définition d’une variable liée

Initialisation de la variable

SQL> v a r name varchar2 ( 1 0 ) ;SQL> begin : name := ’ mark ’ ; end ;

/P r o c é d u r e PL/SQL t e r m i n é e avec s u c c è sSQL> p r i n t nameNAME−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−mark

123/185

Appel de affnom de SQL*Plus

Exécution de la procédure avec un paramètre IN OUT

Affichage la nouvelle valeur de la variable

SQL> execute affnom ( : name ) ;P r o c é d u r e PL/SQL t e r m i n é e avec s u c c è s .SQL> p r i n t nameNAME−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−MARK

124/185

Passage de paramètres

Il y a plusieurs façons de passage de paramètres :

Appel de la procédure en spécifiant les paramètres

Appel de la procédure sans paramètre si ce dernier est unparamètre d’entrée initialisé

Appel de la procédure en changeant la position desparamètres (il faut spécifier le nom du paramètre)

125/185

Passage de paramè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 paramè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 é d u r e PL/SQL t e r m i n é e avec s u c c è 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 procédures et les fonctions stockées

Sont des blocs PL/SQL qui possèdent un nom

Consistent à ranger le block PL/SQL compilé dans la base dedonnées (CREATE)

Peuvent être réutilisées sans être recompilées (EXECUTE)

Peuvent être appelées de n’importe bloc PL/SQL

Peuvent être regroupées dans un package

128/185

Les procédures stockéesSyntaxe

CREATE [ OR REPLACE ] PROCEDURE [(P1 , . . . , Pn ) ] I S[ D é 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 procédure est correcteOu

Procedure Created with compilation errors : Corrigerles erreurs → SHOW ERRORS;

129/185

Les procédures stocké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 procédures stockéesSyntaxe

La procédure stockée est appelée par les applications soit :

En utilisant son nom dans un bloc PL/SQL (autre procédure)Par execute dans SQL*Plus

Dans un bloc PL/SQL :

DECLAREBEGIN

[ , . . . , ] ;END;

Sous SQL*PLUS :

EXECUTE [ , . . . , ] ;

131/185

Appel des procédures stockéesExemple

ACCEPT VRefPro . . .ACCEPT VPriUni . . .

DECLAREVErr NUMBER;

BEGINAjoutProd (&VRefPro , . . . , & VPriUni , VErr ) ;IF VErr=0 THEN

DBMS OUTPUT. PUT LINE ( ’ O p é 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 stockéesSyntaxe

CREATE [ OR REPLACE ] FUNCTION [(P1 , . . . , Pn ) ]RETURN Type I S[ D é 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 stocké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 stockéesSyntaxe

La fonction stockée est appelé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 stocké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́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 stockéesExemple

SQL> VARIABLE VNbSQL> EXECUTE : VNb:=NbEmp(&VDep , VErr ) ;P r o c é d u r e PL/SQL t e r m i n é e avec s u c c è s .SQL> PRINT VNb

VNB−−−−−−−−−−

300

137/185

Suppression des procédures et des fonctions stockéesSyntaxe

Comme tout objet manipulé par Oracle, les procédures et lesfonctions peuvent être supprimées si nécessaire

Cette suppression est assurée par la commande suivante :

DROP PROCEDURE nomprocedure ;DROP FUNCTION nomfonct ion ;

138/185

Suppression des procédures et des fonctions stocké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 procédures et les fonctions stocké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 :procédures, fonctions, curseurs, variables, ...

Consiste en deux parties :

Spécification (déclaration)Corps (implémentation)

Ne peut pas être appelé, ni paramétré ni imbriqué

Permet à Oracle de lire plusieurs objets à la fois en mémoire

141/185

Développement des packages

Sauvegarder l’énoncé de CREATE PACKAGE dans deuxfichiers différents (ancienne/dernière version) pour faciliter deéventuelles modifications

Le corps du package ne peut pas être compilé s’il n’est pasdéclaré (spécifié)

Restreindre les privilèges pour les procédures à une personnedonnée au lieu de lui donner tous les droits sur toutes lesprocédures

142/185

La spécification du packageSyntaxe

Contient la déclaration des curseurs, variables, types, procédures,fonctions et exceptions

CREATE [OR REPLACE ] PACKAGE I S [ D é c l a r a t i o n des v a r i a b l e s e t Types ]

[ D é c l a r a t i o n des c u r s e u r s ][ D é c l a r a t i o n des p r o c é d u r e s e t f o n c t i o n s ][ D é c l a r a t i o n des e x c e p t i o n s ]

END[ ] ;/

143/185

La spé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 implémente les procédures et fonctions déclarées dans laspécification

Create [Or R e p l a c e ] Package Body I s[ I m p l é m e n t a t i o n p r o c é d u r e s | f o n c t i o n s ]

End [ ] ;/

145/185


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


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 procédures / fonctions du packageSyntaxe

Les procédures et les fonctions définies dans un package sontappelées de la façon suivante :

.[( Paramèt res ) ] ;

Var := .[( Paramèt res ) ] ;

148/185

Appels des procé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 é ’ ) ;Else

DBMS Output . P u t L i n e ( ’ E r r e u r ’ ) ;End I f ;

End ;/

149/185

Packages : Exemples

Cré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 é é 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 ê t r e d é c l a r é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 é c i f i c a t i o n

SQL>

151/185

Les triggers (Déclencheurs)

Un trigger est un programme PL/SQL qui s’exécuteautomatiquement avant ou après une opération LMD(Insert, Update, Delete)

Contrairement aux procédures, un trigger est déclenchéautomatiquement suite à un ordre LMD

152/185

Evénement-Condition-Action

Un trigger est activé par un événement

Insertion, suppression ou modification sur une table

Si le trigger est activé, une condition est évaluée

Prédicat qui doit retourner vrai

Si la condition est vraie, l’action est exécutée

Insertion, suppression ou modification de la base de données

153/185

Composants du trigger

À quel moment se déclenche le trigger ?

BEFORE : le code dans le corps du trigger s’exécute avant lesévènements de déclenchement LMD

AFTER : le code dans le corps du trigger s’exécute avant lesévènements de déclenchement LMD

154/185


Les évènements du déclenchement :

Quelles sont les opérations LMD qui causent l’exécution dutrigger ?

INSERT

UPDATE

DELETE

La combinaison des ces opérations

155/185


Le corps du trigger est dé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

Cré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 désactiver un Trigger:

Alter Trigger [ Enab le | D i s a b l e ] ;

Supprimer un Trigger:

Drop Trigger ;

Dé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 gérer l’ancienne et la nouvellevaleurs manipulé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 à jour la table classe suite à une insertion d’unnouvel é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 prédicats inserting, updating et deleting

Inserting:

True: Le trigger est déclenché suite à une insertionFalse: Sinon

Updating:

True: le trigger est déclenché suite à une mise à jourFalse: sinon

Deleting:

True: le trigger est déclenché suite à une suppressionFalse: sinon

162/185

Les pré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

SéquencesSyntaxe

Auto-incrémentation d’une colonne (évite les doublons)

Dé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 séquencePseudo-colonne NEXTVAL : Incrémentation de la séquence etretourne la nouvelle valeur

164/185

Sé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 requête SQL avant del’exécuter

Possibilité de création d’un code générique et réutilisable(sinon simple paramètrage de valeur de remplacement de laclause where)

Execute immediate chaine_de_caractères ;

chaine_de_caractères est une commande sql donné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 unité logique de travail, un ensembled’instructions de mise à jour des données que SQL traité commeune seule entité :

soit toutes les instructions sont exécutées ensemble,

soit aucune ne l’est.

Exemple :si une somme d’argent doit transiter d’un compte à un autre, lesdeux opérations (crédit et débit) de mise à jour doiventabsolument s’effectuer toutes les deux (ou ne pas s’effectuer dutout) pour que la cohérence des données soit respectée.

168/185


Le mécanisme des transactions est nécessaire parce que diversévénements peuvent interrompre l’exécution d’une séquenced’instructions :

défaillance du système,

panne de courant, micro-coupure,

le désir de revenir en arrière et abandonner les modificationsfaites jusqu’alors.

169/185


Dans Oracle, le début d’une transaction est implicitementdéfini par le début de la session, ou par la dernière instructionCOMMIT ou ROLLBACK.

Les instructions du DDL et du DCL exécutentautomatiquement des COMMIT.

Les modifications (INSERT, DELETE, UPDATE) d’une table nesont rendues permanentes que si l’on quitte la session ou sil’on exécute la commande COMMIT.

170/185


Tant que les changements à la base n’ont pas eu lieu de façonpermanente :

l’utilisateur peut voir ces changements par des ordres SELECT,

les autres utilisateurs ne voient pas les modifications,

l’utilisateur peut défaire les modifications par l’instructionROLLBACK.

Un ROLLBACK automatique a lieu en cas de panne du système

171/185

Contrôle de transactionsCommandes COMMIT et ROLLBACK

Lancer une transaction avec la première commande du LMD à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

Contrô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’état de la table à la fin de l’exécution du bloc ?

174/185

Exemple de transactions en parallèle

CREATE TABLE T ( A NUMBER,

B NUMBER, PRIMARY KEY (A));

INSERT INTO T VALUES ( 1, 1);


SELECT * FROM T ;

Le résultat obtenu est :

A B

-------- ---------

1 1

2 2

COMMIT ;

Validation effectuée.

175/185

Exemple de transactions en parallèleINSERT INTO T VALUES ( 3, 3);


SELECT * FROM T ;


A B

--------- ---------

1 1

2 2

3 3

4 4

ROLLBACK ;

Annulation (ROLLBACK) effectuée.

SELECT * FROM T ;


A B

--------- ---------

1 1

2 2

176/185



SAVEPOINT A ;

1 ligne créée.

Point de sauvegarde (SAVEPOINT) créé.


1 ligne créée.

SELECT * FROM T ;


A B

--------- ---------

1 1

2 2

3 3

4 4

177/185


ROLLBACK TO SAVEPOINT A;

Annulation (ROLLBACK) effectuée.

SELECT * FROM T ;


A B

--------- ---------

1 1

2 2

3 3

COMMIT;

178/185

Blocage des tuples et des tables

Transaction T1

SELECT * FROM T ;


A B

--------- ---------

1 1

2 2

3 3

Transaction T2

SELECT * FROM T ;


A B

--------- ---------

1 1

2 2

3 3

179/185


Transaction T1

update t set b = b - 2 ;

1 ligne mise à 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


Transaction T1

COMMIT;


Transaction T2

1 ligne mise à jour.

select * from t;

A B

--------- ---------

1 -3

2 -2

3 -1

COMMIT;


181/185


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


Transaction T1

lock table t IN EXCLUSIVE MODE;

Table(s) verrouillée(s).

Mises à jour

Mises à jour

Commit ;

Transaction T2

En attente

En attente

lock table t IN EXCLUSIVE MODE;

etc...

183/185


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 partagé 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

New Bases de Données Avancées PL/SQL - LIMSI · 2019. 2. 19. · Types de donn ees scalaires...

Documents

Transcript of New Bases de Données Avancées PL/SQL - LIMSI · 2019. 2. 19. · Types de donn ees scalaires...