PL/SQL

21/01/2021
 Cette partie décrit les caractéristiques générales du langage
PL/SQL :
 Structure d’un programme ;
 Déclaration et affectation de variables ;
 Structures de contrôle (si, tant que, répéter, pour) ;
 Mécanismes d’interaction avec la base ;
 Programmation de transactions.

[Link] 2
 Introduction au PL/SQL
 Tableaux et structures
 Interaction avec la base
 Curseurs
 Exceptions
 Sous-programmes: Procédures et Fonctions
 Triggers
 Packages
[Link] 3
 PL/SQL
21/01/2021
 Le PL de PL/SQL signifie Procedural Language.

 Il s’agit d’une extension procédurale du SQL permettant

d’effectuer des traitements complexes sur une base de données.

 C’est un langage de programmation à la fois puissant, simple et

moderne destiné à exprimer facilement vos règles de gestion
complexes sous forme de procédures et de triggers stockés.

[Link] 5
 PL/SQL, langage de programmation éprouvé, offre de
nombreux avantages :
 intégration parfaite du SQL ;
 support de la programmation orientée objet ;
 très bonnes performances ;
 portabilité ;
 facilité de programmation ;

[Link] 6
 Le PL/SQL n’interprète pas une commande, mais un ensemble
de commandes contenues dans un « bloc » PL/SQL. Ce bloc
peut comporter plusieurs « sous-blocs ».
 Sa structure est simple d’apprentissage et lisible en termes de
maintenance de programmes.

[Link] 7
Les sections indispensables d’un bloc sont le BEGIN et le END. Elles peuvent
être précédées d’une zone optionnelle de déclaration de variables,
DECLARE, puis d’une zone optionnelle de traitement des erreurs,
EXCEPTION.
[Link] 8
 Vous pouvez placer des « sous-blocs » uniquement dans les
sections BEGIN et EXCEPTION, pas dans la partie DECLARE.

[Link] 9
 Les zones DECLARE et EXCEPTION sont facultatives.
 Chaque instruction, de n’importe quelle section, se termine
par un « ; ».
 Il est possible d’insérer des commentaires :

[Link] 10
 PL/SQL permet de déclarer des variables et des constantes, puis de les
utiliser dans des ordres SQL ou des traitements. Seul impératif
président à l’utilisation d’une variable : elle doit d’abord être déclarée.
Cette déclaration s’effectue dans la zone DECLARE.

[Link] 11
 Une variable se déclare de la sorte :
nom variable [CONSTANT] type [ [NOT NULL] := expression ] ;

nom variable représente le nom de la variable:

•30 caractères au plus
•commence par une lettre
•peut contenir lettres, chiffres, _, $ et #
CONSTANT indique que la valeur ne pourra pas être modifiée dans le
code du bloc PL/SQL
NOT NULL indique que la variable ne peut pas être NULL.

[Link] 12
  Types prédéfinis

Types caractères:
CHAR[(n)] : Chaîne de caractères de longueur fixe avec n compris entre 1 et 32767.
VARCHAR2(n): Chaîne de caractères de longueur variable avec n compris entre 1 et
32767.
etc.

[Link] 13
  Types prédéfinis

Types numériques:
NUMBER[(e,d)]: Nombre réel avec e chiffres significatifs stockés et d décimales
BINARY_INTEGER: Nombre entier compris entre -2 147 483 647 et +2 147 483 647
etc.

[Link] 14
  Types prédéfinis

Types pour les grands objets :

BLOB : Permet de stocker un objet binaire jusqu'à 4 Go
CLOB : Permet de stocker un ensemble de caractères, jusqu'à 4 Go
etc.

[Link] 15
 Pour afficher le contenu d’une variable, les procédures
DBMS_OUTPUT.PUT() et DBMS_OUTPUT.PUT_LINE()
prennent en argument une valeur à afficher ou une variable
dont la valeur est à afficher.

[Link] 16
Par défaut, les fonctions d’affichage sont désactivées. Il
convient, à moins que vous ne vouliez rien voir s’afficher, de
les activer avec la commande SET SERVEROUTPUT ON.

[Link] 17
 C’est l’une des plus importantes caractéristiques de PL/SQL. Ces
structures permettent de contrôler l’exécution des ordres SQL et de
traiter de façon procédurale les données. Les principales structures sont
les suivantes :
 IF-THEN-ELSE ;
 FOR-LOOP ;
 WHILE-LOOP ;
 EXIT-WHEN ;
 GOTO (peu utilisé).

[Link] 18
 Traitements conditionnels

Le IF et le CASE fonctionnent de la même façon que dans les autres langages

[Link] 19
 Traitements conditionnels

Le switch du langage C s'implémente en PL/SQL de la façon suivante :

[Link] 20
 LOOP ... END LOOP ; permet d’implémenter les boucles

 L’instruction EXIT WHEN permet de quitter une boucle

[Link] 21
 La boucle FOR existe aussi en PL/SQL :

 Ainsi que la boucle WHILE :

[Link] 22
 PL/SQL
21/01/2021
 Un structure est un type regroupant plusieurs types.
 Pour utiliser un enregistrement, vous devez d'abord le définir puis
déclarer une variable de ce type.

 Il existe 3 types d'enregistrements :

 définis par programme
 basés sur une table
 basés sur un curseur

[Link] 24
Définis par programme

 Une variable de type structure contient plusieurs variables,

ces variables s’appellent aussi des champs.

[Link] 25
Définis par programme : Exemple

[Link] 26
Enregistrements basés sur une table

 Vous n'aurez pas a définir explicitement des enregistrements

basés sur une table car ils sont implicitement définis avec la même
structure que la table.

 Les variables de ce type sont déclarées via l'attribut %ROWTYPE.

[Link] 27
Enregistrements basés sur une table : Exemple

[Link] 28
 Un enregistrement entier peut être assigné à un autre du même
type

 un enregistrement ne peut être comparé à un autre via des

opérateurs booléens. Au lieu de cela, il convient de comparer les
champs individuels des enregistrements.

[Link] 29
 Les enregistrements imbriqués sont des enregistrements contenus
dans des champs qui sont eux-mêmes des enregistrements.

[Link] 30
 PL/SQL
21/01/2021
 Ce cours est une introduction aux interactions possibles entre
la base de données et les scripts PL/SQL.

[Link] 32
 Ordres SQL en PL/SQL
Consultation par SELECT
Modification des données par les ordres de manipulation INSERT,
UPDATE et DELETE.
Contrôle des transactions par COMMIT, ROLLBACK, ou SAVEPOINT.
…

[Link] 33
 La seule instruction capable d’extraire des données à partir
d’un programme PL/SQL est SELECT.

 La particularité de cette instruction au niveau de PL/SQL est la

directive INTO comme le montre la syntaxe suivante:

[Link] 34
 Exemple

[Link] 35
 La clause INTO est obligatoire
 Les interrogations doivent renvoyer une seule ligne

[Link] 36
 %Type : Déclaration d’une variable du même type que
une colonne d’une table ou d’une vue

Une autre variable déclarée

[Link] 37
 %ROWTYPE: Une variable peut contenir toutes les colonnes
d’une ligne d’une table/vue

Exemple :
employe emp%ROWTYPE;

déclare que la variable «employe» contiendra une ligne de la table emp

[Link] 38
 C’est une structure pour représenter une ligne d’une table :

[Link] 39
 Modifier des tables de la BD en utilisant des instructions LMD
INSERT
UPDATE
DELETE

[Link] 40
 Insérer des données

[Link] 41
 Mise à jour

UPDATE nomTable
SET nomColonne = { nomVariablePLSQL
|expression | nomColonne |
[WHERE …];

l’identificateur à gauche de l'opérateur d'affectation est

toujours une colonne de base de données, alors que celui à
droite de l'opérateur peut correspondre à une colonne ou une
variable.
[Link] 42
 Mise à jour

[Link] 43
 Supprimer des données
DELETE FROM nomTable
WHERE nomColonne =
{ nomVariablePLSQL | expression | …}
[,nomColonne2 = … ] …;

Si aucun enregistrement n’est modifié, aucune erreur ne se

produit et aucune exception n’est levée.

[Link] 44
 PL/SQL
21/01/2021
 Lorsque l'on exécute un ordre SQL à partir de PL/SQL, Oracle
alloue une zone de travail privée pour cet ordre.
 Cette zone de travail contient des informations relatives à
l'ordre SQL ainsi que les statuts d’exécution de l’ordre.
 Les curseurs PL/SQL sont un mécanisme permettant de
nommer cette zone de travail et de manipuler les données
qu'elle contient.

[Link] 46
 Un curseur PL/SQL permet de récupérer et de traiter les
données de la base dans un programme PL/SQL, ligne par
ligne.
 Il existe deux sortes de curseurs :
Les curseurs implicites.
Les curseurs explicites.

[Link] 47
 Le langage PL/SQL crée de manière implicite un curseur pour
chaque ordre SQL, même pour ceux qui ne retournent qu'une
ligne.
 Les curseurs implicites sont gérés par Oracle

[Link] 48
 La technique du curseur déclaré explicitement permet de
parcourir une à une les lignes du résultat d'une requête.

 L'intérêt du curseur est donc de pouvoir récupérer (par un

SELECT) plusieurs lignes, ce que ne permet pas le SELECT
normal en PL/SQL .

[Link] 49
Pour les requêtes qui renvoient plus d'un enregistrement, vous
pouvez déclarer explicitement un curseur, ce qui permet de
traiter individuellement les lignes retournées

Les étapes de la vie d'un curseur explicite

1. Déclaration du curseur
2. Ouverture du curseur
3. Traitement des lignes
4. Fermeture du curseur.

[Link] 50
  Tout curseur explicite utilisé dans un bloc PL/SQL doit
obligatoirement être déclaré dans la section « DECLARE
» du bloc, en précisant son nom et l'ordre SQL associé.

La requête peut contenir tous les ordres SQL d’interrogation de

données
[Link] 51
Dans l’exemple suivant vous pouvez observer la création d’un
curseur c_employe qui contient les colonnes NOM, PRENOM,
SALAIRE pour l’ensemble des enregistrements de la table
EMPLOYES.

[Link] 52
 Le curseur renvoie une structure des données correspondante
à la requête qu’il utilise.
 Il est également possible de récupérer cette structure à l’aide
de l’attribut « %ROWTYPE ».

[Link] 53
  Dès que vous ouvrez le curseur, l'exécution de
l'ordre SQL est lancée. Cette phase d'ouverture
s'effectue dans la section « BEGIN » du bloc.
 L’ouverture déclenche :
l’allocation mémoire du curseur,
l’analyse syntaxique et sémantique de l’ordre SELECT,

Syntaxe : OPEN nom_curseur;

[Link] 54
 Attention vous ne pouvez pas ouvrir un curseur qui est déjà
ouvert, si tel est le cas vous obtiendrez une erreur.

[Link] 55
  L'ordre « OPEN » a forcé l'exécution de l'ordre SQL
associé au curseur. Il faut maintenant récupérer les
lignes de l'ordre « SELECT » et les traiter une par
une, en stockant la valeur de chaque colonne de
l'ordre SQL dans une variable.

Syntaxe :

[Link] 56
 Exemple

La commande « FETCH » retourne un enregistrement

[Link] 57
 Lorsque vous exécutez l’instruction « FETCH » le curseur doit
impérativement être ouvert, dans le cas contraire une erreur se produit.
[Link] 58
 La commande « FETCH » peut aussi être combinée à la clause
«BULK COLLECT» pour renvoyer une collection d'enregistrements.

[Link] 59
 On peut récupérer des informations sur un curseur par
l'intermédiaire des attributs suivants :
 <nom_du_curseur>%ISOPEN : retourne TRUE si le curseur est ouvert.

 <nom_du_curseur>%FOUND : retourne TRUE ou FALSE suivant que le

dernier FETCH a retourné une ligne ou non.

IF NOT moncurseur%ISOPEN LOOP

THEN FETCH mcurseur INTO var;
OPEN moncurseur; EXIT WHEN NOT mcurseur%FOUND;
END IF; END LOOP;

[Link] 60
  <nom_du_curseur>%NOTFOUND : retourne TRUE ou FALSE à
l'inverse de %FOUND

LOOP
FETCH mcurseur INTO var;
EXIT WHEN mcurseur%NOTFOUND;
END LOOP;

 <nom_du_curseur>%ROWCOUNT : retourne le nombre de FETCH

ayant renvoyé une ligne. Avant le premier FETCH, cet attribut renvoie 0.

DBMS_OUTPUT.PUT_LINE(TO_CHAR(moncurseur%ROWCOUNT));

[Link] 61
  Chaque fois que vous ouvrez un curseur,
n'oubliez pas de le fermer pour pouvoir libérer
la place mémoire.
 La base de données a un nombre de curseurs
limites pour toutes les sessions.

La syntaxe de fermeture d’un curseur est :

[Link] 62
 Dans la mesure où l'utilisation principale d'un curseur est le
parcours d'un ensemble de lignes ramenées par l'exécution du
« SELECT » associé, il peut être intéressant d'utiliser une
syntaxe plus simple pour l'ouverture du curseur et le parcours
de la boucle.

[Link] 63
  Oracle propose une variante de la boucle « FOR » qui déclare
implicitement la variable de parcours, ouvre le curseur, réalise
les « FETCH » successifs et ferme le curseur.
La syntaxe pour l’utilisation d’un curseur dans une boucle « FOR » est :

[Link] 64
 Si l'on utilise un curseur pour faire des mises à jour, il est
préférable d'utiliser la syntaxe suivante :
DECLARE
CURSOR moncurseur IS
SELECT champ1,champ2 FROM table
FOR UPDATE;

Cette déclaration verrouille les lignes de la table sélectionnée.

Personne d'autre ne pourra mettre à jour ces lignes pendant le
traitement de cette procédure.

[Link] 65
 On n'est pas obligé de verrouiller tous les champs de la ligne.
On peut choisir les champs comme ceci :

DECLARE
CURSOR moncurseur IS
SELECT champ1,champ2 FROM table
FOR UPDATE OF champ1,champ2;

[Link] 66