bases de données avancées pl/sql - perso.limsi.fr · oracle int egration du langage sql en lui...

Bases de Donnees AvanceesPL/SQL

Thierry Hamon

Bureau H202Institut Galilee - Universite Paris 13

&LIMSI-CNRS

[email protected]

https://perso.limsi.fr/hamon/Teaching/P13/BDA-INFO2-2016-2017/

INFO2 – BDA

1/170

[email protected]

https://perso.limsi.fr/hamon/Teaching/P13/BDA-INFO2-2016-2017/

PL/SQL

Introduction

Communication avec un SGBD utilisant SQL

2/170

PL/SQL

Introduction

Solution complete Oracle

3/170

PL/SQL

Introduction

Clauses SQL

SELECT Interrogation des donnees

INSERTUPDATE Langage de Manipulation de Donnees (LMD)DELETE

CREATEALTERDROP Langage de Definition de Donnees (LDD)RENAMETRUNCATE

GRANT Langage de Controle de Donnees (LCD)REVOKE

COMMITROLLBACK Controle de transactionSAVEPOINT

4/170

PL/SQL

Introduction

PL/SQLProcedural Language for Structured Query Language

Langage fournissant une interface procedurale au SGBDOracle

Integration du langage SQL en lui apportant une dimensionprocedurale

Realisation de traitements algorithmiques (ce que ne permetpas SQL)

Mise a disposition de la plupart des mecanismes classiques deprogrammation des langages hotes tels que C, COBOL,PASCAL, C++, JAVA ...

5/170

PL/SQL

Introduction

Environnement PL/SQL

6/170

PL/SQL

Introduction

Avantages de PL/SQL

PL/SQL complement de SQL (qui n’est pas procedural)Mecanismes offerts par PL/SQL :

Structures iteratives : WHILE *** LOOP, FOR *** LOOP,LOOP ***

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

Declaration des curseurs et des tableaux

Declaration de variables

Affectation de valeurs aux variables

Branchements : GOTO, EXIT

Exceptions : EXCEPTION

7/170

PL/SQL

Introduction

Avantages de PL/SQLIntegration

Meilleure coherence du code avec les donnees

Utilisation de librairies standards predefinies (bibliothequespartagees)

8/170

PL/SQL

Introduction

Avantages de PL/SQLBlocs SQL

Traitement par/de blocs SQL dans un enonce PL/SQL(optimisation des transactions reseaux)

9/170

PL/SQL

Introduction

Avantages de PL/SQLExecution de programmes modulaires

Traitements complexes (cas particuliers, erreurs)

Traitements des exceptions

...

10/170

PL/SQL

Introduction

Avantages de PL/SQLResume

Langage portable

Utilisation de variable de stockage

Utilisation de type simple ou de type structuredynamiquement (%TYPE, %ROWTYPE, etc.)

Utilisation des structures de controle des langages proceduraux

Gestion et manipulation des erreurs

Creation d’ordre SQL dynamique

11/170

PL/SQL

Structure de blocs PL/SQL

Utilisation de PL/SQL

3 formes :

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

Fichier de commandes PL/SQL

Programme stocke : procedure, fonction, package ou trigger

12/170

PL/SQL


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/170

PL/SQL


Type de blocs

Bloc anonyme

Procedure

Fonction

14/170

PL/SQL


Type de blocsBloc anonyme

Structure classique (1 a 3 sections)

Un bloc ne peut etre 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/170

PL/SQL


Type de blocsBloc anonyme – Exemple 1

DECLARE x INTEGER ;BEGINx := 1 ;END;

16/170

PL/SQL


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/170

PL/SQL


Type de blocsProcedure

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/170

PL/SQL


Type de blocsProcedure – 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 qui

cree la procedure PL/SQL

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

19/170

PL/SQL


Type de blocsProcedure

Remarques :

Execution (auto) de la procedure :

SQL> EXECUTE proc_exemple

Pas d’execution de procedure (ou d’instructions) en fin detransaction (COMMIT, ROLLBACK, Ordre DDL)Decision d’enregistrement ou d’annulation de la transaction encours : a realiser par le programme appelant la procedure

20/170

PL/SQL


Type de blocsFonction

Fonction : procedure 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/170

PL/SQL


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/170

PL/SQL


Type de blocsFonction

Remarques :

Utilisation de fonction au sein d’une requete SQLExemple :SQL> SELECT solde(1000) FROM dual ;

Solde(1000)

-----------

12024,50

Appel d’une fonction comme une procedure provoque uneerreurExemple : 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/170

PL/SQL


Types de variables

Variables locales

ConstantesCompositesReferences

Variables de l’environnement exterieur a PL/SQL

Attachees (Bind)Hotes (Host)

24/170

PL/SQL


Declaration 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/170

PL/SQL


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 employes

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

Fixer le nom d’un employe a ’Clement’

v nom := ’ Cl ement ’ ;

26/170

PL/SQL


Types de donnees scalairesTypes de base – chaınes de caracteres

VARCHAR2(n)/NVARCHAR2(n) : Chaıne de n caracteres(n < 4000octets) – longueur variable(prefixe N : prise en compte de la localisation – NLS/NationalLanguage Support)

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

LONG/RAW/LONG RAW : Chaıne de caractere ou de donneesbinaires (raw) de longueur variable ( < 2000octets)

27/170

PL/SQL


Types de donnees scalairesTypes de base – nombres et date

NUMBER (n , m) : Reel avec n chiffres significatifs et mdecimalsINTERGER/FLOAT : Nombre entier/reel signeBINARY_INTEGER : Nombre entier signe sur 32 bits (utilisationde la librairie mathematique)DATE : date entre le 1 janvier -4712 et le 21 decembre 9999BOOLEAN : booleenNB : pas d’equivalent en SQL.Il n’est donc pas possible

de stocker un booleen dans une tablede selectionner un element de table dans un variable booleenned’utiliser un booleen dans une requete SQL ou une fonctionPL/SQL appelee dans une requete SQL (parametre en entreeou valeur de retour)

28/170

PL/SQL


Declaration 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/170

PL/SQL


Attribut %TYPE

Definition : declaration d’une variable associee a

Une colonne d’une table dans la BD

Une variable precedemment definie

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/170

PL/SQL


Types de donnees composites

Types :

Les tables PL/SQL

Les records PL/SQL

(voir plus loin)

31/170

PL/SQL

Variables attachees

Variables attachees/Bind variables

Variables de substitution dans les requetes SQLVariable referencant des variables non-PL/SQL

Economie d’analyse de la requete, donc gain de temps pourOracle→ Mecanisme considere par certains comme le point cle deperformance d’Oracle

32/170

PL/SQL

Variables attachees

Variables attachees/Bind variablesDeclaration

Declaration, en SQL*Plus, d’une variable attachee : 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/170

PL/SQL

Variables attachees

Variables attachees/Bind variablesReference 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 reference aux variables non-PL/SQL comme des variableshotePreceder la reference par un ’ : ’

34/170

PL/SQL

Variables attachees

Variables attachees/Bind variablesExemple d’utilisation – optimisation

Exemple de requete SELECT generees 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 requete,

Verification si la requete a deja ete ete soumise

Si oui, recuperation du plan d’execution de la requete, etexecution de la requete

35/170

PL/SQL

Variables attachees

Variables attachees/Bind variablesExemple d’utilisation – optimisation

Si non,

analyse syntaxique de la requetedefinition des differentes possibilites d’executiondefinition du plan d’execution optimal

→ Processus couteux en temps CPU, alors que seule la valeurde id change !

Solution : reutiliser le plan d’execution existant→ Necessite d’utiliser des variables attachees :

Substitution de la valeur par la variable attacheEnvoi de la meme requete pour toutes les valeurs de id

Exemple :

SELECT fname , lname , pcode FROM c u s t WHERE i d = : c u s t n o ;

36/170

PL/SQL

Bloc PL/SQL

Bloc PL/SQLsyntaxe et directives

Les instructions peuvent etre ecrites sur plusieurs lignes.Les unites lexicales peuvent etre separees par des espaces :

DelimiteursIdentificateursLitteraux (ou constantes)Commentaires

37/170

PL/SQL

Bloc PL/SQL

Bloc PL/SQLsyntaxe et directives

Les litteraux

Les dates et les chaınes de caracteres delimitees 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 etre des valeurs simples ou desexpressions

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

38/170

PL/SQL

Bloc PL/SQL

Commentaires dans le code

Preceder un commentaire ecrit sur une seule ligne par ’--’.

Placer un commentaire ecrit 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/170

PL/SQL

Bloc PL/SQL

Les Fonctions SQL en PL/SQL

Les fonctions sur les nombres

Les fonctions sur les chaınes de caracteres

Les fonctions de conversion de type de donnees

Les fonctions de dates

40/170

PL/SQL

Bloc PL/SQL

Exemples de fonctions SQL en PL/SQL

Exemples :

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/170

PL/SQL

Bloc PL/SQL

Blocs imbriques 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/170

PL/SQL

Operateurs

Operateurs dans PL/SQL

Identique a SQL

LogiqueArithmetiqueConcatenationParentheses pour controler l’ordre des operations

Operateur supplementaire : Operateur d’exponentiation **

43/170

PL/SQL

Operateurs

Utilisation des variables liees

Pour referencer une variable en PL/SQL, on doit prefixer 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/170

PL/SQL

Instructions

Instructions SQL dans PL/SQL

Extraire une ligne de donnees a partir de la BD par lacommande SELECT. Un seul ensemble de valeurs peut etreretourne

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

Controler des transactions avec les commandes COMMIT,ROLLBACK et SAVEPOINT

Determiner les resultats du LMD avec des curseurs implicites(voir plus loin)

45/170

PL/SQL

Instructions

Instruction SELECT dans PL/SQL

Recuperer une donnee 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/170

PL/SQL

Instructions


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/170

PL/SQL

Instructions


Retourne la somme des salaires de tous les employes d’undepartement 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/170

PL/SQL

Manipulation de donnees

Manipulation de donnees en PL/SQL

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

INSERT

UPDATE

DELETE

49/170

PL/SQL


Insertion de donnees

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 ement ’ , ’ D i r e c t e u r ’ , 1 0 ) ;

END ;

50/170

PL/SQL


Mise a jour de donnees

Augmenter le salaire de tous les employes 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/170

PL/SQL


Suppression de donnees

Suppression des lignes appartenant au departement 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/170

PL/SQL

Structure de controle

Structure de controle 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/170

PL/SQL


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/170

PL/SQL


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/170

PL/SQL


IF simple

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

Exemple

. . .IF v nom = ’ Cl ement ’ 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/170

PL/SQL


IF-THEN-ELSE

Si le nom de l’employe est ’CLEMENT’, lui attribuer le poste’Enseignant’, le departement n◦ 102 et une commission de 25% sur son salaire actuel, sinon afficher le message ’Employeinexistant’

Exemple

. . .IF v nom = ’ Cl ement ’ 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/170

PL/SQL


IF-THEN-ELSIF

Pour une valeur donnee en entree, retourner une valeurcalculee

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/170

PL/SQL


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 <= 50 then ’ 1−50 rownum ’ | | rownum

when rownum <= 100 then ’ 51−100 rownum ’ | | rownume l s e ’ p l u s de 100 ’ endfrom b c r i . t b g s t f i n a n c e m e n t

where rownum <= 100

Cette requete permet de retourner un contenu dans une colonne enfonction d’une condition

59/170

PL/SQL


Boucle de baseSyntaxe

LOOP −− d e l i m i t e u renonc e 1 ; −− enonc e. . . .EXIT [ WHEN c o n d i t i o n ] ; −− enonc e EXIT

END LOOP;

When : condition est une variable booleenne ou expression (TRUE,FALSE, ou NULL);

60/170

PL/SQL


Boucle de baseExemple

Inserer 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/170

PL/SQL


Boucle FORSyntaxe

FOR i n d i c e IN [ REVERSE ]b o r n e i n f . . Borne sup LOOP

enonc e 1 ;enonc e 2 ;. . . . .

END LOOP;

Utiliser la boucle FOR pour raccourcir le test d’un nombred’iterations

Ne pas declarer l’indice, il est declare implicitement

62/170

PL/SQL


Boucle FORexemple

Inserer Nb articles indexes de 1 a Nb avec la date du systeme 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/170

PL/SQL


Boucle WHILESyntaxe

WHILE c o n d i t i o n LOOPenonc e 1 ;enonc e 2 ;. . . . .

END LOOP;

La condition est evaluee au debut de chaque iteration

Utiliser la boucle WHILE pour repeter des enonces tant que lacondition est vraie

64/170

PL/SQL


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 <= &p i t e m t o t LOOP

INSERT INTO a r t i c l e ( Artno , ADate )VALUES ( v compteur , v Date ) ;

v compteur := v compteur + 1 ;END LOOP;

65/170

PL/SQL


Boucles imbriquees et Labels

Imbriquer les boucles a niveaux multiples

Utiliser les labels pour distinguer les blocs et les boucles

Quitter la boucle exterieure avec un EXIT referencant le label

66/170

PL/SQL


Boucles imbriquees et Labels. . . .BEGIN

<< b o u c e x t>>LOOP

v compteur := v compteur + 1 ;EXIT WHEN v compteur > 1 0 ;

<<b o u c i n t>>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 bou 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 bouc 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/170

PL/SQL

Types de donnees complexes


Types :

RECORDSTABLES

Contiennent des composants internes

Sont reutilisables

68/170

PL/SQL


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

Tres utiles pour rechercher des lignes de donnees dans unetable et les traiter

69/170

PL/SQL


Creation d’un record PL/SQL

Syntaxe

TYPE <Nom Enreg> 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/170

PL/SQL


Attribut %ROWTYPE

Declaration d’une variable associee a une collection decolonnes dans une table ou une vue de la BD

le nom de la table doit preceder %ROWTYPE

Les champs dans le record prennent leurs noms et types descolonnes de la table ou la vue en question

71/170

PL/SQL


Attribut %ROWTYPEExemples

Declarer une variable pour stocker les memes informationsconcernant une personne telles qu’elles sont stockees dans latable PERS

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

Declarer une variable pour stocker les memes informationsconcernant un article telles qu’elles sont stockees dans la tableART

a r t i c l e Art%ROWTYPE;

72/170

PL/SQL

Tables PL/SQL

Tables PL/SQL

Le type de donnees complexe TABLE offre au developpeur unmecanisme pour traiter les tableaux

Il se compose de deux colonnes :

Une cle primaire de type BINARY_INTEGER

Une colonne de type scalaire ou record

73/170

PL/SQL

Tables PL/SQL

Creation d’une table PL/SQL

Syntaxe

TYPE <Nom table> I S TABLE OF <type>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/170

PL/SQL

Tables PL/SQL

Structure d’une table PL/SQLen memoire

Cle primaire Colonne

1 Ritchie

2 Marvin

3 Dennis

. . . . . .

. . . . . .

BINARY INTEGER Scalaire

75/170

PL/SQL

Tables PL/SQL

Creer une table PL/SQL

SQL> DECLARE2 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 donnee t r o u v e eORA−06512: a l i g n e 10

76/170

PL/SQL

Tables PL/SQL

TABLE de RECORDS en PL/SQL

Definit une table dont la deuxieme colonne est unenregistrement au lieu d’un scalaire

Pour definir la deuxieme colonne :

Soit en utilisant l’attribut %ROWTYPESoit en utilisant un record deja defini

77/170

PL/SQL

Tables PL/SQL

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/170

PL/SQL

Tables PL/SQL

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/170

PL/SQL

Curseurs

Les curseurs dans SQL

Un curseur est une zone de travail privee de SQL (zonetampon)

Il y a deux types de curseurs:

Curseurs implicitesCurseurs explicites

Oracle utilise les curseurs implicites pour analyser et executerles enonces de SQL

Les curseurs explicites sont declares explicitement pas leprogrammeur

80/170

PL/SQL

Curseurs

Les attributs des curseurs SQL

Les attributs des curseurs SQL permettent de tester les resultatsdes enonces SQL

SQL%ROWCOUNT Nombre de lignes affecte par l’enonceSQL le plus recent (renvoie un entier).

SQL%FOUND attribut booleen qui prend la valeur TRUEsi l’enonce SQL le plus recent affecte uneou plusieurs lignes.

SQL%NOTFOUND attribut booleen qui prend la valeur TRUEsi l’enonce SQL le plus recent n’affecteaucune ligne.

SQL%ISOPEN Prend toujours la valeur FALSE parce quePL/SQL ferment les curseurs implicitesimmediatement apres leur execution.

81/170

PL/SQL

Curseurs

Les attributs des curseurs SQL

Supprimer de la table ITEM des lignes ayant un ordre specifie.Afficher le nombre de lignes supprimees.

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/170

PL/SQL

Curseurs

Les curseurs

Chaque enonce SQL execute par Oracle a son propre curseur :

Curseurs implicites : declares pour tout enonce SELECT duLMD ou PL/SQL

Curseurs explicites : declares et nommes par le programmeur

83/170

PL/SQL

Curseurs

Controle des curseurs explicites

84/170

PL/SQL

Curseurs

Controle des curseurs explicites

85/170

PL/SQL

Curseurs

Declaration des curseursSyntaxe

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

Ne pas inclure la clause INTO dans la declaration du curseur

Si le traitement des lignes doit etre fait dans un ordrespecifique, on utilise la clause ORDER BY dans la requete

86/170

PL/SQL

Curseurs

Declaration des curseursExemple

DECLARECURSOR C1 I S

SELECT RefArt , NomArt , QteArtFROM A r t i c l eWHERE QteArt < 5 0 0 ;

87/170

PL/SQL

Curseurs

Ouverture du curseurSyntaxe

OPEN n o m d u c u r s e u r ;

Ouvrir le curseur pour executer la requete et identifierl’ensemble actif

Si la requete ne renvoie aucune ligne, aucune exception n’auralieu

Utiliser les attributs des curseurs pour tester le resultat duFETCH

88/170

PL/SQL

Curseurs

Recherche des donnees 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/170

PL/SQL

Curseurs

Recherche des donnees 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 ees r e c h e r c h e e s }. . .

END LOOP;. . .

90/170

PL/SQL

Curseurs

Fermeture du curseurSyntaxe

CLOSE n o m d u c u r s e u r ;

Fermer le curseur apres la fin du traitement des lignes

Rouvrir le curseur si necessaire

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

91/170

PL/SQL

Curseurs

Les attributs du curseur explicite

Obtenir les informations d’etat du curseur (CUR EXP)

Attribut Type Description

CUR EXP%ISOPEN BOOLEAN Prend la valeur TRUEsi le curseur est ouvert

CUR EXP%NOTFOUND BOOLEAN Prend la valeur TRUEsi le FETCH le plus recentne retourne aucune ligne

CUR EXP%FOUND BOOLEAN Prend la valeur TRUEsi le FETCH le plus recent

retourne une ligne

CUR EXP%ROWCOUNT NUMBER Retourne le nombre de lignestraitees jusqu’ici

92/170

PL/SQL

Curseurs

Controle des recherches multiples

Traitement de plusieurs lignes d’un curseurs en utilisant uneboucle

Rechercher une seule ligne a chaque iteration

Utiliser les attributs du curseur explicite pour tester le succesde chaque FETCH

93/170

PL/SQL

Curseurs

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/170

PL/SQL

Curseurs

Attributs %FOUND, %NOTFOUND et %ROWCOUNT

Utiliser l’attribut %ROWCOUNT pour fournir le nombre exact deslignes traitees

Utiliser les attributs %FOUND et %NOT FOUND pour formuler letest d’arret de la boucle

95/170

PL/SQL

Curseurs

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/170

PL/SQL

Curseurs

Obtenir les informations d’etat 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/170

PL/SQL

Curseurs

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/170

PL/SQL

Curseurs

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 facon implicite

Ne pas declarer le record, il est declare implicitement

99/170

PL/SQL

Curseurs

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/170

PL/SQL

Exceptions

Manipulation des exceptions en PL/SQL

Le traitement des exceptions PL/SQL : mecanisme pourmanipuler les erreurs rencontrees lors de l’execution

Possibilite de continuer l’execution si l’erreur n’est passuffisamment importante pour produire la terminaison de laprocedure

Decision de continuer une procedure apres erreur : decisionque le developpeur doit faire en fonction des erreurs possibles

101/170

PL/SQL

Exceptions

Types des exceptions

Declenchees implicitement

Exceptions Oracle predefiniesExceptions Oracle Non-predefinies

Declenchees explicitement

Exceptions definies par l’utilisateur

102/170

PL/SQL

Exceptions

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

enonc e1 ;enonc e2 ;. . .

[WHEN e x c e p t i o n 2 [OR e x c e p t i o n 4 . . . ] THENenonc e3 ;enonc e4 ;. . . ]

[WHEN OTHERS THENenonc e5 ;enonc e6 ;. . . ]

103/170

PL/SQL

Exceptions

Capture des exceptions predefinies

Faire reference au nom dans la partie traitement desexceptions

Quelques exceptions predefinies :

NO DATA FOUND

TOO MANY ROWS

INVALID CURSOR

ZERO DIVIDE

DUP VAL ON INDEX

104/170

PL/SQL

Exceptions

Exceptions predefiniesExemple

BEGIN. . .

EXCEPTIONWHEN NO DATA FOUND THENenonc e1 ; enonc e2 ;DBMS OUTPUT. PUT LINE (TO CHAR ( etudno ) | |

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

enonc e3 ; enonc e4 ;DBMS OUTPUT. PUT LINE ( ’ Donnees i n v a l i d e s ’ ) ;

WHEN OTHERS THENenonc e5 ; enonc e6 ;

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

105/170

PL/SQL

Exceptions

Capture des exceptions definies par l’utilisateur

106/170

PL/SQL

Exceptions

Capture des exceptions non-predefiniesExemple

Capture de l’erreur n◦ 2291 (violation de la contrainte integrite).

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/170

PL/SQL

Exceptions

Capture des exceptions definies par l’utilisateur

108/170

PL/SQL

Exceptions

Exceptions definies 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/170

PL/SQL

Exceptions

Fonctions pour capturer les exceptions

SQLCODE : Retourne la valeur numerique du code de l’erreur

SQLERRM : Retourne le message associe au numero de l’erreur

110/170

PL/SQL

Exceptions

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/170

PL/SQL

Sous-programmes

Les sous-programmes

Un sous programme est une sequence d’instruction PL/SQLqui possede un nomOn distingue deux types de sous programmes :

Les proceduresLes fonctions

112/170

PL/SQL

Sous-programmes

Les sous-programmes

Une procedure : sous programme qui ne retourne des resultatsseulement dans ses parametres

Une fonction : sous programme qui retourne des resultatsdans :

Le nom de la fonctionLes parametres de la fonction

113/170

PL/SQL

Sous-programmes

Les proceduresSyntaxe

DECLARE. . .

PROCEDURE <Nom Proc >[(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/170

PL/SQL

Sous-programmes

Les proceduresSyntaxe des parametres

P1,...,Pn suivent la syntaxe :

<Nom Arg> [ IN | OUT | IN OUT] <Type>

Ou :

IN : Parametre d’entree

OUT : Parametre de sortie

IN OUT : Parametre d’entree/Sortie

Par defaut le parametre est IN

115/170

PL/SQL

Sous-programmes

Les proceduresExemple

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/170

PL/SQL

Sous-programmes


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/170

PL/SQL

Sous-programmes


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/170

PL/SQL

Sous-programmes

Les fonctionsSyntaxe

DECLARE[ D e c l a r a t i o n s g l o b a l e s ]FUNCTION <Nom fonc >[(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/170

PL/SQL

Sous-programmes

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/170

PL/SQL

Sous-programmes

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/170

PL/SQL

Sous-programmes

Le parametre IN OUT

Parametre jouant le role des deux parametre IN et OUT

Obligatoire de le specifier

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/170

PL/SQL

Sous-programmes

Appel de affnom de SQL*Plus

Definition d’une variable liee

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/170

PL/SQL

Sous-programmes

Appel de affnom de SQL*Plus

Execution de la procedure avec un parametre 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/170

PL/SQL

Sous-programmes

Passage de parametres

Il y a plusieurs facons de passage de parametres :

Appel de la procedure en specifiant les parametres

Appel de la procedure sans parametre si ce dernier est unparametre d’entree initialise

Appel de la procedure en changeant la position desparametres (il faut specifier le nom du parametre)

125/170

PL/SQL

Sous-programmes

Passage de parametresExemple

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/170

PL/SQL

Sous-programmes

Passage de parametresExemple

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 inconnu inconnu8 inconnu l y o n

SQL>

127/170

PL/SQL

Sous-programmes

Les procedures et les fonctions stockees

Sont des blocs PL/SQL qui possedent un nom

Consistent a ranger le block PL/SQL compile dans la base dedonnees (CREATE)

Peuvent etre reutilisees sans etre recompilees (EXECUTE)

Peuvent etre appelees de n’importe bloc PL/SQL

Peuvent etre regroupees dans un package

128/170

PL/SQL

Sous-programmes

Les procedures stockeesSyntaxe

CREATE [ OR REPLACE ] PROCEDURE <Nom Proc >[(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 procedure est correcteOu

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

129/170

PL/SQL

Sous-programmes

Les procedures stockeesExemple

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/170

PL/SQL

Sous-programmes

Appel des procedures stockeesSyntaxe

La procedure stockee est appelee par les applications soit :

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

Dans un bloc PL/SQL :

DECLAREBEGIN

<Nom Procedure>[<P1> , . . . , <Pn> ] ;END;

Sous SQL*PLUS :

EXECUTE <Nom Procedure>[<P1> , . . . , <Pn> ] ;

131/170

PL/SQL

Sous-programmes

Appel des procedures stockeesExemple

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/170

PL/SQL

Sous-programmes

Les fonctions stockeesSyntaxe

CREATE [ OR REPLACE ] FUNCTION <Nom Fonc>[(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 correcteOu

function Created with compilation errors :Corriger les erreurs → SHOW ERRORS;

133/170

PL/SQL

Sous-programmes

Les fonctions stockeesExemple

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/170

PL/SQL

Sous-programmes

Appel des fonctions stockeesSyntaxe

La fonction stockee est appelee 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

<var> := <Nom fonct ion >[<P1> , . . . , <Pn>]END;

Sous SQL*PLUS :

EXECUTE :< var> := <Nom fonct ion> [<P1> , . . . , <Pn>]

135/170

PL/SQL

Sous-programmes

Appel des fonctions stockeesExemple

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 ee 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/170

PL/SQL

Sous-programmes

Appel des fonctions stockeesExemple

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/170

PL/SQL

Sous-programmes

Suppression des procedures et des fonctions stockeesSyntaxe

Comme tout objet manipule par Oracle, les procedures et lesfonctions peuvent etre supprimees si necessaire

Cette suppression est assuree par la commande suivante :

DROP PROCEDURE nomprocedure ;DROP FUNCTION nomfonct ion ;

138/170

PL/SQL

Sous-programmes

Suppression des procedures et des fonctions stockeesExemple

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/170

PL/SQL

Sous-programmes

Les procedures et les fonctions stockees

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/170

PL/SQL

Packages

Les packages

Un objet PL/SQL qui stocke d’autres types d’objet :procedures, fonctions, curseurs, variables, ...

Consiste en deux parties :

Specification (declaration)Corps (implementation)

Ne peut pas etre appele, ni parametre ni imbrique

Permet a Oracle de lire plusieurs objets a la fois en memoire

141/170

PL/SQL

Packages

Developpement des packages

Sauvegarder l’enonce de CREATE PACKAGE dans deuxfichiers differents (ancienne/derniere version) pour faciliter deeventuelles modifications

Le corps du package ne peut pas etre compile s’il n’est pasdeclare (specifie)

Restreindre les privileges pour les procedures a une personnedonnee au lieu de lui donner tout les droits sur toutes lesprocedures

142/170

PL/SQL

Packages

La specification du packageSyntaxe

Contient la declaration des curseurs, variables, types, procedures,fonctions et exceptions

CREATE [OR REPLACE ] PACKAGE <Nom 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[<Nom Package > ] ;/

143/170

PL/SQL

Packages

La specification 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/170

PL/SQL

Packages

Le corps du packageSyntaxe

On implemente les procedures et fonctions declarees dans laspecification

Create [Or R e p l a c e ] Package Body <Nom Package> 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 [<Nom Package > ] ;/

145/170

PL/SQL

Packages


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/170

PL/SQL

Packages


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/170

PL/SQL

Packages

Appels des procedures / fonctions du packageSyntaxe

Les procedures et les fonctions definies dans un package sontappelees de la facon suivante :

<NomPackage>.<NomProcedure >[( Paramet res ) ] ;

Var := <NomPackage>.<NomFonction >[( Paramet res ) ] ;

148/170

PL/SQL

Packages

Appels des procedures / 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/170

PL/SQL

Packages

Packages : Exemples

Creation 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/170

PL/SQL

Packages

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 e1/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/170

PL/SQL

Triggers

Les triggers (Declencheurs)

Un trigger est un programme PL/SQL qui s’executeautomatiquement avant ou apres une operation LMD(Insert, Update, Delete)

Contrairement aux procedures, un trigger est declencheautomatiquement suite a un ordre LMD

152/170

PL/SQL

Triggers

Evenement-Condition-Action

Un trigger est active par un evenement

Insertion, suppression ou modification sur une table

Si le trigger est active, une condition est evaluee

Predicat qui doit retourner vrai

Si la condition est vraie, l’action est executee

Insertion, suppression ou modification de la base de donnees

153/170

PL/SQL

Triggers

Composants du trigger

A quel moment se declenche le trigger ?

BEFORE : le code dans le corps du triggers s’execute avantles evenements de declenchement LMD

AFTER : le code dans le corps du triggers s’execute avant lesevenements de declenchement LMD

154/170

PL/SQL

Triggers


Les evenements du declenchement :

Quelles sont les operations LMD qui causent l’execution dutrigger ?

INSERT

UPDATE

DELETE

La combinaison des ces operations

155/170

PL/SQL

Triggers


Le corps du trigger est defini par un bloc PL/SQL anonyme

[DECLARE]BEGIN[ EXEPTION ]END;

156/170

PL/SQL

Triggers

Composants du triggerSyntaxe

Create [Or R e p l a c e ] Trigger <Nom Trigger>[ B e f o r e | A f t e r ] <O p e r a t i o n DML> On <Nom Table>[ For Each Row ] [When <C o n d i t i o n >]DeclareBeginExceptionEnd ;/

157/170

PL/SQL

Triggers

Composants du triggerExemple

Creation d’un trigger qui remplit la table statistique

(Nom Table → Nb Insert) lors d’une insertion dans la table facture

Create Or R e p l a c e Trigger S t a r t F a c t u r eA f t e r I n s e r t On F a c t u r eFor Each Row

DeclareVNbInse r t Number ;

BeginSe lect N b I n s e r t Into VNbInse 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 = VNbInse r t+1Where Nom Table= ’ F a c t u r e ’ ;

ExceptionWhen No Data Found Then

I n s e r t Into S t a t i s t i q u e Values ( 1 , ’ F a c t u r e ’ ) ;End ;/

158/170

PL/SQL

Triggers

Manipulation des triggers

Activer ou desactiver un Trigger:

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

Supprimer un Trigger:

Drop Trigger <Nom Trigger >;

Determiner les triggers de votreBD:

Select Trigger Name From U s e r T r i g g e r s

159/170

PL/SQL

Triggers

Les attributs :Old et :New

Ces deux attributs permettent de gerer l’ancienne et la nouvellevaleurs manipulees

Insert(...) ... → New

Delete ...

Where(...) ... → Old

Update ...

Set ( ...) ... → New

Where(...) ... → Old

160/170

PL/SQL

Triggers

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 etudiant

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/170

PL/SQL

Triggers

Les predicats inserting, updating et deleting

Inserting:

True: Le trigger est declenche suite a une insertionFalse: Sinon

Updating:

True: le trigger est declenche suite a une mise a jourFalse: sinon

Deleting:

True: le trigger est declenche suite a une suppressionFalse: sinon

162/170

PL/SQL

Triggers

Les predicats 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/170

PL/SQL

Sequences

SequencesSyntaxe

Auto-incrementation d’une colonne (evite les doublons)

Definition :

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 sequence

Pseudo-colonne NEXTVAL : Incrementation de la sequence etretourne la nouvelle valeur

164/170

PL/SQL

Sequences

SequencesExemple

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/170

PL/SQL

SQL dynamique

SQL dynamique

Construction dans un programme une requete SQL avant del’executer

Possibilite de creation d’un code generique et reutilisable(sinon simple parametrage de valeur de remplacement de laclause where)

Execute immediate chaine_de_caracteres ;

chaine_de_caracteres est une commande sql donnee entre’...’

166/170

PL/SQL

SQL dynamique

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/170

PL/SQL

Controle de transactions

Controle de transactionsCommandes COMMIT et ROLLBACK

Lancer une transaction avec la premiere commande du LMD ala suite d’un COMMIT ou un ROLLBACK

Utiliser le COMMIT ou le ROLLBACK de SQL pour terminer unetransaction

168/170

PL/SQL


Commande ROLLBACK

169/170

PL/SQL



Determiner le traitement des transactions pour le bloc PL/SQLsuivant

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;

170/170

bases de données avancées pl/sql - perso.limsi.fr · oracle int egration du langage sql en lui...

Documents