Apprendre le PL/SQL cours complet
Apprendre le PL/SQL cours complet
...
1.2 - Connexion et accès à SQL*Plus
- Connexion à SQL*plus :
prompt>. oraenv (Entrée)
prompt>sqlplus (Entrée)
login (entrer votre login) (Entrée)
password (entrer votre mot de passe) (Entrée)
SQL>
- Déconnexion de SQL*plus :
SQL>exit (Entrée)
prompt>
1.3 - Manipulations de base
- Aide en ligne
help <nom-commande> : aide sur la commande citée (ex. : help exit)
- Dialogue avec le système hôte
host <commande> : lance la commande qui doit être une commande du système d'exploitation.
- : host ls (Entrée)
- Travail sous sqlplus
1) Travail en direct :
- a) La plupart des commandes sql peuvent être tapées sur une ou plusieurs lignes et doivent être terminées par ;
SQL ne fait pas la différence entre minuscules et majuscules.
Il est toutefois recommandé de mettre les mots SQL en majuscules, d'aller à la ligne et d'indenter les lignes de commande.
- b) Oracle interprète la commande et l'exécute immédiatement. Les résultats sont affichés.
2) Travail à partir d'un script
- a) On écrit une suite de commandes (terminées par un ;) à l'aide d'un éditeur de texte (vi, emacs, xemacs, ...),
bien qu'il y ait un buffer SQL et l'on sauvegarde le fichier dans le répertoire de travail, le nom du fichier devant avoir pour extension .sql
- b) On exécute les commandes soit en faisant du "couper/coller" de l'éditeur vers SQL, soit en dialogant avec le système hôte de la manière suivante :
start <nomprog> : charge le fichier et lance les commandes contenues dans le fichier nomprog.sql (inutile de taper .sql)
@ <nomprog> : charge le fichier et lance les commandes contenues dans le fichier nomprog.sql (ne pas taper .sql)
- Exercice
o Sous un éditeur taper :
o PROMPT Bonjour a tous!
o -- ceci est une remarque se terminant à la fin de la ligne
o REM c'est encore une remarque se terminant à la fin de la ligne
o /*
o ceci est un bloc de remarques
o pouvant s'étendre sur plusieurs lignes
*/
o Sauvegerder, puis exécuter.
o Attention! Pour simplifier le travail, sauvegarder dans le répertoire dans lequel on a lancé Oracle (sqlplus).
1.4 - Tables : création, modification, suppression
Les principales notions concernant ces opérations sont décrites dans un aide-mémoire auquel on peut accéder en cliquant ici.
Remarque :
Les opérations Ajout, Modification, Suppression ne sont pas effectives automatiquement.
La commande COMMIT rend effectives les opérations faites précédemment (automatique lorsqu'on quitte Oracle).
La commande ROLLBACK permet de restaurer les tables dans l'état antérieur.
...
1.6 - Tables : Contraintes d'intégrité
Une contrainte d'intégrité est une règle qui permet de contrôler la validité et la cohérence des valeurs entrées dans les tables.
- Contrainte de colonne :
Cette contrainte est ainsi nommée parce qu'elle concerne une colonne, elle est déclarée sur la ligne de définition de la colonne à laquelle elle se rapporte.
Pas de virgule entre la définition de la colonne et celle de la contrainte
[CONSTRAINT nom_contrainte] la_contrainte [option]
Exemple :
AGE NUMBER(3,0) CONSTRAINT Toujours NOT NULL,
[CONSTRAINT nom_contrainte] : permet de donner un nom à la contrainte ; ce nom est sauvegardé dans le dictionnaire des données.
la_contrainte :
NOT NULL : spécifie que pour toute occurrence la colonne doit être valorisée
UNIQUE : toutes les valeurs de la colonne sont nécessairement distinctes
PRIMARY KEY : la colonne est une clé primaire pour la table.
peut être remplacé par UNIQUE NOT NULL
REFERENCES table(colonne) : il s'agit d'une contrainte d'intégrité fonctionnelle par rapport à une clé ; chaque valeur de la colonne doit exister dans la table et la colonne référencées.
On utilise cette contrainte pour les clés étrangères.
Dans une table COMMANDE,
code_client VARCHAR2(8)
CONSTRAINT fk_client REFERENCES CLIENT(code_client),
A chaque entrée d'une valeur code_client dans la table COMMANDE, Oracle vérifie l'existence de cette valeur dans la table CLIENT.
CHECK : mot-clé associé à une condition qui doit être vérifiée par toutes les valeurs de la colonne (domaine des valeurs de l'attribut).
age NUMBER(3,0) CONSTRAINT Toujours NOT NULL CHECK (age BETWEEN 0 AND 150), [option]
ON DELETE CASCADE : En sa présence, lorsqu'une occurrence est supprimée (suppression d'une instanciation de la clé primaire), dans chaque table ayant une clé étrangère associée, il y a suppression des occurrences correspondantes (même valeur de clé étrangère que la valeur de la clé primaire)
Dans l'exemple précédent, la suppression d'un client (à partir de son code) entraînera la suppression de toutes les commandes de ce client
Remarque : L'utilisation des contraintes d'intégrité fonctionnelle impose un ordre dans la création des tables et dans leur suppression.
- Contrainte de table :
Une contrainte de table est une contrainte d'intégrité qui porte sur un ensemble de colonnes d'une même table.
Une virgule sépare la définition d'une contrainte de table des définitions des colonnes
[CONSTRAINT nom_contrainte] la_contrainte [option] :
[CONSTRAINT nom_contrainte] : permet de donner un nom à la contrainte ; ce nom est sauvegardé dans le dictionnaire des données.
UNIQUE (col i, col j, ...) : l'unicité porte sur le n-uplet des valeurs
PRIMARY KEY (col i, col j, ...) : clé primaire de la table
CREATE TABLE article_commande
(no_commande NUMBER(5), -- numéro de la commande
code_article VARCHAR2(10), -- référence de l'article commandé
quantite NOMBER(3), -- nombre d'articles pour la référence et la commande
CONSTRAINT C_artcom PRIMARY KEY (no_commande,code_article)
);
FOREIGN KEY (col i, col j, ...) REFERENCES table.(col m, col n, ...)
ON DELETE CASCADE
désigne une clé étrangère constituée de plusieurs colonnes.
Il est très utile de mettre des commentaires pour la définition de chaque colonne, mais il ne faut pas en mettre sur la dernière ligne contenant );
- 1.7 - Consultation de données
Oracle - Consultation des données
- Voir le contenu d'une table (titre des colonnes et occurrences)
SELECT * FROM T ;
=> affichage du contenu de la table T
- Restreindre la consultation à certaines colonnes de la tables
SELECT c1, c2, ..., ck
FROM T;
=> seules les colonnes c1, c2, ..., ck sont affichées dans l'ordre dans lequel elles ont été demandées.
On peut intercaler ALL ou DISTINCT entre la commande SELECT et le nom des colonnes.
SELECT [ALL/DISTINCT] c1, c2, ..., ck
FROM T;
ALL [par défaut] permet d'afficher toutes les occurrences (lignes).
DISTINCT n'affiche que les occurrences (n-uplets, lignes) distinctes.
- Remarque : SELECT est une commande très importante.
- C'est elle qui permet d'accéder aux données
- Elle permet d'obtenir bien plus que des données brutes.
- Le résultat de la sélection est une table
Quelques éléments du dictionnaire des données
1) Dans le Dictionnaire des Données, la table all_catalog contient le catalogue général de la BD.
- Structure de la table (nom et type des colonnes) :
DESC all_catalog ;
- Contenu de la table (toutes les occurrences) :
SELECT *
FROM all_catalog;
2) Liste des noms de tables de l'utilisateur (colonne table_name de la table user_tables) :
SELECT table name
FROM user tables;
3) Liste des noms de tables auxquelles l'utilisateur peut accéder avec le nom de leur propriétaire
(colonnes owner, table_name de la table all_tables) :
SELECT owner, table_name
FROM all_tables ;
Remarque : Accès d'un utilisateur au dictionnaire des données
-- "USER_" : vues permettant de ne voir que les objets appartenant à un utilisateur,
-- "ALL_" : vue permettant de voir tous les objets accessibles à un utilisateur.
-- Exemple :
select table_name FROM user_tables;
select table_name FROM tabs; -- tabs est un synonyme de user_tables
select table_name FROM all_tables;
4) Création de tables à partir des tables fm.A_xxx :
Ici "fm" est le nom du propriétaire de la table et "A_xxx" est le nom de la table.
Exemple de copie d'une table avec sa structure et son contenu :
CREATE TABLE A_xxx
AS SELECT *
FROM fm.A_xxx ;
5) Travail sur une table :
-- afficher la structure d'une table :
Table dont fm est est le propriétaire et à laquelle l'utilisateur a accès :
DESC fm.A_artiste
Table personnelle :
DESC A_artiste
Remarque : l'écriture de cette commande tient toujours sur une seule ligne, elle n'a pas besoin du ; de fin de commande.
-- lister les nom, localite, datns (date de naissance) de tous les artistes :
-- projection sur plusieurs colonnes
SELECT nom, localite, datns
FROM A_artiste ;
6) Autorisations :
o donner une autorisation de lecture d'une table à un autre utilisateur
GRANT select -- insert, update, all
ON nomtable
TO nom_utilisateur -- ou TO PUBLIC
[WITH GRANT OPTION] ;
/* la dernière ligne optionnelle permet à nom_utilisateur de transmettre à quelqu'un d'autre
l'autorisation qu'il a reçue.
*/ o retirer une autorisation :
REVOKE select
ON nomtable
FROM nom_utilisateur ;
/*
il y a suppression des droits en chaîne à ceux à qui l'utilisateur avait pu transmettre cette autorisation
*/
- create table 1.8 - Exercices
TD/TP SQL, SQL*PLUS N°01
TD/TP SQL, SQL*PLUS N°01
CREATION D'UNE TABLE, MODIFICATION DE LA STRUCTURE, INSERTION DE DONNEES
------------------------------------------------------------------------
- Créer une table permettant de stocker des noms, prénoms, dates de naissance (type date), taille en cm.
- Structure de la table
- voir la structure à l'aide de la commande
DESC[RIBE] nom_table
- modifier cette structure selon les règles suivantes :
. la date de naissance est une chaine de 10 caractères 'DD/MMM/YYYY'
. on ajoute le caractère poids (l'unité est le kg).
- Contenu de la table
- insérer des occurrences
- visualiser le contenu de la table
- renommer la table
- Voir la liste des tables personnelles
SELECT table_name from tabs;
- Suppression de la table
GESTION DE VENTE À DOMICILE
---------------------------
On se place dans le cadre d'une gestion simplifiée de vente à domicile.
Schéma relationnel :
CATALOGUE (référence, libellé, taille, couleur, prix, qté_en_stock)
CLIENT (code_client, nom, prénom, adresse, tél)
COMMANDE (no_commande, date, code_client)
ARTICLE_COMMANDE (no_commande, référence, qté)
Remarque : les clés primaires sont soulignées, les clés étrangères sont en italique.
Travail demandé :
Ecrire en SQL la création des tables suivantes :
CATALOGUE, CLIENT, COMMANDE, ARTICLE_COMMANDE.
Insérer des données cohérentes dans la base
Chapitre 2 : Oracle - Premières requêtes
________________________________________
2.1 - Requêtes sur une table
- a) Syntaxe générale :
SELECT ... -- tout (*) ou projection sur une ou plusieurs colonnes
FROM ... -- liste des tables
WHERE expression -- conditions de jointures, restrictions
GROUP BY ... -- groupement des occurrences par paquets, les résultats concernent les paquets
HAVING ... -- condition sur les "paquets"
ORDER BY ... -- rangement des lignes de résultats selon un critère
Remarque : le résultat de la sélection est une table (virtuelle).
- b) Sélection simple : les clauses SELECT et FROM
- Contenu d'une table
SELECT * FROM T ;
Base exemple :
SELECT * FROM client
- Projection sur certaines colonnes
Rappel :
Considérons une Relation R de n attributs A1, A2, ... An. L'ensemble des occurrences de R peut être considéré comme un sous-ensemble de l'ensemble produit D=D1xD2x...xDn, Di étant le domaine de valeurs associé à l'attribut Ai pour tout i.
La projection de R sur un sous-ensemble des attributs est un sous-ensemble de D, le résultat ne contient donc pas de doublons (lignes identiques).
Si dans la projection aucun composant de la clé n'est supprimé, les lignes obtenues en supprimant les attributs non désirés seront toutes distinctes, par contre si au moins un des composants de la clé est absent dans la projection, il faut d'abord extraire des occurrences les colonnes retenues dans la projection, puis éliminer les lignes en double (les doublons).
SELECT [ALL|DISTINCT] c1, c2, ..., ck
FROM T;
Remarque concernant ORACLE : on peut intercaler ALL ou DISTINCT entre la commande SELECT et le nom des colonnes :
ALL (par défaut) permet d'afficher toutes les occurrences, même les doublons
DISTINCT n'affiche que les occurrences distinctes.
SELECT nom, prénom, tél
FROM client;
- Opérations de calcul (+, -, *, /) sur des colonnes
- -- référence, libellé et prix TTC des articles du catalogue
- SELECT référence, libellé, prix*1.206 [AS] prixTTC
- FROM catalogue;
- -- valeur HT du stock pour chaque article
- SELECT référence, libellé, prix*qté_en_stock [AS] valeur_stock
FROM catalogue;
On remarquera :
o les lignes de commentaires (précédées de --)
o les alias de colonnes (AS est facultatif) : nouveau nom donné à la colonne résultat
- c) Sélections avec qualification
- Restriction : utilisation de la clause WHERE expression
L'expression est évaluée pour chaque tuple (ou n-uplet, occurrence, ligne) de la table.
Seuls les tuples pour lesquels l'expression est vraie sont retenus.
SELECT *
FROM catalogue
WHERE qté_en_stock=0;
- Opérateurs SQL utilisés avec la clause WHERE
- arithmétiques : + - * /
- sur les dates : + -
- sur les chaînes de caractères : || (concaténation)
- de comparaison (nombres, chaînes ou dates) :
=, != ou <>, <, <=, >, >=
ordre lexicographique pour les chaînes de caractères
ordre chronologique pour les dates
- SELECT no_commande, code_client
- FROM commande
WHERE date_commande >= '01-JAN-1999';
- booléens : AND, OR, NOT
- -- commandes d'un client donné (de code 1234) depuis le 1er janvier 1999
- SELECT no_commande, code_client
- FROM commande
- WHERE code_client=1234
AND date_commande >= '01-JAN-1999';
- autres opérateurs :
1) x BETWEEN a AND b
(expression vraie si x est compris entre a et b, bornes incluses)
- -- numéros des commandes passées entre deux dates
- SELECT no_commande
- FROM commande
WHERE date_commande BETWEEN "01-JAN-1999" AND '31-JAN-1999';
2) x IN (v1, v2, ..., vk)
(expression vraie si x est égal à un des éléments de la liste) v1, v2, ..., vk doivent être de même type que x
-- numéros des commandes d'articles figurant dans une liste
SELECT no_commande
FROM article_commande
WHERE référence IN ('P201','P203','P206');
3) x NOT IN (v1, v2, ..., vk)
(expression vraie si x est différent de tous les éléments de la liste)
-- liste des articles qui sont ni rouges, ni verts, ni jaunes
SELECT référence, libellé, couleur, taille
FROM catalogue
WHERE couleur NOT IN ('rouge','vert','jaune');
4) x LIKE y
expression vraie si les deux chaînes sont égales en tenant compte des caractères jokers de la seconde chaîne
le caractère % remplace n'importe quelle chaîne de caractères, même la chaîne vide
le caractère _ remplace exactement un caractère.
-- liste des clients dont le nom est DUPONT, DUPOND ou DUPONS
SELECT prénom, nom
FROM client
WHERE nom LIKE 'DUPON_';
Résultat : PRENOM NOM
Jérome DUPONT
Julien DUPOND
Monique DUPONS
-- liste des clients dont le nom ressemble à MO[N]ET
SELECT prénom, nom
FROM client
WHERE nom LIKE 'MO%NET%';
Résultat : PRENOM NOM
Francette MONET
Claude MONET
Jean MONNET
Isidore MONNETER
Françou MONETTE
5) x NOT LIKE y
x IS NULL
(expresion vraie si la valeur de la colonne n'est pas renseignée)
x IS NOT NULL
(expresion vraie si la valeur de la colonne est renseignée)
-- liste des prénom, nom, numéro de téléphone des clients
SELECT prénom, nom, tél
FROM client
WHERE tél IS NOT NULL ;
-- liste des clients dont on n'a pas le numéro de téléphone
SELECT prénom, nom
FROM client
WHERE tél IS NULL ;
6) x op ANY (...), x opALL (...), x opSOME (...), x op EXISTS (...) avec (op : =, !=, <, <=, > ou >=)
ALL : la condition est vraie si elle est vérifiée pour toutes les occurrences qui interviennent dans la comparaison. Nous pouvons remarquer que dans le cas de l'opérateur numérique > cela revient à chercher un maximum.
Dans la base_exemple, trouver le produit le plus cher :
-- Occurrences insérées dans la table (auparavant vide) :
INSERT INTO catalogue VALUES('A000001','TEE-SHIRT','XL','BLANC',150.00,5);
INSERT INTO catalogue VALUES('A000002','TEE-SHIRT','L','BLEU NUIT',140.00,5);
INSERT INTO catalogue VALUES('A000003','TEE-SHIRT','M','BLANC',130.00,6);
INSERT INTO catalogue VALUES('A000004','TEE-SHIRT','XL','BLANC',170.00,3);
INSERT INTO catalogue VALUES('A000005','TEE-SHIRT','XL','NOIR',160.00,9);
-- Requête (Attention ! Requête dans une requête (voir chapître 4)) :
SELECT libelle, prix
FROM catalogue
WHERE prix >= ALL(SELECT prix FROM catalogue);
-- Résultat de la requête :
LIBELLE PRIX
----------- ----------
TEE-SHIRT 170
- Fonctions SQL (voir chapitre suivant)
2.2 Opérateurs de l'algèbre relationnelle
UNION [ALL], INTERSECT, MINUS
Opérateur UNION
Il permet d'obtenir un ensemble de lignes qui est la réunion des lignes obtenues par deux sélections.
Les lignes communes n'apparaitront qu'une seule fois, sauf si l'on a utilisé UNION ALL.
Les lignes de chaque sélection doivent être de même nature.
Les alias de colonnes n'apparaissent qu'une fois.
La clause ORDER BY est écrite en dernière ligne. (cette clause sera revue plus loin)
SELECT ...
FROM ...
WHERE ...
UNION
SELECT ...
FROM ...
WHERE ...
ORDER BY ... ;
Opérateur INTERSECT
Il permet d'obtenir l'ensemble des lignes communes à deux sélections
SELECT ...
INTERSECT
SELECT ... ;
Opérateur MINUS
Il permet de retirer d'une sélection les lignes présentes dans une deuxième sélection.
SELECT ...
MINUS
SELECT ... ;
2.3 Opérations de Jointure
Rappel : notion de jointure
Une jointure nous sert à faire des requêtes concernant des données stockées dans plusieurs tables, mais ayant un certain lien entre elles.
Notons que rechercher dans la base exemple la liste des clients ayant passé commande à une date donnée nécessite de rechercher dans les tables CLIENT et COMMANDE en utilisant le lien no_client.
La jointure entre deux tables est le résultat de l'action qui consiste à créer une table virtuelle constituée d'un sous-ensemble du produit cartésien des deux tables, constitué de la manière suivante :
- on considère le produit cartésien des deux tables, c'est à dire une table ayant pour colonnes toutes les colonnes des deux tables, et pour tuples les tuples formés de toutes les associations de chaque tuple d'une table avec chaque tuple de l'autre.
- on supprime ensuite tous les tuples incohérents, c'est à dire ceux qui ne vérifient pas la condition de jointure.
Cette condition de jointure est en rapport avec la construction du MCD et des contraintes liant les tables.
- enfin, on supprime les colonnes qui font double emploi.
- Remarque : à la jointure est souvent associée une projection sur certaines colonnes.
- Jointure simple sur deux tables
La requête est faite sur les deux tables : FROM table1, table2
La condition de jointure est spécifiée dans la clause WHERE prédicat
sous une forme du genre :
WHERE table1.col_k=table2.col_h
En l'absence de condition de jointure, le résultat obtenu est le produit cartésien des tables.
-- liste des clients qui ont passé commande depuis le 1er janvier 1999
SELECT DISTINCT prénom, nom
FROM client,commande
WHERE client.code_client=commande.code_client
ANDdate_commande>'01-JAN-1999' ;
-- noter la présence de DISTINCT
-- jointure sur la colonne commune code_client
-- il est nécessaire d'indiquer nomtable.nomcolonne en cas d'homonymes
-- Pour simplifier l'écriture on peut définir des alias de table, par exemple :
SELECT DISTINCT prénom, nom
FROM client cl,commande co
WHERE cl.code_client=co.code_client
AND date_commande>'01-JAN-1999' ;
Equi-jointure : la condition de jointure est l'égalité entre une colonne de la première table et une colonne de la deuxième table.
- Jointure sur plusieurs tables
On indique la liste des tables sur lesquelles est faite la requête.
On écrit une condition complexe comprenant les conditions de jointure des tables prises deux à deux.
- Jointure d'une table avec elle-même
On peut avoir besoin de rassembler ou comparer des informations provenant de lignes différentes de la même table.
Dans ce cas il faut utiliser des alias de table
FROM table1 Ta, table1 Tb
- Jointure externe
Lorsqu'une ligne d'une table figurant dans une jointure ne peut être liée à aucune ligne de la seconde table parce qu'elle ne satisfait pas à la condition d'équi-jointure entre les deux tables, elle ne figure pas dans le résultat de la jointure.
En Oracle, une option permet de faire figurer dans le résultat les lignes satisfaisant à la condition d'équi-jointure plus celles n'ayant pas de correspondant. Cette option s'obtient en accolant (+) au nom de colonne de la table dans laquelle manquent les éléments, dans la condition d'équi-jointure.
2.4 Rangement des résultats
Clause ORDER BY
Il s'agit d'ordonner les résultats ; dans une requête, cette clause est donc toujours placée en dernier, après toutes les autres clauses.
On peut ordonner les occurrences obtenues dans la sélection selon les valeurs d'une colonne nommée par son alias (ou à défaut son nom ou l'expression qui la définit), par son numéro d'ordre au niveau de l'affichage ou même selon une partie de la colonne (sous_chaîne).
Le sens du rangement est indiqué derrière chaque indicatif de rangement :
ASC : ordre croissant du rangement (par défaut)
DESC : ordre décroissant
SELECT ...
FROM ...
WHERE ...
ORDER BY colonne [ASC|DESC];
(ASC est facultatif (valeur par défaut))
Pour ordonner selon une liste de critères :
- les critères sont donnés dans l'ordre d'importance : du plus important au moins important
- derrière chaque critère est indiqué le sens du rangement : ASC (facultatif) ou DESC
- exemple : ORDER BY col1 DESC, col2 ASC
EXEMPLES :
-- liste des clients dont on n'a pas le numéro de téléphone
-- rangés par ordre alphabétique des noms
SELECT prénom, nom
FROM client
WHERE tél IS NULL
ORDER BY nom ;
-- liste des clients dont on n'a pas le numéro de téléphone
-- rangés par ordre alphabétique des noms et des prénoms
SELECT prénom, nom
FROM client
WHERE tél IS NULL
ORDER BY nom, prénom ;
-- ordre demandé par le nom des colonnes
ou
SELECT prénom, nom
FROM client
WHERE tél IS NULL
ORDER BY 2, 1 ;
-- ordre demandé par le numéro de sortie des colonnes
-- ordre croissant sur la date :
-- on peut transformer la date en chaîne de caractères et extraire les sous-chaînes qui nous intéressent
BREAK ON code_client ON nom
SELECT nom, TO_CHAR(date_commande, 'dd/mm/yyyy') dates
FROM E_client cl, E_commande co
WHERE cl.code_client = co.code_client
ORDER BY nom, SUBSTR(dates,7,4), SUBSTR(dates,4,2), SUBSTR(dates,1,2) ;
2.5 Présentation des résultats
FORMATAGE DES RESULTATS
- FORMATAGE D'UNE PAGE
Longueur de page, de ligne :
SET PAGESIZE n -- longueur de page
SET LINESIZE n -- longueur de ligne
SET HEADING ON -- affichage des titres des colonnes
SET HEADING OFF -- suppression des titres des colonnes
Entete de page :
TTITLE [COL n|SKIP n|TAB n|LEFT|RIGHT|CENTER| 'texte']
TAB n : mettre le texte apres n tabulations
COL n : mettre le texte a partir de la colonne n
SKIP n : sauter n lignes
TTITLE OFF : pour annuler l'affichage du titre
TTITLE ON : pour retablir l'affichage du titre
BTITLE : bas de page (mêmes commandes)
- FORMATAGE DE COLONNES
COLUMN nom_colonne options
Les options s'appliquent à toutes les colonnes de nom (ou d'alias) nom_colonne pendant la durée d'une session ou jusqu'à ce que d'autres options les remplacent.
- Consultation des options :
COL nom_colonne
- Principales options :
HEADING chaine : change l'entête de la colonne.
Mettre des apostrophes si la chaîne contient des blancs
Le caractère "|" force le retour ligne
FORMAT masque
Chaînes de caractères :
- FORMAT An => chaine de n caractères
Nombres :
- FORMAT 99999 => taille d'affichage du nombre
- FORMAT 09999 => complète le nombre par des 0 à gauche
- FORMAT $999 => préfixe le nombre avec $
- FORMAT 999.99 => aligne le point décimal dans la position indiquée
JUSTIFY L | R | C
WRAPPED | TRUNCATED
NEWLINE : passage a la ligne avant l'affichage de la colonne
CLEAR : toutes les options sont annulees
ON/OFF : bascule ``options définies''/''options par defaut''
- RUPTURE DE SEQUENCE
BREAK ON element_de_rupture [action] [ON element_de_rupture [action]]
où element_de_rupture =
un nom de colonne ou une expression (la valeur n'est pas répétée si elle est identique à celle de l'occurrence précédente))
RAW : l'action demandée est executée à chaque ligne
PAGE : l'action demandée est executée à chaque page
REPORT : l'action demandée est executée en fin de rapport (report).
et où action =
SKIP n : saut de n lignes
SKIP PAGE : commencer une nouvelle page
- TOTALISATION
COMPUTE operation OF col1 col2, ... ON element_de_rupture;
Opérations possibles : SUM, MIN, MAX, AVG, STD (écart-type), VAR (variance), COUNT (nombre de valeurs non nulles de la colonne), NUM (nombre de valeurs de la colonne y compris les valeurs nulles).
- Effacement de commandes de formatage
Les commandes sont valables pendant toute la session, tant qu'elles ne sont pas remplacées par d'autres ou effacées.
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
- Envoi des résultats d'une commande dans un fichier
SPOOL nom_fichier
Séquence de commandes SQL
SPOOL OFF (pour arreter)
2.6 Exercices
FEUILLE D'EXERCICES SQL, SQL*PLUS N°02
VOCABULAIRE : BASES DE DONNEES => ORACLE
Relation => table :
. Nom de la Relation => Nom de la Table
. Attribut => titre d'une colonne
. Valeur d'un attribut => valeur d'un élément de la colonne
. Domaine d'un attribut => valeurs possibles dans la colonne
. Contrainte sur les valeurs d'un attribut => contrainte de colonne
. Occurrence (tuple, n-uplet) => ligne (nombre variable de lignes)
. Clé primaire composée d'un seul attribut => contrainte de colonne
. Clé primaire composée de plusieurs attributs => contrainte de table
. Clé étrangère => contrainte de colonne
RAPPELS
Projection : SELECT c1,c2,...ck FROM T ;
Restriction : SELECT * FROM T WHERE cond ;
Jointure : SELECT .... FROM T1, T2 WHERE T1.c1 = T2.c2 ;
(ou avec des alias de tables :)
SELECT ... FROM T1 a, T2 b
WHERE a.c1 = b.c2 ;
Remarque : SELECT [ALL|DISTINCT] pour spécifier si l'on veut toutes les occurrences ou seulement celles qui sont distinctes. (par défaut ALL)
REQUETES :
1)
Requête sur une seule table : projection sur trois colonnes
restriction à pays = 'FRANCE'
Présentation des résultats : alias de colonne
ORDER BY : tri croissant sur une colonne
Afficher la liste des artistes nés en FRANCE par ordre croissant des noms.
Titre des colonnes de résultats : NOM LOCALITE DATE_NAISSANCE
2)
Requête sur une seule table : projection sur trois colonnes,
restriction à une colonne contenant la chaîne 'FEMME'
LIKE, %
Présentation des résultats : tri croissant sur deux colonnes.
Afficher la liste des oeuvres dont le titre contient le mot FEMME.
Tri par valeur, titre.
Titre des colonnes de résultats : DATE_CREATION TITRE VALEUR
3)
Requête sur une seule table : restriction à un intervalle de valeurs,
OU à des valeurs non définies
ET restriction à un champ contenant
un OU plusieurs mots donnés.
-- OR, BETWEEN, IS NULL
Présentation des résultats : des colonnes de largeur prédéfinie
tri croissant sur deux colonnes.
Afficher la liste des oeuvres dont la valeur est comprise entre 380 000
et 950 000 F ou bien n'est pas définie et dont le titre contient le mot FEMME
ou le mot DEMOISELLE ou le mot ENFANT
Tri par valeur, titre.
Titre des colonnes de résultats : TITRE DATE_CREATION VALEUR
4) Requête sur une seule table : projection sur 4 colonnes, alias de colonne
Restriction avec AND, OR, LIKE,
NOT LIKE, NOT IN, IS NOT NULL
Présentation des résultats : tri sur 3 colonnes,
Deux colonnes non répétées
Longueurs de lignes, de page
Afficher la liste des artistes vivants, nés en FRANCE ailleurs qu'à PARIS
ou nés dans un pays autre que la FRANCE et l'ALLEMAGNE.
Tri par pays, localité, nom.
Titre des colonnes de résultats : PAYS LOCALITE NOM DATE_NAISSANCE
5) Requête sur deux tables : jointure naturelle sur une colonne, alias de table
Présentation des résultats : rupture de séquence
Afficher la liste des oeuvres des artistes nés en ESPAGNE.
Tri par artiste, année de création
Le nom de l'artiste ne devra pas être répété.
Titre des colonnes de résultats : NOM_ARTISTE DATE_CREATION TITRE_OEUVRE
6) Requête sur deux tables : jointure naturelle sur 1 colonne, alias de table
Présentation des résultats : tri et rupture de séquence
Afficher la liste des oeuvres de tous les artistes nés hors de FRANCE.
Tri par nom, année de création.
Pays et nom_artiste ne devront pas être répétés.
Titre des colonnes de résultats :
PAYS NOM_ARTISTE DATE_CREATION TITRE
