Oracle

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

TP1

2010/2011 ORACLE 2

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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

2010/2011 ORACLE 3

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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

2010/2011 ORACLE 4

• 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

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• Créer un Service Windows qui gèrera notre instance

2010/2011 ORACLE 5

• oradim -new -sid Mastersid -startmode manual

• Vérifier le démarrage de service par tapez services.msc

• 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

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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 INIT.ORA que nous devons créer.

• L’installation oracle place un fichier dans le répertoire initsmpl.ora c:\oracle\product\10.1.0\Db_1\admin\sample\pfile

2010/2011 ORACLE 6

• 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,

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• Sauvegardez ce fichier (utilisez bloc note)

• dans:

c:\Oracle\master\master_init\init_master_sid.ora

2010/2011 ORACLE 7

db_name = master_basedb_block_size = 2048 # trace (log) filescontrol_files = C:\ORACLE\master\control01_master_sid.ora # add if undo in create databaseundo_management = auto

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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

– @ 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

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• Tapez et sauvegardez sous : c:\Oracle\master\mql_script\create_database.txt

2010/2011 ORACLE 9

CREATE DATABASE master_base

CHARACTER SET WE8ISO8859P1

LogFile

group 1 'c:\oracle\master\redo1.log' size 10M reuse,

group 2 'c:\oracle\ master \redo2.log' Size 10M reuse

DataFile 'c:\oracle\ master \system.dbf' Size 50M

autoextend on

next 10M maxsize unlimited

extent management local

sysaux datafile 'c:\oracle\ mmaster \sysaux.dbf' Size 10M

autoextend on

next 10M

maxsize unlimited

undo tablespace master_undo

datafile 'c:\oracle\ master \master_undo.dbf'

Size 10M

default temporary tablespace master_temp

tempfile 'c:\oracle\ master\master_temp.dbf'

Size 10M

;

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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, SYSAUX.DBF, SYSTEM.DBF, REDO1.LOG, REDO2.LOG et CONTROL01_MQL_SID.ORA

2010/2011 ORACLE 10

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• La base créée est vide, et il faut exécuter des scripts qui installent des tables et programmes systèmes dans la base:

catalog.sql crée le dictionnaire de données

catproc.sql créé les structures pour PL/SQL.

• Tapez les ligne suivant (SqlPlus lance ces deux scripts (20 minutes))

2010/2011 ORACLE 11

connect sys/ mon_pass as sysdba

Startup pFile= c:\oracle\master\master_init\init_master_sid.ora

@? \rdbms\admin\catalog.sql

@? \rdbms\admin\catproc.sqlShutdown

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• Créons à présent un fichier pour nos données (TableSpace):

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

2010/2011 ORACLE 12


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

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• A titre de vérification, nous utilisons le script demobld qu'Oracle a fourni pour créer une table DEPT et EMP

• Tapez:

2010/2011 ORACLE 13


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

@? \ODP.NET\samples\DataSet\RelationalData\Setup\demobld

select * from dept;

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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

2010/2011 ORACLE 14

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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é.

2010/2011 ORACLE 15

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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).

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

2010/2011 ORACLE 16

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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

2010/2011 ORACLE 17

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

TP2

2010/2011 ORACLE 18

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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 --…….

2010/2011 ORACLE 19

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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)

) ;

2010/2011 ORACLE 20

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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

2010/2011 ORACLE 21

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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)

2010/2011 ORACLE 22

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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.

2010/2011 ORACLE 23

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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))

2010/2011 ORACLE 24

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

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)

);

2010/2011 ORACLE 25

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

CREATE TABLE Fournisseur(

NumFour NUMBER(4),

RaisonSoc VARCHAR2(30),

CONSTRAINT PK_FOURNISSEUR PRIMARY KEY (NumFour)

);

2010/2011 ORACLE 26

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

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),

CONSTRAINT CK_PRODUIT_PU CHECK (PU<30000)

);

2010/2011 ORACLE 27

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

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)

);

2010/2011 ORACLE 28

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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);

2010/2011 ORACLE 29

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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:– DROP INDEX nom_index;

2010/2011 ORACLE 30

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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;

2010/2011 ORACLE 31

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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:

• ALTER TABLE nom_table ADD CONSTRAINT nom_constraint…;

2010/2011 ORACLE 32

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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)

2010/2011 ORACLE 33

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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];

2010/2011 ORACLE 34

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

2010/2011 ORACLE 35

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

NP DESIG PU NF

10 PRO10 1100 1

20 PRO20 2045,225 3

COMMANDE

NC NP DATEC QTE

1 10 12-02-2009 2

3 20 16-02-2009 4

3 10 20-02-2009 6

1 20 26-02-2009 3

Fournisseur

Nf Raison soc

1 SOS SALMI

2 SOS INCONNU

3 SOS MED ALI

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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 tuplesSELSECT * FROM nom_table;

2010/2011 ORACLE 36

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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,

2010/2011 ORACLE 37

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• EX4 :liste des Clients qui habitent à Rabat,


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


WHERE UPPER (ville) = ‘RABAT’;

Ou


WHERE LOWER(ville)= ‘rabat’;

2010/2011 ORACLE 38

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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

2010/2011 ORACLE 39

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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


WHERE Qte IS NULL;

2010/2011 ORACLE 40

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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 commençant par « «PR_%INV »

SELCET * FROM Produit

WHERE UPPER(desig) LIKE ‘PR@_@%INV%’ ESCAPE ‘@’;

2010/2011 ORACLE 41

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• EX 8 : Clients habitant une ville dont le nom se termine par AT


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’);

2010/2011 ORACLE 42

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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;

2010/2011 ORACLE 43

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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;

2010/2011 ORACLE 44

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• EX13: Liste des noms et prénoms des clients qui ont lancé des commandes.

SELECT Nom, Prenom

FROM Client, Commande

WHERE

Client.NumCli = Commande.NumCli;

• NB : Utilisation des alias pour alléger l’écriture,

SELECT Nom, Prenom

FROM Client cli, Commande cmd

WHARE

cli.NumCli = cmd.NumCli;

2010/2011 ORACLE 45

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• EX14: Liste des numéros, noms et prénoms des clients qui ont lancé des commandes

SELECT cli.NumCli, Nom, Prenom

From Client cli, commande cmd

WHERE

Cli.NumCli=cmd.NumCli;

• EX 15 : Liste des numéros, noms et prénoms des clients qui ont commandé le 23/12/2008;

SELECT cli.NumCli, Nom, Prenom

FROM Client cli, Commande cmd

WHERE

Cli.NumCli = cmd.NumCli AND

DateC = ’23-12-2008’;

2010/2011 ORACLE 46

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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 :


WHERE

Qte=( SELECT MAX(Qte) FROM Commande

WHERE DateC >= ’15-02-2009’

2010/2011 ORACLE 47

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

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’

);

2010/2011 ORACLE 48

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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’);

2010/2011 ORACLE 49

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

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

Cli1.NumCli = cmd1.NumCli AND

(cmd1.NumCli,cmd1.NumProd) IN (SELECT Cli.NumCli, Prod.NumProd From Client Cli, Produit Prod, Commande Cmd WHERE

Cmd.NumCli=Cli.NumCli AND

Cmd.NumProd = Prod.NumProd AND

NumFour = 1);

2010/2011 ORACLE 50

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

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

WHERE

Cmd.NumCli = cli.NumCli);

2010/2011 ORACLE 51

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

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);

2010/2011 ORACLE 52

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

6. Sous-Requête de Mises A Jour

UPDATE table1 alias1

SET colonne =(

SELECT …

FROM table2 alias2

WHERE

Alias2.colonneA= alias1.colonneB …)

[WHERE ….+;

• Exp:

--Mettre à jour le champ NbrCmd dans la table NbrCmdCli

UPDATE NbrCmdCli NCC SET NbrCmd = (

SELECT count(*)

FROM Commande cmd

WHERE cmd.NumCli = NCC.NumCli);

2010/2011 ORACLE 53

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

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

(SELECT cli.NumCli, Nom, Prenom

From Commande cmd, Client cli

WHERE cmd.NumCli = cli.NumCli);

2010/2011 ORACLE 54

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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’)

2010/2011 ORACLE 55

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• EX 17: Liste des clients qui ont passé au moins une commande


WHERE

EXISTS (SELECT * FROM Commande cmd

WHERE


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


WHERE

NOT EXISTS (SELECT * FROM Commande cmd

WHERE


2010/2011 ORACLE 56

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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);

2010/2011 ORACLE 57

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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);

2010/2011 ORACLE 58

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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

2010/2011 ORACLE 59

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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

DATEC <’01-03-2009’

GROUP BY NumCli;

2010/2011 ORACLE 60

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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;

2010/2011 ORACLE 61

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

UNION INTERSECTION DIFFERENCE

Requête SQL1UNIONRequête SQL2

Requête SQL1INTERSECTRequête SQL2

Requête SQL1MINUSRequête SQL2

2010/2011 ORACLE 62

• 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.

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

INTERSECT, MINUS).

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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;

2010/2011 ORACLE 63

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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’

2010/2011 ORACLE 64

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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’

2010/2011 ORACLE 65

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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***’

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

2010/2011 ORACLE 66

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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’

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

2010/2011 ORACLE 67

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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

SELECT NumCli, NumProd, DateC, Qte,

DECODE(supp,0,’NON SUPPRIME’,1,’SUPPRIME’)

FROM Commande;

2010/2011 ORACLE 68

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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

2010/2011 69ORACLE

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

Nombre de mois situés entre deux datesMONTHS_BETWEEN

ADD_MONTHS

NEXT_DAY

LAST_DAY

ROUND

TRUNC

Ajoute des mois calendaires à une date

Jour qui suit la date spécifiée

Dernier jour du mois

Arrondit une date

Tronque une date

FONCTION DESCRIPTION

2010/2011 70ORACLE

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')

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

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

• LAST_DAY('01-SEP-95')

19.6774194

'11-JUL-94'

'08-SEP-95'

'30-SEP-95'

2010/2011 71ORACLE

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• ROUND('25-JUL-95','MONTH')

• ROUND('25-JUL-95','YEAR')

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

• TRUNC('25-JUL-95','YEAR')

01-AUG-95

01-JAN-96

01-JUL-95

01-JAN-95

2010/2011 72ORACLE

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• Oracle effectue automatiquement les conversions suivantes

VARCHAR2 ou CHAR

De Vers

VARCHAR2 ou CHAR

NUMBER

DATE

2010/2011 73ORACLE

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

NUMBER CHARACTER

TO_CHAR

TO_NUMBER

DATE

TO_CHAR

TO_DATE

2010/2011 74ORACLE

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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

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

TO_CHAR(date, 'fmt')

2010/2011 75ORACLE

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

YYYY

YEAR

MM

MONTH

DY

DAY

Année exprimée avec 4 chiffres

Année exprimée en toutes lettres

Mois exprimé avec 2 chiffres

3 premières lettres du nom du jour

Jour exprimé en toutes lettres

Mois exprimé en toutes lettres

2010/2011 76ORACLE

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

– Conversion d’une chaîne de caractères en format numérique

avec la fonction TO_NUMBER

TO_NUMBER(char)

TO_DATE(char[, 'fmt'])

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

fonction TO_DATE

2010/2011 77ORACLE

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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

2010/2011 ORACLE 78

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

TP3

2010/2011 ORACLE 79

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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

2010/2011 ORACLE 80

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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

2010/2011 ORACLE 81

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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;

2010/2011 ORACLE 82

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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)

2. CREATE OR REPLACE VIEW NbCmdClients

AS SELECT NumCli, COUNT(*) AS nbCMD

FROM Commande

GROUP BY (NumCli)

2010/2011 ORACLE 83

PROBLEME

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• Exemple : Créer la vue ProdCom qui permet de manipuler que les Produits

commandés.

CREATE VIEW ProdCom AS

SELECT P.NumProd, Desig, PU, DateC, Qte

FROM Produit P, Commande C

WHERE P.NumProd=C.NumProd;

• Utilisation:

SELECT NumProd, Desig FROM Prod_com

WHERE Quantite>10;

2010/2011 ORACLE 84

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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

2010/2011 ORACLE 85

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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’);ERREUR à la ligne 1 :

ORA-01732: les manipulations de données sont interdites sur cette vue

2010/2011 ORACLE 86

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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) à partirde la table PRODUIT ne contenant que les produits du fournisseur dont laraison sociale est « SOS SALMI ».– Vérifier la structure de la vue avec DESC

– Vérifier le contenu de la vue avec SELECT.

2010/2011 ORACLE 87

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• Ex3

Insérer avec INSERT trois nouveaux produits dans la vue PRODUIT_1 (exemple prod50, prod60 et pror70). Tels que prod50 et prod60appartiens 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

2010/2011 ORACLE 88

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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


– 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


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

2010/2011 ORACLE 89

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

TP4

2010/2011 ORACLE 90

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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

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

2010/2011 ORACLE 91

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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, …)

2010/2011 ORACLE 92

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

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

SELECT TABLE_NAME FROM ALL_TABLES

WHERE OWNER=‘darmont’;

2010/2011 ORACLE 93

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

TP5

2010/2011 ORACLE 94

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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

2010/2011 ORACLE 95

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

DECLARE

--Déclaration constantes/variables

BEGIN

--Commandes/instructions

EXCEPTION

--Traitement des erreurs à l’exé.

END;

2010/2011 ORACLE 96

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

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

v_numclicompteur INTEGER DEFAULT 0; -- idem

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

2010/2011 ORACLE 97

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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 Client.nom%TYPE;

• Type d’une autre variable

v_credit REAL;

v_debit credit%TYPE;

• Type d’un tuple d’une table– Syntaxe:

Nom_var nom_table%ROWTYPE;

– Ex: v_client client%ROWTYPE;

2010/2011 ORACLE 98

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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

ex. TYPE Tab_entiers TABLE OF INTEGER

INDEX BY BINARY_INTEGER;

TYPE Tab_cli TABLE OF client.nom%TYPE

INDEX BY BINARY_INTEGER;

1. Déclaration d’une variable de ce type

v_entier Tab_entiers;

v_client Tab_cli;

2. Utilisation

v_nomClient(1):=‘ALI’;

v_entiers(-1):=-10;

2010/2011 ORACLE 99

L’option INDEX BY BINARY_INTEGERest facultative depuis la version 8 dePL/SQL. Si elle est omise, le typedéclaré est considéré comme unenested table (extension objet). Si elleest présente, l’indexation necommence pas nécessairement à 1 etpeut être même négative (l’intervallede valeurs du type BINARY_INTEGERva de – 2 147 483 647 à 2 147 483647).

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

SET SERVEROUTPUT ON

DECLARE

TYPE numberTab IS VARRAY (10) OF NUMBER;

t numberTab ;

i number ;

k number ;

BEGIN

t := numberTab ( ) ;

t .EXTEND( 1 0 ) ;

FOR i IN 1 . . 1 0 LOOP

t ( i ) := i ;

END LOOP;

k := t ( 1 0 ) ;

FOR i in REVERSE 2 . . 1 0 LOOP

t ( i ) := t ( i - 1 ) ;

END LOOP;

t ( 1 ) := k ;

FOR i IN 1 . . 1 0 LOOP

DBMS_OUTPUT.PUT_LINE( t ( i ) ) ;

END LOOP;

END;

/

2010/2011 ORACLE 100

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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 fournisseur.numfour%TYPE,

raison_ sociale CHAR(20));

2). Définition d’une variable de ce type

ex. v_fournisseur Enr_four;

3). Utilisation

V_four.num:=4;

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

2010/2011 ORACLE 101

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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;

NB : i doit être de type BINARY_INTEGER.

2010/2011 ORACLE 102

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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;

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

2010/2011 ORACLE 103

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

Utilisation des enregistrements personnalisés

ex.

v_fournisseur.numero := 4589;

v_fournisseur.raison_sociale := ‘COGIP’;

SELECT numfour, raisonsoc

INTO v_fournisseur

FROM fournisseur

WHERE numfour=4589;

2010/2011 ORACLE 104

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• Instruction sélective

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

IF condition1 THEN

-- Instructions

ELSEIF condition2 THEN

-- Instructions

ELSE

-- Instructions

END IF;

2010/2011 ORACLE 105

Exemple:

IF MOD(i,5)=5 THEN

v_qte:=5;

ELSEIF MOD(i,7)=0 THEN

v_qte:=7;

ELSE

v_qte:=i;

END IF;

Do

cte

ur

: A

bd

ela

lim

SA

DIQ


CASE v_sel

WHEN expression_1 THEN résultat_1;

…;

WHEN expression_k THEN résultat_k;

[ELSE résultat_par_defaut;]

END CASE;

Ou

CASE

WHEN condition_1 THEN résultat_1

…

WHEN condition_k THEN résultat_k

[ELSE résultat_par_defaut]

END CASE;

2010/2011 ORACLE 106

Exemple:

DECLARE

v_m INTEGER;

v_mention VARCHAR2(20);

BEGIN

v_mention:=CASE v_m

WHEN 1 THEN ‘REDOUBLE’

WHEN 2 THEN ‘PASSABLE’

WHEN 3 THEN ‘ASSEZ-BIEN’

WHEN 4 THEN ‘BIEN’

WHEN 5 THEN ‘TRES BIEN’

END;

END;

Do

cte

ur

: A

bd

ela

lim

SA

DIQ


CASE v_sel

WHEN expression_1 THEN instructions;

…;

WHEN expression_k THEN instructions;

[ELSE instructions;]

END CASE;

Ou

CASE

WHEN condition_1 THEN instructions ;

…

WHEN condition_k THEN instructions;

[ELSE instructions;]

END CASE;

2010/2011 ORACLE 107

Exemple:

DECLARE

v_moy REAL;

v_resultat VARCHAR2(20);

v_compt INTEGER :=1;

BEGIN

CASE

WHEN v_moy<9.80 THEN

v_compt:=v_compt+1;

v_resultat:=‘REDOUBLE’;

WHEN v_moy>=9.80 AND

v_moy<10THEN

v_compt:=v_compt+1;

v_resultat := ‘ZONE DE RACHAT’;

ELSE

v_compt:=v_compt+1;

v_resultat :=‘REUSSIT

END CASE;

END;

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• Instructions itératives

FOR compteur IN [REVERSE] min..max

LOOP

-- Instructions

END LOOP;

WHILE condition LOOP

-- Instructions

END LOOP;

LOOP

-- Instructions

END LOOP;

2010/2011 ORACLE 108

Exemple:

DECLARE

c_max_loop CONSTANT POSITIVE:=30;

v_compt POSITIVE :=1;

BEGIN

LOOP

v_compt:=v_compt+1;

IF v_compt=c_max_loop THEN

GOTO e_suivant;

END IF;

END LOOP;

<<e_suivant>>

v_compt:=v_compt*10;

END;

Ou

EXIT WHEN v_compt:=c_max_loop;

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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 !

2010/2011 ORACLE 109

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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.

NB : Un tuple du curseur sera de type

calcul%ROWTYPE.

2010/2011 ORACLE 110

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• Ouverture d’un curseur : OPEN nom_curs;

• Gestion automatique d’un curseur

ex. FOR tuple IN calcul LOOP

var1:=tuple.numprod;

var2:=tuple.prixttc;

END LOOP;

• Gestion « manuelle »

ex. LOOP

FETCH calcul INTO tuple;

EXIT WHEN calcul%NOTFOUND;

…

END LOOP

2010/2011 ORACLE 111

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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;

2010/2011 ORACLE 112

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

Gestio automatique d’un curseur

DECLARE

CURSOR calcul IS…

BEGIN

FOR v_enreg IN calcul LOOP

Var1:=v_enreg.numprod;

Var2:=v_enreg.pTTC;

…

END LOOP;

END;

• Remarque

• Ouverture et fermeture du curseur se fait d’une façon implicite

• Déclaration de v_enreg n’est pas obligatoire

Gestion manuelle

DECLARE

CURSOR calcul IS…

V_enreg calcul%ROWTYPE;

BEGIN

OPEN calcul;

LOOP

FETCH calcul INTO v_enreg;

EXIT WHEN

Calcul%NOTFOUND;

Calcul%NOTFOUND;

Var1:=v_enreg.numprod;

Var2:=v_enreg.pTTC;

…

END LOOP

CLOSE calcul;

END;

2010/2011 ORACLE 113

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• À 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é

2010/2011 ORACLE 114

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

Nom Code erreur SQLCODE

CURSOR_ALREADY_OPEN DUP_VAL_ON_INDEX

INVALID_CURSOR INVALID_NUMBER

LOGIN_DENIED NO_DATA_FOUND NOT_LOGGED_ON PROGRAM_ERROR STORAGE_ERROR

TIMEOUT_ON_RESOURCE TOO_MANY_ROWS

VALUE_ERROR ZERO_DIVIDE

ORA-06511 ORA-00001 ORA-01001 ORA-01722 ORA-01017 ORA-01403 ORA-01012 ORA-06501 ORA-06500 ORA-00051 ORA-01422 ORA-06502 ORA-01476

-6511-1

-1001-1722-1017-1403-1012-6501-6500

-51-1422-6502-1476

2010/2011 ORACLE 115

Exceptions prédéfinies

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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

2010/2011 ORACLE 116

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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;

2010/2011 ORACLE 117

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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

2010/2011 ORACLE 118

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• -- 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;

tuple calcul%ROWTYPE;

2010/2011 ORACLE 119

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

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;

2010/2011 ORACLE 120

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

-- Recopie des valeurs dans la table prodttc

FOR tuple IN calcul LOOP

INSERT INTO prodttc VALUES

(tuple.numprod, tuple.prixttc);

END LOOP;

-- Validation de la transaction

COMMIT;

EXCEPTION

WHEN aucun_produit THEN

RAISE_APPLICATION_ERROR(-20501,

‘Erreur : table client vide’);

END;

2010/2011 ORACLE 121

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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 ;

v_compt := v_compt+1 ;

END LOOP ;

DBMS_OUTPUT.PUT_LINE ( ‘La somme est : ‘||TO_CHAR(v_som) ) ;

END;

2010/2011 ORACLE 122

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

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,

2010/2011 ORACLE 123

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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.

2010/2011 ORACLE 124

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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 :

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

2010/2011 ORACLE 125

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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.

2010/2011 ORACLE 126

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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 numéro est passe en paramètre et se rappelant récursivement sur le père de cette personne.

2010/2011 ORACLE 127

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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.

2010/2011 ORACLE 128

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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.

2010/2011 ORACLE 129

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

2010/2011 ORACLE 130

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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.

2010/2011 ORACLE 131

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

Insertion Suppression Mise à jour

Avant 1 2 3

Après 4 5 6

2010/2011 ORACLE 132

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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

– :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

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• -- Test de clé primaire sur la table client

CREATE OR REPLACE TRIGGER trig_clep

BEFORE INSERT ON client

FOR EACH ROW

DECLARE

n INTEGER;

cle_existe EXCEPTION;

cle_nulle EXCEPTION;

BEGIN

-- Existence de la clé primaire

SELECT COUNT(numcli) INTO n FROM client

WHERE numcli=:NEW.numcli;

IF n>0 THEN

RAISE cle_existe;

END IF

-- Valeur nulle

IF :NEW.numcli IS NULL THEN

RAISE cle_nulle;

END IF;

EXCEPTION

WHEN cle_existe THEN

RAISE_APPLICATION_ERROR(-20501,‘Clé primaire déjà utilisée !’);

WHEN cle_nulle THEN

RAISE_APPLICATION_ERROR(-20502,‘La clé primaire doit avoir une valeur!’);

END;

2010/2011 ORACLE 134

Do

cte

ur

: A

bd

ela

lim

SA

DIQ

• 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.

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

Documents

Oracle