Le LangagePL/SQL
- partie 1 -
1
Le Langage de Bloc
PL/SQL
• PL/SQL:
–‘Procédural Language’ : est une sur-couche à SQL
+ boucles, contrôles, affectations,
exceptions, ….
–Chaque programme est un bloc (BEGIN – END)
– Programmation adaptée pour une architecture Client - Serveur
2
Bloc PL/SQL
• Le bloc de requêtes est envoyé sur le serveur:
celui-ci exécute tout le bloc et renvoie un résultat
final.
CLIENT SERVEUR
BEGIN
INSERT … Exécution
SI ….
ALORS du bloc
SELECT …
FSI
PL/SQL
END; Résultat
3
Structure d’un bloc PL/SQL
• Section DECLARE : déclaration de DECLARE
– Variables locales simples
– Variables tableaux --déclarations
– …
• Section BEGIN BEGIN
– Section des ordres exécutables
– Ordres SQL --exécutions
– Ordres PL
• Section EXCEPTION
EXCEPTION
– Réception en cas d’erreur
– Exceptions SQL ou utilisateur --erreurs
END;
4 /
Structure d’un bloc PL/SQL
DECLARE optionnelle
Variables, ...
BEGIN obligatoire
Instructions SQL et PL/SQL
Possibilités de blocs fils (imbrication de blocs)
EXCEPTION optionnelle
Traitement des exceptions (gestion des erreurs)
END ; obligatoire
5
Exemple d’un bloc PL/SQL
DECLARE
var_x VARCHAR(5);
BEGIN
SELECT nom_colonne
INTO var_x
FROM nom_table
EXCEPTION
WHEN nom_exception THEN
......
END ;
/
6
Types PL/SQL et déclarations
Les types scalaires:
• PL/SQL supporte les types du langage SQL (CHAR, VARCHAR , NUMBER , DATE)
et offre des types supplémentaires pour déclarer des variables et des constantes :
Type Explication
BINARY_INTEGER Nombre entier compris entre -2 147 483 647 et +2 147 483 647
NUMBER[(e,d)] Nombre réel avec e chiffres significatifs et d décimales
BOOLEN Une variable booléenne peut recevoir les valeurs TRUE, FALSE ou NULL
NATURAL Sous-ensemble de BINARY_INTEGER des entiers de 0 à 2147483647
…
7
Evaluation des expressions
• L’ évaluation des expressions repose sur les règles de priorité
des opérateurs ci-dessous présentés de la plus haute vers la
plus faible priorité.
• L’usage des ( ) est autorisé pour imposer une priorité dans
l’évaluation d’une expression
Opérateur Opération
Haute ** , NOT Puissance, négation logique
*,/ Multiplication, division
+, - , || Addition, soustraction, concaténation
Faible <, <=, != , >, >=, Inférieur, inférieur ou égale, différent,..,
IS NULL, test de nullité,
BETWEEN, IN intervalle , appartenance.
AND ET logique
OR OU logique 8
Déclaration des variables en PL/SQL
Syntaxe:
Identificateur [CONSTANT] type_données [NOT NULL] [:= expression];
• Variables de type SQL :
nbr NUMBER(2) ;
nom VARCHAR(30) ;
minimum CONSTANT INTEGER := 5 ;
salaire NUMBER(8,2) ;
debut NUMBER NOT NULL := 5.2 ;
• Variables de type booléen (TRUE, FALSE, NULL)
fin BOOLEAN ;
ok BOOLEAN := TRUE;
9
Affectation des variables en PL/SQL
Syntaxe:
Identificateur := expr;
Exemple:
Affecter la date de naissance
v_datenaissance := ’23-SEP-2004’;
10
Les directives
%TYPE
et
%ROWTYPE
11
La directive %TYPE
• On peut déclarer qu’une variable est du même type :
• qu’une colonne d’une table
• ou qu’une autre variable
Syntaxe: nom_variable1 nom_variable2%TYPE ;
%TYPE : se lit de même type que
12
La directive %TYPE
• On peut déclarer qu’une variable est du même type :
• qu’une colonne d’une table
• ou qu’une autre variable
Syntaxe: nom_variable1 nom_variable2%TYPE ;
%TYPE : se lit de même type que
Exemples:
• v_salaire est une variable de même type que la colonne salaire de la
table employe :
v_salaire employe.salaire%TYPE;
• v_montant est une variable de même type que la variable v_salaire:
v_montant v_salaire%TYPE; 13
La directive %ROWTYPE
•Une variable peut contenir toute une ligne d’une table :
•PL/SQL permet de déclarer une variable de même structure qu’une ligne
d’une table en utilisant le type générique %ROWTYPE :
Syntaxe: nom_variable nom_table%ROWTYPE
Exemples:
v_employe est une variable de même type qu'une ligne de la table employe :
v_employe employe%ROWTYPE;
La variable v_employe contiendra une ligne de la table employe
14
Variables faisant référence aux tables
•Exemple d’une variable qui référence une ligne de table :
v_employe employe%ROWTYPE;
•Contenu d’une variable qui référence une ligne de table :
– syntaxe: variable.colonne
Exemple:
v_employe.salaire
15
Exemple d’utilisation
v_employe employe%ROWTYPE ;
v_nom employe.name%TYPE ;
Select * INTO v_employe
From employe
where employe_no = 009 ;
v_nom := v_employe.name;
…
v_employe.salaire := 2000;
Insert into employe values v_employe;
16
Le Type enregistrement :
RECORD
17
Type enregistrement (RECORD):
• PL/SQL offre un type composé: le type enregistrement (RECORD)
• Un enregistrement peut être défini en définissant ses éléments:
La déclaration se fait en deux étapes :
1. Déclaration du type enregistrement :
Syntaxe: TYPE nom-type IS RECORD (
champ1 type1,
champ2 type2, …);
Exemple: TYPE emp_ligne IS RECORD (
matricule integer,
nom varchar(30)
);
2. Déclaration d’une variable du type enregistrement :
Syntaxe: nom_variable nom-type ;
Exemple: v_employe emp_ligne;
v_employe.matricule := 500 ; … 18
L’instruction SELECT dans PL/SQL
Récupérer une donnée de la BD avec SELECT.
SELECT liste_colonnes
INTO { nom_var1 [, nom_var2] ……
| nom_record
}
FROM table
WHERE condition;
19
Exemple d’instruction SELECT dans PL/SQL
La clause INTO est obligatoire
DECLARE
v_departement_no NUMBER(2);
v_departement_adresse VARCHAR2(15);
BEGIN
SELECT deptno, adresse
INTO v_departement_no,
v_departement_adresse FROM departement
WHERE nom_dept =‘INFORMATIQUE’;
……
END;
20
Exemple d’instruction SELECT dans PL/SQL
calculer la somme des salaires de tous les
employés d’un département donné :
DECLARE
v_somme_salaires employe.salaire%TYPE;
v_departement_no NUMBER NOT NULL := 10;
BEGIN
SELECT sum (salaire) --fonction d’agrégat
INTO v_somme_salaires
FROM employe
WHERE deptno = v_departement_no;
END;
21
Insertion de données
Ajouter les informations d’un nouvel employé à la table employe
DECLARE
v_emp_no NUMBER NOT NULL := 105;
BEGIN
INSERT INTO employe (empno, emp_nom, deptno)
VALUES (v_emp_no, ‘TOUNSI’, 10);
END;
22
Mise à jour de données
Augmenter le salaire de tous les employés dans la table employe.
DECLARE
v_augm_sal emp.salaire%TYPE := 200;
BEGIN
UPDATE employe
SET salaire = salaire + v_augm_sal
END;
23
Suppression de données
Suppression des lignes appartenant au département 10 de la
table emp.
DECLARE
v_ deptno emp.deptno%TYPE := 10;
BEGIN
DELETE FROM emp
WHERE deptno = v_deptno;
END;
24
Affichage dans PL/SQL
25
Affichage utilisant
le Package DBMS_OUTPUT
• Messages enregistrés dans une mémoire tampon côté serveur
• Le contenu de la mémoire tampon est affichée sur le poste client à la fin
Serveur ORACLE
Client SQL/PLUS BEGIN
DBMS_OUTPUT.PUT_LINE('Message1');
DBMS_OUTPUT.PUT_LINE('Message2');
Message1 DBMS_OUTPUT.PUT_LINE('Message3');
Message2 END;
Message3
Message1
Message2
Message3
SQL> SET SERVEROUT ON
Mémoire tampon
26
Les Structures de contrôle dans PL/SQL
• IF conditionnel
• Les boucles :
- LOOP …
- FOR …
- WHILE …
27
Instruction IF
Syntaxe: IF condition THEN
énoncés;
[ELSIF condition THEN
énoncés;]
[ELSIF condition THEN
énoncés;]
…
[ELSE
énoncés;]
END IF;
28
Exemple IF-THEN-ELSE
Si le nom de l’employé est ‘Clément’,
alors attribuer le département n° 102,
sinon afficher le message ‘Employé inexistant’.
……
IF v_nom = ‘Clément’ THEN
v_deptno := 102;
…
ELSE
DBMS_OUTPUT.PUT_LINE(‘Employé inexistant’);
END IF;
……
29
Boucle de base (LOOP)
Syntaxe :
LOOP -- délimiteur
énoncé 1; -- énoncé
énoncé 2;
…….
EXIT [ WHEN condition]; -- énoncé EXIT
END LOOP; -- délimiteur
30
Exemple Boucle de base (LOOP)
Insérer 10 articles :
……
v_compteur NUMBER(2) := 1;
BEGIN
……
LOOP
INSERT INTO article VALUES (v_compteur, …, ... ...);
v_compteur := v_compteur + 1;
EXIT WHEN (v_compteur > 10);
END LOOP;
……
31
Boucle FOR
Syntaxe :
FOR indice IN [REVERSE] borne_inf .. Borne_sup LOOP
énoncé 1;
énoncé 2;
……..
END LOOP;
indice : variable entière déclarée implicitement par la boucle
(Ne pas déclarer l’indice, il est déclaré implicitement)
indice : s’incrémente automatiquement de 1 à la fin de
chaque itération
32
Boucle FOR
Insérer 10 articles indexés de 1 à 10 en utilisant la boucle FOR
Exemple:
……
BEGIN
……
FOR i IN 1 .. 10 LOOP
INSERT INTO article VALUES ( i , …., ... ...);
END LOOP;
……
33
Boucle WHILE
Syntaxe:
WHILE condition LOOP
énoncé 1;
énoncé 2; La condition est
…….. évaluée au début de
END LOOP; chaque itération.
Utiliser la boucle WHILE pour répéter des
énoncés tant que la condition est vraie
34
Boucle WHILE
Exemple:
DECLARE
…
v_compteur NUMBER(2) := 1;
BEGIN
……
WHILE (v_compteur < 10) LOOP
INSERT INTO article VALUES (compteur, …);
v_compteur := v_compteur + 1;
END LOOP;
……
35
