BASES DE D ONNÉES

AVANCÉES
SQL Avancé
CONFIDENTIALITÉ ET DROIT D’ACCÈS

1. PRINCIPES
2. ACCORDER UN PRIVILÈGE
3. RETIRER UN PRIVILÈGE
4. LES RÔLES
1. PRINCIPE

SQL permet de préciser les autorisations d'accès aux données. Une

autorisation s'appelle un privilège. Un privilège précise un objet et les
opérations qu'il est possible d'exécuter sur cet objet.

Principes :
• l'utilisateur créateur d'un objet (table, vue, index, procédure, etc.) en
devient le propriétaire; il peut effectuer sur cet objet toutes les opérations
techniquement permises;
• le propriétaire d'un objet peut accorder à un autre utilisateur un privilège
sur cet objet pour certaines opérations;
• le propriétaire d'un objet peut autoriser l'utilisateur auquel il a accordé un
privilège à transmettre tout ou partie de celui-ci à un troisième utilisateur;
• le propriétaire d'un objet peut retirer un privilège accordé.

SQL implémente le modèle de contrôle d'accès discrétionnaire. 69

1. PRINCIPE
Commande SQL pour donner des droits :
GRANT privilèges ON table (ou vue) TO user [WITH GRANT OPTION] ;

▪ Privilèges : ALL ou {select, insert, update, delete, alter, index}

▪ Table : train ou Fé[Link] (pour la table d’un autre utilisateur Félix)
▪ Utilisateur : Thérèse, ou PUBLIC (pour tous les utilisateurs)
▪ with grant option : possibilité de transmettre ces droits

l’utilisateur U1 moi ...

autorise l’utilisateur U2
à effectuer l’opération X
sur l’objet Y
X

grant update(QSTOCK,PRIX)
Y
on PRODUIT
to P_MERCIER; 70

U2
2. ACCORDER UN PRIVILÈGE

grant select, update(QSTOCK,PRIX)

on PRODUIT
to P_MERCIER, S_FINANCIERS;

grant select(NCLI,NOM,LOCALITE)
on CLIENT
to public;

grant all privileges

on CLIENT
to P_MERCIER, S_FINANCIERS
with grant option;

6
2. ACCORDER UN PRIVILÈGE

grant run
on SUP_DETAIL
to public;

grant run
on COMPTA01
to S_FINANCIERS
with grant option;

7
3. RETIRER UN PRIVILÈGE

 Commande SQL pour supprimer des droits :

REVOKE privilèges ON table (ou vue) FROM user ;

 Sil’option « with grant option » a été utilisée alors

les droits sont révoquées en cascade

8
3. RETIRER UN PRIVILÈGE

revoke update(PRIX)
on PRODUIT
from P_MERCIER;

revoke run
on COMPTA01
from P_MERCIER;

revoke grant option for update (COMPTE)

on CLIENT
from P_MERCIER;
(si P_MERCIER avait déjà transmis ce privilège ? Cf. clés étrangères :
mode cascade ou mode restrict)

9
4. LES RÔLES

Les rôles facilitent la gestion des grands systèmes.

opér. X

privilège rôle R U2

obj. Y

create role CONSULTANT;

grant select on CLIENT to CONSULTANT;

grant update (ADRESSE, LOCALITE) on CLIENT to CONSULTANT;

grant CONSULTANT to P_MERCIER;

revoke select (LOCALITE) from CONSULTANT;

revoke CONSULTANT from P_MERCIER; 75
drop role CONSULTANT;
LES VUES

11
VISION GLOBALE D’UNE BD

12
NIVEAU EXTERNE
 Appelé également niveau des vues
 Un groupe d’utilisateurs d’une base de données conceptuelle

peut avoir besoin :

d’une partie seulement des informations de la base de
données,
et/ou de ces informations structurées différemment
 i.e. d’une BD conceptuelle différente de la base de départ,
mais dépendant de celle-ci :
1. Tout attribut figurant dans la nouvelle base est aussi
dans la base de départ ou dépend des attributs de celle-ci
2. Les instances de chaque table de la nouvelle base sont
calculées à partir de celle de la base de départ
 Toute base de données conceptuelle satisfaisant ces de7u8x
conditions est une vue
DEFINITION
Une vue est une présentation particulière (d'une partie) des données de
la base de données.
Une vue se présente comme une table virtuelle sans données stockées :
ses données n'existent que lorsqu'on demande à les consulter . . .

En SQL :

create view COM_COMPLETE (NCOM,NCLI,NOMCLI,LOC,DATECOM)

as select NCOM, [Link], NOM, LOCALITE, DATECOM
from CLIENT CLI, COMMANDE COM
where [Link] = [Link];

Quel espace une vue occupe-t-elle sur disque ?

→ Rien ou presque : seule la définition ci-dessus est stockée.
79
DEFINITION

Une vue se manipule comme une table normale, du moins en consultation :

select NOMCLI,NCOM,DATECOM
from COM_COMPLETE
where LOC = 'Toulouse';

select NPRO
from DETAIL
where NCOM in ( select NCOM
from COM_COMPLETE
where LOC = 'Toulouse');

select LOC, count(*)

from COM_COMPLETE CC, DETAIL D
where [Link] = [Link]
group by LOC;
DEFINITION

Comment le SGBD exécute-t-il une requête SFW comportant une vue ?

En théorie (pour comprendre) :

le contenu de la vue est évalué et stocké temporairement sur le disque;
puis la requête est exécutée sur cette table temporaire.

En pratique cette procédure est souvent trop coûteuse :

le SGBD reformule la requête SFW en y remplaçant le nom de la vue par
sa définition(1) puis l'exécute.

select NOMCLI,NCOM,DATECOM from COM_COMPLETE where LOC

= 'Toulouse';

select NOM,NCOM,DATECOM from CLIENT CLI, COMMANDE COM
where [Link] = [Link] and LOCALITE = 'Toulouse';

(1) En réalité le processus peut être un peu plus complexe.

DEFINITION

On peut définir une vue sur des tables de base ou sur d'autres vues.

On peut supprimer une vue :

drop view COM_COMPLETE

EXEMPLE
S = {E(emp,dep) ; M(dep,mgr) ; S(emp,sal) }
Vue V :
 tables = {EM(emp,mgr) ; BS(emp,sal) }
Calcul des instances :

 Chaque table d’une vue est entièrement déterminée par la donnée

d’une expression nommée :
T = e, où T est une table et e une expression relationnelle sur la
base de départ
Les instances de T sont calculées par e
 Exemple : définition de la vue :

83
DEFINITION
 Une vue est une base de données virtuelle dont le schéma et
le contenu sont dérivés de la base réelle par un ensemble de
requêtes
tout ensemble d’expressions nommées de la forme T = e sur le
schéma S de BD est une vue sur S.
 Vue :BD conceptuelle plus abstraite
les données de la vue sont construites à partir de celles de la BD
conceptuelle mais peuvent ne pas être présentes dans celle-ci
les données de la vue n’ont pas d’existence indépendante de celles
de la BD conceptuelle
 Une vue est donc un ensemble de relations déduites d'une
base de données, par composition des relations de la base
 Par abus de langage, une vue relationnelle est une table
virtuelle 84
GESTION DES VUES
 Les vues sont manipulées, interrogées et mises à jour
comme n’importe quelle BD conceptuelle (tables), mais
cela dépend de l’implémentation choisie :
Vues virtuelles

Vues matérialisées

85
VUES VIRTUELLES
 Vue virtuelle
les relations de la vue ne sont pas stockées
seule sa définition est stockée
le SGBD doit traduire les requêtes et les mises à jour sur la vue
en requêtes et mises à jour sur la BD conceptuelle
 Traduction des requêtes
Chaqu e table de la vue d ans la requête est remplacée par
l’expression relationnelle qui la traduit
Exemple :

 Traduction des mises à jour

Problèmes dès que la vue est définie à partir de plusieurs
86
tables
VUES MATÉRIALISÉES

 Stockées physiquement
Exemple : entrepôts de données

 Les requêtes sont évaluées sur la vue

 Nécessité de propager les mises à jour effectuées sur la

base au niveau des vues pour le maintien de la cohérence

87
LES VUES VIRTUELLES
L’UTILISATION DES VUES

 Au niveau de l’utilisateur (indépendance logique) :

Indépendance logique
 protection des programmes d’application contre les modifications d u
schéma
 Exemple : le remplacement de E et M par la table EDM(emp,dep,mgr)

n’implique pas la réécriture des programmes définis sur E et M

 Au niveau du système (confidentialité) :

Protection des données (exemple : BS)

89
LES VUES : SYNTAXE SQL
Création d'une vue
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW <Nom_Vue>
[(attribut [, attribut ] )]
AS <requête>
[ WITH CHECK OPTION ]
[ WITH READ ONLY ] ;

 OR REPLACE remplace la vue par la nouvelle définition même si elle existait

déjà (évite de détruire la vue avant de la recréer).
 FORCE pour créer la vue sans vérifier si les tables, vues ou clichés qui
l’alimentent existent, ou si les privilèges adéquats (SELECT, INSERT, UPDATE,
ou DELETE) sur ces objets sont acquis par l’utilisateur qui crée la vue.
 NOFORCE (par défaut) pour créer la vue en vérifiant au préalable si les tables,
vues ou clichés qui l’alimentent existent et que les privilèges sur ces objets sont
acquis.
 La clause WITH CHECK OPTION permet de spécifier que les tuples de la vue
insérés ou mis à jour doivent satisfaire aux conditions de la requête.
 WITH READ ONLY déclare la vue non modifiable par INSERT, UPDATE, 9o0u
DELETE.
Suppression d'une vue : DROP VIEW <nom de vue> ;
LES VUES - USAGE

A quoi servent les vues ?

1. Interface pour des besoins particuliers

2. Mécanisme de contrôle d'accès
3. Mécanisme d'évolution de la base de données
4. Formulation de requêtes complexes
5. etc
LES VUES - USAGE

1. Interface pour des besoins particuliers

But : présentation des données adaptées aux besoins spécifiques d'un

utilisateur.

create view HABITUDE_ACHAT(LOCALITE,NPRO,VOLUME)

as select LOCALITE,[Link],sum(QCOM*PRIX)
from CLIENT CLI,COMMANDE COM,DETAIL D,PRODUITP
where [Link] =[Link]
and [Link] =[Link]
and [Link] = [Link]
group by LOCALITE, [Link];
LES VUES - USAGE

2. Mécanisme de contrôle d'accès

But : limiter la visibilité des données pour un utilisateur.

create view ANALYSE(LOCALITE, CAT, DATE, NPRO, QCOM) as

select LOCALITE, CAT, DATECOM, NPRO, QCOM
from CLIENT C, COMMANDE M, DETAIL D
where [Link] = [Link] and [Link] = [Link];

revoke select on CLIENT from HOLLANDE;

revoke select on COMMANDE from HOLLANDE;
revoke select on DETAIL from HOLLANDE;
grant select on ANALYSE to HOLLANDE;
LES VUES - USAGE

3. Mécanisme d'évolution de la base de données

But : protéger un utilisateur (ou les programmes d'application) des effets des
modifications de structure de la base de données.

Exemple. Si on remplace la table :

CLIENT(NCLI,NOM,ADRESSE,LOCALITE,CAT,COMPTE);
par les deux tables complémentaires :

CLIENT_SIG(NCLI,NOM,ADRESSE,LOCALITE);
CLIENT_COM(NCLI,CAT,COMPTE);
on peut conserver les anciennes requêtes grâce à la vue :

create view CLIENT(NCLI,NOM,ADRESSE,LOCALITE,CAT,COMPTE)

as
select [Link],NOM,ADRESSE,LOCALITE,CAT,COMPTE
from CLIENT_SIG CS, CLIENT_COM CC
where [Link] =[Link];
LES VUES - USAGE

4. Formulation de requêtes complexes

But : formuler certaines requêtes qui ne peuvent s'exprimer en une seule fois. Par
exemple, une fonction agrégative sur le résultat d'une fonction agrégative n'est pas
autorisée :

create view VAL_STOCK_ACTU(STOCK,VALEUR) as

select [Link], (QSTOCK - sum([Link]))*PRIX
from DETAIL D, PRODUIT P
where [Link] = [Link]
group by [Link], QSTOCK, PRIX;

select sum(VALEUR) from VAL_STOCK_ACTU;

Remarque : les limites de formulation existent surtout en SQL2. SQL3 offre plus de
souplesse et de régularité.
MODIFIER LES DONNÉES D'UNE VUE (1)

Peut-on demander la modification (insert, delete, update) des données d'une

vue ?

En théorie :
oui pour autant que le SGBD puisse répercuter sans ambiguïté la demande sur
les données réelles dans des tables de base, mais les conditions de
modifiabilité sont théoriquement complexes;
MODIFIER LES DONNÉES D'UNE VUE (2)

En pratique le SGBD impose des limitations très fortes sur les vues
modifiables (updatable views). Par exemple :

• la vue ne contient pas les clauses distinct, group by, union, except, intersect;
• la vue ne contient pas de fonctions agrégatives;
• la vue ne contient pas de sous-requête qui cite la table de la vue;
• la vue contient la clé primaire de la table de base;
• la vue ne contient pas de jointure;
• si la vue est définie sur une autre vue, celle-ci doit être elle-même
modifiable.
MODIFIER LES DONNÉES D'UNE VUE (3)

Il est possible d'effectuer dans une vue une modification qui reste invisible !

create view
CLIENT_TOULOUSE(NCLI,NOM,ADRESSE,LOCALITE,CAT,COMPTE)
as
select NCLI,NOM,ADRESSE,LOCALITE,CAT,COMPTE
from CLIENT where LOCALITE ='Toulouse';

La modification suivante est légale :

insert into
CLIENT_TOULOUSE(NCLI,NOM,ADRESSE,LOCALITE,CAT,COMPTE)
values ('Z180','CHARLIER','rue des Gaulois','Poitiers',null,0);

. . . bien que la nouvelle ligne soit invisible dans la vue :

select *from CLIENT_TOULOUSE;

La vue a un comportement irrégulier : on n'y retrouve pas ce qu'on y a inséré !

MODIFIER LES DONNÉES D'UNE VUE (4)
Pour régulariser le comportement de la base de données :

create view
CLIENT_TOULOUSE(NCLI,NOM,ADRESSE,LOCALITE,CAT,COMPTE)
as
select NCLI,NOM,ADRESSE,LOCALITE,CAT,COMPTE
from CLIENT where LOCALITE ='Toulouse'
with check option;

La modification suivante est rejetée :

insert into
CLIENT_TOULOUSE(NCLI,NOM,ADRESSE,LOCALITE,CAT,COMPTE)
values ('Z180','CHARLIER','rue des Gaulois','Poitiers',null,0);

Le comportement de la vue devient régulier : on y retrouve ce qu'on y a stocké, ni

plus, ni moins !
VUES MATÉRIALISÉES
VUES MULTI-DIMENSIONNELLES
 Besoin des entreprises
accéder à toutes les données de l’entreprise
regrouper les informations disséminées dans les bases
analyser et prendre des décisions rapidement (OLAP :
Online Analytical Processing )
 Exemples d'applications concernées
bancaire : regrouper les infos d’un client réponse à ses
demandes
mailing ciblés pour le marketing
grande distribution : regrouper les infos ventes produits à
succès
modes, habitudes d’achat 101

préférences par secteurs géographiques

DATAWAREHOUSE

 Ensemble de données archivées variant dans le temps,

organisées par sujets, consolidées dans une base de
données unique, gérée dans un environnement de
stockage p articulier, aidant à la prise de décision dans
l’entreprise.
 Trois fonctions essentielles :

collecte de données de bases existantes et chargement

gestion des données dans l’entrepôt
analyse de données pour la prise de décision
 Vue concrète :

Idéale pour modéliser un sous-ensemble de données

102
VUE MATÉRIALISÉE

 Appelée aussi vue concrète

 C’est une table calculée à partir des tables de base
par une question et matérialisée sur disque par le
SGBD
 Une vue concrète est calculée dès sa définition et
mise à jour chaque fois qu’une table de base est
mise à jour
 La vue est souvent utilisée

 Les tables de base sont peu mises à jour

 Définition de stratégie de report efficaces des mises

à jour effectuées sur les tables de base 103
EXEMPLES

 Vue des ventes totalisées par produit, fournisseurs,

dates
CREATE CONCRETE VIEW VENTESPFD (numpro,num fou, d ate,
compte, quantot) AS
SELECT numpro, numfou, date, COUNT(*) AS compte, SUM(quantité)
AS quantot
FROM ventes
GROUP BY Numpro, Numfou, Date;
 Vue plus compacte (sans fournisseurs) :
CREATE CONCRETE VIEW ventespd (numpro, date,compte, quantot)
AS
SELECT numpro, date,COUNT(*)AS compte,SUM(quantité) AS quantot
FROM ventes
104
GROUP BY numpro, date;
LES REQUÊTES RÉCURSIVES

1. INTRODUCTION

2. FORME GÉNÉRALE

3. FORME COMPARABLE À UNE VUE

4. EXEMPLE

40
INTRODUCTION

SQL2 ne permet pas la formulation de requêtes récursives.

Par exemple : lister tous les subordonnés directs et indirects de la personne p4.

Cette possibilité existe en SQL3 41

FORME GÉNÉRALE D ' UNE REQUÊTE RÉCURSIVE

forme du résultat (signature)

calcul du résultat
with T
as (E) extraction du résultat
R;

with T
as ( initialisation

E0
union all
Ei
)
R; récurrence (cite T)

42
FORME COMPARABLE À UNE VUE :

forme du résultat (signature)

calcul du résultat
with T
as (E) extraction du résultat
R;

Vue : forme du résultat (signature)

calcul du résultat
create view T
as E; extraction du résultat
R;

Remarque : la forme with T est plus générale et n'est pas limitée à

l'expression des requêtes récursives.
43
EXEMPLE :
Lister tous les subordonnés directs et indirects de la personne p4

with ORGAN (NIVEAU, NPERS, NOM, RESP)

as (
-- Initialisation (E0)
select 1, NPERS, NOM, RESPONSABLE
from PERSONNE
where NPERS = 'p4‘
union all
-- Incrémentation (Ei)
select [Link] + 1, [Link], [Link], [Link]
from ORGAN O, PERSONNE P
where [Link] = [Link]
)
-- Elaboration du résultat
select NIVEAU, NPERS, NOM, RESP from ORGAN;

LEVEL NPERS NOM RESPONSABLE

1 p4 Dupont p1
2 p5 Verger p4
2 p6 Dupont p4
3 p7 Dermiez p6
 LES EXTENSIONS OBJET DE SQL3

1. TYPES DE DONNÉES COMPLEXES

2. TYPE DÉFINIS PAR L’UTILISATEUR (TDU)
3. TABLES TYPÉES
4. HIÉRARCHIES DE TYPES ET DE TABLES
SQL3 offre de nouvelles structures de données propres au modèle de
données orienté objet. Deux défis :
• cohabitation de deux approches a priori incompatibles
• préserver l'interprétation SQL2

1. les types de données complexes : row et array

2. les types définis par l'utilisateur
3. les tables typées
4. les hiérarchies de types et de tables
1. TYPES DE DONNÉES COMPLEXES (ROW)
Le constructeur row définit un type de données composé de plusieurs
types de données élémentaires ou composées.

create table CLIENT(

NCLI char(10) not null primary key,
NOM char(32) not null,
ADRESSE row(RUE char(30), LOCALITE char(60)),
CAT char(2));

values('B332','MONTI',row('r. Neuve', 'Genève'),'B2')

select NCLI, NOM, [Link]

from CLIENT
where [Link] = 'Poitiers';
1. TYPES DE DONNÉES COMPLEXES (ARRAY)

Le constructeur array définit un type de données constitué d’un tableau

d’éléments dont on spécifie le nombre maximum. La représentation des
prénoms des clients (on admet qu’un client possède de 0 à 4 prénoms) et
de leurs multiples adresses.

create table CLIENT2(

NCLI char(10) not null primary key,
NOM char(32) not null,
PRENOM char(15) array(4),
ADRESSES row(RUE char(30), LOCALITE char(60)) array(2),
CAT char(2));

select NCLI, NOM, PRENOM[1], PRENOM[2], ADRESSES[1].RUE

from CLIENT2
where ADRESSES[1].LOCALITE = 'Poitiers';
2. TYPES DÉFINI PAR L’UTILISATEUR (TDU)

extension de
"create domain"
create type Chaine as varchar(60) default '?';

create type Contact as (RUE Chaine, LOCALITE Chaine);

create table CLIENT(

NCLI char(10) not null primary key,
NOM Chaine not null,
ADRESSE Contact,
CAT char(2));
3. TABLE TYPÉE

create type TPERSONNE as (

NCLI char(10),
NOM Chaine,
ADRESSE Contact,
LOCALITE char(2));

create table CLIENT of TPERSONNE;

create table PROSPECT of TPERSONNE;

Remarque : les tables typées et les tables relationnelles (= classiques)

peuvent coexister dans une BD
4. HIÉRARCHIE DE TYPES

nature de l'Object Id
create type TPERSONNE as (
TPERSONNE NPERS char(10),
NPERS
NOM NOM Chaine,
ADRESSE
LOCALITE ADRESSE Contact,
LOCALITE char(2))
REF is system generated;
TCLIENT
TPROSPECT
CAT
COMPTE
REPRESENTANT create type TCLIENT under TPERSONNE
as (
CAT char(2),
TCLIENT_PRIVE TCLIENT_INDUSTRIEL COMPTE decimal (9,2)); sous-type
REDUCTION NUM_TVA

Remarque : les contraintes ne sont pas définies dans les types mais
dans les tables.
4. HIÉRARCHIE DE TABLES TYPÉES

Object Id

PERSONNE
create table PERSONNE of TPERSONNE
IdP (REF is IdP system generated,
NPERS
NOM
CLIENT
NPERS with options not null,
ADRESSE
LOCALITE IdP NOM with options not null,
oid: IdP NPERS
id: NCLI NOM ADRESSE with options not null, sous-table
ADRESSE
LOCALITE LOCALITE with options not null,
CAT[0-1]
COMPTE primary key (NPERS));
CLIENT oid: IdP
CAT[0-1] id: NPERS
COMPTE
create table CLIENT of TCLIENT under PERSONNE
(COMPTE with options not null
contenu apparent de CLIENT default 0
check(COMPTE >= 0));

Permet d’ajouter des propriétés à

une colonne telle qu’une contrainte
ou une valeur par défaut
4. HIÉRARCHIE DE TABLES TYPÉES

PERSONNE
IdP
select NPERS, NOM, ADRESSE
les clients
NCLI
NOM
from CLIENT
ADRESSE
LOCALITE
where LOCALITE = 'Poitiers';
oid: IdP
id: NCLI
select NPERS, NOM, ADRESSE
les personnes
from PERSONNE y compris les clients
CLIENT where LOCALITE = 'Poitiers';
CAT[0-1]
COMPTE

select NPERS, NOM, ADRESSE les personnes

from only(PERSONNE) sauf les clients
where LOCALITE = 'Poitiers';
4. HIÉRARCHIE DE TABLES TYPÉES

Références entre tables

Par clés étrangères classiques ou par références (via object Id)

create table COMMANDE (

NCOM char(10) not null primary key,
DATECOM date not null,
REFCLI REF(TCLIENT) scope CLIENT not null);

La colonne REFCLI de chaque ligne de COMMANDE est une référence vers une
ligne de type TCLIENT et plus particulièrement une ligne de la table CLIENT.

select NCOM, DATECOM, REFCLI->NPERS, REFCLI->NOM

from COMMANDE
where REFCLI->LOCALITE = 'Poitiers';

REFCLI-> représente la ligne référencée par REFCLI tandis que REFCLI->NCLI

désigne la valeur de NCLI de la ligne référencée par REFCLI.
COMPLÉMENTS
• distinct type : TDU simple fortement typé limitant strictement les possibilités de
comparaison et opérations; objectif de sécurité de manipulation; exemple :
MONTANT_DOLLARS et MONTANT_EUROS;

• type instantiable / not instantiable (= concrete/abstract en Java) : peut-on ou non

créer des instances.

• type final / not final : accepte ou non des sous types.

• on peut associer des méthodes aux TDU (comme à une classe)

• notion d'encapsulation, de polymorphisme et de surcharge sont d’application
parmi les TDU d’une même branche d’une hiérarchie.
CREATE TYPE dvd UNDER film AS (
editeur CHARACTER VARYING(35),
bonus ROW(
nom CHARACTER VARYING(35),
durée CHARACTER VARYING(35)) ARRAY[10])) 135
INSTANTIABLE
NOT FINAL );
LES PRÉDICATS
DÉFINITION

Un prédicat check est une expression logique (condition) associée à

un schéma, à une table ou à une colonne.

Un prédicat se présente sous la forme check(condition).

La condition d'un prédicat doit être satisfaite à tout moment,

notamment après toute modification. Si tel n'est pas le cas, la
modification est annulée.
PRÉDICAT DE TABLE (1)

create table CLIENT ( NCLI ...,

...,
CAT char(2),
primary key (NCLI),
check (CAT is null or
CAT in ('B1','B2','C1','C2'));

alter table CLIENT

add check (CAT is null or CAT in ('B1','B2','C1','C2'));
alter table CLIENT
contrainte nommée
add constraint CHK_CAT
check (CAT is null or CAT in ('B1','B2','C1','C2'));

create table CLIENT ( ..., contrainte nommée

CAT char(2),
primary key (NCLI),
constraint CHK_CAT check (CAT is null or
CAT in ('B1','B2','C1','C2'));
PRÉDICAT DE TABLE (2)

alter table COMMANDE

add check ((DATECOM >= (select max(DATECOM)
from COMMANDE)
and DATECOM <= CURRENT_DATE) is not false);

alter table COMMANDE

add check (NCLI in (select NCLI from CLIENT));

alter table CLIENT

drop constraint CHK_CAT;
PRÉDICAT DE COLONNE/DOMAINE

CAT char(2) check(CAT is null or

CAT in ('B1','B2','C1','C2'))

CAT char(2) constraint CHK_CAT check(CAT is null or contrainte nommée

CAT in ('B1','B2','C1','C2'))

create domain MONTANT integer check(value >= 0);

Remarque. De nombreux SGBD limitent les conditions à l'état de la ligne

courante. Pas question de référencer d'autres lignes, de la même table ou
d'une autre table.
LES SOUS-PROGRAMMES :
1. LES PROCÉDURES
2. LES FONCTIONS
AVANTAGES:
 La simplification et réutilisabilité, le découpage d’un
problèmes permet d’obtenir des blocs qui sont:
Lisibles et facile à comprendre
Faciles à maintenir: détection rapide de l’erreur et
correction sans difficultés

Faciles à faire évoluer : ajout facile d’autres fonctionnalités

Réutilisables

La performance (créés et compilés une seule fois)

La sécurité : possibilité s’attribuer les droits accès à une
procédure ou une fonction stockée sans lui donner les droits142
directement sur les tables qu’elle utilise
DÉFINITION
Une procédure SQL (Stored procedure) est une procédure stockée dans la
base de données. Elle peut être invoquée par un programme extérieur, par
une procédure SQL ou par un déclencheur.
Quel est son intérêt ?
• Elle est unique pour tous les utilisateurs (simplicité de gestion et
d'évolution).
• Elle a été construite par un spécialiste et est doncfiable.
• Elle peut encapsuler des fonctions complexes d'accès ou de modification
de données avec validation.
• Elle peut cacher l'usage du langage SQL (wrapping).
• Outre la fonction de base qu'elle réalise, elle peut exécuter de manière
transparente de nombreuses fonctions annexes : statistiques,
journalisation, contrôle d'accès, gestion de l'intégrité des données, etc.

Malheureusement, pas de standardisation procédures du langage des

SQL (langages propriétaires, COBOL, etc.) Java, C#, VB,
SYNTAXE

CREATE [OR REPLACE] PROCEDURE nom procédure

[(liste paramètres formels)]
AS | IS
[partie déclaration]
BEGIN ...
[EXCEPTION ...]
END [nom procédure];

144
EXEMPLE

Exemple : suppression intelligente d'une ligne de DETAIL

create procedure SUP_DETAIL (in COM char(12),in PRO char(15))

begin
delete from DETAIL
where NCOM = :COM and NPRO = :PRO;
if (select count(*) from DETAIL
where NCOM=:COM) = 0
then delete from COMMANDE
where NCOM = :COM
end if; on supprime la ligne de COMMANDE
si on vient de supprimer sa dernière ligne de DETAIL
end;

call SUP_DETAIL('30182','PA60');
LES FONCTIONS SQL

146
DÉFINITION & SYNTAXE

 La création de fonction est très similaire à la création de

procédure, sauf que la syntaxe diffère.

CREATE [OR REPLACE] FUNCTION nom fonction

[(liste d’arguments)] RETURN type
{AS | IS}
BEGIN
--statements
RETURN value;
[EXCEPTION]
END;

 Le plus de la fonction est l’utilisation de la clause

RETURN qui indique le type d u résultat retourné par la
147
fonction.
MANIPULATION PROCÉDURE/FONCTION

 Lorsque les tables qu’une procédure (ou une fonction)

subissent des modifications structurelles, il faut alors la
recompiler.

ALTER {FUNCTION|PROCEDURE} nom COMPILE;

 Suppression d’une procédure ou une fonction.

DROP {FUNCTION|PROCEDURE} nom;

68
LE CATALOGUE SQL

69
DÉFINITION
 Appelés également dictionnaire de données
 Un catalogue SQL contient la description des données de la base :
Pour chaque relation :
 nom de la relation, identificateur d u fichier et structure d u

fichier
 nom et domaine de chaque attribut

 nom des index

 contraintes d’intégrité

Pour chaque index :

 nom et structure de l’index

 attribut appartenant à la clé de recherche

Pour chaque vue :

 nom de la vue 150

 définition de la vue
EXEMPLES …
Exemple 1:
Les catalogues gérés par SQL Server :
▪ SYSTABLES, SYSCOLUMNS, SYSINDEXES

Le catalogue d'une base de données est un ensemble de tables décrivant le

schéma de cette base de données.

Il comprend notamment :

• une table répertoriant les tables de la base de données

• une table décrivant les colonnes de ces tables

• une table décrivant les clés (PK et FK) et une autre décrivant leurs composants

• une table décrivant les vues

• une table décrivant les utilisateurs et une autre décrivant les privilèges
151
• et plusieurs dizaines d'autres tables ...
EXEMPLES …

La table des tables (SYS_TABLE) et la table des colonnes (SYS_COLUMN)

description de la R = réelle/de base

table SYS_TABLE ! V = virtuelle/vue
null admis ?
Y/N

72
EXEMPLES …

La table des clés (SYS_KEY) et la table des composants (SYS_KEY_COMP)

P = primary PK référencée n° d'ordre de la colonne

F = foreign par la FK dans la clé

73
EXEMPLES …

Observations :
• ces tables contiennent leur propre description
• ces tables peuvent être consultées comme toute autre table
• mais ... il n'est pas possible de les modifier directement via les requêtes
de modification
• aucune contrainte n'est déclarée
• le principal utilisateur d u catalogue est le SGBD lui-même

Autres appellations :
• dictionnaire de données
• métadonnées (et leur métaschéma)

74
EXEMPLES …

 Requêtes aux catalogues

SELECT TBNAME FROM SYS_COLUMNS
WHERE CNAME = ‘S#’
SELECT COUNT(*) FROM SYSTABLES
WHERE CREATOR = ‘Dupont’

 Mise à jour des catalogues

par le système seulement

75
EXEMPLES …
Utilisation (1)
Quelles sont les colonnes de la table DETAIL ?

select CNAME, CTYPE, LEN1, NULLS

from SYS_COLUMN
where TNAME = 'DETAIL';

Dans quelles tables de base existe-t-il des colonnes dont le nom commence par
'NCOM' ?

select TNAME
from SYS_TABLE
where TNAME in (select TNAME
from SYS_COLUMN
where CNAME like 'NCOM%')
and TTYPE = 'R';
76
EXEMPLES …

Utilisation (2)
Quelles sont les propriétaires des tables référencées par la table DETAIL ?

select distinct CREATOR

from SYS_TABLE
where TNAME in
(select TNAME
from SYS_KEY
where KEYID in
(select KTARG
from SYS_KEY
where TNAME ='DETAIL'));

77
EXEMPLES …
Exemple 2: Le dictionnaire de données sous Oracle
▪ Le dictionnaire des données contient :

▪ la définition des tables, vues, index, clusters, synonymes,

séquences, procédures, fonctions, paquetages, déclencheurs,
etc. ;
▪ la description de l’espace disque alloué et occupé pour
chaque objet ;
▪ les valeurs par défaut des colonnes (DEFAULT) ;
▪ la description des contraintes de vérification et d’intégrité
référentielle ;
▪ le nom des utilisateurs de la base ;
▪ les privilèges et rôles pour chaque utilisateur ;
▪ des informations d’audit (accès aux objets) et d’autre natur e
1 58
(commentaires par exemple).
EXEMPLES …

 Toutes les informations contenues dans les tables système du

dictionnaire des données et accessibles au travers de vues sont
codées en MAJUSCULES.
 Le dictionnaire des données est mis automatiquement à jour après
chaque instruction SQL du LMD (INSERT, UPDATE, DELETE, LOCK
TABLE, MERGE).
 Ces données sont stockées dans des tables et des vues.
Les tables : sont accessibles en lecture seulement, elles appartiennent à
l’utilisateur SYS et sont lues et écrites par le serveur base de données. Les
données dans les tables sont encodées.
Les vues: elles sont plus intéressantes car bien structurées.
 L’interrogation du dictionnaire des données ne peut se faire
qu’au travers de requêtes SELECT. 159