Introduction au PL/SQL Oracle

voire

IF /∗ c o n d i t i o n 1 ∗/ THEN

/∗ i n s t r u c t i o n s 1 ∗/

ELSIF /∗ c o n d i t i o n 2 ∗/

/∗ i n s t r u c t i o n s 2 ∗/

ELSE

END IF ;

/∗ i n s t r u c t i o n s 3 ∗/

Les conditions sont les mêmes qu’en SQL. Le switch du langage C s’implémente en PL/SQL de la façon suivante :

CASE /∗ v a r i a b l e ∗/

WHEN /∗ v a l e u r 1 ∗/ THEN

/∗ i n s t r u c t i o n s 1 ∗/

WHEN /∗ v a l e u r 2 ∗/ THEN

/∗ i n s t r u c t i o n s 2 ∗/

. . .

WHEN /∗ v a l e u r n ∗/ THEN

/∗ i n s t r u c t i o n s n ∗/

ELSE

/∗ i n s t r u c t i o n s par d é f a u t ∗/

END CASE;

1.1.6 Traitements répétitifs

LOOP ... END LOOP ; permet d’implémenter les boucles

LOOP

/∗ i n s t r u c t i o n s ∗/

END LOOP ;

L’instruction EXIT WHEN permet de quitter une boucle.

LOOP

/∗ i n s t r u c t i o n s ∗/

EXIT WHEN /∗ c o n d i t i o n ∗/ ;

END LOOP ;

La boucle FOR existe aussi en PL/SQL :

FOR /∗ v a r i a b l e ∗/ IN /∗ i n f ∗/ . . /∗ sup ∗/ LOOP

/∗ i n s t r u c t i o n s ∗/

END LOOP ;

Ainsi que la boucle WHILE :

WHILE /∗ c o n d i t i o n ∗/ LOOP

/∗ i n s t r u c t i o n s ∗/

END LOOP ;

Est-il possible, en bidouillant, d’implémenter une boucle DO ... WHILE ?

4

1.2 Tableaux et structures

1.2.1 Tableaux

Création d’un type tableau

Les types tableau doivent être définis explicitement par une déclaration de la forme

TYPE /∗ t y p e ∗/ IS VARRAY ( /∗ t a i l l e ∗/ ) OF /∗ t y p e E l e m e n t s ∗/ ;

– type est le nom du type tableau crée par cette instruction

– taille est le nombre maximal d’éléments qu’il est possible de placer dans le tableau.

– typeElements est le type des éléments qui vont être stockés dans le tableau, il peut s’agir de n’importe quel

type.

Par exemple, créons un type tableau de nombres indicé de 1 à 10, que nous appelerons numberTab

TYPE numberTab IS VARRAY ( 1 0 ) OF NUMBER;

Déclaration d’un tableau

Dorénavant, le type d’un tableau peut être utilisé au même titre que NUMBER ou VARCHAR2. Par exemple, déclarons

un tableau appelé t de type numberTab,

DECLARE

BEGIN

END;

/

TYPE numberTab IS VARRAY ( 1 0 ) OF NUMBER;

t numberTab ;

/∗ i n s t r u c t i o n s ∗/

Allocation d’un tableau

La création d’un type tableau met à disposition un constructeur du même nom que le type créé. Cette fonction

réserve de l’espace mémoire pour ce tableau et retourne l’adresse mémoire de la zone réservée, il s’agit d’une sorte de

malloc. Si, par exemple, un type tableau numtab a été crée, la fonction numtab() retourne une tableau vide.

DECLARE

BEGIN

END;

/

TYPE numberTab IS VARRAY ( 1 0 ) OF NUMBER;

t numberTab ;

t := numberTab ( ) ;

/∗ u t i l i s a t i o n du t a b l e a u ∗/

Une fois cette allocation faite, il devient presque possible d’utiliser le tableau...

Dimensionnement d’un tableau

Le tableau retourné par le constructeur est vide. Il convient ensuite de réserver de l’espace pour stocker les éléments

qu’il va contenir. On utilise pour cela la méthode EXTEND(). EXTEND s’invoque en utilisant la notation pointée. Par

exemple,

DECLARE

BEGIN

END;

/

TYPE numberTab IS VARRAY ( 1 0 ) OF NUMBER;

t numberTab ;

t := numberTab ( ) ;

t . EXTEND ( 4 ) ;

/∗ u t i l i s a t i o n du t a b l e a u ∗/

5

Dans cet exemple, t.EXTEND(4) ; permet par la suite d’utiliser les éléments du tableau t(1), t(2), t(3) et t(4).

Il n’est pas possible ”d’étendre” un tableau a une taille supérieure a celle spécifiée lors de la création du type tableau

associé.

Utilisation d’un tableau

On accede, en lecture et en écriture, au i-eme élément d’une variable tabulaire nommé T avec l’instruction T(i).

Les éléments sont indicés à partir de 1.

Effectuons, par exemple, une permutation circulaire vers la droite des éléments du tableau t.

DECLARE

BEGIN

TYPE numberTab IS VARRAY ( 1 0 ) OF NUMBER;

t numberTab ;

i number ;

k number ;

t := numberTab ( ) ;

t . EXTEND ( 1 0 ) ;

FOR i IN 1 . . 1 0 LOOP

t ( i ) := i ;

END LOOP ;

k := t ( 1 0 ) ;

FOR i in REVERSE 2 . . 1 0 LOOP

t ( i ) := t ( i − 1 ) ;

END LOOP ;

t ( 1 )

FOR i IN 1 . . 1 0 LOOP

:= k ;

DBMS_OUTPUT . PUT_LINE ( t ( i ) ) ;

END LOOP ;

END;

/

1.2.2 Structures

Un structure est un type regroupant plusieurs types. Une variable de type structuré contient plusieurs variables,

ces variables s’appellent aussi des champs.

Création d’un type structuré

On définit un type structuré de la sorte :

TYPE /∗ nomType ∗/ IS RECORD

(

/∗ l i s t e d e s champs ∗/

) ;

nomType est le nom du type structuré construit avec la syntaxe précédente. La liste suit la même syntaxe que la

liste des colonnes d’une table dans un CREATE TABLE. Par exemple, construisons le type point (dans IR2),

TYPE point IS RECORD

(

abscisse NUMBER,

ordonnee NUMBER

) ;

Notez bien que les types servant à définir un type structuré peuvent être quelconques : variables scalaires, tableaux,

structures, etc.

Déclaration d’une variable de type structuré

point est maintenant un type, il devient donc possible de créer des variables de type point, la règle est toujours la

même pour déclarer des variables en PL/SQL, par exemple

6

p point ;

permet de déclarer une variable p de type point.

Utilisation d’une variable de type structuré

Pour accéder à un champ d’une variable de type structuré, en lecture ou en écriture, on utilise la notation pointée :

v.c est le champ appelé c de la variable structuré appelée v. Par exemple,

DECLARE

BEGIN

END;

/

TYPE point IS RECORD

(

abscisse NUMBER,

ordonnee NUMBER

) ;

p point ;

p . abscisse := 1 ;

p . ordonnee := 3 ;

DBMS_OUTPUT . PUT_LINE ( ’ p . a b s c i s s e = ’

’ and p . ordonnee = ’

| | p . ordonnee ) ;

| | p . abscisse | |

Le script ci-dessous crée le type point, puis crée une variable t de type point, et enfin affecte aux champs abscisse

et ordonnee du point p les valeurs 1 et 3.

7

1.3 Utilisation du PL/SQL

Ce cours est une introduction aux interactions possibles entre la base de données et les scripts PL/SQL.

1.3.1 Affectation

On place dans une variable le résultat d’une requête en utilisant le mot-clé INTO. Les instructions

SELECT champ_1 ,

FROM . . .

. . . , champ_n INTO v_1 ,

. . . , v_n

affecte aux variables v 1, ..., v n les valeurs retournées par la requête. Par exemple

DECLARE

BEGIN

END;

/

num NUMBER;

nom VARCHAR2( 3 0 ) := ’ Poupée Batman ’

;

SELECT numprod INTO num

FROM PRODUIT

WHERE nomprod = nom ;

DBMS_OUTPUT . PUT_LINE ( ’L ’ ’ a r t i c l e

’ a pour numéro ’

nom | |

| |

’

| | num ) ;

Prêtez attention au fait que la requête doit retourner une et une une seule ligne, sinon, une erreur se produit à

l’exécution.

1.3.2 Tables et structures

Si vous ne tenez pas à vous prendre la tête pour choisir le type de chaque variable, demandez-vous ce que vous

allez mettre dedans ! Si vous tenez à y mettre une valeur qui se trouve dans une colonne d’une table, il est possible de

vous référer directement au type de cette colonne avec le type nomTable.nomColonne%type. Par exemple,

DECLARE

BEGIN

END;

/

num PRODUIT . numprod%type ;

nom PRODUIT . nomprod%type := ’ Poupée Batman ’

;

SELECT numprod INTO num

FROM PRODUIT

WHERE nomprod = nom ;

DBMS_OUTPUT . PUT_LINE ( ’L ’ ’ a r t i c l e

’ a pour numéro ’

nom | |

| |

’

| | num ) ;

Pour aller plus loin, il est même possible de déclarer une structure pour représenter une ligne d’une table, le type

porte alors le nom suivant : nomTable%rowtype.

DECLARE

BEGIN

END;

/

nom PRODUIT . nomprod%type := ’ Poupée Batman ’

ligne PRODUIT%rowtype ;

;

SELECT ∗ INTO ligne

FROM PRODUIT

WHERE nomprod = nom ;

DBMS_OUTPUT . PUT_LINE ( ’L ’ ’ a r t i c l e

ligne . nomprod | |

’

’ a pour numéro ’

| |

| | ligne . numprod ) ;

8

1.3.3 Transactions

Un des mécanismes les plus puissants des SGBD récents réside dans le système des transactions. Une transaction

est un ensemble d’opérations “atomiques”, c’est-à-dire indivisible. Nous considérerons qu’un ensemble d’opérations est

indivisible si une exécution partielle de ces instructions poserait des problèmes d’intégrité dans la base de données.

Par exemple, dans le cas d’une base de données de gestion de comptes en banque, un virement d’un compte à un autre

se fait en deux temps : créditer un compte d’une somme s, et débiter un autre de la même somme s. Si une erreur

survient pendant la deuxième opération, et que la transaction est interrompue, le virement est incomplet et le patron

va vous assassiner.

Il convient donc de disposer d’un mécanisme permettant de se protéger de ce genre de désagrément. Plutôt que

se casser la tête a tester les erreurs a chaque étape et a balancer des instructions permettant de “revenir en arriere”,

nous allons utiliser les instructions COMMIT et ROLLBACK.

Voici le squelette d’un exemple :

/∗ i n s t r u c t i o n s ∗/

IF /∗ e r r e u r ∗/ THEN

ROLLBACK;

ELSE

END;

COMMIT;

Le ROLLBACK annule toutes les modifications faites depuis le début de la transaction (donc depuis le précédent

COMMIT), COMMIT les enregistre définitivement dans la base de données.

La variable d’environnement AUTOCOMMIT, qui peut être positionnée a ON ou a OFF permet d’activer la gestion des

transactions. Si elle est positionnée à ON, chaque instruction a des répercussions immédiates dans la base, sinon, les

modifications ne sont effectives qu’une fois qu’un COMMIT a été exécuté.

9

1.4 Exceptions

Le mécanisme des exceptions est implémenté dans la plupart des langages récent, notament orientés objet. Cette

façon de programmer a quelques avantages immédiats :

– obliger les programmeurs à traiter les erreurs : combien de fois votre prof de C a hurlé en vous suppliant

de vérifier les valeurs retournées par un malloc, ou un fopen ? La plupart des compilateurs des langages à

exceptions (notamment java) ne compilent que si pour chaque erreur potentielle, vous avez préparé un bloc de

code (éventuellement vide...) pour la traiter. Le but est de vous assurer que vous n’avez pas oublié d’erreur.

– Rattraper les erreurs en cours d’exécution : Si vous programmez un système de sécurité de centrale

nucléaire ou un pilote automatique pour l’aviation civile, une erreur de mémoire qui vous afficherait l’écran

bleu de windows, ou le message “Envoyer le rapport d’erreur ?”, ou plus simplement le fameux “Segmentation

fault” produirait un effet des plus mauvais. Certaines erreurs d’éxecution sont rattrapables, autrement dit, il est

possible de résoudre le problème sans interrompre le programme.

– Ecrire le traitement des erreurs à part : Pour des raisons fiabilité, de lisibilité, il a été considéré que

mélanger le code “normal” et le traitement des erreurs était un style de programmation perfectible... Dans les

langages a exception, les erreurs sont traitées a part.

1.4.1 Rattraper une exception

Je vous ai menti dans le premier cours, un bloc en PL/SQL a la forme suivante :

DECLARE

BEGIN

/∗ d e c l a r a t i o n s ∗/

/∗ i n s t r u c t i o n s ∗/

EXCEPTION

/∗ t r a i t e m e n t d e s e r r e u r s ∗/

END;

Une exception est une “erreur type”, elle porte un nom, au même titre qu’une variable a une identificateur, par

exemple GLUBARF. Lorsque dans les instructions, l’erreur GLUBARF se produit, le code du BEGIN s’interrompt et le

code de la section EXCEPTION est lancé. On dit aussi que quand une exception est levée (raised) (on dit aussi jetée

(thrown)), on la rattrape (catch) dans le bloc EXCEPTION. La section EXCEPTION a la forme suivante :

EXCEPTION

WHEN E1 THEN

/∗ t r a i t e m e n t ∗/

WHEN E2 THEN

/∗ t r a i t e m e n t ∗/

WHEN E3 THEN

/∗ t r a i t e m e n t ∗/

WHEN OTHERS THEN

/∗ t r a i t e m e n t ∗/

END;

On énumere les erreurs les plus pertinentes en utilisant leur nom et en consacrant a chacune d’elle un traitement

particulier pour rattraper (ou propager) l’erreur. Quand un bloc est traité, les WHEN suivants ne sont pas évalués.

OTHERS est l’exception par défaut, OTHERS est toujours vérifié, sauf si un cas précédent a été vérifié. Dans l’exemple

suivant :

DECLARE

BEGIN

/∗ d e c l a r a t i o n s ∗/

/∗ i n s t r u c t i o n s ∗/

COMMIT;

EXCEPTION

WHEN GLUBARF THEN

ROLLBACK;

DBMS_OUTPUT . PUT_LINE ( ’GLUBARF e x c e p t i o n r a i s e d ! ’ ) ;

WHEN OTHERS THEN

DBMS_OUTPUT . PUT_LINE ( ’SQLCODE = ’

DBMS_OUTPUT . PUT_LINE ( ’SQLERRM = ’

| | SQLCODE ) ;

| | SQLERRM ) ;

10

END;

Les deux variables globales SQLCODE et SQLERRM contiennent respectivement le code d’erreur Oracle et un message

d’erreur correspondant a la derniere exception levée. Chaque exception a donc, en plus d’un nom, un code et un

message.

1.4.2 Exceptions prédéfinies

Bon nombre d’exceptions sont prédéfinies par Oracle, par exemple

– NO DATA FOUND est levée quand la requête d’une instruction de la forme SELECT ... INTO ... ne retourne

aucune ligne

– TOO MANY ROWS est levée quand la requête d’une instruction de la forme SELECT ... INTO ... retourne plusieurs

lignes

– DUP VAL ON INDEX est levée si une insertion (ou une modification) est refusée à cause d’une contrainte d’unicité.

On peut enrichir notre exemple de la sorte :

DECLARE

BEGIN

num NUMBER;

nom VARCHAR2( 3 0 ) := ’ Poupée Batman ’

;

SELECT numprod INTO num

FROM PRODUIT

WHERE nomprod = nom ;

DBMS_OUTPUT . PUT_LINE ( ’L ’ ’ a r t i c l e

’ a pour numéro ’

nom | |

| |

’

| | num ) ;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT . PUT_LINE ( ’ Aucun a r t i c l e ne p o r t e l e nom ’

| | nom ) ;

WHEN TOO_MANY_ROWS THEN

DBMS_OUTPUT . PUT_LINE ( ’ P l u s i e u r s a r t i c l e s p o r t e n t

| | nom ) ;

WHEN OTHERS THEN

l e nom ’

DBMS_OUTPUT . PUT_LINE ( ’ I l y a un g r o s p ro b lèm e . . . ’ ) ;

END;

/

SELECT numprod INTO num... lève une exception si la requête renvoie un nombre de lignes différent de 1.

1.4.3 Codes d’erreur

Je vous encore menti, certaines exceptions n’ont pas de nom. Elle ont seulement un code d’erreur, il est conseillé

de se reporter à la documentation pour les obtenir. On les traite de la façon suivante

EXCEPTION

WHEN OTHERS THEN

IF SQLCODE = CODE1 THEN

/∗ t r a i t e m e n t ∗/

ELSIF SQLCODE = CODE2 THEN

/∗ t r a i t e m e n t ∗/

ELSE

DBMS_OUTPUT . PUT_LINE ( ’ J ’ ’ v o i s pas c ’ ’ que ca

p e u t e t r e . . . ’ ) ;

END;

C’est souvent le cas lors de violation de contraintes.

1.4.4 Déclarer et lancer ses propres exceptions

Exception est un type, on déclare donc les exceptions dans une section DECLARE. Une exception se lance avec

l’instruction RAISE. Par exemple,

11

DECLARE

BEGIN

GLUBARF EXCEPTION;

RAISE GLUBARF ;

EXCEPTION

WHEN GLUBARF THEN

DBMS_OUTPUT . PUT_LINE ( ’ g l u b a r f

r a i s e d . ’ ) ;

END;

/

12

1.5 Sous-programmes

1.5.1 Procédures

Syntaxe

On définit une procédure de la sorte

CREATE OR REPLACE PROCEDURE /∗ nom ∗/ ( /∗ p a r a m e t r e s ∗/ ) IS

/∗ d e c l a r a t i o n d e s v a r i a b l e s

l o c a l e s ∗/

BEGIN

END;

/∗ i n s t r u c t i o n s ∗/

les parametres sont une simple liste de couples nom type. Par exemple, la procedure suivante affiche un compte a

rebours.

CREATE OR REPLACE PROCEDURE compteARebours ( n NUMBER) IS

BEGIN

IF n >= 0 THEN

DBMS_OUTPUT . PUT_LINE ( n ) ;

compteARebours ( n − 1 ) ;

END IF ;

END;

Invocation

En PL/SQL, une procédure s’invoque tout simplement avec son nom. Mais sous SQL+, on doit utiliser le mot-clé

CALL. Par exemple, on invoque le compte à rebours sous SQL+ avec la commande CALL compteARebours(20).

Passage de paramètres

Oracle permet le passage de parametres par référence. Il existe trois types de passage de parametres :

– IN : passage par valeur

– OUT : aucune valeur passée, sert de valeur de retour

– IN OUT : passage de paramètre par référence

Par défaut, le passage de paramètre se fait de type IN.

CREATE OR REPLACE PROCEDURE incr ( val IN OUT NUMBER) IS

BEGIN

val := val + 1 ;

END;

1.5.2 Fonctions

Syntaxe

On crée une nouvelle fonction de la façon suivante :

CREATE OR REPLACE FUNCTION /∗ nom ∗/ ( /∗ p a r a m e t r e s ∗/ ) RETURN /∗ t y p e

∗/ IS

/∗ d e c l a r a t i o n d e s v a r i a b l e s

l o c a l e s ∗/

BEGIN

END;

/∗ i n s t r u c t i o n s ∗/

L’instruction RETURN sert à retourner une valeur. Par exemple,

CREATE OR REPLACE FUNCTION module ( a NUMBER, b NUMBER) RETURN NUMBER IS

BEGIN

IF a < b THEN

RETURN a ;

ELSE

13

RETURN module ( a − b , b ) ;

END IF ;

END;

Invocation

Tout comme les procédures, l’invocation des fonctions ne pose aucun problème en PL/SQL, par contre, sous SQL+,

c’est quelque peu particulier. On passe par une pseudo-table nommée DUAL de la façon suivante :

SELECT module ( 2 1 , 1 2 ) FROM DUAL ;

Passage de paramètres

Les paramètres sont toujours passés avec le type IN.

14

1.6 Curseurs

1.6.1 Introduction

Les instructions de type SELECT ... INTO ... manquent de souplesse, elles ne fontionnent que sur des requêtes

retourant une et une seule valeur. Ne serait-il pas intéressant de pouvoir placer dans des variables le résultat d’une

requête retournant plusieurs lignes ? A méditer...

1.6.2 Les curseurs

Un curseur est un objet contenant le résultat d’une requête (0, 1 ou plusieurs lignes).

déclaration

Un curseur se déclare dans une section DECLARE :

CURSOR /∗ nomcurseur ∗/ IS /∗ r e q u ê t e ∗/ ;

Par exemple, si on tient à récupérer tous les employés de la table EMP, on déclare le curseur suivant.

CURSOR emp_cur IS

SELECT ∗ FROM EMP ;

Ouverture

Lors de l’ouverture d’un curseur, la requête du curseur est évaluée, et le curseur contient toutes les données

retournées par la requête. On ouvre un curseur dans une section BEGIN :

OPEN /∗ nomcurseur ∗/ ;

Par exemmple,

DECLARE

CURSOR emp_cur IS

SELECT ∗ FROM EMP ;

OPEN emp_cur ;

/∗ U t i l i s a t i o n du c u r s e u r ∗/

BEGIN

END;

Lecture d’une ligne

Une fois ouvert, le curseur contient toutes les lignes du résultat de la requête On les récupère une par une en

utilisant le mot-clé FETCH :

FETCH /∗ nom curseur

∗/ INTO /∗ l i s t e v a r i a b l e s ∗/ ;

La liste de variables peut être remplacée par une structure de type nom curseur%ROWTYPE. Si la lecture de la ligne

échoue, parce qu’il n’y a plus de ligne à lire, l’attribut %NOTFOUND prend la valeur vrai.

DECLARE

BEGIN

CURSOR emp_cur IS

SELECT ∗ FROM EMP ;

ligne emp_cur%rowtype

OPEN emp_cur ;

LOOP

FETCH emp_cur INTO ligne ;

EXIT WHEN emp_cur%NOTFOUND ;

DBMS_OUTPUT . PUT_LINE ( ligne . ename ) ;

END LOOP ;

/∗ . . . ∗/

END;

15

Fermeture

Après utilisation, il convient de fermer le curseur.

CLOSE /∗ nomcurseur ∗/ ;

Complétons notre exemple,

DECLARE

BEGIN

CURSOR emp_cur IS

SELECT ∗ FROM EMP ;

ligne emp_cur%rowtype ;

OPEN emp_cur ;

LOOP

FETCH emp_cur INTO ligne ;

EXIT WHEN emp_cur%NOTFOUND ;

DBMS_OUTPUT . PUT_LINE ( ligne . ename ) ;

END LOOP ;

CLOSE emp_cur ;

END;

/

Le programme ci-dessus peut aussi s’écrire

DECLARE

BEGIN

CURSOR emp_cur IS

SELECT ∗ FROM EMP ;

ligne emp_cur%rowtype ;

OPEN emp_cur ;

FETCH emp_cur INTO ligne ;

WHILE emp_cur%FOUND LOOP

DBMS_OUTPUT . PUT_LINE ( ligne . ename ) ;

FETCH emp_cur INTO ligne ;

END LOOP ;

CLOSE emp_cur ;

END;

Boucle FOR

Il existe une boucle FOR se chargeant de l’ouverture, de la lecture des lignes du curseur et de sa fermeture,

FOR ligne IN emp_cur LOOP

/∗ Traitement ∗/

END LOOP ;

Par exemple,

DECLARE

CURSOR emp_cur IS

SELECT ∗ FROM EMP ;

ligne emp_cur%rowtype ;

BEGIN

END;

/

FOR ligne IN emp_cur LOOP

DBMS_OUTPUT . PUT_LINE ( ligne . ename ) ;

END LOOP ;

16

1.7 Curseurs parametrés

1.7.1 Introduction

A votre avis, le code suivant est-il valide ?

DECLARE

BEGIN

NUMBER n := 1 4 ;

DECLARE

CURSOR C IS

SELECT ∗

FROM PERSONNE

WHERE numpers >= n ;

BEGIN

ROW C%rowType ;

FOR ROW IN C LOOP

DBMS_OUTPUT . PUT_LINE ( ROW . numpers ) ;

END LOOP ;

END;

END;

/

Réponse : non. La requête d’un curseur ne peut pas contenir de variables dont les valeurs ne sont pas fixées.

Pourquoi ? Parce que les valeurs des ces sont susceptibles de changer entre la déclaration du curseur et son ouverture.

Le remède est un curseur paramétré.

1.7.2 Définition

Un curseur paramétré est un curseur dont la requête contient des variables dont les valeurs ne seront fixées qu’à

l’ouverture.

1.7.3 Déclaration

On précise la liste des noms et des type des parametres entre parentheses après le nom du curseur :

CURSOR /∗ nom ∗/ ( /∗ l i s t e d e s p a r a mè t r e s ∗/ ) IS

/∗ r e q u ê t e ∗/

Par exemple, créeons une requête qui, pour une personne donnée, nous donne la liste des noms et prénoms de ses

enfants :

CURSOR enfants ( numparent NUMBER) IS

SELECT ∗

FROM PERSONNE

WHERE pere = numparent

OR mere = numparent ;

1.7.4 Ouverture

On ouvre un curseur paramétré en passant en paramètre les valeurs des variables :

OPEN /∗ nom ∗/ ( /∗ l i s t e d e s p a r a mè t r e s ∗/ )

Par exemple,

OPEN enfants ( 1 ) ;

1.7.5 Lecture d’une ligne, fermeture

la lecture d’une ligne suit les mêmes règles qu’avec un curseur non paramétré.

17

1.7.6 Boucle pour

La boucle pour se charge de l’ouverture, il convient donc de placer les paramètre dans l’entête de la boucle,

FOR /∗ v a r i a b l e ∗/ IN /∗ nom ∗/ ( /∗ l i s t e p a r a mè t r e s ∗/ ) LOOP

/∗ i n s t r u c t i o n s ∗/

END LOOP ;

Par exemple,

FOR e IN enfants ( 1 ) LOOP

DBMS_OUTPUT . PUT_LINE ( e . nompers | |

’

’

| | e . prenompers ) ;

END LOOP ;

1.7.7 Exemple récapitulatif

DECLARE

CURSOR parent IS

SELECT ∗

FROM PERSONNE ;

p parent%rowtype ;

CURSOR enfants ( numparent NUMBER) IS

SELECT ∗

FROM PERSONNE

WHERE pere = numparent

OR mere = numparent ;

BEGIN

e enfants%rowtype ;

FOR p IN parent LOOP

DBMS_OUTPUT . PUT_LINE ( ’ Les e n f a n t s de ’ | | p . prenom | |

:

FOR e IN enfants ( p . numpers ) LOOP

| | p . nom | |

’ s o n t

’

’

’ ) ;

DBMS_OUTPUT . PUT_LINE ( ’ ∗ ’ | | e . prenom

| | e . nom

) ;

| |

’

’

END LOOP ;

END LOOP ;

END;

/

18

1.8 Triggers

1.8.1 Principe

Un trigger est une procédure stockée qui se lance automatiquement lorsqu’un événement se produit. Par événement,

on entend dans ce cours toute modification des données se trouvant dans les tables. On s’en sert pour contrôler ou

appliquer des contraintes qu’il est impossible de formuler de façon déclarative.

1.8.2 Classification

Type d’événement

Lors de la création d’un trigger, il convient de préciser quel est le type d’événement qui le déclenche. Nous réaliserons

dans ce cours des triggers pour les événements suivants :

– INSERT

– DELETE

– UPDATE

Moment de l’éxecution

On précise aussi si le trigger doit être éxecuté avant (BEFORE) ou après (AFTER) l’événement.

Evénements non atomiques

Lors que l’on fait un DELETE ..., il y a une seule instruction, mais plusieurs lignes sont affectées. Le trigger doit-il

être exécuté pour chaque ligne affectée (FOR EACH ROW), ou seulement une fois pour toute l’instruction (STATEMENT) ?

– un FOR EACH ROW TRIGGER est exécuté à chaque fois qu’une ligne est affectée.

– un STATEMENT TRIGGER est éxecutée à chaque fois qu’une instruction est lancée.

1.8.3 Création

Syntaxe

On déclare un trigger avec l’instruction suivante :

CREATE OR REPLACE TRIGGER nomtrigger

[ BEFORE | AFTER ]

[ FOR EACH ROW |

DECLARE

]

[INSERT | DELETE | UPDATE] ON nomtable

/∗ d e c l a r a t i o n s ∗/

/∗ i n s t r u c t i o n s ∗/

BEGIN

END;

Par exemple,

SQL> CREATE OR REPLACE TRIGGER pasDeDeleteDansClient

BEFORE DELETE ON CLIENT

BEGIN

RAISE_APPLICATION_ERROR ( −20555 ,

2

3

4

5 END;

/

6

Déclencheur créé .

SQL> SELECT COUNT( ∗ )

...