...

Résumé

Maîtriser la programmation avec Oracle

Tout particulièrement destiné aux débutants et aux étudiants, cet ouvrage décrit de manière concise et pratique tous les mécanismes de programmation avec Oracle (jusqu’à la version 20c) relatifs à SQL et PL/SQL. De la création des tables à la programmation de transactions, en passant par l’optimisation des requêtes et traitements, il passe en revue tous les aspects fondamentaux d’Oracle. La gestion des données structurées est également expliquée (objets, XML et JSON), ainsi que différentes notions d’administration. Ce livre est illustré par de nombreux cas d’utilisation, validés par des experts, et complété par une cinquantaine d’exercices corrigés, disponibles en ligne.

À qui s’adresse cet ouvrage ?

• À tous ceux qui souhaitent s’initier à Oracle, SQL ou...

CHEZ LE MÊME ÉDITEUR

DU MÊME AUTEUR

C. Soutou. – Programmer avec MySQL (5e édition).

N°67379, 2017, 522 pages.

C. Soutou et F. Brouard. – Modélisation de bases de données.

...

Christian Soutou

Avec

...

ÉDITIONS EYROLLES

61, bd Saint-Germain

75240 Paris Cedex 05

www.editions-eyrolles.com

Pour mon fils chéri Paul (alias Slownirik) qui ne lira jamais ce livre, qui a choisi la musique pour s’exprimer et qui habite sur la planète de la vérité que je commence à approcher

...

Avant-propos

Cet ouvrage s’adresse principalement aux débutants souhaitant aborder la programmation avec Oracle et aux développeurs désireux d’approfondir leurs connaissances sur le sujet.

Pour sa rédaction, la documentation officielle d’Oracle m’a été d’une aide précieuse, ainsi que le point de vue de certains experts :

•Tom Kyte : http://asktom.oracle.com

•...

Introduction

L’histoire de SQL (Structured Query Language) a commencé en 1974 avec le logiciel System-R de IBM et son langage qui se nommait initialement SEQUEL (Structured English as QUEry Language). Le langage SQL est devenu une norme en 1986 et il s’enrichit au fil du temps. Tous les langages de programmation proposent des API pour se connecter à une base de données relationnelle et permettent aux développeurs de coder avec SQL. Les systèmes qui ont adopté ce langage dès le début (Oracle et IBM notamment) ont acquis désormais une grande maturité. Le langage SQL est le langage informatique d’accès aux données le plus utilisé et connu dans le monde.

Cette introduction présente le cadre général de l’ouvrage. Viennent ensuite l’utilisation des interfaces de commandes que propose Oracle et le modèle de données proposé en exemple dans les différents chapitres.

...

1

Définition des données

Ce chapitre décrit les instructions nécessaires pour créer les principales structures de stockage (tables, colonnes, contraintes, séquences et index). Ces instructions constituent l’aspect DDL (Data Definition Language) du langage SQL.

Création de tables (CREATE TABLE)

Une table est un objet de schéma capable de stocker des données sous la forme de lignes composées de colonnes. Pour pouvoir créer une table, il faut disposer du privilège CREATE TABLE. Si vous avez le privilège CREATE ANY TABLE, vous pouvez créer des tables dans tout schéma. Le mécanisme des privilèges est décrit au chapitre 5.

La syntaxe simplifiée pour créer une table est la suivante :

CREATE TABLE [schéma.]nom_table

(colonne1 type1 [DEFAULT [ON NULL] expression1] [NOT NULL] [, … ]

 [CONSTRAINT nom_contrainte1 type_contrainte1] [,…])

...

2

Manipulation des données

Ce chapitre décrit dans un premier temps les aspects fondamentaux du DML (Data Manipulation Language) d’Oracle :

•l’insertion de lignes avec INSERT ;

•la modification de données avec UPDATE ;

•la suppression de lignes avec DELETE ;

•la mise à jour conditionnée avec MERGE.

Ce chapitre s’intéresse également à l’intégrité référentielle et présente les principales fonctions SQL qui serviront à transformer des données lors de manipulations ou d’extractions (voir chapitre 4).

Insertions d’enregistrements (INSERT)

Pour pouvoir insérer des lignes dans une table, il faut que vous soyez propriétaire de cette dernière ou que vous ayez reçu le privilège d’insertion sur la table en question. Si vous détenez le privilège INSERT ANY TABLE...

3

Évolution d’un schéma

Ce chapitre décrit l’aspect DDL (Data Definition Language) du langage SQL d’Oracle qui rend possible l’évolution de quelques objets d’un schéma. L’instruction ALTER TABLE est celle qui dispose probablement du plus grand nombre d’options. Cette instruction permet, entre autres, d’ajouter, de renommer, de modifier et de supprimer des colonnes d’une table. Elle permet aussi de gérer certains index et les contraintes.

Comme toute instruction DDL, une validation (commit) implicite est opérée et la modification structurelle ne peut donc pas être annulée (à l’opposé de toute instruction du DML).

La fin du chapitre s’intéresse à des aspects relatifs à la récupération de données invalides, à la gestion des séquences et aux paramétrages relatifs à la globalisation (langues, collations et dates).

Gestion des colonnes

La table à faire évoluer doit se trouver dans votre schéma à moins que vous ne disposiez du privilège ALTER sur la table ou ALTER ANY TABLE pour intervenir dans tout schéma. Si vous opérez dans un autre schéma, vous devez créer des clés primaires ou uniques et vous devez également détenir le privilège CREATE INDEX sur la table ou CREATE ANY INDEX.

Considérons la table des réclamations à faire évoluer (la déclaration de la clé étrangère n’est pas présentée).

Figure 3–1...

4

Interrogation des données

Ce chapitre est consacré à l’instruction qui permet d’extraire des données sous critères et qui a fait le succès du langage SQL : SELECT (aussi appelée « requête »). Une requête peut être exploitée dans une interface SQL, une unité de programme PL/SQL ou au sein d’un quelconque programme (Java, C, PHP, etc.). Une requête est une instruction déclarative (on y décrit ce que l’on cherche sans préciser le moyen de le réaliser). Elle retourne invariablement un résultat tabulaire et peut inclure des résultats de fonctions (numériques, dates ou chaînes de caractères). À l’inverse, les instructions procédurales permettent de programmer un traitement (comme un algorithme).

Éléments du SELECT

Il faut plus de 90 pages pour décrire la syntaxe de l’instruction SELECT dans la documentation de la version 12c. De plus, bon nombre d’options, sont référencées dans d’autres documents. Il n’est donc pas raisonnable de décortiquer cette instruction en une seule écriture syntaxique. Ce chapitre va progressivement aborder les fondamentaux.

Pour pouvoir extraire des lignes d’une table, il faut être propriétaire de la table ou avoir reçu le droit SELECT sur cette dernière. Le privilège SELECT ANY TABLE permettra d’extraire des données de toute table.

Syntaxe basique (SELECT)

La syntaxe simplifiée du SELECT est la suivante, elle s’applique aux tables comme aux vues.

SELECT {* | [ DISTINCT | ALL ] [alias_table.]expression [AS alias_colonne] [, …] }
    FROM nom_table [alias_table]

  [ JOIN clause_jointure ] [ WHERE clause_filtre ]

  [ GROUP BY clause_regroupement ]

  [ HAVING clause_filtre ]

  [ ORDER BY { expression | position | alias_colonne }

             [ASC | DESC] [ NULLS FIRST | NULLS LAST ] [, …] ]

  [ clause_limitation_lignes ];

•DISTINCT : permet d’éviter les doublons.

•ALL : n’évite pas les doublons (par défaut).

•...

5

Contrôle des données

Comme pour tout système multi-utilisateurs, un usager d’un SGBD se doit d’être toujours identifié avant d’accéder à une quelconque ressource. L’accès aux données est ainsi contrôlé à des fins de sécurité et de confidentialité. Ce chapitre décrit d’une part l’architecture logique d’un serveur Oracle et d’autre part, l’utilisation de SQL pour contrôler les accès au travers des mécanismes suivants :

•conteneurs (containers) ;

•espaces de stockage (tablespaces) ;

•utilisateurs, privilèges et rôles ;

•vues et synonymes ;

•dictionnaire des données.

Architectures logiques

Depuis la version 12c, il est possible d’opter pour une architecture multitenant (une instance permettra d’héberger plusieurs bases, option payante initialement et disponible principalement pour l’édition Enterprise). Cette architecture diffère radicalement des éditions précédentes et apporte un grand nombre de nouveautés, notamment au niveau de l’administration et du partage de ressources. À partir de la version 20c, les trois premières bases seront gratuites.

Instances classiques

Avant la version 12c, une instance Oracle ne comportait qu’une seule base. On appelle désormais ce type d’architecture une « non-CDB » (non-Container Database). Il ne sera bientôt plus possible d’installer de telles instances. Selon la version retenue, le choix vous sera peut-être donné à l’installation et il suffira de décocher la dernière option pour définir une instance classique.

Figure 5–1 Choix de l’installation (ici, mode CDB)

Plusieurs composants constituent une instance :

•...