<!-- Slide number: 1 -->
PROGRAMMER AVEC PL/SQL
PL/SQL
1
Notes:
<!-- Slide number: 2 -->
PLAN DU MODULE
2 me partie : PL / SQL
Pr sentation du PL/SQL
Mod les de programmes en PL/SQL
Structure dun bloc PL/SQL anonyme
La d claration
Les instructions
Les curseurs (explicites)
Concepts avanc s des curseurs explicites
Les exceptions
Les sous-programmes
Les triggers
2
Notes:
<!-- Slide number: 3 -->
QUEST CE QUE PL/SQL ?
" PL/SQL est un langage proc dural
" Il est utilis dans le noyau Oracle (oracle7, oracle8) et les produits Oracle
" SQL :
est un langage ensembliste et non proc dural
" PL/SQL :
est un langage proc dural, qui int gre des ordres SQL de gestion de la base de donn es
3
<!-- Slide number: 4 -->
FONCTIONNALITES DU PL/SQL ?
" Instructions SQL int gr es dans PL/SQL :
- SELECT, INSERT, UPDATE, DELETE
- Gestion des transaction : COMMIT, ROLLBACK, &
- Les fonctions : TO_CHAR, TO_DATE, UPPER, SUBSTR, ROUND, &
" Instructions sp cifiques PL/SQL :
- D finition des variables
- Traitements conditionnels
- Traitements r p titifs
- Traitements des curseurs
- Traitements des erreurs
4
<!-- Slide number: 5 -->
FONCTIONNEMENT SQL ET PL/SQL
" SQL et PL ont tous deux un moteur associ
- SQL STATEMENT EXECUTOR
- PROCEDURAL STATEMENT EXECUTOR
" Le moteur SQL se trouve toujours avec le noyau RDBMS
" Le moteur PL/SQL peut se trouver avec le noyau RDBMS et les outils Oracle
5
<!-- Slide number: 6 -->
LE MOTEUR PL/SQL
" Le moteur SQL interpr te les commandes une une
" Le moteur PL/SQL interpr te des blocs de commandes
SQL
Oracle sans
PL/SQL
Application
SQL
SQL
IF & THEN
SQL
ELSE
SQL
END IF
Oracle avec
PL/SQL
Application
6
<!-- Slide number: 7 -->
STRUCTURE DUN BLOC PL/SQL
" Le bloc PL/SQL :
PL/SQL ninterpr te pas une commande, mais un ensemble de commandes contenu dans un bloc
PL/SQL.
" Structure dun bloc PL/SQL :
Un bloc est compos de trois sections.
DECLARE
d claration de variables, constantes, exceptions, curseurs
BEGIN
instructions SQL et PL/SQL
EXCEPTION
traitement des erreurs
END;
7
<!-- Slide number: 8 -->
STRUCTURE DUN BLOC PL/SQL
" Les section DECLARE et EXCEPTION sont facultatifs
" Chaque instruction de nimporte quelle section est termin e par un ;
" Dans la section BEGIN, possibilit de sous-blocs (imbrication de blocs)
" Possibilit de placer des commentaires
-- commentaire sur une ligne
ou
/* commentaire sur
plusieurs lignes */
8
<!-- Slide number: 9 -->
STRUCTURE DUN BLOC PL/SQL
" Exemple :
PROMPT nom du produit desir
ACCEPT nom_prod
DECLARE
qte_stock number (5) ;
BEGIN
select quantite into qte_stock
from stock
where produit = &nom_prod ;
IF qte_stock > 0 then
update stock
set quantite = quantite 1
where produit = &nom_prod ;
insert into vente
values (&nom_prod || VENDU, SYSDATE) ;
ELSE insert into commande
values (&nom_prod || DEMANDE, SYSDATE) ;
END IF ;
COMMIT ;
END ;
/
9
<!-- Slide number: 10 -->
DECLARATION DE VARIABLES
" Les variables locales se d finissent dans la partie DECLARE dun bloc PL/SQL
" Variable PL/SQL de type ORACLE
- Syntaxe :
nom_var CHAR ; -- longueur max 255
nom_var NUMBER ; -- longeur max 38
nom_var DATE ;
- Exemple :
DECLARE
nom char (15) ;
num ro number ;
date_jour date ;
salaire number (7,2) ;
BEGIN
&
END ;
10
<!-- Slide number: 11 -->
DECLARATION DE VARIABLES
" Variables PL/SQL de type bool en
- Syntaxe :
nom_var BOOLEAN ; -- valeur (TRUE, FALSE, NULL)
- Exemple :
Publicité
DECLARE
reponse BOOLEAN ;
BEGIN
&
END ;
11
<!-- Slide number: 12 -->
INITIALISATION ET VISIBILITE DES VARIABLES
" Linitialisation dune variable peut se faire par :
- Lop rateur := dans la section DECLARE, BEGIN et EXCEPTION
- Lordre SELECT & INTO dans la section BEGIN
- Le traitement dun curseur dans la section BEGIN
" Une variable est visible dans le bloc o elle a t d clar e, et dans les blocs imbriqu s si elle na pas t red finie
12
<!-- Slide number: 13 -->
INITIALISATION ET VISIBILITE DES VARIABLES
" Lop rateur :=
DECLARE
nom char (10) := BONJOUR ;
salaire number (7,2) := 1500 ;
reponse boolean := TRUE ;
BEGIN
&
END ;
- Figer laffectation de valeur une variable avec la clause CONSTANT
DECLARE
pi constant number (7,2) := 3.14 ;
BEGIN
&
END ;
13
<!-- Slide number: 14 -->
INITIALISATION ET VISIBILITE DES VARIABLES
" Lordre SELECT
- Syntaxe :
select col1, col2
into var1, var2
from nom_table
;
- R gle :
La clause INTO est obligatoire
Le SELECT doit obligatoirement ramener une ligne et une seule, sinon erreur
(Pour traiter un ordre SELECT qui pourrait ramener plusieurs lignes, on utilise un curseur).
14
<!-- Slide number: 15 -->
INITIALISATION ET VISIBILITE DES VARIABLES
- Exemple :
DECLARE
nom_emp char (15) ;
salaire emp.sal%TYPE ;
commission emp.comm%TYPE ;
nom_depart char (15) ;
BEGIN
select ename, sal, comm, dname
into nom_emp, salaire, commission, nom_depart
from emp, dept
where ename = MILLER and
emp.deptno = dept.deptno ;
&
END ;
15
<!-- Slide number: 16 -->
TRAITEMENTS CONDITIONNELS
" D finition :
Ex cution dune instruction en fonction du r sultat dune condition.
" Syntaxe :
IF condition1 then traitement1 ;
ELSIF condition2 then traitement2 ;
ELSE traitement3 ;
END IF ;
Les op rateurs utilis s dans les conditions sont les m mes que dans SQL :
= < > != >= <=
IS NULL, IS NOT NULL, BETWEEN, LIKE, AND, OR, etc.
" R gles :
- D s que lune des conditions est vraie, ex cution du traitement qui suit le THEN.
- Si aucune condition nest vraie, ex cution du traitement ELSE.
- Seules les clauses IF, THEN, END IF sont obligatoires.
16
<!-- Slide number: 17 -->
TRAITEMENTS CONDITIONNELS
" Exemple :
DECLARE
emploi char (10) ;
nom char (15) := MILLER ;
mes char (30) ;
BEGIN
select job into emploi
from emp where ename = nom ;
if emploi is null then
mes := nom || na pas demploi ;
elsif emploi = SALESMAN then
update emp set comm = 1000
where ename = nom ;
mes := nom || commission modifi e ;
else update emp set comm = 0
where ename = nom ;
mes := nom || pas de commission ;
end if ;
insert into resultat values (mes) ;
commit ;
END ;
17
<!-- Slide number: 18 -->
TRAITEMENTS REPETITIFS
" D finition :
Ensemble dinstructions crites une seule fois et ex cut es plusieurs reprises.
PL/SQL permet deffectuer des traitements r p titifs gr ce la clause LOOP.
" trois types de boucles :
- La boucle de base
- La boucle FOR
- La boucle WHILE
18
<!-- Slide number: 19 -->
LA BOUCLE DE BASE
" Syntaxe :
BEGIN
loop
instructions ;
end loop ;
END ;
Sortie de la boucle de base par la commande : EXIT [ when condition]
" Exemple : Ins rer les 10 premiers chiffres dans la table RESULTAT.
DECLARE
nbre number := 1 ;
BEGIN
loop
insert into resultat
values (nbre) ;
nbre := nbre + 1 ;
exit when nbre > 10 ;
end loop ;
END ;
19
<!-- Slide number: 20 -->
LA BOUCLE FOR
" Syntaxe :
FOR indice IN exp1, exp2
LOOP
instructions ;
END LOOP ;
" R gles :
- D claration implicite de la variable indice
- exp1, exp2 : constantes, expressions ou variables
- Sans loption REVERSE, indice varie de exp1 exp2 avec un incr ment de 1
- Avec loption REVERSE, indice varie de exp2 exp1 avec un pas de 1.
Publicité
" Exemple : calcul de factorielle 9
DECLARE
fact number := 1 ;
BEGIN
for i in 1,9
loop
fact := fact * i ;
end loop ;
insert into resultat
values (fact, FACTORIELLE 9) ;
END ;
20
<!-- Slide number: 21 -->
LA BOUCLE WHILE
" Lex cution de la boucle se fait tant que la condition de la clause WHILE est v rifi e.
" Syntaxe :
BEGIN
WHILE condition
LOOP
instructions ;
END LOOP ;
END ;
La condition peut tre une combinaison dexpressions au moyen dop rateurs : < > = != AND OR &
" Exemple : reste de la division de 7324 par 9
DECLARE
reste number := 7324 ;
BEGIN
while reste >= 9
loop
reste := reste 9 ;
end loop ;
insert into resultat values (reste, Reste division de 7324 par 9) ;
END ;
21
<!-- Slide number: 22 -->
LES CURSEURS EN PL/SQL
" D finition :
Zone de m moire de taille fixe, utilis e par le noyau dOracle pour analyser et interpr ter tout ordre SQL.
" Deux types de curseurs :
- Le curseur implicite : curseur SQL g n r et g r par le noyau pour chaque ordre SQL.
- Le curseur explicite : curseur SQL g n r et g r par lutilisateur pour traiter un ordre
SELECT qui ram ne plusieurs lignes.
22
<!-- Slide number: 23 -->
ETAPES DUTILISATION DUN CURSEUR EXPLICITE
" Lutilisation dun curseur explicite pour traiter un ordre SELECT susceptible de ramener
plusieurs lignes n cessite 4 tapes :
- D claration du curseur
- Ouverture du curseur
- Traitement des lignes
- Fermeture du curseur
23
<!-- Slide number: 24 -->
ETAPES DUTILISATION DUN CURSEUR EXPLICITE : DECLARATION
" D claration :
Tout curseur explicite utilis dans un bloc PL/SQL doit tre d clar dans la section DECLARE du
bloc en donnant :
- son nom
- lordre SELECT associ
" Syntaxe :
CURSOR nom_curseur IS ordre_select ;
" Exemple :
DECLARE
cursor dept_10 is
select ename, sal from emp where deptno = 10 order by sal ;
BEGIN
&
END ;
24
<!-- Slide number: 25 -->
ETAPES DUTILISATION DUN CURSEUR EXPLICITE : OUVERTURE
" Ouverture :
Apr s avoir d clar le curseur, ouverture de celui-ci pour faire ex cuter lordre SELECT
- allocation m moire du curseur
- analyse syntaxique et s mantique de lordre SELECT
- positionnement de verrous ventuels (si SELECT & for update)
" Louverture dun curseur se fait dans la section BEGIN du bloc
" Syntaxe :
OPEN nom_curseur ;
" Exemple :
DECLARE
cursor dept_10 is select ename, sal from emp where deptno = 10 order by sal ;
BEGIN
&
open dept_10 ;
&
END ;
25
<!-- Slide number: 26 -->
ETAPES DUTILISATION DUN CURSEUR EXPLICITE :TRAITEMENT DES LIGNES
" Traitement des lignes :
Apr s lex cution du SELECT les lignes ramen es sont trait es une par une, la valeur de chaque colonne du SELECT doit tre stock e dans une liste de variables r ceptrices
" Syntaxe :
FETCH nom_cursor INTO liste_variables ;
Le FETCH ram ne une seule ligne la fois; pour traiter n lignes, pr voir une boucle
26
<!-- Slide number: 27 -->
ETAPES DUTILISATION DUN CURSEUR EXPLICITE : TRAITEMENT DES LIGNES
" Exemple :
DECLARE
cursor dept_10 is select ename, sal from emp where deptno = 10 order by sal ;
nom emp.ename%TYPE ;
salaire emp.sal%TYPE ;
BEGIN
open dept_10 ;
loop
fetch dept_10 into nom, salaire ;
if salaire > 2500 then
insert into resultat
values (nom, salaire) ;
end if ;
exit when salaire = 5000 ;
end loop ;
END ;
27
<!-- Slide number: 28 -->
ETAPES DUTILISATION DUN CURSEUR EXPLICITE : FERMETURE
" Fermeture :
Apr s le traitement des lignes, pour lib rer la place m moire, on ferme le curseur.
" Syntaxe :
CLOSE nom_ curseur ;
28
<!-- Slide number: 29 -->
ETAPES DUTILISATION DUN CURSEUR EXPLICITE : FERMETURE
" Exemple :
DECLARE
cursor dept_10 is
select ename, sal from emp
where deptno = 10
order by sal ;
nom emp.ename%TYPE ;
salaire emp.sal%TYPE ;
BEGIN
open dept_10 ;
loop
fetch dept_10 into nom, salaire ;
if salaire > 2500 then
insert into resultat values (nom, salaire) ;
end if ;
exit when salaire = 5000 ;
Publicité
end loop ;
close dept_10 ;
END ;
29
<!-- Slide number: 30 -->
ETAPES DUTILISATION DUN CURSEUR EXPLICITE
" Exemple : trouver les n plus gros salaires de la table EMP.
Prompt nombre de salaire ?
Accept nombre
DECLARE
cursor c1 is
select ename, sal from emp
order by sal DESC ;
vename emp.ename%TYPE ;
vsal emp.sal%TYPE ;
BEGIN
open c1 ;
for i in 1..&nombre
loop
fetch c1 into vename, vsal ;
insert into resultat values (vsal, vename) ;
end loop ;
close c1 ;
END ;
/
Select chaine NOM, num SALAIRE from resultat
/
Rollback
/
30
<!-- Slide number: 31 -->
LES ATTRIBUTS DUN CURSEUR
" D finition :
Les attributs dun curseur (implicite ou explicite) sont des indicateurs sur l tat dun curseur.
%FOUND
%NOTFOUND
%ISOPEN
%ROWCOUNT
Derni re ligne trait e
Ouverture dun curseur
Nombre de lignes d j trait es
31
<!-- Slide number: 32 -->
LES ATTRIBUTS DUN CURSEUR : %FOUND
" Attribut %FOUND
" Type : bool en
" Curseur implicite : SQL%FOUND
INSERT
TRUE (vrai) UPDATE
DELETE
SELECT & INTO : ram ne une et une seule ligne
" Curseur explicite : nom_curseur%FOUND ;
TRUE (vrai) le dernier FETCH a ramen une ligne
Traite au moins une ligne
32
<!-- Slide number: 33 -->
LES ATTRIBUTS DUN CURSEUR : %FOUND
" Exemple :
DECLARE
cursor dept_10 is
select ename, sal from emp
where deptno = 10
order by sal ;
nom emp.ename%TYPE ;
salaire emp.sal%TYPE ;
BEGIN
open dept_10 ;
fetch dept_10 into nom, salaire ;
while dept_10%FOUND loop
if salaire > 2500 then
insert into resultat
values (nom, salaire) ;
end if ;
fetch dept_10 into nom, salaire ;
end loop ;
close dept_10 ;
END ;
33
<!-- Slide number: 34 -->
LES ATTRIBUTS DUN CURSEUR : %NOTFOUND
" Attribut %NOTFOUND
" Type : bool en
" Curseur implicite : SQL%NOTFOUND
INSERT
TRUE (vrai) UPDATE
DELETE
SELECT & INTO : ne ram ne pas de ligne
" Curseur explicite : nom_curseur%NOTFOUND ;
TRUE (vrai) le dernier FETCH na pas ramen de ligne
Ne traite aucune ligne
34
<!-- Slide number: 35 -->
LES ATTRIBUTS DUN CURSEUR : %NOTFOUND
" Exemple :
DECLARE
cursor dept_10 is
select ename, sal from emp
where deptno = 10
order by sal ;
nom emp.ename%TYPE ;
salaire emp.sal%TYPE ;
BEGIN
open dept_10 ;
loop
fetch dept_10 into nom, salaire ;
exit when dept_10%NOTFOUND ;
if salaire > 2500 then
insert into resultat
values (nom, salaire) ;
end if ;
end loop ;
close dept_10 ;
END ;
35
<!-- Slide number: 36 -->
LES ATTRIBUTS DUN CURSEUR : %ISOPEN
" Attribut : %ISOPEN
" Type : bool en
" Curseur implicite : SQL%ISOPEN
Toujours FALSE car Oracle referme les curseurs apr s utilisation
" Curseur explicite : nom_curseur%ISOPEN
TRUE (vari) le curseur est ouvert
36
<!-- Slide number: 37 -->
LES ATTRIBUTS DUN CURSEUR : %ISOPEN
" Exemple :
DECLARE
cursor dept_10 is
select ename, sal from emp
where deptno = 10
order by sal ;
nom emp.ename%TYPE ;
salaire emp.sal%TYPE ;
BEGIN
if not (dept_10%ISOPEN) then
open dept_10 ;
Publicité
end if ;
loop
fetch dept_10 into nom, salaire ;
exit when dept_10%NOTFOUND ;
if salaire > 2500 then
insert into resultat
values (nom, salaire) ;
end if ;
end loop ;
close dept_10 ;
END ;
37
<!-- Slide number: 38 -->
LES ATTRIBUTS DUN CURSEUR : %ROWCOUNT
" Attribut : %ROWCOUNT
" Type : num rique
" Curseur implicite : SQL%ROWCOUNT
Nombre de lignes trait es
0 SELECT & INTO ne ram ne aucune ligne
1 SELECT & INTO ram ne exactement 1 ligne
2 SELECT & INTO ram ne plus d1 ligne
" Curseur explicite : nom_curseur%ROWCOUNT
Traduit la ni me ligne ramen e par le FECTH
INSERT
UPDATE
DELETE
38
<!-- Slide number: 39 -->
LES ATTRIBUTS DUN CURSEUR : %ROWCOUNT
" Exemple :
DECLARE
cursor dept_10 is
select ename, sal from emp
where deptno = 10
order by sal ;
nom emp.ename%TYPE ;
salaire emp.sal%TYPE ;
BEGIN
open dept_10 ;
loop
fetch dept_10 into nom, salaire ;
exit when dept_10%NOTFOUND or
dept_10%ROWCOUNT > 15 ;
if salaire > 2500 then
insert into resultat
values (nom, salaire) ;
end if ;
end loop ;
close dept_10 ;
END ;
39
<!-- Slide number: 40 -->
CURSEUR : TRAITEMENT SIMPLIFIE AVEC ROWTYPE
DECLARE
cursor dept_10 is
select ename, sal from emp
where deptno = 10
order by sal ;
record_dept dept_10%ROWTYPE ;
BEGIN
open dept_10 ;
loop
fetch dept_10 into record_dept;
exit when dept_10%NOTFOUND or
dept_10%ROWCOUNT > 15 ;
if record_dept.sal > 2500 then
insert into resultat
values (ename, sal) ;
end if ;
end loop ;
close dept_10 ;
END ;
40
<!-- Slide number: 41 -->
CURSEUR : TRAITEMENT SIMPLIFIE AVEC FOR - LOOP
DECLARE
cursor dept_10 is
select ename, sal from emp
where deptno = 10
order by sal ;
BEGIN
for record_dept in dept_10 loop
if record_dept.sal > 2500 then
insert into resultat
values (ename, sal) ;
end if ;
end loop ;
END ;
Pas de d claration de structure
Ouverture automatique du curseur
Condition de sortie de boucle automatique (fin denregistrements)
Fermeture automatique du curseur
41
<!-- Slide number: 42 -->
CURSEUR PARAMETRES
DECLARE
cursor dept_X (num_dep number) is
select ename, sal from emp
where deptno = num_dep
order by sal ;
record_dept dept_X%ROWTYPE ;
BEGIN
open dept_X (10) ;
loop
fetch dept_X into record_dept;
exit when dept_X%NOTFOUND or
dept_X%ROWCOUNT > 15 ;
if record_dept.sal > 2500 then
insert into resultat
values (ename, sal) ;
end if ;
end loop ;
close dept_X ;
END ;
42
<!-- Slide number: 43 -->
MISE A JOUR DES DONNEES AVEC UN CURSEUR
La clause CURRENT OF permet dacc der directement en criture la ligne en cours de lecture
La d claration du curseur doit tre associ e la pose dun verrou dintention (FOR UPDATE)
Exemple :
DECLARE
cursor curemp is
select ename, sal from emp where comm is NULL FOR UPDATE OF comm;
record_dept curemp%ROWTYPE ;
BEGIN
for record_dept in curemp loop
update emp set comm = sal/3 where CURRENT OF curemp;
end loop ;
commit;
END ;
43