Dossier n° 6 - Fascicule a
ORACLE
7 : PL/SQL
Sommaire
1. Introduction.................................................
2. Environnement................................................
3. Structure d'un Bloc..........................................
4. Les déclarations PL/SQL......................................
4.1 Types de données............................................
4.2. Conversion des types de données...........................
4.3. Variables et constantes...................................
4.3.1. La définition des variables en PL/SQL...................
4.3.2. Exemples de déclaration de variables....................
4.3.3. L'assignement des variables PL/SQL......................
4.4. Les tableaux (table PL/SQL)...............................
4.4.1. La déclaration d'un tableau.............................
4.4.2. L'accès aux éléments d'un tableau.......................
4.4.3. Exemple.................................................
4.5. Les enregistrements prédéfinis (record PL/SQL)............
4.4.1. La déclaration d'un enregistrement......................
4.4.2. L'accès aux champs d'un enregistrement..................
4.4.3. Exemple.................................................
5. Les structures de contrôles.................................
5.1. Les Traitements Conditionnels.............................
5.2. Les Traitements Répétitifs................................
5.2.1. L'instruction LOOP......................................
5.2.2. L'instruction FOR ... LOOP..............................
5.2.3. L'instruction WHILE ... LOOP............................
6. La Gestion des erreurs......................................
6.1. Les exceptions internes...................................
6.2. Les exceptions utilisateurs (externes)....................
7. Les curseurs en PL/Sql......................................
7.1. Définition................................................
7.2. Les types de curseurs.....................................
7.3. Les étapes d'utilisation d'un curseur explicite...........
7.3.1. La déclaration d'un curseur.............................
7.3.2. L'ouverture et la fermeture du curseur..................
7.3.3. Le traitement des lignes................................
7.4. Les attributs d'un curseur................................
7.4.1 L'attribut %Found........................................
7.4.2. L'attribut %NotFound....................................
7.4.3. L'attribut %Isopen......................................
7.4.4. L'attribut %RowCount....................................
7.4.5. L'attribut %Rowtype.....................................
7.5. Les boucles et les curseurs...............................
7.6. Le curseur paramètre......................................
7.7. La clause "current of ..."................................
8. Les Sous-Programmes.........................................
9. Utilisation de PL/SQL pour la génération des tables de tests
Sql
est un langage complet pour travailler sur une base de données, mais il ne
comporte pas d'instructions procédurales.
PL/SQL
comprend quant à lui :
·
·
· la gestion des transactions (Commit, Rollback,
Savepoint),
· les fonctions de Sql
· plus une partie procédurale (IF, WHILE, ...).
PL/SQL est donc un langage
algorithmique complet.
Remarque
: il ne comporte pas
d'instructions de LDD (Alter, Create, Rename) ni les instructions de contrôle
comme (Grant et Revoke).
PL/SQL
est un langage au même titre que SQL.
Tout
comme SQL, PL/SQL peut être utilisé au sein des outils de
PL/SQL
comporte donc des instructions SQL, pour bénéficier des avantages du SQL et
intègre d'autre part des instructions PL qui permettent de gérer :
· des boucles (LOOP, FOR, WHILE, EXIT [WHEN], GOTO)
· des
conditionnelles (IF, THEN, ELSIF, ELSE, END IF)
· des calculs et des fonctions,
· des sous-programmes,
· des packages (paquettage ou librairie ou module).
Le
fonctionnement de PL/SQL est basé sur l'interprétation d'un "bloc" de
commandes. Ce mode de fonctionnement permet d'obtenir des gains de transmission
et des gains de performances :
Schémas :
SQL
et PL/SQL ont tous les deux un "moteur" associé :
· SQL Statement Executor,
· Procedural Statement Executor
Le
moteur SQL se trouve dans le noyau SGBDR
Le
moteur PL/SQL se trouve :
· soit avec le noyau RDBMS,
· soit avec l'outil.
Schéma 1 : PL/SQL dans le noyau du
SGBDR
Utilisable
avec : Sql*Dba (V6.0), Sql*Plus (V3), Précompilateurs V1.3
Schéma 2 : Le traitement d'un RPC
par le noyau du SGBDR
RPC
= Remote Procedure Call
Schéma 3 : PL/SQL dans les outils
Utilisable
avec : Sql*Plus 3.0, Sql*Forms V3, bientôt Sql*Menu, Sql*ReportWriter.
Ceci
permet de bénéficier d'un langage procédural au sein de l'outil, et de mettre
en oeuvre une approche algorithmique au niveau de l'écriture des triggers.
L'utilisation
de PL/SQL à partir de Sql*Plus est possible soit en introduisant directement
vos ordres PL/SQL dans l'éditeur, soit en chargeant les instructions PL/SQL à
partir d'un fichier.
Bien
que les instructions de Sql*Plus (column, ttitle, ...) sont interdites dans un
bloc PL/SQL, vous pouvez insérer dans le même fichier des ordres Sql*Plus et
des blocs PL/SQL. Notez que chaque bloc de PL/SQL doit se terminer par une
barre oblique (/ ou slash).
PL/SQL
peut également tirer avantage des variables de substitution de Sql*Plus.
PL/SQL
n'interprète pas une commande, mais un ensemble de commandes contenu dans un
programme ou "bloc" PL/SQL.
La
structure d'un bloc est la suivante:
DECLARE
Déclarations de variables, constantes,
exception
BEGIN
Section obligatoire contenant des
commandes exécutables
Instructions SQL et PL/SQL
Possibilités de blocs fils (imbrication
de blocs)
EXCEPTION
Traitement des exceptions (gestion des
erreurs)
END ;
Remarque
:
Les
sections Declare et Exception sont optionnelles.
Chaque
instruction de n'importe quelle section doit se terminer par un ';'.
La
structuration des instructions en blocs procurent une plus grande lisibilité des
programmes et une simplification des traitements complexes.
Exemple
de bloc PL/SQL :
DECLARE
qte_stock number(5);
BEGIN
Select quantite into qte_stock from
inventaire
where produit='raquette tennis';
-- contrôle du stock suffisant --
If qte_stock > 0
then update inventaire set
quantite=quantite-1
where produit='raquette tennis';
insert into achat
values ('raquette tennis',
SYSDATE);
else
insert into acheter
values ('Plus de raquettes de
tennis',SYSDATE);
end if;
commit;
END ;
La partie déclarative dans un bloc PL/SQL, peut comporter trois types de déclarations.
Elle est délimitée par les mots-clé
· DECLARE, qui spécifie le début et
· BEGIN, qui signifie la fin de la déclaration et le début de la partie des commandes.
Les types de déclarations possibles dans cette partie sont les suivants :
· déclaration des variables et des constantes,
· déclaration de curseurs,
· déclaration des exceptions.
4.1 Types de données
Chaque variable ou constante utilisée dans un bloc PL/SQL, possède un type de données qui détermine son format de stockage, sa contrainte et son champ valide de valeurs.
PL/SQL offre deux variétés de types de données prédéfinies :
· scalaire,
· composé.
|
Types
de données PL/SQL |
|
|
Types Scalaires |
Types Composés |
|
binary_integer natural positive decimal float integer real smallint number |
record table |
|
char long varchar varchar2 |
|
|
boolean date rowid |
|
|
raw long raw |
|
binary_integer : entier de 2(p-31) à 2(p+31)+1
natural : entiers naturels
positive : entiers positifs
decimal, float, integer, real et sallint : sous-types de number
char : chaîne de caractères jusqu'à 32767 caractères (au lieu de 255 dans la définition d'une colonne de table)
varchar2 : chaîne de caractères jusqu'à 32767 caractères (au lieu de 2000 dans la définition d'une colonnne de table)
long : équivalent à varchar2 sauf 2go dans la base.
4.2. Conversion des types de données
Les conversions des types de données en PL/SQL, sont regroupées en deux familles :
· les conversions explicites avec les fonctions définies dans Sql telles que to_date, to_char, etc.
· les conversions implicites sont réalisées automatiquement par PL/SQL et fonctionnent selon les trois règles suivantes :
1.
évaluation d'expressions,
2.
affectation des variables,
3. affectation d'arguments.
4.3. Variables et constantes
La déclaration d'une
variable consiste à allouer un espace pour stocker et modifier une valeur. Elle
est typée et peut recevoir une valeur par défaut et/ou un statut NOT NULL.
Une constante est définie
comme une variable, mais l'utilisateur ne peut pas modifier son contenu.
4.3.1. La définition des variables en PL/SQL
Les variables se
définissent dans
nomvariable [CONSTANT]
{type | variable%TYPE | table.%ROWTYPE
}
[NOT
NULL]
[{:= | DEFAULT } expression PL/SQL]
Remarques :
·
L'attribut constant
permet de figer l'affectation d'une variable.
·
L'attribut 'Not
Null' rend obligatoire d'initialiser la variable lors de sa définition.
·
On peut faire
référence à une colonne d'une table par l'instruction : nom_variable
TABLE.COLONNE%TYPE
·
On peut faire
référence à une ligne d'une table par l'instruction nom_variable
TABLE%ROWTYPE
·
On peut faire
référence à une variable précedemment définie par l'instruction :
nom_variable Pnom_variable%TYPE
L'initialisation d'une
variable de fait par l'opérateur : ':='
suivi :
·
d'une
constante,
·
d'une
expression PL/SQL,
·
d'une fonction
PL/SQL.
Une expression boolénne
donne comme résultat TRUE, FALSE ou NULL.
Les variables peuvent
également être définies dans l'environnement extérieur au Bloc PL/SQL :
- champs de l'écran en
Sql*Forms,
- variables définies en
langage hôte dans Pro*.
Ces variables seront
utilisées préfixées de ':'.
4.3.2. Exemples de déclaration de variables
Total number(9,2);
Nom char(10):='Fischer';
Longeur number not null := length (Nom) * 2;
Date_Creation Date;
Numero EMPLOYE.EMPNO%TYPE;
Dpt DEPARTEMENT%ROWTYPE;
Prenom Nom%TYPE;
Pi Constant Number:=3.14;
4.3.3. L'assignement des variables PL/SQL
Deux possibilités
d'assignement ou d'affectation sont disponibles :
1. par l'opérateur
d'affectation : ':=',
2. par
La difficulté dans
l'utilisation de
Si le Select retourne
une et une seule ligne l'affectation s'effectue correctement.
Par contre,
Si le Select retourne 0
ligne : NO_DATA_FOUND ou
Si le Select retourne plusieurs lignes : TOO_MANY_ROWS
une erreur PL/SQL est
générée.
4.4. Les tableaux (table PL/SQL)
Nous avons vu dans le
tableau des types de données que le langage PL/SQL fournit deux types d'objets
composés :
·
les tables
(TABLE),
·
les
enregistrements (RECORD).
Les tableaux sont conçus
comme les tables de la base de données. Ils possèdent une clé primaire
(index) pour accéder aux lignes du tableau.
Un tableau, comme une
table, ne possède pas de limite de taille. De cette façon, le nombre
d'éléments d'un tableau va croître dynamiquement.
Les tableaux peuvent
posséder une colonne et une clé primaire. Par contre ni la colonne, ni la clé
primaire (index) ne peut être nommé.
La colonne peut être de
n'importe quel type scalaire, mais la clé primaire doit être du type
BINARY_INTEGER.
Remarque :
Les prochaines versions de
PL/SQL pourront gérer des tableaux avec plusieurs colonnes nommées et possédant
des clés primaires composés et de n'importe quel type.
4.4.1. La déclaration d'un tableau
Les tableaux PL/SQL
doivent être déclarés en deux étapes :
1. Déclaration du type de
la TABLE
2. Déclaration d'une table
de ce type.
Vous pouvez déclarer un
type TABLE dans la partie déclarative d'un bloc, d'un sous-programme ou
d'un package en utilisant la syntaxe suivante :
TYPE nom_type IS TABLE OF
{typecolonne | variable%TYPE |
table.column%TYPE } [NOT NULL]
INDEX BY BINARY_INTEGER ;
nomtype : utilisé ensuite
dans la déclaration des tables PL/SQL.
typecolonne : type de données
comme CHAR, DATE ou NUMBER.
Lorsque le type est
déclaré, vous pouvez déclarer des tableaux de ce type, ainsi :
nom_tab nom_type ;
nom_tab : correspond à un
tableau PL/SQL.
4.4.2. L'accès aux éléments d'un tableau
Pour accéder à un élément
du tableau, vous devez spécifier une valeur de clé primaire en respectant la
syntaxe suivant :
nom_tab(valeur_cle_primaire) ;
valeur_cle_primaire : doit
être du type BINARY_INTEGER ( -231 -1 à 231 -1)
Pour affecter la valeur
d'une expression PL/SQL à un élément du tableau utiliser la syntaxe suivante :
nom_tab(valeur_cle_primaire) :=
expression_plsql;
4.4.3. Exemple
DECLARE
TYPE
nom_tabtype IS TABLE OF CHAR(25)
INDEX BY BINARY_INTEGER ;
...
tnom nom_tabtype ;
...
BEGIN
...
tnom(1):='Dupont Marc' ;
...
END ;
4.5. Les enregistrements prédéfinis (record PL/SQL)
La
restriction posée par l'utilisation du type %ROWTYPE pour déclarer un
enregistrement réside dans le manque de spécification des types de données au
niveau de l'enregistrement.
L'implémentation du
nouveau type composé nommé RECORD a permis la levée de cette restriction.
4.4.1. La déclaration d'un enregistrement
Par analogie aux tableaux
PL/SQL, la déclaration d'un enregistrement se fait également en en deux
étapes :
1. Déclaration du type de
l'enregistrement
2. Déclaration de la
variable sur le type défini.
Vous pouvez déclarer un
type RECORD dans la partie déclarative d'un bloc, d'un sous-programme ou
d'un package en utilisant la syntaxe suivante :
TYPE nom_type IS RECORD
(champ {typechamp | table.column%TYPE }
[NOT NULL],
champ {typechamp | table.column%TYPE }
[NOT NULL],...)
nomtype : utilisé ensuite
dans la déclaration des tables PL/SQL.
typecolonne : type de
données comme CHAR, DATE ou NUMBER.
Lorsque le type est
déclaré, vous pouvez déclarer des enregistrements de ce type, ainsi :
nom_enr nom_type ;
nom_enr : correspond à un
Record PL/SQL.
4.4.2. L'accès aux champs d'un enregistrement
Pour accéder à un élément
d'une variable de type record, il suffit d'utiliser la syntaxe suivante :
nom_enr.nom_champ
Pour affecter la valeur
d'une expression PL/SQL à un élément de l'enregistrement utiliser la syntaxe
suivante :
nom_enr.nom_champ := expression_plsql;
4.4.3. Exemple
DECLARE
TYPE ADRESSE IS RECORD
( Numero smallint,
Rue char(35),
CodePost char(5),
Ville char(25),
Pays char(30) );
TYPE CLIENT IS RECORD
( NumCli smallint,
NomCli char(40),
AdrCli ADRESSE,
CA number) ;
monclient CLIENT;
BEGIN
...
monclient.NumCli:=1234;
monclient.NomCli:='Dupont SARL';
monclient.AdrCli.Numero:=10;
...END ;
5.1. Les Traitements Conditionnels
Les
instructions conditionnelles doivent permettre de contrôler le moment où des
instructions sont exécutées.
Syntaxe :
IF
condition_plsql
Then
commandes
[Else
commandes ]
[ELSIF
condition_plsql
Then commandes
[Else commandes ] ]
END
IF;
La condition peut
utiliser les variables définies ainsi que tous les opérateurs présents dans SQL
: =, <, >, <=, >=, <>, IS NULL, IS NOT NULL.
Else est utilisé si les instructions qui suivent ne
possèdent pas de conditions.
ELSIF est utilisé si les instructions qui suivent
possèdent des conditions.
Exemple :
DECLARE
vjob char(10);
vnom emp.ename%type:='Miller';
message char(30);
BEGIN
Select job into vjob from emp where
ename=vnom;
-- contrôle de la valeur de vjob --
If vjob is NULL
then message:= vnom || 'pas de
travail';
elsif vjob='Vendeur'
then
update emp set comm=1000 where
ename=vnom;
message:= vnom || 'a 1000 Frs de
commission';
else
update emp set comm=0 where
ename=vnom;
message:= vnom || 'pas de
commission ';
end if;
insert into resultat values
(NULL,NULL,message);
commit;
END
;
/
select * from resultat;
5.2. Les Traitements Répétitifs
Les
traitements répétitifs permettre de répéter une suite de comandes, sans en
répéter les instructions.
PL/SQL
nous offre la possibilité d'effectuer des traitements répétitifs grâce à trois
types d'instructions.
5.2.1. L'instruction LOOP
Acronyme de boucle, LOOP
permet de répéter une séquence de commandes. Cette séquence est comprise entre
le mot-clé LOOP, indiquant le début d'une boucle et END LOOP, spécifiant sa
fin.
Syntaxe
:
BEGIN
[<<Label>>] LOOP
...
instructions
...
END LOOP [<<Label>>];
END ;
Les
commandes EXIT, EXIT WHEN condition
et GOTO permettent de sortir de la Boucle.
Exemple
:
DECLARE
nombre number;
BEGIN
nombre:=0;
LOOP
nombre:=nombre+1;
if nombre > 10
then exit;
end if;
END
LOOP ;
END ;
5.2.2. L'instruction FOR ... LOOP
La
limitation des traitements répétitifs peut se faire en utilisant
Syntaxe
:
[<<Label>>]
FOR
compteur IN [REVERSE]
var_debut .. var_fin LOOP
...
instructions
...
END
LOOP [<<Label>>];
END ;
compteur : est une variable de type entier, locale à
Elle s'incrémente de 1,
après chaque traitment du contenu de la boucle, jusqu'à ce qu'il atteigne la
valeur de droite (var_fin).
Le mot clé REVERSE permet d'utiliser une décrémentation de 1, en
prenant comme valeur initiale celle de l'expression entière de droite et comme
valeur de fin celle de l'expression entière de gauche.
Il est possible d'arréter
la boucle avant sa fin normale par une commande EXIT conditionnelle.
5.2.3. L'instruction WHILE ... LOOP
Syntaxe
:
WHILE
condition_plsql LOOP
...
instructions
...
END
LOOP ;
END ;
La condition est une
expression définie en combinant les opérateurs : <, >, = , !=, <=,
>=; and, or ...
Expression est une
constante, une variable, le résultat d'une fonction.
Exemple
:
DECLARE
salaire emp.sal%type;
manager emp.mgr%type;
nom
emp.ename%type;
num_debut constant number(4):=7902;
BEGIN
select sal, mgr, ename
into salaire, manager, nom
from emp where empno=num_debut;
WHILE
salaire < 4000 LOOP
select sal, mgr, ename
into salaire, manager, nom
from emp where empno=manager;
END
LOOP ;
insert into resultat values
(NULL,Salaire,Nom);
commit;
END ;
Le
mécanisme de gestion d'erreurs dans PL/SQL est appelé gestionnaire des
exceptions.
Il
permet au développeur de planifier sa gestion et d'abandonner ou de continuer
le traitement en présence d'une erreur.
Il
faut affecter un traitement approprié aux erreurs apparues dans un bloc PL/SQL.
C'est
pourquoi on distingue 2 types
d'erreurs ou d'exceptions :
·
Erreur interne Oracle (Sqlcode = 0) : dans ce cas la main est
rendue directement au système environnant.
·
Anomalie déterminée par l'utilisateur.
La
solution :
1. Donner un nom à l'erreur (si elle n'est pas déjà prédéfinie),
2. Définir les anomalies utilisateurs, leur associer un nom,
3. Définir le traitement à effectuer.
6.1. Les exceptions internes
Une
erreur interne est produite quand un bloc PL/SQL viole une règle d'Oracle ou
dépasse une limite dépendant du système d'exploitation.
Les
erreurs Oracle générées par le noyau sont numérotées, or le gestionnaire des
exceptions de PL/SQL, ne sait que gérer des erreurs nommées.
Pour
cela PL/SQL a redéfini quelques erreurs Oracle comme des exceptions. Ainsi,
pour gérer d'autres erreurs Oracle, l'utilisateur doit utiliser le gestionnaire
OTHERS ou EXCEPTION_INIT pour nommer ces erreurs.
Les
exceptions fournies par Oracle sont regroupées dans ce tableau :
|
Nom d'exception |
Valeur SqlCode |
Erreur Oracle |
|
CURSOR_ALREADY_OPEN |
-6511 |
ORA-06511 |
|
DUP_VAL_ON_INDEX |
-1 |
ORA-00001 |
|
INVALID_CURSOR |
-1001 |
ORA-01001 |
|
INVALID_NUMBER |
-1722 |
ORA-01722 |
|
LOGIN_DENIED |
-1017 |
ORA-01717 |
|
NO_DATA_FOUND |
-1403 |
ORA-01413 |
|
NOT_LOGGED_ON |
-1012 |
ORA-01012 |
|
PROGRAM_ERROR |
-6501 |
ORA-06501 |
|
STORAGE_ERROR |
-6500 |
ORA-06500 |
|
TIMEOUT_ON_RESOURCE |
-51 |
ORA-00051 |
|
TOO_MANY_ROWS |
-1422 |
ORA-01422 |
|
TRANSACTION_BACKED_OUT |
-61 |
ORA-00061 |
|
VALUE_ERROR |
-6502 |
ORA-06502 |
|
ZERO_DIVIDE |
-1476 |
ORA-01476 |
OTHERS
: toutes les autres erreurs non explicitement nommées.
Pour
gérer les exceptions, le développeur doit écrire un gestionnaire des exceptions
qui prend le contrôle du déroulement du bloc PL/SQL en présence d'une
exception.
Le
gestionnaire d'exception fait partie du bloc PL/SQL et se trouve après les
commandes.
Il
commence par le mot clé EXCEPTION
et se termine avec le même END
du bloc.
Chaque
gestion d'exception consiste à spécifier son nom d'erreur après
Exemple
: Utilisation des erreurs prédéfinies
DECLARE
wsal emp.sal%type;
BEGIN
select sal into wsal from emp;
EXCEPTION
WHEN TOO_MANY_ROWS then ... ;
-- gérer erreur trop de lignes
WHEN NO_DATA_FOUND then ... ;
-- gérer erreur pas de ligne
WHEN OTHERS then ... ;
-- gérer toutes les autres erreurs
END ;
Remarques :
L'exception optionnelle
OTHERS est toujours située à la fin des exceptions.
Pour rattacher une
séquence de commandes à plus d'une exception, l'utilisateur peut utiliser
l'opérateur booléen OR comme suit :
WHEN erreur1 OR erreur2
THEN
-- gérer erreur12
6.2. Les exceptions utilisateurs (externes)
PL/SQL
permet à l'utilisateur de définir ses propres exceptions.
La
gestion des anomalies utilisateur peut se faire dans un bloc PL/SQL en
effectuant les opérations suivantes :
1.
Nommer l'anomalie (type exception) dans
DECLARE
Nom_ano
Exception;
2.
Déterminer l'erreur et passer la main au traitement approprié par
BEGIN
...
If (condition_anomalie) then raise
Nom_ano ;
3.
Effectuer le traitement défini dans
EXCEPTION
WHEN (Nom_ano) then (traitement);
Exemple
: Utilisation des erreurs prédéfinies et nommées
DECLARE
wsal emp.sal%type;
sal_zero Exception;
BEGIN
select sal into wsal from emp;
if wsal=0 then
raise sal_zero
end if;
EXCEPTION
WHEN sal_zero then
-- gérer erreur salaire
WHEN TOO_MANY_ROWS then ... ;
-- gérer erreur trop de lignes
WHEN NO_DATA_FOUND then ... ;
-- gérer erreur pas de ligne
WHEN OTHERS then ... ;
-- gérer toutes les autres erreurs
END ;
Le
développeur peut utiliser les fonctions Sqlcode et Sqlerrm pour coder les
erreurs Oracle en Exception.
Sqlcode
est une fonction propre à PL/SQL qui retourne le numéro (généralement négatif)
de l'erreur courante.
Sqlerrm
reçoit en entrée le numéro de l'erreur et renvoie en sortie le message de
l'erreur codé sur 196 octets.
Pour traiter une commande
Sql, PL/SQL ouvre une zone de contexte pour exécuter la commande et stocker les
informations.
7.1. Définition
Le
curseur permet de nommer cette zone de contexte, d'accéder aux informations et
éventuellement de contrôler le traitement.
Cette
zone de contexte est une mémoire de taille fixe, utilisée par le noyau pour
analyser et interpréter tout ordre Sql.
Les
statuts d'exécution de l'ordre se trouve dans le curseur.
7.2. Les types de curseurs
·
Le curseur
explicite
Il
est créé et géré par l'utilisateur pour traiter un ordre Select qui ramène plusieurs
lignes.
Le
traitement du select se fera ligne par ligne.
·
Le curseur
implicite
Il
est généré et géré par le noyau pour les autres commandes Sql.
7.3. Les étapes d'utilisation d'un curseur explicite
Pour
traiter une requête qui retourne plusieurs lignes, l'utilisateur doit définir
un curseur qui lui permet d'extraire la totalité des lignes sélectionnées.
L'utilisation
d'un curseur pour traiter un ordre Select ramenant plusieurs lignes, nécessite
4 étapes :
1.
Déclaration du curseur
2.
Ouverture du curseur
3.
Traitement des lignes
4.
Fermeture du curseur.
7.3.1. La déclaration d'un curseur
La
déclaration du curseur permet de stocker l'ordre Select dans le curseur.
La
syntaxe de définition :
Le
curseur se définit dans
Cursor nomcurseur [(nomparam type
[,nomparam type, ...)]
IS commande SELECT ;
Exemple
:
Declare
Cursor DEPT10 is
select ename, sal from emp where
depno=10;
7.3.2. L'ouverture et la fermeture du curseur
L'étape
d'ouverture permet d'effectuer :
1. l'allocation mémoire du curseur ;
2. l'analyse sémantique et syntaxique de l'ordre (parsing) ;
3. le positionnement de verrous éventuels (si select for update...)
L'étape
de fermeture permet d'effectuer :
la
libération de la place mémoire.
La
syntaxe :
Dans
la partie traitement du bloc PL/SQL
Avoir
préalablement déclaré le curseur pour l'ouvrir
Avoir
préalablement ouvert le curseur pour le fermer
OPEN nomcurseur [(nomparam type
[,nomparam type, ...)]
/* traitement des lignes */
CLOSE nomcurseur
Exemple
:
Begin
...
OPEN DEPT10
/* traitement des lignes */
CLOSE DEPT10
7.3.3. Le traitement des lignes
Il
faut traiter les lignes une par une et renseigner les variables réceptrices
définies dans
Syntaxe
:
Dans
la partie traitement du bloc Pl/Sql
Avoir
préalablement ouvert le curseur puis
FETCH nomcurseur INTO { nomvariable
[,nomvariable] ...
| nomrecord }
L'ordre
fetch ne ramène qu'une seule ligne à la fois.
De
ce fait il faut recommencer l'ordre pour traiter la ligne suivante.
Exemple
:
Begin
OPEN DEPT10
LOOP
FETCH DEPT10 into vnom, vsalaire;
/* traitement ligne */
END LOOP;
7.4. Les attributs d'un curseur
Les
attributs d'un curseur nous fournissent des informations quant à l'exécution de
l'ordre. Elles sont conservées par Pl/Sql après l'exécution du curseur
(explicite ou implicite).
Ces
attributs permettent de tester directement le résultat de l'exécution.
Tous
les attributs ont un nom.
|
Curseur implicite |
Curseur explicite : |
|
Sql%Found |
Nomcurseur%Found |
|
Sql%Notfound |
Nomcurseur%Notfound |
|
Sql%Isopen |
Nomcurseur%Isopen |
|
Sql%Rowcount |
Nomcurseur%Rowcount |
|
|
Nomcurseur%Rowtype |
7.4.1 L'attribut %Found
Signification
Cet
attribut est de type booléen : soit vrai, soit faux.
Le
curseur implicite est vrai si les instructions insert, update, delete traitent
au moins une ligne.
Le
curseur explicite est vrai si le Fetch ramène au moins une ligne.
Exemple
Declare
Cursor num1_cur is Select num
from num1_tab order by sequence;
Cursor num2_cur is Select num
from num2_tab order by sequence;
num1 number;
num2 number;
pair_num number:=0;
Begin
Open num1_cur;
Open num2_cur;
Loop
Fetch num1_cur into num1;
Fetch num2_cur into num2;
if (num1_cur%Found) and (num2_cur%Found)
then
pair_num:=
pair_num+1;
insert into
sum_tab values (pair_num, num1+num2);
else
exit;
end if;
End loop;
close num1_cur;
close mun2_cur;
End ;
7.4.2. L'attribut %NotFound
Signification
Cet
attribut est de type booléen : soit vrai, soit faux.
Le
curseur implicite est vrai si les instructions insert, update, delete ne
traitent aucune ligne.
Le
curseur explicite est vrai si le Fetch ne ramène plus de ligne.
Exemple
Declare
Cursor num1_cur is Select num from num1_tab order by sequence;
Cursor num2_cur is Select num from num2_tab order by sequence;
num1 number;
num2 number;
pair_num number:=0;
Begin
Open
num1_cur;
Open
num2_cur;
Loop
Fetch
num1_cur into num1;
Fetch
num2_cur into num2;
Exit when (num1_cur%NotFound) or (num2_cur%NotFound);
pair_num:= pair_num+1;
insert
into sum_tab values (pair_num, num1+num2);
End loop;
close
num1_cur;
close
mun2_cur;
End ;
7.4.3. L'attribut %Isopen
Signification
Cet
attribut est de type booléen : soit vrai, soit faux.
Le
curseur implicite est toujours faux car Oracle referme toujours les curseurs
qu'il ouvre après chaque utilisation.
Le
curseur explicite est vrai si le curseur est ouvert.
Exemple
Declare
Cursor
Dept10 is
Select ename, sal from emp where deptno=10;
Begin
If not
(Dept10%Isopen)
then
open Dept10 ;
End
if;
Fetch
Dept10 into ....
/*
traitement */
End;
7.4.4. L'attribut %RowCount
Signification
:
Cet
attribut est de type numérique.
Le
curseur implicite indique le nombre de lignes traités par les ordres insert,
update, delete.
Le
curseur explicite est :
·
incrémenté à
chaque ordre fetch,
·
il traduit
donc la nième ligne traitée.
Exemple
Declare
curseur c1 is
select ename, empno, sal from emp
order by sal desc;
nom
char(10);
numero
number(4);
salaire
number(7,2);
Begin
Open c1;
Loop
Fetch
c1 into nom, numéro, salaire;
exit
when (c1%rowcount > 25) or
(c1%notfound);
insert
into temp values (salaire, numéro, nom);
commit;
End Loop;
Close c1;
End;
7.4.5. L'attribut %Rowtype
Signification
Cet
attribut permet la déclaration implicite d'une structure dont les éléments sont
d'un type identique aux colonnes ramenées par le curseur.
Syntaxe
:
Dans
la partie déclarative du bloc.
Cursor nomcurseur is ordre_select;
nomrecord nomcurseur%Rowtype;
Les
éléments de la structure sont identifiés par :
nomrecord.nomcolonne
La
structure est renseignée par le Fetch :
Fetch nomcurseur into nomrecord;
Exemple
Declare
/* la fonction nvl permet de tester
colonne : si comm=NULL alors nvl retourne 0
si comm NOT NULL alors nvl retourne comm
*/
cursor c1
is select sal + nvl(comm,0) saltot, ename
from emp;
/* l'enregistrement comporte deux colonnes :
saltot, ename */
c1_record c1%rowtype;
Begin
open c1;
Loop
Fetch c1 into c1_record;
Exit
when c1%notfound;
if
c1_record.saltot > 2000
then insert into temp
values (null, c1_record.saltot, c1_record.ename);
end
if;
End Loop;
close c1;
End;
7.5. Les boucles et les curseurs
L'objectif
est de fournir au programmeur une structure simple et efficace pour utiliser
les structures de boucle et les curseurs.
Declare
Cursor nomcurseur is ordre_select;
Begin
For
nomrecord in nomcurseur Loop
/* traitement */
End Loop;
End;
Permet
d'obtenir une génération implicite de la structure suivante :
Declare
Cursor
nomcurseur is ordre_select;
nomrecord
nomcurseur%rowtype;
Begin
Open nomcurseur;
Loop
Fetch
nomcurseur into nomrecord;
Exit
when nomcurseur%notfound
/*
traitement */
End
Loop;
Close
nomcurseur;
End;
7.6. Le curseur paramètre
Il
permet d'utiliser des variables dans le curseur. Principalement dans la clause
where.
Il
faut pour cela spécifier les noms et les types des paramètres dans la
déclaration du curseur.
Cursor nomcurseur (param1 type, param2 type,...)
is select ordre_select ;
L'ordre_select
utilise les paramètres.
Les
types possibles sont : char, number, date, boolean sans spécifier la longueur.
Begin
Open nomcurseur (valeur1 , valeur2,
....) ;
Exemple
:
Declare
cursor c1
(depart number)
is
select sal, nvl(comm,0) commi
from emp where deptno=depart;
total
number(11,2):=0;
sal_sup
number(4):=0;
comm_sup
number(4):=0;
Begin
for
c1_record in c1(20) Loop
total:= total+ c1_rec.sal + c1_rec.commi;
if
c1_rec.sal > 2000.00
then sal_sup:=sal_sup+1;
end
if;
if
c1_rec.commi > 1000.00
then comm_sup:=comm_sup+1;
end
if;
End Loop;
insert into
temp
values
(sal_sup, comm_sup, 'total salaire '|| to_char(total));
commit;
End;
7.7. La clause "current of ..."
Cette
clause permet d'accéder directement à la ligne ramenée par l'ordre Fetch afin
de la traiter (update, delete).
·
Il faut se
réserver la ligne lors de la déclaration du curseur par le positionnement d'un
verrou d'intention :
( ... For update of nom_colonne )
·
Il faut
spécifier que l'on veut traiter la ligne courante au Fetch par la clause :
( ... Where current of nom_curseur ).
Exemple
:
Declare
Cursor c1
is select ename, sal
from emp
for update of sal;
Begin
For
c1_record in c1 Loop
if
c1_record.sal > 1500
then
insert into resultat values
(c1_record.sal, c1_record.sal * 1.3, c1_record.ename);
update emp set sal = sal * 1.3
where current of c1;
end
if;
End loop;
End;
Les sous-programmes sont
des blocs PL/SQL nommés.
Ils peuvent être
paramétrés et être exécutés.
Le langage PL/SQL propose
deux types de sous-programmes :
·
les procédures
et
·
les fonctions.
Habituellement une
procédure est utilisée pour réaliser une action et une fonction pour réaliser
un calcul.
Comme les blocs PL/SQL les
sous-programmes se composent :
·
d'une partie
déclarative,
·
d'une partie
exécutable et
·
d'une partie
optionnelle.
Un sous-programme peut
être déclaré dans un bloc PL/SQL, une procédure, une fonction et un package.
Les sous-programmes
peuvent être récursifs et surchargés.
Script
de création d'une table des employés :
prompt création
de la table des employés
drop table employe;
create table employe
( numero
number (5,0) not null,
nom char(25) );
prompt Bloc
PL/SQL d'initialisation de la table des employes declare
i_num
employe.numero%type;
i_nom
employe.nom%type;
lettre1 number;
lettre2 number;
lettre3 number;
lettre4 number;
begin
i_num:=1;
lettre1:=65;lettre2:=65;lettre3:=65;lettre4:=65;
while
i_num <1000
loop
i_nom:=chr(lettre1) || chr(lettre2) || chr(lettre3) ||
chr(lettre4);
insert
into employe values (i_num,i_nom);
commit;
lettre1:=lettre1+1;
if
lettre1 > 65+25 then lettre1:=65;
lettre2:=lettre2+1;
end if;
if
lettre2 > 65+25 then lettre2:=65;
lettre3:=lettre3+1;
end if;
if
lettre3 > 65+25 then lettre3:=65;
lettre4:=lettre4+1;
end if;
i_num:=i_num+1;
end loop;
end;
/
prompt creation de l'index primaire idx_emp
create unique index pk_emp on employe (numero);
select * from employe;