pl/sql (#1) définition (1)ljv.free.fr/orapdf/plsql_bases_2010.pdf1/76 pl/sql (#1) jean-luc goerig...

Jean-Luc GOERIG1/76 PL/SQL (#1)

Instructions de base Instructions de base

Séquences, curseursSéquences, curseurs

Traitement des erreursTraitement des erreurs

Procédures et fonctionsProcédures et fonctions

Tableaux, structures et collectionsTableaux, structures et collections

JeanJean--Luc GOERIG Luc GOERIG -- plsql_bases.pdf plsql_bases.pdf -- #090915 #090915 -- révision septembre 2009révision septembre 2009

PL/SQL (#1)


Définition (1)Définition (1)

� PL/SQL = Procedural Language / SQL

� Extension du langage SQL

� Langage de programmation procédural (L3G)

� Intégré dans Oracle (SQL+, PRO*C,SQL*FORMS …)

� Possibilités:

� Ordres SQL (LMD): SELECT,INSERT,UPDATE,DELETE

� Variables et sous-programmes (BLOC PL/SQL)

� Structures conditionnelles: IF,THEN,ELSEIF,ELSE,ENDIF

� Structures itératives : LOOP,FOR,WHILE,EXIT WHEN

� Utilisation des fonctions et prédicats SQL


Définition (2)Définition (2)

� Possibilités:

� Gestion de curseurs

� Traitement des erreurs : EXCEPTION

� Gestion de transactions: COMMIT,ROLLBACK,SAVEPOINT

� Procédures et fonctions: CREATE PROCEDURE ou FUNCTION

� Packages: regroupement de procédures et fonctions

� Packages standards Oracle (DBMS_OUTPUT,UTL_FILE …)

� Codage de Triggers

� Restriction:

� LDD impossible (ALTER,CREATE….)

� Avantages:

� Portabilité sur Oracle, performant, facilité de programmation


Une structure de BlocsUne structure de Blocs

� PL/SQL interprète un ensemble de commandes contenu dans un programme ou bloc PL/SQL:

DECLARE

-- Section facultative

-- Déclarations:variables locales au bloc

constantes, exceptions

curseurs

BEGIN

-- Section obligatoire contenant les commandes exécutables

-- Instructions SQL et PL/SQL

-- Imbrication de blocs:

BEGIN –- bloc B

…

END; -- fin bloc B

EXCEPTION


-- Traitement des exceptions (gestion des erreurs)

END;

DECLARE


-- Déclarations:variables locales au bloc

constantes, exceptions

curseurs

BEGIN

-- Section obligatoire contenant les commandes exécutables

-- Instructions SQL et PL/SQL

-- Imbrication de blocs:

BEGIN –- bloc B

…

END; -- fin bloc B

EXCEPTION


-- Traitement des exceptions (gestion des erreurs)

END;

� Chaque instruction de n’importe quelle section doit se terminer par un ‘;’


-- traitement de commandes

DECLARE

qte_stock number(5);

BEGIN

Select quantite into qte_stock

from inventaire

where produit=‘chaise’;

-- contrôle du stock suffisant

if qte_stock > 0 then

update inventaire set quantite = quantite – 1


insert into achat

values (‘chaise’,sysdate);

else

insert into acheter

values (‘Plus de chaise!’,sysdate);

end if;

commit;

END;

-- traitement de commandes

DECLARE

qte_stock number(5);

BEGIN

Select quantite into qte_stock

from inventaire


-- contrôle du stock suffisant

if qte_stock > 0 then

update inventaire set quantite = quantite – 1


insert into achat

values (‘chaise’,sysdate);

else

insert into acheter

values (‘Plus de chaise!’,sysdate);

end if;

commit;

END;

ExempleExemple


Variables (1)Variables (1)

� Types de variables:

� variables locales: type natif Oracle (NUMBER,CHAR…)

type booléen (valeurs TRUE,FALSE, NULL)

faisant référence au D. de Données Oracle

� variables externes: variables SQL*Plus (&)

Host variables (langage hôte avec ‘:‘)

variables d’écrans SQL*Forms

� Déclaration des variables:

� les variables locales sont définies dans la section DECLARE

� Variables de type Oracle:

DECLARE

nom char(15);

numero_truc number; -- longueur 38

date_jour date;

salaire number(7,2);

BEGIN …

DECLARE

nom char(15);

numero_truc number; -- longueur 38

date_jour date;

salaire number(7,2);

BEGIN …



� Variables de type booléen:

� Variables faisant référence au D.D. Oracle (%TYPE):

� variables héritant du même type qu’une colonne de table

� permet de répercuter des modifications de structure d’une table

DECLARE

reponse boolean;-- valeur TRUE,FALSE,NULL

BEGIN …

DECLARE

reponse boolean;-- valeur TRUE,FALSE,NULL

BEGIN …

DECLARE

nom emp.name%TYPE; -- de même type que la colonne NAME de la table EMP

BEGIN …

DECLARE

nom emp.name%TYPE; -- de même type que la colonne NAME de la table EMP

BEGIN …

� variable reprenant la structure qu’une ligne d’une table (%ROWTYPE)

DECLARE

enreg emp%ROWTYPE; -- chaque champ de la variable ENREG a même nom et même type

-- que la colonne associée: enreg.nomcolonne

BEGIN …

DECLARE

enreg emp%ROWTYPE; -- chaque champ de la variable ENREG a même nom et même type

-- que la colonne associée: enreg.nomcolonne

BEGIN …



� Variables de même type qu’une variable précédemment définie:

� Visibilité et initialisation des variables:

� Une variable est visible dans le bloc où elle a été déclarée

et dans les blocs imbriqués si elle n’a pas été redéfinie

� opérateur d’affectation :=

DECLARE

commission number(7,2);

salaire commission%TYPE;

BEGIN …

DECLARE

commission number(7,2);

salaire commission%TYPE;

BEGIN …

DECLARE

nom char(10) := ‘Zorro’;

salaire number(7,2) := 9800;

reponse boolean := TRUE;

BEGIN …

DECLARE

nom char(10) := ‘Zorro’;

salaire number(7,2) := 9800;

reponse boolean := TRUE;

BEGIN …



� Ordre SELECT …INTO… (section BEGIN)

Attention!

�Clause into obligatoire!

�Le select ne doit ramener qu’une seule ligne! (sinon programmer un curseur)

DECLARE

nom_emp CHAR(15);

sal emp.salaire%TYPE;

com emp.commission%TYPE;

nom_serv CHAR(15);

BEGIN

SELECT nom,salaire,commission,service

INTO nom_emp,sal,com,nom_serv

FROM emp,serv

WHERE nom='dupont'

AND emp.noserv=serv.noserv;

DECLARE

nom_emp CHAR(15);

sal emp.salaire%TYPE;

com emp.commission%TYPE;

nom_serv CHAR(15);

BEGIN

SELECT nom,salaire,commission,service

INTO nom_emp,sal,com,nom_serv

FROM emp,serv

WHERE nom='dupont'

AND emp.noserv=serv.noserv;


Structures conditionnellesStructures conditionnelles

Opérateurs valides: = < > != >= <= and or not

is null is not null between like

IF <condition> THEN IF <condition> THEN IF <condition1> THEN

traitement; traitement1; traitement1;

END IF; ELSE ELSIF <condition2> THEN

traitement2 traitement2;

END IF; ELSE

traitement3;

END IF;

IF <condition> THEN IF <condition> THEN IF <condition1> THEN

traitement; traitement1; traitement1;

END IF; ELSE ELSIF <condition2> THEN

traitement2 traitement2;

END IF; ELSE

traitement3;

END IF;

� Trois formes de IF:

� Structure CASE:

CASE <variable>

WHEN <condition1> THEN instructions1;


…

WHEN <conditionN> THEN instructionsN;

[ ELSE instructionsZ; ]

END CASE;

CASE <variable>



…

WHEN <conditionN> THEN instructionsN;

[ ELSE instructionsZ; ]

END CASE;


Structures itératives (boucles) (1)Structures itératives (boucles) (1)

� 4 types de boucle :

boucle de base LOOP boucle WHILE

boucle FOR boucle CURSOR...…FOR

� Boucle de base LOOP:

� sortie de la boucle par la commande EXIT [label] [when condition]

DECLARE

nbre NUMBER:=1;

BEGIN

LOOP

INSERT INTO résultat VALUES (nbre);

nbre:=nbre+1

EXIT WHEN nbre > 10;

END LOOP;

END;

DECLARE

nbre NUMBER:=1;

BEGIN

LOOP

INSERT INTO résultat VALUES (nbre);

nbre:=nbre+1

EXIT WHEN nbre > 10;

END LOOP;

END;

LOOPinstructions;

END LOOP;

LOOPinstructions;

END LOOP;


Structures itératives (2)Structures itératives (2)

� Boucle FOR (pour):

la déclaration de la variable indice est implicite.

valeur_début, valeur_fin: constantes, expressions ou variables.

l'indice varie de valeur_début à valeur_fin avec un incrément de 1.

avec l'option REVERSE le pas est de -1 de valeur_fin vers valeur_début.

DECLARE

fact NUMBER:=1;

BEGIN

FOR i IN 1..9

LOOP

fact:=fact*i;

END LOOP;

INSERT INTO resultat VALUES (fact,'factorielle 9');

END;

DECLARE

fact NUMBER:=1;

BEGIN

FOR i IN 1..9

LOOP

fact:=fact*i;

END LOOP;

INSERT INTO resultat VALUES (fact,'factorielle 9');

END;

FOR var_indice IN [REVERSE] valeur_début .. valeur_fin

LOOP

instructions;

END LOOP;

FOR var_indice IN [REVERSE] valeur_début .. valeur_fin

LOOP

instructions;

END LOOP;



� Boucle WHILE (Tq):

Exécution de la boucle tant que la condition de la clause while est vérifiée

DECLARE

reste NUMBER := 7324;

BEGIN

WHILE reste >= 9

LOOP

reste := reste - 9;

END LOOP;

INSERT INTO resultat VALUES (reste,’reste de la division de 7324 par 9’);

END;

DECLARE

reste NUMBER := 7324;

BEGIN

WHILE reste >= 9

LOOP

reste := reste - 9;

END LOOP;

INSERT INTO resultat VALUES (reste,’reste de la division de 7324 par 9’);

END;

BEGIN

WHILE condition

LOOP

instructions;

END LOOP;

END;

BEGIN

WHILE condition

LOOP

instructions;

END LOOP;

END;



� Boucle Répéter:

� Dommage! il n’y a pas de repeat en PL/SQL!

� Se programme avec la syntaxe LOOP … EXIT.

BEGIN

...

LOOP

<instructions>;

EXIT WHEN <condition>;

END LOOP;

...

BEGIN

...

LOOP

<instructions>;

EXIT WHEN <condition>;

END LOOP;

...


Utilisation de variables définies sous SQL*PlusUtilisation de variables définies sous SQL*Plus

� On peut définir des variables sous SQL*Plus par la commande ACCEPT

Ces variables sont utilisables dans le bloc PL/SQL en les préfixant par &

Ce sont des variables de substitution.

-- variables SQL*Plus

accept lenom prompt ‘taper votre nom:’

declare

vnom char(15);

vnompilote transair.pilote.nompil%type;

begin

vnom := &lenom;

select nompil into vnompilote

from transair.pilote

where nompil='&lenom';

...

end;

-- variables SQL*Plus

accept lenom prompt ‘taper votre nom:’

declare

vnom char(15);

vnompilote transair.pilote.nompil%type;

begin

vnom := &lenom;

select nompil into vnompilote

from transair.pilote

where nompil='&lenom';

...

end;

NB: ces variables sont utilisables avec Sql Developer sans accept.


Package DBMS_OUTPUTPackage DBMS_OUTPUT

� Le package standard DBMS_OUTPUT :

� PL/SQL ne possède pas d’instructions natives permettant d’afficher sous SQL*PLUS le contenu des variables.

� Pour tracer l’exécution d’un bloc PL/SQL, il est commode d’utiliser le package DBMS_OUTPUT livré d’origine avec Oracle.

� Pour utiliser DBMS_OUTPUT dans SQL*Plus, passer la commande

<SET SERVEROUTPUT ON> avant l’exécution du bloc PL/SQL.

� Dans Sql Developer, c’est l’icône [Activer la sortie SGBD] qui active <SERVEROUTPUT ON> dans la fenêtre [Sortie SGBD].

� Ne pas oublier l’instruction DBMS_OUTPUT.ENABLE.

declare

vnum transair.pilote.nopilote%type;

vnom transair.pilote.nompil%type;

begin

dbms_output.enable;

select nopilote,nompil into vnum,vnom from transair.pilote where nompilote =‘6723’;

dbms_output.put_line('Pilote: '||vnum||' '||vnom);

...

end;

declare

vnum transair.pilote.nopilote%type;

vnom transair.pilote.nompil%type;

begin

dbms_output.enable;

select nopilote,nompil into vnum,vnom from transair.pilote where nompilote =‘6723’;

dbms_output.put_line('Pilote: '||vnum||' '||vnom);

...

end;


Mise en œuvre de PL/SQL et déboguage + exerciceMise en œuvre de PL/SQL et déboguage + exercice

� Programmer en PL/SQL sous SQL *Plus:

� Saisir le code PL/SQL avec un éditeur dans un script SQL

� Exécuter le script sous SQL*Plus avec la commende @

� Déboguage du programme: commande show errors;

� Afficher le code source: select line,text from user_sources;

� Mieux: programmer avec Sql Developer ! (voir diapo suivante)

Exercice:

Ecrire un programme PL/SQL qui effectue à la demande la conversion d’un nombre de secondes en heures,minutes et secondes ou la conversion d’un nombre d’heures,minutes et secondes en nombre de secondes.

exemple: 1 heure 20 min 30 sec = 4860 secondes

Utiliser pour les calculs les fonctions Oracle suivantes:

floor(nombre/diviseur) pour obtenir le résultat en entier

mod(nombre,diviseur) pour le modulo

Faire afficher les résultats avec DBMS_OUTPUT

Exercice:

Ecrire un programme PL/SQL qui effectue à la demande la conversion d’un nombre de secondes en heures,minutes et secondes ou la conversion d’un nombre d’heures,minutes et secondes en nombre de secondes.

exemple: 1 heure 20 min 30 sec = 4860 secondes

Utiliser pour les calculs les fonctions Oracle suivantes:

floor(nombre/diviseur) pour obtenir le résultat en entier

mod(nombre,diviseur) pour le modulo

Faire afficher les résultats avec DBMS_OUTPUT


PL/SQL avec PL/SQL avec SqlSql DeveloperDeveloper


Complément: les séquences (1)Complément: les séquences (1)

� Utililité des séquences:

� Générateur de valeurs séquentielles (compteur), des valeurs de clé primaire…

� Très utilisé en PL/SQL

� Définition d’une séquence:

� Par l'ordre CREATE SEQUENCE:

CREATE SEQUENCE [schéma.] <nom_séquence>

[ INCREMENT BY < 1 | entier >]

[ START WITH entier ]

[ < MAXVALUE entier | NOMAXVALUE > ]

[ < MINVALUE entier | NOMINVALUE > ]

[ < CYCLE | NOCYCLE > ]

[ < CACHE entier | NOCACHE > ]

[ < ORDER | NOORDER > ];

CREATE SEQUENCE [schéma.] <nom_séquence>

[ INCREMENT BY < 1 | entier >]

[ START WITH entier ]

[ < MAXVALUE entier | NOMAXVALUE > ]

[ < MINVALUE entier | NOMINVALUE > ]

[ < CYCLE | NOCYCLE > ]

[ < CACHE entier | NOCACHE > ]

[ < ORDER | NOORDER > ];

Depuis Oracle 11g, il est possible d’utiliser CURRVAL et NEXTVAL dans un bloc

PL/SQL. Les expressions <séquence.CURRVAL> et <séquence.NEXTVAL> peuvent être utilisées à tout endroit où une expression de type Number peut apparaître.


Séquences (2)Séquences (2)

nom_séquence nom enregistré dans le dictionnaire

de données

INCREMENT pas d'incrémentation du numéro de séquence

valeur positive ou négative

START WITH valeur de départ de la séquence

par défaut: = MINVALUE pour séquence asc.

= MAXVALUE pour séquence desc.

MAXVALUE limites maximum ou minimum

MINVALUE de la séquence

NOMAXVALUE limites hautes et basses par défaut

NOMINVALUE (1 à 10E27-1 selon le sens asc. ou



� Exemple de création et de suppression:

SQL> create sequence seqmachine

2 start with 1000

3 increment by 10

4 nomaxvalue

5 nocycle;

Séquence créée.

SQL> drop sequence seqmachine;

Séquence supprimée.

SQL> create sequence seqmachine

2 start with 1000

3 increment by 10

4 nomaxvalue

5 nocycle;

Séquence créée.

SQL> drop sequence seqmachine;

Séquence supprimée.

� Mise en œuvre:� Utilisation d'une séquence dans une ordre SQL (SELECT, INSERT, UPDATE)

en tant que pseudo-colonne par:

nom_séquence.NEXTVAL (génère la valeur suivante de la séquence)

nom_séquence.CURRVAL (donne la valeur courante de la séquence)



SQL> create table machine

2 (nomachine number(4) primary key,

3 nommachine varchar2(15),

4 typemachine char(1));

Table créée.

SQL> insert into machine (nomachine,nommachine,typemachine)

2 values (seqmachine.nextval,'Vrombisseuse','X');

1 ligne créée.


2 values (seqmachine.nextval,'Trimeusse','Z');

1 ligne créée.

SQL> select * from machine;

NOMACHINE NOMMACHINE T

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

1000 Vrombisseuse X

1010 Trimeusse Z

SQL> select seqmachine.currval from dual;

CURRVAL

---------

1010

SQL> create table machine

2 (nomachine number(4) primary key,

3 nommachine varchar2(15),

4 typemachine char(1));

Table créée.


2 values (seqmachine.nextval,'Vrombisseuse','X');

1 ligne créée.


2 values (seqmachine.nextval,'Trimeusse','Z');

1 ligne créée.

SQL> select * from machine;

NOMACHINE NOMMACHINE T

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

1000 Vrombisseuse X

1010 Trimeusse Z

SQL> select seqmachine.currval from dual;

CURRVAL

---------

1010



� Modification:

� Modification possible de certains paramètres d'une séquence

ALTER SEQUENCE [schéma.] nom_séquence

[INCREMENT BY <1|valeur>]

[<MAXVALUE valeur | NOMAXVALUE>]

[<MINVALUE valeur | NOMINVALUE>]

[<CYCLE | NOCYCLE>]

[<CACHE valeur | 20 | NOCACHE>];

ALTER SEQUENCE [schéma.] nom_séquence

[INCREMENT BY <1|valeur>]

[<MAXVALUE valeur | NOMAXVALUE>]

[<MINVALUE valeur | NOMINVALUE>]

[<CYCLE | NOCYCLE>]

[<CACHE valeur | 20 | NOCACHE>];

SQL> alter sequence seqmachine

2 increment by 5

3 maxvalue 5050

4 cycle;

Séquence modifiée.

SQL> select * from user_sequences

2 where sequence_name like 'SEQMACH%';

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER

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

SEQMACHINE 1 5050 5 Y N 20 1015

SQL> alter sequence seqmachine

2 increment by 5

3 maxvalue 5050

4 cycle;

Séquence modifiée.

SQL> select * from user_sequences

2 where sequence_name like 'SEQMACH%';

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER

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

SEQMACHINE 1 5050 5 Y N 20 1015



� Compteur alphanumérique:

� Génération automatique de valeurs alphanumériques pour clé primaire

SQL> select seqmachine.nextval from dual;

NEXTVAL

---------

1030

SQL> select 'MA'||lpad(to_char(seqmachine.nextval),4,'0') numéro

2 from dual;

NUMÉRO

------

MA1035

SQL> select 'MA'||lpad(to_char(seqmachine.currval),4,'0') numéro

2 from dual;

NUMÉRO

------

MA1035

SQL> select seqmachine.nextval from dual;

NEXTVAL

---------

1030

SQL> select 'MA'||lpad(to_char(seqmachine.nextval),4,'0') numéro

2 from dual;

NUMÉRO

------

MA1035

SQL> select 'MA'||lpad(to_char(seqmachine.currval),4,'0') numéro

2 from dual;

NUMÉRO

------

MA1035

� Tout appel à une séquence par NEXTVAL déclenche l'incrémentation !


Séquences avec Séquences avec SqlSql DeveloperDeveloper


CurseursCurseurs

� Un curseur est une zone de mémoire de taille fixe, utilisée par le noyau d'ORACLE pour analyser et interpréter tout ordre SQL.

� Il existe deux types de curseurs :

� Curseur explicite (select ... into) ☺

� Curseur implicite (for) pour simplifier l’écriture. �


Etapes d’un curseur expliciteEtapes d’un curseur explicite

� L'utilisation d'un curseur explicite nécessite 4 étapes :

1)Déclaration : DECLARE

CURSOR nom_curseur IS ordre select;

2)Ouverture : BEGIN

OPEN nom_curseur;

L'ouverture provoque l'allocation mémoire, l'analyse syntaxique et

sémantique du select, le positionnement de verrous (si select ... for update)

3)Traitement des lignes : FETCH nom_curseur INTO liste de variables;

La valeur de chaque colonne est stockée dans une variable réceptrice.

le fetch ramène une seule ligne à la fois.

4)Fermeture : CLOSE nom_curseur;

La place mémoire est libérée à la fermeture du curseur.


Notes : algorithme de lecture d’un fichier séquentielNotes : algorithme de lecture d’un fichier séquentiel


Curseur explicite Curseur explicite TqTq

� Exemple général (Tq): ☺

-- prompt curseur explicite version1 while + %found

-- drop table liste;

-- create table liste (col1 char(20),col2 char(20),col3 char(20));

declare

cursor cv1 is

select nom_emp,service, nom_serv

from emp,service where service=num_serv;

vnom emp.nom_emp%type;

vnumserv emp.service%type;

vservice service.nom_serv%type;

begin

open cv1;

fetch cv1 into vnom, vnumserv, vservice;

while cv1%found

loop

insert into liste values(vnom,vnumserv,vservice);


end loop;

close cv1;

end;

-- select * from liste;

-- prompt curseur explicite version1 while + %found

-- drop table liste;

-- create table liste (col1 char(20),col2 char(20),col3 char(20));

declare

cursor cv1 is






begin

open cv1;


while cv1%found

loop



end loop;

close cv1;

end;

-- select * from liste;


Attributs d’un curseur (1)Attributs d’un curseur (1)

� Ils indiquent l'état (statut) du curseur :

%FOUND %NOTFOUND %ISOPEN %ROWCOUNT

� %FOUND : type booléen

SQL%FOUND (curseur implicite) ou nom_curseur%FOUND

TRUE si une ligne au moins est traitée ou ramenée.

� %NOTFOUND : type booléen

SQL%NOTFOUND ou nom_curseur%NOTFOUND

TRUE si aucune ligne n'est traitée ou ramenée.


Attributs d’un curseur (2)Attributs d’un curseur (2)

� %ISOPEN : type booléen

SQL%ISOPEN est toujours à FALSE

nom_curseur%ISOPEN est à TRUE lorsque le curseur est ouvert.

� %ROWCOUNT : type numerique

SQL%ROWCOUNT contient le nombre de lignes traitées (I,U,D)

avec 0 : le select into ne ramène aucune ligne

1 : le select into ramène 1 ligne

2 : le select into ramène plus d'1 ligne

nom_curseur%ROWCOUNT traduit la nième ligne ramenée par le fetch.


Curseur avec Curseur avec SqlSql DeveloperDeveloper


Curseur explicite avec Curseur explicite avec looploop

prompt curseur explicite version2 loop et %notfound

drop table liste;

create table liste (col1 char(20),col2 char(20),col3 char(20));

declare

cursor cv2 is






begin

open cv2;

loop


exit when cv2%notfound;


end loop;

close cv2;

commit;

end; /

select * from liste;

prompt curseur explicite version2 loop et %notfound

drop table liste;


declare

cursor cv2 is






begin

open cv2;

loop




end loop;

close cv2;

commit;

end; /

select * from liste;


Curseur FORCurseur FOR

� Curseur avec boucle FOR: �

PROMPT nombre de salaires ?

ACCEPT nombre

drop table résultat;

create table résultat (col1 char(20),col2 char(20))

/

DECLARE

CURSOR cv3 IS SELECT nom_emp,salaire

FROM emp ORDER BY salaire DESC;

vnom emp.nom_emp%TYPE;

vsal emp.salaire%TYPE;

BEGIN

OPEN cv3;

FOR i IN 1..&nombre

LOOP

FETCH cv3 INTO vnom,vsal;

INSERT INTO résultat VALUES (vnom,vsal);

END LOOP;

CLOSE cv3;

END;

/

PROMPT nombre de salaires ?

ACCEPT nombre

drop table résultat;

create table résultat (col1 char(20),col2 char(20))

/

DECLARE

CURSOR cv3 IS SELECT nom_emp,salaire

FROM emp ORDER BY salaire DESC;

vnom emp.nom_emp%TYPE;

vsal emp.salaire%TYPE;

BEGIN

OPEN cv3;

FOR i IN 1..&nombre

LOOP

FETCH cv3 INTO vnom,vsal;

INSERT INTO résultat VALUES (vnom,vsal);

END LOOP;

CLOSE cv3;

END;

/


Curseurs implicites (1)Curseurs implicites (1)

� Curseur implicite = simplification d’écriture �

� Déclaration implicite d'une structure dont les éléments sont d'un type identique aux colonnes ramenées par le curseur:

DECLARE

CURSOR nom_curseur is ordre select;

nom_structure nom_curseur%ROWTYPE;

BEGIN

� les éléments de la structure sont identifiés par :nom_structure.nom_colonne

� la structure est renseignée par le FETCH :

fetch nom_curseur INTO nom_structure;



� Forme condensée utilisant la boucle FOR:DECLARE

CURSOR nom_curseur IS ordre select ;

BEGIN

FOR nom_structure IN nom_curseur

LOOP

-- traitements

END LOOP;

prompt curseur version1 implicite forme condensée

drop table liste;


declare

cursor cv4 is select nom_emp,service,nom_serv


begin

for ligne in cv4

loop

insert into liste

values(ligne.nom_emp,ligne.service,ligne.nom_serv);

end loop;

end;

prompt curseur version1 implicite forme condensée

drop table liste;


declare

cursor cv4 is select nom_emp,service,nom_serv


begin

for ligne in cv4

loop

insert into liste

values(ligne.nom_emp,ligne.service,ligne.nom_serv);

end loop;

end;



� Déclaration du curseur directement dans une boucle FOR:FOR nom_structure IN ( ordre select )

LOOP

traitement;

END LOOP;

Il n'y a plus de déclaration du curseur.

prompt curseur version2 implicite et boucle for

pause

drop table liste;


begin

for cv5 in

(select nom_emp,service,nom_serv

from emp,service where service=num_serv)

loop

insert into liste

values(cv5.nom_emp,cv5.service,cv5.nom_serv);

end loop;

end;

prompt curseur version2 implicite et boucle for

pause

drop table liste;


begin

for cv5 in

(select nom_emp,service,nom_serv

from emp,service where service=num_serv)

loop

insert into liste

values(cv5.nom_emp,cv5.service,cv5.nom_serv);

end loop;

end;


Curseurs paramétrés(1)Curseurs paramétrés(1)

� Curseurs paramétrés:

� Ils permettent de pouvoir réutiliser un même curseur avec des valeurs différentes dans un même bloc PL/SQL.

� Les paramètres sont de type CHAR, NUMBER, DATE, BOOLEAN.

� Le passage des valeurs se fait à l'ouverture du curseur.

DECLARECURSOR nom_curseur(param1 TYPE, param2 TYPE, ...)

IS ordre select ....;-- utilisant les paramètres para1 et para2 –

BEGINOPEN nom_curseur (val1, val2, ....);

-- ou FOR nom_structure IN nom_curseur (val1, val2, ...);

DECLARECURSOR nom_curseur(param1 TYPE, param2 TYPE, ...)

IS ordre select ....;-- utilisant les paramètres para1 et para2 –

BEGINOPEN nom_curseur (val1, val2, ....);

-- ou FOR nom_structure IN nom_curseur (val1, val2, ...);


Curseurs paramétrés(2)Curseurs paramétrés(2)

� Exemple:

prompt curseur explicite paramétré

accept numservice

declare

cursor cv6 (param1 number) is


from emp,service where service=num_serv

and num_serv=param1;




begin

open cv6(&numservice);


while cv6%found

loop



end loop;

close cv6;

End;

prompt curseur explicite paramétré

accept numservice

declare

cursor cv6 (param1 number) is


from emp,service where service=num_serv

and num_serv=param1;




begin

open cv6(&numservice);


while cv6%found

loop



end loop;

close cv6;

End;


Curseurs de mise à jour(1)Curseurs de mise à jour(1)

� Curseur avec la clause FOR UPDATE et CURENT OF:

� Ce type de curseur permet de verrouiller les lignes d’une table interrogée par un curseur dans le but de mettre à jour la table, sans qu’un autre utilisateur ne la modifie en même temps (accès concurrents).

� Permet d'accéder directement en modification (ordre UPDATE) ou en suppression (ordre DELETE) à la ligne que vient de ramener l'ordre FETCH.

� Il faut au préalable réserver les lignes lors de la déclaration du curseur par un verrou d'intention :

FOR UPDATE OF <nom_colonne>

� La modification ou la suppression utilisent la clause :

WHERE CURRENT OF nom_curseur


Curseurs de mise à jour(2)Curseurs de mise à jour(2)

prompt curseur pour mise à jour

prompt changement de service de tous les employés du bar

insert into service values (5,'fast-food');

select * from emp,service

where service=num_serv and nom_serv like 'bar%';

declare

vbar service.nom_serv%type := 'bar';




cursor cv6 is


from emp,service

where service=num_serv

and nom_serv like vbar

for update of service;

begin ...

prompt curseur pour mise à jour

prompt changement de service de tous les employés du bar

insert into service values (5,'fast-food');


where service=num_serv and nom_serv like 'bar%';

declare

vbar service.nom_serv%type := 'bar';




cursor cv6 is


from emp,service


and nom_serv like vbar

for update of service;

begin ...

� Exemple (début):


Curseurs de mise à jour (3)Curseurs de mise à jour (3)

begin

open cv1;


while cv1%found

loop

update emp set service=5

where current of cv1;


end loop;

close cv1;

end;

/



and nom_serv like 'fast%';

begin

open cv1;


while cv1%found

loop

update emp set service=5

where current of cv1;


end loop;

close cv1;

end;

/



and nom_serv like 'fast%';

� Exemple (suite):


Traitement des erreurs (1)Traitement des erreurs (1)

� La section EXCEPTION permet d'affecter un traitement approprié aux erreurs survenues lors de l'exécution d'un bloc PL/SQL.

� Deux types d'erreurs sont possibles :

� erreurs spécifiques à l’application (user exception)

� erreurs internes ORACLE (SQLCODE !=0)

� Déclaration d’une exception spécifique (user):

� Déclarer un nom pour chaque erreur dans section DECLARE

� Ecrire le traitement à effectuer dans la partie EXCEPTION

� Les erreurs Oracle les plus courantes sont prédéfinies

� Il y a sortie du bloc PL/SQL après traitement de l’erreur



� Le code PL/SQL qui traite l’erreur est écrit dans la section EXCEPTION

� Sortie du bloc après un traitement exception :

� il faut découper le programme en sous bloc PL/SQL ou gérer soit même les traitements d'erreurs en utilisant les attributs du curseur.

� Le WHEN OTHERS est toujours en dernier

DECLARE

<nom_erreur> EXCEPTION;

BEGIN

IF <condition_detection>

THEN RAISE <nom_erreur>;

EXCEPTION

WHEN <nom_erreur> THEN

<traitement erreur>;

[WHEN OTHERS THEN <traitements>;]

End;

DECLARE

<nom_erreur> EXCEPTION;

BEGIN

IF <condition_detection>

THEN RAISE <nom_erreur>;

EXCEPTION

WHEN <nom_erreur> THEN

<traitement erreur>;

[WHEN OTHERS THEN <traitements>;]

End;



-- exemple exception utilisateur

declare

gros_salaire exception;

cursor cv1 is select nom_emp, salaire, nom_serv



vsalaire emp.salaire%type;


begin

dbms_output.enable;

open cv1;

loop

fetch cv1 into vnom, vsalaire, vservice;


if vsalaire > 9999 then raise gros_salaire;

end if;

end loop;

close cv1;

exception

when gros_salaire then

dbms_output.put_line('Exception gros_salaire! :'||vnom||' '||vsalaire);

when others then null;

end;

-- exemple exception utilisateur

declare

gros_salaire exception;

cursor cv1 is select nom_emp, salaire, nom_serv



vsalaire emp.salaire%type;


begin

dbms_output.enable;

open cv1;

loop

fetch cv1 into vnom, vsalaire, vservice;


if vsalaire > 9999 then raise gros_salaire;

end if;

end loop;

close cv1;

exception

when gros_salaire then

dbms_output.put_line('Exception gros_salaire! :'||vnom||' '||vsalaire);

when others then null;

end;



� Erreurs Oracle prédéfinies:

� Elles ne sont ni déclarées, ni détectées. Elles ont des noms pré-définis mais on peut

les changer. Elles sont associées à un traitement dans la partie EXCEPTION

� Quelques erreurs courantes:

DUP_VAL_ON_INDEX -1

FETCH_OUT_OF -1002

INVALID_CURSOR -1001

INVALID_NUMBER -1722

NO_DATA_FOUND -1403

TOO_MANY_ROWS -1422

VALUE_ERROR -6502

ZERO_DIVIDE -1476

� SQLCODE renvoie le numéro de l'erreur courante.

� SQLERRM [(code erreur)] renvoie le libellé de l'erreur courante.



-- exemple exception too_many_rows

declare




begin

dbms_output.enable;

select nom_emp,service, nom_serv into vnom,vnumserv,vservice

from emp,service

where service=num_serv;

exception

when TOO_MANY_ROWS then

dbms_output.put('Exception TOO_MANY_ROWS: ');

dbms_output.put_line('Il faut utiliser un curseur!');

dbms_output.put_line('SQLCODE='||to_char(SQLCODE));

dbms_output.put_line('SQLERRM='||SQLERRM);

when others then



end;

/

-- exemple exception too_many_rows

declare




begin

dbms_output.enable;

select nom_emp,service, nom_serv into vnom,vnumserv,vservice

from emp,service

where service=num_serv;

exception

when TOO_MANY_ROWS then

dbms_output.put('Exception TOO_MANY_ROWS: ');

dbms_output.put_line('Il faut utiliser un curseur!');



when others then



end;

/


Exercice sur les curseursExercice sur les curseurs

La base de données de la société VOYAGE-ECO comprend 4 tables :

CHAUFFEUR (n°car, nom )

VOYAGE (n°car#, datevoyage, ville# )

PRIME (nom, date versement, montant)

DESTINATION (ville, seuil, base)

Chaque voyage effectué donne lieu à l'insertion d'une ligne dans la table VOYAGE.

La ville représente la destination du voyage.

Un chauffeur ne conduit qu'un seul car, un car n'est conduit que par un seul chauffeur.

Les primes sont attribuées en fonction du nombre de voyages effectués par destination, du seuil et de la base.

Ex : La ligne (Bruxelles, 10, 120) dans la table DESTINATION signifie que les chauffeurs ayant effectués plus de 10 voyages vers Bruxelles auront une prime égale à 120€ x (nb de voyage - 10).

Le chauffeur ayant réalisé le plus de voyage sur une destination aura également une augmentation de 10% de la prime acquise sur cette destination.

Le traitement est effectué chaque semestre et zéro ou une ligne par chauffeur est insérée dans la table PRIME.

Ensuite, les lignes de la table VOYAGE sont supprimées.

Ecrire le programme PL/SQL qui réalise le traitement semestriel des primes

La base de données de la société VOYAGE-ECO comprend 4 tables :

CHAUFFEUR (n°car, nom )

VOYAGE (n°car#, datevoyage, ville# )

PRIME (nom, date versement, montant)

DESTINATION (ville, seuil, base)

Chaque voyage effectué donne lieu à l'insertion d'une ligne dans la table VOYAGE.

La ville représente la destination du voyage.

Un chauffeur ne conduit qu'un seul car, un car n'est conduit que par un seul chauffeur.

Les primes sont attribuées en fonction du nombre de voyages effectués par destination, du seuil et de la base.

Ex : La ligne (Bruxelles, 10, 120) dans la table DESTINATION signifie que les chauffeurs ayant effectués plus de 10 voyages vers Bruxelles auront une prime égale à 120€ x (nb de voyage - 10).

Le chauffeur ayant réalisé le plus de voyage sur une destination aura également une augmentation de 10% de la prime acquise sur cette destination.

Le traitement est effectué chaque semestre et zéro ou une ligne par chauffeur est insérée dans la table PRIME.

Ensuite, les lignes de la table VOYAGE sont supprimées.

Ecrire le programme PL/SQL qui réalise le traitement semestriel des primes


Variables curseurs (REF CURSORS)Variables curseurs (REF CURSORS)

� REF CURSOR:� Une variable curseur est un curseur dynamique qui n’est pas associé à

une requête donnée comme un curseur classique (statique).

� Une variable curseur permet au curseur d’évoluer au cours du programme.

� Déclarée en deux étapes:

�Déclaration du type

�Déclaration de la variable du type

DECLARETYPE nomTypeCurseurDyn IS REF CURSOR [ RETURN typeRetourSQL ];...

nom_du_curseur_dyn nomTypeCurseurDyn;

DECLARETYPE nomTypeCurseurDyn IS REF CURSOR [ RETURN typeRetourSQL ];...

nom_du_curseur_dyn nomTypeCurseurDyn;

� le type du retour est en général la structure d’un enregistrement d’une table.

� L’ouverture du curseur est commandée par l’instruction OPEN <curseur> FOR <requète SQL> écrite dans la section BEGIN.

� La lecture du curseur s’opère toujours avec l’instruction FETCH



DECLARE

TYPE ref_pil IS REF CURSOR;

cur_pil ref_pil;

vnopil transair.pilote.nopilote%TYPE;

vnom transair.pilote.nompil%TYPE;

vadr transair.pilote.adresse%TYPE;

vsal transair.pilote.salaire%TYPE;

BEGIN

dbms_output.enable;

OPEN cur_pil FOR SELECT nopilote,nompil,adresse

from transair.pilote where comm is not null;

FETCH cur_pil INTO vnopil,vnom,vadr;

WHILE cur_pil%FOUND LOOP

dbms_output.put_line(vnopil||':'||vnom||':'||vadr);


END LOOP;

CLOSE cur_pil;

dbms_output.put_line('++++++++++++++++');

-- suite sur la diapo suivante

DECLARE

TYPE ref_pil IS REF CURSOR;

cur_pil ref_pil;

vnopil transair.pilote.nopilote%TYPE;

vnom transair.pilote.nompil%TYPE;

vadr transair.pilote.adresse%TYPE;

vsal transair.pilote.salaire%TYPE;

BEGIN

dbms_output.enable;

OPEN cur_pil FOR SELECT nopilote,nompil,adresse




dbms_output.put_line(vnopil||':'||vnom||':'||vadr);


END LOOP;

CLOSE cur_pil;


-- suite sur la diapo suivante



-- suite du ref cursor


OPEN cur_pil FOR SELECT nopilote,nompil,salaire


FETCH cur_pil INTO vnopil,vnom,vsal;


dbms_output.put_line(vnopil||':'||vnom||':'||vsal);


END LOOP;

CLOSE cur_pil;

END;

-- suite du ref cursor


OPEN cur_pil FOR SELECT nopilote,nompil,salaire




dbms_output.put_line(vnopil||':'||vnom||':'||vsal);


END LOOP;

CLOSE cur_pil;

END;

� On observe dans ce programme que le même curseur (cur_pil) sert pour deux requêtes SQL différentes.

� Ce type de curseur peut être passé en paramètre dans une procédure cataloguée.


NotesNotes


Procédures et Fonctions cataloguéesProcédures et Fonctions cataloguées

� Une procédure est un sous-programme PL/SQL (ou Java) qui effectue un traitement particulier.

� Les procédures sont compilées et stockées dans la base de données, comme tout autre objet (donnée) manipulé par le moteur du SGBD.

� Amélioration des performances: les procédures et fonctions n’ont plus besoin d'être analysées une seconde fois à l'exécution.

� Les procédures chargées en mémoire pour exécution seront partagées et réutilisées par tous les objets qui la demandent (applications)

� Une fonction est un sous-programme qui renvoie une valeur.

� Les procédures et fonctions permettent d’encapsuler les données d’une base.


Procédures (1)Procédures (1)

� Une procédure est une unité de traitement qui peut contenir :

des commandes SQL de manipulation des données (LMD),

des instructions PL/SQL,

des variables, des constantes, des curseurs, un gestionnaire d'erreurs.

� La structure générale d'une procédure à partir du bloc PL/SQL est la suivante :

PROCEDURE <nomprocedure> [ (parametres,...)] { IS | AS }Déclaration des variables locales;

BEGINInstructions SQL et PL/SQL;

EXCEPTIONTraitement des exceptions (gestion des erreurs);

END [nomprocedure];

PROCEDURE <nomprocedure> [ (parametres,...)] { IS | AS }Déclaration des variables locales;

BEGINInstructions SQL et PL/SQL;

EXCEPTIONTraitement des exceptions (gestion des erreurs);

END [nomprocedure];

� Le mot-clé Declare a été remplacé par le mot-clé Procedure.



� Création d'une procédure stockée :

� L’option OR REPLACE permet de spécifier au système le remplacement de la procédure si elle existe déjà dans la base de données.

� Les paramètres sont définis selon la syntaxe suivante :

nomparamètre [ IN | OUT | IN OUT ] type [ { := default } valeur]IN : indique que la variable est passée en entrée,OUT : indique que la variable est renseignée par la procédure puis renvoyée àl'appelantIN OUT : passage par référence Le type du paramètre ne doit pas contenir d'indication sur la longueur.

� La partie déclarative de la procédure constitue l’interface

CREATE [OR REPLACE] PROCEDURE [schema].<nomprocedure> [ (parametres,...)]{ IS | AS }

Déclaration des variables locales;BEGIN

Instructions SQL et PL/SQL;EXCEPTION

des exceptions;END;

CREATE [OR REPLACE] PROCEDURE [schema].<nomprocedure> [ (parametres,...)]{ IS | AS }

Déclaration des variables locales;BEGIN

Instructions SQL et PL/SQL;EXCEPTION

des exceptions;END;



� Exemple: procédure AugmenterSalaire:

CREATE OR REPLACE PROCEDURE AugmenterSalaire

(NumSal integer, Montant Real) ISSalaireActuel Real;SalaireNull Exception;

BEGINSelect sal into SalaireActuel From emp

Where empno = NumSal ;

If (SalaireActuel is NULL)

then raise SalaireNull ;

else Update emp set sal = sal + Montant

Where empno = NumSal ; End If;

EXCEPTIONWhen no_data_found then

insert into emp_audit values (NumSal,'Inconnu');When SalaireNull then

insert into emp_audit values (NumSal,'Salaire Null');

END AugmenterSalaire;

CREATE OR REPLACE PROCEDURE AugmenterSalaire

(NumSal integer, Montant Real) ISSalaireActuel Real;SalaireNull Exception;

BEGINSelect sal into SalaireActuel From emp

Where empno = NumSal ;

If (SalaireActuel is NULL)

then raise SalaireNull ;

else Update emp set sal = sal + Montant

Where empno = NumSal ; End If;

EXCEPTIONWhen no_data_found then

insert into emp_audit values (NumSal,'Inconnu');When SalaireNull then

insert into emp_audit values (NumSal,'Salaire Null');

END AugmenterSalaire;



� Modification d'une procédure :� recompilation avec la commande:

ALTER PROCEDURE [schema].nomprocedure COMPILE ;

� Suppression d'une procédure :DROP PROCEDURE [schema].nomprocedure ;

� Exécution : une procédure est appelée comme une instruction PL/SQL� Ex: appel pour augmenter le salaire d'un employé dans un autre sous-programme :

AugmenterSalaire(NumeroSalarie, Augmentation) ;

AugmenterSalaire(100, 1000);

� A partir d'un programme applicatif (programme hôte)

EXEC SQL EXECUTE

BEGIN

AugmenterSalaire(100, 1000);

END;

END-EXEC ;

� A partir de Sql*Plus (EXECUTE):

SQL> EXECUTE AugmenterSalaire(7788,1000) ;

SQL> EXECUTE AugmenterSalaire(8000,1000) ;



� Mise au point d'une procédure (déboguage) avec SQ*Plus:La commande SHOW ERRORS permet de récupérer les messages suivants :

ERRORS FOR PROCEDURE supprimer_emp ;LINE/COL ERROR------------ -------------------------------------------3/24 PL/SQL-00103 : Encountered the symbol "EMPNO" when ...5/0 PL/SQL-00103 : Encountered the symbol "END" when ...

� Le développeur peut interroger le dictionnaire de données pour obtenir des informations sur les erreurs. Les vues du dictionnaire s'appellent :

USER_ERRORS ALL_ERRORS DBA_ERRORS

� Le texte source peut être obtenu à partir des vues suivantes :

USER_SOURCE ALL_SOURCE DBA_SOURCE

� Le développeur peut également utiliser le package public DBMS_OUTPUT pour mettre au point ses procédures.

� SQL Developer possède un débogueur intégré!� Le compte doit avoir le privilège système DEBUG CONNECT SESSION


Fonctions (1)Fonctions (1)

� Une fonction est un sous-programme qui renvoie une valeur.

� La structure d'une fonction est identique à celle d'une procédure, mais les fonctions possèdent une clause RETURN.

� Les arguments sont définis selon la syntaxe suivante :

(nomargument [IN] type [,nomargument [IN] type] ... )

� Le type de l'argument ne doit pas contenir d'indication sur la longueur.

� Le nom de la fonction est valorisé par une valeur conforme au type par l'instruction Return.

� Une fonction doit être déclarée avant d'être utilisée.

� Ne pas confondre les deux clauses RETURN: Return type et Return (valeur)

FUNCTION nomfonction [ (argument,...)]RETURN type { IS | AS }

Déclarations de variables locales;BEGIN

Instructions SQL et PL/SQL;RETURN (valeur);

EXCEPTIONTraitement des exceptions;

END [nomfonction];

FUNCTION nomfonction [ (argument,...)]RETURN type { IS | AS }


Instructions SQL et PL/SQL;RETURN (valeur);


END [nomfonction];



� Exemple de fonction VerifierSalaire:

CREATE FUNCTION VerifierSalaire

(Numgrade integer, Montant Real)

RETURN boolean IS

MinSalaire Real;MaxSalaire Real;

BEGIN

Select losal, hisal into MinSalaire, MaxSalaire

From salgradWhere grade = Numgrade ;

RETURN ( (Montant >= MinSalaire) and (Montant <= MaxSalaire) );

END VerifierSalaire;

CREATE FUNCTION VerifierSalaire

(Numgrade integer, Montant Real)

RETURN boolean IS

MinSalaire Real;MaxSalaire Real;

BEGIN

Select losal, hisal into MinSalaire, MaxSalaire

From salgradWhere grade = Numgrade ;

RETURN ( (Montant >= MinSalaire) and (Montant <= MaxSalaire) );

END VerifierSalaire;



� Création d’une fonction stockée:

� Recompilation d’une fonction:ALTER FUNCTION [schema].nomfonction COMPILE ;

� Suppression d’une fonction:DROP FUNCTION [schema].nomfonction ;

� Exécution d’une fonction dans un programme PL/SQL:a:= VerifierSalaire(100, 1000)

EXEC SQL EXECUTE

BEGIN

a:=VerifierSalaire(100, 1000);

END;

END EXEC;

CREATE [OR REPLACE] FUNCTION nomfonction [ (argument,.)]RETURN type { IS | AS }


Instructions SQL et PL/Sql;RETURN(Valeur);


END ;

CREATE [OR REPLACE] FUNCTION nomfonction [ (argument,.)]RETURN type { IS | AS }


Instructions SQL et PL/Sql;RETURN(Valeur);


END ;



� Retrouver la liste des sous-programmes créés dans la base:Select owner, object_name, object_type, status

from dba_objects

where object_type in (‘PROCEDURE,’FUNCTION’,’TRIGGER’,’PACKAGE’)

and owner not in (‘SYS’,’SYSTEM’)

order by owner, object_name;

� Retrouver le code d’un sous-programme créé dans la base:Select line, text from dba_source

where owner =‘ZORRO’

and name = ‘NOM_PROG’

order by line;


Procédures et fonctions avec Procédures et fonctions avec SqlSql DeveloperDeveloper


Déboguer une procédure avec Déboguer une procédure avec SqlSql DeveloperDeveloper


Tableaux, structures et collections en PL/SQLTableaux, structures et collections en PL/SQL

� Il existe des types de données complexes utilisées en PL/SQL:

� Rappel sur typage et déclaration de variable simples

� Les collections de type RECORD

� Les collections de type TABLE ou VARRAY

� Méthodes communes



� Typage et déclaration de variables simples:

� Exemples de déclaration:

� Il existe plusieurs catégories de types de variables :

�Simples (INTEGER, NUMBER, DATE, CHAR, BOOLEAN, VARCHAR, ...) = scalaire

�Type composé (RECORD, TABLE, VARRAY , NESTED TABLE)

�Type référence (utilisé en Objet-Relationnel)

�Pointeur de LOB (CLOB, BLOB, BFILES, NCLOB )

�Type 'dynamique' %TYPE qui définit une variable du type de la colonne d'une table existante: nom emp.ename%TYPE

X NUMBER NOT NULL :=10.40;

C VARCHAR(100) := 'Coucou';

N CONSTANT INTEGER DEFAULT 100;

V BOOLEAN := TRUE;

X NUMBER NOT NULL :=10.40;

C VARCHAR(100) := 'Coucou';

N CONSTANT INTEGER DEFAULT 100;

V BOOLEAN := TRUE;

Nom_var [constant]type[NOT NULL][:= valeur | DEFAULT expression];Nom_var [constant]type[NOT NULL][:= valeur | DEFAULT expression];



� Les collections (variables) de type RECORD:

� Elles permettent de déclarer une variable de type 'enregistrement' ou 'ligne d'une table’

� C'est donc un type structuré décomposable en colonnes élémentaires.

� Les RECORDS peuvent être imbriqués et contenir d'autres RECORDS.

� Les RECORDS peuvent être affectés de manière élémentaire, champ par champ, ou de manière globale grâce à un SELECT INTO :

rec_emp.salaire := 2400;SELECT * INTO rec_emp FROM emp

WHERE empno = &no_saisi;

DECLARE

TYPE t_rec_emp IS RECORD

(nom emp.ename%TYPE, salaire emp.sal%TYPE, comission

emp.comm%TYPE);

rec_emp t_rec_emp;

DECLARE

TYPE t_rec_emp IS RECORD

(nom emp.ename%TYPE, salaire emp.sal%TYPE, comission

emp.comm%TYPE);

rec_emp t_rec_emp;

TYPE type_enr IS RECORD

( liste_de_types_avec_%TYPE_ou_non | nom_table%ROWTYPE );

TYPE type_enr IS RECORD

( liste_de_types_avec_%TYPE_ou_non | nom_table%ROWTYPE );



� Les collections de type TABLE (variables tableaux):

Une table PL/SQL:

� c’est une collection ordonnée d’élément du même type

� accessible uniquement en PL/SQL

� stockée en mémoire, elle peut grandir dynamiquement

� utilise des index non consécutif

� ils ont le même format que des champs d’une table

� on n’utilise pas SQL pour s’en servir

Définition :

Declaration : mon_tableau nom_tableau_pl ;

TYPE nom_tableau_pl IS TABLE OF

< type_scalaire | variable%TYPE | table.colonne%TYPE [NOT

NULL] | table%ROWTYPE | type_complexe >

INDEX BY BINARY_INTEGER;

TYPE nom_tableau_pl IS TABLE OF

< type_scalaire | variable%TYPE | table.colonne%TYPE [NOT

NULL] | table%ROWTYPE | type_complexe >

INDEX BY BINARY_INTEGER;



� Exemple 1: tableau de 2 entiers

-- table de multiplication par 8 et par 9...

declare

type tablemul is record ( par8 number, par9 number);

type tabledentiers is table of tablemul

index by binary_integer;

ti tabledentiers;

i number;

begin

for i in 1..10 loop

ti(i).par9 := i*9 ;

ti(i).par8:= i*8;

dbms_output.put_line (i||'*8='||ti(i).par8||'

'||i||'*9='||ti(i).par9 );

end loop;

end;

-- table de multiplication par 8 et par 9...

declare

type tablemul is record ( par8 number, par9 number);

type tabledentiers is table of tablemul


ti tabledentiers;

i number;

begin

for i in 1..10 loop

ti(i).par9 := i*9 ;

ti(i).par8:= i*8;

dbms_output.put_line (i||'*8='||ti(i).par8||'

'||i||'*9='||ti(i).par9 );

end loop;

end;



� Exemple 2: tableau de champs numériques

-- on va lire les salaires de la table emp...

declare

type tsal is table of emp.sal%type


tasal tsal;

i number;

cursor cur is select sal from emp;

begin

open cur;

i :=1;

loop

exit when cur%notfound;

fetch cur into tasal(i) ;

dbms_output.put_line ('salaire ...'||tasal(i));

i := i + 1;

end loop;

end;

-- on va lire les salaires de la table emp...

declare

type tsal is table of emp.sal%type


tasal tsal;

i number;


begin

open cur;

i :=1;

loop



dbms_output.put_line ('salaire ...'||tasal(i));

i := i + 1;

end loop;

end;



� Les collections VARRAY:

� Un VARRAY stocke un ensemble ordonné d'éléments.

� Chaque élément dispose d'un index qui lui est associée.

� Spécifier la taille maximale et le type des éléments stockés dans le VARRAY lors de la création.

� Dans Oracle, les indices de tableau commencent à partir du 1er, et non de 0 (comme en C et Java).

� On utilise des VARRAY quand on connaît à l’avance la taille de

l’ensemble de données et que cette taille est très stable.

� Autre possibilité: on peut utiliser le DDL SQL CREATE TYPE.

TYPE nom_type IS VARRAY (taille) OF

< type_colonne | type_record >;

TYPE nom_type IS VARRAY (taille) OF

< type_colonne | type_record >;

DECLARE

TYPE t_tab_emp IS VARRAY (1000) OF emp%ROWTYPE;

tab_emp t_tab_emp; -- déclaration de la variable !

DECLARE

TYPE t_tab_emp IS VARRAY (1000) OF emp%ROWTYPE;

tab_emp t_tab_emp; -- déclaration de la variable !



Exemple simple de varray:

DECLARE

TYPE TYPE_TAB IS VARRAY (100) OF VARCHAR2(1);

TAB TYPE_TAB := TYPE_TAB(1,2,3,4);

BEGIN

TAB(1) := 'T';

TAB(2) := 'O';

TAB(3) := 'T';

TAB(4) := 'O';

FOR I IN 1..4 LOOP

DBMS_OUTPUT.PUT_LINE(TAB(I));

END LOOP;

END;

DECLARE

TYPE TYPE_TAB IS VARRAY (100) OF VARCHAR2(1);

TAB TYPE_TAB := TYPE_TAB(1,2,3,4);

BEGIN

TAB(1) := 'T';

TAB(2) := 'O';

TAB(3) := 'T';

TAB(4) := 'O';

FOR I IN 1..4 LOOP

DBMS_OUTPUT.PUT_LINE(TAB(I));

END LOOP;

END;



� Description des attributs / méthodes utilisables avec des TABLESet VARRAY PL/SQL:

ajoute un élément dans la tablenom_table_sql.EXTEND

supprime les éléments de n à m nom_table_sql.DELETE (n,m)

supprime l’élément n nom_table_sql.DELETE (n)

supprime toute la table nom_table_sql.DELETE

retourne le nombre d’éléments de la table nom_table_sql.COUNT



élément du tableaunom_table_sql.PRIOR(n)

Retourne l’élément après ou avant le nièmenom_table_sql.NEXT(n)

nom_table_sql.TRIM(n)

élément suivant nom_table_sql.TRIM

retourne la valeur du dernier index nom_table_sql.LAST

retourne le premier index du tableaunom_table_sql.FIRST



-- exemple d’utilisation des attributs / méthodes de TABLES et VARRAY :

declare

type tsal is table of emp.sal%type index by binary_integer;

tasal tsal;

i number;


begin

open cur;

i :=1;

loop



i := i + 1;

end loop;

close cur;

dbms_output.put_line('premier élément:'||tasal(tasal.FIRST) );

dbms_output.put_line('dernier élément:'||tasal(tasal.LAST) );

dbms_output.put_line('nombre d élément : '||tasal.COUNT);

dbms_output.put_line('suppresion des éléments'||

tasal.next(1)||' à '||tasal.prior(tasal.COUNT));

tasal.delete(2,tasal.COUNT-1);


end;

-- exemple d’utilisation des attributs / méthodes de TABLES et VARRAY :

declare

type tsal is table of emp.sal%type index by binary_integer;

tasal tsal;

i number;


begin

open cur;

i :=1;

loop



i := i + 1;

end loop;

close cur;

dbms_output.put_line('premier élément:'||tasal(tasal.FIRST) );

dbms_output.put_line('dernier élément:'||tasal(tasal.LAST) );


dbms_output.put_line('suppresion des éléments'||

tasal.next(1)||' à '||tasal.prior(tasal.COUNT));

tasal.delete(2,tasal.COUNT-1);


end;



Autre exemple de varray:SQL> declare

2 type month_va is varray(13) of VARCHAR2(20);3 v_month_va month_va;4 v_count_nr number;5 begin6 v_month_va:=month_va('A','B','C','D','E','F','G');7 DBMS_OUTPUT.put_line('Length:'||v_month_va.count);89 v_month_va.extend;

10 v_month_va(v_month_va.last):='Null';11 DBMS_OUTPUT.put_line('Length:'||v_month_va.count);1213 for i in v_month_va.first..v_month_va.last14 loop15 DBMS_OUTPUT.put_line('v_month_va(i): '||v_month_va(i));16 end loop;17 end;18 /

Length:7Length:8v_month_va(i): Av_month_va(i): Bv_month_va(i): Cv_month_va(i): Dv_month_va(i): Ev_month_va(i): Fv_month_va(i): Gv_month_va(i): Null

PL/SQL procedure successfully completed.

pl/sql (#1) définition (1)ljv.free.fr/orapdf/plsql_bases_2010.pdf1/76 pl/sql (#1) jean-luc goerig...

Documents