DEES INFORMATIQUE

1

V 1.0.0.a

LIVRET 5

SYSTEME DE GESTION DE BASES

DE DONNEES RELATIONNELLES

Plan du cours :

1. ORACLE SQL

1.1 Présentation des SGBD relationnelles

1.2 Notions de schémas

1.3 Caractéristiques et spécificités du SGBD ORACLE

1.4 Langage Interrogation des Données (LID)

1.5 Langage de Manipulation des Données (LMD)

1.6 Langage de Définition des Données (LDD)

2. ORACLE PL/SQL

2.1 Historique et présentation générale du langage PL/SQL

2.2 Présentation du PL/SQL ENGINE

2.3 Eléments du langage

2.4 Les structures de contrôle

2.5 Les types de données composites

2.6 Les curseurs

3. GESTIONS DES EXCEPTIONS, PROCÉDURES, FONCTIONS ET

TRIGGERS

3.1 Les exceptions

3.2 Généralités sur les procédures et les fonctions

3.3 Les procédures

3.4 Les fonctions

3.5 Les « packages »

3.6 Les triggers

3.7 Applications des triggers : DDL, CALL

3.8 Maintenance des triggers

Evaluation :

2 devoirs à rendre

DEES INFORMATIQUE

2

V 1.0.0.a

Auteur : Monsieur Daniel PERDRIOLLE

1. ORACLE SQL

1.1 Présentation des SGBD relationnelles

A- Définition

Une base de données est un ensemble d'informations structurées et

mémorisées sur support informatique.

Ces informations sont accessibles à l 'aide d'une appl ication appelée

système de gestion de base de données (SGBD).

Elle peut être de nature :

Hiérarchique

Objet

Relationnel le

Documentaire

Autres

Si ce SGBD est basé sur le système de gestion de base de données de

CODD, on dit qu'i l s'agit d'un système de gestion de base de données

relationnel (SGBDR).

Pour dialoguer avec un SGBDR, on uti l ise le langage SQL. Ce langage

permet de soumettre des requêtes (ou des interrogations) au SGBDR.

Un SGBD est un ensemble de logiciels qui fournit un environnement

pour :

Décrire

Mémoriser

Manipuler

Traiter des col lections de données

Tout en assurant leur :

Sécurité et contrôle d'accès

Confidential i té

Intégrité

DEES INFORMATIQUE

3

V 1.0.0.a

B- Les générations de SGBD

L'histoire des SGBD peut être résumée en distinguant trois générations qui

correspondent à différents modèles de données.

Les SGBD basés sur les modèles d'accès :

Ces SGBD basés sur les modèles d'accès tendent à privi légier

l 'optimisation des entrées/sorties. Ces SGBD sont les plus anciens, mais

sont encore répandus, notamment dans la grande informatique, pour des

raisons de performances, de fiabi l i té, de tai l le du parc des systèmes

instal lés, et d'importance des appl ications construites autour d'eux.

Exemple : bases de données hiérarchiques.

Les SGBD basés sur le modèle relationnel :

La deuxième génération de base de données est née en 1970 avec

l 'apparit ion du modèle relationnel.

Aujourd'hui tout SGBD Relationnel offre un ensemble intégré d'outi ls basés

sur un langage de 4ème génération (L4G) afin d'accroître la productivité

des uti l isateurs.

Les SGBD Avancés :

On parle de troisième génération de SGBD pour désigner des systèmes qui

supportent bien ces appl ications nouvel les qui exploitent des

environnements opérationnels complexes (répartis, paral lèles ou

hétérogènes).

Ce sont :

Les SGBD Orientés Objets,

Les SGBD Déducti fs,

Les SGBD Répartis.

C- Intégrité d'une base de données.

L'une des missions d'une base de données est d'assurer à tout instant

l ' intégrité c'est à dire la cohérence, la fiabi l i té, et la pertinence des

données qu'el le gère.

Le concepteur a la responsabi l i té de définir les contraintes d'intégrité qui

s'appl iquent à la base au moment de sa création.

En cours d'uti l isation, le système véri fie en permanence le respect de ces

contraintes.

DEES INFORMATIQUE

4

V 1.0.0.a

Intégrité de domaine : domaine d'un attribut : c’est l 'ensemble

des valeurs qu'i l peut prendre.

Intégrité référentielle : permet de contrôler la cohérence des

données en véri fiant les contraintes de jointure entre les tables.

Intégrité des relations : permet de contrôler que chaque l igne

d'une table relationnel le est repérée par une valeur unique.

D- Le langage SQL

Le langage de manipulation de données relationnel les se compose d'un

ensemble de commandes permettant d'interroger et de modifier une base

de données.

Ce " langage de requête structuré " (SQL = Structured Query Language)

est une évolution (1981) du langage SEQUEL d'IBM.

Il permet notamment d'exprimer de façon simple, des sélections, des

projections et des jointures.

On distingue trois sous ensembles :

Le langage de description des données (LDD)

Le langage de manipulation des données (LMD)

Le langage d’interrogation de données (LID)

Le SQL est un langage déclarati f dont la syntaxe est très simple (comme

beaucoup de langages de ce type) ce qui permet de se concentrer sur le

problème à résoudre.

1.2 Notions de schémas

L'organisation des données : Schéma Conceptuel

A- Les entités

Notion d’entités :

L'entité est un objet abstrait qui sert à regrouper des données pour

représenter le fonctionnement d'une organisation. Ces données seront

regroupées de façon homogène.

Exemples :

Dans une organisat ion de type "LYCÉE", on trouvera les entités : ÉLÈVE,

CLASSE, MATIÈRE, PROFESSEUR.

DEES INFORMATIQUE

5

V 1.0.0.a

Dans une entreprise, on trouvera les entités : PRODUIT, CLIENT,

FOURNISSEUR, COMMANDE, LIVRAISON, etc.

Notion de propriété :

Chaque entité est caractérisée par un ensemble de propriétés (ou

attributs). Leur nombre et leur type dépendra de l 'entité étudiée.

Pour l 'entité « ÉLÈVE », nous aurons comme propriétés : Numéro, Nom,

Prénom, Date de naissance, Adresse, Vi l le, Code postal.

Notion d'occurrence et de valeurs :

Une occurrence correspond à un individu d'une entité, par exemple, pour

l 'entité « ÉLÈVE », i l y aura autant d'occurrences que d'élèves dans le

lycée.

Les valeurs d'une occurrence sont les données qui lui sont rattachées. En

fait, chaque propriété aura une valeur donnée, pour une occurrence de

l 'entité.

Exemple :

L'élève n° 125, Dupont Jules, né le 12/05/1982, habitant 12 rue des

Martyrs, 75009 Paris.

Notion d'identifiant :

Parmi les différentes propriétés, i l y en a une, l 'identi fiant, qui sert à

différencier sans ambiguïté chaque occurrence de l 'entité, c'est à dire qu'à

une valeur donnée de l ' identi fiant ne peut correspondre qu'une seule

occurrence. Souvent on uti l isera comme identi fiant un code, un numéro,

une référence, afin de respecter le principe de l ' identi fiant.

Exemple :

Un nom de famil le peut être présent plusieurs fois dans l 'ensemble des

occurrences de l 'ent ité « ÉLÈVE », on choisira donc un numéro d'élève afin

d'éviter les homonymies.

Dans les traitements qui uti l iseront cette entité, i l ne sera pas uti le

d'uti l iser autre chose que l ' identi fiant, car à partir d'une valeur de celui-ci

on pourra obtenir les valeurs des autres propriétés de l 'occurrence

correspondante de l 'entité.

DEES INFORMATIQUE

6

V 1.0.0.a

Représentation :

On représente (par convention) une entité dans un rectangle surmonté du

nom de l 'entité, l istant l 'ensemble des propriétés. L'identi fiant sera mis en

évidence par un soulignement (là aussi par convention).

B- Les associations et les cardinalités

Notion d'association :

El le représente le l ien entre deux entités, l ien qui correspond

généralement à un verbe dans la description de l 'organisation.

Exemple :

Pour représenter la phrase suivante « un élève fait partie d'une classe »,

on aura, d'une part, l 'entité « ÉLÈVE », d'autre part, l 'ent ité « CLASSE »,

et entre les deux l 'association « FAIT PARTIE ».

Par convention, l 'association sera représentée dans un « cartouche », avec

en haut le nom de l 'association, et en bas les propriétés dont el le pourrait

être porteuse.

Notion de cardinalités :

On indique pour chaque entité et chaque association deux cardinal i tés,

minimum et maximum, qui indiquent le nombre d'occurrences de l 'entité

qui peuvent être concernées par cette association.

Les "couples de cardinali tés " possibles sont : 0,1 1,1 0,n 1,n

Exemple :

Pour décrire le système d'information d'un lycée, on peut uti l iser la

formulation suivante :

DEES INFORMATIQUE

7

V 1.0.0.a

Un élève fait partie d'une seule classe <=> Un élève fait partie au

minimum d'une classe et au maximum d'une classe=>1,1

Une classe comprend au moins un élève <=> Une classe comprend au

minimum un élève et au maximum plusieurs (n) =>1,n

L'association hiérarchique ou contrainte d'intégrité fonctionnelle

(C.I.F) :

El le met en jeu DEUX ENTITÉS MAXIMUM entre lesquel les existe une

dépendance fonctionnel le, c'est à dire qu'à UNE valeur de l ' identi fiant de

l 'ENTITÉ SOURCE, ne correspond qu'UNE SEULE valeur de l 'ENTITÉ BUT.

La CARDINALITÉ côté source de l 'association sera 1,1 ou 0,1.

IMPORTANT : une association hiérarchique ne peut pas être porteuse de

propriétés !

L'association FAIT PARTIE de notre exemple est donc une association

hiérarchique.

L'association non hiérarchique :

El le met en jeu DEUX ENTITÉS OU PLUS.

Une tel le associat ion peut être porteuse de données, données qui

dépendent de l 'ensemble des identi fiants des entités auxquel les est rel iée

l 'association.

Exemple :

Pour décrire le système d'information d'un lycée, on peut uti l iser la

formulation suivante :

Un professeur enseigne au moins une matière à au moins une classe=>1,n

Une classe reçoit des cours dans au moins une matière par au moins un

professeur=>1,n

Une matière fait l 'objet de cours dans au moins une classe par au moins

un professeur=>1,n

DEES INFORMATIQUE

8

V 1.0.0.a

Schéma Conceptuel correspondant à l'exemple :

DEES INFORMATIQUE

9

V 1.0.0.a

Les données dans le SGBDR : Le Schéma Relationnel

Notion de relation :

Une relation est ce qui l ie les différentes propriétés d'un objet, par

exemple la relation PROFESSEUR (Numéro, Nom, Prénom, Adresse, Vi l le,

Code postal).

Parmi les attributs de cette relation, un a un rôle équivalent à celui de

l ' identi fiant dans le schéma conceptuel. On parlera ici de CLÉ PRIMAIRE

de la relation, et on la mettra en évidence par un soul ignement.

Règles de passage du schéma conceptuel au schéma relationnel :

Une entité du modèle conceptuel devient une relation dans le modèle

relationnel, ses propriétés deviennent les attributs de la relation,

l ' identi fiant de l 'entité devient la clé primaire de la relation.

Exemple :

schéma conceptuel schéma relationnel

PROFESSEUR (Numéro, Nom, Prénom, Adresse,

Vi l le, Code postal)

Les associations hiérarchiques ne deviennent pas des relations, mais le

l ien particul ier qu'el les représentent (la dépendance entre deux

identi fiants) est signalé par la présence, dans la relation correspondant à

l 'entité source de la dépendance fonct ionnel le, de la clé de la relation

correspondant à l 'entité but. On parlera alors de clé étrangère (signalée

par un #).

DEES INFORMATIQUE

10

V 1.0.0.a

Exemple :

schéma conceptuel schéma relationnel

CLASSE (Numéro, Libel lé)

ÉLÈVE (Numéro, Nom, Prénom, Adresse, Vi l le, Code

postal, Date naissance, # NumClasse)

Remarques :

1,1 étant du côté ÉLÈVE, c'est l ' identi fiant de

CLASSE qui se place en tant que clé étrangère dans

ÉLÈVE

Pour ne pas confondre dans la relation ÉLÈVE le

numéro de l 'élève et le numéro de la classe, ce

dernier attribut a été modifié de Numéro en

NumClasse

Les associations non hiérarchiques deviennent des relations, avec comme

clef primaire la concaténation des clés étrangères correspondant aux

identi fiants des entités auxquel les ces associations sont reliées ; si el les

sont porteuses de propriétés, ces propriétés deviennent des attributs de la

relation.

Par exemple :

schéma conceptuel schéma relationnel

PROFESSEUR (Numéro, Nom, Prénom,

Adresse, Vi l le, Code postal)

CLASSE (Numéro, Libel lé)

MATIÈRE (Code, Libel lé)

ENSEIGNE (# NumProf + #NumClasse +

# Code, Nbre d'heures)

ENSEIGNE étant l iée à 3 entités, on

retrouve leurs trois identi fiants

concaténés pour faire ensemble la clé

primaire de la relation ENSEIGNE.

L'association étant porteuse de

propriété, on retrouve cel le-ci comme

attribut de la relation.

DEES INFORMATIQUE

11

V 1.0.0.a

Schéma Relationnel correspondant au Schéma Conceptuel de

l'exemple :

ÉLÈVE (Numéro, Nom, Prénom, Adresse, Vi l le, Code postal, Date

naissance, # NumClasse)

PROFESSEUR (Numéro, Nom, Prénom, Adresse, Vi l le, Code postal)

CLASSE (Numéro, Libel lé)

MATIÈRE (Code, Libel lé)

ENSEIGNE (# NumProf + # NumClasse + # Code, Nbre d'heures)

Les tables dans le SGBDR :

Dans un logiciel de type SGBDR, les relations du schéma relationnel sont

présentées sous forme de tables.

Les tables sont en fait des tableaux où les colonnes ont pour ti tre les

attributs des relations, et où une l igne comportera une valeur pour chaque

attribut.

1.3 Caractéristiques et spécificités du SGBD Oracle

Introduction au SGBD Oracle

Oracle est un SGBD (système de gestion de bases de données) édité par la

société du même nom (Oracle Corporation - http://www.oracle.com),

leader mondial des bases de données.

La société Oracle Corporation a été créée en 1977 par Lawrence El l ison,

Bob Miner, et Ed Oates. El le s'appel le alors Relat ional Software

Incorporated (RSI) et commercial ise un Système de Gestion de Bases de

données relationnelles (SGBDR ou RDBMS pour Relat ional Database

Management System) nommé Oracle.

En 1979, le premier prototype (RDBMS - RSI1) intégrant la séparation des

espaces d'adressage entre les programmes uti l isateurs et le noyau Oracle

est commercial isé. Cette version est entièrement développée en langage

assembleur. La seconde version (RDBMS - RSI2) est un portage de

l 'appl ication sur d'autres plates-formes.

En 1983 la troisième version apporte des améliorations au niveau des

performances et une mei l leure prise en charge du SQL. Cette version est

entièrement codée en langage C. A la même époque RSI change de raison

sociale et devient Oracle.

DEES INFORMATIQUE

12

V 1.0.0.a

En 1984, la première version d'Oracle (Oracle 4) est commercial isée sur

les machines IBM.

En 1985, Oracle 5 permet une uti l isation cl ient-serveur grâce au

middleware SQL*Net.

En 1986, Oracle a été porté sur la plateforme 8086.

En 1988, Oracle 6 est disponible sur un grand nombre de plates-formes et

apporte de nombreuses nouvel les fonctionnal ités ainsi qu'une amélioration

notable des performances.

En 1991, Oracle 6.1 propose une option Paral lel Server (dans un premier

temps sur la DEC VAX, puis rapidement sur de nombreuses autres plates-

formes).

En 1992, Oracle 7 sort sur les plates-formes UNIX (el le ne sortira sur les

plates-formes Windows qu'à parti r de 1995). Cette version permet une

mei l leure gestion de la mémoire, du CPU et des entrées-sorties. La base

de données est accompagnée d'outi ls d'administration (SQL*DBA)

permettant une exploitation plus aisée de la base.

En 1997, la version Oracle 7.3 (baptisée Oracle Universal Server)

apparaît, suivie de la version 8 offrant des capacités objet à la base de

données et les appl ications multimédia.

En 1999, la version 8i est publ iée dans le but d'affiner ses appl ications

avec Internet. La base de données comporte nativement une machine

virtuel le Java.

En 2001, Oracle 9i ajoute 400 nouvel les fonctionnalités et permet de l ire

et d'écrire des documents XML.

À partir de la version 9i, intégration du moteur OLAP au sein d’ Oracle : le

moteur Oracle express est dorénavant référencé au sein de l ’option Oracle

OLAP. Les données multidimensionnelles sont accessibles à partir du

langage SQL.

DEES INFORMATIQUE

13

V 1.0.0.a

En 2003, la version 10g est publ iée. Le g signifie « grid » ; un des atouts

marketing de la 10g est en effet qu'el le supporte le « grid computing ».

En 2005, vers la f in novembre, une version complètement gratuite est

publ iée, la « Oracle Database 10g Express Edition ».

Le 20 avri l 2009, Oracle acquiert Sun Microsystems.

En septembre 2009, sortie de Oracle 11g Release 2.

Oracle est écrit en langage C et est disponible sur de nombreuses

plates-formes matérielles (plus d'une centaine) dont :

AIX (IBM)

Solaris (Sun)

HP/UX (Hewlett Packard)

Windows NT (Microsoft)

Linux

Les versions

Oracle se décline en plusieurs versions

Oracle Server Standard (4 processeurs max) et Standard One (2

processeurs max), une version comprenant les outi ls les plus

courants de la solution Oracle. Il ne s'agit pas pour autant d'une

version bridée...

Oracle Server Enterprise Edition

Les fonctionnalités

Oracle est un SGBD permettant d'assurer :

La définition et la manipulation des données

La cohérence des données

La confidential i té des données

L'intégrité des données

La sauvegarde et la restauration des données

La gestion des accès concurrents

Les composants

Outre la base de données, la solution Oracle est un véritable

environnement de travai l constitué de nombreux logiciels permettant

notamment une administration graphique d'Oracle, de s'interfacer avec

DEES INFORMATIQUE

14

V 1.0.0.a

des produits divers et d'assistants de création de bases de données et de

configuration de cel les-ci.

On peut classer les outils d'Oracle selon diverses catégories :

Les outi ls d'administration

Les outi ls de développement

Les outi ls de communication

Les outi ls de génie logiciel

Les outi ls d'aide à la décision

Les outils d'administration

Oracle est fourni avec de nombreux outi ls permettant de simpl i fier

l 'administration de la base de données.

Parmi ces outils, les plus connus sont :

Oracle Manager (SQL*DBA)

NetWork Manager

Oracle Enterprise Manager

Import/Export : un outi l permettant d'échanger des données entre

deux bases Oracle

Outils de développement

Oracle propose également de nombreux outi ls de développement

permettant d'automatiser la création d'appl ications s'interfaçant avec la

base de données.

Ces outils de développement sont :

Oracle Designer

Oracle Developer

SQL*Plus : une interface interactive permettant d'envoyer des

requêtes SQL et PL/SQL à la base de données. SQL*Plus permet

notamment de paramétrer l 'environnement de travai l (formatage des

résultats, longueur d'une l igne, nombre de l ignes par page, ...)

Oracle Developper : i l s'agit d'une suite de produits destinés à la

conception et à la création d'appl ications cl ient-serveur. Il est

composé de 4 appl ications :

DEES INFORMATIQUE

15

V 1.0.0.a

� Oracle Forms (anciennement SQL*Forms) : un outi l permettant

d'interroger la base de données de façon graphique sans

connaissances préalables du langage SQL. SQL*Forms permet

ainsi de développer des appl ications graphiques (fenêtres,

formulaires, ...) permettant de sélectionner, modifier et

supprimer des données dans la base.

� Oracle Reports (SQL*ReportWriter) : un outi l permettant de

réal iser des états

� Oracle Graphics : un outi l de génération automatique de

graphiques dynamiques pour présenter graphiquement des

statistiques réal isées à partir des données de la base

� Procedure Bui lder : un outi l permettant de développer des

procédures, des fonctions et des packages

Outils de programmation

Oracle dispose d'un grand nombre d' interfaces (API) permettant à des

programmes écrits dans divers langages de s'interfacer avec la base de

données en envoyant des requêtes SQL. Ces interfaces (appelées

précompilateurs) forment une famil le dont le nom commence par PRO* :

Pro*C

Pro*Cobol

Pro*Fortran

Pro*Pascal

Pro*PLI

...

Architecture du SGBD

Une base de données Oracle est constituée de plusieurs éléments :

Des processus chargés en mémoire sur le serveur

Des fichiers physiques stockés sur le serveur

Un espace mémoire sur le serveur appelé SGA (System Global Area)

DEES INFORMATIQUE

16

V 1.0.0.a

On appel le instance Oracle les processus et la SGA d'une base de données

Oracle.

Les fichiers physiques d'une base Oracle

Les fichiers physiques d'une base Oracle permettent de stocker de manière

persistante les données manipulées par Oracle, tandis que la mémoire sert

à optimiser la vitesse de fonctionnement de la base de données.

On distingue généralement deux types de fichiers :

Les fichiers servant à stocker les informations de la base. Tous ces

fichiers sont des fichiers binaires, ce qui signifie qu'i ls sont

inexploitables avec un éditeur de texte.

Les fichiers destinés à la configuration et au fonctionnement de la

base Oracle.

Oracle a défini une architecture permettant de définir une méthode

d'organisation standard des fichiers de la base Oracle. Cette architecture

est nommée OFA (Optimal Flexible Architecture).

Les fichiers d'une base de données Oracle sont les suivants :

Les fichiers de données (dont l 'extension est .dbf). Ces fichiers

contiennent l 'ensemble des données de la base (les tables, les vues,

les procédures stockées, ...).

Les fichiers Redo Log (dont l 'extension est .rdo ou .log). Ces fichiers

contiennent l 'historique des modifications effectuées sur la base de

données.

DEES INFORMATIQUE

17

V 1.0.0.a

Les fichiers de contrôle (dont l 'extension est .ctl). Ces fichiers

permettent de stocker les informations sur l 'état de la base de

données (emplacement des fichiers, dates de création, ...).

Une base de données Oracle nécessite au minimum un fichier de données,

deux fichiers redo Log et un fichier de contrôle.

Le dictionnaire de données

A- Présentation du dictionnaire de données Oracle

Le dictionnaire de données Oracle représente le cœur de la base de

données. Il s 'agit d'un ensemble de tables systèmes contenant les

informations relatives à la structure de la base de données :

Uti l isateurs de la base (ainsi que leurs privi lèges et leur rôle)

Noms et caractérist iques des objets contenus dans la base (tables,

vues, index, clusters, triggers, packages, ...)

Contraintes d'intégrité

Ressources physiques al louées à la base

...

Le dictionnaire est créé au moment de la création de la base et est mis à

jour.

Il appartient à l 'ut i l isateur SYS, mais l 'uti l isateur SYSTEM (c'est-à-dire

l 'administrateur de la base), possède des droits de lecture sur des vues du

dictionnaire. Enfin, le dictionnaire de données est conservé dans le

tablespace SYSTEM.

Le dictionnaire de données sert principalement dans deux

situations :

Afin de véri fier la syntaxe et les privi lèges sur une requête SQL

(pour le DDL, Data Definit ion Language)

Afin d'obtenir des informations sur la structure de la base de

données

B- Les différentes vues

De nombreuses vues permettent à des uti l isateurs d'accéder à certaines

parties du dictionnaire de données. Les vues fournissent à l 'administrateur

de la base le mei l leur moyen pour obtenir les caractéristiques techniques

de cel le-ci.

DEES INFORMATIQUE

18

V 1.0.0.a

Les vues du dictionnaire de données sont classées par famille et nommées

en fonction de l 'appartenance à une de ces familles. Voici la l iste de ces

famil les de vues :

Les vues USER (dont le nom commence par USER_) donnent des

informations sur tous les objets logiques dont l 'uti l isateur connecté

est propriétaire (tables, index, vues, procédures, ...)

Les vues ALL (dont le nom commence par ALL_) fournissent des

informations sur les objets pour lesquels l 'uti l isateur a un droit

d'accès, c'est-à-dire les objets de la base créés par l 'uti l isateur ainsi

que tous les objets accessibles par cet uti l isateur.

Les vues DBA (dont le nom commence par DBA_). Ces vues sont

réservées à l 'administrateur de la base (DBA, DataBase

Administrator) afin de lui fournir des informations sensibles sur tous

les objets de la base de données.

Les vues V$ (dont le nom commence par V$_) sont des vues

dynamiques permettant d'avoir des informations sur l 'état courant

de l ' instance de la base de données de son démarrage à son arrêt.

El les permettent par exemple, de connaître les fichiers physiques

actuel lement uti l isés par la base (logs, rol lback segments, ...).

1.4 Langage Interrogation des Données (LID)

Les exemples suivants sont bâtis sur une base de données composée

des deux relations suivantes :

EMP (NOM, NUM, FONCTION, N_SUP, EMBAUCHE, SALAIRE, COMM,

N_DEPT)

DEPT (N_DEPT, NOM,LIEU)La clause SELECT

La commande SELECT constitue, à el le seule, le langage permettant

d'interroger une base de données. El le permet :

de sélectionner certaines colonnes d'une table : c'est l 'opération de

projection ;

de sélectionner certaines l ignes d'une table en fonction de leur

contenu : c'est l 'opération de restriction ;

de combiner des informations venant de plusieurs tables : ce sont

les opérations de jointure, union, intersection, différence

relationnel le ;

de combiner entre elles ces différentes opérations.

DEES INFORMATIQUE

19

V 1.0.0.a

Une interrogation, on parle plutôt de requête, est une combinaison

d'opérations portant sur des tables (relations) et dont le résultat est lui-

même une table dont l 'existence est éphémère (le temps de la requête).

Note :

On peut introduire un commentaire à l ' intérieur d'une commande SQL en

l 'encadrant par /* */.

Sélection de colonnes ou projection

La commande SELECT la plus simple a la syntaxe suivante :

SELECT *

FROM nom_table ;

Dans laquelle :

nom_table : est le nom de la table sur laquel le porte la sélection.

* : signifie que toutes les colonnes de la table sont sélectionnées.

Par défaut toutes les l ignes sont sélectionnées. On peut l imiter la sélection

à certaines colonnes, en indiquant une l iste de noms de colonnes à la

place de l 'astérisque.

SELECT nom_col1, nom_col2, ...

FROM nom_table ;

Exemple : Donner le nom et la fonction de chaque employé.

SELECT nom, fonction FROM emp;

La clause DISTINCT ajoutée derrière la commande SELECT permet

d'él iminer les dupl ications.

Exemple : Quel les sont toutes les fonctions différentes.

SELECT DISTINCT fonction FROM emp;

Sélection de lignes ou restriction

La clause WHERE permet de spécifier quel les sont les l ignes à

sélectionner. El le est suivie d'un prédicat qui sera évalué pour chaque

DEES INFORMATIQUE

20

V 1.0.0.a

l igne de la table. Les l ignes pour lesquel les le prédicat est vrai seront

sélectionnées.

La syntaxe est la suivante :

SELECT *

FROM nom_table

WHERE predicat ;

Un prédicat n'est ni plus ni moins que la façon dont on exprime une

propriété. Les prédicats, qu'i ls soient simples ou composés, sont

constitués à partir d'expressions que l 'on compare entre el les.

Une expression simple peut être :

une variable désignée par un nom de colonne,

une constante.

Les expressions peuvent être de trois types : numérique, chaîne de

caractères ou date.

A chacun de ces types correspond un format de constante :

Constante numérique

� nombre contenant éventuel lement un signe, un point décimal

et une puissance de dix. Ex : -10, 2.5, 1.2 E-10

Constante chaîne de caractères

� une chaîne de caractères entre apostrophes. Ex : 'MARTIN'

(Attention, une lettre en majuscules n'est pas considérée

comme égale à la même lettre en minuscule).

Constante date

� Une chaîne de caractères entre apostrophes au format suivant

: jour-mois-année où le jour est sur deux chiffres, le mois est

désigné par les trois premières lettres de son nom en anglais,

l 'année est sur deux chiffres. Ex : '01-FEB-85'.

On peut, en SQL, exprimer des expressions plus complexes en uti l isant des

opérateurs et des fonctions.

Prédicat simple

Un prédicat simple est le résultat de la comparaison de deux expressions

au moyen d'un opérateur de comparaison qui peut être :

DEES INFORMATIQUE

21

V 1.0.0.a

= égal

!= différent

< inférieur

<= inférieur ou égal

> supérieur

>= supérieur ou égal

Les trois types d'expressions peuvent être comparés au moyen de

ces opérateurs :

Pour les types date, la relation d'ordre est l 'ordre chronologique.

Pour les types caractère, la relation d'ordre est l 'ordre alphabétique.

Il faut ajouter à ces opérateurs arithmétiques classiques les

opérateurs suivants :

expr1 BETWEEN expr2 AND expr3

vrai si expr1 est compris entre expr2 et expr3, bornes incluses

expr1 IN (expr2, expr3, ...)

vrai si expr1 est égale à l 'une des expressions de la l iste entre

parenthèses

expr LIKE chaine

où chaine est une chaîne de caractères pouvant contenir l 'un

des caractères jokers :

� _ remplace exactement 1 caractère

� % remplace une chaîne de caractères de longueur quelconque,

y compris de longueur nul le.

Exemple : Quels sont les employés dont la commission est supérieure au

salaire ?

SELECT nom, salaire, comm FROM emp WHERE comm > salaire;

Exemple : Quels sont les employés gagnant entre 20000 et 25000?

SELECT nom, salaire FROM emp WHERE salaire BETWEEN 20000 AND

25000;

Exemple : Quels sont les employés commerciaux ou ingénieurs?

SELECT num, nom, fonction, salaire FROM emp WHERE fonction IN

('commercial ', ' ingenieur');

DEES INFORMATIQUE

22

V 1.0.0.a

Exemple : Quels sont les employés dont le nom commence par M?

SELECT nom FROM emp WHERE nom LIKE 'M%';

Prédicats composés

Les opérateurs logiques AND (et) et OR (ou inclusif) peuvent être uti l isés

pour combiner entre eux plusieurs prédicats. L'opérateur NOT placé

devant un prédicat en inverse le sens.

L'opérateur AND est prioritaire par rapport à l 'opérateur OR. Des

parenthèses peuvent être uti l isées pour imposer une priorité dans

l 'évaluation du prédicat, ou simplement pour rendre plus claire

l 'expression logique.

Exemple : Quels sont les employés du département 30 ayant un salaire

supérieur à 25000?

SELECT nom FROM emp WHERE n_dept = 30 AND salaire > 25000;

Exemple : Quels sont les employés directeurs, ou commerciaux et

travai l lant dans le département 10?

SELECT nom, fonct ion, salaire, n_dept FROM emp WHERE fonction =

'directeur' OR (fonct ion = 'commercial ' AND n_dept = 10);

La requête précédente donnerait le même résultat sans les parenthèses,

résultat différent de celui du SELECT suivant.

Exemple : Quels sont les employés directeurs ou commerciaux, et

travai l lant dans le département 10?

SELECT num, nom, fonction, n_dept FROM emp WHERE

(fonction='directeur' OR fonction = 'commercial ') AND n_dept = 10;

Valeurs NULL

Pour SQL, une valeur NULL est une valeur non définie. Il est possible

d'ajouter une l igne à une table sans spécifier de valeur pour les colonnes

non obl igatoire : ces colonnes absentes auront la valeur NULL .

Par exemple les employés dont la rémunération ne prend pas en compte de

commission auront une valeur NULL, c'est-à-dire indéfinie, comme

commission.

DEES INFORMATIQUE

23

V 1.0.0.a

L'opérateur IS NULL permet de tester la valeur NULL : le prédicat expr IS

NULL est vrai si l 'expression a la valeur NULL (c'est-à-dire si el le est

indéfinie).

Exemple : Quels sont les employés dont la commission a la valeur NULL?

SELECT nom FROM emp WHERE comm IS NULL;

L'opérateur IS NOT NULL permet de construire un prédicat vrai si la

valeur n'est pas NULL (et donc le prédicat expr IS NOT NULL est vrai si

expr est définie).

Remarques :

La valeur NULL est différente de la valeur zéro qui, el le, est une

valeur bien définie.

Le prédicat expr = NULL est toujours faux, et ne permet donc pas

de tester si l 'expression a la valeur NULL .

Une expression de la forme NULL + val donne NULL comme résultat

quel le que puisse être la valeur de val.

Nom de colonne

Les colonnes constituant le résultat d'un SELECT peuvent être renommées

dans le SELECT , ceci est uti le en particulier lorsque la colonne résultat est

une expression.

Pour cela, il suffit de faire suivre l'expression définissant la colonne

d'un nom, selon les règles suivantes :

le nom (30 caractères maximum) est inséré derrière l 'expression

définissant la colonne, séparé de cette dernière par un espace.

si le nom contient des séparateurs (espace, caractère spécial), ou

s'i l est identique à un mot clé de SQL (ex : DATE), i l doit être mis

entre gui l lemets "".

Ce nom est celui sous lequel la colonne sera connue des interfaces

externes. Sous SQLPLUS (uti l i taire en l igne de commande d'Oracle qui

permet aux uti l isateurs d'exécuter interactivement des commandes SQL et

PL/SQL), par exemple, i l constituera le ti tre par défaut de la colonne, et

servira de référence pour définir un format pour la colonne.

Exemple : Salaire de chaque employé.

SELECT nom, salaire "SALAIRE MENSUEL" FROM emp;

DEES INFORMATIQUE

24

V 1.0.0.a

Remarque : Attention, ce nom n'est pas connu à l ' intérieur du SELECT .

Classer le résultat d'une interrogation

Les l ignes constituant le résultat d'un SELECT sont obtenues dans un

ordre indéterminé. On peut, dans un SELECT , demander que le résultat

soit classé dans un ordre ascendant ou descendant, en fonction du contenu

d'une ou plusieurs colonnes (jusqu'à 16 critères de classement possibles).

Les critères de classement sont spécifiés dans une clause ORDER BY dont

la syntaxe est la suivante :

ORDER BY {nom_col1 | num_col1 [DESC] [, nom_col2 | num_col2

[DESC],...]}

Le classement se fait d'abord selon la première colonne spécifiée dans

l 'ORDER BY puis les l ignes ayant la même valeur dans la première colonne

sont classées selon la deuxième colonne de l 'ORDER BY, etc... Pour

chaque colonne, le classement peut être ascendant (par défaut) ou

descendant (DESC).

L'ORDER BY peut faire référence à une colonne par son nom ou par sa

position dans la l iste des colonnes présentes derrière le SELECT (la

première colonne sélectionnée a le numéro 1, la deuxième a le numéro 2,

...).

Exemple : Donner tous les employés classés par fonction, et pour chaque

fonction classés par salaire décroissant:

SELECT nom, fonct ion, salaire FROM emp ORDER BY fonction, salaire

DESC;

Remarque : Dans un classement les valeurs NULL sont toujours en tête

quel que soit l 'ordre du classement (ascendant ou descendant).

Syntaxe globale du Select

SELECT [DISTINCT | ALL]

{ *

| { [schema.]{table | view | snapshot}.* | expr [c_al ias]

[, { [schema.]{table | view | snapshot}.* | expr [c_al ias] } ] ... }

FROM [schema.]{table | view | snapshot} [t_al ias]

DEES INFORMATIQUE

25

V 1.0.0.a

[, [schema.]{table | view | snapshot} [t_al ias] ] ...

[WHERE condition ]

[ [START WITH condition] CONNECT BY condition]

[GROUP BY expr [, expr] ... [HAVING condition] ]

[{UNION | UNION ALL | INTERSECT | MINUS} SELECT command ]

[ORDER BY {expr|position} [ASC | DESC] [, {expr | position} [ASC |

DESC]] ...]

[FOR UPDATE [OF [[schema.]{table | view}.]column

[, [[schema.]{table | view}.]column] ...] [NOWAIT] ]

DISTINCT

Renvoie toutes les l ignes sélectionnées en enlevant les doublons.

ALL

Renvoie toutes les l ignes sélectionnées sans enlever les doublons. C'est la

valeur par défaut.

*

Renvoie toutes les colonnes de toutes les tables, les vues et les cl ichés

précisés dans le FROM.

table.*, view.*, snapshot.*

Sélectionne toutes les colonnes de la table, de la vue ou du cl iché précisé.

expr

Sélectionne une expression habituel lement calculée sur les valeurs des

colonnes appartenant à l 'une des tables, vues, ou cl ichés de la clause

FROM.

c_alias

La chaîne de caractères qui sert d'en-tête à la colonne (par défaut expr).

schema

Est le nom du schéma contenant les tables, vues ou cl ichés sélectionnés.

Le schéma par défaut est celui de l 'uti l isateur qui exécute la requête.

table, view, snapshot

Est le nom de la table, de la vue ou du cl iché contenant les données

sélectionnées.

DEES INFORMATIQUE

26

V 1.0.0.a

t_alias

Synonyme pour la table dont le nom précède, à uti l iser dans le reste de la

requête.

WHERE

Restreint les l ignes sélectionnées à cel les pour lesquel les la condition est

vraie. Si cette clause est omise, toutes les l ignes des tables, vues ou

cl ichés précisés derrière le FROM sont renvoyées.

START WITH, CONNECT BY

Renvoie les l ignes en parcourant une arborescence.

GROUP BY

Groupe les l ignes sélectionnées en se basant sur la valeur de expr pour

chaque l igne et renvoie une seule l igne par groupe.

HAVING

Restreint les groupes de l ignes renvoyés à ceux pour lesquels la condition

spécifiée est vraie. Sans cette clause, tous les groupes sont renvoyés.

UNION, UNION ALL, INTERSECT, MINUS

Combine les l ignes retournées par deux SELECT en uti l isant une opération

ensembliste.

ORDER BY

Ordonne les lignes sélectionnées :

� expr

En uti l isant la valeur de expr. Cette expression est basée sur des colonnes

précisées derrière le SELECT ou sur des colonnes appartenant à des

tables, vues ou cl ichés présents derrière le FROM.

� position

Donne le numéro de la colonne dans l 'ordre du SELECT.

� ASC, DESC

Mode ascendant ou descendant. La valeur par défaut ASC.

FOR UPDATE

"locke" les l ignes sélectionnées.

DEES INFORMATIQUE

27

V 1.0.0.a

NO WAIT

Retourne le contrôle à l 'uti l isateur si la commande SELECT essaye de

bloquer une table ut i l isée par un autre uti l isateur.

Prérequis :

Pour pouvoir sélectionner des l ignes d'un objet (table, vue, cl iché, i l faut

soit être propriétaire de cet objet, soit avoir le privi lège SELECT sur cet

objet.

Le privi lège SELECT ANY TABLE permet de sélectionner des l ignes de

n'importe quel objet appartenant à n'importe quel uti l isateur.

Expressions et fonctions single-row

Une expression est un ensemble de variables (contenu d'une colonne), de

constantes et de fonctions combinées au moyen d'opérateurs. Les

fonctions prennent une valeur dépendant de leurs arguments qui peuvent

être eux-mêmes des expressions.

Les expressions peuvent figurer :

en tant que colonne résultat d'un

SELECT ,

dans une clause

WHERE,

dans une clause

ORDER BY.

Il existe trois types d'expressions correspondant chacun à un type de

données de SQL : arithmétique, chaîne de caractère, date.

A chaque type correspondent des opérateurs et des fonctions spécifiques.

SQL autorise les mélanges de types dans les expressions et effectuera les

conversions nécessaires : dans une expression mélangeant dates et

chaînes de caractères, les chaînes de caractères seront converties en

dates, dans une expression mélangeant nombres et chaînes de caractères,

les chaînes de caractères seront convert ies en nombre.

A- Expressions et fonctions arithmétiques

Une expression arithmétique peut contenir :

des noms de colonnes

des constantes

DEES INFORMATIQUE

28

V 1.0.0.a

des fonctions arithmétiques combinés au moyen des opérateurs

arithmétiques.

Les opérateurs arithmétiques présents dans SQL sont les suivants:

+ addition ou + unaire

- soustraction ou - unaire

* multiplication

/ division

Remarque : la division par 0 provoque une fin avec code d'erreur.

Priorité des opérateurs :

Une expression arithmétique peut comporter plusieurs opérateurs. Dans ce

cas, le résultat de l 'expression peut varier selon l 'ordre dans lequel sont

effectuées les opérations. Les opérateurs de multipl ication et de division

sont prioritaires par rapport aux opérateurs d'addition et de soustraction.

Des parenthèses peuvent être uti l isées pour forcer l 'évaluation de

l 'expression dans un ordre différent de celui découlant de la priorité des

opérateurs.

Exemple : Donner pour chaque commercial son revenu (salaire +

commission).

SELECT nom, salaire+comm FROM emp WHERE fonction = 'commercial ';

Exemple : Donner la l iste des commerciaux classée par commission sur

salaire décroissant.

SELECT nom, comm/salaire, comm, salaire FROM emp WHERE fonction =

'commercial ' ORDER BY comm/salaire DESC;

Exemple : Donner la l iste des employés dont la commission est inférieure

à 5% du salaire.

SELECT nom, salaire, comm FROM emp WHERE comm <= salaire *.05;

Les Fonctions arithmétiques :

Dans ce paragraphe, ont été regroupées les fonctions ayant un ou

plusieurs nombres comme arguments, et renvoyant une valeur numérique.

ABS(nb) : Renvoie la valeur absolue de nb.

CEIL(nb) : Renvoie le plus petit entier supérieur ou égal à nb.

DEES INFORMATIQUE

29

V 1.0.0.a

COS(n) : Renvoie le cosinus de n, n étant un angle exprimé en

radians.

COSH(n) : Renvoie le cosinus hyperbol ique de n.

EXP(n) : Renvoie e puissance n.

FLOOR(nb) : Renvoie le plus grand entier inférieur ou égal à nb.

LN(n) : Renvoie le logarithme népérien de n qui doit être un entier

strictement positi f.

LOG(m,n) : Renvoie le logarithme en base m de n. m doit être un

entier strictement supérieur à 1, et n un entier strictement positi f.

MOD(m,n) : Renvoie le reste de la division entière de m par n, si n

vaut 0 alors renvoie m. Attention, uti l isée avec au moins un de ses

arguments négati fs, cette fonction donne des résultats qui peuvent

être différents d'un modulo classique. Cette fonction ne donne pas

toujours un résultat dont le signe du diviseur.

POWER(m,n) : Renvoie m puissance n, m et n peuvent être des

nombres quelconques entiers ou réels mais si m est négati f n doit

être un entier.

ROUND(n[,m]) : Si m est positi f, renvoie n arrondi (et non pas

tronqué) à m chiffres après la virgule. Si m est négati f, renvoie n

arrondi à m chiffres avant la vi rgule. m doit être un entier et i l vaut

0 par défaut.

SIGN(nb) : Renvoie -1 si nb est négati f, 0 si nb est nul, 1 si nb est

positi f.

SIN(n) : Renvoie le sinus de n, n étant un angle exprimé en

radians.

SINH(n) : Renvoie le sinus hyperbol ique de n.

SQRT(nb) : Renvoie la racine carrée de nb qui doit être un entier

positi f ou nul.

TAN(n) : Renvoie la tangente de n, n étant un angle exprimé en

radians.

TANH(n) : Renvoie la tangente hyperbolique de n.

TRUNC(n[,m]) : Si m est positi f, renvoie n arrondi tronqué à m

chiffres après la virgule. Si m est négati f, renvoie n tronqué à m

chiffres avant la virgule. m doit être un entier et i l vaut 0 par

défaut.

Exemple : Donner pour chaque employé son salaire journal ier.

SELECT nom, ROUND (salaire/22,2) FROM emp;

DEES INFORMATIQUE

30

V 1.0.0.a

B- Expressions et fonctions sur les chaînes de caractères

Opérateur sur les chaînes de caractères :

Il existe un seul opérateur sur les chaînes de caractères : la

concaténation. Cet opérateur se note au moyen de deux caractères |(barre

verticale) accolés. Le résultat d'une concaténation est une chaîne de

caractères obtenue en écrivant d'abord la chaîne à gauche de || puis cel le

à droite de ||.

Exemple :

SELECT nom || '/ ' || fonction FROM emp;

Fonctions sur les chaînes de caractères :

Le paragraphe suivant contient les fonctions travail lant sur les chaînes de

caractères et renvoyant des chaînes de caractères.

CONCAT(chaîne1,chaîne2) : Renvoie la chaîne obtenue en

concaténant chaîne1 à chaîne2. Cette fonction est équivalente à

l 'opérateur de concaténation | |.

INITCAP(chaîne) : Renvoie chaîne en ayant mis la première lettre

de chaque mot en majuscule et toutes les autres en minuscule. Les

séparateurs de mots sont les espaces et les caractères non

alphanumériques.

LOWER(chaîne) : Renvoie chaîne en ayant mis toutes ses lettres en

minuscules.

LPAD(chaîne,long,[char]) : Renvoie la chaîne obtenue en

complétant, ou en tronquant, chaîne pour qu'el le ait comme

longueur long en ajoutant éventuel lement à gauche le caractère (ou

la chaîne de caractères) char. La valeur par défaut de char est un

espace.

LTRIM(chaîne[,ens]) : Renvoie la chaîne obtenue en parcourant à

partir de la gauche chaîne et en supprimant tous les caractères qui

sont dans ens. On s'arrête quand on trouve un caractère qui n'est

pas dans ens. La valeur de defaut de ens est un espace.

REPLACE(chaine,avant,après) : Renvoie chaine dans laquel le

toutes les occurrences de la chaîne de caractères avant ont été

remplacés par la chaîne de caractères après.

RPAD(chaîne,n,[char]) : Renvoie la chaîne obtenue en complétant,

ou en tronquant, chaîne pour qu'el le ait comme longueur long en

ajoutant éventuel lement à droite le caractère (ou la chaîne de

caractères) char. La valeur par défaut de char est un espace.

DEES INFORMATIQUE

31

V 1.0.0.a

RTRIM(chaîne[,ens]) : Renvoie la chaîne obtenue en parcourant à

partir de la droite chaîne et en supprimant tous les caractères qui

sont dans ens. On s'arrête quand on trouve un caractère qui n'est

pas dans ens. La valeur de defaut de ens est un espace.

SOUNDEX(chaîne) : Renvoie la chaîne de caractères constituée de

la représentation phonétique des mots de chaîne.

SUBSTR(chaîne,m[,n]) : Renvoie la partie de chaîne commençant

au caractère m et ayant une longueur de n.

TRANSLATE(chaîne, avant, après) : Renvoie une chaîne de

caractères en remplaçant chaque caractère de chaîne présent dans

avant par le caractère situé à la même position dans après. Les

caractères de chaîne non présents dans avant ne sont pas modifiés.

avant peut contenir plus de caractères que après, dans ce cas les

caractères de avant sans correspondants dans après seront

supprimés de chaîne.

UPPER(chaîne) : Renvoie chaîne en ayant mis toutes ses lettres en

majuscules.

Le paragraphe suivant contient les fonctions travail lant sur les chaînes de

caractères et renvoyant des entiers.

INSTR(chaîne, sous-chaîne, debut, occ) : Renvoie la position du

premier caractère de chaîne correspondant à l 'occurrence occ de

sous-chaîne en commençant la recherche à la position début.

LENGTH(chaîne) : Renvoie la longueur de chaîne, exprimée en

nombre de caractères.

C- Expressions et fonctions sur les dates

Opérateurs sur les dates :

Au moyen des opérateurs arithmétiques + et - i l est possible de construire

les expressions suivantes :

date +/- nombre : le résultat est une date obtenue en ajoutant le

nombre de jours nombre à la date, date.

date2 - date1 : le résultat est le nombre de jours entre les deux

dates.

Fonctions sur les dates :

ADD_MONTHS(date,n) : Renvoie la date obtenue en ajoutant n

mois à date. n peut être un entier quelconque. Si le mois obtenu a

DEES INFORMATIQUE

32

V 1.0.0.a

moins de jours que le jour de date, le jour obtenu est le dernier du

mois.

LAST_DAY(date) : Renvoie la date du dernier jour du mois de date.

MONTHS_BETWEEN(date2, date1) : Renvoie le nombre de mois

entre date2 et date1, si date2 est après date1 le résultat est positi f,

sinon le résultat est négati f. Si les jours date2 et date1 sont les

mêmes, ou si ce sont les derniers jours du mois, le résultat est un

entier. La partie fractionnaire est calculée en considérant chaque

jour comme 1/31ème de mois

NEXT_DAY(date,nom_du_jour) : Renvoie la date du prochain jour

de la semaine dont le nom est nom_de_jour.

ROUND(date[,précision]) : Renvoie date arrondie à l 'unité

spécifiée dans précision. L'unité de précision est indiquée en

uti l isant un des masques de mise en forme de la date. On peut ainsi

arrondir une date à l 'année, au mois, à la minute,... Par défaut la

précision est le jour.

SYSDATE : Renvoie la date et l 'heure courantes du système

d'exploitation hôte.

TRUNC(date[,précision]) : Renvoie date tronquée à l 'unité

spécifiée dans précision. Les paramètres sont analogues à ceux de la

fonction ROUND.

Exemple : Donner la date du lundi suivant l 'embauche de chaque employé.

SELECT NEXT_DAY (embauche,'MONDAY') FROM emp;

Exemple : Donner la date d'embauche de chaque employé, arrondie à

l 'année

SELECT ROUND (embauche,'Y') FROM emp;

Exemple : Donner pour chaque employé, le nombre de jours depuis son

embauche.

SELECT ROUND (SYSDATE-embauche) FROM emp;

D- Fonctions de conversion

ASCII(chaîne) : Renvoie le nombre correspondant au code asci i du

premier caractère de chaine.

CHR(nombre) : Renvoie le caractère dont nombre est le code asci i .

DEES INFORMATIQUE

33

V 1.0.0.a

TO_CHAR(nombre, format) : Renvoie la chaîne de caractères

obtenue en convertissant nombre en fonction de format.

Format est une chaîne de caractères pouvant contenir les caractères

spécifiques suivant : 9 représente un chiffre (non représenté si non

significati f), un $ précédera le premier chiffre significati f…

TO_CHAR(date, format) : Renvoie la conversion d'une date en

chaîne de caractères. Le format indique quel le partie de la date doit

apparaître, c'est une combinaison des codes (Exemple : année yyyy,

numéro de la semaine dans le mois mm).

TO_DATE(chaîne, format) : Permet de convertir une chaîne de

caractères en donnée de type date. Le format est identique à celui

de la fonction TO_CHAR.

TO_NUMBER(chaîne) : Convertit chaîne en sa valeur numérique.

Remarque : On peut également insérer dans le format une chaîne de

caractères quelconque, à condition de la placer entre gui l lemets"".

Exemple : Donner la l iste des dates d'embauche de tous les employés

SELECT TO_CHAR (embauche,'DD/MM/YY HH24:MI:SS') FROM emp;

Exemple : Donner la l iste de tous les employés dont le nom ressemble à

DUPONT.

SELECT nom FROM emp WHERE SOUNDEX(nom) = SOUNDEX('DUPONT');

Exemple : Donner la l iste de tous les noms des employés en ayant

supprimé tous les 'L' et les 'E' en tête des noms.

SELECT LTRIM(nom,'LE') FROM emp;

Exemple : Donner la l iste de tous les noms des employés en ayant

remplacé les A et les M par des * dans les noms.

SELECT TRANSLATE (nom,'AM', '**') FROM emp;

Exemple : Afficher tous les salaires avec un $ en tête et au moins trois

chiffres (dont deux décimales).

SELECT TO_CHAR (salaire,'<MATH>99900.00') FROM emp;

DEES INFORMATIQUE

34

V 1.0.0.a

E- Autres fonctions

GREATEST(expr1, expr2,...) : Renvoie la plus grande des valeurs

expr1, expr2,.... Toutes les expressions sont converties au format

de expr1 avant comparaison.

LEAST(expr1, expr2,...) : Renvoie la plus petite des valeurs

expr1, expr2,.... Toutes les expressions sont converties au format

de expr1 avant comparaison.

NVL(expr_1, expr_2) : Prend la valeur expr_1, sauf si expr_1 est

NULL auquel cas NVL prend la valeur expr_2.

Une valeur NULL en SQL est une valeur non définie.

Lorsque l 'un des termes d'une expression a la valeur NULL,

l 'expression entière prend la valeur NULL. D'autre part, un prédicat

comportant une comparaison avec une expression ayant la valeur

NULL prendra toujours la valeur faux. La fonction NVL permet de

remplacer une valeur NULL par une valeur significative.

DECODE(crit, val_1, res_1 [, val_2, res_2 ...], def) : Cette

fonction permet de choisir une valeur parmi une l iste d'expressions,

en fonction de la valeur prise par une expression servant de critère

de sélection. Le résultat récupéré est :

� res_1 si l 'expression crit a la valeur val_1

� res_2 si l 'expression crit a la valeur val_2

� def (la valeur par défaut) si l 'expression crit n 'est égale à

aucune des expressions val_1, val_2,...,.

Les expressions résultats res_1, res_2, ..., def peuvent être de

types différents : caractère et numérique, ou caractère et date (le

résultat est du type de la première expression rencontré dans le

DECODE).

La fonction DECODE permet également de mélanger dans une

colonne résultat des informations venant de plusieurs colonnes d'une

même table.

Exemple : Donner pour chaque employé ses revenus (salaire +

commission). SELECT nom, salaire, comm, salaire+NVL(comm,0) FROM

emp;

Exemple : Donner la l iste des employés avec pour chacun d'eux sa

catégorie (président = 1, directeur = 2, autre = 3)

SELECT nom, DECODE (fonction,'president',1,'directeur',2,3) FROM emp;

DEES INFORMATIQUE

35

V 1.0.0.a

Exemple : Donner la l iste des employés en les identi fiant par leur fonction

dans le département 10 et par leur nom dans les autres départements.

SELECT DECODE (n_dept,10,fonction,nom)FROM emp;

Fonctions multiples rows et fonctions de groupe

Dans les exemples précédents, chaque l igne résultat d'un SELECT était le

résultat de calculs sur les valeurs d'une seule l igne de la table consultée.

Il existe un autre type de SELECT qui permet d'effectuer des calculs sur

l 'ensemble des valeurs d'une colonne.

A- Les fonctions

Ces calculs sur l'ensemble des valeurs d'une colonne se font au

moyen de l'une des fonctions suivantes :

AVG([DISTINCT | ALL] expression) : Renvoie la moyenne des

valeurs d'expression.

COUNT(* | [DISTINCT | ALL] expression) : Renvoie le nombre de

l ignes du résultat de la requête. Si expression est présent, on ne

compte que les l ignes pour lesquel les cette expression n'est pas

NULL.

MAX([DISTINCT | ALL] expression) : Renvoie la plus petite des

valeurs d'expression.

MIN([DISTINCT | ALL] expression) : Renvoie la plus grande des

valeurs d'expression.

STDDEV([DISTINCT | ALL] expression) : Renvoie l 'écart-type des

valeurs d'expression.

SUM([DISTINCT | ALL] expression) : Renvoie la somme des

valeurs

VARIANCE([DISTINCT | ALL] expression) : Renvoie la variance

des valeurs d'expression.

DISTINCT : Indique à la fonction de groupe de ne prendre en

compte que des valeurs distinctes.

ALL : Indique à la fonction de groupe de prendre en compte toutes

les valeurs, c'est la valeur par défaut.

Exemple : Donner le total des salaires du département 10.

SELECT SUM(salaire) FROM emp WHERE n_dept = 10;

DEES INFORMATIQUE

36

V 1.0.0.a

Exemple : Donner le nom, la fonction et le salaire de l 'employé (ou des

employés) ayant le salaire le plus élevé.

SELECT nom, fonction, salaire FROM emp WHERE salaire = (SELECT

MAX(salaire) FROM emp);

Remarques :

Ces SELECT sont différents de ceux vus précédemment. Il est, par

exemple, impossible de demander en résultat à la fois une colonne

et une fonction de groupe.

Un SELECT comportant une fonction de groupe peut être uti l isé dans

une sous-interrogation.

B- Valeurs NULL

Aucune des fonctions de groupe ne t ient compte des valeurs NULL à

l 'exception de COUNT(*). Ainsi, SUM(col) est la somme des valeurs non

NULL de la colonne col. De même AVG est la somme des valeurs non

NULL divisée par le nombre de valeurs non NULL .

C- Calcul sur plusieurs groupes

Il est possible de subdiviser la table en groupes, chaque groupe étant

l 'ensemble des l ignes ayant une valeur commune. C'est la clause GROUP

BY qui permet de découper la table en plusieurs groupes :

GROUP BY expr_1, expr_2, ...

Si on a une seule expression, ceci définit les groupes comme les

ensembles de l ignes pour lesquel les cette expression prend la même

valeur. Si plusieurs expressions sont présentes les groupes sont définis de

la façon suivante : parmi toutes les l ignes pour lesquel les expr_1 prend la

même valeur, on regroupe cel les ayant expr_2 identique, ... Un SELECT

de groupe avec une clause GROUP BY donnera une l igne résultat pour

chaque groupe.

Exemple : Total des salaires pour chaque département

SELECT SUM(salaire), n_dept FROM emp GROUP BY n_dept;

DEES INFORMATIQUE

37

V 1.0.0.a

Remarque : Dans la l iste des colonnes résultat d'un SELECT comportant

une fonction de groupe, ne peuvent f igurer que des caractéristiques de

groupe, c'est-à-dire :

soit des fonctions de groupe ;

soit des expressions figurant dans le GROUP BY.

D- Sélection des groupes

De la même façon qu'i l est possible de sélectionner certaines l ignes au

moyen de la clause WHERE, i l est possible dans un SELECT comportant

une fonction de groupe de sélectionner par la clause HAVING, qui se place

après la clause GROUP BY.

Le prédicat dans la clause HAVING suit les mêmes règles de syntaxe

qu'un prédicat figurant dans une clause WHERE .

Cependant, i l ne peut porter que sur des caractéristiques du groupe :

fonction de groupe ou expression figurant dans la clause GROUP BY, dans

ce cas la clause HAVING doit être placée après la clause GROUP BY.

Exemple : Donner la l iste des salaires moyens par fonction pour les

groupes ayant plus de deux employés.

SELECT fonction, COUNT(*), AVG(salaire) FROM emp GROUP BY function

HAVING COUNT(*) > 2;

Remarque : Un SELECT de groupe peut contenir à la fois une clause

WHERE et une clause HAVING. La clause WHERE sera d'abord appl iquée

pour sélectionner les l ignes, puis les groupes seront constitués à partir

des l ignes sélectionnées, et les fonctions de groupe seront évaluées.

Exemple : Donner le nombre d'ingénieurs ou de commerciaux des

départements ayant au moins deux employés de ces catégories.

SELECT n_dept, COUNT(*) FROM emp WHERE fonction in

('ingenieur', 'commercial ') GROUP BY n_dept HAVING COUNT(*) >= 2;

Une clause HAVING peut comporter une sous-interrogation.

Exemple : Quel est le département ayant le plus d'employés?

SELECT n_dept, COUNT(*) FROM emp GROUP BY n_dept HAVING COUNT(*)

= (SELECT MAX(COUNT(*)) FROM emp GROUP BY n_dept) ;

DEES INFORMATIQUE

38

V 1.0.0.a

E- Fonction de groupe à deux niveaux

Il est possible d'appl iquer au résultat d'un SELECT avec GROUP BY un

deuxième niveau de fonction de groupe.

Exemple : la fonction MAX peut être appl iquée aux nombres d'employés de

chaque département pour obtenir le nombre d'employés du département

ayant le plus d'employés.

SELECT MAX(COUNT(*)) FROM emp GROUP BY n_dept ;

Les différents types de jointures

La jointure est une opération permettant de combiner des informations

venant de plusieurs tables. Les exemples suivants se l imiteront à deux

tables, mais on peut joindre jusqu'à 256 tables. Une jointure se formule

simplement en spécifiant plusieurs tables derrière le FROM de la façon

suivante :

SELECT ...

FROM nom_table1, nom_table2...

WHERE predicat;

Si on ne précise pas de condition de sélection, le résultat obtenu sera le

produit cartésien des tables présentes derrière le FROM (résultat non

souhaité en général).

Il n 'existe pas d'associations impl icites ou expl icites entre les tables dans

SQL. Les associations entre les tables sont définies dynamiquement lors

des interrogations, ce qui contribue à la grande souplesse du langage SQL

et rend possible toute association même si el le n'a pas été prévue lors de

la définit ion et du chargement de la base.

A- Equi-jointure

Le rapprochement de chaque l igne de la table emp avec la l igne de la table

dept ayant même numéro de département permet d'obtenir la l iste des

employés avec la local i té dans laquel le i ls travai l lent. Ce rapprochement

entre deux colonnes appartenant à deux tables différentes mais ayant le

même sens (ici le numéro de département) et venant vraisemblablement

d'une relation 1-n lors de la conception (ici 1 entité département pour n

DEES INFORMATIQUE

39

V 1.0.0.a

entités employés) est assez naturel. C'est pourquoi ce type de jointure

porte le nom de jointure naturelle ou d'équi-jointure.

Exemple : Donner pour chaque employé son nom et son l ieu de travai l .

SELECT emp.nom, l ieu FROM emp, dept WHERE emp.n_dept =

dept.n_dept;

Le fait que la colonne contenant le numéro de département ait le même

nom dans les deux tables a rendu nécessaire le préfixage par le nom de

table dans le critère de jointure (clause WHERE). Le nom de colonne nom

a lui aussi besoin d'être préfixé car i l appartient aux deux tables (nom de

la personne dans l 'une et nom du département dans l 'autre). Par contre, le

nom de colonne l ieu n'a pas besoin d'être préfixé, car i l n'y a pas

d'ambiguïté sur la table à laquel le cette colonne appartient.

B- Jointure d'une table à elle-même

Il peut être uti le de rassembler des informations venant d'une l igne d'une

table avec des informations venant d'une autre l igne de la même table.

Exemple : Donner pour chaque employé le nom de son supérieur

hiérarchique.

SELECT emp.nom, mgr.nom FROM emp, emp mgr WHERE emp.n_sup=

mgr.num;

Remarque : Dans ce cas, i l faut impérativement renommer au moins l 'une

des deux occurrences de la table (ici emp) en lui donnant un synonyme,

afin de pouvoir préfixer sans ambigüité chaque nom de colonne.

C- Autres jointures

Le critère d'égal ité est le critère de jointure le plus naturel. Mais on peut

uti l iser d'autres types de comparaisons comme critères de jointures.

Exemple : Quels sont les employés gagnant plus que SIMON?

SELECT emp.nom, emp.salaire, emp.fonction FROM emp, emp j WHERE

emp.salaire > j.salaire AND J.nom = 'SIMON';

DEES INFORMATIQUE

40

V 1.0.0.a

D- Jointure externe

Lorsqu'une l igne d'une table figurant dans une jointure n'a pas de

correspondant dans les autres tables, el le ne satisfait pas au critère

d'équi-jointure et donc ne figure pas dans le résultat de la jointure.

Une option permet de faire figurer dans le résultat les l ignes satisfaisant

la condition d'équi-jointure plus cel les n'ayant pas de correspondant. Cette

option s'obtient en accolant (+) au nom de colonne de la table dans

laquel le manquent des éléments, dans la condition d'équi-jointure.

Exemple : Le département 40 ne figurait pas dans le résultat du SELECT

précédent. Par contre, i l f igurera dans le résultat du SELECT suivant.

SELECT emp.nom, l ieu FROM emp, dept WHERE emp.n_dept(+) =

dept.n_dept;

Le (+) peut s'interpréter comme l 'ajout d'une l igne fictive dont toutes les

colonnes ont la valeur NULL, et qui réal ise la correspondance avec les

l ignes de l 'autre table qui n'ont pas de correspondant réel. Dans l 'exemple

ci-dessus, la valeur de nom associée au département 40 est la valeur

NULL.

Exemple : Retrouver les départements n'ayant aucun employé.

SELECT dept.n_dept, emp.nom FROM emp, dept WHERE dept.n_dept =

emp.n_dept (+) AND emp.nom IS NULL;

Les opérateurs ensemblistes

Les opérateurs ensemblistes permettent de "joindre" des tables

verticalement, c'est-à-dire de combiner dans un résultat unique des l ignes

provenant de deux interrogations. Les l ignes peuvent venir de tables

différentes mais après projection on doit obtenir des tables ayant même

schéma de relation.

Les opérateurs ensemblistes sont les suivants :

l 'union :

UNION

l ' intersection :

INTERSECT

la différence relationnel le :

MINUS

DEES INFORMATIQUE

41

V 1.0.0.a

La syntaxe d'utilisation est la même pour ces trois opérateurs :

SELECT ... {UNION | INTERSECT | MINUS } SELECT ...

Dans une requête utilisant des opérateurs ensemblistes :

Tous les SELECT doivent avoir le même nombre de colonnes

sélectionnées, et leur types doivent être un à un identiques.

Les conversions éventuel les doivent être faites à l ' intérieur du

SELECT à l 'aide des fonctions de conversion.

Les doubles sont él iminés (DISTINCT impl icite).

Les noms de colonnes (titres) sont ceux du premier SELECT.

La largeur des colonnes est la plus grande parmi tous les

SELECT .

Dans une requête on ne peut trouver qu'un seul ORDER BY.

S'i l est présent, i l doit être mis dans le dernier SELECT et i l

ne peut faire référence qu'aux numéros des colonnes et non

pas à leurs noms (car les noms peuvent être différents dans

chacune des interrogations).

On peut combiner le résultat de plus de deux SELECT au moyen des

opérateurs UNION, INTERSECT , MINUS.

SELECT . .. UNION SELECT ... MINUS SELECT ...

Dans ce cas l 'expression est évaluée de gauche à droite, mais on peut

modifier l 'ordre d'évaluation en uti l isant des parenthèses.

SELECT ...

UNION (SELECT ...

MINUS

SELECT ...)

Les requêtes imbriquées

Une caractéristique puissante de SQL est la possibi l i té qu'un critère de

recherche employé dans une clause WHERE (expression à droite d'un

opérateur de comparaison) soit lui-même le résultat d'un SELECT ; c'est

ce qu'on appel le une sous-interrogation.

DEES INFORMATIQUE

42

V 1.0.0.a

A- Sous-interrogation ramenant une seule valeur

Exemple : Quels sont les employés ayant la même fonction que CODD ?

SELECT nom FROM emp WHERE fonction = (SELECT fonction FROM emp

WHERE nom ='CODD');

Remarques :

Une sous-interrogation qui ne ramène aucune l igne se termine avec

un code d'erreur.

Une sous-interrogation ramenant plusieurs l ignes provoquera aussi,

dans ce cas, une erreur (pour traiter correctement ce cas, voir

paragraphe ci-dessous)

B- Sous-interrogation ramenant plusieurs lignes

Une sous-interrogation peut ramener plusieurs l ignes à condition que

l 'opérateur de comparaison admette à sa droite un ensemble de valeurs.

Les opérateurs permettant de comparer une valeur à un ensemble

de valeurs sont :

l 'opérateur

IN

les opérateurs obtenus en ajoutant

ANY ou ALL à la suite d'un opérateur de comparaison

classique (=, !=, >, >=, <, <=)

ANY: la comparaison est vraie si el le est vraie pour au moins

un des éléments de l 'ensemble.

ALL: la comparaison sera vraie si el le est vraie pour tous les

éléments de l 'ensemble.

Exemple : Quels sont les employés gagnant plus que tous les employés du

département 30.

SELECT nom, salaire FROM emp WHERE salaire > ALL (SELECT salaire

FROM emp WHERE n_dept = 30);

C- Sous-interrogation ramenant plusieurs colonnes

Il est possible de comparer le résultat d'un SELECT ramenant plusieurs

colonnes à une l iste de colonnes. La l iste de colonnes figurera entre

parenthèses à gauche de l 'opérateur de comparaison.

DEES INFORMATIQUE

43

V 1.0.0.a

Exemple : Quels sont les employés ayant même fonction et même

supérieur que CODD?

SELECT nom, fonct ion, n_sup FROM emp WHERE (fonct ion, n_sup) =

(SELECT fonction, n_sup FROM emp WHERE nom = 'CODD');

D- Sous-interrogation synchronisée avec l' interrogation principale

Dans les exemples précédents, la sous-interrogation était évaluée d'abord,

puis le résultat pouvait être uti l isé pour exécuter l ' interrogation

principale. SQL sait également traiter une sous-interrogation faisant

référence à une colonne de la table de l ' interrogation principale. Le

traitement dans ce cas est plus complexe, car i l faut évaluer la sous-

interrogation pour chaque l igne de l ' interrogation principale.

Exemple : Quels sont les employés ne travai l lant pas dans le même

département que leur supérieur hiérarchique.

SELECT nom FROM emp e WHERE n_dept != (SELECT n_dept FROM emp

WHERE e.n_sup = num) AND n_sup IS NOT NULL;

Il a fal lu ici renommer la table emp de l ' interrogation principale pour

pouvoir la référencer dans la sous-interrogation. n_sup IS NOT NULL est

nécessaire car dans le cas où n_sup est vide, la colonne n_sup est NULL

et la sous-requête ne ramène alors aucune valeur.

E- Sous-interrogation ramenant au moins une ligne

L'opérateur EXISTS permet de construire un prédicat vrai si la sous-

interrogation qui suit ramène au moins une l igne.

Exemple : Quels sont les employés travai l lant dans un département qui a

procédé à des embauches depuis le début de l 'année 94.

SELECT *FROM emp e WHERE EXISTS (SELECT * FROM emp WHERE

embauche >= '01-jan-94' AND n_dept = e.n_dept);

Remarque : On peut inverser le sens de l 'opérateur EXISTS en le faisant

précéder de NOT.

DEES INFORMATIQUE

44

V 1.0.0.a

F- Sous-interrogations multiples

Un SELECT peut comporter plusieurs sous-interrogations, soit imbriquées,

soit au même niveau dans différents prédicats combinés par des AND ou

des OR .

Exemple : Liste des employés du département 10 ayant même fonction que

quelqu'un du département de DUPONT.

SELECT nom, fonction FROM emp WHERE n_dept = 10 AND fonction IN

(SELECT fonction FROM emp WHERE n_dept = (SELECT n_dept FROM emp

WHERE nom = 'DUPONT'));

1.5 Langage de Manipulation des Données (LMD)

Le langage de manipulation de données est le langage permettant de

modifier les informations contenues dans une base de données.

L'unité manipulée est la l igne. Il existe trois commandes SQL permettant

d'effectuer les trois types de modifications des données : ajout,

modification et suppression.

Insertion des données : simple et avancée

A- Définition

La commande INSERT permet d'insérer une l igne dans une table en

spécifiant les valeurs à insérer.

La syntaxe est la suivante :

INSERT INTO nom_table(nom_col1, nom_col2, ...)

VALUES (val1, val2...)

La l iste des noms de colonne est optionnel le. Si el le est omise, la l iste des

colonnes sera par défaut la l iste de l 'ensemble des colonnes de la table

dans l 'ordre de la création de la table. Si une l iste de colonnes est

spécifiée, les colonnes ne figurant pas dans la l iste auront la valeur NULL.

Il est possible d'insérer dans une table des l ignes provenant d'une autre

table.

La syntaxe est la suivante :

INSERT INTO nom_table(nom_col1, nom_col2, ...)

SELECT ...

DEES INFORMATIQUE

45

V 1.0.0.a

Le SELECT peut contenir n'importe quel le clause sauf un ORDER BY qui

impliquerait un classement des l ignes contraire à l 'esprit du relationnel.

Exemple : Insérer dans la table bonus les noms et salaires des directeurs.

INSERT INTO bonus

SELECT nom, salaire

FROM emp

WHERE fonction = 'directeur';

B- Description de la syntaxe globale

INSERT INTO [schema.]table | view

[ (column [, column] ...) ]

VALUES (expr [, expr] ...) | subquery

schema : est le nom du schéma contenant la table ou la vue. Le

schéma par défaut est celui de l 'uti l isateur qui exécute la requête.

table / view : est le nom de la table dans laquel le les l ignes seront

insérées. Si c'est un nom de vue qui est précisé, les données seront

insérées dans la table basée sur la vue.

column : nom des colonnes

VALUES Expr / subquery : valeurs des colonnes

Prérequis :

Pour pouvoir insérer des l ignes dans une table, i l faut soit être

propriétaire de cet objet, soit avoir le privi lège INSERT sur cette table.

Le privi lège INSERT ANY TABLE permet d'insérer des l ignes dans

n'importe quel le table appartenant à n'importe quel uti l isateur.

Mise à jour des données

A- Définition

La commande UPDATE permet de modifier les valeurs d'une ou plusieurs

colonnes, dans une ou plusieurs l ignes existantes d'une table.

La syntaxe est la suivante :

UPDATE nom_table

SET nom_col1 = {expression1 | ( SELECT ...) },

nom_col2 = {expression2 | ( SELECT ...) }

WHERE predicat

DEES INFORMATIQUE

46

V 1.0.0.a

Les valeurs des colonnes nom_col1, nom_col2, ... sont modifiées dans

toutes les l ignes satisfaisant au prédicat. En l 'absence d'une clause

WHERE, toutes les l ignes sont mises à jour. Les expressions expression1,

expression2,... peuvent faire référence aux anciennes valeurs de la l igne.

Exemple : Augmenter de 10% les ingénieurs.

UPDATE emp

SET salaire = salaire * 1.1

WHERE fonction = 'ingenieur' ;

B- Description de la syntaxe globale

UPDATE [schema.]table | view [alias]

SET (column [, column] ...) = (subquery)

| column = expr | (subquery)

[, (column [, column] ...) = (subquery)

| column = expr | (subquery) ] ...

[WHERE condition]

schema : est le nom du schéma contenant la table ou la vue à

modifier. Le schéma par défaut est celui de l 'uti l isateur qui exécute

la requête.

table, view : est le nom de la table à mettre à jour. Si c'est un nom

de vue, la table mise à jour est cel le sur laquel le la vue est définie.

alias : est un al ias assigné à la table. Les al ias sont généralement

uti l isés dans des UPDATE contenant des requêtes.

column : est le nom de la colonne qui sera modifiée.

expr : est la nouvel le valeur de la colonne.

subquery : est un SELECT qui renvoie les nouvel les valeurs

affectées aux colonnes correspondantes.

WHERE : restreint les l ignes modifiées à cel les pour lesquel les la

condition est vraie. Si on omet cette clause toutes les l ignes sont

modifiées.

Prérequis :

Pour pouvoir modifier des l ignes appartenant à une table, i l faut soit être

propriétaire de cet objet, soit avoir le privi lège UPDATE sur cette table.

Le privi lège UPDATE ANY TABLE permet de sélectionner des l ignes de

n'importe quel objet appartenant à n'importe quel uti l isateur.

DEES INFORMATIQUE

47

V 1.0.0.a

Suppression des données

A- Définition

La commande DELETE permet de supprimer des l ignes d'une table.

La syntaxe est la suivante :

DELETE FROM nom_table

WHERE prédicat ;

Toutes les l ignes pour lesquel les prédicat est évalué à vrai sont

supprimées. En l 'absence de la clause WHERE, toutes les l ignes de la

table sont supprimées.

Syntaxe :

DELETE [FROM] [schema.]table | view [alias]

[WHERE condition]

schema : est le nom du schéma contenant la table ou la vue à

détruire. Le schéma par défaut est celui de l 'uti l isateur qui exécute

la requête.

table, view : est le nom de la table ou de la vue contenant les

l ignes qui seront détruites. Si c'est un nom de vue, les l ignes

détruites appartiennent à la table sur laquel le la vue est basée.

alias : est un al ias assigné à la table. Les al ias sont généralement

uti l isés dans des DELETE contenant des requêtes.

WHERE : détruit seulement les l ignes satisfaisant la condition. Cette

condition peut référencer la table et peut contenir des sous-

requêtes. Si cette clause est omise détruit toutes les l ignes.

Prérequis :

Pour détruire des l ignes appartenant à une table, i l faut soit être

propriétaire de la table, soit avoir le privi lège DELETE sur cette table.

Le privi lège DELETE ANY TABLE permet à un uti l isateur de détruire des

l ignes se trouvant dans n'importe quelle table, ou n'importe quel le vue

basée sur une table.

DEES INFORMATIQUE

48

V 1.0.0.a

1.6 Langage de Définition des données (LDD)

Le langage de définit ion des données est le langage permettant de créer

ou de modifier le schéma d'une relation et donc d'une table.

Il permet de créer, de modifier et de supprimer non seulement les tables,

mais aussi les vues, les index….

Les tables

A- Créer une table

Une table est :

une unité de stockage élémentaire, composée de l ignes et de

colonnes,

la structure de base contenant les données des uti l isateurs.

Le nom d'une table dans une BDD Oracle

doit commencer par une lettre

ne doit pas dépasser les 30 caractères

ne peut contenir que les caractères A àZ, a àz, 0 à9, _, $, et #

ne doit pas porter le nom d'un autre objet appartenant au même

uti l isateur

ne doit pas être un mot réservé

Quand on crée une table, on peut spécifier les informations

suivantes :

la définit ion des colonnes,

les contraintes d'intégrité,

la TableSpace contenant la table,

les caractéristiques de stockage,

le cluster contenant la table,

les données résultant d'une éventuel le requête.

Création simple :

La commande de création de table la plus simple ne comportera que le

nom et le type de chaque colonne de la table. L'on peut créer une table

par la commande CREATE TABLE en spécifiant le nom et le type de

chaque colonne. A la création, la table sera vide mais un certain espace lui

sera al loué.

DEES INFORMATIQUE

49

V 1.0.0.a

La syntaxe est la suivante :

CREATE TABLE nom_table

(nom_col1 TYPE1,

nom_col2 TYPE2,

.. .)

L'option NOT NULL assure qu'ORACLE interdit lors d'un INSERT ou d'un

UPDATE que cette colonne contienne la valeur NULL , par défaut el le est

autorisée.

Création avec Insertion de données :

On peut insérer des données dans une table lors de sa création par la

commande suivante :

CREATE TABLE nom_table

[(nom_col1,

nom_col2,

. ..)]

AS SELECT...

On peut ainsi, en un seul ordre SQL créer une table et la remplir avec des

données provenant du résultat d'un SELECT .

On n'a pas besoin alors de spécifier de type pour les colonnes : les types

des données sont ceux provenant du SELECT. Si des conversions de type

sont à fai re, on peut dans le SELECT uti l iser les fonct ions TO_CHAR,

TO_DATE , TO_NUMBER.

Par défaut les noms des colonnes de la nouvel le table sont les noms des

colonnes du SELECT. Si des expressions apparaissent dans le SELECT , les

colonnes correspondantes doivent impérativement être renommées.

Le SELECT peut contenir des fonctions de groupes mais pas d'ORDER BY

car les l ignes d'une table ne peuvent pas être classées.

On peut, et même on doit, quand on crée une table définir les contraintes

d'intégrité que devront respecter les données que l 'on mettra dans la table

(voir un peu plus bas).

Les types de données :

NUMBER[(longueur,[précision]) : Ce type de données permet de

stocker des données numériques à la fois entières et réel les dont la

DEES INFORMATIQUE

50

V 1.0.0.a

valeur est comprise entre 10^-130 et 10^125 avec une précision de

38 chiffres.

� longueur : précise le nombre maximum de chiffres significati fs

stockés (par défaut 38),

� précision : donne le nombre maximum de chiffres après la virgule

(par défaut 38), sa valeur peut être comprise entre -84 et 127.

Une valeur négative signifie que le nombre est arrondi à gauche

de la virgule.

CHAR(longueur) : Ce type de données permet de stocker des

chaînes de caractères de longueur fixe. Longueur doit être inférieur

à 255, sa valeur par défaut est 1.

VARCHAR(longueur) : Ce type de données permet de stocker des

chaînes de caractères de longueur variable. Longueur doit être

inférieure à 2000, i l n'y a pas de valeur par défaut.

DATE : Ce type de données permet de stocker des données

constituées d'une date et d'une heure.

RAW(longueur) : Ce type de données permet de stocker des

caractères non imprimables.

LONG : Ce type de données permet des stocker des chaînes de

caractères de longueur variable et inférieure à 2^31 -1. Les

colonnes de ce type sont soumises à certaines restrictions ;

� une table ne peut pas contenir plus d'une colonne de ce type ;

� les colonnes de ce type ne peuvent pas apparaître dans des

contraintes d'intégrité ;

� les colonnes de ce type ne peuvent pas être indexées ;

� les colonnes de ce type ne peuvent pas apparaître dans des

clauses : WHERE, GROUP BY, ORDER BY ou CONNECT BY ainsi que

dans un DISTINCT.

B- Modifier une table

On peut modifier dynamiquement la définit ion d'une table grâce à la

commande ALTER TABLE. Trois types de modifications sont possibles :

ajout, modification et suppression d'une colonne existante.

DEES INFORMATIQUE

51

V 1.0.0.a

Ajouter une colonne :

La commande suivante permet d'ajouter une ou plusieurs colonnes à une

table existante :

ALTER TABLE nom_table ADD

(nom_col1 TYPE1,

nom_col2 TYPE2,

. ..)

Les types possibles sont les mêmes que ceux décrits avec la commande

CREATE TABLE .

Si la table contient déjà des l ignes, la nouvel le colonne aura des valeurs

NULL pour les l ignes existantes.

Modifier une colonne :

La commande suivante permet de modif ier une ou plusieurs colonnes à une

table existante :

ALTER TABLE nom_table MODIFY

(nom_col1 TYPE1,

nom_col2 TYPE2,...);

Il est possible de modifier la définit ion d'une colonne, à condition que la

nouvel le définit ion soit compatible avec le contenu de la colonne et en

respectant les contraintes suivantes :

dans tous les cas i l est possible d'augmenter la tai l le d'une colonne ;

i l est possible de diminuer la tai l le, ou même de changer le type

d'une colonne vide ;

on peut spécifier NOT NULL si la colonne ne contient aucune valeur

NULL ;

on peut dans tous les cas spécifier NULL pour autoriser les valeurs

NULL.

Supprimer une colonne :

La commande suivante permet de supprimer une ou plusieurs colonnes à

une table existante :

ALTER TABLE nom_table

DROP COLUMN (nom_col1, nom_col2,…);

DEES INFORMATIQUE

52

V 1.0.0.a

C- Supprimer une table

La commande DROP TABLE permet de supprimer une table, sa syntaxe est

la suivante :

DROP TABLE nom_table

[CASCADE CONSTRAINTS];

La table nom_table est alors supprimée. La définit ion de la table ainsi que

son contenu sont détruits, et l 'espace occupé par la table est l ibéré.

CASCADE CONSTRAINTS

Supprime toutes les contraintes qui se réfère à la table supprimée.

Prérequis :

Pour pouvoir détruire une table, i l faut soit être propriétaire de cette

table, soit avoir le privi lège DROP ANY TABLE.

D- Renommer une table

On a la possibil i té de changer le nom d'une table par la commande

RENAME , la syntaxe est la suivante :

RENAME ancien_nom TO nouveau_nom ;

Les contraintes

Les contraintes :

Contrôlent les règles de gestion dans une table

Empêchent la suppression d'une table lorsqu'i l existe des

dépendances

Sont stockées dans la table USER_CONSTRAINTS du dictionnaire de

données

Peuvent être nommées automatiquement sous le format SYS_Cn

Peuvent être nommées manuel lement par l 'uti l isateur

� Lors de la création (CREATE TABLE)

� Après la création (ALTER TABLE)

A- Créer un contrainte d’intégrité

Ajouter une contrainte avec CREATE TABLE :

A la création d'une table, les contraintes d'intégrité se déclarent de la

façon suivante :

CREATE TABLE nom_table (

DEES INFORMATIQUE

53

V 1.0.0.a

nom_col_1 type_1,

nom_col_2 type_2,

...

nom_col_n type_n

CONSTRAINT [nom_contrainte_1] contrainte_1,

CONSTRAINT [nom_contrainte_2] contrainte_2,

...

CONSTRAINT [nom_contrainte_m] contrainte_m

);

Ou bien de la façon suivante :

CREATE TABLE nom_table (

nom_col_1 type_1 CONSTRAINT [nom_contrainte_1_1]

contrainte_1_1

CONSTRAINT [nom_contrainte_1_2] contrainte_1_2

. ..

CONSTRAINT [nom_contrainte_1_m] contrainte_1_m,

nom_col_2 type_2 CONSTRAINT [nom_contrainte_2_1]

contrainte_2_1

CONSTRAINT [nom_contrainte_2_2] contrainte_2_2

. ..

CONSTRAINT [nom_contrainte_2_p] contrainte_2_p,

...

nom_col_n type_n CONSTRAINT [nom_contrainte_n_1]

contrainte_n_1

CONSTRAINT [nom_contrainte_n_2] contrainte_n_2

. ..

CONSTRAINT [nom_contrainte_n_q] contrainte_n_q

);

Les contraintes différentes que l'on peut déclarer sont les

suivantes :

NOT NULL : La colonne ne peut pas contenir de valeurs NULL .

UNIQUE : Chaque l igne de la table doi t avoir une valeur différente

ou NULL pour cette (ou ces) colonne.

DEES INFORMATIQUE

54

V 1.0.0.a

PRIMARY KEY : Chaque l igne de la table doit avoir une valeur

différente pour cette (ou ces) colonne. Les valeurs NULL sont

rejetées.

FOREIGN KEY : Cette colonne fait référence à une colonne clé d'une

autre table.

CHECK : Permet de spécifier les valeurs acceptables pour une

colonne.

Exemple : Contrainte PRIMARY KEY

CREATE TABLE emp (num NUMBER(4) PRIMARY KEY,

nom VARCHAR2(10),…

n_dept NUMBER(7,2) NOT NULL);

ou

CREATE TABLE emp

(num NUMBER(4), nom VARCHAR2(10),…

n_dept NUMBER(7,2) NOT NULL,

CONSTRAINT emp_num_pk PRIMARY KEY (num));

Exemple : Contrainte CHECK

CREATE TABLE emp (num NUMBER(4) PRIMARY KEY,

nom VARCHAR2(10),…n_dept NUMBER(7,2) NOT NULL,

CONSTRAINT emp_ndept_KT CHECK (n_dept BETWEEN 10 AND 99), …));

Exemple : Contrainte FOREIGN KEY

CREATE TABLE emp (num NUMBER(4) PRIMARY KEY,

nom VARCHAR2(10),…

n_dept NUMBER(7,2) NOT NULL REFERENCES dept (n_dept));

ou

CREATE TABLE emp (num NUMBER(4) PRIMARY KEY,

nom VARCHAR2(10),…n_dept NUMBER(7,2) NOT NULL,

CONSTRAINT emp_ndept_KT FOREIGN KEY (n_dept) REFERENCES dept

(n_dept));

Exemple : Contrainte NOT NULL

n_dept NUMBER(7,2) NOT NULL;

ou

n_dept NUMBER(7,2) CONSTRAINT CT_ndept NOT NULL;

DEES INFORMATIQUE

55

V 1.0.0.a

Exemple: Contrainte UNIQUE

CREATE TABLE emp (num NUMBER(4) PRIMARY KEY,

nom VARCHAR2(10) UNIQUE,…

n_dept NUMBER(7,2) NOT NULL,);

ou

CREATE TABLE emp (num NUMBER(4) PRIMARY KEY,

nom VARCHAR2(10),…

n_dept NUMBER(7,2) NOT NULL,

CONSTRAINT emp_nom_KT UNIQUE (nom)) ;

Ajouter une contrainte avec ALTER TABLE :

A la modification d'une table, les contraintes d'intégrité se déclarent de la

façon suivante :

ALTER TABLE nom_table

ADD CONSTRAINTE contrainte_de_table

Cette variante ajoute une nouvel le contrainte à une table en uti l isant la

même syntaxe que CREATE TABLE .

Exemple :

ALTER TABLE emp ADD CONSTRAINT emp_ndept_KT CHECK (n_dept

BETWEEN 10 AND 99), …));

B- Modifier une contrainte d’intégrité

La commande MODIFY permet de modifier une contrainte d’ intégrité.

Pour uti l iser cette commande sur une contrainte, i l est nécessaire de faire

un ALTER TABLE sur la table contenant la contrainte.

Syntaxe :

ALTER TABLE nom_table MODIFY PRIMARY KEY contrainte | UNIQUE

(colonne) contrainte | CONSTRAINT nom_contrainte contrainte

[CASCADE] ;

C- Supprimer une contrainte d’intégrité

La commande DROP permet de supprimer une contrainte d’ intégrité.

Pour uti l iser cette commande sur une contrainte, i l est nécessaire de faire,

comme pour une modification, un ALTER TABLE sur la table contenant la

contrainte.

DEES INFORMATIQUE

56

V 1.0.0.a

Syntaxe :

ALTER TABLE nom_table DROP PRIMARY KEY contrainte | UNIQUE

(colonne) contrainte | CONSTRAINT nom_contrainte contrainte

[CASCADE] ;

Exemple: Supprimer la contrainte PRIMARY KEY de la table DEPT, ainsi que

la contrainte FOREIGN KEY définie sur la colonne N_DEPT de la table EMP

ALTER TABLE dept (DROP PRIMARY KEY CASCADE);

Exemple: Supprimer une contrainte CHECK sur N_DEPT de la table EMP

ALTER TABLE emp DROP CONSTRAINT emp_ndept_KT;

D- Désactivation/Activation d'une contrainte

Syntaxe :

ALTER TABLE nom_table [DISABLE | ENABLE] CONSTRAINT

nom_contrainte [CASCADE];

On peut uti l iser les clauses ENABLE et DISABLE dans CREATE TABLE et

ALTER TABLE

E- Afficher les contraintes sur une table

SELECT constraint_name, constraint_type, search_condition FROM

user_constraints WHERE nom_table= 'EMP'

F- Afficher les colonnes associées aux contraintes dans une table

SELECT constraint_name, column_name FROM user_cons_column

WHERE nom_table= 'EMP'

Constraint_Name Constraint-type Search_Condition

SYS_C003042 C(Signifie CHECK) NUM is notNul

SYS_C003044 U(Signifie Unique)

TEST_TT P(Signifie PrimaryKey)

DEES INFORMATIQUE

57

V 1.0.0.a

Constraint_Name ColumnName

TEST_TT NUM

SYS_C003042 N_DEPT

Les vues

Les vues permettent d'assurer l 'objecti f d'indépendance logique. Grâce à

el les, chaque uti l isateur pourra avoir sa vision propre des données.

On a vu que le résultat d'un SELECT est lui-même une table.

Une tel le table, qui n'existe pas dans la base mais est créée

dynamiquement lors de l 'exécution du SELECT , peut être vue comme une

table réel le par les uti l isateurs. Pour cela, i l suffi t de cataloguer le

SELECT en tant que vue.

Les uti l isateurs pourront consulter la base, ou modifier la base (avec

certaines restrictions) à travers la vue, c'est-à-dire manipuler la table

résultat du SELECT comme si c'était une table réel le.

A- Créer une vue

La commande CREATE VIEW permet de créer une vue en spécifiant les

données, lorsque des métadonnées en mode lecture sont sol l ici tées pour

une requête qui fait référence à la vue.

Syntaxe :

CREATE [ OR REPLACE ] [ FORCE | NOFORCE ] VIEW [ propriétaire . ]

nom_vue

(nom_colonne,...,nom_colonneN)

AS instruction_sélection...;

[WITH CHECK OPTION

[CONSTRAINT constraint]] ;

L'instruction OR REPLACE crée à nouveau la vue si el le existe déjà.

Les clauses FORCE et NOFORCE indiquent respectivement que :

la vue est créée sans se soucier de l 'existence des tables qu'el le

référence ou des privi lèges adéquats sur les tables,

DEES INFORMATIQUE

58

V 1.0.0.a

la vue est créée seulement si les tables existent et si les permissions

requises sont données.

La clause WITH CHECK OPTION l imite les insertions et les mises à jour

exécutées par l ' intermédiaire de la vue.

La clause CONSTRAINT est un nom optionnel donné à la contrainte WITH

CHECK OPTION.

Les vues peuvent être créées à partir d'autres vues. Pour cela, i l suffi t de

référencer les vues dans la clause FROM de l ' instruction select.

CREATE VIEW nom_vue

AS

SELECT * FROM nom_vue2;

Bien que cela soit possible, i l faut éviter l 'accumulation sur plusieurs

niveaux afin d'éviter tout problème de gestion lors des suppressions de

vue notamment.

La spécification des noms de colonnes de la vue est facultative. Par

défaut, les noms des colonnes de la vue sont les mêmes que les noms des

colonnes résultat du SELECT (si certaines colonnes résultat du SELECT

sont des expressions, i l faut renommer ces colonnes dans le SELECT , ou

spécifier les noms de colonne de la vue).

Une fois créée, une vue s'uti l ise comme une table. Il n'y a pas de

dupl ication des informations mais stockage de la définit ion de la vue.

D'autre part, la clause ORDER BY ne peut pas être employée dans

l ' instruction CREATE VIEW . Cependant, la clause de regroupement

GROUP BY peut être uti l isée à des fins d'ordonnancement.

Exemple : Création d'une vue constituant une restriction de la table emp

aux employés du département 10.

CREATE VIEW emp10 AS

SELECT *

FROM emp

WHERE n_dept = 10 ;

Le CHECK OPTION permet de véri fier que la mise à jour ou l ' insertion

faite à travers la vue ne produisent que des l ignes qui font partie de la

sélection de la vue.

DEES INFORMATIQUE

59

V 1.0.0.a

Ainsi donc, si la vue emp10 a été créée avec CHECK OPTION on ne pourra

à travers cette vue ni modifier, ni insérer des employés ne faisant pas

partie du département 10.

Il est possible d'effectuer des INSERT et des UPDATE à travers des vues,

sous deux conditions :

le SELECT définissant la vue ne doit pas comporter de jointure,

les colonnes résultat du SELECT doivent être des colonnes réel les et

non pas des expressions.

Exemple : Modificat ion des salaires du département 10 à travers la vue

emp10.

UPDATE emp10

SET sal = sal *1.1;

Toutes les l ignes de la table emp, tel les que le contenu de la colonne

n_dept est égal à 10 seront modifiées.

Remarque :

La table USER_VIEW permet d'afficher le nom et la définit ion des vues de

l 'uti l isateur.

La colonne VIEW-NAME contient le nom de la vue et la colonne TEXT

contient l 'ordre SELECT qui est stocké dans une colonne de type LONG.

B- Modifier une vue

La modification d'une vue s'effectue au moyen de la commande ALTER

VIEW . L'instruction OR REPLACE vu précédemment spécifique à Oracle

peut être également uti l isée pour modifier la vue.

C- Supprimer une vue

Une vue peut être détruite par la commande :

DROP VIEW nom_vue;

D- Renommer une vue

On peut renommer une vue par la commande :

RENAME ancien_nom TO nouveau_nom;

DEES INFORMATIQUE

60

V 1.0.0.a

E- Règles d'exécution des ordres LMD

Vous ne pouvez pas :

Supprimer un enregistrement si la vue contient :

Des fonctions de groupe

Une clause GROUP BY

Le mot-clé DISTINCT

Vous ne pouvez pas

Modifier les données si la vue contient :

Des fonctions de groupe

Une clause GROUP BY

Le mot-clé DISTINCT

Des colonnes définies par des expressions

Vous ne pouvez pas

Ajouter des données si la vue contient :

Des fonctions de groupe

Une clause GROUP BY

Le mot-clé DISTINCT

Des colonnes définies par des expressions

Les tables de base contiennent des colonnes NOT NULL non

sélectionnées par la vue

Les séquences

A- Définition

Une séquence :

Permet de générer automatiquement des numéros uniques

Est un objet partageable entre plusieurs uti l isateurs

Est uti l isée en général pour créer une valeur de clé primaire

Remplace le code appl icati f

Améliore les performances d'accès aux valeurs (stockage en mémoire

cache)

Syntaxe :

CREATE SEQUENCE Nom_séquence

INCREMENT BY (entier)

START WITH (entier)

MAXVALUE (entier)

DEES INFORMATIQUE

61

V 1.0.0.a

MINVALUE (entier)

CYCLE | NOCYCLE

CACHE( entier)| NOCACHE

INCREMENT BY : Définit l ' interval le d'incrémentation

START WITH : Première valeur de la séquence

MINVALUE : Valeur minimale (par défaut 1 ou -1026)

MAXVALUE : Valeur maximale (par défaut 1027 ou -1)

CYCLE | NOCYCLE : Revenir à la valeur initiale

CACHE entier| NOCACHE : Al location de séquences en mémoire

(par défaut 20)

Exemple : Créer un compteur DEPT_DEPTNO pour la clé primaire de la

table DEPT Créer

CREATE SEQUENCE dept_deptno

INCREMENT BY 10

START WITH 10

MAXVALUE 1000

Exemple : Créer un compteur pour la clé primaire de la table EMP

CREATE SEQUENCE EMP_EMPNO

INCREMENT BY 1

START WITH 1

MAXVALUE 1000

Les Pseudo Colonnes:

L'interrogation d'une séquence se fait par l 'uti l isation des "pseudo-

colonnes" CURRVAL et NEXTVAL . On parle de pseudo-colonne car cela se

manipule un peu comme une colonne de table, mais ce n'est pas une

colonne de table.

La pseudo-colonne CURRVAL retourne la valeur courante de la

séquence.

La pseudo-colonne NEXTVAL incrémente la séquence et retourne la

nouvel le valeur.

Exemple :

INSERT INTO emp (num, nom, salaire) VALUES (emp_empno.NEXTVAL,

'rich', 2000);

DEES INFORMATIQUE

62

V 1.0.0.a

Exemple :

SELECT emp_empno.CURRVAL FROM dual;

Remarque :

La table USER_SEQUENCES contient les valeurs des séquences définies

par l 'uti l isateur.

B- Modifier une séquence

La commande ALTER SEQUENCE permet de modifier une séquence.

ALTER SEQUENCE ne modifie que les numéros de séquence à venir.

Vous devez supprimer la séquence puis la recréer si vous voulez modifier

le premier numéro (START WITH).

Syntaxe :

ALTER SEQUENCE Nom_séquence

INCREMENT BY (entier)

START WITH (entier)

MAXVALUE (entier)

MINVALUE (entier)

CYCLE | NOCYCLE

CACHE( entier)| NOCACHE

C-Supprimer une séquence

La commande DROP SEQUENCE permet de supprimer une séquence.

Syntaxe :

DROP SEQUENCE Nom_séquence;

Les index

A- Introduction - Généralités

Selon le modèle relationnel, les sélect ions peuvent être faites en uti l isant

le contenu de n'importe quel le colonne et les l ignes sont stockées dans

n'importe quel ordre.

DEES INFORMATIQUE

63

V 1.0.0.a

Considérons le SELECT suivant :

SELECT *

FROM emp

WHERE nom = 'MARTIN'

Un moyen de retrouver la ou les l ignes pour lesquelles, nom est égal à

MARTIN est de balayer toute la table.

Un tel moyen d'accès conduit à des temps de réponse prohibiti fs pour des

tables dépassant quelques centaines de l ignes.

Une solution offerte par tous les systèmes de gestion de bases de données

est la création d'index, qui permettra de satisfaire aux requêtes les plus

fréquentes avec des temps de réponse acceptables.

Un index sera matérial isé par la création de blocs disque contenant des

couples (valeurs d' index, numéro de bloc) donnant le numéro de bloc

disque dans lequel se trouvent les l ignes correspondant à chaque valeur

d'index.

Structure d'un index :

Les index sont des structures permettant de retrouver une l igne dans une

table à partir de la valeur d'une colonne ou d'un ensemble de colonnes. Un

index contient la l iste triée des valeurs des colonnes indexées avec les

adresses des l ignes (numéro de bloc dans la partit ion et numéro de l igne

dans le bloc) correspondantes.

Tous les index oracle sont stockés sous forme d'arbres équil ibrés (btree) :

une structure arborescente permet de retrouver rapidement dans l ' index la

valeur de clé cherchée, et donc l 'adresse de la l igne correspondante dans

la table.

Dans un tel arbre, toutes les feui l les sont à la même profondeur, et donc

la recherche prend approximativement le même temps quelle que soit la

valeur de la clé.

Lorsqu'un bloc d'index est plein, i l est éclaté en deux blocs. En

conséquence, tous les blocs d'index ont un taux de remplissage variant de

50% à 100%. Sans index on balaie séquentiel lement toute la table quel le

que soit la position de l ’élément recherché.

Utilisation des index :

L'adjonction d'un index à une table ralentit les mises à jour (insertion,

suppression, modification de la clé) mais accélère beaucoup la recherche

d'une l igne dans la table.

DEES INFORMATIQUE

64

V 1.0.0.a

L'index accélère la recherche d'une l igne à partir d'une valeur donnée de

clé, mais aussi la recherche des l ignes ayant une valeur d'index supérieure

ou inférieure à une valeur donnée, car les valeurs de clés sont triées dans

l ' index.

Exemple : Les requêtes suivantes bénéficieront d'un index sur le champ

n_dept.

SELECT * FROM emp WHERE num = 16034 ;

SELECT * FROM emp WHERE num >= 27234 ;

SELECT * FROM emp WHERE num BETWEEN 16034 AND 27234 ;

Un index est uti l isable même si le critère de recherche est constitué

seulement du début de la clé.

Exemple : La requête suivante bénéficiera d'un index sur la colonne nom.

SELECT *

FROM emp

WHERE nom LIKE 'M%' ;

Par contre si le début de la clé n'est pas connu, l ' index est inuti l isable.

Exemple : La requête suivante ne bénéficiera pas d'un index sur le champ

nom.

SELECT *

FROM emp

WHERE nom LIKE '?????????' ;

Valeurs NULL :

El les ne sont pas représentées dans l ' index, ceci afin de minimiser le

volume nécessaire pour stocker l ' index. En contrepartie, l ' index ne sera

d'aucune uti l i té pour retrouver les valeurs NULL lorsque le critère de

recherche est du type IS NULL.

Conversions :

L'index n'est uti l isable que si le critère de sélection est le contenu de la

colonne indexée, sans aucune transformation. Par exemple, un index sur

salaire ne sera pas uti l isé pour la requête suivante :

SELECT * FROM emp

WHERE salaire * 12 > 300000 ;

DEES INFORMATIQUE

65

V 1.0.0.a

Attention en particulier aux conversions de type qui peuvent empêcher

l 'uti l isation de l ' index.

SQL est un langage typé, chaque type de données (numérique, caractère,

date) ayant ses propres opérateurs, ses propres fonctions et sa propre

relation d'ordre. En conséquence, si dans une expression, figurent à la fois

un nombre et une chaîne de caractères, SQL convertira la chaîne de

caractères en nombre. De même, si dans une expression, f igurent à la fois

une chaîne de caractères et une date, SQL convertira la chaîne de

caractères en date.

Or, dans un prédicat du type :

WHERE fonction(col_indexée) = constante

SQL ne peut pas uti l iser l ' index.

Ceci peut se produire, de façon insidieuse, lorsque SQL est obl igé

d'ajouter un appel à une fonction de conversion à cause d'une discordance

de type.

Exemple : Le prédicat suivant ne bénéficiera pas d'un index sur le champ

embauche.

SELECT * FROM emp

WHERE embauche LIKE '????' ;

En effet, SQL est obligé d'effectuer une conversion, et le prédicat qui sera

évalué est :

WHERE TO_CHAR(embauche) LIKE '????'

Le critère de recherche est une fonction de embauche, et non le champ

embauche lui-même, dans ce cas l ' index est inuti l isable.

Choix des index, on indexe en priorité :

les clés primaires

les colonnes servant de critère de jointure

les colonnes servant souvent de critère de recherche

Les tables de grande tai l le

Les colonnes qui contiennent un grand nombre de valeurs NULL

La colonne contient un grand nombre de valeurs distinctes

Ne pas indexer :

les colonnes contenant peu de valeurs distinctes (index alors peu

efficace)

DEES INFORMATIQUE

66

V 1.0.0.a

les colonnes fréquemment modifiées

Création automatique :

Un index est crée par Oracle avec les contraintes

PRIMARY KEY

UNIQUE

Création Manuelle :

Vous pouvez créer des index dans des colonnes pour améliorer le temps

d'accès

Index sur l 'âge

Remarque :

La table USER_INDEXES contient les noms d'index et leur unicité.

USER_IND_COLUMNS contient les noms d'index de tables et de colonne.

A- Créer un index

Un index peut être créé par la commande suivante :

CREATE [UNIQUE] INDEX nom_index

ON nom_table (nom_col1 , nom_col2, ...)

[PCTFREE nombre]

[COMPRESS | NOCOMPRESS]

[ROWS = nombre_lignes] ;

dans laquelle :

L'option UNIQUE indique que l 'on interdit que deux l ignes aient la

même valeur dans la colonne indexée.

PCTFREE précise le pourcentage de place laissé l ibre dans les blocs

d'index à la création de l ' index. Cette place l ibre évi tera une

réorganisation de l ' index des les premières insertions de nouvel les

clés. La valeur par défaut est 20%.

NOCOMPRESS indique que l 'on ne veut pas comprimer les clés.

ROWS est une estimation du nombre de l ignes, permettant

d'optimiser l 'algorithme de classement.

Un index peut être créé dynamiquement sur une table contenant déjà des

l ignes. Il sera ensuite tenu à jour automatiquement lors des modifications

de la table.

DEES INFORMATIQUE

67

V 1.0.0.a

Index concaténé :

Un index concaténé est un index portant sur plusieurs colonnes.

Exemple :

CREATE INDEX xemp

ON (n_dept,num) ;

Les index concaténés peuvent être uti l isés pour matérial iser une clé

composée de plusieurs colonnes.

SQL sait uti l iser un index concaténé même si le critère de recherche ne

porte pas sur toutes les colonnes présentes dans l 'index.

Exemple : L'index ci-dessus est uti l isable si l 'on ne connait que le numéro

de département.

SELECT nom

FROM emp

WHERE n_dept = 20 ;

On peut créer plusieurs index indépendants sur une même table.

Les requêtes SQL sont transparentes au fait qu'i l existe un index ou non.

C'est l 'optimiseur du système de gestion de bases de données qui, au

moment de l 'exécution de chaque requête, recherche s'i l peut s'aider ou

non d'un index.

Index comprimé et non comprimé :

Les clés dans les index peuvent être comprimées ou non. La compression

est une technique permettant de réduire dans des proportions très

importantes (d'autant plus que la clé est longue) le volume de l ' index.

En contrepartie, i l faut parfois un traitement supplémentaire pour

recomposer la clé lors des mises à jour de l ' index.

Par défaut, les index sont comprimés, les avantages de réduction de tai l le

l 'emportant sur les inconvénients dans la plupart des cas.

SQL sait exécuter certaines requêtes directement au niveau de l ' index

sans passer par le segment de données, si l ' index est non comprimé et si

tous les champs résultats de la requête sont dans l ' index.

Exemple : L'index crée par :

CREATE INDEX x

ON emp (num, nom)

nocompress ;

DEES INFORMATIQUE

68

V 1.0.0.a

Permettra de répondre à la question :

SELECT nom

FROM emp

WHERE num < 17217 ;

Sans l ire la table puisque toutes les informations se trouvent dans l ' index

et que l ' index est non concaténé.

B- Modifier un index

La commande ALTER INDEX permet de modifier un index.

ALTER INDEX [schema.]index options

C- Supprimer un index

Un index peut être supprimé dynamiquement par la commande :

DROP INDEX nom_index;

L'espace l ibéré reste attaché au segment d'index de la table : i l pourra

être uti l isé pour un autre index sur la même table.

L'espace ne sera rendu à la partit ion que lors de la suppression de la

table.

Prérequis :

Pour pouvoir détruire un index, i l faut soit être propriétaire de cette

index, soit avoir le privi lège DROP ANY INDEX.

Les synonymes

A- Créer un Synonyme

C'est quoi un synonyme (SYNONYM) ? C'est un Alias sur un Objet de la

base ou Schéma, une sorte de raccourcis. L'Objet peut être une Table, une

Vue, une Séquence…

Le synonyme peut être Public ou Privé :

Public, i l sera accessible à partir de tous schéma et user.

Privé i l sera accessible uniquement à parti r du schéma dans lequel i l

a été créé.

DEES INFORMATIQUE

69

V 1.0.0.a

Pourquoi créer des synonymes?

Masquer le vrai nom des objets et leur local isation.

Simpli fier les noms des objets.

Éviter le pré-fixage dans les requêtes avec le nom de son

propriétaire.

Syntaxe :

CREATE [OR REPLACE] [PUBLIC] SYNONYM nom_synonyme FOR

objet;

Création d'un synonyme avec CREATE SYNONYM

Tout d'abord vous devez avoir le privilège :

CREATE SYNONYM pour créer un synonyme Privé dans votre

schéma.

CREATE ANY SYNONYM pour créer un synonyme Privé dans

n'importe quel schéma.

CREATE PUBLIC SYNONYM pour créer un synonyme Publ ic

PUBLIC donne l 'accès à tous les uti l isateurs

Exemple : créer un synonyme sur la séquence EMP_EMPNO

CREATE PUBLIC SYNONYM ma_sequence FOR EMP_EMPNO;

B- Modifier un synonyme

L'instruction OR REPLACE vu précédemment spécifique à Oracle est

uti l isée pour modifier le synonyme.

C-supprimer un synonyme

Un synonyme peut être supprimé par la commande :

DROP SYNONYME nom_synonyme;

Les clusters

A-Introduction - Généralités

Définition :

Le cluster est une organisation physique des données qui consiste à

regrouper physiquement (dans un même bloc disque) les l ignes d'une ou

DEES INFORMATIQUE

70

V 1.0.0.a

plusieurs tables ayant une caractérist ique commune (une même valeur

dans une ou plusieurs colonnes) constituant la clé du cluster.

La mise en cluster a trois objectifs :

accélérer la jointure selon la clé de cluster des tables mises en

cluster,

accélérer la sélection des l ignes d'une table ayant même valeur de

clé, par le fait que ces l ignes sont regroupées physiquement,

économiser de la place, du fait que chaque valeur de la clé du

cluster ne sera stockée qu'une seule fois.

Le regroupement en cluster est totalement transparent à l 'uti l isateur : des

tables mises en cluster sont toujours vues comme des tables

indépendantes.

Par exemple, on pourrait mettre en cluster les tables emp et dept selon

n_dept. Ces tables seraient réorganisées de la façon suivante : un bloc de

cluster serait créé pour chaque numéro de département, ce bloc contenant

à la fois les l ignes de la table emp et de la table dept correspondant à ce

numéro de département. La jointure entre les tables emp et dept selon

n_dept deviendrait alors beaucoup plus rapide, puisqu'elle serait déjà

réal isée dans l 'organisation physique des tables.

Pour que l 'on puisse mettre une table en cluster, i l faut que l 'une au moins

des colonnes faisant partie du cluster soit définie comme obl igatoire (NOT

NULL).

On peut indexer les colonnes d'une table en cluster, y compris les

colonnes correspondant à la clé ou à une partie de la clé du cluster. La clé

el le-même est automatiquement indexée, on peut éventuel lement la

réindexer pour créer un index unique servant à contrôler son unicité.

B- Créer un cluster :

Avant de pouvoir mettre en cluster une ou plusieurs tables, i l faut créer le

cluster au moyen de la commande CREATE CLUSTER dont la syntaxe est

la suivante :

CREATE CLUSTER nom_cluster

(cle1 type1,

cle2 type2,

.. .)

DEES INFORMATIQUE

71

V 1.0.0.a

où l 'on donne un nom au cluster, et où l 'on définit le nom et le type des

colonnes constituant la clé du cluster.

CREATE CLUSTER nom_cluster

(cle1 type1,

cle2 type2,

.. .)

[SIZE taille_du_bloc]

[COMPRESS | NOCOMPRESS]

[SPACE nom_de_space_definition]

dans laquelle :

SIZE est la tai l le d'un bloc de cluster. Cette tai l le peut varier de 1/6

de bloc oracle à 1 bloc oracle (2k octets sur vax/vms), ce paramètre

doit être choisi de façon à avoir un bon remplissage des blocs.

COMPRESS|NOCOMPRESS est relati f à l ' index qui sera crée sur la

clé du cluster

SPACE spécifie le SPACE DEFINITION qui définira les paramètres

d'al location d'espace pour le cluster.

Exemple :

CREATE CLUSTER DEM

(DEPNO NUMBER)

SIZE 512;

C- Mise en cluster d'une table :

En principe c'est dès sa création qu'i l faut spécifier si une table sera

implantée dans un cluster.

Lors de la création de la table :

L'option cluster de l 'ordre CREATE TABLE permet de spécif ier que la table

doit être mise en cluster. Le cluster doit déjà exister.

CREATE TABLE nom_table

(nom_col1 TYPE1 NOT NULL ,

(nom_col2 TYPE2 NOT NULL , ...)

CLUSTER NOM_CLUSTER (nom_coli, nom_colj...)

DEES INFORMATIQUE

72

V 1.0.0.a

nom_col i , nom_colj sont des noms de colonnes de la table, el les seront

identi fiées une à une aux colonnes clés du cluster spécifiées à la création

du cluster.

Table déjà existante :

Il est possible de modifier un cluster à l ’aide de la commande ALTER

CLUSTER.

ALTER CLUSTER schema.cluster options

E- Supprimer un cluster

Un cluster ne contenant aucune table peut être supprimé par la

commande:

DROP CLUSTER nom_cluster ;

Remarque : Les performances du cluster ne sont valables que si on n'a

pas de blocs chaînés (ex de grande table).

2. ORACLE PL/SQL

2.1 Historique et présentation générale du langage PL/SQL

PL/SQL est le langage procédural d'Oracle. Il est une extension du SQL qui

est un langage ensembliste. Il est spécifique à Oracle.

PL/SQL permet de gérer des traitements qui uti l isent les instructions SQL

dans un langage procédural.

Les instructions de manipulation des données, de description des données,

de contrôle des transactions, les fonctions SQL peuvent être uti l isées avec

la même syntaxe.

La gestion des variables et des structures de contrôle (tests, boucles)

augmente la capacité de traitement des données.

La gestion des curseurs et du traitement des erreurs accroît les

possibi l i tés de traitements.

Les instructions sont regroupées dans une unité appelée bloc qui ne

génère qu'un accès à la base.

DEES INFORMATIQUE

73

V 1.0.0.a

Les blocs ou procédures PL/SQL sont compilés et exécutés par le moteur

PL/SQL.

Ce moteur est intégré au moteur de la base de données et dans un certain

nombre d'outi ls (Forms, Report).

En résumé, PL/SQL permet de construire des appl ications

Les principaux avantages / inconvénients sont les suivants :

Aucune connaissance dans le lange SQL est nécessaire

Permet le traitement par bloc de SQL

Uti l isation de variable de stockage et de type simple et structuré

dynamique

Traitements plus complexes, notamment pour la gestion des cas

particul iers et des erreurs.

Uti l isation de l ibrair ies standards prédéfinies (suppl ied PLSQL

packages, comme les RDBMS_xxx)

Paramétrage et création d'ordres SQL dynamiques.

Le PL/SQL peut être utilisé sous 3 formes :

Un bloc de code, exécuté comme une commande SQL, via un

interpréteur standard

Un fichier de commande PL/SQL

Un programme stocké (procédure, fonct ion, package ou trigger)

2.2 Présentation du PL/SQL Engine

PL/SQL est un langage structuré en blocs, constitués d'un ensemble

d'instructions.

Un bloc PL/SQL peut être "externe", on dit alors qu'i l est anonyme, ou

alors stocké dans la base de données sous forme de procédure, fonction ou

trigger.

Un bloc PL/SQL est intégralement envoyé au moteur PL/SQL, qui traite

chaque instruction PL/SQL et sous-traite les instructions purement SQL au

moteur SQL, afin de réduire le trafic réseau.

DEES INFORMATIQUE

74

V 1.0.0.a

PL/SQL architecture :

Environnement PL/SQL Moteur PL/SQL dans Oracle :

Chaque bloc PL/SQL peut être constitué de 3 sections :

Une section facultative de déclaration et init ial isation de types,

variables et constantes

Une section obl igatoire contenant les instructions d'exécution

Une section facultative de gestion des erreurs

Structure d'un Block PL/SQL :

[ DECLARE ]

- Variables, constantes, curseurs,

& exceptions

BEGIN

- Ordres SQL

- Instructions de Contrôle PL/SQL

- Branchements conditionnels, etc.

DEES INFORMATIQUE

75

V 1.0.0.a

[ EXCEPTION ]

- Traitements à effectuer si erreurs

END ;

Un bloc PL/SQL minimum peut être représenté de la façon suivante :

BEGIN

Nul l ;

END ;

Le mot clé BEGIN détermine le début de la section des instructions

exécutables.

Le mot clé END; indique la fin de la section des instructions exécutables.

Une seule instruction figure dans ce bloc : Nul l; qui ne génère aucune

action.

Ce bloc PL/SQL ne fait donc absolument rien !

La section déclarative (facultative) d'un bloc débute par le mot clé

DECLARE.

El le contient toutes les déclarations des variables qui seront uti l isées

localement par la section exécutable, ainsi que leur éventuel le

init ial isation.

Exemple :

DECLARE

LC$Chaine VARCHAR2(15) := 'Salut Monde' ;

BEGIN

DBMS_OUTPUT.PUT_LINE( LC$Chaine ) ;

END ;

Une variable LC$Chaine est déclarée de type VARCHAR2(15) et init ial isée

avec la valeur 'Salut Monde' ;

Dans la section exécutable, cette variable est transmise à la fonction

DBMS_OUTPUT() pour être affichée à l 'écran.

Cette section ne peut pas contenir d'instructions exécutables. Toutefois, i l

est possible de définir dans cette section des procédures ou des fonctions

contenant une section exécutable.

DEES INFORMATIQUE

76

V 1.0.0.a

Toute variable doit avoir été déclarée avant de pouvoir être uti l isée dans

la section exécutable.

La section de gest ion des erreurs (facultative) débute par le mot clé

EXCEPTION.

El le contient le code exécutable mis en place pour la gestion des erreurs

Lorsqu'une erreur intervient dans l 'exécution, le programme est stoppé et

le code erreur est transmis à cette section.

Exemple :

DECLARE

LC$Chaine VARCHAR2(15) := 'Hel lo World' ;

BEGIN

DBMS_OUTPUT.PUT_LINE( LC$Chaine ) ;

EXCEPTION

When OTHERS then

Nul l ;

END ;

Les erreurs doivent être interceptées avec le mot clé WHEN suivi du code

erreur ciblé. Ici , le code OTHERS qui définit toutes les erreurs non

interceptées individuel lement par les clauses WHEN précédentes.

Cette section peut elle-même contenir d'autres blocs PL/SQL.

Les blocs PL/SQL peuvent être imbriqués les uns dans les autres :

DECLARE

#

BEGIN

DECLARE

#..

BEGIN

##

BEGIN

###

END ;

###

END ;

##..

END ;

DEES INFORMATIQUE

77

V 1.0.0.a

Voici quelques règles à respecter lors de la création d’un bloc

PL/SQL :

Les instructions peuvent être écrites sur plusieurs l ignes.

Les identi fiants doivent :

� Contenir jusqu'à 30 caractères.

� Être encadrés de guil lemets s’i ls contiennent un mot réservés.

� Commencer par une lettre.

� Avoir un nom distinct de celui d'une table ou d'une colonne de la

base.

Les chaînes de caractères et les dates doivent être entourées de

simples cotes ( ' ' ).

Les nombres peuvent avoir de simples valeurs ou une notation

scienti fique

Les commentaires peuvent être

� sur plusieurs l ignes avec :

/* début de commentaire

fin de commentaire*/

� sur une l igne précédée de :

-- début et fin de commentaire

2.3 Eléments du langage

Gestion des Variables en PL/SQL

Les variables doivent être déclarées et init ial isées dans la section

déclarative.

L'assignation d'une valeur à une variable peut être faite de 2 façons

différentes :

Leur init ial isation, facultative, s'effectue avec l 'opérateur :=

Par l ' intermédiaire d'un ordre SELECT # INTO ou FETCH # INTO

Syntaxe avec :=:

NomVar [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr];

Remarques :

Adopter des conventions pour nommer des objets.

Initial iser les constantes et les variables déclarées NOT NULL.

Déclarer au plus un identi fiant par l igne.

Le type peut être primiti f ou objet.

DEES INFORMATIQUE

78

V 1.0.0.a

Exemple :

DECLARE

LN$Nbre NUMBER(3) := 0 ;

LD$Date DATE := SYSDATE ;

LC$Nom VARCHAR2(10) := 'PL/SQL' ;

Une constante est une variable dont l ' init ial isation est obl igatoire et dont

la valeur ne pourra pas être modifiée en cours d'exécution.

El le est déclarée avec le mot clé : CONSTANT qui doit précéder le type

Exemple :

DECLARE

LN$Pi CONSTANT NUMBER := 3. 1415926535 ;

PL/SQL n'est pas sensible à la casse. Pour lui les expressions suivantes

sont équivalentes :

NOM_VARIABLE NUMBER ;

Nom_Variable Number ;

nom_variable number ;

Exemple avec SELECT # INTO ou FETCH # INTO:

DECLARE

LC$Nom_emp emp.nom%Type ;

Cursor C_EMP Is

SELECT nom FROM emp WHERE num = 1014;

BEGIN

SELECT nom INTO LC$Nom_emp FROM emp WHERE num = 1014;

OPEN C_EMP ;

FETCH C_EMP Into LC$Nom_emp ;

CLOSE C_EMP ;

END ;

Visualiser les résultats à la console ou dans un fichier :

DBMS_OUTPUT.PUT_LINE

UTL_FILE

Principaux types primitifs :

BINARY_INTEGER

NUMBER [ ( precision, scale ) ]

DEES INFORMATIQUE

79

V 1.0.0.a

CHAR [ ( longueur_maximum ) ]

LONG

VARCHAR2 ( longueur_maximum )

DATE

BOOLEAN

ROWID (pseudo-colonne)

Etc…

Exemple :

c CHAR( 1 );

nom VARCHAR2(10) := ‘Scott ’;

cpt BINARY_INTEGER := 0;

tot NUMBER( 9, 2 ) := 0;

Dat := SYSDATE + 7;

nb CONSTANT NUMBER ( 3, 2 ) := 8.25;

vrai BOOLEAN NOT NULL := TRUE;

Le typage dynamique : l‘attribut %TYPE

Le typage dynamique permet de déclarer une variable à partir :

D'une autre variable déjà déclarée

De la définit ion d’un attribut de la base de données

Au niveau de la syntaxe, %TYPE est préfixer avec :

Le nom de la variable déclarée précédemment

Le nom table.colonne de la base de données

Remarque :

Dans ce cas PL/SQL évalue le type de donnée et sa tai l le (ce qui peut

nécessiter un accès au dictionnaire de la base).

Exemple :

DECLARE

nom emp.nom%TYPE;

job emp.fonction%TYPE;

balance NUMBER( 7, 2 );

min_balance balance%TYPE := 10;

Remarque :

Le type de données de la colonne peut être inconnu.

DEES INFORMATIQUE

80

V 1.0.0.a

Le type de données de la colonne peut changer en exécution.

Maintenance plus aisée.

Exemple :

DECLARE

Mon_nom emp.nom%TYPE;

BEGIN

SELECT nom INTO Mon_nom FROM emp

WHERE ROWNUM = 1;

DBMS_OUTPUT.PUT_LINE(Mon_nom);

END;

L'Attribut %ROWTYPE

Cette attribut permet de déclarer une variable à partir d'un ensemble de

colonnes d'une table ou d'une vue. Il faut préfixer %ROWTYPE avec le nom

de la table de la base de données.

Les champs dans le RECORD ont les mêmes noms et les mêmes types de

données que les colonnes de la table ou de la vue associées.

Exemple :

DECLARE

empl emp%ROWTYPE;

BEGIN

SELECT * INTO empl FROM emp WHERE ROWNUM = 1;

DBMS_OUTPUT.PUT_LINE(empl.nom);

DBMS_OUTPUT.PUT_LINE(empl.salaire);

END;

Les intérêts de ce type sont:

Le nombre de colonnes & leur type peuvent être inconnus.

Le nombre de colonnes & leur type peuvent changer.

Uti le (sinon indispensable) lorsqu'on recherche

� Une l igne avec l 'ordre SELECT.

� Plusieurs l ignes avec un curseur expl icite.

Imbrication et Portée d'une Variable

Les instructions peuvent être imbriquées là où les instructions exécutables

sont autorisées.

DEES INFORMATIQUE

81

V 1.0.0.a

La section EXCEPTION peut contenir des blocs imbriqués.

Les boucles possèdent leur propre portée les incréments y sont définis

Un identifiant est visible dans les régions où on peut référencer cet

identifiant :

Un bloc voit les objets du bloc de niveau supérieur.

Un bloc ne voit pas les objets des blocs de niveau inférieur.

Opérateurs et Fonctions

A- Opérateurs

Ils sont identiques à SQL :

Valeurs de vérité

Arithmétiques

Concaténation

Opérateur exponentiel ( ** )

Parenthèsage pour contrôler la priorité des opérations

B- Fonctions

Elles sont identiques à SQL :

Intégrées :

� Numériques

� Caractères

� Conversion de type

� Date

Non intégrées :

� GREATEST

� LEAST

� Fonctions de groupe

Exemple :

DECLARE

enr emp%ROWTYPE;

adr VARCHAR2(64);

BEGIN

SELECT * INTO enr FROM emp WHERE ROWNUM = 1;

adr := UPPER(enr.nom) || CHR(10) ||

LOWER (enr.fonction) || CHR(10) ||

DEES INFORMATIQUE

82

V 1.0.0.a

TO_CHAR (enr.embauche,'DD/MM/YYYY');

DBMS_OUTPUT.PUT_LINE(adr);

END;

C- Conversion de Type de Donnée

Convertir des données en type de données comparables.

Des types de données hétérogènes peuvent provoquer une erreur et/ou

affecter les performances.

Fonctions de Conversion :

TO_CHAR

TO_DATE

TO_NUMBER

Interaction avec la base

Uti l iser les commandes SELECT, INSERT, UPDATE, DELETE, COMMIT et

ROLLBACK dans un bloc PL/SQL.

Déterminer le résultat des ordres SQL en uti l isant les attributs du curseur

implicite.

Contrôler les transactions avec PL/SQL.

Exemple :

DECLARE

Mon_nom emp.nom%TYPE;

matricule emp.num%TYPE := 7839;

Mon_salaire emp.salaire%TYPE := 5500;

BEGIN

UPDATE emp SET salaire = Mon_salaire

WHERE num = matricule;

SELECT nom ,salaire

INTO Mon_nom, Mon_salaire

FROM emp

WHERE num = matricule;

DBMS_OUTPUT.PUT_LINE (Mon_nom||’ ‘||TO_CHAR (Mon_salaire));

END;

DEES INFORMATIQUE

83

V 1.0.0.a

Les transactions : COMMIT et COLLBACK

Une transaction est une unité de travai l composée de plusieurs actions

élémentaires.

Chaque transaction est considérée comme atomique et fait évoluer la base

de données d'un état cohérent vers un autre état cohérent.

Soit une transaction s'exécute correctement et les modifications qu'el le a

engendrées deviennent publ iques et définitives ("Commit"), soit tout se

passe comme si el le n'avait jamais existé ("Rollback").

Une transaction démarre lors de la première commande LMD qui suit un

COMMIT ou un ROLLBACK.

Une transaction se termine en utilisant l'une des commandes SQL :

COMMIT ou ROLLBACK.

Commandes de verrouillage supplémentaires:

SELECT . . . FOR UPDATE .

A- COMMIT

Cette instruction permet d'enregistrer en base toutes les modifications

effectuées au cours de la transaction.

B- ROLLBACK

Cette instruction permet d'annuler en base toutes les modifications

effectuées au cours de la transaction.

BEGIN

INSERT INTO emp( num, nom, fonction )

VALUES ( 9991, 'Dupontont', 'CLERK' ) ;

INSERT INTO emp( num, nom, fonction )

VALUES ( 9992, 'Duboudin', 'CLERK' ) ;

SAVEPOINT mise_a_jour ;

UPDATE emp SET salaire = 2500 WHERE num > 9990 ;

ROLLBACK TO SAVEPOINT mise_a_jour ;

COMMIT ;

END ;

DEES INFORMATIQUE

84

V 1.0.0.a

Remarque :

Avec TO SAVEPOINT nom savepoint, l 'annulation porte sur toutes les

modifications effectuées, à partir de l 'étiquette nom savepoint.

Dans cet exemple, une étiquette SAVEPOINT est placée après les

instructions d'insert ion.

Un ROLLBACK TO SAVEPOINT est ajouté après l ' instruction de mise à

jour puis un COMMIT est effectué.

Les insertions sont bien enregistrées en base mais pas la mise à jour.

OPEN

La commande OPEN permet l ’ouverture d’un curseur SQL.

Le curseur doit avoir été préalablement défini dans la section déclarative.

Exemple :

DECLARE

LC$Nom_emp emp.nom%Type ;

Cursor C_EMP ( LN$Numemp IN emp.num%Type ) Is

SELECT nom FROM emp WHERE num = LN$Numemp

BEGIN

OPEN C_EMP ( 1024 );

FETCH C_EMP INTO LC$Nom_emp ;

CLOSE C_EMP ;

END ;

OPEN FOR

Ouverture d'un curseur SQL incluant l 'ordre select correspondant.

La déclaration préalable du curseur dans la section déclarative n'est pas

nécessaire.

Exempe :

DECLARE

LC$Nom_emp emp.nom%Type ;

BEGIN

OPEN C_EMP FOR 'SELECT nom FROM emp WHERE num = 1024' ;

FETCH C_EMP INTO LC$Nom_emp ;

CLOSE C_EMP ;

END ;

DEES INFORMATIQUE

85

V 1.0.0.a

CLOSE

Cette instruction est uti l isée pour fermer un curseur préalablement ouvert

avec l ' instruction OPEN :

CLOSE(nom_curseur)

Après cette instruct ion, le curseur n'est plus val ide et toute instruction s'y

reportant génèrera une erreur.

(voir exemple précédent)

EXIT

Cette instruction permet de quitter une structure itérative.

Exit saute à l ' instruction suivant le mot clé END LOOP;

Dans le cas de boucles imbriquées, l ' indication d'un label permet de

quitter tout ou partie des boucles imbriquées.

Exemple :

DECLARE

LN$Num pls_integer := 0 ;

BEGIN

LOOP

LN$Num := LN$Num + 1 ;

dbms_output.put_l ine ( to_char( LN$Num ) ) ;

EXIT WHEN LN$Num > 3 ; -- sortie de la boucle lorsque

LN$Num est supétieur à 3

END LOOP ;

END ;

FETCH

Cette instruction permet de ramener une l igne d'un curseur préalablement

ouvert avec l ' instruction OPEN ou OPEN FOR .

-> Pour voir des exemples, reportez-vous à la section Les curseurs

explicites.

FORALL

Cette instruction permet de générer des ordres SQL de masse basés sur le

contenu d'une col lection.

DEES INFORMATIQUE

86

V 1.0.0.a

GOTO et RETURN

A- GOTO

Cette instruction permet d'exécuter un saut dans le code vers le label

précisé.

Une instruction val ide doit suivre la déclaration du label.

Exemple :

DECLARE

LN$I pls_integer := 0 ;

LN$J pls_integer := 0 ;

BEGIN

LOOP

LN$I := LN$I + 1 ;

LOOP

LN$J := LN$J + 1 ;

dbms_output.put_l ine( to_char( LN$I ) || ', ' || to_char(

LN$J ) ) ;

IF LN$J > 3 THEN GOTO sortie ;

END IF ;

END LOOP ;

END LOOP ;

<<sortie>>

nul l ;

END ;

B- RETURN

Cette instruction permet de sortir d'une procédure ou d'une fonction

Structure générale d’un programme PL/SQL.

2.4 Les structures de contrôle

Tout langage procédural a des structures de contrôle qui permettent de

traiter l ' information d'une manière logique en contrôlant le flot des

informations. Les structures disponibles au sein de PL/SQL incluent IF-

THEN ELSE, LOOP, FOR, WHILE et EXIT-WHEN. Ces structures procurent de

la flexibi l i té dans la manipulation des données de la base de données.

DEES INFORMATIQUE

87

V 1.0.0.a

FOR, LOOP et WHILE

Ces instructions déclarent une structure de type itérative (boucle).

A- LOOP

LOOP instruction;[instruction;[...]] END LOOP;

Cette syntaxe met en place une boucle simple ou aucune condition de

sortie n'est indiquée. Il faut donc une instruction EXIT pour sortir de ce

type de boucle.

Exemple :

DECLARE

LN$I pls_integer := 0 ;

BEGIN

LOOP

LN$I := LN$I + 1 ;

dbms_output.put_l ine( to_char( LN$I) ) ;

EXIT WHEN LN$I > 2 ;

END LOOP ;

END ;

B- WHILE

WHILE expression booléenne LOOP instruction;[instruction;[...]]

END LOOP;

Cette syntaxe permet de mettre en place une boucle dont la condition de

test est évaluée au début.

Si expression booléenne donne le résultat FALSE, les instructions

suivantes jusqu'au mot clé END LOOP; ne seront pas exécutées.

Exemple :

DECLARE

LN$I pls_integer := 0 ;

BEGIN

WHILE LN$I < 3

LOOP

LN$I := LN$I + 1 ;

dbms_output.put_l ine( to_char( LN$I) ) ;

END LOOP ;

END ;

DEES INFORMATIQUE

88

V 1.0.0.a

C- FOR

FOR variable index IN [REVERSE] borne_début..borne_fin LOOP

instruction;[instruction;[...]] END LOOP;

Cette syntaxe permet de mettre en place une boucle dont le nombre

d'i térations est fixé dès l 'entrée.

Variable index représente le nom de la variable qui servira

d'indice. Cette variable ne nécessite pas de définit ion préalable dans

la section déclarative.

Reverse permet de faire varier l ' indice dans le sens contraire

(décrémentation).

Borne début représente l ' indice de départ.

Borne fin représente l ' indice de fin.

Exemple :

DECLARE

LN$I pls_integer := 0 ;

BEGIN

FOR i IN 1..3

LOOP

dbms_output.put_l ine( to_char( i ) ) ;

END LOOP ;

END ;

Exemple:

DECLARE

LN$I pls_integer := 0 ;

DEGIN

FOR i IN REVERSE 1..3

LOOP

dbms_output.put_l ine( to_char( i ) ) ;

END LOOP ;

END ;

D- FOR (curseur) :

Voir chapitre sur les curseurs

DEES INFORMATIQUE

89

V 1.0.0.a

IF-THEN-ENDIF

Cette instruction permet de faire des tests conditionnels.

Seuls les mots clé IF et END IF; sont obl igatoires. Les clauses ELSIF et

ELSE sont facultatives.

Exemple :

DECLARE

LN$I pls_integer := 0 ;

LN$J pls_integer := 0 ;

BEGIN

LOOP

LN$I := LN$I + 1 ;

LOOP

LN$J := LN$J + 1 ;

IF LN$J = 1 THEN

dbms_output.put_l ine( '1' ) ;

ELSIF LN$J = 2 THEN

dbms_output.put_l ine( '2' ) ;

ELSE

dbms_output.put_l ine( '3' ) ;

GOTO sortie ;

END IF ;

END LOOP ;

END LOOP ;

<<sortie>>

nul l ;

END ;

CASE

Cette instruction permet de mettre en place des structures de test

conditionnel de type IF .. ELSE .. END IF, à la grande différence qu'el le

est uti l isable dans les requêtes SQL

2 syntaxes sont possibles :

CASE simple

[<<label>>] CASE opérateur { WHEN contenu_opérateur THEN {

instruction;} ... }... [ELSE { instruction;}...]

END CASE [label];

DEES INFORMATIQUE

90

V 1.0.0.a

CASE de recherche

[<<label>>] CASE { WHEN expression_booléenne THEN {

instruction;} ... }... [ELSE { instruction;}...] END CASE [label];

Opérateur peut être n'importe quel type PL/SQL à l'exception des

objets suivants :

BLOB

BFILE

Type objet

Enregistrement

Col lection (NESTED TABLE, INDEX-BY TABLE, VARRAY)

Pour le CASE simple, chaque mot clé WHEN véri fie l 'égal i té entre

opérateur et contenu_opérateur. Dans l 'affirmative, l ' instruction suivant le

mot clé THEN est exécutée, puis la structure CASE est quittée et

l 'exécution du programme est reprise après le mot clé END CASE .

Exemple :

DECLARE

LN$Num pls_integer := 0 ;

BEGIN

LOOP

LN$Num := LN$Num + 1 ;

CASE LN$Num

WHEN 1 THEN dbms_output.put_l ine( '1' ) ;

WHEN 2 THEN dbms_output.put_l ine( '2' ) ;

WHEN 3 THEN dbms_output.put_l ine( '3' ) ;

ELSE

EXIT ;

END CASE ;

END LOOP ;

END ;

Exemple de CASE de recherche :

DECLARE

LN$Num pls_integer := 0 ;

BEGIN

LOOP

DEES INFORMATIQUE

91

V 1.0.0.a

LN$Num := LN$Num + 1 ;

CASE

WHEN LN$Num BETWEEN 1 AND 3 THEN

dbms_output.put_l ine( To_char( LN$Num )) ;

WHEN LN$Num < 5 THEN dbms_output.put_l ine(

To_char( LN$Num )) ;

ELSE dbms_output.put_l ine( To_char( LN$Num )) ;

END CASE ;

EXIT WHEN LN$Num = 5 ;

END LOOP ;

END ;

Cette fois l 'opérateur est précisé sur chaque l igne WHEN.

Il ne s'agit alors plus d'un simple test d'égal ité, mais de n'importe quel le

expression booléenne restituant un résultat TRUE ou FALSE.

On observe également que le débranchement dans une clause WHEN est

exclusif. En effet, dans chaque itération de boucle, la variable LN$Num est

inférieure à 5, mais n'est prise en compte dans la deuxième clause WHEN

que lorsque la première n'est plus véri fiée

Pour le CASE de recherche, l 'omission de la clause ELSE provoque une

erreur.

2.5 Les types de données composites

Les deux types de données composites de PL/SQL sont TABLE et RECORD .

Le type de donnée TABLE permet à l 'uti l isateur de définir un tableau

PL/SQL. Le type de données RECORD permet d'al ler au-delà de l 'attribut

de variable %ROWTYPE ; avec ce type, on peut spécifier des champs

définis par l 'uti l isateur et des types de données pour ces champs.

Table

Une table PL/SQL :

c’est une col lection ordonnée d’éléments du même type

est accessible uniquement en PL/SQL

stockés en mémoire, i ls peuvent grandir dynamiquement

i ls uti l isent des index non consécuti f

i ls ont le même format que des champs d’une table

on n’uti l ise pas SQL pour s’en servir

DEES INFORMATIQUE

92

V 1.0.0.a

Syntaxe :

DECLARE

TYPE nom_type IS TABLE OF type_scalaire

[ NOT NULL ] INDEX BY BINARY_INTEGER ;

Id_var nom_type ;

Exemple :

DECLARE

TYPE nom_table_pl IS TABLE OF VARCHAR2 (25) INDEX BY

BINARY_INTEGER;

mon_tableau nom_table_pl ;

Après avoir défini la structure du tableau, el le peut être uti l isée dans des

définit ions de variables, comme c'est le cas pour nom_table_pl dans

l 'exemple précédent.

RECORD

Peuvent contenir un ou plusieurs champs de type Scalaire, RECORD ou

TABLE.

Ils traitent un ensemble de champs comme une unité logique.

Ils sont pratiques pour récupérer et traiter les données d'une table.

Syntaxe :

DECLARE

TYPE nom_type IS RECORD

( champ1 type_att [NOT NULL] { := | DEFAULT expr}

[,champ2 type_att [NOT NULL] { := | DEFAULT expr }] );

id_var nom_type;

Exemple :

DECLARE

TYPE employe IS RECORD ( ename VARCHAR2( 25 ),

job VARCHAR2( 25 ),

sal NUMBER( 7,2 ) );

emp employe;

DEES INFORMATIQUE

93

V 1.0.0.a

Exemple :

DECLARE

TYPE employe IS RECORD ( ename VARCHAR2( 25 ),

job VARCHAR2( 25 ),

sal NUMBER( 7,2 ));

empl employe;

BEGIN

SELECT nom, fontion, salaire

INTO empl FROM emp

WHERE num = 7839;

DBMS_OUTPUT.PUT_LINE(empl.ename);

DBMS_OUTPUT.PUT_LINE(empl.job);

DBMS_OUTPUT.PUT_LINE(empl.sal);

END;

2.6 Les curseurs

PL/SQL uti l ise des curseurs pour tous les accès à des informations de la

base de données. Le langage supporte à la fois l 'emploi de curseurs

implicites et expl icites. Les curseurs impl icites sont ceux qui sont établ is

lorsqu'un curseur expl icite n'a pas été déclaré. Il faut uti l iser des curseurs

expl icites ou des curseurs de boucles FOR dans toutes les requêtes qui

renvoient plusieurs l ignes.

SELECT INTO (curseurs implicite)

Cette instruction permet d'exécuter un ordre Select impl icite.

Cet ordre ne doit ramener qu'une l igne sous peine de générer l 'exception

NO_DATA_FOUND si aucune l igne n'est ramenée ou TOO_MANY_ROWS

si plus d'une l igne sont ramenées.

Syntaxe :

SELECT l ist_attribut

INTO nom_var | nom_record

FROM table

WHERE condition;

Cette clause INTO est obl igatoire en PL/SQL. La clause INTO convient si

une seule l igne de données est renvoyée.

DEES INFORMATIQUE

94

V 1.0.0.a

Exemple :

DECLARE

nom_nom emp.nom%TYPE;

BEGIN

SELECT nom INTO mon_nom

FROM emp

WHERE num = 7839; -- matricule unique & NON NULL

DBMS_OUTPUT.PUT_LINE(mon_nom);

END;

Uti l isée avec la clause BULK COLLECT, el le permet de charger une

col lection avec les l ignes ramenées.

Dans l 'exemple suivant toutes les l ignes de la table EMP sont chargées

dans une col lection.

DECLARE

TYPE TYP_TAB_EMP IS TABLE OF emp%Rowtype ;

Tabemp TYP_TAB_EMP ;

BEGIN

SELECT

*

BULK COLLECT

INTO

Tabemp

FROM emp;

FOR i IN Tabemp.first..Tabemp.last LOOP

dbms_output.put_l ine( To_char( Tabemp(i).num ) || ' - ' ||

Tabemp(i).nom ) ;

END LOOP ;

END ;

Les curseurs explicites

Un curseur est une zone mémoire de tail le fixe, uti l isée par le moteur SQL

pour analyser et interpréter un ordre SQL.

Un curseur expl icite, contrairement au curseur implicite (SELECT INTO)

est géré par l 'uti l isateur pour traiter un ordre SELECT qui ramène

plusieurs l ignes.

Tout curseur expl icite géré dans la sect ion exécution doit avoir été déclaré

dans la section déclarative.

DEES INFORMATIQUE

95

V 1.0.0.a

Déclaration de curseur :

CURSOR name IS

(instruction sql)

L'instruction SQL peut être n'importe quel le requête val ide. Après

l ' init ial isation d'un curseur, les act ions d'un curseur peuvent être

contrôlées avec les instructions OPEN, FETCH et CLOSE.

Exemple :

DECLARE

-- déclaration du curseur

CURSOR C_EMP IS

SELECT num, nom, fonction FROM emp;

-- variables d'accuei l

LN$Num emp.num%Type ;

LC$Nom emp.nom%Type ;

LC$Job emp.fonction%Type ;

BEGIN

OPEN C_EMP ; -- ouverture du curseur

LOOP -- boucle sur les l ignes

FETCH C_EMP INTO LN$Num, LC$Nom, LC$Job ;

-- Lecture d'une l igne

EXIT WHEN C_EMP%NOTFOUND ; -- sortie lorsque le

curseur ne ramène plus de l igne

END LOOP ;

CLOSE C_EMP ; -- fermeture du curseur

END ;

Un curseur nommé C_EMP est déclaré avec l 'ordre Select correspondant

(CURSOR C_EMP IS...)

Il est ouvert avec l ' instruction OPEN, lu avec l ' instruction FETCH et fermé

avec l ' instruction CLOSE.

Paramètres du curseur :

Un curseur est paramétrable. On peut donc uti l iser le même curseur pour

obtenir di fférents résultats.

DEES INFORMATIQUE

96

V 1.0.0.a

DECLARE

-- déclaration du curseur

CURSOR C_EMP ( PN$Num IN emp.num%Type )IS

SELECT num, nom, fonction

FROM emp

WHERE num = PN$Num;

-- variables d'accuei l

LN$Num emp.num%Type ;

LC$Nom emp.nom%Type ;

LC$Job emp.fonction%Type ;

BEGIN

OPEN C_EMP( 7369 ) ; -- ouverture du curseur avec passage du

paramètre 7369

LOOP

…….

Déclaration d'une variable curseur :

DECLARE

TYPE TYP_REF_CUR IS REF CURSOR ;

-- variable curseur

CEMP TYP_REF_CUR ;

-- variables d'accuei l

LN$Num emp.num%Type ;

LC$Nom emp.nom%Type ;

LC$Job emp.fonction%Type ;

BEGIN

OPEN CEMP FOR 'SELECT num, nom, fonction FROM emp; --

ouverture du curseur

LOOP

FECTH CEMP INTO LN$Num, LC$Nom, LC$Job ; -- Lecture d'une

l igne

EXIT WHEN CEMP%NOTFOUND ; -- sort ie lorsque le curseur ne

ramène plus de l igne

END LOOP ;

CLOSE CEMP ; -- fermeture du curseur

END ;

DEES INFORMATIQUE

97

V 1.0.0.a

Spécification d'un curseur :

Les attributs de curseur :

%FOUND : Cet attribut prend la valeur TRUE lorsqu’une l igne est

ramenée, sinon i l prend la valeur FALSE

%NOTFOUND : Cet attribut prend la valeur FALSE lorsqu’une l igne

est ramenée, sinon i l prend la valeur TRUE

%ISOPEN : Cet attribut prend la valeur TRUE lorsque le curseur

indiqué est ouvert, sinon i l prend la valeur FALSE

%ROWCOUNT : Cet attribut retourne le nombre de l ignes impactées

par la dernière instruction SQL

FOR (curseur)

Cette instruction permet de gérer un curseur sans uti l iser les ordres

OPEN, FETCH et CLOSE

Exemple :

DECLARE

-- Déclaration du curseur

CURSOR C_EMP IS

SELECT * FROM emp WHERE fonction = ' administrati f ';

BEGIN

FOR Cur IN C_EMP LOOP

dbms_output.put_l ine( To_char( Cur.num ) || ' - ' || Cur.nom )

;

END LOOP ;

END ;

La variable de curseur impl icite Cur, non définie dans la section

déclarative, doit être uti l isée pour manipuler dans la boucle, les objets du

curseur (To_char( Cur.num ),Cur.nom).

Instruction FOR et curseur paramétré :

DECLARE

-- Déclaration du curseur

CURSOR C_EMP ( PC$Job IN emp.fonction%Type ) IS

SELECT * FROM emp WHERE fonction = PC$Job;

BEGIN

FOR Cur IN C_EMP( 'administrati f ' ) LOOP

DEES INFORMATIQUE

98

V 1.0.0.a

dbms_output.put_l ine( To_char( Cur.num ) || ' - ' || Cur.nom )

;

END LOOP ;

END ;

Le passage des paramètres s'effectue sur le curseur déclaré (C_EMP) et

non sur la variable curseur (Cur).

3. GESTIONS DES EXCEPTIONS, PROCEDURES,

FONCTIONS ET TRIGGERS

3.1 Les exceptions

Généralités

Qu’est ce qu’une Exception ?

C’est une erreur PL/SQL déclenchée pendant l ’exécution du code.

Il y a deux modes d’exception :

Impl icitement, une erreur Oracle est apparue.

Expl icitement, par l ’uti l isateur.

Une exception ce gère dans la section EXCEPTION.

Capture des exceptions

Syntaxe dans la section exception :

EXCEPTION

WHEN exception1 [OR exception2 . . .] THEN

statement1;

statement2;

. . .

[WHEN exception3 [OR exception4 . . .] THEN

statement1;

statement2;

. . .]

[WHEN OTHERS THEN

statement1;

statement2;

. . .]

DEES INFORMATIQUE

99

V 1.0.0.a

Règle de capture :

Le mot-clé EXCEPTION démarre la section de gestion des exceptions.

Plusieurs EXCEPTION sont autorisées.

Une seule est exécutée avant de quitter le bloc.

Le gestionnaire d’Exception WHEN OTHERS capture toutes les erreurs qui

ne sont pas encore gérées.

WHEN OTHERS est la dernière clause.

Exemple :

DECLARE

X NUMBER;

BEGIN

X := 'YYYY';

DBMS_OUTPUT.PUT_LINE('IT WORKS');

EXCEPTION

WHEN VALUE_ERROR THEN

DBMS_OUTPUT.PUT_LINE( 'VALUE_ERROR EXCEPTION

HANDLER');

END;

VALUE_ERROR EXCEPTION HANDLER

Exceptions Oracle 9i prédéfinies

El les ne sont pas définies dans la section DECLARE .

El les sont déclenchées automatiquement par Oracle 9i.

El les uti l isent un nom standard dans la section EXCEPTION.

Exemples d’exceptions prédéfinies:

NO_DATA_FOUND

TOO_MANY_ROWS

INVALID_CURSOR

ZERO_DIVIDE

DUP_VAL_ON_INDEX

VALUE_ERROR

CURSOR_ALREADY_EXISTS

Exceptions Utilisateur

El les sont définies dans la section DECLARE.

DEES INFORMATIQUE

100

V 1.0.0.a

El les sont déclenchées expl icitement dans la section BEGIN par

l ' instruction RAISE .

Dans la section EXCEPTION, ont référence le nom défini dans la section

DECLARE.

Exemple :

DECLARE

DEPTNO_CODE NUMBER(2,0);

INVALID_DEPTNO_CODE EXCEPTION;

CURSOR c1 IS SELECT n_dept FROM dept WHERE ROWNUM = 1;

BEGIN

OPEN c1;

FETCH c1 INTO DEPTNO_CODE;

CLOSE c1;

IF DEPTNO_CODE NOT IN (11,22,33) THEN

RAISE INVALID_DEPTNO_CODE;

END IF;

DBMS_OUTPUT.PUT_LINE('EVERYTHING IS OK');

EXCEPTION

WHEN INVALID_DEPTNO_CODE THEN

DBMS_OUTPUT.PUT_LINE('INVALID DEPTNO_CODE');

END;

INVALID DEPTNO_CODE

Fonctions de Gestion des Erreurs

SQLCODE

Retourne la valeur numérique correspondant au code de l ’erreur.

SQLERRM

Retourne le message associé au numéro d’erreur qui est survenu.

3.2 Généralités sur les procédures et les fonctions

Une procédure est un ensemble de code PL/SQL nommé, défini par

l 'uti l isateur et généralement stocké dans la BDD.

Une fonction est identique à une procédure à la différence qu'el le retourne

une valeur.

DEES INFORMATIQUE

101

V 1.0.0.a

Un paquetage est le regroupement de plusieurs procédures et fonctions

dans un objet distinct.

Ces ensembles nommés sont stockés dans la base de données,

offrant les avantages suivants :

Le code relati f aux règles de gestion est central isé. Cela permet de

dissocier les fonctions au sein d'une équipe.

La partie traitement des règles de gestion est confiée à une partie

de l 'équipe et la conception des interfaces est confiée à l 'autre

partie.

Ces traitements stockés sont donc déportés des interfaces cl ientes,

permettant le partage du code entre plusieurs appl ications ainsi

qu'une amélioration des performances, car le code stocké est pré-

compilé.

Ces traitements sont accessibles par toute appl ication tierce

supportant l 'appel des procédures stockées (Sql*Plus, Forms,

Reports, Pro*C, Pro*Cobol, etc.).

Cela permet également de tirer parti de la réuti l isation des requêtes

dans la base qui se trouvent dans le pool partagé de la zone

SGA(System Global Area).

Pour créer un objet procédural, vous devez disposer du privi lège système

CREATE PROCEDURE pour votre schéma ou du privi lège système CREATE

ANY PROCEDURE pour la création dans un autre schéma.

Pour autoriser un autre schéma à exécuter une procédure de votre

schéma, vous devez lui octroyer le privi lège EXECUTE

GRANT EXECUTE ON ma_procedure TO autre_schéma

3.3 Les procédures

Une procédure est un ensemble de code PL/SQL nommé, défini par

l 'uti l isateur et généralement stocké dans la BDD.

Une procédure est paramétrable afin d'en faci l i ter la réuti l isation.

DEES INFORMATIQUE

102

V 1.0.0.a

Syntaxe :

CREATE [OR REPLACE] PROCEDURE proc_mini [parameters] [IN |

OUT | IN OUT | NOCOPY]

IS

BEGIN

NULL;

END;

IN(valeur par défaut) : indique que le paramètre transmis par le

programme appelant n'est pas modifiable par la procédure

OUT : indique que le paramètre est modifiable par la procédure.

IN OUT : indique que le paramètre est transmis par le programme

appelant et renseigné par la procédure.

NOCOPY : indique que le paramètre est transmis par référence

(pointeur) et non par copie de la valeur.

Par défaut, les paramètres sont transmis par copie, c'est à dire qu'un

espace mémoire est créé pour recevoir une copie de la valeur avec la

clause NOCOPY, aucun espace mémoire supplémentaire n'est créé, c'est

donc l 'adresse de l 'espace mémoire init ial qui est transmise, permettant

d'une part de ne pas gaspi l ler la mémoire disponible (surtout lorsqu'i l

s'agit de grands objets (LOB) et également d'éviter le temps nécessaire à

la gestion de ces nouveaux espace mémoire (empilement, dépi lement,

etc.).

Exemple:

CREATE OR REPLACE PROCEDURE Augmentation

(PN$Numemp IN emp.num%Type -- numéro de l 'employé

PN$Pourcent IN NUMBER -- pourcentage d'augmentation)

IS

BEGIN

-- augmentation de l 'employé

UPDATE emp SET salaire = salaire * PN$Pourcent

WHERE num = PN$Numemp ;

END;

La procédure Augmentation reçoit deux paramètres :

PN$Numemp en entrée (IN) de même type que la colonne num de la

table emp qui reçoit le numéro d'employé.

DEES INFORMATIQUE

103

V 1.0.0.a

PN$Pourcent en entrée (IN) de type NUMBER qui reçoit le

pourcentage d'augmentation.

Appel à cette procédure dans un bloc PL/SQL anonyme :

Les paramètres sont passés lors de l 'appel de la fonction.

Augmentation( 7369, 1.1 ) ; -- appel de la procedure

3.4 Les fonctions

Une fonction est identique à une procédure à la différence qu'el le retourne

obl igatoirement une valeur d'où le mot clé obl igatoire RETURN.

Syntaxe :

CREATE [OR REPLACE] FUNCTION function_name [parameters] [IN |

OUT | IN OUT | NOCOPY]

RETURN return_datatype;

IS

BEGIN

Return return_variable;

EXCEPTION

Return return_variable;

END;

Exemple:

CREATE OR REPLACE FUNCTION F_Test_Augmentation

(PN$Numemp IN emp.num%Type

,PN$Pourcent IN NUMBER) Return NUMBER

IS

LN$Salaire emp.salaire%Type ;

BEGIN

SELECT salaire INTO LN$Salaire FROM emp WHERE num =

PN$Numemp;

-- augmentation virtuel le de l 'employé

LN$Salaire := LN$Salaire * PN$Pourcent ;

Return( LN$Salaire ) ; -- retour de la valeur

END;

DEES INFORMATIQUE

104

V 1.0.0.a

3.5 Les packages

Un paquetage est un ensemble de procédures et fonctions regroupées dans

un objet nommé.

Par exemple, le paquetage Oracle DBMS_LOB regroupe toutes les fonctions

et procédures manipulant les grands objets (LOBs).

Le paquetage UTL_FILE regroupe les procédures et fonct ions permettant

de l ire et écrire des fichiers du système d'exploitation.

Un paquetage est organisé en deux parties distinctes

la spécification (introduite par ‘CREATE PACKAGE’) l iste les entêtes

de procédures et fonctions contenues dans le package,

le corps du package (introduit par ‘CREATE PACKAGE BODY’) qui

contient le code effecti f des procédures et fonctions déclarées

précédemment.

Exemple :

CREATE OR REPLACE PACKAGE Pkg_Finance IS

-- Variables globales et publ iques

GN$Salaire emp.salaire%Type ;

-- Fonctions publ iques

FUNCTION F_Test_Augmentation

(PN$Numemp IN emp.num%Type, PN$Pourcent IN NUMBER) Return

NUMBER ;

-- Procédures publ iques

PROCEDURE Test_Augmentation

(PN$Numemp IN emp.num%Type -- numéro de l 'employé

,PN$Pourcent IN OUT NUMBER -- pourcentage d'augmentation ) ;

End Pkg_Finance ;

Package créé.

CREATE OR REPLACE PACKAGE BODY Pkg_Finance IS

-- Variables globales privées

GR$Emp emp%Rowtype ;

-- Procédure privées

PROCEDURE Affiche_Salaires IS

CURSOR C_EMP IS SELECT * FROM emp ;

BEGIN

DEES INFORMATIQUE

105

V 1.0.0.a

OPEN C_EMP ;

LOOP

FETCH C_EMP INTO GR$Emp ;

EXIT WHEN C_EMP%NOTFOUND ;

dbms_output.put_l ine( 'Employé ' || GR$Emp.nom || ' --

> ' || Lpad( To_char( GR$Emp.salaire), 10 ) ) ;

END LOOP ;

CLOSE C_EMP ;

END Affiche_Salaires ;

-- Fonctions publ iques

FUNCTION F_Test_Augmentation(

PN$Numemp IN emp.num%Type

,PN$Pourcent IN NUMBER) Return NUMBER

IS

LN$Salaire emp.salaire%Type ;

BEGIN

SELECT salaire INTO LN$Salaire FROM emp WHERE num =

PN$Numemp ;-- augmentation virtuel le de l 'employé

LN$Salaire := LN$Salaire * PN$Pourcent ;

-- Affectation de la variable globale publique

GN$Salaire := LN$Salaire ;

Return( LN$Salaire ) ; -- retour de la valeur

END F_Test_Augmentation;

-- Procédures publ iques

PROCEDURE Test_Augmentation

(PN$Numemp IN emp.num%Type

,PN$Pourcent IN OUT NUMBER) IS

LN$Salaire emp.salaire%Type ;

BEGIN

SELECT salaire INTO LN$Salaire FROM emp WHERE num =

PN$Numemp ;-- augmentation virtuel le de l 'employé

PN$Pourcent := LN$Salaire * PN$Pourcent ;

-- appel procédure privée

Affiche_Salaires ;

END Test_Augmentation;

END Pkg_Finance;

Corps de package créé.

DEES INFORMATIQUE

106

V 1.0.0.a

La spécification du paquetage est créée avec une variable globale et

publique :

GN$Salaire.

une procédure publ ique : PROCEDURE Test_Augmentation

une fonction publ ique : FUNCTION F_Test_Augmentation

qui sont visibles depuis l 'extérieur (le programme appelant).

Le corps du paquetage est créé avec une procédure privée : PROCEDURE

Afiche_Salaires qui n'est visible que dans le corps du paquetage.

Le corps définit également une variable globale au corps du paquetage :

GR$Emp uti l isée par la procédure privée.

L'accès à un objet d'un paquetage est réalisé avec la syntaxe

suivante :

nom_paquetage.nom_objet[(liste paramètres)]

Appel de la fonction F_Test_Augmentation du paquetage :

Pkg_Finance.F_Test_Augmentation( 7369, 1.1 ) ;

Appel de la procédure Test_Augmentation du paquetage :

Pkg_Finance.Test_Augmentation( 7369, LN$Pourcent ) ;

Interrogation de la variable globale publique : GN$Salaire :

dbms_output.put_l ine( 'Valeur salaire du package : ' || To_char(

Pkg_Finance.GN$Salaire ) ;

3.6 Les triggers

Un déclencheur ou triggers est un bloc PL/SQL associé à une vue ou une

table, qui s'exécutera lorsqu'une instruction du langage de manipulation

de données (DML) sera exécutée.

L'avantage principal du déclencheur réside dans le fait que le code est

central isé dans la base de données, et se déclenchera quel que soit l 'outi l

uti l isé pour mettre à jour ces données, donnant ainsi l 'assurance qu'une

uti l isation d'un ordre DML depuis Sql*Plus, Forms ou n' importe quel le

appl ication tierce procurera un résultat identique sur les données.

L'inconvénient principal du déclencheur réside dans le fait que son

exécution uti l ise des ressources qui peuvent augmenter sensiblement les

DEES INFORMATIQUE

107

V 1.0.0.a

temps de traitement, notamment lors de modifications massives apportées

sur une table.

Un déclencheur s'exécute dans le cadre d'une transaction. Il ne peut donc

pas contenir d'instruction COMMIT ou ROLLBACK ou toute instruction

générant une fin de transaction impl icite (ordre DDL).

Les ordres SQL (SELECT, INSERT, UPDATE, DELETE) contenus dans le bloc

PL/SQL et qui se référent à la table sur laquel le s'exécute le déclencheur

peuvent générer l 'exception ORA-04091 TABLE IS MUTATING.

Le bloc PL/SQL qui constitue le trigger peut être exécuté avant ou après la

véri fication des contraintes d'intégrité.

Il peut être exécuté pour chaque l igne affectée par l 'ordre DML ou bien

une seule fois pour la commande.

Seules les colonnes de la l igne en cours de modification sont accessibles

par l ' intermédiaire de 2 variables de type enregistrement OLD et NEW .

OLD représente la valeur avant modification.

OLD n 'est renseignée que pour les ordres DELETE et UPDATE . El le

n'a aucune signification pour un ordre INSERT, puisqu'aucune

ancienne valeur n'existe.

NEW représente la nouvel le valeur.

NEW n 'est renseignée que pour les ordres INSERT et UPDATE . El le

n'a aucune signification pour un ordre DELETE, puisqu'aucune

nouvel le valeur n'existe.

Ces deux variables peuvent être ut i l isées dans la clause WHEN du

déclencheur et dans la section exécutable.

Dans cette section, elles doivent être préfixées comme des variables

hôtes avec l'opérateur :

Les noms de ces deux variables sont fixés par défaut, mais i l est possible

de les modifier en précisant les nouveaux noms dans la clause

REFERENCING.

REFERENCING OLD AS nouveau_nom NEW AS nouveau_nom

Dans le cas d'un déclencheur BEFORE UPDATE ou AFTER UPDATE , la

clause OF peut être ajoutée après le mot clé UPDATE pour spécifier la

l iste des colonnes modifiées.

Cela permet de restreindre l 'activation du déclencheur sur les seules

colonnes visées.

DEES INFORMATIQUE

108

V 1.0.0.a

Le mot clé WHEN(condition) permet également de restreindre le champs

d'activation du déclencheur en ajoutant une clause restrictive.

Les triggers de Tables

Exemple :

Créons un déclencheur très basique qui ne fait qu'afficher le numéro et le

nom d'un employé que l 'on veut supprimer de la table EMP

CREATE OR REPLACE TRIGGER TRG_BDR_EMP

BEFORE DELETE -- avant supression

ON emp -- sur la table EMP

FOR EACH ROW -- pour chaque l igne

DECLARE

LC$Chaine VARCHAR2(100);

BEGIN

dbms_output.put_l ine( 'Suppression de l ' 'employé n° ' || To_char(

:OLD.num )|| ' -> ' || :OLD.nom ) ;

End ;

Exemple :

La DRH annonce que désormais, tout nouvel employé devra avoir un

numéro supérieur ou égal à 10000.

Il faut donc interdire toute insertion qui ne reflète pas cette nouvel le

directive.

CREATE OR REPLACE TRIGGER TRG_BIR_EMP

BEFORE INSERT -- avant insertion

ON emp -- sur la table EMP

FOR EACH ROW -- pour chaque l igne

BEGIN

If :NEW.num < 10000 Then

RAISE_APPLICATION_ERROR ( -20010, 'Numéro employé

inférieur à 10000' ) ;

End i f ;

END ;

Exemple :

Il est possible de gérer dans le même déclencheur des ordres DML

différents en combinant les termes de la clause BEFORE avec le mot clé

OR.

CREATE OR REPLACE TRIGGER TRG_BIUDR_EMP

DEES INFORMATIQUE

109

V 1.0.0.a

BEFORE INSERT OR UPDATE OR DELETE-- avant insertion, modification ou

suppression

ON emp -- sur la table EMP

FOR EACH ROW -- pour chaque l igne

BEGIN

If INSERTING Then

dbms_output.put_l ine( 'Insertion dans la table EMP' ) ;

End i f ;

If UPDATING Then

dbms_output.put_l ine( 'Mise à jour de la table EMP' ) ;

End i f ;

If DELETING Then

dbms_output.put_l ine( 'Suppression dans la table EMP' ) ;

End i f ;

END ;

Suppression d’un trigger :

DROP TRIGGER TRG_BIR_EMP ;

Les triggers sur vues

La syntaxe d'un déclencheur sur vue est identique à cel le du déclencheur

sur table, à la différence que la clause INSTEAD OF .

Ce type de déclencheur est particul ier dans la mesure ou son exécution

remplace cel le de la commande DML à laquel le i l est associé.

Ce type de déclencheur n'est définissable que sur les vues et lui seul peut

être mis en place sur les vues.

Exemple :

Nous mettons à la disposition de certains uti l isateurs une vue permettant

de sélectionner les employés qui ont la fonction ‘administracti f ’ .

CREATE OR REPLACE VIEW VW_EMP_admin AS

SELECT num "Numéro", nom "Nom", n_dept "Dept.", salaire "Salaire"

FROM emp

WHERE fonction = 'administrati f ';

A travers cette vue, les utilisateurs peuvent insérer des lignes

Insert into VW_EMP_admin values( 9994, 'Schmoll ', 20, 2500 ) ;

DEES INFORMATIQUE

110

V 1.0.0.a

Cependant, i ls ne peuvent pas voir leurs insertions car la colonne fonction

(inuti le dans ce cas) ne fait pas partie de la vue et donc de l ' insertion !

Nous al lons donc créer un déclencheur sur vue qui va résoudre ce

problème.

CREATE OR REPLACE TRIGGER TRG_BIR_VW_EMP_admin

INSTEAD OF INSERT -- à la place de l ' insertion

ON VW_EMP_admin -- sur la vue VW_EMP_admin

FOR EACH ROW -- pour chaque l igne

BEGIN

INSET INTO emp ( num, nom, n-dept, salaire, fonction ) -- on

valorise la colonne fonction

Values (:NEW."Numéro", :NEW."Nom", :NEW."Dept.",

:NEW."Salaire", 'administrati f ' ) ;

END ;

L'uti l isateur peut désormais visual iser ses insertions.

3.7 Application des triggers : DDL, CALL

Depuis la version Oracle8i, i l est désormais possible d'uti l iser des

déclencheurs pour suivre les changements d'état du système ainsi que les

connexions/déconnexions uti l isateur et la survei l lance des ordres DDL et

DML.

Lors de l 'écriture de ces déclencheurs, i l est possible d'uti l iser des

attributs pour identi fier précisément l 'origine des évènements et adapter

les traitements en conséquence.

Les Attributs

Liste des attributs :

ora_cl ient_ip_adress

ora_database_name

ora_des_encrypted_password

ora_dict_obj_name

ora_dict_obj_name_list

ora_dict_obj_owner

ora_dict_obj_owner_l ist

ora_dict_obj_type

ora_grantee

DEES INFORMATIQUE

111

V 1.0.0.a

ora_instance_num

ora_is_alter_column

ora_is_creating_nested_table

ora_is_drop_column

ora_is_servererror

ora_login_user

ora_privi leges

ora_revokee

ora_server_error

ora_sysevent

ora_with_grant_option

Les évènements "système"

Syntaxe:

CREATE TRIGGER nom_déclencheur {BEFORE|AFTER}

évènement_système ON {DATABASE|SCHEMA}bloc PL/SQL

Evènement système :

STARTUP

SHUTDOWN

SERVERERROR

Les évènements uti l isateur

Les évènements utilisateurs

CREATE TRIGGER nom_déclencheur {BEFORE|AFTER}

évènement_utilisateur ON{DATABASE|SCHEMA}

bloc PL/SQL

Evènement utilisateur :

LOGON

LOGOFF

CREATE

ALTER

DROP

ANALYZE

ASSOCIATE STATISTICS

AUDIT

NOAUDIT

DEES INFORMATIQUE

112

V 1.0.0.a

COMMENT

DDL

DISSOCIATE STATISTICS

GRANT

RENAME

REVOKE

TRUNCATE

3.8 Maintenance des triggers

Il est possible de désactiver un déclencheur avec la commande suivante:

ALTER TRIGGER nom_déclencheur DISABLE

et de l 'activer avec la commande suivante:

ALTER TRIGGER nom_déclencheur ENABLE

De la même façon, on peut désactiver tous les déclencheurs définis sur

une table:

ALTER TABLE nom_table DISABLE ALL TRIGGERS

et de les activer avec la commande suivante:

ALTER TABLE nom_table ENABLE ALL TRIGGERS

Les informations sur les déclencheurs sont visibles à travers les

vues du dictionnaire de données :

USER_TRIGGERS pour les déclencheurs appartenant au schéma

ALL_TRIGGERS pour les déclencheurs appartenant aux schémas

accessibles

DBA_TRIGGERS pour les déclencheurs appartenant à tous les

schémas

La colonne BASE_OBJECT_TYPE permet de savoir si le déclencheur est

basé sur une table, une vue, un schéma ou la total ité de la base.

DEES INFORMATIQUE

113

V 1.0.0.a

La colonne TRIGGER_TYPE permet de savoir s'i l s'agit d'un déclencheur

BEFORE, AFTER ou INSTEAD OF si son mode est FOR EACH ROW ou non

s'i l s 'agit d'un déclencheur évènementiel ou non.

La colonne TRIGGERING_EVENT permet de connaître l 'évènement

concerné par le déclencheur.

La colonne TRIGGER_BODY contient le code du bloc PL/SQL

DEES INFORMATIQUE

114

V 1.0.0.a

DEVOIR1 A RENDRE

LIVRET5 - Système de Gestion de bases de données

relationnelles

EXERCICE1

Base de données

Considérons la base de données dont le schéma et l 'extension sont donnés

ci-dessous.

EMP (EMPNO, ENAME, JOB, MGR , HIREDATE, SAL, COMM, DEPTNO)

Clés primaires

Clés étrangères

Table EMP

EMPNO ENAM

E JOB MGR

HIREDAT

E SAL COMM

DEPTN

O

7369 SMITH CLERK 7902 17/12/80

800.00

NULL 20

7499 ALLEN SALESMA

N

7698 20/02/81

1600.0

0

300.0

0

30

7521 WARD SALESMA

N

7698 22/02/81 1250.0

0

500.0

0

30

7566 JONES MANAGER 7839 02/04/81 2975.0

0

NULL 20

7654 MARTI

N

SALESMA

N

7698 28/09/81

1250.0

0

1400.

00

30

7698 BLAKE MANAGER 7839 01/05/81 2850.0

0

NULL 30

7782 CLARK MANAGER 7839 09/06/81 2450.0

0

NULL 10

7788 SCOTT ANALYST 7566 09/11/81 3000.0

0

NULL 20

DEES INFORMATIQUE

115

V 1.0.0.a

7839 KING PRESIDEN

T

NULL

17/11/81

5000.0

0

NULL 10

7844 TURNE

R

SALESMA

N

7698 08/09/81

1500.0

0

0.00 30

7876 ADAMS CLERK 7788 23/09/81 1100.0

0

NULL 20

7900 JAMES CLERK 7698 03/12/81

950.00

NULL 30

7902 FORD ANALYST 7566 03/12/81

3000.0

0

NULL 20

7934 MILLER CLERK 7782 23/01/82 1300.0

0

NULL 10

DEPT (DEPTNO, DNAME, LOC)

Table DEPT

DEPTNO DNAME LOC

10 ACCOUNTING NEW-YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

Création des bases de données, contraintes d’intégrité

Travail demandé

1) Créer la table DEPT. Ne pas oubl ier de définir ne numéro de

département DEPTNO comme clé primaire. Intégrer également la

contrainte de domaine suivante : le nom d'un département (DNAME) ne

peut être que ACCOUNTING, RESEARCH, SALES ou OPERATIONS.

2) Remplir la table DEPT.

3) Recopier la table EMP en passant la commande suivante :

CREATE TABLE EMP AS SELECT * FROM SCOTT.EMP;

(Création de la table EMP par copie de la table EMP de l ’uti l isateur

SCOTT).

DEES INFORMATIQUE

116

V 1.0.0.a

4) Insérer le tuple (7369, ‘Bidon’, NULL, NULL, NULL, NULL, NULL, NULL)

dans la table EMP. Celà fonctionne. Est-ce normal ?

5) Annuler l ’ insertion précédente.

6) Ajouter les contraintes d’intégrité nécessaires à la table EMP (clé

primaire et clés étrangères) à l ’aide de la commande ALTER TABLE EMP

ADD CONSTRAINT

7) Dans EMP, ajouter les nouveaux employés :

(7369, 'WILSON', 'MANAGER', 7839, '17/11/91', 3500.00, 600.00, 10) ;

(7657, 'WILSON', 'MANAGER', 7839, '17/11/91', 3500.00, 600.00, 50) ;

(7657, 'WILSON', 'MANAGER', 7000, '17/11/91', 3500.00, 600.00, 10) ;

(7657, 'WILSON', 'MANAGER', 7839, '17/11/91', 3500.00, 600.00, 10).

Remarques ?

8) Val ider l ’ insertion précédente.

EXERCICE 2

Soit une table quelconque TABL, dont la clé primaire CLENUM est

numérique. Définir un trigger en insertion permettant d’implémenter une

numérotation automatique de la clé. Le premier numéro doi t être 1.

DEES INFORMATIQUE

117

V 1.0.0.a

DEVOIR2 A RENDRE

LIVRET5 – Système de Gestion de bases de données

relationnelles

EXERCICE 1

Soit le schéma relat ionnel d’une agence bancaire régionale.

CLIENT (NUMCL, NOM, PRENOM, ADR, CP, VILLE, SALAIRE, CONJOINT)

DETENTEUR (NUMCL, NUMCP)

COMPTE (NUMCP, DATEOUVR, SOLDE)

Attributs soul ignés : Clés primaires.

Attributs en ital iques : Clés étrangères.

NUMCL et CONJOINT sont définis sur le même domaine.

Écrire un trigger en insertion permettant de contrôler les contraintes

suivantes :

−le département dans lequel habite le cl ient doit être 01, 07, 26, 38, 42,

69, 73, ou 74 ;

−le nom du conjoint doit être le même que celui du cl ient.

EXERCICE 2

Soit une table quelconque TABL, dont la clé primaire CLENUM est

numérique. Définir un trigger en insertion permettant d’implémenter une

numérotation automatique de la clé. Le premier numéro doi t être 1.

EXERCICE 3

Soit le schéma relationnel d’une agence bancaire régionale.

CLIENT (NUMCL, NOM, PRENOM, ADR, CP, VILLE, SALAIRE, CONJOINT)

DETENTEUR (NUMCL, NUMCP)

COMPTE (NUMCP, DATEOUVR, SOLDE)

Attributs soulignés : Clés primaires.

Attributs en italiques : Clés étrangères.

NUMCL et CONJOINT sont définis sur le même domaine.

Écrire un trigger en insertion permettant de contrôler les contraintes suivantes :

−le département dans lequel habite le client doit être 01, 07, 26, 38, 42, 69, 73, ou 74 ;

−le nom du conjoint doit être le même que celui du client.