Oracle 10g

SQL
pour
Oracle 10g
Razvan Bizoï
© Tsoft, Groupe Eyrolles, 2006, ISBN : 2-212-12055-9, ISBN 13 : 978-2-212-12055-4
Préambule
Ce guide de formation a pour but de vous permettre d'acquérir une bonne
connaissance du langage SQL.
Vous pourrez ensuite tirer profit d'un autre ouvrage du même auteur "PL/SQL pour
Oracle 10g". Ces deux ouvrages vous permettront de préparer les examens de
certification Oracle suivants :
« 1Z0-007 » Introduction to Oracle9i: SQL® (ou la version 10g lorsque
l’examen sera disponible ce qu’il n’est pas encore à la parution de cet ouvrage)
« 1Z0-001 » Introduction to Oracle: SQL® and PL/SQL™
« 1Z0-147 » Program with PL/SQL
Support de formation
Ce guide de formation est idéal pour être utilisé comme support élève dans une
formation se déroulant avec un animateur dans une salle de formation, car il permet à
l'élève de suivre la progression pédagogique de l'animateur sans avoir à prendre
beaucoup de notes. L'animateur, quant à lui, appuie ses explications sur les images
figurant sur chaque page de l'ouvrage.
Cet ouvrage peut aussi servir de manuel d'autoformation car il est rédigé à la façon
d'un livre, il est complet comme un livre, il va beaucoup plus loin qu'un simple
support de cours. De plus, il inclut une quantité d'ateliers conçus pour vous faire
acquérir une bonne pratique d’administration de la base de données.
Les ateliers
Le livre vise à donner la possibilité à chacun de manipuler et mettre en œuvre les
fonctionnalités de ces deux langage sans pour autant avoir besoin d’un serveur Oracle
classique qui nécessite des ressources, des droits (distribués avec parcimonie par les
DBA) et surtout une licence (qui n’est pas de plus accessible).
Le choix de présenter l’installation et l’ensemble des ateliers avec Oracle Database
10g Express Edition, qui offre une compatibilité totale avec les produits de la famille
Oracle Database, s’est imposé de lui-même, pour permettre de démarrer petit mais de
voir grand.
© Eyrolles/Tsoft – SQL pour Oracle10g
1
Préambule
Progression pédagogique
Ce cours comprend 22 chapitres, il est prévu pour durer cinq jours avec un animateur
pour des personnes n'ayant aucune connaissance préalable du sujet.
Suivant l'expérience des stagiaires et le but poursuivi, l'instructeur passera plus ou
moins de temps sur chaque module.
Attention : l'apprentissage « par cœur » des chapitres n'est d'aucune utilité pour passer
les examens. Une bonne pratique et beaucoup de réflexion seront réellement utiles
ainsi que la lecture des aides en ligne. C'est pourquoi ce livre présente des ateliers
pratiques en fin de chaque chapitre.
Présentation de l’environnement
Le premier module vous propose une description des concepts de base de données
relationnelle ainsi que de faire connaissance avec les composants constitutifs d’une
base de données relationnelle.
Dans ce module, vous allez découvrir le mode de communication du serveur Oracle
avec les clients.
Nous allons traiter dans ce module de l'installation d’Oracle 10g Express Edition, les
étapes d’installation et le détail des pré-requis en ressources système nécessaires pour
l’installation dans deux environnements : Windows et Linux.
Les outils SQL*Plus
Dans ce module, vous allez découvrir les interfaces de base de données mise à la
disposition pour SQL et PL/SQL. Oracle fournit un certain nombre d’outils standards
qui ont évolué et maturé suivant les versions. Puissants et performants, ces outils se
retrouvent sur toutes les plateformes quelle que soit la version.
Nous allons traiter dans ce module, l’utilisation de l’environnement SQL*Plus
Worksheet, l’environnement iSQL*Plus ainsi que l’installation et utilisation de
l’environnement SQL Developer.
Interrogation des données
Dans ce module, vous allez découvrir l’extraction des données d’une table le choix des
colonnes souhaitées.
Vous trouvez également dans ce module le traitement des colonnes contenant des
valeurs NULL, les opérations arithmétiques avec les colonnes de type numériques et
les colonnes de type date ainsi que l’affichage des résultats des requêtes triées.
2
© Eyrolles/Tsoft – SQL pour Oracle10g
Préambule
Les opérateurs logiques
Nous allons traiter dans ce module, de l’utilisation des opérateurs logiques simples et
des opérateurs multiples.
Ce module décrit également de la mise en œuvre des opérateurs de recherche dans les
chaînes de caractères, de l’utilisation des opérateurs pour traiter des valeurs NULL des
colonnes.
Vous trouvez également dans ce module comment combiner plusieurs expressions de
type logique à l’aide des opérateurs logiques AND, OR ou NOT.
Les chaînes de caractères
Dans ce module, vous allez découvrir le traitement des chaînes de caractères, leur
formatage, l’extraction une partie d’une chaîne de caractères ainsi que la recherche et
remplacement des sous chaînes.
Nous allons traiter dans ce module les nouvelles fonctionnalités de traitement des
chaînes de caractères à l'aide des expressions régulières.
Les fonctions numériques
Nous allons traiter dans ce module de l’utilisation des types de données numériques,
des calculs arithmétiques, des arrondis et manipulation du signe des données.
Vous allez également y découvrir les nouveautés des nombres réels à virgule flottante
ainsi que les fonctions de traitements et conversions de ces types de données.
Le traitement des dates
Vous trouvez dans ce module les modalités de récupération des informations
concernant la date et l’emplacement de la base, de changement des paramètres de zone
horaire pour la session ainsi que le stockage de ces informations dans la base de
données.
Nous allons également y traiter les expressions de type date ainsi que les arrondis de
ces expressions.
Les conversions SQL
Dans ce module, vous allez découvrir les modalités d’effectuer des conversions
implicites et le traitement de conversion entre les différents types de données
Nous allons également traiter dans ce module, la manipulation des expressions de type
date de type chaîne de caractères.
Groupement des données
Ce module décrit les fonctions dites "verticales" pour les regroupements et le calcul
cumulatif. Les fonctions verticales sont utilisées pour le calcul cumulatif des valeurs
par rapport à un regroupement ou pour l’ensemble des lignes de la requête.
Agrégation et Analyse
Dans ce module, vous allez découvrir les nouvelles fonctionnalités de traitement
multidimensionnel, les fonctions d’agrégation multidimensionnelles, les calculs
suivant un partitionnement de données et le concept de fenêtrage.
Nous allons également traiter dans ce module, des fonctions de classement, des
requêtes qui utilisent des valeurs détaillées et des résultats des regroupements en
même temps.
© Eyrolles/Tsoft – SQL pour Oracle10g
3
Préambule
Les requêtes multitables
Nous examinerons dans cette section comment coupler les lignes de deux ou plusieurs
tables afin d'en extraire des données corrélées, utiliser la syntaxe d’Oracle pour les
jointures ou la syntaxe ANSI SQL :1999.
Les jointures complexes
Dans ce module, vous allez découvrir les interrogations avec les opérateurs
ensemblistes et l’utilisation des sous-requêtes.
Nous examinerons tout particulièrement l’utilisation des sous-requêtes avec les
opérateurs IN, ALL et ANY, les sous-requêtes dans la clause FROM et les sousrequêtes synchronisées.
Mise à jour des données
Ce module présente le Langage de Manipulation de Donnée ou LMD, (UPDATE,
INSERT et DELETE), qui permet d'effectuer les trois types de modifications (mise à
jour de lignes, ajout de lignes et suppression de lignes sélectionnées).
Nous examinerons tout particulièrement l’utilisation des nouveautés des insertions
multi-tables des mises à jour conditionnées.
Les transactions
Dans ce module vous pouvez découvrir la gestion des transactions. Toute base de
données a pour objectif de fournir aux utilisateurs un accès simultané aux données.
La notion de concurrence d'accès et de verrouillage des données intervient lorsque
plusieurs utilisateurs essaient d'accéder simultanément aux mêmes données. Le
concept de transaction est différent mais il n'en reste pas moins à la base de la gestion
des accès concurrents : les données modifiées lui sont réservées jusqu'à sa fin.
Nous examinerons tout particulièrement la structuration d’une transaction et les
annulations partielles.
La création des tables
Les tables contiennent un ensemble fixe de colonnes, chaque colonne possède un nom
ainsi que des caractéristiques spécifiques.
Une table d'objets est une table dont toutes les lignes sont des types de données
abstraits possédant chacun un identifiant d'objet (OID, Object ID).
La gestion des tables
Les tables sont mises en relation via les colonnes qu'elles ont en commun. Vous
pouvez faire en sorte que la base de données applique ces relations au moyen de
l'intégrité référentielle.
L'intérêt d'employer des contraintes est qu'Oracle assure en grande partie l'intégrité des
données. Par conséquent, plus vous ajoutez de contraintes à une définition de table,
moins vous aurez de travail pour la maintenance des données.
Les index
L’index est une structure de base de données utilisée par le serveur pour localiser
rapidement une ligne dans une table.
Dans ce module, nous allons détailler l’index de table de type B-Tree classique et
l’index de type bitmap conçu pour supporter des requêtes sur des tables volumineuses
dont les colonnes contiennent peu de valeurs distinctes.
Vous pouvez également découvrir dans ce module les tables organisées en index.
4
© Eyrolles/Tsoft – SQL pour Oracle10g
Préambule
Les vues et autres objets
Ce module concerne les autres objets de la base de données comme le lien de base de
données qui permet de se connecter à une base et d'accéder à partir de là à des objets
situés dans une autre base de façon transparente, c'est-à-dire comme s'ils se trouvaient
dans la base à laquelle vous êtes directement connecté.
Les profils
Depuis Oracle8, les administrateurs de bases de données disposent de différentes
fonctionnalités qui sont essentielles pour assurer la sécurité des mots de passe.
Pour améliorer le contrôle de la sécurité de la base de données, la gestion de mot de
passe d'Oracle est contrôlée par des administrateurs de base de données avec des
profils.
Le profil d'un utilisateur limite l'utilisation de la base de données et les ressources
d'instance conformément à sa définition. Vous pouvez affecter un profil à chaque
utilisateur et un profil par défaut à tous les utilisateurs ne disposant pas d'un profil
spécifique.
Les utilisateurs
Lorsqu'on parle d'utilisateurs de base de données, il est généralement question de trois
types d'entités :
Les utilisateurs finaux sont des utilisateurs qui se connectent à la base Oracle pour
interagir avec les données qui y sont stockées et les maintenir
Les applications qui sont écrites pour aider les utilisateurs finaux à exécuter plus
facilement et plus rapidement leurs tâches.
Les administrateurs de bases de données surveillent et maintiennent la base ellemême; ils ont donc besoin du plus haut niveau de privilèges.
Les privilèges
En tant qu'administrateur de bases de données, vous êtes chargé d'octroyer et de
révoquer des privilèges d'accès aux utilisateurs de la base. Vous pouvez employer des
rôles pour faciliter l'administration de privilèges, et des vues pour limiter l'accès des
utilisateurs à certaines données.
Ce module décrit comment utiliser et gérer les privilèges de niveaux système et objet,
les rôles et les vues afin d'assurer la sécurité des données de la base et de garantir leur
intégrité.
Le dictionnaire de données
Le dictionnaire est un ensemble de tables et de vues qui contient toutes les
informations concernant la structure de stockage et tous les objets de la base. Toute
information concernant la base de données se retrouve dans le dictionnaire de
données.
Le dictionnaire de données Oracle stocke toutes les informations utilisées pour gérer
les objets de la base. Ce dictionnaire est généralement exploité par l'administrateur de
base de données, mais c'est aussi une source d'information utile pour les développeurs
et les utilisateurs.
Ce module présente les mécanismes d’accès à ces informations à travers les vues du
dictionnaire de données.
© Eyrolles/Tsoft – SQL pour Oracle10g
5
Préambule
Conventions utilisées dans l’ouvrage
« MAJUSCULES »
[ ]
[,...]
{ }
|
...
Les ordres SQL ou tout identifiant ou mot clé. Utilisé pour les mots clé, les
noms des tables, les noms des champs, les noms des blocs etc....
L’information qui se trouve entre les crochets est facultative.
L’argument précédent peut être répété plusieurs fois.
Liste de choix exclusive.
Séparateur dans une liste de choix.
La suite est non significative pour le sujet traité.
La définition suivante est valable uniquement dans la version Oracle8i.
La définition est valable pour la version Oracle9i mais également dans les
versions suivantes.
La définition est valable à partir de la version Oracle10g.
La définition uniquement valable pour l’environnement de travail
UNIX/Linux.
La définition uniquement valable pour l’environnement de travail Windows.
Ce sigle introduit un exemple de code avec la description complète telle
qu’elle est présente à l’écran dans l’outil de commande.
Une note qui présente des informations intéressantes en rapport avec le sujet
traité.
Un encadré. Attention met en évidence les problèmes potentiels et vous aide
à les éviter. Il peut être également une mise en garde ou une définition
critique.
Une Astuce, apporte une suggestion ou propose une méthode plus simple
pour effectuer une action donnée.
Un Conseil, une démarche impérative à suivre pour pouvoir résoudre le
problème.
6
© Eyrolles/Tsoft – SQL pour Oracle10g