Le langage PL/SQL

1
 Introduction
• 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 partie LID de SQL (Select),
• la partie LMD de SQL (Update, Insert, Delete),
• 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).
2
 Introduction
• Tout comme SQL, PL/SQL peut être utilisé au sein des outils de la
famille Oracle comme : Sql*Plus, Sql*Forms, Sql*Pro, ....
• 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).

3
 Structure d’un bloc
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. 4
 Exemple de bloc
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 ;
/
5
 Les déclarations PL/SQL
• 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.

6
 Les types de données
Types de données PL/SQL
Types Scalaires Types Composés
binary_integer record
natural
positive
decimal
float table
integer
real
smallint
number
char
long
varchar
varchar2
boolean
date
rowid
raw
long raw 7
 Conversion de types
• 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, to_number.

• les conversions implicites sont réalisées automatiquement par PL/SQL.

8
 Variables et constantes
• Les variables se définissent dans la partie DECLARE du bloc PL/SQL en
utilisant la syntaxe suivante :

nomvariable [CONSTANT]
{type | variable%TYPE | table.%ROWTYPE }
[NOT NULL]
[{:= | DEFAULT } expression PL/SQL]

9
 Exemples de déclarations
DECLARE
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;
BEGIN
10
 Assignement des variables
• Deux possibilités d'assignement ou d'affectation sont disponibles :
1. par l'opérateur d'affectation : ':=',
2. par la clause Select ... Into ... .

• La difficulté dans l'utilisation de la clause Select résulte du nombre de

lignes ou d'occurrences retourné.
• 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.
11
 Les tableaux
• 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.
12
 Déclarations 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.
•
• La syntaxe pour déclarer un type TABLE dans la partie déclarative d'un bloc est :
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.

13
 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;

14
 Exemple de tableau
DECLARE
TYPE nom_tabtype IS TABLE OF CHAR(25)
INDEX BY BINARY_INTEGER ;
...
tnom nom_tabtype ;
...
BEGIN
...
tnom(1):='Dupont Marc' ;
...
END ;
/
15
 Les enregistrements
• Ladé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.

• 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.

16
 Exemple Enregistrement
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 ;
17
 Variable de liaison
• Une variable de liaison est utilisée dans les blocs PL/SQL et dans SQL*PLUS.
• Elle est créée avec la commande VAR dans SQL*PLUS.

• Création de la variable dans SQL*PLUS : VAR [IABLE] [nom [type] ]

VAR maVar VARCHAR2(10)

• Utilisation dans un bloc : « :maVar »

Begin
:maVar := ‘bonjour’;
dbms_output.put_line(:maVar);
end;
• On accède à la variable avec un « : » devant. 18
 Variable de liaison
• Création et initialisation de la variable dans SQL*PLUS
VAR maVar2 VARCHAR2(10);
EXEC :maVar2 := 'bonjour2';
Print :maVar2 ;

• EXEC permet d’exécuter une commande PLSQL, ici une affectation ( :=)
• Print permet d’afficher le contenu de la variable dans SQL*PLUS

• Utilisation dans un bloc :

BEGIN
dbms_output.put_line(:maVar2);
END; 19
 Instruction d’entrée
• La saisie de la valeur d’une variable se fait en utilisant l’opérateur &
• Exemples :
• Prénom := &Prénom
• SELECT nomc, ville FROM CLIENT WHERE nom LIKE '%M&chaine.ET%';

• On peut également saisir la valeur d’une variable globale avec

l’instruction accept et cela en dehors du bloc PL/SQL :
• SQL>accept Vnom
• SQL>Ben Salah

20
 Instructions de sortie
• Pour afficher un message à l’écran il suffit d’utiliser la commande Prompt
suivie d’un message. Cette commande est utilisée en dehors des blocs.
• SQL>prompt Bonjour à tous !
• SQL> Bonjour à tous !
•
• Pour afficher des résultats on utilise le package (ensemble de procédures et
fonctions) DBMS_OUTPUT.
• Tout d'abord, avant le bloc PL/SQL, on doit utiliser l'instruction :
• Set serveroutput on
•
Puis pour chaque affichage :
• dbms_output.put_line('texte'||X); -- (remarque : la concaténation de
chaînes de caractères avec : ||)
21
 Exemple
SQL>SET SERVEROUTPUT ON

DECLARE
V_ville varchar2(20) ;
code Client.Codc%Type;

BEGIN
code:=&code;
SELECT ville INTO V_ville FROM Client WHERE codc=code;

DBMS_OUTPUT.PUT_LINE(‘La ville du client ‘|| code ||’:‘|| V_ville);

END ;
/ 22
 Structures conditionnelles
• 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.
23
 Structures conditionnelles
DECLARE
vjob char(10);
vnom emp.ename%type:='Miller';
message char(30);
BEGIN
Select job into vjob from emp where ename=vnom;
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 ;
/
24
 Structure conditionnelle Selon
CASE expression
WHEN valeur_1 THEN traitement_1
WHEN valeur_2 THEN traitement_2 ...
WHEN valeur_n THEN traitement_n
ELSE traitement
END CASE
OU BIEN
CASE
WHEN condition_1 THEN traitement_1
WHEN condition_2 THEN traitement_2 ...
WHEN condition_n THEN traitement_n
ELSE traitement
END CASE
25
 Structure conditionnelle Selon
CASE
WHEN n_salary < 2000 THEN v_msg := 'Low';
WHEN n_salary >= 2000 and n_salary <=3000 THEN v_msg := 'Fair';
WHEN n_salary >= 3000 THEN v_msg := 'High';
END CASE;
----------------------------------------

CASE Epreuve
WHEN ‘DS’ THEN coef := 1,5;
WHEN ‘Examen’ THEN coef := 3;
WHEN ‘TP’ THEN coef := 2;
END CASE;
26
 Structures itératives – L’instruction LOOP
• Syntaxe :
[<<Label>>] LOOP
...
instructions
...
END LOOP [<<Label>>];

• Les commandes EXIT, EXIT WHEN condition et GOTO permettent de

sortir de la Boucle.

27
 Structures itératives – L’instruction LOOP
DECLARE
nombre number;
BEGIN
nombre:=0;
LOOP
nombre:=nombre+1;
if nombre > 10
then exit;
end if;
END LOOP ;
END ;
/
28
Structures itératives – L’instruction FOR…LOOP
• Syntaxe :
[<<Label>>]
FOR compteur IN [REVERSE] var_debut .. var_fin LOOP
...
instructions
...
END LOOP [<<Label>>];

• Les commandes EXIT, EXIT WHEN condition et GOTO permettent de

sortir de la Boucle.

29
Structures itératives – L’instruction WHILE…LOOP
• Syntaxe :
WHILE condition_plsql LOOP
...
instructions
...
END LOOP ;

• Les commandes EXIT, EXIT WHEN condition et GOTO permettent de

sortir de la Boucle.

30
Structures itératives – L’instruction WHILE…LOOP
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 ;
/
31