Oracle : Langage SQL

Blue Flower

Chercher

Détails: Catégorie : Oracle; Publication : 30 novembre 2017; Affichages : 5356

Données

ORACLE

2 Introduction au langage SQL

2.1 Préliminaires
Ici on va parler des commandes nécessaires permettant de travailler avec le SGBD Oracle; Dans une base de données, on commence par créer les tables de la base et ensuite on utilise différents commandes ou instructions pour manipuler les données enregistrées dans la base. Si pour une raison ou pour une autre vous voulez aller plus loin, n'hésitez pas à vous référer à la documentation de Oracle.

2.2 Oracle : Les types des données

Quand on manipule des tables de données avec le SGBD Oracle (ou n'importe quel SGBD d'ailleurs), il faut être précis sur les types des données que peut contenir une quelconque colonne des tables.

2.3 Les chaînes de caractères

le type CHAR représente un caractère
 quant à CHAR(N), il représente une chaîne d'au plus N caractères (N<=255) et
LONG une chaîne d'au plus 65535 caractères.
Les constantes chaînes sont entourées d'apostrophes. 
Une apostrophe dans le texte doit être doublée :
ci-dessus on donne deux exemples de  contante chaînes de caractères
'Jean'
'aujourd''hui'

On vient donc de voir que Oracle, manipule les caractères en différenciant bien un caractère d'une chaîne de caractères ; une chaîne de caractères peut-être de taille inférieure ou égale à N (N<=255) ou d'au plus 65535 caractères.

2.4 Les nombres

Les types numériques :

NUMBER ce type représente un nombre
NUMBER(N) quant à lui un nombre entier de N chiffres,
NUMBER(N,M) un nombre sur N positions dont M après la virgule et 
NUMBER(N,-M) un nombre entier de N positions arrondi 
au M ieme chiffre précédant la virgule.

Illustrons tout ceci par des exemples

Exemples :

Si on représente le nombre 123 au format NUMBER(3,-1) on aura 120.
Une chose qu'il faut aussi savoir c'est que Ces types n'ont pas tous la même représentation interne :
NUMBER est représenté par un format "flottant" (floating point format) c'est à dire sous la forme mantisse * 2 exposant ; L'exposant est dans l'intervalle [-129,124]. La précision de la mantisse est de 126 bits (environ 37 chiffres decimaux). Les nombres flottants sont représentés de façon approchée.
NUMBER (..) est représenté par un format "fixe" (fixed point format) c'est a dire sous une forme décimale avec la virgule à un endroit fixe du nombre : dndn-1...d1d0,d-1d-2...
Un nombre en format "fixe" est représenté de façon exacte.
Notation des constantes numériques
3.4, -10.3, 1.4e+45 (1.4*10⁴⁵)

2.4.1 Les dates

Le type ici est DATE. Une date est stockée sous la forme d'un nombre représentant siècle, année, mois, jour, heure, minute, seconde . Ce type sert donc également à stocker des heures. Le format par défaut des constantes de type DATE est le suivant :

'DD-MMM-YY' avec D: Day, M : Month, Y : Year

Exemple

'10-APR-85' pour le 10 avril 1985

On peut:
. ajouter un nombre (de jours) à une date, pour avoir une nouvelle date.
. soustraire deux dates pour avoir le nombre de jours les séparant
. comparer deux dates

2.4.2 Les données nulles

Dans une table, une ligne peut avoir des colonnes sans valeur. On dit que la valeur de la colonne est la constante NULL. On peut tester la présence de cette valeur à l'aide des opérateurs

IS NULL et IS NOT NULL

2.4.3 Les conversions de type

Diverses fonctions permettent des changements de type :

TO_NUMBER convertit une chaîne de caractères en nombre
TO_CHAR convertit un nombre ou une date en chaîne de caractères
TO_DATE convertit une chaîne de caractères ou un nombre en date.

2.5 Création d'une table

syntaxe

CREATE TABLE nom_table (nom_colonne1 type_colonne1 contrainte_colonne1, nom_colonne2 type_colonne2 contrainte_colonne2, ...)

L' action consiste à créer la table nom_table avec les colonnes indiquées :
nom_colonnei est le nom de la colonne i à créer dont type_colonnei est le type des données ( par exemple char(30) number(6,2)) et contrainte_colonnei est une contrainte que doit respecter les données de la colonnei. Voici deux contraintes possibles pour la colonnei:

NOT NULL <---- aucune valeur nulle n'est permise dans la colonne.
UNIQUE <---- aucune valeur ne peut apparaître plusieurs fois dans la colonne.

On verra par la suite que d'autres contraintes sont possibles.

Exemple

nous souhaitons créer une table enregistrant les livres vendus par une librairie. Nous lui donnerons la structure suivante :

Name	Type	Contrainte	Signification
TITRE	CHAR(20)	NOT NULL UNIQUE	Titre du livre
AUTEUR	CHAR(15)	NOT NULL	Son auteur
GENRE	CHAR(15)	NOT NULL	Son genre (Roman, Poesie, Policier, BD,...)
ACHAT	DATE	NOT NULL	Date d'achat du livre
PRIX	NUMBER(6,2)	NOT NULL	Son prix
DISPONIBLE	CHAR(1)	NOT NULL	Est-il disponible ? O (oui), N (non)

La table s'appellera BIBLIO. La commande pour la créer est la suivante

SQL> create table biblio -- <---- le SQL est le prompte
2 ( titre char(20) not null unique,
3 auteur char(15) not null,
4 genre char(15) not null,
5 achat date not null,
6 prix number(6,2) not null,
7 disponible char(1) not null);
Table created. -- <---- message du système qui dit que la table est créée

2.6 Afficher la structure d'une table

syntaxe

SQL> describe nom_table -- commande SQLPLUS

L' action consiste à afficher la structure de table

Exemple

SQL> describe biblio
et le résultat de la commande est présenté ci-dessus:

Name	Null?	Type
TITRE	NOT NULL	CHAR(20)
AUTEUR	NOT NULL	CHAR(15)
GENRE	NOT NULL	CHAR(15)
ACHAT	NOT NULL	DATE
PRIX	NOT NULL	NUMBER(6,2)
DISPONIBLE	NOT NULL	CHAR(1)

2.7 Remplissage d'une table

syntaxe:

insert into table [(colonne1, colonne2, ..)] values (valeur1, valeur2, ....)

Comme action on ajoute une ligne (valeur1, valeur2, ..) à la table. Ces valeurs sont affectées aux colonne1, colonne2,... si elles sont présentes, sinon aux colonnes de la table dans l'ordre ou elles ont été définies.

Exemple

Dans l'exemple ci-dessus il faut avoi en-tête que nous somme dans un environnement UNIX ; et de la signification de la commande SQLPLUS host

SQL> edit insert -- <-- construction du fichier de commandes insert
SQL> host cat insert.sql <-- contenu du fichier de commandes insert.sql
SQL>insert into biblio values ('Candide','Voltaire','Essai','18-oct-85',140,'o');
SQL>insert into biblio values ('Les fleurs du mal','Baudelaire','Poeme','01-jan-78',120,'n');
SQL>insert into biblio values ('Tintin au Tibet','Herge','BD','10-nov-90',70,'o');
SQL>insert into biblio values ('Du cote de chez Swann','Proust','Roman','08-dec-78',200,'o');
SQL>insert into biblio values ('La terre','Zola','roman','12-jun-90',50,'n');
SQL>insert into biblio values ('Madame Bovary','Flaubert','Roman','12-mar-88',130,'o');
SQL>insert into biblio values ('Manhattan transfer','Dos Passos','Roman','30-aug-87',320,'o');
SQL>insert into biblio values ('Tintin en Amerique','Herge','BD','15-may-91',70,'o');
SQL> start insert
1 row created.
1 row created.
1 row created.
SQL>insert into biblio values ('Du cote de chez Swann','Proust','Roman','08-dec-78',200,'o')
*
ERROR at line 1: <--- 'Du côte de chez Swann' fait 21 caractères pour une colonne
<--- de 20 positions.
ORA-01401: inserted value too large for column
1 row created.
1 row created.
1 row created.
1 row created.
SQL> select * from biblio; -- <-- affichage du contenu de la table

TITRE	AUTEUR	GENRE	ACHAT	PRIX	D
Candide	Voltaire	Essai	18-OCT-85	140	o
Les fleurs du mal	Baudelaire	Poeme	01-JAN-78	120	n
Tintin au Tibet	Herge	BD	10-NOV-90	70	0
La terre	Zola	roman	12-JUN-90	50	n
Madame Bovary	Flaubert	Roman	12-MAR-88	130	0
Manhattan transfer	Dos Passos	Roman	30-AUG-87	320	0
Tintin en Amerique	Hergé	BD	15-MAY-91	70	0

7 rows selected. -- <-- le système affiche le nombr de lignes de la table

Remarques

1 : Oracle ne tronque pas les chaînes trop grandes pour une colonne. Il les refuse en déclarant une erreur.
2 : A l'affichage, chaque colonne est titrée avec son nom. Celui-ci est tronqué si la place manque.

Exemple :

Pour illustrer ce qui est dit dans la remarque ci-dessus, oracle remplace par "D" le mot DISPONIBLE au moment de l'affichage.

Vérifions qu'une nouvelle insertion est bien faite en fin de table en procédant comme suit:

SQL> host cat insert2.sql -- <-- nouveau fichier de commandes
insert into biblio values ('Du cote de ch. Swann','Proust','Roman','08-dec-78',200,'o');
SQL> start insert2 -- <-- exécution de l'insertion
1 row created.
SQL> select * from biblio; -- <-- contenu de la table

TITRE	AUTEUR	GENRE	ACHAT	PRIX	D
Candide	Voltaire	Essai	18-OCT-85	140	o
Les fleurs du mal	Baudelaire	Poeme	01-JAN-78	120	n
Tintin au Tibet	Herge	BD	10-NOV-90	70	0
La terre	Zola	roman	12-JUN-90	50	n
Madame Bovary	Flaubert	Roman	12-MAR-88	130	0
Manhattan transfer	Dos Passos	Roman	30-AUG-87	320	0
Tintin en Amerique	Hergé	BD	15-MAY-91	70	0
Du coté de ch. Swann	Proust	Roman	08-DEC-78	200	0 <== ligne ajoutée

8 rows selected.

2.8 Consultation de la table

2.8.1 Introduction
La commande SELECT a une syntaxe très riche :

SQL>SELECT [ALL|DISTINCT] [*|expression1 alias1, expression2 alias2, ...]
FROM nom_table

Ici on affiche les valeurs de expression_i pour toutes les lignes de nom_table.expression_i peut être une colonne ou une expression plus complexe. Le symbole * désigne l'ensemble des colonnes. Par défaut, toutes les lignes de nom_table (ceci est introduit par ALL dans la commande) sont affichées. Si DISTINCT est présent, les lignes identiques selectionnées ne sont affichées qu'une fois. Les valeurs de expressioni sont affichées dans une colonne ayant pour titre expression_i ou alias_i si celui-ci a été utilisé.

Remarque

La commande SELECT affiche ses résultats en série. S'il y en a beaucoup, on ne voit que les derniers ... Pour avoir un affichage page par page, il faut utiliser la commande SQLPLUS suivante:

SQL>set pause on

Il faut valider avec la touche Entrée pour démarrer l'affichage. Après chaque page, on peut avoir la suivante par la touche Return ou arrêter par la touche Suppr. L'affichage page par page restera en vigueur tant qu'on n'aura pas émis la commande inverse

SQL>set pause off

Exemples

SQL> select * from biblio; <-- affichage de toutes les colonnes

TITRE	AUTEUR	GENRE	ACHAT	PRIX	D
Candide	Voltaire	Essai	18-OCT-85	140	o
Les fleurs du mal	Baudelaire	Poeme	01-JAN-78	120	n
Tintin au Tibet	Herge	BD	10-NOV-90	70	0
La terre	Zola	roman	12-JUN-90	50	n
Madame Bovary	Flaubert	Roman	12-MAR-88	130	0
Manhattan transfer	Dos Passos	Roman	30-AUG-87	320	0
Tintin en Amerique	Hergé	BD	15-MAY-91	70	0
Du coté de ch. Swann	Proust	Roman	08-DEC-78	200	0 <== ligne ajoutée

8 rows selected.
SQL> select titre, auteur from biblio;

TITRE	AUTEUR
Candide	Voltaire
Les fleurs du mal	Baudelaire
Tintin au Tibet	Herge
La terre	Zola
Madame Bovary	Flaubert
Manhattan transfer	Dos Passos
Tintin en Amerique	Hergé
Du coté de ch. Swann	Proust

8 rows selected.
SQL> select titre,prix from biblio;

TITRE	PRIX
Candide	140
Les fleurs du mal	120
Tintin au Tibet	70
La terre	50
Madame Bovary	130
Manhattan transfer	320
Tintin en Amerique	70
Du coté de ch. Swann	200

8 rows selected.
SQL> select titre TITRE_DU_LIVRE, prix PRIX_ACHAT from biblio;
-- <== utilisation d'alias TITRE_DU_LIVRE et PRIX_ACHAT

TITRE	PRIX
Candide	140
Les fleurs du mal	120
Tintin au Tibet	70
La terre	50
Madame Bovary	130
Manhattan transfer	320
Tintin en Amerique	70
Du coté de ch. Swann	200

8 rows selected.

Nous verrons ultérieurement des commandes SQLPLUS permettant un affichage plus sophistiqué.

2.8.2 Affichage des lignes vérifiant une condition

syntaxe :

 SQL> SELECT ....
WHERE condition

action réalisée :
seules les lignes vérifiant la condition sont affichées

Exemples

SQL> select titre,prix from biblio where prix>100;

TITRE	PRIX
Candide	140
Les fleurs du mal	120
Madame Bovary	130
Manhattan transfer	320
Du cote de ch. Swann	200

SQL> select titre,prix,genre
2 from biblio
3 where genre='Roman';

TITRE	PRIX	GENRE
Madame Bovary	130	Roman
Manhattan transfer	320	Roman
Du cote de ch. Swann	200	Roman

Un des livres a le genre 'roman' et non 'Roman'. Nous utilisons la fonction upper qui transforme une chaîne de caractères en majuscules pour avoir tous les romans.

SQL>
1 select titre,prix,genre
2 from biblio
3 where upper(genre)='ROMAN'

TITRE	PRIX	GENRE
La terre	50	roman
Madame Bovary	130	Roman
Manhattan transfer	320	Roman
Du côté de ch. Swann	200	Roman

Nous pouvons réunir des conditions par les opérateurs logiques AND qui est le "ET logique", OR le "OU logique" et NOT la "Negation logique" :

SQL> 
 SQL>
1 select titre,prix,genre
2 from biblio
3 where upper(genre)='ROMAN'
4 and prix<100;

TITRE	PRIX	GENRE
La terre	50	roman

SQL> select titre,genre from biblio;

TITRE	GENRE
Candide	Essai
Les fleurs du mal	Poeme
Tintin au Tibet	BD
La terre	roman
Madame Bovary	Roman
Manhattan transfer	Roman
Tintin en Amerique	BD
Du cote de ch. Swann	Roman

8 rows selected.

 SQL> 
1 select titre,genre from biblio 
2 where upper(genre)='ROMAN' or upper(genre)='BD'

TITRE	GENRE
Tintin au Tibet	BD
La terre	Roman
Madame Bovary	Roman
Manhattan transfer	Roman
Tintin en Amerique	BD
Du cote de ch. Swann	Roman

6 rows selected.
SQL>
1 select titre,genre from biblio
2 where NOT( upper(genre)='ROMAN' or upper(genre)='BD')

TITRE	GENRE
Candide	Essai
Les fleurs du mal	Poeme

2 rows selected.
SQL> select titre,achat from biblio;

TITRE	ACHAT
Candide	18-OCT-85
Les fleurs du mal	01-JAN-78
Tintin au Tibet	10-NOV-90
La terre	12-JUN-90
Madame Bovary	12-MAR-88
Manhattan transfer	30-AUG-87
Tintin en Amerique	15-MAY-91
Du cote de ch. Swann	08-DEC-78

8 rows selected.
SQL>
1 select titre,achat from biblio
2 where achat>'31-dec-87'

TITRE	ACHAT
Tintin au Tibet	10-NOV-90
La terre	12-JUN-90
Tintin en Amerique	15-MAY-91

SQL> select titre,prix from biblio
2 where prix between 100 and 150; <-- prix dans l'intervalle [100..150]

TITRE	PRIX
Madame Bovary	136.5

2.8.3 Affichage des lignes selon un ordre détérmine

Aux syntaxes précédentes, il est possible d'ajouter une clause indiquant l'ordre d'affichage désiré :

syntaxe SELECT ....
ORDER BY expression1 [asc|desc], expression2 [asc|dec], ...

action:
Les lignes résultat de la selection sont affichées dans l'ordre de
1 : ordre croissant (asc qui est la valeur par défaut) ou décroissant (desc) de expression1
2 : en cas d'égalité de expression1, l'affichage se fait selon les valeurs de expression2
etc ..

Exemples

SQL> select titre, genre,prix,achat from biblio
2 order by achat desc; <-- ordre decroissant des dates d'achat
TITRE GENRE PRIX ACHAT
----------------------------------------------
Tintin en Amerique BD 70 15-MAY-91
Introduction au langage SQL 15
Tintin au Tibet BD 70 10-NOV-90
La terre roman 50 12-JUN-90
Madame Bovary Roman 130 12-MAR-88
Manhattan transfer Roman 320 30-AUG-87
Candide Essai 140 18-OCT-85
Du cote de ch. Swann Roman 200 08-DEC-78
Les fleurs du mal Poeme 120 01-JAN-78
8 rows selected.
SQL>
1 select titre, genre,prix,achat from biblio
2 order by prix; <-- ordre croissant (par defaut) des prix
TITRE GENRE PRIX ACHAT
-------------------------------------------------
La terre roman 50 12-JUN-90
Tintin au Tibet BD 70 10-NOV-90
Tintin en Amerique BD 70 15-MAY-91
Les fleurs du mal Poeme 120 01-JAN-78
Madame Bovary Roman 130 12-MAR-88
Candide Essai 140 18-OCT-85
Du cote de ch. Swann Roman 200 08-DEC-78
Manhattan transfer Roman 320 30-AUG-87
8 rows selected.
SQL>
1 select titre, genre,prix,achat from biblio
2 order by genre desc; <-- ordre decroissant du genre
TITRE GENRE PRIX ACHAT
------------------------------------------------
La terre roman 50 12-JUN-90
Madame Bovary Roman 130 12-MAR-88
Du cote de ch. Swann Roman 200 08-DEC-78
Manhattan transfer Roman 320 30-AUG-87
Les fleurs du mal Poeme 120 01-JAN-78
Candide Essai 140 18-OCT-85
Tintin au Tibet BD 70 10-NOV-90
Tintin en Amerique BD 70 15-MAY-91
8 rows selected.
SQL >
1 select titre, genre,prix,achat from biblio
2 order by genre desc, prix; <--- tri double critere
<-- 1 : ordre decroissant du genre
<-- 2 : ordre croissant du prix
TITRE GENRE PRIX ACHAT
--------------------------------------------
La terre roman 50 12-JUN-90
Madame Bovary Roman 130 12-MAR-88
Du cote de ch. Swann Roman 200 08-DEC-78
Manhattan transfer Roman 320 30-AUG-87
Les fleurs du mal Poeme 120 01-JAN-78
Candide Essai 140 18-OCT-85
Tintin au Tibet BD 70 10-NOV-90
Tintin en Amerique BD 70 15-MAY-91
8 rows selected.
SQL>
1 select titre, genre,prix,achat from biblio
2 order by genre desc, prix desc; <-- tri double critere
<-- 1 : ordre decroissant du genre
<-- 2 : ordre decroissant du prix
TITRE GENRE PRIX ACHAT
------------------------------------------
La terre roman 50 12-JUN-90
Manhattan transfer Roman 320 30-AUG-87
Du cote de ch. Swann Roman 200 08-DEC-78
Madame Bovary Roman 130 12-MAR-88
Les fleurs du mal Poeme 120 01-JAN-78
Candide Essai 140 18-OCT-85
Tintin au Tibet BD 70 10-NOV-90
Tintin en Amerique BD 70 15-MAY-91
8 rows selected.

2.9 Suppression de lignes dans une table

syntaxe:

DELETE FROM table [WHERE condition]

On supprime les lignes de table vérifiant condition. Si cette dernière est absente, toutes les lignes sont détruites.

Exemples

SQL> select titre from biblio; <-- liste des titres
TITRE
--------------------
Candide
Les fleurs du mal
Tintin au Tibet
La terre
Madame Bovary
Manhattan transfer
Tintin en Amerique
Du cote de ch. Swann
8 rows selected.

SQL> DELETE from biblio where titre='Candide';
1 row DELETEd.
SQL> select titre from biblio; <-- verification
TITRE
--------------------
Les fleurs du mal
Tintin au Tibet
La terre
Madame Bovary
Manhattan transfer
Tintin en Amerique
Du cote de ch. Swann
7 rows selected. <-- une ligne a été supprimee

2.10 Modification du contenu d'une table

syntaxe:

 update table set colonne1 = expression1, colonne2 = expression2, ...
[where condition]

Action:
Pour les lignes de table vérifiant condition (toutes les lignes s'il n'y a pas de condition), colonnei reçoit la valeur expression i.

Exemples

SQL> select genre from biblio;
GENRE
---------------
Poeme
BD
roman
Roman
Roman
BD
Roman
7 rows selected.
On met tous les genres en majuscules :

SQL> update biblio set genre=upper(genre);
7 rows updated.
SQL> select genre from biblio; <--- verification
GENRE
---------------
POEME
BD
ROMAN
ROMAN
ROMAN
BD
ROMAN
7 rows selected.

SQL> select genre,prix from biblio;
GENRE PRIX
-----------------
POEME 120
BD 70
ROMAN 50
ROMAN 130
ROMAN 320
BD 70
ROMAN 200
7 rows selected.
Le prix des romans augmente de 5% :
SQL> update biblio set prix=prix*1.05 where genre='ROMAN';
4 rows updated.
SQL> select genre,prix from biblio; <-- verification
GENRE PRIX 
------------------
POEME 120
BD 70
ROMAN 52.5
ROMAN 136.5
ROMAN 336
BD 70
ROMAN 210
7 rows selected.

2.11 Mise à jour définitive d'une table

Lorsqu'on apporte des modifications à une table, Oracle les génère en fait sur une copie de la table. Elles peuvent-être alors rendues définitives ou bien être annulées par les commandes COMMIT et ROLLBACK.

syntaxe

 SQL>COMMIT

Action:
La commande COMIT rend définitives les mises à jour faites sur les tables depuis le dernier COMMIT.

syntaxe:

 SQL>ROLLBACK

Action :
La commande ROLLBACKannule toutes modifications faites sur les tables depuis le dernier COMMIT.

Remarques

Un COMMIT est fait implicitement aux moments suivants :

	a) A la deconnexion d'Oracle
	b) Après chaque commande affectant la structure des tables : CREATE, ALTER, DROP.

Exemples

SQL> select titre from biblio;<-- liste des titres
TITRE
--------------------
Les fleurs du mal
Tintin au Tibet
Introduction au langage SQL 18
La terre
Madame Bovary
Manhattan transfer
Tintin en Amerique
Du cote de ch. Swann
7 rows selected.
SQL> commit; <-- sauvegarde de l'état actuel de la base
Commit complété.
Suppression d'un titre
SQL> DELETE from biblio where titre='La terre';
1 row DELETEd.
SQL> select titre from biblio; <-- verification
TITRE
--------------------
Les fleurs du mal
Tintin au Tibet
Madame Bovary
Manhattan transfer
Tintin en Amerique
Du cote de ch. Swann
6 rows selected. <--- le titre 'La terre' n'est plus present
SQL> rollback; <-- retour a l'etat de la base lors du dernier commit.
Rollback complété.
SQL> select titre from biblio; <-- Verification
TITRE
--------------------
Les fleurs du mal
Tintin au Tibet
La terre <-- Le titre 'La terre' est recupere
Madame Bovary
Manhattan transfer
Tintin en Amerique
Du cote de ch. Swann
7 rows selected. <-- on est bien revenu a l'etat anterieur.
SQL> select prix from biblio;
PRIX
----------
120
70
52.5
136.5
336
70
210
7 rows selected.
Tous les prix sont mis a zero.
SQL> update biblio set prix=0;
7 rows updated.
SQL> select prix from biblio; <-- verification
PRIX
----------
0
0
0
0
0
0
0
7 rows selected.
SQL> rollback; <-- retour au dernier etat valide de la base.
Rollback complété.
SQL> select prix from biblio; <-- verification
PRIX
----------
120 <-- on est bien revenu a l'etat anterieur
70
52.5
136.5
336
70
210
7 rows selected.

2.12 Mise en forme de l'affichage à l'écran

Il existe diverses commandes SQLPLUS (et non SQL) permettant le contrôle de l'affichage à l'écran.
2.12.1 Contrôle de l'affichage des colonnes
Titre de la colonne

syntaxe :

  SQL> COLUMN nom_colonne HEADING titre_colonne
Action :
 donne le titre titre_colonne a la colonne indiquée.

Exemples

 SQL> select titre,prix from biblio;
TITRE PRIX
-------------------- ----------
Les fleurs du mal 120
Tintin au Tibet 70
La terre 52.5
Madame Bovary 136.5
Manhattan transfer 336
Tintin en Amerique 70
Du cote de ch. Swann 210
7 rows selected.
 SQL> column titre heading 'Titre du livre' <-- on donne un titre a la colonne titre
 SQL> column prix heading 'Prix d''achat' <-- ainsi qu'a la colonne prix
 SQL> select titre,prix from biblio; <-- verification
Titre du livre Prix d'achat
-------------------- ------------
Les fleurs du mal 120
Tintin au Tibet 70
La terre 52.5
Madame Bovary 136.5
Manhattan transfer 336
Tintin en Amerique 70
Du cote de ch. Swann 210
7 rows selected.

2.12.2 Format d'une colonne numérique ou texte

syntaxe

 
COLUMN nom_colonne FORMAT format

action :
La colonne indiquée est formatée au format indique. Différents formats existent pour les divers types de colonnes :

Colonne de texte : Le format An permet d'afficher les valeurs sur n positions.
Colonne de nombres
format signification
9999 nombres cadres sur 4 positions
0999 idem avec affichage des zeros de tété
9990 idem - affiche de plus 0 au lieu d'un blanc si la valeur est nulle
$9999 fait preceder la valeur par $
B9999 affiche une valeur nulle comme un blanc
9999MI affiche - derriere une valeur negative
9999PR affiche les valeurs negatives entre parentheses
9,999 une virgule sera placée a la position indiquee
99.99 un point decimal sera place a l'endroit indique
9.99EEEE la valeur est affichee en notation scientifique

Exemples

SQL> column prix format 9999.99
SQL> select prix from biblio;
Prix d'achat
------------
120.00
70.00
52.50
136.50
336.00
70.00
210.00
7 rows selected.
SQL> column prix format 0999.99
SQL> select prix from biblio
Prix d'achat
------------
0120.00
0070.00
0052.50
0136.50
0336.00
0070.00
0210.00
7 rows selected.
SQL> column prix format 999.99EEEE
SQL> select prix from biblio;
Prix d'achat
------------
1.20E+02
7.00E+01
5.25E+01
1.37E+02
3.36E+02
7.00E+01
2.10E+02
7 rows selected.
SQL> column titre format A20
SQL> select titre from biblio;
Introduction au langage SQL 21
Titre du livre
--------------------
Les fleurs du mal
Tintin au Tibet
La terre
Madame Bovary
Manhattan transfer
Tintin en Amerique
Du cote de ch. Swann
7 rows selected.
SQL> column titre format A15
SQL> select titre from biblio;
Titre du livre
---------------
Les fleurs du m <-- les titres sont affiches a raison de 15 caractères par ligne.
al
Tintin au Tibet
La terre
Madame Bovary
Manhattan trans
fer
Tintin en Ameri
que
Du cote de ch.
Swann
7 rows selected.

2.12.3 Affichage d'une colonne de dates

Le format implicite d'affichage d'une date est le format A9 : DD-MON-YY. Nous ne pouvons le changer. Ce qui est possible, c'est d'utiliser une fonction de conversion de la date en chaîne de caractères :

definition TO_CHAR(date,format)
parametres date : la date a convertir
format : le format de conversion

résultat une date sous forme de chaîne de caractères.
Il existe de nombreux formats de conversion. Voici quelques-uns des éléments qui peuvent le composer :
élément Signification:
YYYY, YYY, YY, Y Les 4 chiffres de l'annee, ou les 3 derniers chiffres, ou les 2 derniers, ou le dernier

YEAR Annee en toutes lettres
MM Nº du mois [1..12].
MONTH Nom du mois sur 9 positions
MON Nom du mois abrege aux 3 premieres lettres.
WW N¢X de la semaine dans l'annee [1..52].
DDD Jour de l'annee [1..366]
DD Jour du mois [1..31]
D Jour de la semaine [1..7]
DAY Nom du jour
DY Nom du jour abrege aux 3 premieres lettres.
"texte" texte sera place tel quel dans la date

Nous donnons dans la suite divers exemples de formatage de date. Nous utilisons pour cela :
la fonction SYSDATE Elle donne la date du jour. Le résultat est du type date. la table DUAL C'est une table particuliere definie sous Oracle et qui permet l'utilisation de la commande SELECT lorsqu'on n'a pas de colonne de table a afficher.

Exemples

SQL > select sysdate from dual;
SYSDATE
--------------------------------------------------------------------------
16-OCT-91
SQL> select to_char(sysdate,'DD MONTH YYYY') from dual;
TO_CHAR(SYSDATE,'DDMONTHYYYY')
---------------------------------------------------------------------------
16 OCTOBER 1991
SQL> select to_char(sysdate,'DD/MM/YY') from dual;
TO_CHAR(SYSDATE,'DD/MM/YY')
---------------------------------------------------------------------------
16/10/91
SQL> select to_char(sysdate,'Day DD MONTH YYYY') from dual;
TO_CHAR(SYSDATE,'DAYDDMONTHYYYY')
---------------------------------------------------------------------------
Wednesday 16 OCTOBER 1991
SQL> columnn achat format A8 <-- colonne achat sur 8 positions
SQL> select titre, to_char(achat,'DD/MM/YY') ACHAT from biblio;
TITRE ACHAT
---------------------------------------------------------------------------
Les fleurs du mal 01/01/78
Tintin au Tibet 10/11/90
La terre 12/06/90
Madame Bovary 12/03/88
Manhattan transfer 30/08/87
Tintin en Amerique 15/05/91
Du cote de ch. Swann 08/12/78
7 rows selected.

2.13 création d'une table a partir d'une autre table

On utilise ici une variante de CREATE dont la syntaxe :

 CREATE TABLE table2
AS SELECT colonne1, colonne2, .... FROM table1 [WHERE condition]

action :
crée table2 avec pour structure colonne1, colonne2, ... de table1. Par ailleurs table2 reçoit les lignes de table1 vérifiant condition.

Exemples

SQL> create table romans as <-- on cree la table romans
2 select * from biblio <-- avec la structure de la table biblio
3 where genre='ROMAN'; <-- en ne conservant que les romans
Table created.
SQL> describe romans <-- verification de la structure de la table romans
Name 	Null? 				Type
-------------------------------
TITRE 	NOT NULL 			CHAR(20)
AUTEUR 	NOT NULL 			CHAR(15)
GENRE 	NOT NULL 			CHAR(15)
ACHAT 	NOT NULL 			DATE
PRIX 	NOT NULL 			NUMBER(6,2)

DISPONIBLE NOT NULL CHAR(1)
SQL> select titre,genre from romans; <-- Qu'y-a-t-il dans la table ?
TITRE 					GENRE
-----------------------------------------------------------------------------
La terre 				ROMAN <-- Que des romans
Madame Bovary 			ROMAN
Manhattan transfer 		ROMAN
Du cote de ch. Swann 	ROMAN
SQL> create table cheap as <-- création de la table cheap
2 select titre,auteur,prix from biblio <-- avec pour colonnes titre, auteur,prix
<-- de biblio
3 where prix<100; <-- n'enregistrant que les livres d'un prix
<-- inferieur a 100 francs
Table created.
SQL> describe cheap <-- verification de la structure de la table cheap
Name 			Null? 				Type
-------------------------------------------------------------------------------
TITRE 			NOT NULL 			CHAR(20)
AUTEUR 			NOT NULL 			CHAR(15)
PRIX 			NOT NULL 			NUMBER(6,2)
SQL> select * from cheap; <-- contenu de la table cheap
TITRE AUTEUR PRIX
---------------------------------------------------------------------------------
Tintin au Tibet Herge 70 <-- que des prix inferieurs a 50 francs
La terre Zola 52.5
Tintin en Amerique Herge 70
SQL> create table vide as <-- on cree une table appelee vide
2 select * from biblio <-- qui a la structure de la table biblio
3 where 1=2; <-- mais qui sera vide (condition 1=2 toujours fausse)
Table created.
SQL> describe vide <--- structure de la table vide
Name 			Null? 		Type
---------------------------------------------------------------------------------
TITRE 			NOT NULL 	CHAR(20)
AUTEUR 			NOT NULL 	CHAR(15)
GENRE 			NOT NULL 	CHAR(15)
ACHAT 			NOT NULL 	DATE
PRIX 			NOT NULL 	NUMBER(6,2)
DISPONIBLE 		NOT NULL 	CHAR(1)
SQL> select * from vide; <-- contenu de la table vide
no rows selected <-- elle est vide

2.14 Obtenir la liste des tables creees

Oracle enregistre des informations sur les tables créées par un utilisateur dans une table (une vue en fait) appelée TABS dont la structure est la suivante :

SQL> describe tabs

Name 				Null? 			Type
-----------------------------------------
TABLE_NAME 			NOT NULL 		CHAR(30)
TABLESPACE_NAME 		NOT NULL 		CHAR(30)
CLUSTER_NAME 			NOT NULL 		CHAR(30)
PCT_FREE 			NOT NULL 		NUMBER
PCT_USED 			NOT NULL 		NUMBER
INI_TRANS 			NOT NULL 		NUMBER
MAX_TRANS 			NOT NULL 		NUMBER
INITIAL_EXTENT 						NUMBER
NEXT_EXTENT 						NUMBER
MIN_EXTENTS 			NOT NULL		NUMBER
MAX_EXTENTS 			NOT NULL 		NUMBER
PCT_INCREASE 			NOT NULL 		NUMBER
BACKED_UP CHAR(1)

Nous n'entrerons pas dans les détails de cette structure. Seulement nous remarquons que le nom des tables est enregistré dans la colonne table_name. Nous obtenons donc la liste des tables créées par la commande :

select table_name from tabs

Exemple

SQL> select table_name from tabs;
TABLE_NAME
------------------------------------------
BIBLIO
CHEAP
ROMANS
VIDE
4 rows selected.

2.15 Ajout de lignes dans une table en provenance d'une autre table

Il est possible d'ajouter des lignes d'une table à une autre table lorsque leurs structures sont compatibles :

syntaxe INSERT INTO table1 [(colonne1, colonne2, ...)]
SELECT colonnea, colonneb, ... FROM table2 WHERE condition

action:
Les lignes de table2 verifiant condition sont ajoutees a table1. Les colonnes colonnea, colonneb, .... de table2 sont affectees dans l'ordre a colonne1, colonne2, ... de table1 et doivent doncêtre de type compatible.

Exemple

select * from biblio;
TITRE AUTEUR GENRE ACHAT PRIX D
------------------------------------------------------------------
Les fleurs du mal Baudelaire POEME 01-JAN-78 60 n
Tintin au Tibet Herge BD 10-NOV-90 70 o
La terre Zola ROMAN 12-JUN-90 52.5 n
Madame Bovary Flaubert ROMAN 12-MAR-88 136.5 o
Manhattan transfer Dos Passos ROMAN 30-AUG-87 336 o
Tintin en Amerique Herge BD 15-MAY-91 70 o
Du cote de ch. Swann Proust ROMAN 08-DEC-78 210 o
Le pere Goriot Balzac ROMAN 01-SEP-91 200 o
8 rows selected.
SQL> create table biblio2 as select * from biblio where disponible='n'
Table created.
SQL> select * from biblio2;
TITRE AUTEUR GENRE ACHAT PRIX D
---------------------------------------------------------------
Les fleurs du mal Baudelaire POEME 01-JAN-78 60 n
La terre Zola ROMAN 12-JUN-90 52.5 n
SQL> insert into biblio2 select * from biblio where disponible='o';
6 rows created.
SQL> select * from biblio2;
TITRE AUTEUR GENRE ACHAT PRIX D
---------------------------------------------------------------
Les fleurs du mal Baudelaire POEME 01-JAN-78 60 n
La terre Zola ROMAN 12-JUN-90 52.5 n
Tintin au Tibet Herge BD 10-NOV-90 70 o
Madame Bovary Flaubert ROMAN 12-MAR-88 136.5 o
Manhattan transfer Dos Passos ROMAN 30-AUG-87 336 o
Introduction au langage SQL 25
Tintin en Amerique Herge BD 15-MAY-91 70 o
Du cote de ch. Swann Proust ROMAN 08-DEC-78 210 o
Le pere Goriot Balzac ROMAN 01-SEP-91 200 o
8 rows selected.
SQL> drop table biblio2;
Table dropped.

2.16 Changer le nom d'une table

syntaxe

RENAME table1 TO table2

action :
change la nom table1 en table2

Exemples

SQL> select table_name from tabs; <-- liste des tables
TABLE_NAME
------------------------------
ARTICLES <-- tables existantes
BIBLIO
CLIENTS
CMD_ID
COMMANDES
SAUVEGARDE
STOCKS
7 rows selected.
SQL> rename biblio to livres; <-- la table biblio devient la table livres
Table renamed.
SQL> select table_name from tabs; <-- liste des tables
TABLE_NAME
------------------------------
ARTICLES
CLIENTS
CMD_ID
COMMANDES
LIVRES <------
SAUVEGARDE
STOCKS
7 rows selected.
SQL> rename livres to biblio; <-- la table livres redevient la table biblio
Table renamed.
SQL> select table_name from tabs; <-- liste des tables
TABLE_NAME
------------------------------
ARTICLES
BIBLIO <----
CLIENTS
CMD_ID
COMMANDES
SAUVEGARDE
STOCKS
7 rows selected.

2.17 Suppression d'une table

syntaxe

DROP TABLE table <== supprime table

Exemples

SQL> select table_name from tabs;
TABLE_NAME
------------------------------
BIBLIO
CHEAP
ROMANS
VIDE
SQL> drop table romans;
Table dropped.
SQL> drop table cheap;
Table dropped.
SQL> drop table vide;
Table dropped.
SQL> select table_name from tabs; <-- verification
TABLE_NAME
------------------------------
BIBLIO <-- il ne reste que biblio
1 row selected.

2.18 Modification de la structure d'une table

syntaxe ALTER TABLE table
[ ADD (nom_colonne1 type_colonne1 contrainte_colonne1, 
nom_colonne2 type_colonne2 contrainte_colonne2, ...)]
[MODIFY (nom_colonnea type_colonnea contrainte_colonnea, 
nom_colonneb type_colonneb contrainte_colonneb, ...)]

action:
permet d'ajouter (ADD) ou de modifier (MODIFY) des colonnes de table. La syntaxe nom_colonnei type_colonnei contrainte_colonnei est celle du CREATE TABLE.

Exemple

SQL> alter table biblio
2 add (nbpages number(4)) <-- on ajoute une colonne
3 modify (genre char(20)); <-- on en modifie une autre
Table altered.
SQL> describe biblio <-- verification
Name Null? Type
---------------------------------------
TITRE NOT NULL CHAR(20)
AUTEUR NOT NULL CHAR(15)
GENRE NOT NULL CHAR(20) <--- a change
ACHAT NOT NULL DATE
PRIX NOT NULL NUMBER(6,2)
DISPONIBLE NOT NULL CHAR(1)
NBPAGES NUMBER(4) <--- a été cree
SQL> select titre,genre,nbpages from biblio; <-- contenu de biblio
TITRE GENRE NBPAGES
--------------------------------------
Les fleurs du mal POEME
Tintin au Tibet BD
La terre ROMAN
Madame Bovary ROMAN
Manhattan transfer ROMAN
Tintin en Amerique BD
Du cote de ch. Swann ROMAN
7 rows selected.
SQL> alter table biblio
2 modify (titre char(15)); <-- on diminue la largeur de la colonne titre
modify (titre char(15))
*
ERROR at line 2:
ORA-01441: column to be modified must be empty to decrease column length

L'exemple précédent montre qu'on ne peut diminuer la largeur d'une colonne si celle-ci contient déjà des valeurs.

2.19 Les vues

Il est possible d'avoir une vue partielle d'une table ou de plusieurs tables. Une vue se comporte comme une table mais ne contient pas de données. Ses données sont extraites d'autres tables ou vues. Une vue comporte plusieurs avantages :
1 Un utilisateur peut n'etre interesse que par certaines colonnes et certaines lignes d'une table donnée. La vue lui permet de ne voir que ces lignes et ces colonnes.
2 Le proprietaire d'une table peut desirer n'en autoriser qu'un acces limite, a d'autres utilisateurs. La vue lui permet de le faire. Les autres utilisateurs qu'il aura nommes n'auront acces qu'a la vue qu'il aura definie.

2.19.1 création d'une vue

syntaxe CREATE VIEW nom_vue
AS SELECT colonne1, colonne2, ... FROM table WHERE condition
[ WITH CHECK OPTION ]

action :
cree la vue nom_vue. Celle-ci est une table ayant pour structure colonne1, colonne2, ... de table et pour lignes, les lignes de table verifiant condition (toutes les lignes s'il n'y a pas de condition)
"WITH CHECK OPTION" Cette clause optionnelle indique que les insertions et les mises à jour sur la vue, ne doivent pas créer de lignes que la vue ne pourrait selectionner.

Remarque

La syntaxe de CREATE VIEW est en fait plus complexe que celle presentee et permet notamment de creer une vue a partir de plusieurs tables. Il suffit pour cela que la Requête SELECT porte sur plusieurs tables (cf chapitre suivant).

Exemples

On cree a partir de la table biblio, une vue ne comportant que les romans (selection de lignes) et que les colonnes titre, auteur, prix (selection de colonnes) :

SQL> create view romans as select titre,auteur,prix
2 from biblio where genre='ROMAN';
View created.
SQL> select * from romans; <-- contenu de la vue
TITRE AUTEUR PRIX
---------------------------------------------
La terre Zola 52.5
Madame Bovary Flaubert 136.5
Manhattan transfer Dos Passos 336
Du cote de ch. Swann Proust 210
SQL> describe romans
Name Null? Type
-------------------------------------------
TITRE NOT NULL CHAR(20)
AUTEUR NOT NULL CHAR(15)
PRIX NOT NULL NUMBER(6,2)
SQL> insert into biblio<-- on ajoute une ligne a la table biblio
2 values ('Le pere Goriot','Balzac','Roman','01-sep-91',200,'o');
1 row created.
SQL> select * from romans; <-- le nouveau titre est-il dans les romans ?

TITRE AUTEUR PRIX
--------------------------------------------
La terre Zola 52.5
Madame Bovary Flaubert 136.5
Manhattan transfer Dos Passos 336
Du cote de ch. Swann Proust 210
<-- il n'y est pas parce que le genre est Roman
<--et non ROMAN comme l'exige la vue creee
<-- on met les genres en majuscules
SQL> update biblio set genre=upper(genre);
8 rows updated.
SQL> select * from romans;
TITRE AUTEUR PRIX
---------------------------------------------
La terre Zola 52.5
Madame Bovary Flaubert 136.5
Manhattan transfer Dos Passos 336
Du cote de ch. Swann Proust 210
Le pere Goriot Balzac 200<-- le livre ajoute est là

Des exemples précédents, on voit un avantage de la vue par rapport a la table secondaire lorsqu'on veut filtrer une table principale. Rappelons les deux méthodes :

1 create table romans as select titre, auteur, prix from biblio where genre='ROMAN'
2 create view romans as select titre, auteur, prix from biblio where genre='ROMAN'

La méthode 1 crée une table qui devient obsoléte des qu'un nouveau roman est ajouté à la table biblio. La méthode 2 cree une vue qui elle, est toujours à jour.

2.19.2 Mise a jour d'une vue

Il est possible de mettre à jour une vue comme on le fait pour une table. Toutes les tables d'où sont extraites les données de la vue sont affectées par cette mise à jour.

Exemples

SQL> DELETE from romans where prix=210; <-- suppression de lignes dans la vue
1 row DELETEd.
SQL> select * from romans; <-- verification
TITRE AUTEUR PRIX
----------------------------------------
La terre Zola 52.5
Madame Bovary Flaubert 136.5
Manhattan transfer Dos Passos 336
Le pere Goriot Balzac 200
SQL> select * from biblio; <-- impact sur biblio
TITRE AUTEUR GENRE ACHAT PRIX D
---------------------------------------
Les fleurs du mal Baudelaire POEME 01-JAN-78 60 n
Tintin au Tibet Herge BD 10-NOV-90 70 o
La terre Zola ROMAN 12-JUN-90 52.5 n
Madame Bovary Flaubert ROMAN 12-MAR-88 136.5 o
Manhattan transfer Dos Passos ROMAN 30-AUG-87 336 o
Tintin en Amerique Herge BD 15-MAY-91 70 o
Le pere Goriot Balzac ROMAN 01-SEP-91 200 o
7 rows selected.
SQL> update romans set prix=prix*1.05; <-- modification de tous les prix de la vue
4 rows updated.
SQL> select * from romans; <-- verification
TITRE AUTEUR PRIX
---------------------------------------------------------
La terre Zola 55.13
Madame Bovary Flaubert 143.33
Manhattan transfer Dos Passos 352.8
Le pere Goriot Balzac 210

SQL> select * from biblio; <-- impact sur biblio
TITRE AUTEUR GENRE ACHAT PRIX D
---------------------------------------------------------
Les fleurs du mal Baudelaire POEME 01-JAN-78 60 n
Tintin au Tibet Herge BD 10-NOV-90 70 o
La terre Zola ROMAN 12-JUN-90 55.13 n
Madame Bovary Flaubert ROMAN 12-MAR-88 143.33 o
Manhattan transfer Dos Passos ROMAN 30-AUG-87 352.8 o
Tintin en Amerique Herge BD 15-MAY-91 70 o
Le pere Goriot Balzac ROMAN 01-SEP-91 210 o
7 rows selected.

2.19.3 Obtenir la liste des vues

La table système user_views contient des informations sur toutes les vues créées par l'utilisateur. Sa structure est la suivante :

SQL> describe user_views
Name Null? Type
--------------------------------------------------------
VIEW_NAME NOT NULL CHAR(30) <-- nom de la vue
TEXT_LENGTH NUMBER
TEXT LONG
On obtient donc la liste des vues par la commande :
select view_name from user_views
SQL> select view_name from user_views;
VIEW_NAME
---------------------------------------------------------
ROMANS

On peut utiliser le concept de vue pour simplifier cette commande :

SQL> create view vues as select view_name from user_views;
<--- la vue nommee vues ne contient que la colonne view_name
<--- de la table user_views
View created.
SQL> select * from vues; <-- donne le nom des vues créées
VIEW_NAME
-----------------------------------------------------
ROMANS
VUES

2.19.4 Supprimer une vue

syntaxe DROP VIEW nom_vue

action :
supprime la vue nommee

Exemple

SQL> drop view romans;
View dropped.
SQL> select * from vues; <-- affiche les vues creees
VIEW_NAME
-------------------------------------------------------------------------------
VUES <-- la vue romans n'existe plus

2.20 Utilisation de fonctions de groupes

Il existe des fonctions qui au lieu de travailler sur chaque ligne d'une table, travaillent sur des groupes de lignes. Ce sont essentiellement des fonctions statistiques nous permettant d'avoir la moyenne, l'ecart-type, etc ... des données d'une colonne.

syntaxe1 SELECT f1, f2, .., fn FROM table
[ WHERE condition ]

action:
calcule les fonctions statistiques fi sur l'ensemble des lignes de table verifiant l'eventuelle condition.

syntaxe2 SELECT f1, f2, .., fn FROM table
[ WHERE condition ]
[ GROUP BY expr1, expr2, ..]

action :
Le mot clé GROUP BY a pour effet de diviser les lignes de table par groupe. Chaque groupe contient les lignes pour lesquelles les expressions expr1, expr2, ... ont même valeur.

Exemple

GROUP BY genre

met dans un même groupe, les livres ayant le même genre. La clause GROUP BY auteur,genre mettrait dans le même groupe les livres ayant même auteur et même genre. Le WHERE condition elimine d'abord de la table les lignes ne vérifiant pas condition. Ensuite les groupes sont formes par la clause GROUP BY. Les fonctions fi sont ensuite calculées pour chaque groupe de lignes.

syntaxe3 SELECT f1, f2, .., fn FROM table
[ WHERE condition ]
[ GROUP BY expression]
[ HAVING condition_de_groupe]

action La clause HAVING filtre les groupes formes par la clause GROUP BY. Elle est donc toujours liée à la présence de cette clause GROUP BY.

Exemple

GROUP BY genre HAVING genre!='ROMAN'
Les fonctions statistiques fi disponibles sont les suivantes :

AVG(expression) moyenne de expression
COUNT(expression) nombre de lignes pour lesquelles expression a une valeur
COUNT(*) nombre total de lignes dans la table
MAX(expression) max de expression
MIN(expression) min de expression
STDDEV(expression) ecart-type de expression
SUM(expression) somme de expression
VARIANCE(expression) variance de expresion

Exemples

SQL> select prix from biblio;
PRIX
--------------------------------------------------------------------------------
120
70
52.5
136.5
336
70
210
200
8 rows selected.
SQL> select avg(prix), max(prix), min (prix) from biblio;
AVG(PRIX) MAX(PRIX) MIN(PRIX)
----------------------------------------------------------------------------------
149.375 336 52.5
SQL> select titre, prix,genre from biblio;
TITRE PRIX GENRE
---------------------------------------------------------------------------------
Les fleurs du mal 120 POEME
Tintin au Tibet 70 BD
La terre 52.5 ROMAN
Madame Bovary 136.5 ROMAN
Manhattan transfer 336 ROMAN
Tintin en Amerique 70 BD
Du cote de ch. Swann 210 ROMAN
Le pere Goriot 200 ROMAN
8 rows selected.
SQL> select avg(prix) moyenne, stddev(prix) ecart_type
2 , max(prix) prix_maxi from biblio
3 where genre='ROMAN'; <-- on ne s'interesse qu'aux romans
MOYENNE ECART_TYPE PRIX_MAXI
----------------------------------------------------------
187 104.330604 336
SQL> select count(*) from biblio
2 where genre='BD'; <-- Combien y-a-t-il de BD ?
COUNT(*)
----------------------------------------------------------
2
SQL> select count(*) from biblio where genre='ROMAN'
2 and prix<100; <-- Combien de romans a moins de 100 francs
COUNT(*)
----------
1
SQL> select genre,prix from biblio;
GENRE PRIX
-------------------------------------------------------------
POEME 60
BD 70
ROMAN 52.5
ROMAN 136.5
ROMAN 336
BD 70
ROMAN 210
ROMAN 200
8 rows selected.
SQL> select genre,avg(prix),count(*) from biblio
2 group by genre;
GENRE AVG(PRIX) COUNT(*)
-----------------------------------------------------------
BD 70 2
POEME 60 1
ROMAN 187 5
SQL>
1 select genre,avg(prix),count(*) from biblio
2 group by genre
3 having genre!='ROMAN';
GENRE AVG(PRIX) COUNT(*)
------------------------------------------------------------
BD 70 2
POEME 60 1
SQL>
1 select genre,avg(prix),count(*) from biblio
2 where prix<65

3 group by genre
4 having genre!='ROMAN';
GENRE AVG(PRIX) COUNT(*)
------------------------------------------------------------
POEME 60 1
SQL>
1 select genre,avg(prix) from biblio
2 group by genre
3 having avg(prix)>60
GENRE AVG(PRIX)
-----------------------------------------------------------
BD 70
ROMAN 187

Remarque

De facon générale, la syntaxe HAVING ... peut s'employer lorsque les arguments du SELECT arg1, arg2, ..argn sont :

aune constante ou une fonction sans paramètres (ex : SYSDATE)
b une fonction de groupe telle que celles que nous venons de voir
c une des expressions expri de la clause GROUP BY expr1, expr2, ....

2.21 La pseudo-colonne ROWNUM

Une pseudo-colonne se comporte comme une colonne, si ce n'est qu'elle ne fait pas partie de la table. L'une d'entre-elles appelee ROWNUM numerote de 1 a n les lignes résultat d'une Requête. Elle nous permet par exemple de limiter le nombre de lignes résultat de la requête :
WHERE ROWNUM <5 par exemple, limite a 4 le nombre de lignes selectionnees.

Exemple

SQL> select rownum,titre,prix from biblio;
ROWNUM 	TITRE 					PRIX
----------------------------------------
1 		Les fleurs du mal 		120
2 		Tintin au Tibet 		70
3 		La terre 				52.5
4 		Madame Bovary 			136.5
5 		Manhattan transfer 		336
6 		Tintin en Amerique 		70
7 		Du cote de ch. Swann 	210
8 		Le pere Goriot 			200
8 rows selected.
SQL>select rownum,titre,prix from biblio
2 where prix<100;
---------- ------------------------------------------
1 Tintin au Tibet 70
2 La terre 52.5
3 Tintin en Amerique 70
SQL> select titre,auteur from biblio where rownum <4; <-- affichage des 3 premieres lignes de biblio
TITRE 				AUTEUR
------------------------------------------------------
Les fleurs du mal 	Baudelaire
Tintin au Tibet 	Herge
La terre 			Zola

2.22 Mémoriser et imprimer les commandes SQL et leurs résultats

Il est necessaire d'avoir la possibilité de conserver et imprimer le résultat des Requêtes notamment ceux du SELECT. Cela se fait avec la commande SQLPLUS (et non SQL)

syntaxe1 spool fichier

action:
les affichages écran sont dupliqués dans fichier. Si celui-ci n'a pas de suffixe, il s'appellera en fait fichier.lst.

syntaxe2 spool off

action: arrête toute duplication de l'écran dans un fichier

syntaxe3 spool out

action:
s'utilise à la place de spool off. Imprime alors le contenu du fichier dupliquant l'écran.

Exemples

SQL> spool copie <-- duplication dans copie.lst
SQL> select titre,genre from biblio;
TITRE 						GENRE <-- affichage écran
----------------------------------------------------------------
Les fleurs du mal 			POEME
Tintin au Tibet 			BD
La terre 					ROMAN
Madame Bovary 				ROMAN
Manhattan transfer 			ROMAN
Tintin en Amerique 			BD
Du cote de ch. Swann 		ROMAN
Le pere Goriot 				ROMAN
8 rows selected.

SQL> spool off -- <-- fin de la duplication
SQL> host -- <-- on passe sous le système
$ ll copie.lst <-- on verifie que copie.lst existe bien
-rw-rw-r-- 1 omara ingenieur 882 Oct 21 15:05 copie.lst
$ cat copie.lst -- <-- son contenu

SQL> select titre,genre from biblio; -- <-- tout ce qui est apparu à l'écran
TITRE GENRE
------------------------------------------------------------------
Les fleurs du mal 		POEME
Tintin au Tibet 		BD
La terre 				ROMAN
Madame Bovary 			ROMAN
Manhattan transfer 		ROMAN
Tintin en Amerique 		BD
Du cote de ch. Swann 	ROMAN
Le pere Goriot 			ROMAN
8 rows selected.
SQL> spool off -- <-- dernière commande enregistrée
$ exit -- <-- retour à SQLPLUS

Chercher

Oracle : Langage SQL

Connexion

Tags populaires

derniers Articles