Burkina Faso
Unité- Progrès-Justice
Université Aube Nouvelle
ORACLE DEVELOPER :
1-Programmer avec Oracle: PL/SQL
2-Générateur d’application : Oracle Forms developer
Durée : 20 Heures
Enseignante : Madame IMA RASMATA Période :
Oracle PL/SQL Developer Certified Associate Novembre – Décembre 2023
Téléphone : 00226 77 55 96 62
Burkina Faso
Unité- Progrès-Justice
Université Aube Nouvelle
PROGRAMMER AVEC ORACLE PL/SQL
Durée : 10 Heures
PRE-REQUIS : Connaissance du Langage SQL
Burkina Faso
Unité- Progrès-Justice
Université Aube Nouvelle
RAPPELS SUR LE Langage SQL
RAPPEL SUR LE LANGAGE SQL (Structured uery Language)
1. DÉFINITION D’UNE BASE DE DONNÉES
Une base de données est un ensemble d'informations structurées.
Un SGBDR (Système de Gestion de Bases de Données Relationnel) est un logiciel qui
permet de :
- stocker,
- consulter,
- modifier,
- supprimer
les données de la base de données.
Un SGBDR stocke les informations dans des tables.
ORACLE DATABASE SQL / Année Académique 2020-2021 29/11/2023 4
1. DÉFINITION D’UNE BASE DE DONNÉES
SQL (Structured Query Language) :
- est le langage utilisé pour accéder aux données d'une base de données.
- est normalisé. C'est un standard adopté par l'ANSI (American National
Standards Institute).
ANSI SQL89 Université Aube Nouvelle
- est un langage ensembliste (non procédural)
- est un langage « universel » utilisé par :
* les administrateurs
* les développeurs
* les utilisateurs
pour :
* administrer et contrôler
* définir et développer
* manipuler
ORACLE DATABASE SQL / Année Académique 2020-2021 29/11/2023 5
2. CLSSIFICATION DES TYPES DE LANGAGE SQL
❖ Langage de manipulation de données (LMD) : Insert, Update, delete
Université Aube Nouvelle
❖ Langage de définition de données (LDD) : create, alter, drop, truncate
❖ Langage de controle de données : grant,revoke
❖ Langage de contrôle de transaction (LCT) : commit, rollback
❖ Langage d’extraction de données ou de requête : Select
ORACLE DATABASE SQL / Année Académique 2020-2021 29/11/2023 6
2. LES COMMANDES
Commandes de manipulation des données :
- SELECT : interrogation
- INSERT : insertion
- UPDATE : mise à jour
- DELETE : suppression Université Aube Nouvelle
Les commandes de définition de données :
- CREATE : création d'un objet
- ALTER : modification d'un objet
- TRUNCATE : supprimer les lignes d'une table
- DROP : supprimer un objet
- RENAME : renommer un objet
Remarque : les commandes GRANT et REVOKE seront vues dans le cours
d'administration.
ORACLE DATABASE SQL / Année Académique 2020-2021 29/11/2023 7
3. L'offre ORACLE
Université Aube Nouvelle
ORACLE DATABASE SQL / Année Académique 2020-2021 29/11/2023 8
Burkina Faso
Unité- Progrès-Justice
Université Aube Nouvelle
PROGRAMMER AVEC ORACLE PL/SQL
PROGRAMME
1. INTRODUCTION à PL*SQL
2. STRUCTURE D'UN BLOC PL/SQL
3. LES VARIABLES UTILISÉES DANS PL/SQL Université Aube Nouvelle
3.1. LES DIFFÉRENTS TYPES DE VARIABLES LOCALES
3.1.1. Variables de type ORACLE
3.1.2. Variables de type BOOLEEN 1
3.1.3. Variables faisant référence au dictionnaire de données
3.1.4. Initialisation des variables
3.1.5. Visibilité des variables
3.2. VARIABLES DE L'ENVIRONNEMENT EXTÉRIEUR À PL/SQL
4. LES TRAITEMENTS
4.1. IF : TRAITEMENT CONDITIONNEL
4.2. BOUCLE DE BASE LOOP : TRAITEMENT RÉPÉTITIF
4.3. BOUCLE FOR : TRAITEMENT RÉPÉTITIF
4.4. BOUCLE WHILE : TRAITEMENT RÉPÉTITIF
Matière : ORACLE PL/QL Année Académique : 2023-2024 29/11/2023
10
PROGRAMME
5. LES CURSEURS EN PL/SQL
5.1. DÉFINITIONS
5.2. CURSEUR EXPLICITE
5.3. LES ATTRIBUTS D'UN CURSEUR Université Aube Nouvelle
5.3.1. %FOUND
5.3.2. %NOTFOUND
5.3.3. %ISOPEN
5.3.4. %ROWCOUNT
5.4. SIMPLIFICATION D'ÉCRITURE
5.4.1. Déclaration de variables
5.4.2. Traitement du curseur
6. GESTION DES ERREURS EN PL/SQL
7. EXERCICES PL/SQL
7.1. EX1 : LES BOUCLES
7.2. EX2 : LES CURSEURS
7.3. EX3 : LES ERREURS
Matière : ORACLE PL/QL Année Académique : 2023-2024 29/11/2023 11
1. INTRODUCTION
SQL : est un langage ensembliste et non procédural
PL/SQL : est un langage procédural qui intègre des ordres SQL de gestion de la base de données
Instructions SQL intégrées dans PL/SQL :
- SELECT
Université Aube Nouvelle
- INSERT, UPDATE, DELETE
- COMMIT, ROLLBACK, SAVEPOINT
- TO_CHAR, TO_DATE, UPPER, ...
Instructions spécifiques à PL/SQL :
- définition de variables
- traitements conditionnels
- traitements répétitifs
- traitement des curseurs
- traitement des erreurs
Matière : ORACLE PL/QL Année Académique : 2023-2024 29/11/2023 12
STRUCTURE D'UN BLOC PL/SQL
Un bloc PL/SQL est divisé en 3 sections :
DECLARE
Déclaration de variables, constantes,
exceptions, curseurs
BEGIN [nom_du_bloc]
Instructions SQL et PL/SQL
Université Aube Nouvelle
EXCEPTION
Traitement des exceptions
(gestion des erreurs)
END [nom_du_bloc] ;
Remarques :
- les sections DECLARE et EXCEPTION sont facultatives.
- chaque instruction se termine par un ;
- Les commentaires :
-- sur une ligne
ou
/* sur plusieurs
lignes */
Matière : ORACLE PL/QL Année Académique : 2023-2024 29/11/2023 13
2. STRUCTURE D'UN BLOC PL/SQL (SUITE ...)
Exemple :
PROMPT nom du produit SQL
ACCEPT prod
DECLARE PL/SQL
qte NUMBER(5)
BEGIN
SELECT quantite INTO qte Université Aube Nouvelle
FROM stock
WHERE produit= '&prod';
-- on contrôle le stock
IF qte > 0 THEN
UPDATE stock
SET quantite = quantite - 1
WHERE produit = '&prod';
INSERT INTO vente
VALUES('&prod' || 'VENDU' , SYSDATE);
ELSE INSERT INTO commande
VALUES('&prod' || 'DEMANDE' , SYSDATE);
END IF;
COMMIT;
END;
/
Matière : ORACLE PL/QL Année Académique : 2023-2024 29/11/2023 14
3. LES VARIABLES UTILISÉES DANS PL/SQL
3.1. Les différents types de variables locales
Les variables locales se déclarent dans la partie DECLARE du bloc PL/SQL.
Différents types de variables :
Université Aube Nouvelle
* Variables de types ORACLE
* Variables de type BOOLEAN
* Variables faisant référence au dictionnaire de données
Initialisation des variables
Visibilité des variables
Matière : ORACLE PL/QL Année Académique : 2023-2024 29/11/2023 15
3.1.2. VARIABLES DE TYPE BOOLEEN
Syntaxe :
nom_var BOOLEAN;
Exemple :
DECLARE Université Aube Nouvelle
retour BOOLEAN;
BEGIN
...
END;
Matière : ORACLE PL/QL Année Académique : 2023-2024 29/11/2023 16
3.1.3. VARIABLES FAISANT RÉFÉRENCE AU DICTIONNAIRE DE DONNÉES
* Variable de même type qu'un attribut d'une table de la base
Syntaxe :
nom_var [Link]%TYPE;
Université Aube Nouvelle
Exemple :
DECLARE
nom [Link]%TYPE;
BEGIN
...
END;
Matière : ORACLE PL/QL Année Académique : 2023-2024 29/11/2023 17
3.1.3. VARIABLES FAISANT RÉFÉRENCE AU DICTIONNAIRE DE DONNÉES (SUITE ...)
- Variable de même structure qu'une ligne d'une table de la base
Syntaxe :
nom_var table%ROWTYPE;
Exemple :
DECLARE Université Aube Nouvelle
ligne pilote%ROWTYPE;
BEGIN
...
END;
Remarque :
La structure ligne contient autant de variables que de colonnes de la table. Ces variables portent le même nom et sont
de même type que les colonnes de la table.
Pour y accéder :
ligne.<nom_col1>
ligne.<nom_col2>
...
ligne.<nom_coln>
Matière : ORACLE PL/QL Année Académique : 2023-2024 29/11/2023 18
3.1.3. VARIABLES FAISANT RÉFÉRENCE AU DICTIONNAIRE DE DONNÉES (SUITE) ...)
- Variable de même type qu'une autre variable
Syntaxe :
nom_var2 nom_var1%TYPE;
Université Aube Nouvelle
Exemple :
DECLARE
ancien_sal NUMBER(5);
nouveau_sal ancien_sal%TYPE;--NUMBER(5);
BEGIN
...
END;
Matière : ORACLE PL/QL Année Académique : 2023-2024 29/11/2023 19
TYPE DE DONNÉES:
Les types SQL (number, varchar2(n), date,char(n), ..)
▪ Le type Boolean
▪ Le type variable %TYPE Université Aube Nouvelle
▪ Le type rangée %ROWTYPE
▪ Les types CURSOR. (dynamique ou non)
▪ Les types RECORD
Matière : ORACLE PL/QL Année Académique : 2023-2024 29/11/2023 20
EXEMPLE: TYPE RECORD
SET SERVEROUTPUT ON;
DECLARE
TYPE ENRE IS RECORD
(
Nom1 [Link]%TYPE, Université Aube Nouvelle
prenom1 VARCHAR2(20)
);
numad1 NUMBER:=20;
ENR ENRE;
BEGIN
SELECT nom, prenom INTO ENR.nom1,ENR.prenom1
FROM etudiants
WHERE numad = numad1;
DBMS_OUTPUT.PUT_LINE('le nom est '|| ENR.nom1 || 'le prenom est '
||ENR.prenom1);
Matière : ORACLE PL/QL Année Académique : 2023-2024 29/11/2023 21
END;
3.1.4. INITIALISATION DES VARIABLES
Avec :
opérateur :=
ou
SELECT ... INTO ...
Exemple :
Université Aube Nouvelle
DECLARE
var1 CHAR(10) := 'DUPONT';
var2 NUMBER(5,2) := 100;
var3 CHAR(10);
var4 DATE;
BEGIN
SELECT col1, col2
INTO var3, var4
FROM ... ;
...
END;
Remarque :
le SELECT doit ramener une et une seule ligne, sinon erreur.
Matière : ORACLE PL/QL Année Académique : 2023-2024 29/11/2023 22
3.1.5. VISIBILITÉ DES VARIABLES
Une variable est visible dans le bloc où elle a été déclarée et dans les blocs imbriqués si elle
n'a pas été redéfinie.
DECLARE
var1 NUMBER(3);
var2 CHAR(10);
BEGIN
... var1 NUMBER(3)
... var2 CHAR(10)
DECLARE Université Aube Nouvelle
var1 CHAR(10);
var3 DATE;
BEGIN
... var1 CHAR(10)
... var2
... var3
END;
...
DECLARE
var4 NUMBER(5,2);
BEGIN
... var1 NUMBER(3)
... var2
... var4
END;
... var1 NUMBER(3)
... var2 CHAR(10)
Matière : ORACLE PL/QL Année Académique : 2023-2024 29/11/2023 23
END;
3.2. VARIABLES DE L'ENVIRONNEMENT EXTÉRIEUR À PL/SQL
Outre les variables locales vues précédemment, un bloc PL/SQL peut utiliser d'autres variables :
- les champs d'écrans FORMS,
- les variables définies en langage hôte (préfixée de :)
Université Aube Nouvelle
- les variables définies dans SQL*Plus (préfixée de &)
Matière : ORACLE PL/QL Année Académique : 2023-2024 29/11/2023 24
4. LES TRAITEMENTS
4.1. IF : TRAITEMENT CONDITIONNEL
Exécution d'un traitement en fonction d'une condition.
IF condition1 THEN traitement 1;
Université Aube Nouvelle
ELSIF condition2 THEN traitement 2;
[ELSE traitement 3;]
END IF;
Les opérateurs utilisés dans les conditions sont les mêmes que dans SQL :
=, <, ... IS NULL, LIKE, ...
Dès que l'une des conditions est vraie, le traitement qui suit le THEN est exécuté.
Si aucune condition n'est vraie, c'est le traitement qui suit le ELSE qui est exécuté.
Matière : ORACLE PL/QL Année Académique : 2023-2024 29/11/2023 25
Exemple 1
Declare choix number;
begin
IF choix =1 THEN Université Aube Nouvelle
delete from commander
where numarticle = 100 ;
ELSIF choix =2 THEN
delete from commander
where numarticle = 110;
ELSE
delete from commander
where numarticle = 130;
END IF;
END;
Matière : ORACLE PL/QL Année Académique : 2023-2024 29/11/2023 26
Exemple 2
Exercice: écrire un bloc PL/SQL qui permet de
déclarer deux variable de type number (vente et
bonus) et qui met à jour le salaire de l’employé Université Aube Nouvelle
comme suit: (salaire = salaire+bonus)
– Si vente est > 1000 alors bonus = vente*50%
– Sinon bonus = vente *20%
Matière : ORACLE PL/QL Année Académique : 2023-2024 29/11/2023 27
Réponse
CREATE OR REPLACE PROCEDURE Augmentation
(vente in number) AS
Bonus number;
Université Aube Nouvelle
BEGIN
IF VENTE > 1000 THEN BONUS := VENTE*0.5;
ELSE
BONUS := VENTE*0.2;
END IF;
UPDATE EMPLOYES
SET SALAIRE = SALAIRE+BONUS;
Commit;
END;
Matière : ORACLE PL/QL Année Académique : 2023-2024 29/11/2023 28
CASE --- WHEN
L’instruction CASE: permet d’exécuter un bloc PL/SQL selon la valeur d’une
variable
Exemple:
CREATE OR REPLACE PROCEDURE CASE1(CHOIX IN NUMBER) AS
Université Aube Nouvelle
BEGIN
CASE CHOIX
WHEN 1 THEN
INSERT INTO employes (numemp, salaire )
VALUES (44,28000);
WHEN 2 THEN
UPDATE employes
SET salaire = salaire +10 where
numemp =10;
ELSE
dbms_output.put_line('pas bon choix');
END CASE;
END;
Matière : ORACLE PL/QL Année Académique : 2023-2024 29/11/2023 29
4.2. BOUCLE DE BASE LOOP : TRAITEMENT RÉPÉTITIF
Exécution d'un traitement plusieurs fois, le nombre n'étant pas connu mais dépendant d'une
condition.
BEGIN
LOOP [label]
instructions;
END LOOP [label];
END;
Université Aube Nouvelle
Pour sortir de la boucle, utiliser la clause :
EXIT [lable] WHEN condition
Exemple : insérer les 10 premiers chiffres dans la table result
DECLARE
nb NUMBER := 1;
BEGIN
LOOP
INSERT INTO result
VALUES (nb);
nb := nb + 1;
EXIT WHEN nb > 10;
END LOOP;
END ;
Matière : ORACLE PL/QL Année Académique : 2023-2024 29/11/2023 30
4.2. BOUCLE DE BASE LOOP : TRAITEMENT RÉPÉTITIF
Exécution d'un traitement plusieurs fois, le nombre n'étant pas connu mais dépendant d'une
condition.
BEGIN
LOOP [label]
instructions;
END LOOP [label];
END;
Université Aube Nouvelle
Pour sortir de la boucle, utiliser la clause :
EXIT [lable] WHEN condition
Exemple : insérer les 10 premiers chiffres dans la table result
DECLARE
nb NUMBER := 1;
BEGIN
LOOP
INSERT INTO result
VALUES (nb);
nb := nb + 1;
EXIT WHEN nb > 10;
END LOOP;
END ;
Matière : ORACLE PL/QL Année Académique : 2023-2024 29/11/2023 31
4.2. BOUCLE DE BASE LOOP : TRAITEMENT RÉPÉTITIF
CREATE OR REPLACE FUNCTION COMPTER RETURN
NUMBER AS
compteur number:=0;
Université Aube Nouvelle
BEGIN
LOOP
compteur:=compteur+1;
EXIT WHEN compteur=10;
END LOOP ;
RETURN compteur;
END;
Matière : ORACLE PL/QL Année Académique : 2023-2024 29/11/2023 32
4.3. BOUCLE FOR : TRAITEMENT RÉPÉTITIF
Exécution d'un traitement un certain nombre de fois. Le nombre étant connu.
BEGIN
FOR indice IN [REVERSE] exp1 ... exp2
LOOP
instructions;
END LOOP; Université Aube Nouvelle
END;
Remarques :
- inutile de déclarer indice
- indice varie de exp1 à exp2 de 1 en 1
- si REVERSE est précisé, indice varie de exp2 à exp1 avec un pas de -1.
Exemple : calcul de la factorielle 5
DECLARE
fact NUMBER := 1;
BEGIN
FOR i IN 1 .. 5
LOOP
fact := fact * i ;
END LOOP;
END;
Matière : ORACLE PL/QL Année Académique : 2023-2024 29/11/2023 33
4.3. Boucle FOR : traitement répétitif
set serveroutput on;
BEGIN
FOR i IN 1..3 LOOP
DBMS_OUTPUT.PUT_LINE (TO_CHAR(i)); Université Aube Nouvelle
END LOOP;
END;
Matière : ORACLE PL/QL Année Académique : 2023-2024 29/11/2023 34
4.3. Boucle FOR : traitement répétitif : Décrémentation
Exemple
set serveroutput on; Université Aube Nouvelle
BEGIN
FOR i IN REVERSE 1..3 LOOP
DBMS_OUTPUT.PUT_LINE (TO_CHAR(i));
END LOOP;
END;
Matière : ORACLE PL/QL Année Académique : 2023-2024 29/11/2023 35
4.4. Boucle WHILE : traitement répétitif
Exécution d'un traitement tant qu'une condition reste vraie.
BEGIN
WHILE condition
LOOP
instructions;
Université Aube Nouvelle
END LOOP;
END;
Exemple : reste de la division de 5432 par 5
DECLARE
reste NUMBER := 5432;
BEGIN
WHILE reste >= 5
LOOP
reste := reste -5;
END LOOP;
END;
Matière : ORACLE PL/QL Année Académique : 2023-2024 29/11/2023 36
4.4. Boucle WHILE : traitement répétitif
Exécution d'un traitement tant qu'une condition reste vraie.
BEGIN
WHILE condition
LOOP
Université Aube Nouvelle
instructions;
END LOOP;
END;
Exemple : reste de la division de 5432 par 5
DECLARE
reste NUMBER := 5432;
BEGIN
WHILE reste >= 5
LOOP
reste := reste -5;
END LOOP;
END;
Matière : ORACLE PL/QL Année Académique : 2023-2024 29/11/2023 37
4.4. Boucle WHILE : traitement répétitif
set serveroutput on;
DECLARE
I NUMBER:=1;
BEGIN Université Aube Nouvelle
WHILE I < 10 LOOP
I:= I+1;
DBMS_OUTPUT.PUT_LINE (TO_CHAR(I));
END LOOP;
END;
Matière : ORACLE PL/QL Année Académique : 2023-2024 29/11/2023 38
ORACLE DEVELOPER
1-Programmer avec Oracle PL/SQL
2-Générateur d’application : Oracle Forms developer
Merci pour votre attention
Questions
Matière : ORACLE PL/QL Année Académique : 2023-2024 29/11/2023 39