pl/sql (#1) définition (1)ljv.free.fr/orapdf/plsql_bases_2010.pdf1/76 pl/sql (#1) jean-luc goerig...
Post on 16-Mar-2020
9 Views
Preview:
TRANSCRIPT
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)
Jean-Luc GOERIG2/76 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
Jean-Luc GOERIG3/76 PL/SQL (#1)
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
Jean-Luc GOERIG4/76 PL/SQL (#1)
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
-- Section facultative
-- Traitement des exceptions (gestion des erreurs)
END;
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
-- Section facultative
-- Traitement des exceptions (gestion des erreurs)
END;
� Chaque instruction de n’importe quelle section doit se terminer par un ‘;’
Jean-Luc GOERIG5/76 PL/SQL (#1)
-- 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
where produit=‘chaise’;
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
where produit=‘chaise’;
-- contrôle du stock suffisant
if qte_stock > 0 then
update inventaire set quantite = quantite – 1
where produit=‘chaise’;
insert into achat
values (‘chaise’,sysdate);
else
insert into acheter
values (‘Plus de chaise!’,sysdate);
end if;
commit;
END;
ExempleExemple
Jean-Luc GOERIG6/76 PL/SQL (#1)
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 …
Jean-Luc GOERIG7/76 PL/SQL (#1)
Variables (2)Variables (2)
� 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 …
Jean-Luc GOERIG8/76 PL/SQL (#1)
Variables (3)Variables (3)
� 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 …
Jean-Luc GOERIG9/76 PL/SQL (#1)
Variables (4)Variables (4)
� 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;
Jean-Luc GOERIG10/76 PL/SQL (#1)
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 <condition2> THEN instructions2;
…
WHEN <conditionN> THEN instructionsN;
[ ELSE instructionsZ; ]
END CASE;
CASE <variable>
WHEN <condition1> THEN instructions1;
WHEN <condition2> THEN instructions2;
…
WHEN <conditionN> THEN instructionsN;
[ ELSE instructionsZ; ]
END CASE;
Jean-Luc GOERIG11/76 PL/SQL (#1)
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;
Jean-Luc GOERIG12/76 PL/SQL (#1)
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;
Jean-Luc GOERIG13/76 PL/SQL (#1)
Structures itératives (3)Structures itératives (3)
� 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;
Jean-Luc GOERIG14/76 PL/SQL (#1)
Structures itératives (4)Structures itératives (4)
� 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;
...
Jean-Luc GOERIG15/76 PL/SQL (#1)
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.
Jean-Luc GOERIG16/76 PL/SQL (#1)
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;
Jean-Luc GOERIG17/76 PL/SQL (#1)
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
Jean-Luc GOERIG18/76 PL/SQL (#1)
PL/SQL avec PL/SQL avec SqlSql DeveloperDeveloper
Jean-Luc GOERIG19/76 PL/SQL (#1)
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.
Jean-Luc GOERIG20/76 PL/SQL (#1)
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
Jean-Luc GOERIG21/76 PL/SQL (#1)
Séquences (3)Séquences (3)
� 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)
Jean-Luc GOERIG22/76 PL/SQL (#1)
Séquences (4)Séquences (4)
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.
SQL> insert into machine (nomachine,nommachine,typemachine)
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.
SQL> insert into machine (nomachine,nommachine,typemachine)
2 values (seqmachine.nextval,'Vrombisseuse','X');
1 ligne créée.
SQL> insert into machine (nomachine,nommachine,typemachine)
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
Jean-Luc GOERIG23/76 PL/SQL (#1)
Séquences (5)Séquences (5)
� 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
Jean-Luc GOERIG24/76 PL/SQL (#1)
Séquences (6)Séquences (6)
� 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 !
Jean-Luc GOERIG25/76 PL/SQL (#1)
Séquences avec Séquences avec SqlSql DeveloperDeveloper
Jean-Luc GOERIG26/76 PL/SQL (#1)
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. �
Jean-Luc GOERIG27/76 PL/SQL (#1)
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.
Jean-Luc GOERIG28/76 PL/SQL (#1)
Notes : algorithme de lecture d’un fichier séquentielNotes : algorithme de lecture d’un fichier séquentiel
Jean-Luc GOERIG29/76 PL/SQL (#1)
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);
fetch cv1 into 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
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);
fetch cv1 into vnom, vnumserv, vservice;
end loop;
close cv1;
end;
-- select * from liste;
Jean-Luc GOERIG30/76 PL/SQL (#1)
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.
Jean-Luc GOERIG31/76 PL/SQL (#1)
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.
Jean-Luc GOERIG32/76 PL/SQL (#1)
Curseur avec Curseur avec SqlSql DeveloperDeveloper
Jean-Luc GOERIG33/76 PL/SQL (#1)
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
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 cv2;
loop
fetch cv2 into vnom, vnumserv, vservice;
exit when cv2%notfound;
insert into liste values(vnom,vnumserv,vservice);
end loop;
close cv2;
commit;
end; /
select * from liste;
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
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 cv2;
loop
fetch cv2 into vnom, vnumserv, vservice;
exit when cv2%notfound;
insert into liste values(vnom,vnumserv,vservice);
end loop;
close cv2;
commit;
end; /
select * from liste;
Jean-Luc GOERIG34/76 PL/SQL (#1)
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;
/
Jean-Luc GOERIG35/76 PL/SQL (#1)
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;
Jean-Luc GOERIG36/76 PL/SQL (#1)
Curseurs implicites (2)Curseurs implicites (2)
� 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;
create table liste (col1 char(20),col2 char(20),col3 char(20));
declare
cursor cv4 is select nom_emp,service,nom_serv
from emp,service where service=num_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;
create table liste (col1 char(20),col2 char(20),col3 char(20));
declare
cursor cv4 is select nom_emp,service,nom_serv
from emp,service where service=num_serv;
begin
for ligne in cv4
loop
insert into liste
values(ligne.nom_emp,ligne.service,ligne.nom_serv);
end loop;
end;
Jean-Luc GOERIG37/76 PL/SQL (#1)
Curseurs implicites (3)Curseurs implicites (3)
� 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;
create table liste (col1 char(20),col2 char(20),col3 char(20));
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;
create table liste (col1 char(20),col2 char(20),col3 char(20));
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;
Jean-Luc GOERIG38/76 PL/SQL (#1)
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, ...);
Jean-Luc GOERIG39/76 PL/SQL (#1)
Curseurs paramétrés(2)Curseurs paramétrés(2)
� Exemple:
prompt curseur explicite paramétré
accept numservice
declare
cursor cv6 (param1 number) is
select nom_emp,service, nom_serv
from emp,service where service=num_serv
and num_serv=param1;
vnom emp.nom_emp%type;
vnumserv emp.service%type;
vservice service.nom_serv%type;
begin
open cv6(&numservice);
fetch cv6 into vnom, vnumserv, vservice;
while cv6%found
loop
insert into liste values(vnom,vnumserv,vservice);
fetch cv1 into vnom, vnumserv, vservice;
end loop;
close cv6;
End;
prompt curseur explicite paramétré
accept numservice
declare
cursor cv6 (param1 number) is
select nom_emp,service, nom_serv
from emp,service where service=num_serv
and num_serv=param1;
vnom emp.nom_emp%type;
vnumserv emp.service%type;
vservice service.nom_serv%type;
begin
open cv6(&numservice);
fetch cv6 into vnom, vnumserv, vservice;
while cv6%found
loop
insert into liste values(vnom,vnumserv,vservice);
fetch cv1 into vnom, vnumserv, vservice;
end loop;
close cv6;
End;
Jean-Luc GOERIG40/76 PL/SQL (#1)
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
Jean-Luc GOERIG41/76 PL/SQL (#1)
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';
vnom emp.nom_emp%type;
vnumserv emp.service%type;
vservice service.nom_serv%type;
cursor cv6 is
select nom_emp,service, nom_serv
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');
select * from emp,service
where service=num_serv and nom_serv like 'bar%';
declare
vbar service.nom_serv%type := 'bar';
vnom emp.nom_emp%type;
vnumserv emp.service%type;
vservice service.nom_serv%type;
cursor cv6 is
select nom_emp,service, nom_serv
from emp,service
where service=num_serv
and nom_serv like vbar
for update of service;
begin ...
� Exemple (début):
Jean-Luc GOERIG42/76 PL/SQL (#1)
Curseurs de mise à jour (3)Curseurs de mise à jour (3)
begin
open cv1;
fetch cv1 into vnom, vnumserv, vservice;
while cv1%found
loop
update emp set service=5
where current of cv1;
fetch cv1 into vnom, vnumserv, vservice;
end loop;
close cv1;
end;
/
select * from emp,service
where service=num_serv
and nom_serv like 'fast%';
begin
open cv1;
fetch cv1 into vnom, vnumserv, vservice;
while cv1%found
loop
update emp set service=5
where current of cv1;
fetch cv1 into vnom, vnumserv, vservice;
end loop;
close cv1;
end;
/
select * from emp,service
where service=num_serv
and nom_serv like 'fast%';
� Exemple (suite):
Jean-Luc GOERIG43/76 PL/SQL (#1)
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
Jean-Luc GOERIG44/76 PL/SQL (#1)
Traitement des erreurs (2)Traitement des erreurs (2)
� 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;
Jean-Luc GOERIG45/76 PL/SQL (#1)
Traitement des erreurs (3)Traitement des erreurs (3)
-- exemple exception utilisateur
declare
gros_salaire exception;
cursor cv1 is select nom_emp, salaire, nom_serv
from emp,service where service=num_serv;
vnom emp.nom_emp%type;
vsalaire emp.salaire%type;
vservice service.nom_serv%type;
begin
dbms_output.enable;
open cv1;
loop
fetch cv1 into vnom, vsalaire, vservice;
exit when cv1%notfound;
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
from emp,service where service=num_serv;
vnom emp.nom_emp%type;
vsalaire emp.salaire%type;
vservice service.nom_serv%type;
begin
dbms_output.enable;
open cv1;
loop
fetch cv1 into vnom, vsalaire, vservice;
exit when cv1%notfound;
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;
Jean-Luc GOERIG46/76 PL/SQL (#1)
Traitement des erreurs (4)Traitement des erreurs (4)
� 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.
Jean-Luc GOERIG47/76 PL/SQL (#1)
Traitement des erreurs (5)Traitement des erreurs (5)
-- exemple exception too_many_rows
declare
vnom emp.nom_emp%type;
vnumserv emp.service%type;
vservice service.nom_serv%type;
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
dbms_output.put_line('SQLCODE='||to_char(SQLCODE));
dbms_output.put_line('SQLERRM='||SQLERRM);
end;
/
-- exemple exception too_many_rows
declare
vnom emp.nom_emp%type;
vnumserv emp.service%type;
vservice service.nom_serv%type;
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
dbms_output.put_line('SQLCODE='||to_char(SQLCODE));
dbms_output.put_line('SQLERRM='||SQLERRM);
end;
/
Jean-Luc GOERIG48/76 PL/SQL (#1)
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
Jean-Luc GOERIG49/76 PL/SQL (#1)
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
Jean-Luc GOERIG50/76 PL/SQL (#1)
Variables curseurs (REF CURSORS)Variables curseurs (REF CURSORS)
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);
FETCH cur_pil INTO 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
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);
FETCH cur_pil INTO vnopil,vnom,vadr;
END LOOP;
CLOSE cur_pil;
dbms_output.put_line('++++++++++++++++');
-- suite sur la diapo suivante
Jean-Luc GOERIG51/76 PL/SQL (#1)
Variables curseurs (REF CURSORS)Variables curseurs (REF CURSORS)
-- suite du ref cursor
dbms_output.put_line('++++++++++++++++');
OPEN cur_pil FOR SELECT nopilote,nompil,salaire
from transair.pilote where comm is not null;
FETCH cur_pil INTO vnopil,vnom,vsal;
WHILE cur_pil%FOUND LOOP
dbms_output.put_line(vnopil||':'||vnom||':'||vsal);
FETCH cur_pil INTO vnopil,vnom,vsal;
END LOOP;
CLOSE cur_pil;
END;
-- suite du ref cursor
dbms_output.put_line('++++++++++++++++');
OPEN cur_pil FOR SELECT nopilote,nompil,salaire
from transair.pilote where comm is not null;
FETCH cur_pil INTO vnopil,vnom,vsal;
WHILE cur_pil%FOUND LOOP
dbms_output.put_line(vnopil||':'||vnom||':'||vsal);
FETCH cur_pil INTO 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.
Jean-Luc GOERIG52/76 PL/SQL (#1)
NotesNotes
Jean-Luc GOERIG53/76 PL/SQL (#1)
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.
Jean-Luc GOERIG54/76 PL/SQL (#1)
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.
Jean-Luc GOERIG55/76 PL/SQL (#1)
Procédures (2)Procédures (2)
� 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;
Jean-Luc GOERIG56/76 PL/SQL (#1)
Procédures (3)Procédures (3)
� 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;
Jean-Luc GOERIG57/76 PL/SQL (#1)
Procédures (4)Procédures (4)
� 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) ;
Jean-Luc GOERIG58/76 PL/SQL (#1)
Procédures (5)Procédures (5)
� 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
Jean-Luc GOERIG59/76 PL/SQL (#1)
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 }
Déclarations de variables locales;BEGIN
Instructions SQL et PL/SQL;RETURN (valeur);
EXCEPTIONTraitement des exceptions;
END [nomfonction];
Jean-Luc GOERIG60/76 PL/SQL (#1)
Fonctions (2)Fonctions (2)
� 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;
Jean-Luc GOERIG61/76 PL/SQL (#1)
Fonctions (3)Fonctions (3)
� 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 }
Déclarations de variables locales;BEGIN
Instructions SQL et PL/Sql;RETURN(Valeur);
EXCEPTIONTraitement des exceptions;
END ;
CREATE [OR REPLACE] FUNCTION nomfonction [ (argument,.)]RETURN type { IS | AS }
Déclarations de variables locales;BEGIN
Instructions SQL et PL/Sql;RETURN(Valeur);
EXCEPTIONTraitement des exceptions;
END ;
Jean-Luc GOERIG62/76 PL/SQL (#1)
Fonctions (4)Fonctions (4)
� 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;
Jean-Luc GOERIG63/76 PL/SQL (#1)
Procédures et fonctions avec Procédures et fonctions avec SqlSql DeveloperDeveloper
Jean-Luc GOERIG64/76 PL/SQL (#1)
Déboguer une procédure avec Déboguer une procédure avec SqlSql DeveloperDeveloper
Jean-Luc GOERIG65/76 PL/SQL (#1)
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
Jean-Luc GOERIG66/76 PL/SQL (#1)
Tableaux, structures et collections en PL/SQLTableaux, structures et collections en PL/SQL
� 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];
Jean-Luc GOERIG67/76 PL/SQL (#1)
Tableaux, structures et collections en PL/SQLTableaux, structures et collections en PL/SQL
� 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 );
Jean-Luc GOERIG68/76 PL/SQL (#1)
Tableaux, structures et collections en PL/SQLTableaux, structures et collections en PL/SQL
� 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;
Jean-Luc GOERIG69/76 PL/SQL (#1)
Tableaux, structures et collections en PL/SQLTableaux, structures et collections en PL/SQL
� 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
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;
Jean-Luc GOERIG70/76 PL/SQL (#1)
Tableaux, structures et collections en PL/SQLTableaux, structures et collections en PL/SQL
� 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
index by binary_integer;
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
index by binary_integer;
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;
Jean-Luc GOERIG71/76 PL/SQL (#1)
Tableaux, structures et collections en PL/SQLTableaux, structures et collections en PL/SQL
� 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 !
Jean-Luc GOERIG72/76 PL/SQL (#1)
Tableaux, structures et collections en PL/SQLTableaux, structures et collections en PL/SQL
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;
Jean-Luc GOERIG73/76 PL/SQL (#1)
Tableaux, structures et collections en PL/SQLTableaux, structures et collections en PL/SQL
� 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
Jean-Luc GOERIG74/76 PL/SQL (#1)
Tableaux, structures et collections en PL/SQLTableaux, structures et collections en PL/SQL
é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
Jean-Luc GOERIG75/76 PL/SQL (#1)
Tableaux, structures et collections en PL/SQLTableaux, structures et collections en PL/SQL
-- 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;
cursor cur is select sal from emp;
begin
open cur;
i :=1;
loop
exit when cur%notfound;
fetch cur into tasal(i) ;
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);
dbms_output.put_line('nombre d élément : '||tasal.COUNT);
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;
cursor cur is select sal from emp;
begin
open cur;
i :=1;
loop
exit when cur%notfound;
fetch cur into tasal(i) ;
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);
dbms_output.put_line('nombre d élément : '||tasal.COUNT);
end;
Jean-Luc GOERIG76/76 PL/SQL (#1)
Tableaux, structures et collections en PL/SQLTableaux, structures et collections en PL/SQL
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.
top related