03/11/2023

N. ABDAT

Le langage PL/SQL

28 oct 2023

Langage PL/SQL
(Procedural Language / Structured Query Language)

PL/SQL est un langage fondé sur les paradigmes de programmation

procédurale et structurée. Il est propriétaire, créé par Oracle et utilisé dans le
cadre de bases de données relationnelles.

Caractéristiques de PL/SQL

 Extension de SQL : des requêtes SQL cohabitent avec les structures de contrôle
habituelles de la programmation structurée (blocs, alternatives, boucles)
 Un programme est constitué de procédures et de fonctions
 Des variables permettent l’échange d’information entre les requêtes SQL et le
reste du programme

28 oct 2023 N. ABDAT

1
 03/11/2023

Utilisation de PL/SQL

 PL/SQL peut être utilisé pour l’écriture des procédures/ fonctions et des
triggers.
 Il sert également pour écrire des fonctions utilisateurs qui peuvent être utilisées
dans les requêtes SQL (en plus des fonctions prédéfinies).
 Il est aussi utilisé dans des outils Oracle, Forms et Report….

BLOC PL/SQL :
constitué de 3 sections :
 section de déclaration
 section des instructions
 section de gestion des erreurs

28 oct 2023 N. ABDAT

Section déclaration
 Contient la déclaration et initialisation de types, variables et constantes
utilisées dans le bloc
 Section facultative
 Commence par le mot clé DECLARE

Section des instructions :

 Contient les instructions du programme
 Section obligatoire
 Commence par le mot clé BEGIN et se termine par le mot clé END

Section gestion des erreurs :

 Contient le traitement des cas d’exception ou erreurs
 Incluse à la fin de la section des instructions
 Section facultative
 Commence par le mot clé EXCEPTION
28 oct 2023 N. ABDAT

2
 03/11/2023

Syntaxe :

BLOC PL/SQL :
[DECLARE … déclarations et initialisation]
BEGIN
… instructions exécutables
[EXCEPTION … interception des erreurs]
END;
Exemples de déclarations et initialisations :
DECLARE
mot CHAR(5); note NUMBER (4,2) := 10;
x NUMBER(4) := 0;
v1 table%ROWTYPE; (type du tuple d'une table)
v2 [Link]%TYPE; ( type d'un attribut d'une table)

28 oct 2023 N. ABDAT

Section des instructions :

Le IF dans PL/SQL

si-alors :
IF condition THEN instructions; END IF;

si-alors-sinon :
IF condition THEN instructions; ELSE instructions; END IF;

Imbrications de conditions :
IF condition_1 THEN instructions;
ELSIF condition_2 THEN instructions;
…………………
ELSIF condition_n THEN instructions;
ELSE instructions;
END IF;

28 oct 2023 N. ABDAT

3
 03/11/2023

Le CASE dans PL/SQL :

CASE variable
WHEN expr1 THEN instructions1;
WHEN expr2 THEN instructions2; …
WHEN exprN THEN instructionsN;
[ELSE instructionsN+1;]
END CASE;

CASE
WHEN condition1 THEN instructions1;
WHEN condition2 THEN instructions2; …
WHEN conditionN THEN instructionsN;
[ELSE instructionsN+1;]
END CASE;

28 oct 2023 N. ABDAT

Les boucles dans PL/SQL :

Boucle Pour : Boucle Tant que : Boucle Répéter :

FOR I IN 1 . . 10 WHILE condition LOOP instructions;
LOOP instructions; LOOP instructions; EXIT WHEN condition;
END LOOP; END LOOP; END LOOP ;

PRODEDURE ET FONCTIONS
Procédure :

CREATE [OR REPLACE] PROCEDURE nomProcédure

[(paramètre [ IN | OUT | IN OUT ] typeSQL [:= | DEFAULT] .... )]
IS Bloc PL/SQL ;

Fonction :
CREATE [OR REPLACE ] FUNCTION nomFonction
[(paramètre [ IN | OUT | IN OUT ] typeSQL ..... ) ] RETURN typeSQL
IS Bloc PL/SQL contenant Return;

28 oct 2023 N. ABDAT

4
 03/11/2023

EXTRAIRE UN TUPLE :

SELECT liste_attributs INTO liste_variables FROM nomTable ….…. ;

Exemples:

SELECT * INTO v1 Résultat :

FROM Bateau WHERE nbat=103; v1= (103, 'EL BAHDJA', 'BNA')

SELECT nombat INTO v2 Résultat :

FROM Bateau WHERE nbat=104; v2= 'LA COLOMBE '

Remarque :

-Une requête SELECT … INTO... doit renvoyer un seul enregistrement.

-Une requête SELECT … INTO... qui renvoie plusieurs enregistrements, ou
qui n’en renvoie aucun, génère une erreur PL/SQL .

28 oct 2023 N. ABDAT

EXTRAIRE PLUSIEURS TUPLES : LES CURSEURS (CURSOR) :

Pour traiter des requêtes renvoyant plusieurs enregistrements, on utilise des curseurs
PL/SQL.

Définition
Le curseur est une zone de mémoire de taille fixe, utilisé par le moteur de la base Oracle
pour analyser et interpréter les ordres SQL.

Il existe deux types de curseurs :

- Le curseur implicite : Curseur SQL généré et géré par ORACLE pour chaque ordre
SQL.

- Le curseur explicite : Curseur SQL généré et géré par l’utilisateur pour traiter un
ordre SELECT qui ramène plusieurs lignes.

28 oct 2023 N. ABDAT

5
 03/11/2023

Étape d’utilisation d’un curseur explicite :

Le curseur explicite permet de traiter individuellement chaque ligne renvoyée par un
SELECT. Il est décrit dans la partie déclarative et est ouvert dans le code du programme,
il s’évalue alors et va se charger en extrayant les données de la base.

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 et l’ordre SELECT associé.

Syntaxe CURSOR nom_curseur IS ordre_select ;

Exemple : DECLARE CURSOR cr IS SELECT nbat, nombat FROM bateau;

Ouverture
Après avoir déclaré le curseur, on "ouvre" celui-ci pour faire exécuter l’ordre SELECT.
L’ouverture déclenche :
• l’allocation mémoire du curseur,
• l’analyse syntaxique et sémantique de l’ordre SELECT
L’ouverture du curseur se fait dans la section BEGIN du bloc.
Syntaxe OPEN nom_curseur ; Exemple : OPEN cr ;
N. ABDAT
28 oct 2023

Traitement des lignes

Après l’exécution du SELECT, on peut parcourir tout le curseur en récupérant les lignes
une par une dans une variable locale.
FETCH permet le positionnement sur la ligne suivante et chargement de
l’enregistrement courant dans 1 ou plusieurs variables.

Syntaxe FETCH nomCurseur INTO listeVar | nomRECORD

Exemple : FETCH cr INTO b;

La variable b est déclarée comme suit : b cr%ROWTYPE;

Fermeture
Le curseur est fermé à la fin du traitement .
La fermeture du curseur se fait dans la section BEGIN du bloc.
Syntaxe CLOSE nom_curseur ;

-Un curseur, durant son existence (de l’ouverture à la fermeture), contient en

permanence l’adresse de la ligne courante.
-Un programme PL/SQL peut travailler avec plusieurs curseurs en même temps.
N. ABDAT
28 oct 2023

6
 03/11/2023

Boucle FOR (gestion semi-automatique)

-FOR de curseur évite les directives OPEN, FETCH et CLOSE.
-La boucle s'arrête d’elle-même à la fin de l’extraction de la dernière ligne du
curseur.
-La variable de réception du curseur est automatiquement déclarée .
-L’accès aux valeurs des colonnes se fait également par la notation pointée.

Exemple
DECLARE CURSOR cr IS SELECT nbat, nombat FROM bateau;
BEGIN
for b in cr loop
dbms_output.put_line('Le bateau N° ' || [Link]||' a pour nom '|| [Link]);
end loop;
END;

28 oct 2023 N. ABDAT

GESTION DES EXCEPTIONS

• Le traitement des cas particuliers ainsi que des erreurs peut se faire en définissant
des exceptions.
• Les exceptions peuvent se programmer dans un bloc PL/SQL, une fonction, une
procédure, un déclencheur...
• Il existe 2 types d'exception : les exceptions prédéfinies (du système) et les
exceptions définies par l'utilisateur.
• Une exception définie par un utilisateur doit être déclarée et déclenchée par ‘Raise’
Syntaxe :
DECLARE
nomexception exception;
BEGIN
instructions contenant RAISE nomexception;
EXCEPTION
WHEN nomexception THEN instructions;
END;

N. ABDAT
28 oct 2023

7
 03/11/2023

Exceptions prédéfinies :
Il existe des exceptions prédéfinies (celles qui se produisent le plus souvent). Oracle
leurs affecte des noms pour les traiter plus facilement dans le bloc EXCEPTION. Le
tableau suivant décrit des exemples d'exceptions prédéfinies :
Nom de l’exception Numéro Commentaires
CASE_NOT_FOUND ORA-06592 Aucun des choix de la structure CASE sans ELSE n’est effectué.
DUP_VAL_ON_INDEX ORA-00001 Insertion d’une ligne en doublon (clé primaire).
INVALID_NUMBER ORA-01722 Échec d’une conversion d’une chaîne de caractères en NUMBER.
NO_DATA_FOUND ORA-01403 Requête ne retournant aucun résultat.
TOO_MANY_ROWS ORA-01422 Requête retournant plusieurs lignes.
VALUE_ERROR ORA-06502 Erreur arithmétique (conversion, troncature, taille) d’un NUMBER
ZERO_DIVIDE ORA-01476 Division par zéro.
INVALID_CURSOR ORA-01001 Ouverture interdite sur un curseur.
CURSOR_ALREADY_OPEN ORA-06511 Ouverture d’un curseur déjà ouvert.

Exemple : utilisation de l'exception prédéfinie NO_DATA_FOUND

…..
BEGIN
SELECT …. INTO …. FROM … WHERE ….;
DBMS_OUTPUT.PUT_LINE (…………);
EXCEPTION
WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE (‘Information inexistante’ );
END;
28 oct 2023 N. ABDAT

Remarque :
On peut définir plusieurs exceptions dans un même programme, dans ce cas on écrira
dans la partie EXCEPTION :
EXCEPTION
WHEN exception1 [OR exception2 …] THEN instructions;
WHEN exception3 [OR exception4 …] THEN instructions;
...
[WHEN OTHERS THEN instructions;]

Variables d’environnement :
Ce sont des variables permettant de paramétrer une session SQL*Plus. L’affectation
d’une variable se fait par la commande SET et son état est donné par SHOW.
Commande Commentaires
SET LINESIZE {80 | n} Taille en caractères d’une ligne de résultats. Exemple :
SET PAGESIZE {24 | n} Taille en lignes d’une page de résultats.
SET PAGESIZE 10;
SET SERVEROUT {ON | Activation de l’affichage pour tracer des
OFF} exécutions. SHOW LINESIZE;
SET TERMOUT {ON|OFF} Affichage des résultats.
SET TIME {ON|OFF} Affichage de l’heure dans le prompt.

28 oct 2023 N. ABDAT