SEANCE 6
LECON 1 . Introduction
LECON 2. Les variables et types de données
LECON 3. Interactions avec Serveur Oracle
SEANCE 6
LECON 1 . INTRODUCTION
Objectifs
A la fin de ce chapitre, vous saurez :
• Définir ce qu’est la PL/SQL
• Décrire les avantages de la PL/SQL
• Reconnaître un bloc PL/SQL de base et ses différentes
parties
• Ecrire des programmes simples en PL/SQL
Sommaire
I. Définition
II. Avantages du PL/SQL
III. Ecrire un programme simple en PL/SQL
IV. Quelques délimiteur du PL/SQL
Rappel
Les clauses SQL
Clauses Catégorie
SELECT Interrogation des données
INSERT
UPDATE Langage de Manipulation de Données
DELETE (LMD)
CREATE
ALTER
DROP Langage de Définition de Données (LDD)
RENAME
TRUNCATE
GRANT Langage de Contróle de Données (LCD)
REVOKE
COMMIT
ROLLBACK Contróle de transaction
SAVEPOINT
I. Définition
I. Définition
• SQL est utilisé dans les bases de données relationnelles
pour exécuter diverses requêtes telles que créer une
table, supprimer une table, insérer dans une table, etc.
• PL/SQL est utilisé pour écrire des blocs de programme,
des procédures, des fonctions, des curseurs, des
déclencheurs et des packages .
I. Définition
• Le PLSQL permet d'écrire des requêtes SQL complexes,
gérer des transactions et manipuler les données
directement dans le même environnement de
programmation.
• Il permet de combiner des requêtes SQL et des
instructions procédurales (boucles, conditions...), dans le
but de créer des traitements complexes destinés à être
stockés sur le serveur de base de données
SQL EST UN LANGAGE INCOMPLET
SQL NE PERMET PAS D’IMLEMENTER TOUTES LES REGLES METIER
LES LANGAGES DE PROGRAMMATION SONT COMPLETS
SQL EST LA SEULE INTERFACE POUR ACCEDER A UNE BD RELATIONNELLE
POUR FAIRE LES BD RELATIONNELLES:
SQL + LP
I. Définition
• Les unités de programme PL/SQL sont compilées par le
serveur de base de données Oracle et stockées dans la
base de données.
• Et au moment de l'exécution, PL/SQL et SQL s'exécutent
dans le même processus serveur, ce qui offre une
efficacité optimale.
II. Avantages du PL/SQL
• Aperçu
• Intégration
• Amélioration des performances
• Portabilité
• Développement modulaire
Avantages de PL/SQL
• PL/SQL complément de SQL (qui n’est pas procédural)
• Mécanismes offerts par PL/SQL :
– Structures itératives : WHILE *** LOOP, FOR *** LOOP, LOOP ***
– Structures conditionnelles : IF *** THEN *** ELSE | ELSEIF
*** ENDIF, CASE ***
– Déclaration des curseurs et des tableaux
– Déclaration de variables
– Affectation de valeurs aux variables
– Branchements : GOTO, EXIT
– Exceptions : EXCEPTION
Avantages de PL/SQL
• Intégration:
– Meilleure cohérence du code avec les données
– Utilisation de librairies standards prédéfinies (bibliothèques
– partagées)
Avantages de PL/SQL
• Amélioration des performances :
– Traitement par/de blocs SQL dans un énoncé PL/SQL
– (optimisation des transactions réseaux)
Avantages de PL/SQL
• Développement (Exécution de programmes) modulaire:
– Traitements complexes (cas particuliers, erreurs)
– ... Procédure/
Bloc
Fonction
Anonyme
DECLARE Stockée
BEGIN Procédure/
Trigger
Fonction
Applicatif
Applicative
EXCEPTION
END;
Trigger
Package
basé
Avantages de PL/SQL
• Résumé:
– Langage portable
– Utilisation de variable de stockage
– Utilisation de type simple ou de type structuré dynamiquement
(%TYPE, %ROWTYPE, etc.)
– Utilisation des structures de contrôle des langages procéduraux
(conditionnelles, itératives, curseurs,..)
– Gestion et manipulation des erreurs
– Création d’ordre SQL dynamique
Utilisation du PL/SQL
• 3 formes :
– Bloc de code, executé comme une commande SQL (utilisation
d’un interpréteur standard SQL+ ou iSQL*PLus)
– Fichier de commandes PL/SQL
– Programme stocké : procédure, fonction, package ou trigger
III. Ecrire un simple programme
PL/SQL
• Un bloc PL/SQL minimum peut être représenté de la
façon suivante :
BEGIN ü Le mot clé BEGIN détermine le début de
la section des instructions exécutables
Null ;
END ; ü L e mo t c l é EN D ; i ndi que l a f i n de la
section des instructions exécutables
/
Chaque instruction se termine par un “;”
Ecrire un programme ‘hello word’
SET SERVEROUTPUT ON
BEGIN
dbms_output.put_line('Hello world!');
END;
/
Structure d'un Block PL/SQL
Ecrire un programme
DECLARE
Partie déclaration DECLARE
-- déclaration & initialisation des variables
BEGIN
BEGIN
/*
* execution de la requête PL SQL Partie exécution
EXCEPTION
*/
/* END;
Partie exception
* exception
*/
END;
/ Les commentaires sont possibles /* */
Ecrire un programme
SET SERVEROUTPUT ON
DECLARE
Partie déclaration
myVar1 number; --déclaration seulement
myVar2 number := 111; --déclaration et initialisation
BEGIN
dbms_output.put_line(‘hello student ID ’ || myVar2);
Partie exécution
END;
/
Dans les langages de programmation, on écrit: myVar2 = 111
Dans PL SQL: myVar2 := 111
Ecrire un programme
Ecrire un programme
Exercice:
On veut afficher le nom et prénom à l’aide du PL/SQL.
1. On cherche d’abord à déclarer deux variables ‘nom’ et
‘prénom’ et à les initialiser avec le nom et prénom.
2. Puis on affiche à l’écran “Bonjour ‘nom’ et ‘prénom’”
Ecrire un programme
Solution:
DECLARE
nom varchar2(20):= 'Sadouk';
prenom varchar2(20):= 'Lamyaa';
BEGIN
dbms_output.put_line('Bonjour ' || nom || ' ' || prenom);
END;
/
Ecrire un programme
Solution
Ecrire un programme
Exercice:
On veut afficher le nom et prénom saisi par l’utilisateur à
l’aide du PL/SQL.
1. On cherche d’abord à déclarer deux variables ‘nom’ et
‘prénom’ et à les initialiser avec le nom et prénom saisis par
l’utilisateur .
2. Puis on affiche à l’écran “Bonjour ‘nom’ et ‘prénom’”
Ecrire un programme
SET SERVEROUTPUT ON
DECLARE
v_NOM VARCHAR2(20):='&L_NOM';
v_PRENOM VARCHAR2(20):='&L_PRENOM';
BEGIN
DBMS_OUTPUT.PUT_LINE ('NOM: ' || v_NOM);
DBMS_OUTPUT.PUT_LINE ('PRENOM: ' || v_PRENOM);
END;
/
Entrez une valeur pour l_nom : Jamil
Entrez une valeur pour l_prenom : Mohammed
NOM: Jamil
PRENOM: Mohammed
Procédure PL/SQL terminée avec succès.
SQL> SET VERIFY OFF; --pour ne pas afficher les anciennes
valeurs &…
SET SERVEROUTPUT ON
DECLARE
v_NOM VARCHAR2(20):='&L_NOM';
v_PRENOM VARCHAR2(20):='&L_PRENOM';
BEGIN
DBMS_OUTPUT.PUT_LINE ('NOM: ' || v_NOM);
DBMS_OUTPUT.PUT_LINE ('PRENOM: ' || v_PRENOM);
END;
/
Entrez une valeur pour l_nom : Jamil
Entrez une valeur pour l_prenom : Mohammed
NOM: Jamil
PRENOM: Mohammed
Procédure PL/SQL terminée avec succès.
IV. Quelques délimiteurs dans
PL/SQL
IV. Quelques délimiteurs dans PL/SQL
• Un délimiteur est un symbole simple ou composé qui a
une signification particulière pour PL/SQL .
• Par exemple, vous pouvez utiliser des délimiteurs pour
représenter des:
– opérations arithmétiques telles que l'addition et la soustraction.
IV. Quelques délimiteurs dans PL/SQL
Délimiteurs / Description
Symboles
+, -, *, / Opérateurs arithmétiques
‘ Symbole pour délimiter un texte
-- Commentaire d’une seule ligne
/* */ Commentaire de plusieurs lignes
; Symbole pour terminer une instruction
:= =
|| Opérateur de concaténation
> , >= , < , <=, = Opérateur relationnel
=> Opérateur d’association
IV. Quelques délimiteurs dans PL/SQL
Exemple
Résumé
• Les blocs PL/SQL sont composés des sections
suivantes :
–Declarative (optionelle) DECLARE
BEGIN
–Executable (obligatoire) EXCEPTION
–Exceptions (optionelle) END;
Résumé
• Un bloc PL/SQL peut être un:
– bloc anonyme
– une procédure
– ou une fonction
SEANCE 6
LECON 2. Les variables et types de
données
Utilisation des variables
• On utilise les variables pour :
– Le stockage temporaire de données
– La manipulation de valeurs stockées
– La possibilité de réutilisation
– Une maintenance plus facile
Types de Variables
• Les variables PL/SQL :
1. Scalaires
2. Composées
3. Références
4. LOB (Large OBject)
Types de Variables
• Les variables PL/SQL :
1. Scalaires
2. Composées
3. Références
4. LOB (Large OBject)
1. Types de données scalaires
• Ils stockent une seule valeur
• Ils n’ont pas de composante interne
“ Je ne veux pas
aller à l’école.
Je veux rester au
FALSE
lit.”
546.88 Casablanca
1. Types de données scalaires
• 4 catégories :
• nombres,
• caractères,
• dates,
• booléens.
Types de données scalaires
Type Description
BINARY_INTEGER entiers allant de -2^31 à 2^31
POSITIVE / NATURAL entiers positifs allant à 2^31
INTEGER entiers allant de -2^126 à 2^126
NUMBER [(precision, scale)] Numérique (entre -2^418 à 2^418
CHAR [(maximum_length)] Chaine fixe de 1 à 32767 caractères
VARCHAR2 (maximum_length) Chaine variable (1 à 32767 caractères)
LONG Comme VARCHAR2 (maximum 2 gigaoctets)
Permet de stocker des données binaires relativement faibles (moins
de 32767 octets) comme VARCHAR2
RAW
Les données RAW ne subissent jamais de conversion de caractères
lors du transfert entre le programme et BD
LONG RAW Comme long mais avec du binaire
DATE date
BOOLEAN Booléen
Déclarer des variables
• Syntaxe :
identifiant [CONSTANT] datatype [NOT NULL]
[:= | DEFAULT expr];
• Exemple :
Declare
v_hiredate DATE;
v_deptno NUMBER(2) NOT NULL := 10;
v_location VARCHAR2(13) := 'Atlanta';
c_comm CONSTANT NUMBER := 1400;
1. Types de données scalaires
• Exemples
v_job VARCHAR2(9);
v_count BINARY_INTEGER := 0;
v_total_sal NUMBER(9,2) := 0;
v_orderdate DATE := SYSDATE + 7;
c_tax_rate CONSTANT NUMBER(3,2) := 8.25;
v_bool1 BOOLEAN ;
v_bool2 BOOLEAN := TRUE;
v_valid BOOLEAN NOT NULL := FALSE;
v_date date;
1. Types de données scalaires
• L’Attribut %TYPE
– Déclarer une variable à partir de :
• la définition d’une colonne de la base de données
• la définition d’une variable précédemment déclarée
– Puis, le PL/SQL détermine le type de donnée et la taille
de la variable.
1. Types de données scalaires
• L’Attribut %TYPE
– Déclarer une variable à partir de :
• la définition d’une colonne de la base de données
• la définition d’une variable précédemment déclarée
– Exemple:
...
v_ename emp.ename%TYPE;
...
1. Types de données scalaires
• L’Attribut %TYPE
– Déclarer une variable à partir de :
• la définition d’une colonne de la base de données
• la définition d’une variable précédemment déclarée
– Exemple:
...
v_balance NUMBER(7,2);
v_min_balance v_balance%TYPE := 10;
...
1. Types de données scalaires
– L’Attribut %TYPE
• Préfixer %TYPE avec :
– La table et la colonne de la base
– Le nom de la variable précédemment déclarée
1. Types de données scalaires
• Déclarer des Variables Booléennes
– Seules les valeurs TRUE, FALSE et NULL peuvent être
affectées à une variable booléenne.
– Ces variables sont reliées par les opérateurs logiques
AND, OR et NOT.
– Ces variables retournent toujours TRUE, FALSE ou
NULL.
Types de Variables
• Les variables PL/SQL :
1. Scalaires
2. Composées
3. Références
4. LOB (Large OBject)
2. Types de Données Composés
• PL/SQL TABLES
• PL/SQL RECORDS
Types de Variables
• Les variables PL/SQL :
1. Scalaires
2. Composées
3. Références
4. LOB (Large OBject)
• Variables de types LOB
Avec les types LOB Document
(CLOB)
(Large OBject), on peut
stocker des blocs de
Photo
données non-structurées: (BLOB)
• texte, images, clips
vidéo ou sons Film
• d’une taille pouvant (BFILE)
aller jusqu’à 4
gigaoctets.
NCLOB
Résumé
• Les variables PL/SQL :
• Sont définis/déclarés dans la section de déclaration
• Peuvent être initialisés aussi dans cette section
(optionnel)
• Peuvent être de type scalaire, composé, référence
ou LOB
• Peuvent être basés sur la structure d’une autre
variable ou d’un objet de la base de données
(%TYPE)
SEANCE 6
LECON 3. Interactions avec le
Serveur Oracle
Objectifs
A la fin de ce chapitre, vous saurez :
• Ecrire une instruction SELECT en PL/SQL
• Ecrire des instructions LMD en PL/SQL
• Contrôler les transactions en PL/SQL
Objectifs
• Pour cela, nous allons apprendre à effectuer les
instructions suivantes:
– Extraire une ligne de la base de grâce à la commande SELECT.
Un seul jeu de valeurs peut être retourné.
– Modifier des lignes dans la base en utilisant des commandes
LMD.
– Contrôler une transaction avec les commandes COMMIT,
ROLLBACK, ou SAVEPOINT.
I. Extraire des données en
PL/SQL
I. Extraire des données en PL/SQL
• Extraire une ligne de la base de données avec SELECT.
• Syntaxe
SELECT select_list
INTO {variable_name}
FROM table
WHERE condition;
I. Extraire des données en PL/SQL
• Exemple
I. Extraire des données en PL/SQL
• Exemple
I. Extraire des données en PL/SQL
• Exemple
• On voudra afficher le ID
(identifiant) du pays qui a
comme nom = ‘Egypt’
I. Extraire des données en PL/SQL
• Exemple
DECLARE
v_country_ID char(2);
BEGIN
select country_id
into v_country_ID
from countries
where country_name = 'Egypt';
dbms_output.put_line('L''identifiant du pays est :' || v_country_ID);
END;
I. Extraire des données en PL/SQL
• Remarque:
– La variable v_country_ID doit être du même type de
données que la colonne country_id.
I. Extraire des données en PL/SQL
Exercice
• On voudra afficher le ID (identifiant) du pays ainsi que le
ID de la région tel que le pays est ‘Egypt’
I. Extraire des données en PL/SQL
• Exemple
DECLARE
v_country_ID char(2);
v_region_ID number;
BEGIN
select country_id , region_ID
into v_country_ID, v_region_ID
from countries
where country_name = 'Egypt';
dbms_output.put_line('L''identifiant du pays est :' || v_country_ID);
dbms_output.put_line('L''identifiant de la région est :' || v_region_ID);
END;
I. Extraire des données en PL/SQL
Solution
II. Manipuler des données en
PL/SQL
II. Manipuler des données en PL/SQL
• Le but est de modifier le contenu des tables de la base de
données à l’aide des commandes LMD suivantes :
– INSERT
– UPDATE
– DELETE INSERT
UPDATE
DELETE
II. Manipuler des données en PL/SQL
1. Insérer des données
Exemple
• Ajouter les informations concernant un nouveau pays à la
table ‘Country’.
• Exemple
DECLARE
v_country_ID char(2) := ‘MO’; char(2);
v_country_name varchar2(40) := ‘Morocco’;
BEGIN
INSERT INTO countries(country_ID, country_name, region_ID)
VALUES(v_country_ID, v_country_name, 4);
END;
II. Manipuler des données en PL/SQL
2. Mettre à jour des données
Exemple
• Augmenter le prix ‘list_price’de tous les produits de la table
products dont l’ID de la catégorie ‘cateogory_ID’est 1.
• Exemple
DECLARE
v_price_augm products.List_Price%TYPE := 2000;
BEGIN
UPDATE products
SET List_Price = List_Price+ v_price_augm
WHERE category_ID = 1;
END;
II. Manipuler des données en PL/SQL
II. Manipuler des données en PL/SQL
2. Supprimer des données
Exemple
• Supprimer les enregistrements dont le département est le
10 dans la table emp.
• Exemple
DECLARE
v_category_ID products.category_ID%Type := 1;
BEGIN
DELETE FROM products
WHERE category_ID = v_category_ID;
END;
II. Manipuler des données en PL/SQL
Conventions d’appellation
• Les colonnes de la base de données et les
identifiants de vos variables doivent avoir des noms
différents.
• Des erreurs de syntaxe peuvent survenir car
PL/SQL cherche d’abord dans la base une colonne
de la table.
II. Manipuler des données en PL/SQL
Conventions d’appellation (ERREUR!)
DECLARE
country_ID countries.country_ID%TYPE;
country_name countries.country_name%TYPE := 'Egypt';
region_ID countries.region_ID%TYPE;
BEGIN
select country_ID, region_ID
into country_ID, region_ID
from countries
where country_name = country_name;
dbms_output.put_line('L''identifiant du pays: ' || country_ID);
dbms_output.put_line('L''identifiant de la région: ' || region_ID);
END;
II. Manipuler des données en PL/SQL
Conventions d’appellation (CORRECT)
DECLARE
v_country_ID countries.country_ID%TYPE;
v_country_name countries.country_name%TYPE := 'Egypt';
v_region_ID countries.region_ID%TYPE;
BEGIN
select country_ID, region_ID
into v_country_ID, v_region_ID
from countries
where country_name = v_country_name;
dbms_output.put_line('L''identifiant du pays: ' || v_country_ID);
dbms_output.put_line('L''identifiant de la région: ' || v_region_ID);
END;
Exercises
• Créer un bloc PL/SQL pour sélectionner des
données d’une table
• Créer un bloc PL/SQL pour insérer des données
dans une table
• Créer un bloc PL/SQL pour mettre à jour des
données d’une table
• Créer un bloc PL/SQL pour supprimer un
enregistrement d’une table
III. Contrôler les transactions
en PL/SQL
III. Contrôler les transactions en PL/SQL
• Une transaction est initialisée par la première
commande LMD, et doit être suivi d’un COMMIT
ou d’un ROLLBACK.
• Utilisez les instructions SQL COMMIT ou
ROLLBACK pour terminer de façon explicite une
transaction.
