Introduction au PL/SQL Oracle

Chapitre 1

Notes de cours

1.1 Introduction au PL/SQL

1.1.1 PL/SQL

Le PL de PL/SQL signifie Procedural Language. Il s’agit d’une extension proc´edurale du SQL permettant d’effectuer

des traitements complexes sur une base de donn´ees. Les possibilit´es offertes sont les mˆemes qu’avec des langages

imp´eratifs (instructions en s´equence) classiques.

Ecrivez-le dans un ´editeur dont vous copierez le contenu dans SQL+. Un script ´ecrit en PL/SQL se termine obliga-

toirement par un /, sinon SQL+ ne l’interpr`ete pas. S’il contient des erreurs de compilation, il est possible d’afficher les

messages d’erreur avec la commande SQL+ : SHOW ERRORS.

1.1.2 Blocs

Tout code ´ecrit dans un langage proc´edural est form´e de blocs. Chaque bloc comprend une section de d´eclaration

de variables, et un ensemble d’instructions dans lequel les variables d´eclar´ees sont visibles.

La syntaxe est

DECLARE

BEGIN

END;

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

/∗ i n s t r u c t i o n s a e x e c u t e r ∗/

1.1.3 Affichage

Pour afficher le contenu d’une variable, les proc´edures DBMS OUTPUT.PUT() et DBMS OUTPUT.PUT LINE() prennent

en argument une valeur a afficher ou une variable dont la valeur est a afficher. Par d´efaut, les fonctions d’affichage

sont desactiv´ees. Il convient, `a moins que vous ne vouliez rien voir s’afficher, de les activer avec la commande SQL+

SET SERVEROUTPUT ON.

1.1.4 Variables

Une variable se d´eclare de la sorte :

nom type [ : = initialisation ]

;

L’initisation est optionnelle. Nous utiliserons les mˆemes types primitifs que dans les tables. Par exemple :

SET SERVEROUTPUT ON

DECLARE

c varchar2 ( 1 5 ) := ’ H e l l o World ! ’ ;

DBMS_OUTPUT . PUT_LINE ( c ) ;

BEGIN

END;

/

Les affectations se font avec la syntaxe variable := valeur ;

3

1.1.5 Traitements conditionnels

Le IF et le CASE fonctionnent de la mˆeme fa¸con que dans les autres langages imp´eratifs :

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

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

ELSE

END IF ;

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ˆemes qu’en SQL. Le switch du langage C s’impl´emente en PL/SQL de la fa¸con 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 ´e f a u t ∗/

END CASE;

1.1.6 Traitements r´ep´etitifs

LOOP ... END LOOP ; permet d’impl´ementer 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´ementer une boucle DO ... WHILE ?

4

1.2 Tableaux et structures

1.2.1 Tableaux

Cr´eation d’un type tableau

Les types tableau doivent ˆetre d´efinis explicitement par une d´eclaration 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´ee par cette instruction

– taille est le nombre maximal d’´el´ements qu’il est possible de placer dans le tableau.

– typeElements est le type des ´el´ements qui vont ˆetre stock´es dans le tableau, il peut s’agir de n’importe quel

type.

Par exemple, cr´eons un type tableau de nombres indic´e de 1 `a 10, que nous appelerons numberTab

TYPE numberTab IS VARRAY ( 1 0 ) OF NUMBER;

D´eclaration d’un tableau

Dor´enavant, le type d’un tableau peut ˆetre utilis´e au mˆeme titre que NUMBER ou VARCHAR2. Par exemple, d´eclarons

un tableau appel´e 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´eation d’un type tableau met `a disposition un constructeur du mˆeme nom que le type cr´e´e. Cette fonction

r´eserve de l’espace m´emoire pour ce tableau et retourne l’adresse m´emoire de la zone r´eserv´ee, il s’agit d’une sorte de

malloc. Si, par exemple, un type tableau numtab a ´et´e cr´ee, 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´e par le constructeur est vide. Il convient ensuite de r´eserver de l’espace pour stocker les ´el´ements

qu’il va contenir. On utilise pour cela la m´ethode EXTEND(). EXTEND s’invoque en utilisant la notation point´ee. 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 ´el´ements du tableau t(1), t(2), t(3) et t(4).

Il n’est pas possible ”d’´etendre” un tableau a une taille sup´erieure a celle sp´ecifi´ee lors de la cr´eation du type tableau

associ´e.

Utilisation d’un tableau

On accede, en lecture et en ´ecriture, au i-eme ´el´ement d’une variable tabulaire nomm´e T avec l’instruction T(i).

Les ´el´ements sont indic´es `a partir de 1.

Effectuons, par exemple, une permutation circulaire vers la droite des ´el´ements 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´e contient plusieurs variables,

ces variables s’appellent aussi des champs.

Cr´eation d’un type structur´e

On d´efinit un type structur´e de la sorte :

TYPE /∗ nomType ∗/ IS RECORD

(

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

) ;

nomType est le nom du type structur´e construit avec la syntaxe pr´ec´edente. La liste suit la mˆeme 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 `a d´efinir un type structur´e peuvent ˆetre quelconques : variables scalaires, tableaux,

structures, etc.

D´eclaration d’une variable de type structur´e

point est maintenant un type, il devient donc possible de cr´eer des variables de type point, la r`egle est toujours la

mˆeme pour d´eclarer des variables en PL/SQL, par exemple

6

p point ;

permet de d´eclarer une variable p de type point.

Utilisation d’une variable de type structur´e

Pour acc´eder `a un champ d’une variable de type structur´e, en lecture ou en ´ecriture, on utilise la notation point´ee :

v.c est le champ appel´e c de la variable structur´e appel´ee 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´ee le type point, puis cr´ee 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´ees et les scripts PL/SQL.

1.3.1 Affectation

On place dans une variable le r´esultat d’une requˆete en utilisant le mot-cl´e INTO. Les instructions

SELECT champ_1 ,

FROM . . .

. . . , champ_n INTO v_1 ,

. . . , v_n

affecte aux variables v 1, ..., v n les valeurs retourn´ees par la requˆete. Par exemple

DECLARE

BEGIN

END;

/

num NUMBER;

nom VARCHAR2( 3 0 ) := ’ Poup´ee 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´ero ’

nom | |

| |

’

| | num ) ;

Prˆetez attention au fait que la requˆete doit retourner une et une une seule ligne, sinon, une erreur se produit `a

l’ex´ecution.

1.3.2 Tables et structures

Si vous ne tenez pas `a vous prendre la tˆete pour choisir le type de chaque variable, demandez-vous ce que vous

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

vous r´ef´erer 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´ee 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´ero ’

nom | |

| |

’

| | num ) ;

Pour aller plus loin, il est mˆeme possible de d´eclarer une structure pour repr´esenter une ligne d’une table, le type

porte alors le nom suivant : nomTable%rowtype.

DECLARE

BEGIN

END;

/

nom PRODUIT . nomprod%type := ’ Poup´ee 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´ero ’

| |

| | ligne . numprod ) ;

8

1.3.3 Transactions

Un des m´ecanismes les plus puissants des SGBD r´ecents r´eside dans le syst`eme des transactions. Une transaction

est un ensemble d’op´erations “atomiques”, c’est-`a-dire indivisible. Nous consid´ererons qu’un ensemble d’op´erations est

indivisible si une ex´ecution partielle de ces instructions poserait des probl`emes d’int´egrit´e dans la base de donn´ees.

Par exemple, dans le cas d’une base de donn´ees de gestion de comptes en banque, un virement d’un compte `a un autre

se fait en deux temps : cr´editer un compte d’une somme s, et d´ebiter un autre de la mˆeme somme s. Si une erreur

survient pendant la deuxi`eme op´eration, et que la transaction est interrompue, le virement est incomplet et le patron

va vous assassiner.

Il convient donc de disposer d’un m´ecanisme permettant de se prot´eger de ce genre de d´esagr´ement. Plutˆot que

se casser la tˆete a tester les erreurs a chaque ´etape 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´ebut de la transaction (donc depuis le pr´ec´edent

COMMIT), COMMIT les enregistre d´efinitivement dans la base de donn´ees.

La variable d’environnement AUTOCOMMIT, qui peut ˆetre positionn´ee a ON ou a OFF permet d’activer la gestion des

transactions. Si elle est positionn´ee `a ON, chaque instruction a des r´epercussions imm´ediates dans la base, sinon, les

modifications ne sont effectives qu’une fois qu’un COMMIT a ´et´e ex´ecut´e.

9

1.4 Exceptions

Le m´ecanisme des exceptions est impl´ement´e dans la plupart des langages r´ecent, notament orient´es objet. Cette

fa¸con de programmer a quelques avantages imm´ediats :

– obliger les programmeurs `a traiter les erreurs : combien de fois votre prof de C a hurl´e en vous suppliant

de v´erifier les valeurs retourn´ees par un malloc, ou un fopen ? La plupart des compilateurs des langages `a

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

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

– Rattraper les erreurs en cours d’ex´ecution : Si vous programmez un syst`eme de s´ecurit´e de centrale

nucl´eaire ou un pilote automatique pour l’aviation civile, une erreur de m´emoire qui vous afficherait l’´ecran

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’´execution sont rattrapables, autrement dit, il est

possible de r´esoudre le probl`eme sans interrompre le programme.

– Ecrire le traitement des erreurs `a part : Pour des raisons fiabilit´e, de lisibilit´e, il a ´et´e consid´er´e que

m´elanger le code “normal” et le traitement des erreurs ´etait un style de programmation perfectible... Dans les

langages a exception, les erreurs sont trait´ees 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ˆeme 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´e. On dit aussi que quand une exception est lev´ee (raised) (on dit aussi jet´ee

(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 ´enumere 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´e, les WHEN suivants ne sont pas ´evalu´es.

OTHERS est l’exception par d´efaut, OTHERS est toujours v´erifi´e, sauf si un cas pr´ec´edent a ´et´e v´erifi´e. 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´ee. Chaque exception a donc, en plus d’un nom, un code et un

message.

1.4.2 Exceptions pr´ed´efinies

Bon nombre d’exceptions sont pr´ed´efinies par Oracle, par exemple

– NO DATA FOUND est lev´ee quand la requˆete d’une instruction de la forme SELECT ... INTO ... ne retourne

aucune ligne

– TOO MANY ROWS est lev´ee quand la requˆete d’une instruction de la forme SELECT ... INTO ... retourne plusieurs

lignes

– DUP VAL ON INDEX est lev´ee si une insertion (ou une modification) est refus´ee `a cause d’une contrainte d’unicit´e.

On peut enrichir notre exemple de la sorte :

DECLARE

BEGIN

num NUMBER;

nom VARCHAR2( 3 0 ) := ’ Poup´ee 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´ero ’

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`em e . . . ’ ) ;

END;

/

SELECT numprod INTO num... l`eve une exception si la requˆete renvoie un nombre de lignes diff´erent 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´e

de se reporter `a la documentation pour les obtenir. On les traite de la fa¸con 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´eclarer et lancer ses propres exceptions

Exception est un type, on d´eclare 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´edures

Syntaxe

On d´efinit une proc´edure 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´edure s’invoque tout simplement avec son nom. Mais sous SQL+, on doit utiliser le mot-cl´e

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

Passage de param`etres

Oracle permet le passage de parametres par r´ef´erence. Il existe trois types de passage de parametres :

– IN : passage par valeur

– OUT : aucune valeur pass´ee, sert de valeur de retour

– IN OUT : passage de param`etre par r´ef´erence

Par d´efaut, le passage de param`etre 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´ee une nouvelle fonction de la fa¸con 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 `a 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´edures, l’invocation des fonctions ne pose aucun probl`eme en PL/SQL, par contre, sous SQL+,

c’est quelque peu particulier. On passe par une pseudo-table nomm´ee DUAL de la fa¸con suivante :

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

Passage de param`etres

Les param`etres sont toujours pass´es 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ˆetes

retourant une et une seule valeur. Ne serait-il pas int´eressant de pouvoir placer dans des variables le r´esultat d’une

requˆete retournant plusieurs lignes ? A m´editer...

1.6.2 Les curseurs

Un curseur est un objet contenant le r´esultat d’une requˆete (0, 1 ou plusieurs lignes).

d´eclaration

Un curseur se d´eclare dans une section DECLARE :

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

Par exemple, si on tient `a r´ecup´erer tous les employ´es de la table EMP, on d´eclare le curseur suivant.

CURSOR emp_cur IS

SELECT ∗ FROM EMP ;

Ouverture

Lors de l’ouverture d’un curseur, la requˆete du curseur est ´evalu´ee, et le curseur contient toutes les donn´ees

retourn´ees par la requˆete. 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´esultat de la requˆete On les r´ecup`ere une par une en

utilisant le mot-cl´e FETCH :

FETCH /∗ nom curseur

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

La liste de variables peut ˆetre remplac´ee par une structure de type nom curseur%ROWTYPE. Si la lecture de la ligne

´echoue, parce qu’il n’y a plus de ligne `a 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`es utilisation, il convient de fermer le curseur.

CLOSE /∗ nomcurseur ∗/ ;

Compl´etons 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’´ecrire

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´es

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´eponse : non. La requˆete d’un curseur ne peut pas contenir de variables dont les valeurs ne sont pas fix´ees.

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

Le rem`ede est un curseur param´etr´e.

1.7.2 D´efinition

Un curseur param´etr´e est un curseur dont la requˆete contient des variables dont les valeurs ne seront fix´ees qu’`a

l’ouverture.

1.7.3 D´eclaration

On pr´ecise la liste des noms et des type des parametres entre parentheses apr`es le nom du curseur :

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

/∗ r e q u ˆe t e ∗/

Par exemple, cr´eeons une requˆete qui, pour une personne donn´ee, nous donne la liste des noms et pr´enoms 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´etr´e en passant en param`etre les valeurs des variables :

OPEN /∗ nom ∗/ ( /∗ l i s t e d e s p a r a m`e 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ˆemes r`egles qu’avec un curseur non param´etr´e.

17

1.7.6 Boucle pour

La boucle pour se charge de l’ouverture, il convient donc de placer les param`etre dans l’entˆete de la boucle,

FOR /∗ v a r i a b l e ∗/ IN /∗ nom ∗/ ( /∗ l i s t e p a r a m`e 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´ecapitulatif

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´edure stock´ee qui se lance automatiquement lorsqu’un ´ev´enement se produit. Par ´ev´enement,

on entend dans ce cours toute modification des donn´ees se trouvant dans les tables. On s’en sert pour contrˆoler ou

appliquer des contraintes qu’il est impossible de formuler de fa¸con d´eclarative.

1.8.2 Classification

Type d’´ev´enement

Lors de la cr´eation d’un trigger, il convient de pr´eciser quel est le type d’´ev´enement qui le d´eclenche. Nous r´ealiserons

dans ce cours des triggers pour les ´ev´enements suivants :

– INSERT

– DELETE

– UPDATE

Moment de l’´execution

On pr´ecise aussi si le trigger doit ˆetre ´execut´e avant (BEFORE) ou apr`es (AFTER) l’´ev´enement.

Ev´enements non atomiques

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

ˆetre ex´ecut´e pour chaque ligne affect´ee (FOR EACH ROW), ou seulement une fois pour toute l’instruction (STATEMENT) ?

– un FOR EACH ROW TRIGGER est ex´ecut´e `a chaque fois qu’une ligne est affect´ee.

– un STATEMENT TRIGGER est ´execut´ee `a chaque fois qu’une instruction est lanc´ee.

1.8.3 Cr´eation

Syntaxe

On d´eclare 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´eclencheur cr´e´e .

SQL> SELECT COUNT( ∗ )

...