Partie1 MPDS PLSQL

Télécharger au format pdf ou txt
Télécharger au format pdf ou txt
Vous êtes sur la page 1sur 65

Faculté des Sciences de Bizerte

Faculté des sciences de Bizerte

Cours PL/SQL
Présenté par: Mme Olfa DRIDI

1ère année MPDS, 2020-2021


Plan du cours PL/SQL
1. Objectifs du cours
2. Contenu du cours
3. Modalité d’évaluation
4. Horaire des cours et travaux pratiques
5. Références

© Olfa DRIDI, FSB, 2020 2


Objectifs généraux
1. Donner à l’étudiant un aperçu des concepts BD relationnelle.
2. Faire un rappel du SQL
3. Initier l’étudiant aux principes BD relationnelle.
4. Initier l’étudiant aux interrogations de textes, images et vidéos insérés
dans une base de donnée.
5. Rendre l’étudiant apte à développer une BD avec Oracle 11g
6. Rendre l’étudiant apte à exploiter une base de donnée.

© Olfa DRIDI, FSB, 2020 3


Contenu
1. Introduction
2. Le langage SQL
3. Le langage PL/SQL
4. Les curseurs
5. Les exceptions
6. Les fonctions et les procédures stockées
7. Les packages
8. Les déclencheurs

© Olfa DRIDI, FSB, 2020 4


Horaire des cours et travaux pratiques
• Un cours magistral par semaine
• Une heure et demi de travaux pratiques par semaine
• Travail personnel (Lectures diverses + essayer les exercices )
– 2 heures de travail par semaine

© Olfa DRIDI, FSB, 2020 5


Liste des travaux pratiques
Liste des sujets:
• TP1: Installation des outils et connexion à la BD
• TP2: Rappel SQL
• TP3: Bloc PL/SQL
• TP4: Les curseurs
• TP5: Les exceptions
• TP6: Les fonctions et les procédures stockées
• TP7: Les déclencheurs

© Olfa DRIDI, FSB, 2020 6


Modalité d’évaluation
1. Travaux pratiques: 20%
Ø Chaque TP sera noté

1. Devoir surveillé: 10%

2. Examen final:70%

© Olfa DRIDI, FSB, 2020 7


Référence principale
1. Oracle Database 11g PL/SQL Programming Workbook
• By Michael McLaughlin, John M. Harper

2. Oracle PL/SQL Programming, 6th Edition


• By Steven Feuerstein, Bill Pribyl

© Olfa DRIDI, FSB, 2020 8


Référence principale

• Pour plus d’informations, visitez le site Web:


https://academy.oracle.com/en/oa-web-overview.html

© Olfa DRIDI, FSB, 2020 9


Avant de commencer …
Pourquoi un cours de PLSQL dans un mastère professionnel de Data
Science?
10
© Olfa DRIDI, FSB, 2020
Data driven science : le 4e paradigme (Jim Gray
- Prix Turing)
• SNR 2013
Extrait : ”A l’heure actuelle, la science vit une révolution qui conduit à
nouveau paradigme selon lequel ’la science est dans les données’,
autrement dit la connaissance émerge du traitement des données [...]
Le traitement de données et la gestion de connaissances
représentent ainsi le quatrième pilier de la science après la théorie,
l’expérimentation et la simulation. L’extraction de connaissances à
partir de grands volumes de données (en particulier quand le nombre
de données est bien plus grand que la taille de l’échantillon),
l’apprentissage statistique, l’agrégation de données hétérogènes, la
visualisation et la navigation dans de grands espaces de données et
de connaissances sont autant d’instruments qui permettent d’observer
des phénomènes, de valider des hypothèses, d’élaborer de nouveaux
modèles ou de prendre des décisions en situation critique”

© Olfa DRIDI, FSB, 2020 11


Traitement de données
• 68% des entreprises qui ont systématiquement recours à une
analyse de données dans leurs prises de décision voient leurs
bénéfices augmenter.
* Selon une étude menée par the Economist Intelligence Unit (2014)

• Pour qui réussit à optimiser son usage, la donnée devient


information, puis, bien partagée au sein de l’entreprise, elle
se transforme en connaissance et constitue son savoir. Elle
peut être une source de services et d’innovations, notamment
lorsqu’on la croise avec d’autres données et qu’elle provient
de sources diverses.
* Enjeux Business des données - CIGREF 2014

© Olfa DRIDI, FSB, 2020 12


Traitement de données en entreprise
• La donnée est donc l’un des principaux actifs immatériels de
nos organisations, et pour autant, n’est pas encore gérée avec
la même rigueur ni les mêmes moyens que les autres
ressources, capital et ressources humaines notamment. Dans
un contexte où elle est devenue critique pour l’activité de
l’entreprise, la mise en place d’une gestion structurée et
industrielle de la donnée est impérative.
* Enjeux Business des données - CIGREF 2014

• Fin 2018, le volume mondial de données numériques


atteignait 33 zettaoctets (1021 octets). Il dépassera les
610 en 2020.

© Olfa DRIDI, FSB, 2020 13


L’information est un facteur de production
presque comme les autres
• L’information a une valeur, variable selon son importance, son
ancienneté…
• L’information doit être
– extraite,
– stockée,
– traitée,
– maintenue…
àc’est la tâche du système d’information !

© Olfa DRIDI, FSB, 2020 14


Définition SI
En informatique et en télécommunications, et plus généralement dans le
monde de l'entreprise, le terme système d'information (ou SI) possède les
significations suivantes :

ØUn ensemble organisé de ressources (personnel, données, procédures,


matériel, logiciel, …) permettant d'acquérir, de stocker, de structurer et de
communiquer des informations sous forme de textes, images, sons, ou de
données codées dans des organisations. Selon leur finalité principale, on
distingue :

a. des systèmes d'information supports d'opérations (traitement de


transaction, contrôle de processus industriels, supports d'opérations de
bureau et de communication)

b. des systèmes d'information supports de gestion (aide à la production de


rapports, aide à la décision…).

© Olfa DRIDI, FSB, 2020 15


Pré-requis du cours PL/SQL

CHAPITRE 1:
LE LANGAGE SQL

Préparé par: Olfa DRIDI

© Olfa DRIDI, FSB, 2020 16


Plan du chapitre 1

• Bases de données

• Système de gestion de base de doonées

• Présentation d’ORACLE

• Le langage SQL

© Olfa DRIDI, FSB, 2020 17


Bases de données
• Base de données (BD, “ database ”)
– Au sens large, collection de données
¨Différentes interprétations
¨ fichier de texte
¨ ensemble de fichiers de textes
¨ un seul fichier d'enregistrements
¨ ensemble de fichiers d’enregistrements
¨ plusieurs sous bases de données indépendantes les unes des autres
¨ Toile : base de données de nature hétérogène et répartie
¨ BD logique = une collection de BD physiques

© Olfa DRIDI, FSB, 2020 18


Bases de données
• Base de données (“database”) : au sens strict,
ensemble de données
1. fortement structurées;
2. persistantes;
3. structure définie dans un schéma;
4. gérées par un système de gestion de bases de données (SGBD).

© Olfa DRIDI, FSB, 2020 19


Système de gestion de base de
données (SGBD)
(SGBD “database management system - DBMS”)
• Logiciel spécialisé pour la gestion de base de données
• Fournit des fonctionnalités liées à la gestion des BD
indépendamment du domaine d'application
– Offre des mécanismes de stockage des données en mémoire
secondaire pour en assurer la persistance
– Offre des mécanismes d'accès à ces données, en sélection et en mise à
jour (insertion, suppression, modification)
– Garantit la performance de ces accès
– Gère l’intégrité des données : intégrité sémantique, gestion de la
concurrence, fiabilité et gestion des pannes, sécurité des données
(ACIDL)

© Olfa DRIDI, FSB, 2020 20


Qu’est-ce qu’un SGBDR ?
• Oracle est un Système de Gestion de Base de Données
Relationnelles (SGBDR)
• Oracle est chargé de :
– stocker les données,
– vérifier les contraintes d’intégrité définies,
– garantir la cohérence des données qu’il stocke, même en cas de panne
(arrêt brutal) du système,
– assurer les relations entre les données définies par les utilisateurs,
– respecte la norme ACID.
• Oracle est un Système de Gestion de Base de données multi-
platormes (UNIX, Linux et Windows)

© Olfa DRIDI, FSB, 2020 21


Historique

© Olfa DRIDI, FSB, 2020 22


Pourquoi Oracle?
• Oracle propose l’ensemble d’outils le plus complet et le plus
intégré pour les développements d’applications, de base de
données et de business intelligence prenant en charge
l’ensemble des approches de développement, plates-formes
technologiques ou systèmes d’exploitation.
Les Composants/Outils d’Oracle
Les outils d’administration

Les outils de développement

Les outils de communication


Les outils de génie logiciel

Les outils d’aide à la décision

© Olfa DRIDI, FSB, 2020 23


Architecture d’ORACLE
• Constituée par un ensemble de couches de bases et d’un
ensemble d’outils
– Le noyau : permet le stockage physique des datas
– Le dictionnaire de données : est une méta-base qui décrit
dynamiquement la BD
– La couche SQL : le langage SQL permet d’écrire des requêtes mais ne
possède pas la puissance d’un langage de programmation
– La couche PL/SQL : extension procédurale du SQL

• Les outils : SQL*Plus, SQL*Loader, SQL*net, Database


Control…

© Olfa DRIDI, FSB, 2020 24


Cours: Systèmes de Gestion de Bases de Données :

Langage de Définition de Données


&
Langage de Contrôle de Données
&
langage d’interrogation de Données
25
© Olfa DRIDI, FSB, 2020
SQL
• SQL signifie « Structured Query Language » c'est-à-dire Langage
d'interrogation structuré.
• SQL est un langage standardisé, implémenté par tous les SGBDR*, qui
permet, indépendamment de la plate- forme technologique et de façon
déclarative, de définir le modèle de données, de le contrôler et enfin de le
manipuler.
• SQL est un langage complet de gestion de bases de données
relationnelles. Il a été conçu par IBM dans les années 70. Il est devenu le
langage standard des systèmes de gestion de bases de données (SGBD)
relationnelles (SGBDR).
• La norme SQL2 (appelée aussi SQL92) date de 1992.
• SQL3 (appelée aussi SQL99) est la nouvelle norme SQL.
• SQL est un langage ensembliste et non procédural. Les traitements
complexes sont parfois difficiles à écrire si on ne peut utiliser des variables
et les structures de programmation comme les boucles et les alternatives

© Olfa DRIDI, FSB, 2020 26


SQL
Le langage SQL s'utilise principalement de trois manières :
• Un programme écrit dans un langage de programmation donné utilise
l'interface de programmation du SGBD pour lui transmettre des instructions
en langage SQL. Ces programmes utilisent des composants logiciels tels
que ODBC ou JDBC. Cette technique est utilisée par l'invite de commande
qui permet à un administrateur d'effectuer des opérations sur les bases de
données, opérations qu'il décrit en SQL ;
• Technique dite embedded SQL : des instructions en langage SQL sont
incorporées dans le code source d'un programme écrit dans un autre
langage ;
• Technique des procédures stockées : des fonctions écrites dans un
langage procédural propre à chaque SGBD (PL/SQL pour Oracle,
SQL/PSM pour MySQL ...) sont enregistrées dans la base de données en
vue d'être exécutées par le SGBD. Cette technique est aussi utilisée pour
les triggers - procédures déclenchées automatiquement sur modification du
contenu de la base de données.

© Olfa DRIDI, FSB, 2020 27


SQL
SQL se subdivise en 5 sous- langages:
• Langage de Définition des Données (LDD ou en anglais DDL, Data
Definition Language) : création, modification et suppression des structures
des différents objets de la BD. Les commandes sont: CREATE, ALTER,
DROP, RENAME, et TRUNCATE.
• Langage d’Interrogation des sonnées (LID) : permet de consulter les datas
de la BD avec la commande SELECT.
• Langage de Manipulation des Données (LMD ou en anglais DML, Data
Manipulation Language) : ajout, modification et suppression de la BD avec
les commandes INSERT, UPDATE, et DELETE.
• Langage de Contrôle des Transactions (LCT) : les commandes sont
COMMIT, ROLLBACK, et SAVEPOINT.
• Langage de Contrôle de Données (LCD ou en anglais DCL, Data Control
Language) : gestion des protections d’accès: les commandes sont GRANT
and REVOKE.

© Olfa DRIDI, FSB, 2020 28


CREATE TABLE
• La commande CREATE TABLE permet de créer une table en
SQL.
• Une table est une entité qui est contenu dans une base de
données pour stocker des données ordonnées dans des
colonnes.
• La création d’une table sert à définir les colonnes et le type de
données qui seront contenus dans chacun des colonne (entier,
chaîne de caractères, date, valeur binaire …).

© Olfa DRIDI, FSB, 2020 29


CREATE TABLE
CREATE TABLE nom_de_la_table (
colonne1 type_donnees,
colonne2 type_donnees,
colonne3 type_donnees,
colonne4 type_donnees )
• Pour chaque colonne, il est également possible de définir des options telles
que (liste non-exhaustive):
– NOT NULL : empêche d’enregistrer une valeur nulle pour une colonne.
– DEFAULT valeur: attribuer une valeur par défaut si aucune données n’est indiquée pour
cette colonne lors de l’ajout d’une ligne dans la table.
– UNIQUE : Deux n-uplets ne peuvent pas recevoir des valeurs identiques pour cet attribut.
– CHECK (condition) : Vérifie lors de l'insertion de n-uplets que l'attribut réalise la
condition condition.
– PRIMARY KEY : indiquer si cette colonne est considérée comme clé primaire pour un
index.
– FOREIGN KEY REFERENCES table [(colonne)] [ON DELETE CASCADE/ ON DELETE
SET NULL]

© Olfa DRIDI, FSB, 2020 30


Types de données
• Les types de données disponibles en SQL varient d'un SGBD à l'autre, on
peut néanmoins citer un certain nombre de types standards que l'on
retrouve dans tous les SGBD.
– INTEGER ou INT, SMALLINT - NUMERIC(X)
– DECIMAL(X,Y) ou NUMERIC(X,Y)
– FLOAT(X), REAL
– CHAR(X)
– VARCHAR2(X)
– DATE (AAAA-MM-JJ)
– DATETIME (AAAA-MM-JJ HH:MM:SS)
– BOOLEAN pour représenter des booléens,
– BLOB (pour Binary Long Oject) pour représenter des données binaires
tels que des documents multimédia (images bitmap, vidéo, etc.)
– ...

© Olfa DRIDI, FSB, 2020 31


Exemples
CREATE TABLE Personne (
Nom VARCHAR2(25),
Prenom VARCHAR2(25),
Age NUMERIC(3) );

CREATE TABLE Personne (


N°SS CHAR(13) PRIMARY KEY,
Nom VARCHAR(25) NOT NULL,
Prenom VARCHAR(25) NOT NULL,
Age INTEGER(3) CHECK (Age BETWEEN 18 AND 65),
Mariage CHAR(13) REFERENCES Personne(N°SS),
UNIQUE (Nom, Prenom) );

© Olfa DRIDI, FSB, 2020 32


ALTER TABLE
• Ajout d’une nouvelle colonne à la table avec ses contraintes
• Augmente ou diminuer la largeur d’une colonne existante
• Changer la catégorie d’une colonne, d’obligation à optionnelle
ou vice versa (NOT NULL à NULL ou vice versa)
• Spécification d’une valeur par défaut pour une colonne
existante
• Changer le type de données d’une une colonne existante
• Spécification d’autres contraintes pour une colonne existante
• Activer ou désactiver une contrainte
• Détruire une contrainte.
• Détruire une colonne
• Renommer une colonne.

© Olfa DRIDI, FSB, 2020 33


ALTER TABLE
La commande ALTER TABLE a les options suivantes:
• ADD
• DROP
• MODIFY
• RENAME
• ENABLE/DISABLE

© Olfa DRIDI, FSB, 2020 34


Exemples
CREATE TABLE Employes (
NumEmp number(4,0),
nom varchar2(15),
prenom varchar2(20),
ville varchar2(30) );
• L’option ADD: permet d’ajouter une colonne ou une contrainte à une table.
ALTER TABLE Employes ADD CONSTRAINT emppk PRIMARY
KEY (numeemp);
àPermet d’ajouter une contrainte de PK pour la table employes sur l’attribute
numeemp.
ALTER TABLE Employes ADD (Salaire NUMBER (8,2));
àPermet d’ajouter un attribut Salaire de type NUMBER(8,2) pour la table
Employes.
ALTER TABLE employes ADD (echelon NUMBER(2,0) NOT
NULL CHECK (echelon >10))
àPermet d’ajouter une colonne avec ses contraintes

© Olfa DRIDI, FSB, 2020 35


Exemples
L’option DROP: permet de supprimer une colonne ou une
contrainte à une table.
ALTER TABLE Employes DROP PRIMARY KEY;
àC’est possible car une table a une seule clé primaire.
ALTER TABLE Employes DROP CONSTRAINT
cksalaire;
ALTER TABLE Employes DROP COLUMN nom;

© Olfa DRIDI, FSB, 2020 36


Exemples
• L’option MODIFY: Cette option permet de modifier le type de
données, la valeur par défaut et la contrainte de NOT NULL
sur une table déjà existante.
ALTER TABLE Employes MODIFY (nom NOT NULL);

ALTER TABLE Employes MODIFY (nom varchar2(20));

ALTER TABLE employes MODIFY (ville DEFAULT


'Montréal');

© Olfa DRIDI, FSB, 2020 37


Exemples
• L’option RENAME, permet de renommer une contrainte ou
une colonne.
ALTER TABLE joueurs RENAME CONSTRAINT SYS_C00126194
TO pkjoueurs;

ALTER TABLE Employes RENAME COLUMN salaire TO


SalaireEmp;

© Olfa DRIDI, FSB, 2020 38


Exemples
• L’option DISABLE ou ENABLE, permet de désactiver ou
activer une contrainte. (importance de connaitre le nom de la
contrainte.)
ALTER TABLE Employes DISABLE CONSTRAINT cksalaire;

ALTER TABLE Employes ENABLE CONSTRAINT cksalaire;

© Olfa DRIDI, FSB, 2020 39


DROP TABLE
• Permet de supprimer / Détruire une table.
DROP TABLE Employes;

• A cause de la FOREIGN KEY, les tables référencées ne


peuvent être détruites.
• Pour pouvoir supprimer les tables référencées:
– Soit, vous supprimez les contraintes de FOREIGN KEY d’abord ou les
désactiver.
– Soit supprimez les table dans un certain ordre. (les tables non
référencées en premier)
– Soit par un CASCADE CONSTRAINT, qui supprime les contraintes de
FOREIGN KEY en cascade.

© Olfa DRIDI, FSB, 2020 40


RENAME TABLE
• Permet de renommer une table ou un objet de la base de
données.
RENAME Employes TO EmployesInfo;

© Olfa DRIDI, FSB, 2020 41


Insertion directe de données

INSERT INTO <Nom de la relation> (


<Liste ordonnée des propriétés à valoriser>)
VALUES (<Liste ordonnée des valeurs à affecter aux
propriétés spécifiées ci-dessus>);

Exemples:
• INSERT INTO livre (titre) VALUES ('L''Attrape-cœurs’);

• INSERT INTO livre (pubdate) VALUES


(TO_DATE('20170330','YYYYMMDD’));

• INSERT INTO Virement (Date, Montant, Objet)


VALUES (14-07-1975, 1000, 'Prime de naissance');

Forma?age des données: https://www.postgresql.org/docs/current/static/functions-


formatting.html

© Olfa DRIDI, FSB, 2020 42


Insertion de données par sélection de valeurs
existantes dans la base
INSERT INTO <Nom de la relation> (<Liste ordonnée
des propriétés à valoriser>)
SELECT ...
• Exemple:
INSERT INTO Credit (Date, Montant, Objet)
SELECT Date, Montant, 'Annulation de débit’
FROM Debit
WHERE Debit.Date = 25-12-2001;

© Olfa DRIDI, FSB, 2020 43


Mise-à-jour de données
UPDATE <Nom de la relation>
SET <Liste d'affectations Propriété = Valeur,
Propriété = Valeur>
WHERE <Condition pour filtrer les tuples à mettre à
jour>;
• Exemples:
– UPDATE Compte SET Monnaie='Euro’
WHERE Monnaie='Franc’;

– UPDATE Compte SET Total=Total * 6,55957


WHERE Monnaie='Euro’;

© Olfa DRIDI, FSB, 2020 44


Suppression de données
DELETE FROM <Nom de la relation>
WHERE <Condition pour filtrer les tuples à
supprimer>;
• Exemples:
– DELETE FROM FaussesFactures;

– DELETE FROM FaussesFactures WHERE Auteur = ‘XXX’;

© Olfa DRIDI, FSB, 2020 45


Suppression d’objets
DROP <type objet> <nom objet>;
• Exemples:
– DROP TABLE Personne;
– DROP VIEW Employe;

© Olfa DRIDI, FSB, 2020 46


Langage de Manipulation de Données

47
© Olfa DRIDI, FSB, 2020
LMD
• La requête de sélection ou question est la base de la
recherche de données en SQL.
• La sélection est la composition d'un produit cartésien, d'une
restriction et d'une projection (ou encore la composition d'une
jointure et d'une projection).
SELECT liste d'attributs projetés
FROM liste de relations du produit cartésien
WHERE condition de la restriction;

© Olfa DRIDI, FSB, 2020 48


SELECT
• Exemples:
– SELECT Nom, Prenom FROM Personne WHERE Age>18;
– SELECT Personne.Nom, Personne.Prenom, Vol.Depart
FROM Personne, Vol
WHERE Personne.Vol=Vol.Numero;
• Les opérateurs de comparaison: P=C , P<>C, P<C, P>C,
P<=C, P>=C, P BETWEEN C1 AND C2, P IN (C1, C2, ...) , P
IS NULL, P LIKE 'chaîne’.
– L'opérateur LIKE permet d'effectuer une comparaison partielle. Il est
surtout employé avec les colonnes contenant des données de type
alpha. Il utilise % et _
• % désigne 0 ou plusieurs caractères quelconques
• _ désigne 1 et 1 seul caractère
• Les opérateurs logiques: and, or et not.

© Olfa DRIDI, FSB, 2020 49


SELECT

© Olfa DRIDI, FSB, 2020 50


SELECT DISTINCT
• L'opérateur SELECT n'élimine pas les doublons (i.e. les tuples
identiques dans la relation résultat) par défaut. Il faut pour cela
utiliser l'opérateur SELECT DISTINCT.

© Olfa DRIDI, FSB, 2020 51


Fonctions d’agrégation
• Une fonction d’agrégation (ou fonction de regroupement)
s'applique aux valeurs du sous-ensemble d'un agrégat e
relation avec pour résultat la production d'une valeur atomique
unique (entier, chaîne, date, etc).
• COUNT(e) : Le nombre d’occurrences de e dans le groupe.
– Les n-uplets pour lesquels e vaut NULL ne sont pas comptés.
– * peut remplacer e. Compte alors le nombre de n-uplets du groupe.
• MAX(e) : La valeur maximale de e pour le groupe.
• MIN(e) : La valeur minimale de e pour le groupe.
• SUM(e) : La somme des valeurs de e pour le groupe.
• AVG(e) : La moyenne de l’évaluation de e sur le groupe.
• STDDEV(e) : L’écart-type de e pour le groupe.
• VARIANCE(e) : La variance de e pour le groupe.
© Olfa DRIDI, FSB, 2020 52
Les fonctions SQL
• Traitement numérique: Il existe également de nombreuses
fonctions mathématiques pour effectuer des calculs ou des
statistiques concernant les données contenus dans une base
de données.
ABS(n) Valeur absolue ABS(-32.5)=32.5
CEIL(n) Plus petit entier relatif égal ou supérieur
CEIL(31.5)=32 CEIL(-31.5)=-31
FLOOR(n) Plus grand entier relatif inférieur ou égal
FLOOR(31.5)=31 FLOOR(-31.5)=-32
MOD(m,n) Reste de la division de m par n exp:MOD(35,4)=3
POWER(m,n) m puissance n exp: POWER(4,3)=84
SIGN(n) Indique le signe de n
SIGN(0)=0 SIGN(-5)=-1 SIGN(5)=1
SQRT(n) Racine carrée de n
SQRT(9)=3 SQRT(-9)=NULL
ROUND(n) Arrondi de n à 100 (Partie entière)
ROUND(15.3)=15 ROUND(15.5)=16
TRUNC(n) n tronqué à 100 (Partie entière)
© Olfa DRIDI, FSB, 2020 TRUNC(15.3)=15 53
Les fonctions SQL
• Traitement de chaîne

CONCAT concaténation : équivalent du ||


INITCAP initiales en lettres capitales
LPAD complément ou troncature à n position à gauche
LTRIM / RTRIM suppression en tête/queue d'une chaîne
REPLACE remplacement
RPAD complément ou troncature à n position à droite
• Exemples:
SELECT LPAD('azerty', 4, 'x'); -- retourne 'azer’
SELECT LPAD('azerty', 6, 'x'); -- retourne 'azerty’
SELECT LPAD('azerty', 10, 'x'); -- retourne 'xxxxazerty’
SELECT LPAD('azerty', 9, 'xy'); -- retourne 'xyxazerty’
SELECT LPAD('123', 6, '0'); -- retourne '000123'

© Olfa DRIDI, FSB, 2020 54


Les fonctions SQL
• Traitement de date
ADD_MONTH, ADD_DAY, ajoute des mois, des jours des années à une
ADD_YEAR ... date
renvoie le n° du dernier jour d'un mois d'une
LAST_DAY
date
MONTH_BETWEEN nombre de mois entre deux dates
NEXT_DAY date du prochain jour d'un nom donné
TO_CHAR date sous forme littérale
DATE_DIFF différence entre deux dates

• Exemples:
SELECT sysdate FROM dual;
• Opérations mathématiques sur les dates :
SELECT sysdate + 10 FROM dual;

© Olfa DRIDI, FSB, 2020 55


Les fonctions SQL
• Conversion
– Conversion implicite
– Conversion explicite : TO_DATE, TO_NUMBER, TO_CHAR
• Générales
– NVL (par exemple NVL(X,0) renvoie 0 si X vaut Null)
– CASE WHEN condition1 THEN valeur1 WHEN condition2 THEN valeur2
ELSE valeur3 END
– Imbrication de fonctions : F3(F2(F1(col,arg1),arg2),arg3)
• Extraction de chaîne
– La fonction substr(X, A, B) renvoie les B caractères à partir du caractère
A dans la chaîne X.

© Olfa DRIDI, FSB, 2020 56


Les fonction SQL
• Traitement des dates
– La gestion des dates peut poser des problèmes selon les formats
paramétrés sur le serveur Oracle (en général par défaut le format est
DD-MON-YY). La solution la plus rigoureuse consiste à utiliser la
fonction de conversion TO_DATE.

© Olfa DRIDI, FSB, 2020 57


GROUP BY

SELECT Societe.Nom, AVG(Personne.Age)


FROM Personne, Societe
WHERE Personne.NomSoc = Societe.Nom
GROUP BY Societe.Nom

© Olfa DRIDI, FSB, 2020 58


HAVING
• La clause HAVING permet d'effectuer une second restriction
après l'opération d'agrégation.
• Il est possible d'effectuer une restriction sur le résultat
provenant du calcul de la fonction de groupe. Dans ce cas,
la restriction n'est plus derrière le WHERE mais derrière la
clause HAVING ; ceci pour indiquer à SQL que la restriction
porte sur une donnée calculée et non pas un champ défini
dans une table.
SELECT Societe.Nom, AVG(Personne.Age)
FROM Personne, Societe
WHERE Personne.NomSoc = Societe.Nom
GROUP BY Societe.Nom
HAVING COUNT(Personne.NumSS) > 2;

© Olfa DRIDI, FSB, 2020 59


ORDER BY
• La clause ORDER BY permet de trier les n-uplets du résultat
et sa syntaxe est la suivante :

ORDER BY expression [ ASC | DESC ] [, ...]

• L'ordre ascendant (ASC) ou descendant (DESC) peut être


spécifié, sachant qu'il est ascendant par défaut.

© Olfa DRIDI, FSB, 2020 60


SELECT Num Dept, COUNT(DISTINCT Fonction)
NbFonctions
FROM Employe
WHERE Salaire > 15000
GROUP BY Num Dept
HAVING COUNT(*) > 2;

© Olfa DRIDI, FSB, 2020 61


Opérateur de concaténation
• L'opérateur || permet de concaténer des champs de type
caractères.
SELECT TIT_CODE || ' ' || CLI_PRENOM ||
' ' || CLI_NOM as NOM
FROM T_CLIENT;

© Olfa DRIDI, FSB, 2020 62


SELECT
SELECT [ ALL | DISTINCT ] { * | expression [ AS nom_affiché ] }
[, ...]
FROM nom_table [ [ AS ] alias ] [, ...]
[ WHERE prédicat ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ {UNION | INTERSECT | EXCEPT [ALL]} requête ]
[ ORDER BY expression [ ASC | DESC ] [, ...] ]

© Olfa DRIDI, FSB, 2020 63


Ordre de résolution des requête SQL
• L'ordre de résolution standard d'une requête SQL est :
– FROM
– WHERE
– GROUP BY
– HAVING
– SELECT
– ORDER BY

© Olfa DRIDI, FSB, 2020 64


Questions ???

65
© Olfa DRIDI, FSB, 2020

Vous aimerez peut-être aussi