Docteur : Abdelalim SADIQ

TP1

2010/2011
ORACLE
2
 • Pour créer la base, nous allons
– créer une variable d'environnement ORACLE_SID
– créer un service
– créer le fichier de paramètres
– démarrer l'instance et la base
– créer la base
– créer les tables système
– créer une TableSpace
– vérifier la création d'une table
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 3
 • utiliser une instance Oracle SID (System IDentifier).
• pour cela nous devons créer une variable d'environnement DOS contenant
ce SID
• création de cette variable est effectuée en utilisant la commande DOS SET

• Accédez d’abord au répertoire c:\oracle\product\10.1.0\Db_1\BIN

• SET ORACLE_SID=Master_sid
• Utilise la commande set pour vérifier que cette variable a bien été
créée
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 4
 • Créer un Service Windows qui gèrera notre instance

• oradim -new -sid Mastersid -startmode manual

• Vérifier le démarrage de service par tapez [Link]

• Aussi vérifier l’existence d’un fichier nommé PWDmaster_sid.ORA

dans le répertoire

C:\oracle\product\10.1.0\Db_1\database
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 5
 • L'instruction de création de la base utilisera un fichier de paramètres.
binaire, appelé SPFILE.
• Ce fichier binaire est généré à partir d'un fichier ASCII [Link] que nous
devons créer.
• L’installation oracle place un fichier dans le répertoire [Link]
c:\oracle\product\10.1.0\Db_1\admin\sample\pfile

• Créez le répertoire c:\Oracle\master avec les sous-répertoires suivants

pour placer le fichier d'initialisation, les scripts, les traces, les échos DOS:
• master_init,
• master_script,
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 6
 • Sauvegardez ce fichier (utilisez bloc note)

db_name = master_base
db_block_size = 2048
# trace (log) files
control_files = C:\ORACLE\master\control01_master_sid.ora
# add if undo in create database
undo_management = auto

• dans:
c:\Oracle\master\master_init\init_master_sid.ora
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 7
 • L'instance va être démarrée en utilisant SqlPlus
• Lancer SqlPlus avec l'option /nolog (nolog= NO LOGin= lancement sans fournir
User/password)
• une fois sous SqlPlus
– nous nous connectons en tant que SYS/mon_mot_de_passe AS SYSDBA
– nous exécutons CREATE DATABASE avec les paramètres de création
– nous créons les fichiers système, puis éventuellement nos tables
• Nous utilisons un script pour démarrée l’instance
– nous tapons les commandes à exécuter dans un fichier ASCII construit avec l'éditeur de
votre choix (Notepad ou autre fonctionne aussi) et sauvegardons le texte dans un fichier
ayant n'importe quel nom (par exemple the_script.txt)
• dans SqlPlus nous invoquons le script en tapant:
• @? \le_script.txt
• Sous SqlPlus
Docteur : Abdelalim SADIQ

– @ est une abréviation de START (= lancement de script)

– ? est une abréviation de "OracleHome" (c:\oracle\product\10.1.0\Db_1\ dans notre cas)

2010/2011 ORACLE 8
 • Tapez et sauvegardez sous : c:\Oracle\master\mql_script\create_database.txt

CREATE DATABASE master_base

CHARACTER SET WE8ISO8859P1
LogFile
group 1 'c:\oracle\master\[Link]' size 10M reuse,
group 2 'c:\oracle\ master \[Link]' Size 10M reuse
DataFile 'c:\oracle\ master \[Link]' Size 50M
autoextend on
next 10M maxsize unlimited
extent management local
sysaux datafile 'c:\oracle\ mmaster \[Link]' Size 10M
autoextend on
next 10M
maxsize unlimited
undo tablespace master_undo
datafile 'c:\oracle\ master \master_undo.dbf'
Size 10M
Docteur : Abdelalim SADIQ

default temporary tablespace master_temp

tempfile 'c:\oracle\ master\master_temp.dbf'
Size 10M
;
2010/2011 ORACLE 9
 • En mode DOS relancez sqlplus :
sqlplus /nolog
• connectez-vous en tant que SYS
connect sys/ mot_de_pass as sysdba
• démarrez l'instance en tapant:
Startup Nomount pFile= c:\oracle\master\master_init\init_master_sid.ora
• lancez le script de création en tapant
@ c:\oracle\master\master_script\create_database.txt
• Et le dossier contient les fichiers suivants:
MQL_TEMP.DBF, [Link], [Link], [Link], [Link] et
CONTROL01_MQL_SID.ORA
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 10
 • La base créée est vide, et il faut exécuter des scripts qui installent des tables et
programmes systèmes dans la base:
[Link] crée le dictionnaire de données
[Link] créé les structures pour PL/SQL.
• Tapez les ligne suivant (SqlPlus lance ces deux scripts (20 minutes))

connect sys/ mon_pass as sysdba

Startup pFile= c:\oracle\master\master_init\init_master_sid.ora
@? \rdbms\admin\[Link]
@? \rdbms\admin\[Link]
Shutdown
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 11
 • Créons à présent un fichier pour nos données (TableSpace):

connect sys/ mon_pass as sysdba

Startup pFile= c:\Oracle\master\master_init\init_master_sid.ora
grant connect to system identified by le_manager;
connect system/ le_manager
create tablespace master_tables
datafile ‘ c:\Oracle\master\master_tables.dbf '
size 10m extent management local uniform size 128k;
-- si nous souhaitons bloquer Sql Plus
-- @?\sqlplus\admin\pupbld
Docteur : Abdelalim SADIQ

• SqlPlus crée le fichier de données MQL_TABLES.DBF

2010/2011 ORACLE 12
 • A titre de vérification, nous utilisons le script demobld qu'Oracle a fourni
pour créer une table DEPT et EMP
• Tapez:

connect sys/ mon_pass as sysdba

Startup pFile= c:\Oracle\master\master_init\init_master_sid.ora
grant connect, resource to mon_user
identified by user_pass;
alter user mon_user
default tablespace master_tables;
connect mon_user/user_pass
@? \[Link]\samples\DataSet\RelationalData\Setup\demobld
select * from dept;
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 13
 • STARTUP [paramètres];
– NOMOUNT Créé la SGA et démarre les processus en arrière plan mais ne permet pas
l'accès à la base (reste un OPEN à faire).
– MOUNT Monte la base pour certaines activités DBA mais ne permet aucun accès à la
base.
– OPEN Permet aux utilisateurs d'accéder à la base.
– EXCLUSIVE Autorise l'instance courant seulement à accéder à la base.
– PFILE Spécifie le fichier d'initialisation à prendre en compte.
– FORCE Annule l'instance courante avant d'effectuer un démarrage normal.
– RESTRICT Autorise seulement l'accès aux utilisateurs avec le privilège RESTRICTED
SESSION.
– PARALLEL Serveur parallèle Oracle.
– SHARED Autre terme pour parallèle.
– RECOVER Démarre la restauration media quand la base démarre
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 14
 • SHUTDOWN [paramètre];
– NORMAL Les nouvelles connexions ne sont pas permises, le serveur oracle
attend la fin de toutes les connexion.

– IMMEDIATE Les utilisateurs sont déconnectés, les opérations en cours

annulées (rollback).

– ABORT L'instance se termine sans fermer les fichiers, une restauration

d'instance est souvent nécessaire (recover) au prochain démarrage.

– TRANSACTIONNAL Plus de connexion possible, les ordres SQL en cours

s'exécute jusqu'à leur terme et aucun nouveau n'est accepté.
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 15
 • BACK_GROUND_DUMP_DEST Emplacement où les fichiers traces des processus en arrière plan sont
enregistrés.
• USER_DUMP_DEST Emplacement où les fichiers traces sont créés.
• COMPATIBLE Version du serveur avec lequel l'instance est compatible.
• CONTROL_FILES Noms des fichiers de contrôle.
• DB_BLOCK_BUFFERS Nombre de blocs mis en cache dans la SGA. La valeur par défaut et minimum est de
50 buffers.
• DB_NAME Identifiant de la base de données de 5 caractères ou moins. (seul paramètre nécessaire à la
création d'une base).
• SHARED_POOL_SIZE Taille en octets de la zone de partage. (Default : 3 500 000).
• IFILE Permet de référencer un autre fichier de paramètre à imbriquer dans la définition.
• LOG_BUFFER Nombre d'octets alloués au buffer redolog dans la SGA.
• MAX_DUMP_FILE_SIZE Taille maximum des fichiers trace, spécifiée en nombre de blocs de l'OS.
• PROCESSES Nombre de processus de l'OS pouvant se connecter simultanément à cette instance.
• SQL_TRACE Active on non l'outil de suivi SQL pour chaque session utilisateur (cf TKPROF pour exploiter les
sorties).
Docteur : Abdelalim SADIQ

• TIMED_STATISTICS Active ou non le minutage dans les fichiers trace et sur les écrans.

2010/2011 ORACLE 16
 • Pour créer la base, nous allons
– créer une variable d'environnement ORACLE_SID
– créer un service
– créer le fichier de paramètres
– démarrer l'instance et la base
– créer la base
– créer les tables système
– créer une TableSpace
– vérifier la création d'une table
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 17
 Docteur : Abdelalim SADIQ

TP2

2010/2011
ORACLE
18
 • SQL
– LDD (Langage de description des données) : création, modification et
suppression des définitions des tables

– LMD (Langage de manipulation des données) :ajout, suppression,

modification et interrogation des données

– LCD (Langage de contrôle des données): gestion des protection d’accès

• Fin d’instruction : ;

• Commentaires : /*…..*/ ou --…….

Docteur : Abdelalim SADIQ

2010/2011 ORACLE 19
 • CREATE TABLE (
Attribut1 TYPE_Att1,
Attribut2 TYPE_Att2,
…………………………….,

CONSTRAINT PK_nomTable
PRIMARY KEY (Attribut1, Attribut2,…)

CONSTRAINT FK_nomTable_attributi
FOREIGN KEY (Attributi)
REFERENCES nomTablei(Attributi),

CONSTRAINT CK_nomTable_Attributj
CHECK (condition_sur_attributj)
Docteur : Abdelalim SADIQ

);

2010/2011 ORACLE 20
 • Number(n) : Entier à n (<=38) chiffres,

• Number(n,m) : Réel à n chiffres au total, m après la virgule

• Varchar2(m): Chaîne de n(<2000) caractères (entre ‘ …’)

• Date : Date au format ‘JJ-MM-AAAA’

• Autres types : DECIMAL, FLOAT, CHAR, VARCHAR, LONG ( Texte : jusqu’à

2Go de données, BLOB, RAW, LONG ROW
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 21
 • Clé primaire :
CONSTRAINT PK_nomTable
PRIMARY KEY (Attribut1, Attribut2,…)

• Clé étrangère :
CONSTRAINT FK_nomTable_attributi
FOREIGN KEY (Attributi)
REFERENCES nomTablei(Attributi),

• Contrainte de domaine :
CONSTRAINT CK_nomTable_Attributj
CHECK (condition_sur_attributj)
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 22
 • Ecrire le script des tables de la base en respectant les conditions suivants:
– Prix unitaire ne doit pas dépasser 30000DH,

– Date de commande doit être égale à la date du système

– La quantité commandée doit être supérieure à zéro.

Docteur : Abdelalim SADIQ

2010/2011 ORACLE 23
 • CLIENT (NumCli(4), Nom(15), Prénom(15), DateNaiss, Rue(20), CP(6), Ville(20))

• PRODUIT (NumProd(4), Desig(20), PU(6.2), #NumFour(4))

• FOURNISSEUR (NumFour(4), RaisonSoc(20))

• COMMANDE (#NumCli, #NumProd, DateC, Quantité(4))

Docteur : Abdelalim SADIQ

2010/2011 ORACLE 24
 CREATE TABLE CLIENT (
NumCli NUMBER(4),
Nom VARCHAR2(30),
PRENOM VARCHAR2(30),
DN DATE,
Rue VARCHAR2(100),
CP NUMBER(4),
Ville VARCHAR2(40),
CONSTRAINT PK_CLIENT PRIMARY KEY (NumCli)
);
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 25
 CREATE TABLE Fournisseur(

NumFour NUMBER(4),

RaisonSoc VARCHAR2(30),

CONSTRAINT PK_FOURNISSEUR PRIMARY KEY (NumFour)

);
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 26
 CREATE TABLE PRODUIT (

NumProd NUMBER(4),

Desig VARCHAR2(30),

PU NUMBER(8,3),

NumFour NUMBER(4) Not NULL,

CONSTRAINT PK_PRODUIT PRIMARY KEY (NumProd),

CONSTRAINT FK_PRODUIT_NUMFOUR FOREIGN KEY (NumFour)

REFERENCES Fournisseur (NumFour),

Docteur : Abdelalim SADIQ

CONSTRAINT CK_PRODUIT_PU CHECK (PU<30000)

);

2010/2011 ORACLE 27
 CREATE TABLE COMMANDE (
NumCli NUMBER(4),
NumProd NUMBER(4),
DateCDATE DEFAULT SYSDATE,
Qte NUMBER(4) NOT NULL,
CONSTRAINT PK_COMMANDE PRIMARY KEY (NumCli, NumProd, DateC),
CONSTRAINT FK_COMMANDE_NUMCLI FOREIGN KEY (NumCli)
REFERENCES Client(NumCli),
CONSTRAINT FK_COMMANDE_NUMPROD FOREIGN KEY (NumProd)
REFERENCES Produit(NumProd),
CONSTRAINT CK_COMMANDE_QTE CHECK (QTE>0)
Docteur : Abdelalim SADIQ

);

2010/2011 ORACLE 28
 • Création d’index (accélération des accès)
– Syntaxe:
CREATE [UNIQYE] INDEX InomTable_attributs
ON nomTable (attribut1 *ASC|DESC+,…);
– Notes:
• UNIQUE=pas double
• ASC/DESC=ordre croissante ou décroissante
– Exemple :
CRETAE UNIQUE INDEX Iclient_NumCli ON Client (NumCli);
– Exercice: Créer un index sur l’attribut Nom de la table client.
CREATE INDEX Iclient_Nom ON Client (Nom);
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 29
 • Destruction de tables:
– DROP TABLES nomTables;
• Question : est-ce que la commande « DROP TABLE
Fournisseur; » peut détruire la table Fournisseur et pourquoi?
• Réponse: Non parce que la table Produit dépend
fonctionnellement de la table Fournisseur
• Solution :
– Supprimer la table produit avant de supprimer la table fournisseur
– Supprimer la contrainte d’intégrité référentielle FK_PRODUIT_NUMFOUR de la
table Produit.
• Destruction d’un index:
Docteur : Abdelalim SADIQ

– DROP INDEX nom_index;

2010/2011 ORACLE 30
 • Supprimer l’index créé sur l’attribut nom de la table client,
– DROP INDEX Iclient_Nom;

• Supprimer toutes les tables de la base de données exemple.

– DROP TABLE Commande;

– DROP TABLE Produit;

– DROP TABLE Fournisseur;

– DROP TABLE Client;

Docteur : Abdelalim SADIQ

2010/2011 ORACLE 31
 • Afficher la définition d’une table
• DESCRIBE (ou DESC )nomTable;
• Ajout d’attributs dans une table
• ALTER TABLE nom_table ADD (attribut1 TYPE, …)
• Suppression d’attribut d’une table :
• ALTER TABLE nom_table DROP COLUMN nom_attribut;
• Modification d’attributs
• ALTER TABLE nom_table MODIFY(attribut TYPE);
• Suppression de contraintes
• ALTER TABLE nom_table DROP CONSTRAINT nom_constraint;
• Ajout de contraintes:
Docteur : Abdelalim SADIQ

• ALTER TABLE nom_table ADD CONSTRAINT nom_constraint…;

2010/2011 ORACLE 32
 • Supprimer la contrainte sur la quantité dans la table
commande

• Ajouter la contraint suivante : quantité >=1 dans la table

commande

• Ajouter l’attribut « Adr » dans la table client,

• Supprimer l’attribut « Rue » de la table client,

• Modifier le type de l’attribut « Adr » VARCHAR2(100)

Docteur : Abdelalim SADIQ

2010/2011 ORACLE 33
 • Ajout d’un tuple

• INSERT INTO nom_table(att1, att2, …)

• VALUES (val_att1, val_att2, …)

• Mise à jour d’un attribut

• UPDATE nom_table SET attribut= Valeut

• [WHERE condition];

• Suppression de tuples

• DELETE FROM nom_table [WHERE condition];

Docteur : Abdelalim SADIQ

2010/2011 ORACLE 34
 CLIENT
NC Nom Prénom DN RUE CP Ville
1 Salmi Sami 12-02-1944 Rue N 17 100023 Rabat
2 IMALLA KAMEL 14-12-1974 100024 Rabat
3 SKFALLI ALI Ryue N 28 100025 Rabat
PRODUIT COMMANDE
NP DESIG PU NF NC NP DATEC QTE
10 PRO10 1100 1 1 10 12-02-2009 2
20 PRO20 2045,225 3 3 20 16-02-2009 4
Fournisseur 3 10 20-02-2009 6
Nf Raison soc 1 20 26-02-2009 3
Docteur : Abdelalim SADIQ

1 SOS SALMI
2 SOS INCONNU
3 SOS MED ALI
2010/2011 ORACLE 35
 • Syntaxe :
SELECT [ALL|DISTINCT] attribut(s)
FROM table(s)
[WHERE condition(s)]
[GROUP BY attribut(s)[HAVING condition]]
[ORDER BY attribut(s)[ASC|DESC]];
• Tous les tuples
SELSECT * FROM nom_table;
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 36
 • EX1 : La liste de tous les clients par ordre alphabétique inverse de nom.

SELECT * FROM client

ORDER BY nom DESC;

• EX2 : Calcul de prix TTC de tous les produits.

SELECT PU+PU*1.8 FROM produit;

Ou SELECT PU+PU*1.8 AS Prix_TTC FROM produit

• EX3: Noms et Prénoms de tous les clients (projection).

SELECT Nom, Prenom FROM Client,

Docteur : Abdelalim SADIQ

2010/2011 ORACLE 37
 • EX4 :liste des Clients qui habitent à Rabat,
SELECT * FROM client
WHERE ville=‘RABAT’;
• Pb : Lors de l’insertion dans la table client, on ne sait pas est-ce que la ville
est écrite en majuscule, en minuscule ou les deux à la fois
• Solution :
LOWER(ch) : conversion de ch en minuscule
UPPER(ch) : conversion de ch en majuscules

SELECT * FROM client

WHERE UPPER (ville) = ‘RABAT’;
Ou
Docteur : Abdelalim SADIQ

SELECT * FROM client

WHERE LOWER(ville)= ‘rabat’;
2010/2011 ORACLE 38
 • Arithmétique: + - * /
• Sur les dates : + -
• Sur les chaînes de caractères : || (concaténation)
• De comparaison (nombres, chaînes ou dates) : = !=, <>, <, >, <=, >=
• Booléens : AND OR NOT
• X BETWEEN a AND b
Expression vraie si x est compris entre a et b, bornes incluses
• IS NULL
Expression vraie si la valeur de la colonne n’est pas renseignée
• IS NOT NULL
Expression vraie si la valeur de la colonne est renseignée
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 39
 • EX 5: Commandes en quantité au moins égale à 3
SELECT * FROM Commande
WHERE Qte>=3;
• EX 6 : Produit dont le prix unitaire est compris entre 2000 ,00 DH et
2500,00 DH
SELECT * FROM produit
WHERE PU BETWEEN 2000 AND 2500;
• EX7 : Commandes en quantité indéterminée
SELECT * FROM Commande
WHERE Qte IS NULL;
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 40
 • Syntaxe
– x LIKE y [ESCAPE CAR]
Expression qui retourne la valeur vraie si les deux chaîne sont égales en tenant
compte des caractères jokers de la seconde chaîne
• Les caractères jokers :
– %: ce caractère remplace n’importe quelle chaîne de caractères, même la
chaîne vide
– _: ce caractère remplace exactement un caractère.
• ESCAPE CAR : Déclare un caractère qui permet de banaliser
(désactives) l’effet de caractères jokers
• Exemple : chercher les produits qui ont comme désignation
Docteur : Abdelalim SADIQ

commençant par « «PR_%INV »

SELCET * FROM Produit
WHERE UPPER(desig) LIKE ‘PR@_@%INV%’ ESCAPE ‘@’;

2010/2011 ORACLE 41
 • EX 8 : Clients habitant une ville dont le nom se termine par AT

SELECT * FROM client

WHERE UPPER (ville) LIKE ‘%AT’

• EX9 : Prénoms des client dont le nom est SAMI, SALAH ou ALI
SELECT prenom FROM client

WHERE UPPER (Nom) IN (‘SAMI’, ’SALAH’, ’ALI’);

Docteur : Abdelalim SADIQ

2010/2011 ORACLE 42
 • Elles opèrent sur un ensemble de valeurs

• AVG(), VARIANCE(), STDDEV(): moyenne, variance et écart-type des

valeurs

• SUM(): somme des valeurs

• MIN(), MAX() : valeur minimum, valeur maximum

• COUNT() : nombre de valeurs

• EX 10 : Moyenne des prix unitaire des produits

SELECT AVG(PU) FROM Produit;

Docteur : Abdelalim SADIQ

2010/2011 ORACLE 43
 • EX 11 : Nombre total de commandes

SELECT COUNT (*) FROM Commande.

SELECT COUNT (NumCli) FROM Commande.

• EX 12 : Nombre de clients ayant passé commande

SELECT COUNT ( DISTINCT NumCli)

FROM Commandes;
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 44
 • EX13: Liste des noms et prénoms des clients qui ont lancé des
commandes.
SELECT Nom, Prenom
FROM Client, Commande
WHERE
[Link] = [Link];
• NB : Utilisation des alias pour alléger l’écriture,
SELECT Nom, Prenom
FROM Client cli, Commande cmd
WHARE
[Link] = [Link];
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 45
 • EX14: Liste des numéros, noms et prénoms des clients qui ont lancé des
commandes
SELECT [Link], Nom, Prenom
From Client cli, commande cmd
WHERE
[Link]=[Link];
• EX 15 : Liste des numéros, noms et prénoms des clients qui ont
commandé le 23/12/2008;
SELECT [Link], Nom, Prenom
FROM Client cli, Commande cmd
WHERE
Docteur : Abdelalim SADIQ

[Link] = [Link] AND

DateC = ’23-12-2008’;

2010/2011 ORACLE 46
 • Sous- requête : C’est une requête incluse dans une expression SQL.

1. La sous-requête ramène une seule valeur. Dans ce cas, on peut utiliser

les Opérateurs de comparaisons.

WHERE exp1 op (SELECT exp2…) où op est un opérateur de comparaison

(=,…). Exp1 et exp2 doivent être cohérents.

• Exp :
SELECT * FROM Commande
WHERE
Qte=( SELECT MAX(Qte) FROM Commande
Docteur : Abdelalim SADIQ

WHERE DateC >= ’15-02-2009’

2010/2011 ORACLE 47
 2. La sous-requête peut ramener plusieurs valeurs. Elle est utilisée dans :
• Opération d’inclusion:
Where exp1[NOT] IN
SELECT(exp2…)
• Exp:
SELECT * FROM Client
WHERE
NumCli NOT IN (
SELECT NumCli FROM Commande
WHERE
DateC >= ’15-02-2009’
Docteur : Abdelalim SADIQ

);

2010/2011 ORACLE 48
 • Opérateurs de comparaison avec (ALL et ANY):

WHERE exp1 op_de_comparaison {ALL|ANY}

(SELECT exp2…)
• Avec:
• ALL : La condition est vraie si la comparaison est vraie pour chacune des valeurs
retournées.
• ANY : La condition est vraie si la comparaison est vraie pour au moins une des valeurs
retournées.
• Exp:
SELECT * FROM Commande WHERE
Qte > ALL (SELECT Qte From Commande
WHERE DateC >= ’15-02-2009’);
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 49
 3. La sous-requête peut retourner une ou plusieurs occurrences d’une liste
d’élément.
WHERE (exp 11, exp21,…) *NOT+ IN
(SELECT exp12, exp22,….)
• Note : La liste des éléments retournés par la sous-requête doit être du même
type que (exp1, exp2,…)
• Exp :
SELECT cli1.* FROM Client cli1, Commande cmd1
WHERE
[Link] = [Link] AND
([Link],[Link]) IN (SELECT [Link], [Link] From Client
Cli, Produit Prod, Commande Cmd WHERE
Docteur : Abdelalim SADIQ

[Link]=[Link] AND
[Link] = [Link] AND
NumFour = 1);

2010/2011 ORACLE 50
 4. Sous-requête synchronisée avec la requête principale : La sous-requête
utilise une expression contenant au moins une colonne d’une table T de la
requête principale. Il est alors nécessaire de définir un alias de la table T.

WHERE *NOT+ EXISTS (SELECT…)

EXISTS : Renvoie un booléen Vrai ou Faux selon le résultat de la requête
– Vrai si le résultat produit au moins une ligne
– Faux sinon
• EXP:
SELECT * FROM Client cli
WHERE
EXISTS ( SELECT* FROM Commande cmd
Docteur : Abdelalim SADIQ

WHERE
[Link] = [Link]);

2010/2011 ORACLE 51
 5. Sous-Requête d’insertion
INSERT INTO nomTable *(att11,att21,…)+
SELECT att12, att22,…
• Exp:
-- Création d’une table nombre de commande par client
CREATE TABLE NbrCmdCli(
NumCli NUMBER(4),
NbrCmd NUMBER(3));
-- Puis insertion de données.
INSERTION INTO NbrCmdCli (NumCli)
(SELECT NumCli FROM Client);
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 52
 6. Sous-Requête de Mises A Jour
UPDATE table1 alias1
SET colonne =(
SELECT …
FROM table2 alias2
WHERE
[Link]= [Link] …)
[WHERE ….+;
• Exp:
--Mettre à jour le champ NbrCmd dans la table NbrCmdCli
UPDATE NbrCmdCli NCC SET NbrCmd = (
SELECT count(*)
Docteur : Abdelalim SADIQ

FROM Commande cmd

WHERE [Link] = [Link]);

2010/2011 ORACLE 53
 7. Sous-Requête de création d’une table:
CREATE TABLE nomTable *(att1, att2,…)+ AS
SELECT att11, att21,…..;
• EXP1 : Création d’une copie de la table commande
CREATE TABLE CopyCmd AS
(SELECT * FROM Commande WHERE 1 = 2 );
==> La Table CopyCmd a la même structure que la table Commande, mais
elle n’a pas les mêmes contraintes (Clé primaire, étrangère, Check, Not
Null, ….)
• EXP2: Création d’une table pleine
CREATE TABLE ClientCommande (NC,NOM, PRENOM) AS
Docteur : Abdelalim SADIQ

(SELECT [Link], Nom, Prenom

From Commande cmd, Client cli
WHERE [Link] = [Link]);
2010/2011 ORACLE 54
 • Ex16 : Liste des numéros, noms et prénoms des clients qui ont commandé
le 20/02/2009;

SELECT NumCli, Nom, Prenom

FROM Client
WHERE
NumCli IN (SELECT NumCli
FROM Commande
WHERE
DateC =’20-02-2009’)
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 55
 • EX 17: Liste des clients qui ont passé au moins une commande
SELECT * FROM Client cli
WHERE
EXISTS (SELECT * FROM Commande cmd
WHERE
[Link] = [Link]);

• EX18 : Liste des clients qui n’ont passé aucune commande

SELECT * FROM Client cli
WHERE
NOT EXISTS (SELECT * FROM Commande cmd
Docteur : Abdelalim SADIQ

WHERE
[Link] = [Link]);

2010/2011 ORACLE 56
 • EX 19:
Numéros des client qui ont commandé au moins un produit en quantité
supérieure à chacune des quantités commandées par le client n°1.

SELECT DISTINCT NumCli

FROM Commande
WHERE Qte > ALL (
SELECT Qte
FROM Commande
WHERE NumCli = 1);
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 57
 • EX 20:
Numéros des clients qui ont commandé au moins un produit en quantité
supérieurs à au moins une des quantités commandées par le client n°1.

SELECT DISTINCT NumCli

FROM Commande
WHERE Qte > ANY (
SELECT Qte
FROM Commande
WHERE NumCli = 1);
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 58
 • EX21:
Quantité totale commandée par chaque client.

SELECT NumCli, SUM(Qte)

FROM Commande
GROUP BY NumCli;

• Remarque : La fonction GROUP BY permet de rassembler les données

sous forme de paquets
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 59
 • EX22:
Nombre de produits différents commandés par chaque client
SELECT NumCli, COUNT (DISTINCT NumProd)
FROM Commande
GROUP BY NumCli;
• EX23:
Nombre de produits différents commandés par chaque client au moins de
février 2009
SELECT NumCli, COUNT (DISTINCT NumProd)
FROM Commande
WHERE DATEC >= ’01-02-2009’ AND
Docteur : Abdelalim SADIQ

DATEC <’01-03-2009’
GROUP BY NumCli;

2010/2011 ORACLE 60
 • La clause HAVING permet de spécifier les paquets de chaque groupe à
traiter. Elle ne s’utilise qu’avec GROUP BY.
• EX 24:
• Quantité moyenne commandée pour chaque produits faisant l’objet de
plus de 3 commandes
SELECT NumProd, AVG(Qte)
FROM Commande
GROUP BY NumProd
HAVING COUNT(*)>3;
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 61
 UNION INTERSECTION DIFFERENCE
Requête SQL1 Requête SQL1 Requête SQL1
UNION INTERSECT MINUS
Requête SQL2 Requête SQL2 Requête SQL2

• Les deux requête SQL1 et SQL2 doivent sélectionner les mêmes attributs
(types, nombre). C.à.D:
– Si Requête SQL1 = select a1, a2 from ….et

Requête SQL2 = select b1, b2 from…

– ALORS a1 et b1 doivent avoir les même type, aussi pour a2 et b2.

Docteur : Abdelalim SADIQ

• Pas de virgule après la requête SQL1 et après les opérations (UNION,

INTERSECT, MINUS).

2010/2011 ORACLE 62
 • EX 25:
• Numéro des produits qui soit, ont un prix inférieur à 2000 DH, soit ont été
commandés par client N°2.
SELECT NumProd FROM Produit WHERE PU<2000
UNION
SELECT NumProd FROM Commande WHERE NumCli=2;
• EX 26:
• Numéro des client qui n’ont pas passé des commandes.
SELECT NumCli FROM Client
MINUS
SELECT distinct NumCli FROM Commande;
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 63
 • LENGTH(ch) : Longueur de la chaîne ch
– Exp : LONGTH (‘ALI’)=3;
• SUBSTR(ch,pos_départ_nbr_caractères): Extraction d’une portion de
chaîne ch
– Exp : SUBSTR (‘ALI’,1,2)=‘AL’ et SUBSTR (‘IL’,1,3)=‘IL’
• REPLACE(ch, sous_ch,[ch_rempl]): Remplacement d’une portion de ch par
une autre.
– Exp: REPLACE(‘SALAH’, ‘SA’,’AL’)=‘ALLAH’
– Exp: REPLACE(‘SALAH’, ‘SALA’)=‘H’
– Exp: REPLACE(‘SALAH’, ‘SaLA’)=‘SALAH’
• || ou CONCAT(ch1, ch2): Concaténation de deux chaînes
– Exp : ‘S’ || ‘AMI’ = ‘SAMI’
– Exp : CONCAT (‘S’, ‘AMI’)=‘SAMI’
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 64
 • LTRIM(ch, [ensemble_de_caractères]): Supprime tout apparence des
caractères, dans l’ensemble des caractères, en début de la chaîne ch.
– Exp: LTRIM(‘YXXZALIXZY’,’XZY’)=‘ALIXZY’

– Exp: LTRIM(‘ ALI ‘)=‘ALI ‘

• RTRIM (ch,[ensemble_de_caractères]) : même rôle de LTRIM mais en fin

de la chaîne ch.
– Exp: LTRIM(‘ ALI ‘)=‘ ALI‘

– Exp: LTRIM(‘XLXYZ’,’ZXY’)=‘XL’
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 65
 • LPAD(ch, n,[ch_remplissage]): Compléter, à gauche ch par ch_remplissage
pour trouver une chaîne de longueur n.
– Exp: LPAD(‘ALI’,7,’-’)=‘----ALI’

– Exp: LPAD(‘ALI’,7,’A***B’)’= A***ALI’

– Exp: LPAD(‘ALI’,7 =‘ ALI’

• RPAD(ch, n, [ch_remplissage]): Compléter, à droit, ch par ch_remplissage

pour trouver une chaîne de longueur n.

– Exp: RPAD(‘ALI’,7,’-’)=‘ALI----’

– Exp: RPAD(‘ALI’,7,’A***B’) = ’ALIA***’

Docteur : Abdelalim SADIQ

– Exp: RPAD(‘ALI’,7 =‘ ALI ’

2010/2011 ORACLE 66
 • INITCAP(ch): Convertit le premier caractère de chaque mot de ch en
majuscule, les autres caractères étant retournés en minuscules
– Exp : INITCAP (‘bEn YaZid’)= ‘Ben Yazid’

• LOWER(ch): Convertit tous les caractères d’une chaîne en minuscules.

– Exp: LOWER(‘bEn YaZid’)=‘ben yazid’

• UPPER(ch): Convertit tous les caractères d’une chaîne en majuscules

– Exp : UPPER(‘bEn YaZid’) =‘BEN YAZID’

• CHR(n): Caratctère ASCII n° « n », Avec n dans la base 10

– Exp: CHR(65) = ‘A’
Docteur : Abdelalim SADIQ

On a code ASCII (‘A’)= (41)16=(65)10

2010/2011 ORACLE 67
 • DECODE(expression, val1, val_retournée1, val2, val_retournée2,
…,*val_défaut_retournée+)
équivalant à:
Swith éval(expression){
Val1:return(val_retournée1)
Val1:return(val_retournée1)
….
Default:
Return(val_défaut_retournée)
}
• Exemple
Docteur : Abdelalim SADIQ

SELECT NumCli, NumProd, DateC, Qte,

DECODE(supp,0,’NON SUPPRIME’,1,’SUPPRIME’)
FROM Commande;
2010/2011 ORACLE 68
 • ROUND () :Arrondit la valeur à la précision spécifiée

ROUND(45.926, 2)= 45.93

• TRUNC () : Tronque la valeur à la précision spécifiée

TRUNC(45.926, 2)= 45.92

• MOD() : Ramène le reste d’une division

MOD(1600, 300)= 100
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 69
 FONCTION DESCRIPTION

Nombre de mois situés entre

MONTHS_BETWEEN deux dates
Ajoute des mois calendaires à
ADD_MONTHS une date
Jour qui suit la date spécifiée
NEXT_DAY

LAST_DAY Dernier jour du mois

ROUND Arrondit une date

Docteur : Abdelalim SADIQ

TRUNC Tronque une date

2010/2011 ORACLE 70
 • MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')
19.6774194

• ADD_MONTHS ('11-JAN-94',6) '11-JUL-94'

• NEXT_DAY ('01-SEP-95','FRIDAY') '08-SEP-95'

Docteur : Abdelalim SADIQ

• LAST_DAY('01-SEP-95') '30-SEP-95'

2010/2011 ORACLE 71
 • ROUND('25-JUL-95','MONTH') 01-AUG-95

• ROUND('25-JUL-95','YEAR') 01-JAN-96

• TRUNC('25-JUL-95','MONTH') 01-JUL-95

• TRUNC('25-JUL-95','YEAR') 01-JAN-95
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 72
 • Oracle effectue automatiquement les conversions suivantes

De Vers

VARCHAR2 ou CHAR NUMBER

VARCHAR2 ou CHAR DATE

Docteur : Abdelalim SADIQ

2010/2011 ORACLE 73
 TO_NUMBER TO_DATE

NUMBER CHARACTER DATE

TO_CHAR TO_CHAR
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 74
 TO_CHAR(date, 'fmt')

• Le modèle de format :
– Doit être placé entre simples quotes et différencie les majuscules et
minuscules.

– Peut inclure tout élément valide de format date

– Comporte un élément fm qui supprime les espaces de remplissage

ou les zéros de tête
Docteur : Abdelalim SADIQ

– Est séparé de la valeur date par une virgule

2010/2011 ORACLE 75
 YYYY Année exprimée avec 4 chiffres

YEAR Année exprimée en toutes lettres

MM Mois exprimé avec 2 chiffres

MONTH Mois exprimé en toutes lettres

3 premières lettres du nom du jour

DY
Docteur : Abdelalim SADIQ

DAY Jour exprimé en toutes lettres

2010/2011 ORACLE 76
 – Conversion d’une chaîne de caractères en format numérique
avec la fonction TO_NUMBER

TO_NUMBER(char)

– Conversion d’une chaîne de caractères en format date avec la

fonction TO_DATE

TO_DATE(char[, 'fmt'])
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 77
 • Transaction : ensemble des modifications de la base

• Début de transaction : début de la session de travail ou fin de

la transaction précédente

• Validation (et fin) d’une transaction : COMMIT;

• Annulation (et fin) d’une transaction : ROLLBACK;

• Fin de session de travail : implique une validation

automatique
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 78
 Docteur : Abdelalim SADIQ

TP3

2010/2011
ORACLE
79
 • Les vues sont des objets virtuels

• Une vue est définie par une requête

• Elle est stockée (nom et définition) dans le dictionnaire de

données de la base (user_views)

• Une vue se comporte comme une table contenant le résultat

d’une requête
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 80
 • Elle peut être utilisée comme une table pour consulter ( sous
certaines conditions modifier) les données de la base.

• Lorsqu’une requête fait appel à une vue, le SGBD substitue à

la vue sa définition et d’est cette dernière qui sera exécutée
– Vue : table virtuelle calculée à partir d’autres tables grâce à une
requête
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 81
 • Commande de création
CREATE [OR REPLACE] VIEW nom_vue (Col1, Col2,…)
AS SELECT c 1, c2,…;
--Noms de colonnes qui seront associés en nombre et ordre aux éléments
du SELECT
• Ou
CREATE [OR REPLACE] VIEW nom_vue
AS SELECT c 1, c2,…;
-- la vue reprend les noms des colonnes ou alias de la requête
• Commande de suppression
• DROP VIEW nom_vue;
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 82
 • Exemple:
1. CREATE OR REPLACE VIEW LNPClients
(nomCli,PrenomCli) AS
SELECT Nom, Prenom, FROM Client;
2. CREATE OR REPLACE VIEW NbCmdClients
AS SELECT NumCli, COUNT(*)
FROM Commande
GROUP BY (NumCli) PROBLEME
2. CREATE OR REPLACE VIEW NbCmdClients
AS SELECT NumCli, COUNT(*) AS nbCMD
FROM Commande
Docteur : Abdelalim SADIQ

GROUP BY (NumCli)

2010/2011 ORACLE 83
 • Exemple : Créer la vue ProdCom qui permet de manipuler que les Produits
commandés.

CREATE VIEW ProdCom AS

SELECT [Link], Desig, PU, DateC, Qte

FROM Produit P, Commande C

WHERE [Link]=[Link];

• Utilisation:

SELECT NumProd, Desig FROM Prod_com

Docteur : Abdelalim SADIQ

WHERE Quantite>10;

2010/2011 ORACLE 84
 • Simplification de l’accès aux données en masquant les
opérations de jointure

• Sauvegarde indirecte de requêtes complexes

• Support de l’indépendance logique

• Renforcement de la sécurité des données par masquage des

lignes et des colonnes sensibles aux usagers non habilités
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 85
 • La mise à jour de données d’une table via une vue pose des problèmes et
la plupart des systèmes impose d’importantes restrictions
– Le mot clé DISTINCT doit être absent.
– La clause FROM doit faire référence à une seule table.
– La clause SELECT doit faire référence directement aux attributs de la table concernée
(pas d’attribut dérivé. Exemple SUBSTR(nom,1,3)).
– Les clauses GROUP BY et HAVING sont interdites.
• Ex.
SQL> CREATE OR REPLACE VIEW Lfour AS
SELECT DISTINCT * FROM FOURNISSEUR;
Vue créée
SQL> INSERT INTO Lfour values (7,’xxxx’);
Docteur : Abdelalim SADIQ

ERREUR à la ligne 1 :
ORA-01732: les manipulations de données sont interdites sur cette vue

2010/2011 ORACLE 86
 • Ex1
Créer la vue CLIENT_RABAT à partir de la table CLIENT ne contenant que
les clients habitant à la ville de Rabat
Toutes les colonnes sont conservée et portent le même nom que les
colonnes de la table. Interroger la vue (DESC et SELECT).

• Ex2
Créer la vue PRODUIT_1 (NP_1, NumFour_1, PrixUnit_1, Desig_1) à partir
de la table PRODUIT ne contenant que les produits du fournisseur dont la
raison sociale est « SOS SALMI ».
– Vérifier la structure de la vue avec DESC
– Vérifier le contenu de la vue avec SELECT.
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 87
 • Ex3
Insérer avec INSERT trois nouveaux produits dans la vue PRODUIT_1
(exemple prod50, prod60 et pror70). Tels que prod50 et prod60
appartiens au fournisseur dont la raison sociale est « SOS SALMI » et
prod70 à un autre fournisseur.
– Vérifier le contenu de la vue et celui la table PRODUIT. Conclure ?
– Supprimer ensuite les enregistrements ajoutées dans la table PRODUIT (par
l’intermédiaire de la vue PRODUIT_1). Conclure ?

• EX4
Afficher la liste des vues déjà créées pat les commandes suivantes:
SELECT VIEW_NAME, TEXT_LENGTH, TEXT FROM USER_VIEWS
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 88
 • EX5
Créer la vue CLIENT_CMD (NCLI, NOMPRENOM, ADR, REFCMD, MHT,
MTVA, MTTC, DATEC) permettant d’avoir la liste des clients qui ont
commandé.
– Vérifier la structure de la vue avec DESC
– Vérifier le contenu de la vue avec SELECT.
– Afficher la liste des clients qui ont commandé au cours du mois de février 2009.
– Essayer de mettre à jour la vue CLIENT_CMD. Conclure ?
• EX6
• Créer la vue CLIENT_NBCMD (NCLI, NOM, PRENOM, ADR, NBRCMD)
permettant d’avoir le nombre de commande pour chaque client.
– Vérifier la structure de la vue avec DESC
– Vérifier le contenu de la vue avec SELECT.
Docteur : Abdelalim SADIQ

– Afficher la liste des clients qui n’ont pas de commande.

2010/2011 ORACLE 89
 Docteur : Abdelalim SADIQ

TP4

2010/2011
ORACLE
90
 • Transmission de privilèges
GRANT privilège ON table|vue
TO user|PUBLIC [WITH GRANT OPTION];
Privilèges :
SELECT : lecture INSERT : insertion
UPDATE : mise à jour DELETE : suppression
ALL : tous les privilèges ALTER : destruction
INDEX : construction d’index
• Suppression de privilèges
Docteur : Abdelalim SADIQ

REVOKE privilège ON table|vue FROM user|PUBLIC;

2010/2011 ORACLE 91
 • Contient sous forme relationnelle la définition de tous les objets créés par
le système et les usagers.

• Ces tables sont accessibles avec SQL (en mode consultation uniquement).

• Quelques tables utiles gérées par Oracle

– USER_CATALOG (TABLE_NAME, TABLE_TYPE)

– USER_TAB_COLUMNS (TABLE_NAME, COLUMN_NAME, …)

– USER_IND_COLUMNS (INDEX_NAME, TABLE_NAME, COLUMN_NAME, …)

– ALL_TABLES (TABLE_NAME, OWNER, …)

Docteur : Abdelalim SADIQ

2010/2011 ORACLE 92
 Exemples

• Tables qui contiennent un attribut Intitule

SELECT TABLE_NAME FROM USER_TAB_COLUMNS

WHERE COLUMN_NAME=‘Intitule’;

• Attributs de la table Client

SELECT COLUMN_NAME FROM USER_TAB_COLUMNS

WHERE TABLE_NAME=‘Client’;

• Tables de l’utilisateur darmont

Docteur : Abdelalim SADIQ

SELECT TABLE_NAME FROM ALL_TABLES

WHERE OWNER=‘darmont’;
2010/2011 ORACLE 93
 Docteur : Abdelalim SADIQ

TP5

2010/2011
ORACLE
94
 • PL/SQL : Langage procédural
Extension de SQL

• Déclaration de variables et de constantes

• Définition de sous-programmes

• Gestion des erreurs à l’exécution (exceptions)

• Manipulation de données avec SQL

Docteur : Abdelalim SADIQ

2010/2011 ORACLE 95
 DECLARE
--Déclaration constantes/variables
BEGIN
--Commandes/instructions
EXCEPTION
--Traitement des erreurs à l’exé.
END;
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 96
  Partie déclarative d’un bloc PL/SQL ou d’un sous-programme
• Syntaxe:
• Identifiant [CONSTANT] Type_données [NOT NULL] [:=|DEFAULT expr];
• Règle de dénomination:
– Préfixer les noms des variables par « v_ » exp: v_dn
– Préfixer les noms des constantes par « c_ » exp: c_ttva
• Types usuels :
INTEGER, REAL, STRING, DATE, BOOLEAN + types SQL(NUMBER,
VARCHAR2…)
• Variables:
V_dn DATE;
compt INTEGER:=0; -- Valeur par défaut
Docteur : Abdelalim SADIQ

v_numclicompteur INTEGER DEFAULT 0; -- idem

v_id CHAR(5) NOT NULL:=‘AP001’;

2010/2011 ORACLE 97
 • Constantes
– c_pi CONSTANT REAL:=3.14;
– c_ttva CONSTANT REAL DEFAULT 0.19;
• Type d’une colonne d’une table
– Syntaxe:
nom_var nom_table.nom_colonne%TYPE;
– EX: v_nom [Link]%TYPE;
• Type d’une autre variable
v_credit REAL;
v_debit credit%TYPE;
• Type d’un tuple d’une table
– Syntaxe:
Docteur : Abdelalim SADIQ

Nom_var nom_table%ROWTYPE;
– Ex: v_client client%ROWTYPE;

2010/2011 ORACLE 98
 • Tableaux : 1) Déclaration d’un type tableau
2) Déclaration d’une variable de ce type
• Syntaxe :
TYPE nom_type IS TABLE OF type éléments
INDEX BY BINARY_INTEGER L’option INDEX BY BINARY_INTEGER
est facultative depuis la version 8 de
ex. TYPE Tab_entiers TABLE OF INTEGER PL/SQL. Si elle est omise, le type
INDEX BY BINARY_INTEGER; déclaré est considéré comme une
TYPE Tab_cli TABLE OF [Link]%TYPE nested table (extension objet). Si elle
est présente, l’indexation ne
INDEX BY BINARY_INTEGER; commence pas nécessairement à 1 et
1. Déclaration d’une variable de ce type peut être même négative (l’intervalle
v_entier Tab_entiers; de valeurs du type BINARY_INTEGER
va de – 2 147 483 647 à 2 147 483
v_client Tab_cli; 647).
2. Utilisation
Docteur : Abdelalim SADIQ

v_nomClient(1):=‘ALI’;
v_entiers(-1):=-10;

2010/2011 ORACLE 99
 SET SERVEROUTPUT ON END LOOP;
DECLARE k := t ( 1 0 ) ;
TYPE numberTab IS VARRAY (10) FOR i in REVERSE 2 . . 1 0 LOOP
OF NUMBER; t ( i ) := t ( i - 1 ) ;
t numberTab ; END LOOP;
i number ; t ( 1 ) := k ;
k number ; FOR i IN 1 . . 1 0 LOOP
BEGIN DBMS_OUTPUT.PUT_LINE( t ( i )
t := numberTab ( ) ; );
t .EXTEND( 1 0 ) ; END LOOP;
FOR i IN 1 . . 1 0 LOOP END;
Docteur : Abdelalim SADIQ

t ( i ) := i ; /

2010/2011 ORACLE 100

 • Enregistrements personnalisés (Structures) : Une structure est un type regroupant
plusieurs types. Une variable de type structure contient plusieurs variables, ces variables
s'appellent aussi des champs
1).Définition d’un type enregistrement
– Syntaxe
Type nom_type IS RECORD(
Nom_champ1 type champ1 [Not NULL] [valeur initiale],
…
Nom_champK type champk [Not NULL] [valeur initiale]);
ex. TYPE Enr_four IS RECORD (
numero [Link]%TYPE,
raison_ sociale CHAR(20));
2). Définition d’une variable de ce type
ex. v_fournisseur Enr_four;
3). Utilisation
Docteur : Abdelalim SADIQ

V_four.num:=4;
V_four.desc:=‘fournisseur matériels informatique’;

2010/2011 ORACLE 101

 • Affectation
ex. numero:=0;
numero:=numero+1;
SELECT numcli INTO numero
FROM client WHERE numcli=numero+1;

SELECT numfour, raisonsoc

INTO v_four.num_four, v_four.rais_soc
FROM fournisseur
WHERE numfour=4;
• Utilisation des tableaux
ex. i:=1;
v_entier(i):=i*2;
Docteur : Abdelalim SADIQ

NB : i doit être de type BINARY_INTEGER.

2010/2011 ORACLE 102

 • Exercice: Evaluez chacune des déclarations suivantes. Déterminez
lesquelles sont invalides et expliquez pourquoi.
– v_id NUMBER(4);

Valide

– v_x, v_y, v_z VARCHAR2(10);

Invalide car un seul identifiants est autorisé par ligne de déclaration,

– v_dn DATE NOT NULL

Invalide car une variable NOT NULL doit être initialisé à sa création,

– v_valide BOONEAN := 1;
Docteur : Abdelalim SADIQ

Invalide car 1 n’est pas une expression booléenne,

2010/2011 ORACLE 103

 Utilisation des enregistrements personnalisés

ex.

v_fournisseur.numero := 4589;

v_fournisseur.raison_sociale := ‘COGIP’;

SELECT numfour, raisonsoc

INTO v_fournisseur

FROM fournisseur
Docteur : Abdelalim SADIQ

WHERE numfour=4589;

2010/2011 ORACLE 104

 • Instruction sélective

IF-THEN, IF-THEN-ELSE ou IF-THEN-ELSIF

IF condition1 THEN
Exemple:
-- Instructions IF MOD(i,5)=5 THEN
ELSEIF condition2 THEN v_qte:=5;

-- Instructions ELSEIF MOD(i,7)=0 THEN

v_qte:=7;
ELSE
ELSE
-- Instructions
Docteur : Abdelalim SADIQ

v_qte:=i;
END IF; END IF;

2010/2011 ORACLE 105

 • Instruction sélective Exemple:
CASE v_sel DECLARE
WHEN expression_1 THEN résultat_1;
v_m INTEGER;
…;
v_mention VARCHAR2(20);
WHEN expression_k THEN résultat_k;
BEGIN
[ELSE résultat_par_defaut;]
END CASE; v_mention:=CASE v_m
WHEN 1 THEN ‘REDOUBLE’
Ou WHEN 2 THEN ‘PASSABLE’
CASE WHEN 3 THEN ‘ASSEZ-BIEN’
WHEN condition_1 THEN résultat_1 WHEN 4 THEN ‘BIEN’
… WHEN 5 THEN ‘TRES BIEN’
Docteur : Abdelalim SADIQ

WHEN condition_k THEN résultat_k

END;
[ELSE résultat_par_defaut]
END;
END CASE;
2010/2011 ORACLE 106
 • Instruction sélective Exemple:
CASE v_sel DECLARE
v_moy REAL;
WHEN expression_1 THEN instructions; v_resultat VARCHAR2(20);
…; v_compt INTEGER :=1;
WHEN expression_k THEN instructions; BEGIN
CASE
[ELSE instructions;] WHEN v_moy<9.80 THEN
END CASE; v_compt:=v_compt+1;
v_resultat:=‘REDOUBLE’;
WHEN v_moy>=9.80 AND
Ou v_moy<10THEN
CASE v_compt:=v_compt+1;
v_resultat := ‘ZONE DE RACHAT’;
WHEN condition_1 THEN instructions ;
ELSE
… v_compt:=v_compt+1;
Docteur : Abdelalim SADIQ

WHEN condition_k THEN instructions; v_resultat :=‘REUSSIT

END CASE;
[ELSE instructions;] END;
END CASE;
2010/2011 ORACLE 107
 • Instructions itératives Exemple:
FOR compteur IN [REVERSE] min..max DECLARE
c_max_loop CONSTANT POSITIVE:=30;
LOOP v_compt POSITIVE :=1;
-- Instructions
BEGIN
END LOOP; LOOP
WHILE condition LOOP v_compt:=v_compt+1;
IF v_compt=c_max_loop THEN
-- Instructions GOTO e_suivant;
END LOOP; END IF;
END LOOP;
LOOP <<e_suivant>>
-- Instructions v_compt:=v_compt*10;
END;
END LOOP;
Docteur : Abdelalim SADIQ

Ou

EXIT WHEN v_compt:=c_max_loop;

2010/2011 ORACLE 108

 • Branchements

--déclaration d’une étiquette

<<nom_étiquette>>

-- Saut inconditionnel

GOTO étiquette;

-- Sortie de boucle

EXIT WHEN condition;

• NB : À utiliser avec énormément de modération !

Docteur : Abdelalim SADIQ

2010/2011 ORACLE 109

 • Curseur : Structure de données permettant de stocker le résultat d’une
requêtes qui retourne plusieurs tuples.

• Déclaration :
CURSOR nom_curs IS requête;
ex. CURSOR calcul IS
SELECT numprod, pu*1.206 pTTC
FROM produit
ORDER BY NumProd;
Tuple calcul%ROWTYPE.
Docteur : Abdelalim SADIQ

NB : Un tuple du curseur sera de type

calcul%ROWTYPE.

2010/2011 ORACLE 110

 • Ouverture d’un curseur : OPEN nom_curs;
• Gestion automatique d’un curseur
ex. FOR tuple IN calcul LOOP
var1:=[Link];
var2:=[Link];
END LOOP;
• Gestion « manuelle »
ex. LOOP
FETCH calcul INTO tuple;
EXIT WHEN calcul%NOTFOUND;
…
Docteur : Abdelalim SADIQ

END LOOP

2010/2011 ORACLE 111

 • Attributs des curseurs

• %NOTFOUND : FALSE si FETCH retourne un résultat

TRUE si FETCH ne retourne pas un résultat

• %FOUND : opposé logique de %NOTFOUND

• %ROWCOUNT : Nombre de lignes lues

• %ISOPEN : TRUE si le curseur est ouvert

FALSE si le curseur est fermé

• Fermeture d’un curseur : CLOSE nom_curs;

Docteur : Abdelalim SADIQ

2010/2011 ORACLE 112

 Gestio automatique d’un curseur Gestion manuelle
DECLARE DECLARE
CURSOR calcul IS… CURSOR calcul IS…
BEGIN V_enreg calcul%ROWTYPE;
BEGIN
FOR v_enreg IN calcul LOOP
OPEN calcul;
Var1:=v_enreg.numprod;
LOOP
Var2:=v_enreg.pTTC; FETCH calcul INTO v_enreg;
… EXIT WHEN
END LOOP; Calcul%NOTFOUND;
END; Calcul%NOTFOUND;
• Remarque Var1:=v_enreg.numprod;
• Ouverture et fermeture du curseur se Var2:=v_enreg.pTTC;
…
Docteur : Abdelalim SADIQ

fait d’une façon implicite

END LOOP
• Déclaration de v_enreg n’est pas
obligatoire CLOSE calcul;
END;
2010/2011 ORACLE 113
 • À chaque erreur à l’exécution, une exception est levée. Ces
exceptions sont gérées par des routines séparées.

• Fonctions PL/SQL pour la gestion d’erreurs

SQLCODE : Code de la dernière exception levée

SQLERRM : Message d’erreur associé

Docteur : Abdelalim SADIQ

2010/2011 ORACLE 114

 Exceptions prédéfinies

Nom Code erreur SQLCODE

CURSOR_ALREADY_OPEN ORA-06511 -6511
DUP_VAL_ON_INDEX ORA-00001 -1
INVALID_CURSOR ORA-01001 -1001
INVALID_NUMBER ORA-01722 -1722
LOGIN_DENIED ORA-01017 -1017
NO_DATA_FOUND ORA-01403 -1403
NOT_LOGGED_ON ORA-01012 -1012
PROGRAM_ERROR ORA-06501 -6501
STORAGE_ERROR ORA-06500 -6500
TIMEOUT_ON_RESOURCE ORA-00051 -51
TOO_MANY_ROWS ORA-01422 -1422
Docteur : Abdelalim SADIQ

VALUE_ERROR ORA-06502 -6502

ZERO_DIVIDE ORA-01476 -1476

2010/2011 ORACLE 115

 • Exceptions personnalisées
– Déclaration : nom_exc EXCEPTION;
– Lever l’exception : IF condition THEN
RAISE nom_exc;
END IF;
• Traitement des exceptions
WHEN nom_exc THEN -- Instruction
ex. WHEN probleme THEN
RAISE_APPLICATION_ERROR(-20501,’Erreur !’);
NB : -20999 ≤ no d’erreur ≤ -20001
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 116

 • Fonctions
FUNCTION nomf (param1, param2…)
RETURN type_valeur_de_retour IS
-- Déclarations locales
BEGIN
-- Instructions
RETURN valeur_de_retour;
EXCEPTION
-- Traitement des exceptions
END;
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 117

 • Procédures
PROCEDURE nomp (param1, param2…) IS
-- Déclarations locales
BEGIN
-- Instructions
EXCEPTION
-- Traitement des exceptions
END;
• Paramètres
nom_param [IN|OUT|IN OUT] TYPE
ex. resultat OUT REAL
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 118

 • -- Calcul du prix TTC des produits

• -- et recopie dans la table PRODTTC

DECLARE

nbp NUMBER(3);

aucun_produit EXCEPTION;

CURSOR calcul IS

SELECT numprod, prixuni*1.206 prixttc

FROM produit;
Docteur : Abdelalim SADIQ

tuple calcul%ROWTYPE;

2010/2011 ORACLE 119

 BEGIN
-- Comptage des produits
SELECT COUNT(*) INTO nbp FROM produit;

-- Test « il existe des produits » ou pas ?

IF nbp = 0 THEN
RAISE aucun_produit;
END IF;
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 120

 -- Recopie des valeurs dans la table prodttc
FOR tuple IN calcul LOOP
INSERT INTO prodttc VALUES
([Link], [Link]);
END LOOP;
-- Validation de la transaction
COMMIT;
EXCEPTION
WHEN aucun_produit THEN
RAISE_APPLICATION_ERROR(-20501,
‘Erreur : table client vide’);
Docteur : Abdelalim SADIQ

END;

2010/2011 ORACLE 121

 • EX1
Écrire un Bloc PL/SQL qui permet de :
– Afficher le nom et le prénom du client numéro 1,
– Concaténer les informations Rue, CP et Ville du client numéro 1 dans la variable global
g_adr
• Ex2 : Donner le Rôle du bloc PL/SQL Suivant :
DECLARE
v_som REAL;
c_max_loop CONSTANT POSITIVE := 30;
v_compt POSITIVE := 1;
BEGIN
v_som := 0 ;
WHILE (v_compt <= c_max_loop) LOOP
v_som := v_som + v_compt ;
Docteur : Abdelalim SADIQ

v_compt := v_compt+1 ;
END LOOP ;
DBMS_OUTPUT.PUT_LINE ( ‘La somme est : ‘||TO_CHAR(v_som) ) ;
END;

2010/2011 ORACLE 122

 EX3 : Écrire un bloc PL/SQL qui permet de déclarer :
 Un enregistrement ETUDIANT (nom : chaîne de longueur 10, prénom :
chaîne de longueur 20 et age : numérique de longueur 3),
 Un tableau d’étudiant,
 Une variable v_tabEtud de type tableau d’étudiant,Ce bloc permet de :
 Remplir 4 cases de ce table par les étudiants : (‘Barkaoui’, ‘Kamel’, 45),
(‘Salmi’, ‘Mahmoud’, 12), (‘Sassi’, ‘Med Ali’, 20) et (‘Sahli’, ‘Hassen’, 34)
 Afficher le nom et le prénom de l’étudiant qui est le plus petit,
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 123

 • Ex4 : Ecrivez un programme affectant les valeurs 1 et 2 a deux variables a
et b, puis permutant les valeurs de ces deux variables.

• Ex5 : Ecrivez un programme plaçant la valeur 10 dans une variable a, puis

affichant la factorielle de a.

• Ex6 : Ecrivez un programme plaçant les valeurs 48 et 84 dans deux

variables a et b puis affichant le pgcd de a et b.
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 124

 • Ex7 :
1. Créez un type tableau pouvant contenir jusqu'a 50 entiers.
2. Créez une variable de ce type , faites une allocation dynamique et
dimensionnez ce tableau a 20 emplacements.
3. Placez dans ce tableau la liste des 20 premiers carres parfaits : 1, 4, 9,
16, 25, …
4. Inversez l'ordre des éléments du tableau
5. Affichez le tableau.
• Ex8 :
Triez le tableau précédent avec la méthode du tri a bulle.
• Ex9 :
Docteur : Abdelalim SADIQ

Recherchez, par dichotomie, si l‘élément 225 se trouve dans le tableau.

2010/2011 ORACLE 125

 • Ex10

Faites de sorte que les scripts important les données des tables CLIENT
vers la table person ne puissent être exécutes qu'une seule fois.

• Ex 11

Les scripts remplissant la table Opération ne fonctionneront pas

aujourd'hui... Même s'il fonctionnaient la dernière fois. Trouvez les codes
d'erreurs des exceptions levées par ces scripts, rattrapez-les de la façon la
plus appropriée qui soit.
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 126

 • Ex12
• Ecrire une fonction récursive retournant bn, avec n entier positif ou nul.
• Ex13
• Ecrire une fonction demi-frères prenant deux numéros de personnes en
paramètre et retournant vrai si et seulement si ces deux personnes ont un
parent en commun.
• Ex14
• Ecrire une fonction cousins germains prenant deux numéros de personnes
en paramètre et retournant vrai si et seulement si ces deux individus sont
cousins germains.
• Ex15
• Ecrire une procédure récursive affichant le nom de la personne dont le
Docteur : Abdelalim SADIQ

numéro est passe en paramètre et se rappelant récursivement sur le père

de cette personne.

2010/2011 ORACLE 127

 • EX16
• Ecrire une procédure récursive affichant les noms des ascendants de sexe
masculin de la personne dont le numéro est passe en paramètre.
• Ex17
• Ecrire une fonction récursive prenant deux numéros de personne A et B et
retournant vrai si A est un ascendant de B.
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 128

 • EX18

En utilisant la table PERSONNE , écrivez une fonction affichant toute la

descendance d'une personne

• EX19

Ecrire une procédure qui affiche tous les clients, et pour chaque client, la
liste des comptes, et pour chacun de ces comptes, l'historique des
opérations.
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 129

 Docteur : Abdelalim SADIQ

2010/2011
ORACLE
130
 • Trigger (déclencheur) : routine déclenchée automatiquement
par des événements liés à des actions sur la base

• Les triggers complètent les contraintes d’intégrité en

permettant des contrôles et des traitements plus complexes.
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 131

 Insertion Suppression Mise à jour
Avant 1 2 3
Après 4 5 6
Docteur : Abdelalim SADIQ

2010/2011 ORACLE 132

 • En SQL*Plus :
– CREATE [OR REPLACE] TRIGGER nom_trig

– BEFORE|AFTER

– INSERT|DELETE|UPDATE

– ON nom_table

– FOR EACH ROW

– -- Bloc PL/SQL contenant le

– -- traitement à effectuer

• Variables spécifiques
Docteur : Abdelalim SADIQ

– :OLD.nom_attribut : valeur de l’attribut avant mise à jour

– :NEW.nom_attribut : valeur de l’attribut après mise à jour

2010/2011 ORACLE 133

 • -- Test de clé primaire sur la table client IF n>0 THEN
CREATE OR REPLACE TRIGGER trig_clep RAISE cle_existe;
BEFORE INSERT ON client END IF
FOR EACH ROW -- Valeur nulle
DECLARE IF :[Link] IS NULL THEN
n INTEGER; RAISE cle_nulle;
cle_existe EXCEPTION; END IF;
cle_nulle EXCEPTION; EXCEPTION
BEGIN WHEN cle_existe THEN
-- Existence de la clé primaire RAISE_APPLICATION_ERROR(-
SELECT COUNT(numcli) INTO n FROM 20501,‘Clé primaire déjà utilisée !’);
client WHEN cle_nulle THEN
WHERE numcli=:[Link]; RAISE_APPLICATION_ERROR(-
20502,‘La clé primaire doit avoir une
Docteur : Abdelalim SADIQ

valeur!’);
END;

2010/2011 ORACLE 134

 • Implémentez les contraintes suivantes.
1. Il ne doit pas être possible de modifier la note min dans la table perquise.
2. Dans un module, il ne doit pas y avoir plus de effecMax élèves inscrits.
3. On ne peut créer un examen pour un module que s'il y a des élèves inscrits dans ce module.
4. Un élève ne peut passer un examen que si sa date d'inscription est antérieure a la date de
l'examen.
5. Il ne doit pas y avoir de circuit dans la table perquise (il existe une façon de la verrier en PL/SQL,
mais comme vous ne la connaissez pas, faites un parcours en profondeur du graphe des pré-
requis)
6. Un élève s'inscrivant a un module doit avoir eu au moins la note min a tous les modules pré-
requis.
7. Ajouter dans étudiant un champ moyenne, celui-ci contiendra la moyenne de chaque étudiant
s'il a passe les examens de tous les modules dans lesquels il est inscrit.
8. Revenez sur la première contrainte : il ne doit être possible de modifier une note min dans la
table perquise que s'il n'existe pas d‘élève dont une inscription serait invalidée.
Docteur : Abdelalim SADIQ

9. Il ne doit être possible de modifier effecMax que si des étudiants ne se retrouvent pas avec une
inscription invalidée.

2010/2011 ORACLE 135