Exemple

Bases de données
2004 - 2005
Mihaela Mathieu
[email protected]
Bases de données
2004-2005
Objectifs :
– comprendre et maîtriser les fonctions d’un SGBD relationnel (Oracle et MySQL)
– connaître le langage SQL
– savoir développer une application sous Oracle et MySQL
c M. Mathieu
2
Bases de données
2004-2005
Contenu du cours :
– Introduction aux SGBD(R)
– historique
– définitions, panorama du marché
– présentation d’Oracle et de SQL*Plus
– présentation de MySQL
– Manipulation et description des objets d’une base - langage SQL
– Système transactionnel
– Administration système d’Oracle
– catalogues de données
– gestion des utilisateurs
c M. Mathieu
3
Bases de données
2004-2005
– Programmation et bases de données
– programmation en PL/SQL
– Java et le SGBD (Oracle)
c M. Mathieu
4
Bases de données
2004-2005
Contenu TP :
– travail avec Oracle (SQL, PL/SQL, C, Java) depuis une plate-forme Windows
ou Unix
Contenu TD :
* rappel du modèle relationnel
c M. Mathieu
5
Bases de données
2004-2005
Le cours ne comprend pas :
–
–
–
–
–
progammation C pour les bases de données
aspects XML
module Oracle Web
... d’autres modules d’Oracle
notions de bases de données OLAP, bases de données distribuées
c M. Mathieu
6
Bases de données
2004-2005
Pré-requis :
– logique
– cours Systèmes d’Informations
c M. Mathieu
7
Bases de données
2004-2005
Chapitre 1 :
Introduction aux SGBD(R)
1.
2.
3.
4.
Définitions
État du marché
Présentation d’Oracle et de SQL*Plus
Présentation de MySQL
c M. Mathieu
8
Bases de données
2004-2005
Base de données = collection structurée de données cohérentes, intègres, protégées et accessibles simultanément aux utilisateurs.
Système de Gestion de Bases de Données = logiciel complexe capable d’assurer
l’exploitation correcte et efficace des BD.
BD au coeur des Systèmes d’Information → importance capitale de la qualité,
de la fiabilité et de la bonne exploitation des SGBDs.
Volume d’une BD : entre 1 MegaBytes et 10? TeraBytes.
La taille d’une BD doit être transparente à l’utilisateur et au développeur (-), de
même pour son déploiement.
c M. Mathieu
9
Bases de données
2004-2005
Une représentation d’une BD :
Schéma externe
Schéma externe
Schéma conceptuel
Schéma physique
Mémoire (externe)
c M. Mathieu
10
Bases de données
2004-2005
Le schéma conceptuel indique la façon dont les données sont modélisées,
structurées (selon le modèle choisi : relationnel, objet ou autre ; les tables et les
indexes, les procédures ...)
Le schéma physique indique comment dont les structures choisies sont implantées physiquement. Tout est stocké en mémoire.
Les schémas externes sont dédiées aux utilisateurs et donnent des vues (partielles) des données de la base.
c M. Mathieu
11
Bases de données
2004-2005
Les acteurs d’une BD :
– utilisateurs : naïfs, occasionnels, avertis
– développeurs
– administrateurs (DBA) : administrateurs de données et administrateurs système
– experts
c M. Mathieu
12
Bases de données
2004-2005
UTILISATEUR
Schéma externe
Schéma externe
Schéma conceptuel
DEVELOPPEUR
Schéma physique
EXPERT
DBA
Mémoire (externe)
c M. Mathieu
13
Bases de données
2004-2005
Plusieurs configurations sont possibles :
– une BD sur une seule machine
– plusieurs BD sur la même machine
– une BD sur plusieurs machines (BD distribuée)
Une BD peut admettre 1 ou plusieurs utilisateurs simultanés.
Pour n’importe quelle configuration le SGBD doit assurer le bon fonctionnement dans la transparence.
Un SGBD doit assurer :
–
–
–
–
–
–
la définition des bases de données et des “objets” contenus
la manipulation des données
la confidentialité
l’intégrité des données
la sécurité de fonctionnement
la gestion des accès concurrents
c M. Mathieu
14
Bases de données
2004-2005
Les SGBD ont un modèle de représentation des données.
Classification de SGBD selon leur modèle :
–
–
–
–
relationnel
réseau, hiérarchique (plus maintenant)
orienté objet (passé de mode). Exemple : O2
XML
Les fonctions d’un SGBD sont indépendantes du modèle de représentation des
données, mais la réalisation de ces fonctions y dépend.
Quelques SGBD :
–
–
–
–
petits (bureautique) : Paradox, MS Access
moyens SGBD : dBASE, FoxPro
de taille importante : Oracle, DB2, SQL Server, Ingres
logiciel libre : MySQL, PostgeSQL.
Le prix d’un SGBD varie entre 100 et quelques 106 euros.
c M. Mathieu
15
Bases de données
2004-2005
MySQL - logiciel libre
- SGBD relationnel, client/serveur
- facile à installer et à utiliser
- supporte mal les gros volumes de données
- langage SQL réduit par rapport au standard
- assure mal la cohérence et la consistence de données
c M. Mathieu
16
Bases de données
2004-2005
ORACLE - SGBD produit depuis 1975 par Oracle Corporation
- SGBD périodiquement mis à jour et en évolution (dernières versions : 9i et
10G). Version utilisée 8.1.7.
SGBD relationnel (qui tolère des représentations objets depuis la version 8
et des traitements RI et en langage XML depuis la version 9).
Pourquoi Oracle ?
–
–
–
–
–
–
utilise SQL très proche du standard comme langage d’interrogation
fiable
multi-utilisateurs
comporte toutes des fonctions des SGBD
possibilité de développer des applications variées
très répandu en milieu industriel
(les moins : administration assez lourde pour des gros volumes de données, son prix)
c M. Mathieu
17
Bases de données
2004-2005
Les composants d’Oracle :
– SQL*Plus : permet de description et la manipulation des données à travers
le langage SQL
– SQL*Loader, SQL*Net, SQL*Admin
– compilateur Pro*C
– Oracle Entreprise Manager
– OracleWeb : développement des application Web
– Portail d’entreprise, gestionnaire de documents papiers et multimédia
– Oracle JDevelopper : réalisation des applications multi-fonctions, multi-fenêtres
sur la BD
– ...
Architecture Client /Serveur sur des serveurs Unix, Windows (NT, XP ...), Linux et
clients PC, Linux, Unix.
c M. Mathieu
18
Bases de données
2004-2005
Les objets qu’Oracle manipule :
– pour la manipulation des données :
– les tables
– les séquences
– les indexes
– les synonymes
– les clusters - jointure physiques sur plusieurs tables (distantes)
– pour les traitements :
– les fonctions et procédures stockées et/ou appelées explicitement
– les triggers - procédures déclenchées lors des événements précis
– les assertions - procédures de vérifications lancées systématiquement
– pour la gestion des utilisateurs
– les users et leurs droits explicites
c M. Mathieu
19
Bases de données
2004-2005
Les méta-données d’Oracle :
– des tables spécifiques avec des informations sur des objets de la même
catégorie
Oracle sait aussi gérer les transactions.
Une base Oracle peut s’interfacer facilement avec une autre base Oracle ou
autre.
Des nombreux logiciels de développement d’applications travaillent sur les
bases Oracle (Swing, PowerBuilder, OpenRoad, ....)
c M. Mathieu
20
Bases de données
2004-2005
Présentation SQL*Plus
SQL*Plus est un outil d’Oracle pour interroger la BD.
– fonctionne comme un interpréteur en ligne
En entrée :
–
–
–
–
des commandes propres
des ordres SQL
des procédures PL/SQL
des fichiers SQL
Il est appelé avec la commande :
>sqlplus nom [email protected][/mot de passe]
c M. Mathieu
21
Bases de données
2004-2005
Exemple 1 :
SQL>desc agent ou SQL>describe agent
commande SQL*PLUS qui indique la structure de l’objet agent.
Équivalent à :
SQL > desc agent
Exemple 2 :
SQL > select *
from agent ;
(toutes les instructions SQL finissent par les caractère ; )
Exemple 3 :
SQL > @fichier.sql exécute les ordres contenus dans fichier.sql
c M. Mathieu
22
Bases de données
2004-2005
Architecture Oracle utilisée :
Oracle 8.1.7 en client/serveur sur educusers (le serveur) et optxy et asixy
(les clients Sun) et PC (les clients Windows).
c M. Mathieu
23
Bases de données
2004-2005
Chapitre 2 :
Types de données
Les tables d’une BD relationnel se composent des attributs qui contiennent
des données. Chaque attribut a un type de données précis.
Les types de données sous SQL :
– tous les types sont simples (pas de types composés, pas de références ...)
– ordonnés ou pas, indexables ou pas
c M. Mathieu
24
Bases de données
2004-2005
Les types de données sont les suivants :
– Chaînes de caractères :
– VARCHAR2(taille) : taille variable d’au plus 2000 caractères (VARCHAR
sous MySQL)
– CHAR(taille) :taille fixe d’au plus 255 caractères
– LONG : au plus 2 ∗ 109 caractères
– non-indexable
– un seul champ LONG par table
c M. Mathieu
25
Bases de données
2004-2005
– Nombres :
– NUMBER(n,v) : n chiffres, v chiffres après la virgule (DECIMAL sous
MySQL)
– INTEGER
– FLOAT
– Dates :
– DATE : date et heure
– type ordonné
– permet des opérations numériques
– (elle est visible comme une chaîne de caractères selon le format de
sortie)
– Données binaires :
– RAW(taille) : jusqu’à 255 octets
– LONG RAW : jusqu’à 2 Giga Octets
– pareil que long
– pb pour la récupération de la valeur
c M. Mathieu
26
Bases de données
2004-2005
Les constantes :
–
–
–
–
–
texte : délimitées par des apostrophes ou guillemets : ’Chaîne’ ou “Chaine”
numériques : nombre entiers ou à virgule : 12, 12,5 ou 12.5
valeurs logiques : TRUE, FALSE
la date courante : SYSDATE (ou NOW sous MySQL)
l’objet NULL
– manque de données, vide
c M. Mathieu
27
Bases de données
2004-2005
La valeur NULL :
–
–
–
–
–
désigne un manque de données
0 n’est pas NULL
si une expression contient NULL, est vaut NULL
la comparaison avec NULL : IS NULL , est pas = NULL
La fonction NVL permet de corriger dans des expressions une possible valeur NULL :
NVL (attribut, valeur par-défaut)
– La fonction COALESCE est une extension de NVL en admettant un nombre
variable de paramètres, elle retourne la première valeur non nulle rencontrée dans la liste de ses paramètres.
c M. Mathieu
28
Bases de données
2004-2005
Fonctions de conversion :
– TO CHAR : numérique ou date -> chaîne de caractères
– TO NUMBER : chaîne ->numérique
– TO DATE : chaîne -> date
Autres fonctions :
– GREATEST, LEAST
Opération et fonctions pour le type DATE :
– opérations + et - avec la signification :
– date +|- nombre : ajout ou suppression d’un nombre de jours de la date
– date1 - date2 : le nombre de jours entre les deux dates
– calcul de dates : LAST DAY, NEXT DAY, ADD MONTHS
c M. Mathieu
29
Bases de données
2004-2005
La table DUAL
– est créée par défaut par Oracle et accessible par tous les utilisateurs
– ne contient qu’un champ DUMMY avec la valeur ’C’
– est utilisée pour obtenir des valeurs calculées sur la base des constantes
Oracle
Exemple :
SELECT SYSDATE FROM DUAL ;
c M. Mathieu
30
Bases de données
2004-2005
Chapitre 3 :
Langage SQL
1. Manipulations (interrogations et modifications des objets données d’une base)
2. Descriptions (créations, modifications, suppressions)
3. Contrôle (gestion des transactions).
c M. Mathieu
31
Bases de données
2004-2005
SQL = Structured Query Language
–
–
–
–
1980 - version stable
issu de QUEL sur Ingres et SEQUEL sur SYSTEM R (IBM)
langage non-procédural
langage interactif
Trois dimensions du langage :
– définition et modification du schéma d’une base de données relationnelle
– interrogation et modification (non-procédurale) de la base
– contrôle de sécurité et confidentialité de la base.
Une commande SQL est souvent appelée requête.
SQL ne fait pas la différence entre les petites et les grandes lettres (sauf à
l’intérieur des chaînes de caractère).
c M. Mathieu
32
Bases de données
2004-2005
1. Consultation de données - une seule commande :
SELECT [ALL/DISTINCT/UNIQUE]
liste de sélection
FROM nom table, ...
[WHERE condition de recherche]
[GROUP BY liste attributs]
[HAVING condition de recherche]
[ORDER BY liste attributs]
ou une version qui opère avec des ensembles :
requête SELECT1 {UNION | MINUS | INTERSECT} requête SELECT2
car le résulat d’une requête SELECT est un ensemble de tuples, i.e. une table.
c M. Mathieu
33
Bases de données
2004-2005
On appèle clause une partie d’une requête.
Les clauses SELECT et FROM sont obligatoires.
La clause SELECT contient la liste de séléction ainsi que des descripteurs
pour la présentation du résultat.
La liste de sélection contient : des attributs, des expressions où des attributs ou des constantes interviennent. Un attribut est sous la forme champs ou
table.champs.
Le symbole ’*’ dans la liste de sélection signifie tous les champs de la table.
Exemple :
SELECT * FROM LECTEUR ;
fournit tous les enregistrements de cette table.
c M. Mathieu
34
Bases de données
2004-2005
SELECT NOM, PRENOM, AGE FROM LECTEUR
fournit uniquement les champs indiqués de la table, présentés enregistrement par
enregistrement.
Les options DISTINCT, UNIQUE assurent la non-redondance du résultat (option implicite : ALL, donc rédondance).
Exemple :
SELECT PRENOM FROM LECTEUR ;
fournit tous les champs PRENOM des enregistrements de cette table, même les
doublons.
SELECT DISTINCT PRENOM FROM LECTEUR
fournit l’ensemble des champs PRENOM, donc sans les doublons.
c M. Mathieu
35
Bases de données
2004-2005
La clause FROM contient une liste d’au moins une table avec ou sans synonyme pour une écriture équivalente plus rapide.
Exemple : les deux requêtes ci-dessous sont strictement équivalentes :
SELECT NOM, PRENOM, AGE FROM LECTEUR
et
SELECT L.NOM, L.PRENOM, L.AGE FROM LECTEUR L
Une vue ou un synonyme se comporte dans une requête SELECT comme une
table.
(On se concentre pour l’instant sur les requêtes SELECT qui opèrent sur une
seule table).
c M. Mathieu
36
Bases de données
2004-2005
La clause ORDER BY permet d’afficher les résultats selon des champs présents dans sa liste de sélection. Le tri peut être croissant (option ASC - implicite)
ou décroissant (option DESC).
La liste de la clause ORDER BY contient des éléments de même type que la
clause SELECT (sauf le ’*’) et / ou des numéros.
Exemple :
SELECT NOM, PRENOM FROM LECTEUR ORDER BY AGE
et
SELECT NOM, PRENOM , AGE FROM LECTEUR ORDER BY 3
Les numéros font référence aux éléments de la clause SELECT.
En absence de cette clause les résultats sont présentés dans leur ordre d’obtention par le système.
c M. Mathieu
37
Bases de données
2004-2005
Les conditions de recherche sont formées d’une ou plusieurs conditions reliées
par les opérateurs logiques : NOT, AND et OR. Ces conditions sont de 3 types :
– conditions de comparaisons
– conditions de jointure
– conditions sous-requête
c M. Mathieu
38
Bases de données
2004-2005
Conditions de comparaison permettent de comparer un attribut ou une expression à un autre attribut ou valeur.
La syntaxe est :
exp opérateur comparaison exp
exp [NOT] BETWEEN exp AND exp
exp [NOT] IN (liste valeurs)
attribut [NOT] LIKE “chaîne”
attribut IS [NOT] NULL
exp désigne une expression formée des attributs, constantes, fonctions reliés
par les opérateurs : +, -, *, /, ||. Les opérateurs de comparaisons sont ceux connus.
Exemple :
SELECT * FROM LECTEUR
WHERE AGE < 10 ;
c M. Mathieu
39
Bases de données
2004-2005
Le prédicat BETWEEN permet de vérifier si l’expression située à gauche se
trouve dans l’intervalle défini.
Exemple :
SELECT *
FROM LECTEUR
WHERE AGE BETWEEN 10 AND 14 ;
Le prédicat IN permet de vérifier si un attribut se trouve dans la liste de valeurs
indiquées.
Exemple :
SELECT *
FROM LECTEUR
WHERE AGE IN (10, 11, 12, 13, 14) ;
c M. Mathieu
40
Bases de données
2004-2005
Le prédicat LIKE permet de réaliser des comparaisons entre un attribut et une
chaîne de caractères en utilisant des caractères de substitution :
– pour zéro ou plusieurs caractères (%)
– pour un seul caractère ( )
Exemple :
SELECT *
FROM LECTEUR
WHERE PRENOM LIKE ’Mich%’ ;
permet d’avoir les enregistrements qui correspond aux lecteurs dont le prénom
commence par ’Mich’.
c M. Mathieu
41
Bases de données
2004-2005
Les fonctions sur les chaînes de caractères :
– UPPER() - conversion en majuscules
– LOWER() - conversion en minuscules
– TRIM() - suppression des répétitions d’un caractère à gauche ou à droite ou
aux deux extrémités
– SUBTRING() - création d’une chaîne a partir d’une autre.
Les fonction sur les dates :
– DATE(exp) - conversion d’une expression numérique ou chaîne de caractère
vers une date
– DAY(date) - le jour du mois de la date
– MONTH(date) - le mais de la date
– YEAR(date) - l’année de la date
c M. Mathieu
42
Bases de données
2004-2005
Les fonctions agrégat (de regroupement) permettent d’obtenir des données synthétiques concernant une table ou des attributs de la table :
–
–
–
–
–
–
–
COUNT(* / attribut) - le nombre d’enregistrements satisfaisant la requête
SUM(attribut) - la somme des valeurs
MIN(attribut) - le minimum
MAX(attribut) - le maximum
AVG(attribut) - la moyenne arithmétique
STDEV(attribut) - l’écart-type
VARIANCE(attribut) - la variance liée à l’écart-type
La fonction COUNT peut contenir aussi les directives DISTINCT ou ALL.
Exemple :
SELECT COUNT(*), AVG(AGE) FROM LECTEUR ;
fournit le nombre d’enregistrements dans la table LECTEUR et la moyenne d’âge.
c M. Mathieu
43
Bases de données
2004-2005
La clause GROUP BY permet de grouper le résultat en fonction des valeurs des
attributs présents dans la liste SELECT.
Exemple 1 :
SELECT COUNT(*), AGE
FROM LECTEUR
GROUP BY AGE ;
fournit le nombre d’enregistrements dans la table LECTEUR pour chaque tranche
d’age. La même requête sans la clause GROUP BY est érronnée, car imbigue.
Exemple 2 :
SELECT COUNT(*)
FROM LECTEUR
WHERE PRENOM = ’Anna’ ;
fournit le nombre d’enregistrements dans la table LECTEUR qui ont le prénom
’Anna’.
c M. Mathieu
44
Bases de données
2004-2005
La clause HAVING décrit une restriction posées sur un groupe des tuples,
donc toute condition qui est posée sur un agrégat doit figurer dans la clause HAVING.
Exemple 3 :
SELECT COUNT(*), AGE
FROM LECTEUR GROUP BY AGE
HAVING COUNT(*) >= 2 ;
fournit les tranches d’age avec au moins deux enregistrements dans la table
LECTEUR .
c M. Mathieu
45
Bases de données
2004-2005
Travail avec la valeur NULL
Des champs peuvent avoir la valeur NULL.
Exemple :
select anom, aprenom, commission
from agent ;
Résultat :
JACQ
LEM
DESCH
BART
BLANCO
DESS
Aime
Roger
Didier
Fabs
Laurent
Lim
10.5
....
c M. Mathieu
46
Bases de données
2004-2005
Les requêtes d’agrégat comme :
select min(commission)
from agent
ou
select count(commission)
from agent
opèrent uniquement sur des valeurs non NULL.
La requête :
select anom, aprenom, nvl(to char(commission), ’sans’)
from agent
affiche :
ANOM
---------JACQ
LEM
DESCH
BART
BLANCO
APRENOM
--------------Aime
Roger
Didier
Fabs
Laurent
NVL(TO CHAR(COMMISSION),’SANS’)
----------sans
sans
sans
sans
10.5
DESS
Lim
sans
c M. Mathieu
47
Bases de données
2004-2005
Le fonctionnement de la fonction COUNT et des autres fonctions agrégat
Exemples :
select count(*)
from agent ;
fournit le nombre total d’enregistrements de la table AGENT.
select count(fonction)
from agent ;
fournit le nombre d’apparitions du champ FONCTION de la table AGENT.
select count(distinct fonction)
from agent ;
fournit le nombre d’apparitions des valeurs distinctes du champ FONCTION.
c M. Mathieu
48
Bases de données
2004-2005
La requete :
select fonction, count(*)
from agent ;
est ambigue et non-résolue.
La requete :
select fonction, count(*)
from agent
group by fonction ;
fournit :
FONCTION
COUNT(*)
------------ ---------ANALYSTE
2
CAPITAINE
1
COMMERCIAL
4
DIRECTEUR
1
EMPLOYE
4
GARDIEN
1
PRESIDENT
1
qui est la liste de toutes les valeurs du champ FONCTION, avec leur nombre
d’apparitions.
c M. Mathieu
49
Bases de données
2004-2005
Si la liste de sélection d’une requete SELECT contient des fonctions d’agrégat
et des attributs des tables de sélection, dans la clause GROUP BY il faut impérativement indiquer tous les attributs ou fonctions scalaires sur ces attributs qui
apparaissent dans la clause SELECT.
c M. Mathieu
50
Bases de données
2004-2005
Requêtes SELECT sur plusieurs tables
Dans la clause FROM on fait figurer plusieurs tables ou on utilise une condition
de sous-requête.
Exemple :
SELECT lecteur.*, pret.*
FROM lecteur, pret ;
fournit les enregistrements concatennés de deux tables.
En absence de toute condition entre les champs de deux tables la requête
réalise simplement le produit cartésien : il faut donc ajouter des conditions de
jointure.
c M. Mathieu
51
Bases de données
2004-2005
Conditions de jointure
Un jointure est un lien entre des attributs semblables des deux tables différente :
table1.attribut1 opérateur table2.attribut2
L’opérateur de comparaison peut être : =, <, >, <> ( !=), <=, >=.
Exemple :
SELECT LECTEUR.CODE LECTEUR, LECTEUR.NOM, LECTEUR.PRENOM
FROM LECTEUR, ENSEIGNANT
WHERE LECTEUR.NOM = ENSEIGNANT.NOM AND
LECTEUR.PRENOM = ENSEIGNANT.PRENOM ;
permet d’extraire des informations de la table LECTEUR qui ont un correspondant
dans la table ENSEIGNANT.
c M. Mathieu
52
Bases de données
2004-2005
Les jointures s’appèlent internes quand l’existence des deux attributs (table1.attribut1,
table2.attribut2) est obligatoire.
Un cas particulier de jointure interne est l’équijointure : on teste l’égalité entre
des attributs qui ont des domaines compatibles et la même signification sémantique.
Exemple :
SELECT lecteur.*, pret.*
FROM lecteur, pret
WHERE lecteur.code lecteur = pret.code lecteur ;
on fournit uniquement les lecteurs qui ont des prêts en cours, un lecteur apparaît
autant de fois que le nombre d’enregistrements de la table pret.
c M. Mathieu
53
Bases de données
2004-2005
Exemple : les lecteurs qui ont en prêt la livre “L’Ile Mistérieuse” :
SELECT nom, prenom
FROM lecteur l, pret p, document d
WHERE lecteur.code lecteur = pret.code lecteur
AND p.code document = d.code document
AND d.titre = ”L’Ile Mystérieuse” ;
L’ordre des conditions d’équijointure n’est pas importante, de même dans une
équijointure on peut permuter les deux attributs.
Remarque : d’autres conditions que = ou != sont rarement utilisées dans les
jointures.
c M. Mathieu
54
Bases de données
2004-2005
Une condition de jointure externe s’exprime à l’aide du (+) mis à coté d’un
attribut :
table1.attribut1 = table2.attribut2(+)
ou
table2.attribut2(+) = table1.attribut1
avec la signification : si des enregistrement de la table2 existent et vérifient la
clause WHERE ils sont sélectionnés conformément à une jointure classique ; si
des enregistrement de la table2 n’existent pas les enregistrements de la table1
sont toutefois sélectionnés.
c M. Mathieu
55
Bases de données
2004-2005
Exemple : soient les tables EMPLOYE(NOM, CODE S) et SERVICE(CODE S,
NOM SERVICE) avec les enregistrements :
NOM
Anna
Nicolas
André
Paul
CODE S
170
800
117
c M. Mathieu
CODE S
117
170
170
NOM SERVICE
RH
Direction
Comptabilité
56
Bases de données
2004-2005
Pour afficher les employés et leur service :
SELECT nom, nom service
FROM employe, service
WHERE e.code s = s.code s ;
mais l’employé ’Paul’ n’y figure pas.
La jointure ouverte (externe) :
SELECT nom, nom service
FROM employe, service
WHERE e.code s = s.code s(+) ;
fournit :
NOM
André
Anna
Nicolas
Paul
c M. Mathieu
NOM SERVICE
RH
Comptabilité
RH
57
Bases de données
2004-2005
Autre jointure ouverte :
SELECT nom, nom service
FROM employe, service
WHERE e.code s(+) = s.code s ;
fournit :
NOM
André
Anna
Nicolas
NOM SERVICE
RH
Contabilité
RH
Direction
Si on veut obtenir une jointure à deux entrées, il faut utiliser l’opération UNION.
c M. Mathieu
58
Bases de données
2004-2005
Une condition de sous-requête :
[NOT] EXISTS (requête SELECT)
la condition est évaluée à vrai, si la requête SELECT retourne au moins une
ligne.
Exemple :
SELECT *
FROM PRET
WHERE EXISTS
(SELECT * FROM DOCUMENT) ;
affiche le contenu de la table PRET uniquement si la table DOCUMENT n’est
pas vide.
c M. Mathieu
59
Bases de données
2004-2005
Exemple 1 : vérifier que des exemplaires du livre “Les Fourmis” sont en prêt :
SELECT COUNT(*)
FROM document d
WHERE d.titre = ’Les formis’
AND EXISTS
(SELECT *
FROM pret p
WHERE d.code document = p.code document) ;
c M. Mathieu
60
Bases de données
2004-2005
Exemple 2 : vérifier que tous les exemplaires du livre “Les Fourmis” sont en
prêt :
SELECT ’Oui’
FROM document d
WHERE d.titre = ’Les formis’
AND NOT EXISTS
(SELECT *
FROM pret p
WHERE d.code document = p.code document)
HAVING COUNT(*) = 0 ;
c M. Mathieu
61
Bases de données
2004-2005
Requêtes imbriquées
Les imbrications des requêtes peuvent apparaître au niveau des conditions de
la clause WHERE et au niveau des tables de sélection.
(Oracle admet jusqu’à 16 requêtes imbriquées.)
Les imbrications de la clause WHERE sont de type :
table.attribut = (requête SELECT)
ou
table.attribut IN (requête SELECT)
ou
table.attribut {<,>,<=,>=} {ANY|ALL}(requête SELECT)
Les deux premières formes sont sémantiquement équivalentes, mais leur façon d’être résolues par le SGBD est différent.
c M. Mathieu
62
Bases de données
2004-2005
Les sous-requêtes qui apparaissent dans la partie droite ne doivent pas contenir des clauses ORDER BY.
Une requête imbriquée peut s’écrire comme une requête non-imbriquée.
Exemple : afficher tous les lecteurs ayant emprunté des livres depuis 24h :
SELECT DISTINCT l.nom, l.prenom
FROM lecteurs l
WHERE l.code lecteur IN (SELECT p.code lecteur
FROM pret p
WHERE p.date pret > SYSDATE -1) ;
Cette requête peut s’écrire comme :
SELECT DISTINCT l.nom, l.prenom
FROM lecteurs l, pret p
WHERE l.code lecteur = p.code lecteur
AND p.date pret > SYSDATE -1 ;
c M. Mathieu
63
Bases de données
2004-2005
Exemple : afficher les agents qui gangent au moins comme un plus grand
salaire d’une fonction :
SELECT anom, aprenom
FROM agent
WHERE salaire >= ANY (SELECT MAX(salaire) FROM agent
GROUP BY fonction)
Exemple : afficher les agents qui gangent plus que le plus grand salaire de
chaque fonction :
SELECT anom, aprenom
FROM agent
WHERE salaire >= ALL (SELECT MAX(salaire) FROM agent
GROUP BY fonction)
c M. Mathieu
64
Bases de données
2004-2005
Le résultat d’une requête SELECT peut être utilisé comme une table de sélection.
Exemple : afficher les agents qui gagnent le plus grand et le plus bas salaire :
select anom, aprenom
from agent A,
( select min(salaire) as sal from agent) B,
( select max(salaire) as sala from agent) C
where A.salaire = B.sal or A.salaire = C.sala ;
Dans les requêtes de cette forme l’usage des synonymes devient impératif.
(Les synonymes sont utilisés pour les noms des tables de la clause FROM et
pour la liste de sélection de la clause SELECT. Ces synonymes sont volatiles.)
c M. Mathieu
65
Bases de données
2004-2005
Une requête ensembliste est de forme :
requête1 UNION [ALL] | MINUS | INTERSECT requête2
où UNION, MINUS, INTERSECT réalise les opérations des ensembles connues.
La version UNION ALL réalise l’union des résultats de chaque requête sans
éliminer les doublons.
Les listes de sélection des deux requêtes se doivent être compatibles par type.
c M. Mathieu
66
Bases de données
2004-2005
Exemple : afficher les agents qui gagnent le plus grand et le plus bas salaire :
select anom, aprenom
from agent
where salaire = (select min(salaire) from agent)
union
select anom, aprenom
from agent
where salaire = (select max(salaire) from agent) ;
c M. Mathieu
67
Bases de données
2004-2005
Langage SQL - Mise à jour des données
Modifications de données :
– insertion INSERT
– mise à jour - UPDATE
– suppression - DELETE (TRUNCATE)
1. Insertion
INSERT INTO nom table [(liste attributs)]
VALUES (liste valeurs)
Si les champs de la table sont explicités par (liste attributs), la liste des valeurs
doit avoir la même longueur car la correspondance se fait de 1 à 1, sinon, la liste
des valeurs doit correspondre en longueur et en type à la liste des attributs de la
table dans leur ordre de définition.
Si des attributs ne sont pas spécifiés, ils prendront la valeur NULL ou leur
valeur par défaut.
c M. Mathieu
68
Bases de données
2004-2005
Exemple : la table COLLECTIVITE a la structure suivante :
SQL>desc collectivite
Name
Type
---------------------------------------------------------------------IDCOLLECT
NUMBER
CDESINATION
VARCHAR2(20)
alors :
INSERT INTO COLLECTIVITE
VALUES (9, ’Picardie’) ;
est équivalent à :
INSERT INTO COLLECTIVITE (CDESINATION, IDCOLLECT)
VALUES (’Picardie’, 8) ;
c M. Mathieu
69
Bases de données
2004-2005
Une autre forme de l’ordre INSERT :
INSERT INTO nom table[(liste attributs)]
requête SELECT ;
permet l’insertion dans la table indiquée des enregistrements (même plusieurs)
obtenus avec la requête SELECT.
Exemple :
INSERT INTO AGENT BIS
SELECT * FROM AGENT ;
réalise une copie de la table AGENT dans la table AGENT BIS
qui a la même structure.
c M. Mathieu
70
Bases de données
2004-2005
2. Modification
UPDATE nom table
SET nom attribut = valeur,.../
(liste attributs) = (liste valeurs)...
WHERE condition ;
Cette instruction permet la modification des enregistrements vérifiant la condition ; cette modification porte sur les attributs précisés dans la clause SET qui se
voient attribués les valeurs indiquées.
Ces valeurs sont soit des constantes, soit des expressions contenant des opérateurs et fonctions (excepté les fonctions agrégats) qui portent sur des constantes
et des attributs.
Le type d’une valeur associée pour un attribut modifié doit être compatible
avec le type de l’attribut.
c M. Mathieu
71
Bases de données
2004-2005
Exemple :
UPDATE COMMANDE
SET DATEC = sysdate,
DATEL = DATEL + 10
WHERE IDCOMM = 20 ;
est équivalent avec :
UPDATE COMMANDE
SET (DATEC , DATEL) = (sysdate, DATEL+ 10)
WHERE IDOMM = 20 ;
Cette requête modifie l’enregistrement de la table COMMANDE ayant la
valeur 20 pour l’attribut clé IDCOMM.
c M. Mathieu
72
Bases de données
2004-2005
La forme suivante de UPDATE permet de faire la modification des attributs
avec des valeurs obtenues par une ou plusieurs requêtes SELECT (appelées
sous-requêtes) :
UPDATE nom table
SET (attribut) = (sous-requête), ...
(liste attributs) = (sous-requête)
[WHERE condition] ;
Les sous requêtes doivent impérativement retourner un seul enregistrement.
Si la sous-requête fait intervenir la table nom table et les attributs à modifier,
on dit que la requête est corrélée. Dans une requête corrélée on doit utiliser des
synonymes pour les tables.
c M. Mathieu
73
Bases de données
2004-2005
Exemple 1 - une requête non-corrélée qui attribue à un certain agent le salaire
maximum de la fonction ’COMMERCIAL’ :
UPDATE AGENT
SET SALAIRE = (SELECT MAX(SALAIRE) FROM AGENT WHERE FONCTION =
’COMMERCIAL’)
WHERE IDAGENT = 210 ;
Exemple 2 - une requête corrélée, qui attribue à un certain agent le salaire maximum de son service :
UPDATE AGENT A1
SET SALAIRE = (SELECT MAX(A2.SALAIRE)
FROM AGENT A2
WHERE A2.SERVICE ID = A1.SERVICE ID)
WHERE IDAGENT = 210 ;
c M. Mathieu
74
Bases de données
2004-2005
3. Suppression
DELETE FROM nom table
[WHERE condition] ;
Cette instruction permet d’enlever de la table nom table tous les enregistrements qui vérifient la condition.
En absence de la clause WHERE tous les enregistrements de la table seront
effacés.
L’ordre :
TRUNCATE nom table ;
permet la suppression rapide de tous les enregistrements de la table nom table.
c M. Mathieu
75
Bases de données
2004-2005
Exemple :
DELETE FROM COMMANDE
WHERE DATEL < SYSDATE - 365 ;
efface les enregistrements de la table COMMANDE qui ont la date de livraison
passé de plus d’un an.
Les instructions :
DELETE FROM COMMANDE ;
et
TRUNCATE COMMANDE ;
fournissent ’presque’ le même résultat : l’effacement de tous les enregistrements
de la table COMMANDE, mais la deuxième forme est plus rapide et ne nécessite
pas de confirmation.
c M. Mathieu
76
Bases de données
2004-2005
SQL - langage de contrôle des données
L’effet des mises à jour :
– les requête INSERT, UPDATE, DELETE modifient d’abord le buffer utilisateur. Elles doivent être rendues définitives avec l’ordre SQL :
COMMIT
ou annulées avec l’ordre
ROLLBACK
– la requête TRUNCATE est effective (une fois qu’elle est saisie, elle est directement exécutée sur la base).
c M. Mathieu
77
Bases de données
2004-2005
Transactions
Transaction = une séquence de commandes traitée comme une opération atomique
afin de garantir la cohérence et l’intégrité des données de la base.
Les commandes sont des mises à jour et / ou des simples consultations.
Exemple 1 : l’enregistrement d’une commande VPC de plusieurs produits, qui
comporte :
– l’insertion de l’entête de la commande
– l’insertions des produits commandés
Ces insertions doivent être exécutées de façon indissociable et définitive.
Exemple 2 : la réservation de places dans un avion (problème de concurrence
d’accès) - solution : gestion transactionnelle des opérations sur la base.
Exemple 3 : un transfert bancaire entre 2 comptes.
c M. Mathieu
78
Bases de données
2004-2005
Les propriétés d’une transactions :
– atomicité : les opérations d’une transaction sont exécutées comme une
unique opération
– consistance : la base reste dans un état cohérent à la fin de la transaction
– isolation : la transaction s’exécute indépendamment d’autres opérations sur
la base
– durabilité : les mises à jour d’une transaction sont définitives
(ACID)
Une transaction comporte :
– un début : création
– une fin : validation ou annulation
c M. Mathieu
79
Bases de données
2004-2005
Création d’une transaction :
– implicite : à partir de la première instruction qui suit la connexion à la base
ou après un ordre COMMIT ou ROLLBACK
– explicite : par l’exécution de l’ordre :
SET TRANSACTION {READ ONLY | READ WRITE}
Validation :
– implicite : à la fin de la connexion
– explicite : par l’ordre COMMIT
Annulation :
– explicite : par l’ordre ROLLBACK ou :
ROLLBACK TO SAVEPOINT point de retour
ou le point de retour est crée avec l’ordre :
SET SAVEPOINT point de retour
à l’intérieur de la transaction.
c M. Mathieu
80
Bases de données
2004-2005
Exemple 1 :
SQL>SELECT* FROM AGENT ;
SQL>INSERT INTO AGENT...
SQL>UPDATE AGENT SET ...
SQL>SELECT * FROM AGENT ;
SQL>COMMIT ;
Les ordres INSERT et UPDATE font partie d’une même transaction, ils seront
exécutés l’un après l’autre, mais leur exécution est faite dans un intervalle
de temps indivisible. Lors du deuxième ordre SELECT les modifications sont
visibles, bien qu’elles ne soient pas encore effectuées dans la base.
Exemple 2 :
SQL>SET TRANSACTION READ ONLY
SQL>SELECT * FROM AGENT ;
SQL>SELECT * FROM SERVICE ;
SQL>COMMIT ;
On obtient le contenus de deux tables au même moment, à savoir à la déclaration
de la transaction.
c M. Mathieu
81
Bases de données
2004-2005
Une transaction READ ONLY contient uniquement des ordres SELECT et
LOCK TABLE nom table
Les requête SELECT sont exécutées avec le contenu de la base tel qu’il était au
moment de la déclaration SET TRANSACTION.
SQL*Plus dispose aussi la commande :
SET AUTOCOMMIT [ON | OFF]
Si on a l’option ON sur le paramètre AUTOCOMMIT, après chaque requête correcte syntaxiquement l’ordre COMMIT est fait automatiquement.
c M. Mathieu
82
Bases de données
2004-2005
SQL - Langage de définition des données
Trois ordres pour définir des objets dans la base :
CREATE objet nom liste paramètres
ALTER objet nom option| paramètre
DROP objet [option]
ou objet peut être : table, vue, indexe, synonyme, séquence, profil utilisateur, procédure stockée ou déclenchée, tablespace, base, etc.
Ces ordres se auto-valident, à savoir si ils sont corrects syntaxiquement et
fonctionellement, ils sont immédiatement exécutés dans la base de façon définitive et irréversible.
c M. Mathieu
83
Bases de données
2004-2005
Manipulation des tables
Une table se compose de colonnes, qui ont chacune un type, et doit respecter des contraintes d’intégrité. Les contraintes d’intégrité sont des conditions qui
s’exprime sur une ou plusieurs colonnes de la table avec, parfois, des colonnes
d’une deuxième table.
La définition d’une table se fait avec l’ordre :
CREATE TABLE nom table
[(nom colonne type [DEFAULT valeur ] [contrainte colonne],...
[contrainte table, ...])]
[options de stockage, cluster, parallélisme, etc]
[AS requête SELECT ]
Les options de stockage et autres dépendent du SGBD (travail de l’administrateur de BD).
c M. Mathieu
84
Bases de données
2004-2005
Exemple 1 :
CREATE TABLE COLLECTIVITE
(IDCOLL NUMBER(2) DEFAULT 0,
NOM COLLECTIVITE VARCHAR2(30)) ;
Les contraintes d’intégrités sur une colonne contrainte colonne ou sur une
table contrainte table sont de forme :
[CONSTRAINT nom contrainte] description contrainte
Si le nom d’une contrainte n’est pas indiquée, il est généré automatiquement.
Le nom sert à pouvoir supprimer la contrainte ou pouvoir l’activer / désactiver.
La description contrainte est de forme :
[NOT] NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY
c M. Mathieu
85
Bases de données
2004-2005
[NOT] NULL indique si la colonne est autorisée ou pas à avoir la valeur NULL
(par défaut NULL autorisé).
UNIQUE [(colonne, ..)] indique que la colonne ou la liste des colonnes engendrent l’unicité de l’enregistrement (forment une clé potentielle).
CHECK (condition) indique que la condition qui porte sur une ou plusieurs
colonnes de la table doit être toujours satisfaite.
PRIMARY KEY[(colonne,...)] indique que la ou les colonnes auxquelles la
contrainte est associée forment la clé primaire de la table (ORACLE admet donc
des clés multiples ayant jusqu’au 16 colonnes).
c M. Mathieu
86
Bases de données
2004-2005
FOREIGN KEY [(colonne, ...)] REFERENCES table2 (colonne, ...) [ON DELETE CASCADE]
Une telle contrainte est une contrainte d’intégrité référentielle. La ou les colonnes
de cette clé étrangère doivent correspondre en nombre et en type avec les colonnes indiquées de la table2. De plus, les colonnes de cette table2 (qui peut être
différente de la table de définition ou la même) doivent impérativement être soit
une clé primaire, soit associées à une contrainte UNIQUE.
L’effet de cette contrainte est que les valeurs des colonnes de la table1 doivent
toujours se retrouver dans la table2.
La clause ON DELETE CASCADE indique que si un enregistrement de la table2
est supprimé, les enregistrements de la table1 avec la clé étrangère correspondante doivent aussi être supprimés.
Aucune contrainte d’intégrité n’est obligatoire pour une table.
c M. Mathieu
87
Bases de données
2004-2005
Exemple 2 :
CREATE TABLE COLLECTIVITE
(IDCOLL NUMBER(2) DEFAULT 0 CONSTRAINT NN COLL IDCOLL NOT NULL
CONSTRAINT PK COLLECTIVITE PRIMARY KEY,
NOM COLLECTIVITE VARCHAR2(30) NOT NULL) ;
équivalent à :
CREATE TABLE COLLECTIVITE
(IDCOLL NUMER(2) DEFAULT 0 CONSTRAINT NN COLL IDCOLL NOT NULL ,
NOM COLLECTIVITE VARCHAR2(30) NOT NULL,
CONSTRAINT PK COLLECTIVITE PRIMARY KEY(IDCOLL)) ;
Exemple 3 :
CREATE TABLE EMPLOYE
(IDEMP NUMBER(5) DEFAULT 0 NOT NULL CONSTRAINT PK EMPLOYE PRIMARY KEY,
NOM VARCHAR25(50) NOT NULL,
PRENOM VARCHAR2(50)NOT NULL,
IDCHEF NNUMBER(5) NULL,
NO SS VARCHAR2(15) CONSTRAINT CC EMP1 CHECK(NO SS LIKE ’1%’ OR NO SS
LIKE ’2%’),
IDCOLL NUMBER(2) CONSTRAINT FK EMP1 FOREIGN KEY REFERENCES
COLLECTIVITE(IDCOLL)) ;
c M. Mathieu
88
Bases de données
2004-2005
Exemple 4 :
CREATE TABLE EMPLOYE
(IDEMP NUMBER(5) DEFAULT 0 NOT NULL CONSTRAINT PK EMPLOYE PRIMARY KEY,
NOM VARCHAR25(50) NOT NULL,
PRENOM VARCHAR2(50)NOT NULL,
IDCHEF NNUMBER(5) NULL ,
NO SS VARCHAR2(15) CONSTRAINT CC EMP1 CHECK(NO SS LIKE ’1%’ OR NO SS
LIKE ’2%’),
IDCOLL NUMBER(2) CONSTRAINT FK EMP1 FOREIGN KEY REFERENCES
COLLECTIVITE(IDCOLL),
CONSTRAINT FK EMP2 FOREIGN KEY (IDCHEF) REFERENCES EMPLOYE(IDEMP)) ;
Dans cette table il y a deux clés étrangères. La contrainte FK EMP1 exige que
la table COLLECTIVITE soit créée avant et que les insertions dans la table EMPLOYE
soient faites après celles de la table COLLECTIVITE. La contrainte FK EMP2 est très
difficile à satisfaire lors des insertions, car les insertions doivent être faites dans
un ordre stricte avant d’assurer l’insertion d’un chef avant ses employés. Que
faire pour s’affranchir ?
c M. Mathieu
89
Bases de données
2004-2005
La clause “AS requête SELECT” permet d’insérer directement dans la table
les enregistrements obtenus avec la requête. L’usage de cette clause change le
format de description de la table :
– la description peut manquer et, dans ce cas, les noms des colonnes de la
table seront donnés par les noms des colonnes présentes dans la requête ;
les mêmes contraintes d’intégrité sont également ajoutées.
– si la description est présente, on n’indique pas le type des colonnes qui
est donné par le type des colonnes de SELECT ; le nombre de colonnes
retournées par ce SELECT doit correspondre au nombre des colonnes de
la table.
Exemple 5 :
CREATE TABLE EMP ARCHIVE
AS SELECT * FROM EMPLOYE ;
Attention ! toute mise à jour postérieure à cette déclaration pour la table EMPLOYE
n’est pas répercutée sur la table EMP ARCHIVE.
c M. Mathieu
90
Bases de données
2004-2005
La modification d’une table est réalisée par un ordre :
ALTER TABLE nom table option ;
Les options sont :
– des changements de l’espace de stockage, des partitions, du parallélisme, etc
−→ non explicitées dans ce cours
– RENAME TO nom nouvelle table
qui permet de changer le nom de la table
– {ADD | MODIFY} [(colonne type [DEFAULT valeur] contrainte colonne , ...)]
[contrainte table]
– DROP COLUMN nom colonne
qui permet d’ajouter des nouvelles colonnes à la table et des nouvelles contraintes
sur la tables ou de modifier le type, la valeur par défaut et les contraintes NULL
d’une colonne.
c M. Mathieu
91
Bases de données
2004-2005
Les modifications de type autorisées sont celles qui augmentent ou diminuent
(à condition que ce soit possible) la taille du type ou les transformations VARCHAR2 ⇐⇒ CHAR.
Exemple 6 :
ALTER TABLE EMP ARCHIVE RENAME ARCHIVE EMPLOYE ;
change le nom de la table créée en exemple 5
Exemple 7 :
ALTER TABLE EMPLOYE ADD
(DATEE DATE DEFAULT SYSDATE NOT NULL) ;
ALTER TABLE EMPLOYE MODIFY
(NOM VARCHAR25(50) NOT NULL,
PRENOM VARCHAR2(50)NOT NULL)
ajoute une colonne à la table EMPLOYE et modifie deux autres colonnes.
Exemple 8 : une solution à la question posée en exemple 4 est de faire d’abord
les insertions et ensuite ajouter la contrainte d’intégrité référentielle :
ALTER TABLE EMPLOYE ADD
CONSTRAINT FK EMP2 REFERENCES EMPLOYE(IDEMP) ;
c M. Mathieu
92
Bases de données
2004-2005
Les options :
– {DROP | DISABLE | ENABLE} {CONSTRAINT nom contrainte | PRIMARY
KEY | UNIQUE (liste colonnes)} CASCADE ;
permet de supprimer, désactiver ou activer une contrainte d’intégrité sur une
table. L’option CASCADE est obligatoire si la contrainte est PRIMARY KEY
ou UNIQUE et si elle est référencée par des contraintes FOREIGN KEY.
Exemple 9 : une autre solution à la question posée en exemple 4 est de désactiver
la contrainte d’intégrité référentielle, de faire les insertions et ensuite de l’activer.
ALTER TABLE EMPLOYE DISABLE
CONSTRAINT FK EMP2 ;
INSERT INTO EMPLOYE ... ;
INSERT INTO EMPLOYE ... ;
ALTER TABLE EMPLOYE DISABLE
CONSTRAINT FK EMP2 ;
c M. Mathieu
93
Bases de données
2004-2005
Exemple 10 : modifier la clé primaire d’une table signifie sa suppression suivie de
sa seconde création et des éventuelles ré-créations des contraintes d’intégrités
référentielles :
ALTER TABLE EMPLOYE DROP PK EMPLOYE CASCADE ;
ALTER TABLE EMPLOYE ADD CONSTRAINT PK EMPLOYE PRIMARY KEY (NOM,PRENOM) ;
ALTER TABLE EMPLOYE ADD CONSTRAINT CU EMPLOYE UNIQUE (IDEMP) ;
ALTER TABLE EMPLOYE ADD CONSTRAINT
FK EMP2 FOREIGN KEY (RESPONSABLE)
REFERENCES EMPLOYE(IDEMP) ;
c M. Mathieu
94
Bases de données
2004-2005
La suppression d’une table est réalisée avec l’ordre :
DROP TABLE nom table CASCADE CONSTRAINTS ;
avec l’option CASCADE CONSTRAINTS on supprime toutes les contraintes référentielles qui font appel à cette table.
Lors de l’exécution de cet ordre le SGBD :
– supprime tous les enregistrements de la table
– supprime tous les indexes associés
– invalide les objets (synonyme, vue ou autre) qui font référence à cette table.
Exemple 11 :
DROP TABLE EMPLOYE CASCADE CONSTRAINTS ;
c M. Mathieu
95
Bases de données
2004-2005
Les séquences
Une séquence est un compteur qui s’incrémente et qui fournit sa valeur courante afin d’être utilisée comme valeur de colonne.
Création :
CREATE SEQUENCE nom séquence [INCREMENT BY valeur1] [START WITH
valeur2] [MAXVALUE valeur3 | NOMAXVALUE] [MINVALUE valeur4 | NOMINVALUE] [CYCLE | NOCYCLE] ;
Modification :
ALTER SEQUENCE nom séquence paramètres
où les paramètres sont les mêmes que pour la création, à l’exception de START
WITH.
Suppression :
DROP SEQUENCE nom séquence ;
c M. Mathieu
96
Bases de données
2004-2005
L’usage d’une séquence :
– séquence.CURVAL : donne la valeur courante
– séquence.NEXTVAL : incrémente la séquence et retourne le résultat.
Une séquence peut être utilisée par plusieurs tables.
Exemple :
CREATE SEQUENCE NO EMP START WITH 200 INCREMENTED BY 5 ;
INSERT INTO EMPLOYE VALUES (NO EMP.NEXTVAL, ’DUPONT’, ’D.’, ..) ;
ALTER SEQUENCE NO EMP INCREMENTED BY 10) ;
INSERT INTO EMPLOYE VALUES (NO EMP.NEXTVAL, ’DUPOND’, ’D.’, ..) ;
SELECT NO EMP.CURVAL
FROM DUAL ;
c M. Mathieu
97
Bases de données
2004-2005
Les vues
Une vue est une table logique basée sur une ou plusieurs tables ou vues et
qui n’a pas d’existence physique.
– elle est une requête SELECT
– elle ne stocke donc pas des données
– les mises à jour à partir des tables est automatique
Une vue est créée pour :
– la confidentialité
– masquer la complexité des données de la base en consultation / modification
c M. Mathieu
98
Bases de données
2004-2005
Création :
CREATE [OR REPLACE] VIEW nom vue [(liste colonnes)] AS requête SELECT
[WITH READ ONLY] [WITH CHECK CONTRAINT condition] ;
La vue est créée à partir de la requête SELECT indiquée qui ne doit pas contenir la clause ORDER BY. Les noms des colonnes de la vue sont soit explicités
avec liste colonnes, soit donnés par la requête SELECT.
Suppression :
DROP VIEW nom vue ;
c M. Mathieu
99
Bases de données
2004-2005
Exemple :
CREATE VIEW AFFECTATION (SALARIE, GROUPE)
AS
SELECT IDEMP, IDCOLL FROM EMPLOYE ;
CREATE VIEW CHEFS AS
SELECT DISTINCT NOM AS NOM CHEF, PRENOM AS PRENOM CHEF
FROM EMPLOYE E1
WHERE EXISTS (SELECT * FROM EMPLOYE E2
WHERE E2.ID CHEF = E1.IDEMP) ;
c M. Mathieu
100
Bases de données
2004-2005
Les index
Les index sont des objets Oracle attachés à une table, créés implicitement ou
explicitement dans le but d’accélérer l’accès à cette table.
Un index se traduit par une structure de données de type table de hachage ou B-arbre.
Index créés :
– implicitement - les clés primaires et les attributs formant une contrainte d’intégrité UNIQUE
– explicitement avec l’ordre :
CREATE INDEX nom index ON nom table (liste colonnes) [options de stockage] ;
c M. Mathieu
101
Bases de données
2004-2005
Suppression d’un index :
– suppression de la contrainte PRIMARY KEY ou UNIQUE
– explicitement avec :
DROP INDEX nom index
Afin d’obtenir vraiment une accélération du temps d’accès il est souhaitable que
la table ait un taille importante et que les colonnes sur lesquelles on met l’index
se trouvent dans la partie WHERE des requêtes accédant à cette table.
Il est conseillé aussi de mettre dans la partie WHERE d’une requête des conditions qui portent sur la totalité des colonnes d’un index.
c M. Mathieu
102
Bases de données
2004-2005
Exemple : Même pour une table avec 14 enregistrements l’effet d’un index est
visible :
select *
from agent
where datee>’01-DEC-91’ ;
Elapsed : 00 :00 :00.06
CREATE INDEX AGENT IDX1
ON AGENT(DATEE) ;
Index created.
Elapsed : 00 :00 :00.89
select *
from agent
where datee > ’01-DEC-91’ ;
Elapsed : 00 :00 :00.01
c M. Mathieu
103
Bases de données
2004-2005
Chapitre 4 :
Notions d’administration de bases
de données
Application à Oracle
1.
2.
3.
4.
5.
Synonymes et catalogues de données
Gestion des utilisateurs
Gestion des connexions à la base
Gestion de la mémoire
Création et entretien d’une base
c M. Mathieu
104
Bases de données
2004-2005
Les synonymes
Un objet est désigné complétement comme :
[instance base.][proprietaire.]objet[@lien connexion]
l’objet étant table, vue, séquence, synonyme, procédure stocké, etc.
Afin d’éviter cette écriture pour désigner l’objet on utilise un synonyme définit
avec :
CREATE [PUBLIC] SYNONYM nom synonyme FOR désignation complète
Le synonyme pourra être utilisé dans des ordres SELECT, UPDATE, MODIFY,
DELETE, LOCK TABLE, GRANT, REVOKE.
c M. Mathieu
105
Bases de données
2004-2005
Exemple 1 :
CREATE SYNONYM market
FOR scott.market research ;
CREATE PUBLIC SYNONYM emp
FOR scott.emp@sales ;
Le premier synonyme fait référence à une table d’un autre utilisateur de la même
base et le deuxième à une table d’une base distante.
Exemple 2 :
CREATE
CREATE
CREATE
CREATE
CREATE
PUBLIC
PUBLIC
PUBLIC
PUBLIC
PUBLIC
SYNONYM
SYNONYM
SYNONYM
SYNONYM
SYNONYM
FOURNISSEUR FOR system.FOURNISSEUR ;
SERVICE FOR system.SERVICE ;
COMMANDE FOR system.COMMANDE ;
AGENT FOR system.AGENT ;
FOURNITURE FOR system.FOURNITURE ;
CREATE PUBLIC SYNONYM COLLECTIVITE FOR system.COLLECTIVITE ;
c M. Mathieu
106
Bases de données
2004-2005
Les catalogues des données
Oracle organise des méta-informations sur les objets contenus dans la base,
ces méta-informations se présentent sous forme des tables en mode lecture uniquement.
Les dictionnaires de données contiennent des informations sur :
– les définitions de tous les objets de la base (tables, vues, séquences, procédures, etc.)
– l’espace mémoire alloué et utilisé par chaque objet
– les valeurs pas défaut des colonnes
– les contraintes d’intégrité
– les utilisateurs et leurs rôles et privilèges
– d’autres informations
Toutes ces informations sont la propriété de l’utilisateur SYSTEM et sont gardées
dans un espace mémoire à part.
Elles sont mises à jour dès qu’un ordre LDD est éxecuté.
c M. Mathieu
107
Bases de données
2004-2005
Ces informations sont visibles avec les vues : USER *, ALL *, DBA *.
La liste de ces vues se trouve dans la table DICTIONARY ayant deux colonnes : TABLE NAME et COMMENTS.
Les vues suivantes sont accessibles par n’importe quel utilisateur :
ALL
ALL
ALL
ALL
ALL
ALL
ALL
ALL
ALL
ALL
ALL
ALL
CATALOG
COL COMMENTS
CONS COLUMNS
CONSTRAINTS
IND COLUMNS
INDEXES
OBJECTS
TAB COLUMNS
TAB COMMENTS
TABLES
USERS
VIEWS
Les informations sur vos propres tables créées se trouveront dans ALL TABLES
et USER TABLES, ALL CONSTRAINTS, ALL INDEXES.
c M. Mathieu
108
Bases de données
2004-2005
La gestion des utilisateurs
L’accès à une BD Oracle se fait en s’identifiant avec un nom utilisateur et un mot
de passe créés par l’administrateur (DBA).
Un utilisateur peut avoir des droits (privilèges) :
– pour créer des objets qui deviennent les siens
– pour utiliser des objets déjà créés
La création d’un utilisateur (par le DBA) :
CREATE USER nom utilisateur IDENTIFIED BY mot passe [options de stockage] ;
Le changement de mot de passe (par l’utilisateur ou par le DBA) :
ALTER USER nom utilisateur IDENTIFIED BY nouveau mot de passe ;
c M. Mathieu
109
Bases de données
2004-2005
La suppression d’un utilisateur :
DROP USER nom utilisateur [CASCADE] ;
L’affectation et la suppression d’un privilège (par le DBA ou le propriétaire de
l’objet) :
GRANT privilège TO nom utilisateur ;
REVOKE privilège TO nom utilisateur ;
Les privilèges :
– le droit de se connecter à la base (CONNECT),
– de créer des objets (CREATE ...),
– des consulter en lecture uniquement (SELECT ON objet)
– ou en modification des objets existants (MODIFY ON objet)
(les objets doivent être désignés de manière non-ambiguë)
c M. Mathieu
110
Bases de données
2004-2005
Exemple :
DROP USER LAURENT CASCADE ;
DROP USER OLIVIER CASCADE ;
/* Creation des utilisateurs profs */
create user LAURENT
identified by test default tablespace donneeopt
temporary tablespace temp quota 3M on donneeopt ;
create user OLIVIER identified by test default tablespace donneeopt
temporary tablespace temp quota 3M on donneeopt ;
grant connect to OLIVIER, LAURENT ;
grant create session, create table, create synonym, create view, create
sequence, create procedure to OLIVIER, LAURENT ;
grant create trigger to OLIVIER, LAURENT ;
GRANT
GRANT
GRANT
GRANT
GRANT
SELECT
SELECT
SELECT
SELECT
SELECT
ON
ON
ON
ON
ON
system.FOURNITURE TO OLIVIER, LAURENT ;
system.SERVICE TO OLIVIER, LAURENT ;
system.COMMANDE TO OLIVIER, LAURENT ;
system.AGENT TO OLIVIER, LAURENT ;
system.FOURNISSEUR TO OLIVIER, LAURENT ;
GRANT SELECT ON system.COLLECTIVITE TO OLIVIER, LAURENT ;
c M. Mathieu
111
Bases de données
2004-2005
La connexion à une base (Oracle)
L’accès en mode client-serveur se fait selon ce schéma :
Serveur Oracle
Net8
base1
base2
Poste client
Net8 - middleware qui s’appuie sur un protocole réseau (TCP/IP). (remplace
SQL*Net).
c M. Mathieu
112
Bases de données
2004-2005
Sur le poste client il faut :
– installer le logiciel Net8 d’Oracle
– le configurer afin de rendre la base accessible (via fichier TNSNAMES.ORA)
– installer soit des drivers JDBC pour programmer sous Java, soit un OBDC
afin d’accéder à la base depuis MS Acces, soit des logiciels Oracle ou de
développement.
Sur le serveur il faut configurer le fichier LISTNER.ORA.
L’emplacement des fichiers LISTNER.ORA et TNSNAMES.ORA est :
$ORACLE HOME/network/admin/.
c M. Mathieu
113
Bases de données
2004-2005
Oracle permet l’accès d’un utilisateur déjà connecté sur une base d’accéder
à une autre base Oracle (modulo le protocole réseau, la configuration, le logiciel
Net8).
Depuis la base courante on accède aux objets de la base distante en indiquant :
[instance base.][proprietaire.]objet@lien connexion
Le lien connexion comporte le nom symbolique de la base distante et se définit
avec :
CREATE [PUBLIC] [SHARED] DATABASE LINK lien connexion CONNECT TO
nom utilisateur IDENTIFIED BY mot de passe USING nom symbolique
Exemple :
CREATE SHARED PUBLIC DATABASE LINK sales.hq.acme.com
CONNECT TO scott IDENTIFIED BY tiger
AUTHENTICATED BY anupam IDENTIFIED BY bhide
USING ’sales’ ;
c M. Mathieu
114
Bases de données
2004-2005
Oracle - fonctionnement du serveur
Sous Unix un utilisateur à part pour Oracle.
Tout le travail du SGBD s’appuie sur le listener :
> lsnrctl [start | stop | status]
Une fois le listner lancé les instances de base se lancent avec :
> set ORACLE SID=ORCL
>svrmgrl
connect internal/oracle
startup
exit
>
c M. Mathieu
115
Bases de données
2004-2005
L’arrêt du serveur doit être précédé de l’arrêt de toutes les instances de la
base :
> set ORACLE SID=ORCL
>svrmgrl
connect internal/oracle
shutdown [immediate]
exit
>
c M. Mathieu
116
Bases de données
2004-2005
Création d’une base
–
–
–
–
création du fichier d’initialisation
création de la nouvelle instance de base
création des dictionnaires, tablespaces, procédures stockées ...
création des utilisateurs
c M. Mathieu
117
Bases de données
2004-2005
Importer / exporter des données
C’est possible sous Oracle avec les exécutables imp et exp qui se trouvent dans :
$ORACLE HOME/bin
Pur faire ces opérations il n’y a pas besoin de privilèges particuliers, à part les
droits sur les objets (tables) manipulés.
Ces opération sont utiles pour transférer les données d’une base à une autre
et aussi pour l’entretien d’une base.
c M. Mathieu
118
Bases de données
2004-2005
Chapitre 5 :
Introduction au PL/SQL
c M. Mathieu
119
Bases de données
2004-2005
PL/SQL :
–
–
–
–
–
une marque Oracle
un vrai langage complémentaire à SQL
un langage structuré et procédural
permet la création des fonctions / procédures stockées au niveau de la base
permet un accès plus fin à la base avec une gestion explicite des erreurs
La structure du code :
[DECLARE
-- declarations]
BEGIN
-- instructions
[EXCEPTION
--traitement exceptions]
END ;
c M. Mathieu
120
Bases de données
2004-2005
Un programme PL/SQL peut être :
– un bloc anonyme saisis directement sous SQL*Plus
– un bloc anonyme créé dans un fichier.pl et exécuté depuis SQL*PLUS :
SQL>@fichier.pl
– une fonction/procédure stockée, un package ou un trigger appelé depuis
des requêtes SQL, d’autre code PL/SQL ou déclenchés lors des certains
événements :
CREATE FUNCTION/PROCEDURE/TRIGGER nom code ...
AS ...
Exemples :
c M. Mathieu
121
Bases de données
2004-2005
Exemples : un code PL/SQL qui ne fait rien :
SQL> begin
2 null ;
3 end ;
4 /
SQL>run
Procedure PL/SQL terminee avec succes.
ou qui fait la même chose (pas grand chose) sous forme de procédure stockée :
SQL> create or replace procedure rien
2 is
3 begin
4 null ;
5 end ;
6 /
Procedure creee.
SQL> execute rien
Procedure PL/SQL terminee avec succes.
c M. Mathieu
122
Bases de données
2004-2005
SQL et PL/SQL
Des ordres SQL sont supportés dans PL/SQL et des fonctions écrites en
PL/SQL sont utilisables dans les ordres SQL.
Les instructions acceptées sous PL/SQL du langage de manipulation de données (LMD) et certaines instructions de gestion de transactions, à savoir :
–
–
–
–
INSERT, UPDATE, DELETE, SELECT,
COMMIT, ROLLBACK, SAVEPOINT,
LOCK TABLE,
SET TRANSACTION READ ONLY.
Les ordres du LDD ne sont pas supportés.
c M. Mathieu
123
Bases de données
2004-2005
Exemple :
DECLARE
qty on hand NUMBER(5) ;
BEGIN
SELECT quantity INTO qty on hand FROM inventory
WHERE product = ’TENNIS RACKET’
FOR UPDATE OF quantity ;
IF qty on hand > 0 THEN -- check quantity
UPDATE inventory SET quantity = quantity - 1
WHERE product = ’TENNIS RACKET’ ;
INSERT INTO purchase record
VALUES (’Tennis racket purchased’, SYSDATE) ;
ELSE
INSERT INTO purchase record
VALUES (’Out of tennis rackets’, SYSDATE) ;
END IF ;
COMMIT ;
EXCEPTION
WHEN no data found THEN
INSERT INTO error table
VALUES (’Product Tennis Rackets not found’) ;
END ;
/
c M. Mathieu
124
Bases de données
2004-2005
Déclaration de variables
Types de données :
–
–
–
–
–
–
–
BINARY INTEGER
Entier
NUMBER [(n,m)]
CHAR [(longueur max)]
Caractères long. fixe < 32767 o
LONG et LONG RAW
VARCHAR2 (longueur max)
Caractères long. Var < 32767 o
DATE
BOOLEAN
True,False ou Null
V Prix
NUMBER(4,2) := 11.5 ;
Affecter des valeurs :
V Prix
:= 15 ;
V PrixUnitaire
:= V Prix ;
c M. Mathieu
125
Bases de données
2004-2005
Déclaration de constantes :
credit limit CONSTANT REAL := 5000.00 ;
La lecture d’une variable (dans un bloc anonyme) est faite avec &variable
Déclaration de variables globales :
VARIABLE type nom variable ;
Une variable globale s’utilise avec :nom variable.
VARIABLE NUMBER(10,2) PRIX ;
...
BEGIN
:PRIX := CALCUL(...)
...
END
PRINT PRIX ;
c M. Mathieu
126
Bases de données
2004-2005
Exemple : Retrouver le type d’un fournisseur donné.
/* usage des variables internes */
set serveroutput on
DECLARE
VFNom Fournisseur.FNom%TYPE ;
VFType Fournisseur.FType%TYPE ;
Resultat CHAR(80) ;
BEGIN
SELECT FNom, FType
INTO VFNom, VFType
FROM Fournisseur
WHERE IdFourS = ’107’ ;
Resultat := ’Fournisseur : ’ || ’107’ || ’ Nom : ’ ||
VFNom ||’ Type : ’|| VFType ;
DBMS OUTPUT.PUT LINE(Resultat) ;
END ;
c M.
/ Mathieu
127
Bases de données
2004-2005
/* Avec des variables globales dont un prompteur */
ACCEPT PID PROMPT ’Numero fournisseur :’
VARIABLE Resultat CHAR (80) ;
DECLARE
VFNom Fournisseur.FNom%TYPE ;
VFType Fournisseur.FType%TYPE ;
BEGIN
SELECT FNom, FType
INTO VFNom, VFType
FROM Fournisseur
WHERE IdFourS = &PID ;
:Resultat := ’Fournisseur : ’ || &PID || ’ Nom : ’ || VFNom ||’
Type : ’|| VFType ;
END ;
/
print Resultat ;
c M. Mathieu
128
Bases de données
2004-2005
L’attribut %TYPE
permet de définir des variables à partir :
– d’une colonne de table
NomF
Agent.ANom%TYPE ;
– d’une autre variable
Prix
NUMBER (4,2) ;
Prix Max
v Prix%TYPE := 56 ;
L’attribut %ROWTYPE
permet de déclarer une variable à partir d’un ensemble de colonnes d’une table
(ou vue)
Agent enregistrement
c M. Mathieu
Agent%ROWTYPE ;
129
Bases de données
2004-2005
Deux types de données composites
– table PL/SQL :
– record PL/SQL : similaire aux structures d’enregistrement
Exemple RECORD PL/SQL
DECLARE
IS RECORD
TYPE Agt enreg
( AgtNom
VARCHAR(25),
AgtPrenom
VARCHAR(25) ) ;
Enreg Agent
c M. Mathieu
Agt enreg ;
130
Bases de données
2004-2005
Les expressions font intervenir les opérateurs connus, des constantes et des
fonctions Oracle ou définies par l’utilisateur à l’exception de fontions d’agrégat.
Les structures de contrôle :
– structures de test :
IF - THEN
IF - THEN - ELSE
IF - THEN - ELSIF
– structures itératives :
LOOP et EXIT
WHILE - LOOP
FOR - LOOP
– structure de contrôle séquentiel :
GOTO
NULL
c M. Mathieu
131
Bases de données
2004-2005
Exemples :
IF sales > quota THEN
compute bonus(empid) ;
UPDATE payroll SET pay = pay + bonus WHERE empno = emp id ;
END IF ;
IF trans type = ’CR’ THEN
UPDATE accounts SET balance = balance + credit WHERE ...
ELSE
IF new balance >= minimum balance THEN
UPDATE accounts SET balance = balance - debit WHERE ...
ELSE
RAISE insufficient funds ;
END IF ;
END IF ;
c M. Mathieu
132
Bases de données
2004-2005
WHILE total <= 25000 LOOP
...
SELECT sal INTO salary FROM emp WHERE ...
total := total + salary ;
END LOOP ;
FOR ctr IN 1..10 LOOP
IF NOT finished THEN
INSERT INTO ... VALUES (ctr, ...) ;
factor := ctr * 2 ; -- legal
ELSE
ctr := 10 ; -- illegal
END IF ;
-- legal
END LOOP ;
c M. Mathieu
133
Bases de données
2004-2005
DECLARE
done BOOLEAN ;
BEGIN
...
FOR i IN 1..50 LOOP
IF done THEN
GOTO end loop ;
END IF ;
...
< <end loop> > -NULL ;
END LOOP ; -- not an executable statement
END ;
c M. Mathieu
134
Bases de données
2004-2005
Curseurs
– curseurs implicits (SELECT)
– curseurs variable
c M. Mathieu
135
Bases de données
2004-2005
Exemple :
CURSOR c1 IS SELECT empno, ename, job, sal FROM emp
WHERE sal > 2000 ;
CURSOR c2 RETURN dept%ROWTYPE IS
SELECT * FROM dept WHERE deptno = 10 ;
CURSOR c3 (start date DATE) IS
SELECT empno, sal FROM emp WHERE hiredate > start date ;
c M. Mathieu
136
Bases de données
2004-2005
Exemple :
DECLARE
VFourT Id
VPrix
VIdCom
CURSOR
Commande.FourT Id%TYPE ;
Commande.Prix %TYPE ;
Commande.IdCom%TYPE ;
LCurseur1
SELECT
FROM
WHERE
IS
IdCom, Prix
Commande
FourT Id = &VFourT Id ;
BEGIN
...
OPEN
LOOP
LCurseur1 ;
...
FETCH LCurseur1 INTO VIdCom, VPrix ;
EXIT WHEN LCurseur1%ROWCOUNT>5
OR
%NOTFOUND ;
...
END LOOP ;
CLOSE
LCurseur1 ;
END ;
c M. Mathieu
137
Bases de données
2004-2005
Traitement d’errreurs
Liste des erreurs prédéfinies :
– ACCESS INTO NULL ORA-06530
– CURSOR ALREADY OPEN ORA-06511
– DUP VAL ON INDEX ORA-00001
– INVALID CURSOR ORA-01001
– INVALID NUMBER ORA-01722
– LOGIN DENIED ORA-01017
– NO DATA FOUND ORA-01403
– NOT LOGGED ON ORA-01012
c M. Mathieu
138
Bases de données
–
–
–
–
–
–
–
–
–
–
–
2004-2005
PROGRAM ERROR ORA-06501
ROWTYPE MISMATCH ORA-06504
SELF IS NULL ORA-30625
STORAGE ERROR ORA-06500
SUBSCRIPT BEYOND COUNT ORA-06533
SUBSCRIPT OUTSIDE LIMIT ORA-06532
SYS INVALID ROWID ORA-01410
TIMEOUT ON RESOURCE ORA-00051
TOO MANY ROWS ORA-01422
VALUE ERROR ORA-06502
ZERO DIVIDE ORA-01476
c M. Mathieu
139
Bases de données
2004-2005
Exemple :
DECLARE
pe ratio NUMBER(3,1) ;
BEGIN
DELETE FROM stats WHERE symbol = ’XYZ’ ;
SELECT price / NVL(earnings, 0) INTO pe ratio FROM stocks
WHERE symbol = ’XYZ’ ;
INSERT INTO stats (symbol, ratio) VALUES (’XYZ’, pe ratio) ;
EXCEPTION
WHEN ZERO DIVIDE THEN
...
WHEN NOT FOUND THEN
...
END ;
c M. Mathieu
140
Bases de données
2004-2005
DECLARE
pe ratio NUMBER(3,1) ;
BEGIN
DELETE FROM stats WHERE symbol = ’XYZ’ ;
BEGIN ---------- sub-block begins
SELECT price / NVL(earnings, 0) INTO pe ratio FROM stocks
WHERE symbol = ’XYZ’ ;
EXCEPTION
WHEN ZERO DIVIDE THEN
pe ratio := 0 ;
END ; ---------- sub-block ends
INSERT INTO stats (symbol, ratio) VALUES (’XYZ’, pe ratio) ;
EXCEPTION
WHEN OTHERS THEN
...
END ;
c M. Mathieu
141
Bases de données
2004-2005
DECLARE
v ename emp.ename%TYPE ;
v job emp.job%TYPE ;
BEGIN
SELECT ename, job
INTO v ename, v job
FROM emp
WHERE hiredate BETWEEN ’1-JAN-92’ AND ’31-DEC-92’ ;
...
EXCEPTION
WHEN no data found THEN
INSERT INTO error tab
VALUES (’Nobody in 92’) ;
WHEN too many rows THEN
INSERT INTO error tab
VALUES (’More then one person in 92’) ;
END ;
c M. Mathieu
142
Bases de données
2004-2005
Si d’autres erreurs ORACLE apparaissent, PL/SQL a deux variables qui permet
de récupérer l’erreur :
– SQLCODE - le numéro de l’erreur
– SQLERRM - le message complet
DECLARE
error-message CHAR (100) ;
error-code NUMBER ;
BEGIN
...
EXCEPTION
WHEN OTHERS
THEN
error message :=SUBSTR(SQLERM,1,100) ;
error code :=SQLCODE ;
INSERT INTO errors VALUES (error message, error code) ;
END ;
c M. Mathieu
143
Bases de données
2004-2005
L’utilisateur peut définir ses propres erreurs (des erreurs de traitement).
Il faut préciser explicitement l’exception (erreur) et lors de son apparition le code
est interrompu avec :
RAISE exception
Exemple :
DECLARE
out of stock EXCEPTION ;
....
IF quantity on hand = 0 THEN
RAISE out of stock ;
END IF
....
EXCEPTION
WHEN out of stock THEN ----traitement
c M. Mathieu
144
Bases de données
2004-2005
Exécution dynamique des requêtes SQL
Avec l’instruction :
EXECUTE IMMEDIATE dynamic string
Exemple 1 :
EXECUTE IMMEDIATE
< :my comm’
EXECUTE IMMEDIATE
’DELETE FROM emp WHERE sal > :my sal AND comm
’DELETE FROM emp WHERE sal > :s AND comm < :c’
ou :my sal est substitué avec sa valeur courante
c M. Mathieu
145
Bases de données
2004-2005
Exemple 2 :
DECLARE
TYPE EmpCurTyp IS REF CURSOR ;
EmpCurTyp ;
emp cv
my ename VARCHAR2(15) ;
NUMBER := 1000 ;
my sal
BEGIN
OPEN emp cv FOR
’SELECT ename, sal FROM emp
WHERE sal > :s’ USING my sal’ ;
...
END ;
c M. Mathieu
146
Bases de données
2004-2005
Exemple 3 :
CREATE PROCEDURE create dept (
deptno IN OUT NUMBER,
dname IN VARCHAR2,
loc
IN VARCHAR2) AS
BEGIN
deptno := deptno seq.NEXTVAL ;
INSERT INTO dept VALUES (deptno, dname, loc) ;
END ;
Appel de cette procedure par du code PL/SQL dynamique :
DECLARE
plsql block VARCHAR2(500) ;
new deptno NUMBER(2) ;
new dname VARCHAR2(14) := ’ADVERTISING’ ;
VARCHAR2(13) := ’NEW YORK’ ;
new loc
BEGIN
plsql block := ’BEGIN create dept( :a, :b, :c) ; END ;’ ;
EXECUTE IMMEDIATE plsql block
USING IN OUT new deptno, new dname, new loc ;
IF new deptno > 90 THEN ...
END ;-c M. Mathieu
147
Bases de données
2004-2005
Procédures, fonctions et triggers
Sont des programmes qui contiennent du SQL et du PL/SQL qui sont gardés
dans la base et exécutés sur demande explicite (procédures et fonctions) ou lors
des événements précis (triggers).
création : CREATE
modification du code : ALTER ou REPLACE
suppression : DROP
c M. Mathieu
148
Bases de données
2004-2005
Exemple 1 :
CREATE FUNCTION get bal(acc no IN NUMBER)
RETURN NUMBER
IS acc bal NUMBER(11,2) ;
BEGIN
SELECT balance
INTO acc bal
FROM accounts
WHERE account id = acc no ;
RETURN(acc bal) ;
END ;
Exemple 2 :
CREATE OR REPLACE PROCEDURE sam.credit (acc no IN NUMBER, amount IN
NUMBER) AS
BEGIN
UPDATE accounts
SET balance = balance + amount
WHERE account id = acc no ;
END ;
c M. Mathieu
149
Bases de données
2004-2005
Trigger = procédure stockée déclenchée lors de l’insertion, modification ou
suppression d’un enregistrement dans une table ou vue bien précise.
CREATE TRIGGER nom trigger [BEFORE | AFTER] évenement ON table vue
[FOR EACH ROW] [WHEN (condition)]
code PL/SQL
Exemple :
CREATE TRIGGER scott.emp permit changes
BEFORE
DELETE OR INSERT OR UPDATE
ON scott.emp
pl/sql block
CREATE TRIGGER scott.salary check
BEFORE
INSERT OR UPDATE OF sal, job ON scott.emp
FOR EACH ROW
WHEN (new.job <> ’PRESIDENT’)
pl/sql block
c M. Mathieu
150
Bases de données
2004-2005
Chapitre 6 :
JAVA et Oracle
c M. Mathieu
151
Bases de données
2004-2005
Trois façon d’interfacer JAVA (le langage) et Oracle (le SGBD) :
– JDBC - API de Java
– SQLJ - produit Oracle pour une Java VM qui supporte des requêtes statiques
– Fonctions stockées Java (PL/SQL)
– CORBA et autres ...
c M. Mathieu
152
Bases de données
c M. Mathieu
2004-2005
153
Bases de données
2004-2005
JDBC (Java DataBase Conectivity)
Oracle a créé donc des drivers (thin, oci7, oci8, ...) et amélioré considérablement le standard de JAVA.
Du point de vue de la programmation, les tâches s’enchaînent de la manière
suivante :
–
–
–
–
–
Création d’une instance d’un driver JDBC
Connexion à la base de données
Création d’un contexte de requête
Soumission d’une requête SQL au serveur SGBD
Collecte et traitement des résultats
c M. Mathieu
154
Bases de données
2004-2005
Packages java
Nous utiliserons les classes définies dans le package java.sql
DriverManager, Connection, Statement et ResultSet
Les résultats (les valeurs des champs de la base) s’obtiennent avec les méthodes get ? ? définies par l’interface ResultSet. Les méthodes existent sous deux
formes autorisant l’accès aux données soit par le nom du champ, soit par son index dans le ResultSet
:
– les chaînes de caractères :
String getString(int index) ; String getString(String name) ;
– les entiers et les réels :
int getInt(int index) ; float getFloat(int index) ;
int getInt(String name) ; float getFloat(String name) ;
– les dates :
Timestamp getDate(int index) ; Timestamp getDate(String name) ;
c M. Mathieu
155
Bases de données
2004-2005
Exemple :
import
import
import
import
java.sql.*
java.math.*
java.io.*
java.awt.*
class JdbcTest {
public static void main (String args []) throws SQLException {
// Load Oracle driver
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver()) ;
// Connect to the local database
Connection conn =
DriverManager.getConnection (
"jdbc :oracle :thin :@educusers :1521 :dbem",
"scott", "tiger") ;
c M. Mathieu
156
Bases de données
2004-2005
// Query the employee names
Statement stmt = conn.createStatement () ;
ResultSet rset = stmt.executeQuery ("SELECT ENAME FROM EMP") ;
// Print the name out
while (rset.next ())
System.out.println (rset.getString (1)) ;
// Close the result set, statement, and the connection
rset.close() ;
stmt.close() ;
conn.close() ;
}
}
c M. Mathieu
157
Bases de données
2004-2005
JDBC permet aussi des reqêtes dynamiques :
import java.sql.* ;
public static void projectsDue(boolean dueThisMonth) throws
SQLException {
// Get JDBC connection from previously initialized SQLJ
DefaultContext.
Connection conn = DefaultContext.getDefaultContext().getConnection() ;
String query = "SELECT name, start date + duration " +
"FROM projects WHERE start date + duration >=
sysdate" ;
if (dueThisMonth)
query += " AND to char(start date + duration, ’fmMonth’) " +
" = to char(sysdate, ’fmMonth’) " ;
c M. Mathieu
158
Bases de données
2004-2005
PreparedStatement pstmt = conn.prepareStatement(query) ;
ResultSet rs = pstmt.executeQuery() ;
while (rs.next()) {
System.out.println("Project : " + rs.getString(1) + " Deadline :
" +
rs.getDate(2)) ;
}
rs.close() ;
pstmt.close() ;
}
c M. Mathieu
159
Bases de données
2004-2005
Les traitements par lot (batch) sous JDBC :
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()) ;
conn = DriverManager.getConnection(
"jdbc :oracle :thin :@educusers :1521 :dbem", "scott",
"tiger") ;
stmt = conn.createStatement() ;
try { stmt.execute(
"create table mytest table (col1 number, col2
varchar2(20))") ;
} catch (Exception e1) {}
//
// Insert in a batch.
//
pstmt = conn.prepareStatement("insert into mytest table values
( ?, ?)") ;
c M. Mathieu
160
Bases de données
2004-2005
pstmt.setInt(1, 1) ;
pstmt.setString(2, "row 1") ;
pstmt.addBatch() ;
pstmt.setInt(1, 2) ;
pstmt.setString(2, "row 2") ;
pstmt.addBatch() ;
pstmt.executeBatch() ;
//
// Select and print results.
//
rset = stmt.executeQuery("select * from mytest table") ;
while (rset.next())
{
System.out.println(rset.getInt(1) + ", " + rset.getString(2)) ;
}
c M. Mathieu
161
Bases de données
2004-2005
SQLJ
Extension de Java qui fonctionne avec les même drivers que JDBC, le code est
fait pour requêtes statiques et exécution est plus rapide.
>sqlj code.sqlj // produit code.java
>javac code.java
Exemple :
void runExample() throws SQLException
{
System.out.println() ;
System.out.println( "Running the example." ) ;
System.out.println() ;
/* Reset the database for the demo application.
*/
#sql { DELETE FROM SALES } ;
c M. Mathieu
162
Bases de données
2004-2005
/* Insert a row into the cleared table.
*/
#sql
{
INSERT INTO SALES VALUES(
101,’Relativistic redshift recorder’,
TO DATE(’22-OCT-1997’,’dd-mon-yyyy’),
10999.95,
1,’John Smith’)
};
/* Insert another row in the table using bind variables.
*/
int
itemID = 1001 ;
String itemName = "Left-handed hammer" ;
double totalCost = 79.99 ;
Integer salesRepID = new Integer(358) ;
String salesRepName = "Jouni Seppanen" ;
Date
dateSold = new Date(97,11,6) ;
#sql { INSERT INTO SALES VALUES( :itemID, :itemName, :dateSold, :totalCo
:salesRepID, :salesRepName) } ;
c M. Mathieu
163
Bases de données
2004-2005
/* Instantiate and initialize the iterator.
**
** The iterator object is initialized using the result of a query.
** The query creates a new instance of the iterator and stores it
in
** the variable ’sales’ of type ’SalesRecs’. SQLJ translator has
** automatically declared the iterator so that it has methods for
** accessing the rows and columns of the result set.
*/
SalesRecs sales ;
#sql sales = { SELECT item number,item name,sales date,cost,
sales rep number,sales rep name FROM sales } ;
/* Print the result using the iterator.
**
** Note how the next row is accessed using method ’next()’, and how
** the columns can be accessed with methods that are named after
the
** actual database column names.
*/
c M. Mathieu
164
Bases de données
while( sales.next() )
{
System.out.println(
System.out.println(
System.out.println(
System.out.println(
System.out.println(
2004-2005
"ITEM ID : " + sales.item number() ) ;
"ITEM NAME : " + sales.item name() ) ;
"COST : " + sales.cost() ) ;
"SALES DATE : " + sales.sales date() ) ;
"SALES REP ID : " + sales.sales rep number()
);
System.out.println( "SALES REP NAME : " + sales.sales rep name()
);
System.out.println() ;
}
/* Close the iterator.
**
** Iterators should be closed when you no longer need them.
*/
sales.close() ;
}
c M. Mathieu
165
Bases de données
2004-2005
Fonctions stockées Java
Les fonctions Java se compilent d’abord afin d’obtenir des classes, ensuite ces
classes sont chargées dans la base avec :
loadjava -user nom/mot de passe@lien connexion nom classe.class
Une définition de type CREATE FUNCTION est nécessaire en indiquant qu’il
s’agit du code Java. L’appel se fait indiquant le nom et les paramètres.
Exemple 1 : soit la fonction très simple :
public class Test
{
public static String Message(){return "Bonjour !" ;}
}
Elle est compilée et chargée :
> javac Test.java
> loadjava -user mihaela/[email protected] java.class
c M. Mathieu
166
Bases de données
2004-2005
Sous SQL*Plus on définit la fonction et on l’appelle :
SQL>
2
3
4
CREATE FUNCTION TESTJAVA RETURN VARCHAR2
AS LANGUAGE JAVA
NAME ’Test.Message() return java.lang.String’ ;
/
Fonction créée.
SQL>
CALL TESTJAVA() INTO :R ;
Appel terminé.
SQL> PRINT :R ;
R
-------------------------------Bonjour !
SQL> SELECT TESTJAVA() FROM DUAL ;
TESTJAVA()
--------------------------------------------------------Bonjour !
c M. Mathieu
167
Bases de données
2004-2005
Exemple 2 : Soit la code java suivant :
public class Fibonacci
{
public static int fib (int n)
{
if (n==1 || n==2) return 1 ;
else
return fib(n-1) + fib(n - 2) ;
}
}
qui est compilé et chargé dans la base avec loadjava. Au niveau de SQL*Plus :
SQL>
SQL>
2
3
4
CREATE OR REPLACE FUNCTION fib (n NUMBER) RETURN NUMBER
AS LANGUAGE JAVA
NAME ’Fibonacci.fib(int) return int’ ;
/
Fonction créée.
c M. Mathieu
168
Bases de données
2004-2005
Son appel est préparé et effectué :
SQL> VARIABLE n NUMBER
SQL> VARIABLE f NUMBER
SQL> EXECUTE :n := 7 ;
Procédure PL/SQL terminée avec succès.
SQL> CALL fib( :n) INTO :f ;
Appel terminé.
SQL> PRINT :f ;
F
---------13
c M. Mathieu
169
Bases de données
2004-2005
Chapitre 7 :
Programmation C et Oracle
c M. Mathieu
170
Bases de données
2004-2005
Oracle offre la possibilité d’écrire des programmes en langages de haut niveau
(FORTRAN, COBOL, C/C++) qui englobent des facilités pour le travail avec les
bases de données.
Pro*C/C++ est le précompilateur pour C/C++. Il permet de :
– écrire des programmes en C et C++
– exécuter à l’intérieur du programme n’importe quel type d’ordre SQL standard :
– création dynamique des tables : CREATE, ALTER, DROP
– manipulation des données : SELECT, INSERT, DELETE, UPDATE
– transactions : COMMIT, ROLLBACK
c M. Mathieu
171
Bases de données
2004-2005
–
–
–
–
–
–
–
inclure des blocs PL/SQL
traiter les erreurs
convertir les types internes de données Oracle en types de données C
utiliser des accès concurents
précompiler dynamiquement
créer et exécuter dynamiquement des ordres SQL
travailler avec les objets de l’utilisateur (fonction, procédures, triggers, vue,
etc)
– travailler avec des objets de grande taille
c M. Mathieu
172
Bases de données
2004-2005
Le schéma de construction de l’exécutable :
c M. Mathieu
173
Bases de données
2004-2005
Le précompilateur se trouve dans $ORACLE HOME/bin et transforme un programme avec extension .pc en programme .c ou .cpp.
Tout source .pc doit contenir au moins :
# include <sqlca.h>
Cette librairie se trouve dans le répertoire $ORACLE HOME/precomp/public/.
Attention ! ! les options de précompilation, compilation et édition des liens sont
nombreuses et parfois indispensables.
c M. Mathieu
174
Bases de données
c M. Mathieu
2004-2005
175
Bases de données
2004-2005
La connexion doit se faire avant tout acces à la base.
La syntaxe est :
EXEC SQL CONNECT { :user IDENTIFIED BY :oldpswd | :usr psw }
[[ AT { dbname | :host variable }] USING :connect string ]
[ {ALTER AUTHORIZATION :newpswd | IN { SYSDBA | SYSOPER } MODE} ] ;
Une forme simple :
EXEC SQL CONNECT :username IDENTIFIED BY :password ;
ou username and password sont des variables hôte de type CHAR or VARCHAR.
On utilise aussi :
EXEC SQL CONNECT :usr pwd ;
ou usr pwd contient toute la chaîne de connexion, le mot de passe étant séparé
avec un ’/’.
c M. Mathieu
176
Bases de données
2004-2005
Exemple :
char *username = "[email protected]" ;
char *password = "TIGER" ;
...
EXEC SQL WHENEVER SQLERROR ...
EXEC SQL CONNECT :username IDENTIFIED BY :password ;
Plusieurs connexion simultanées à des bases de données sont possibles.
c M. Mathieu
177
Bases de données
2004-2005
Les ordres SQL embarqués sont sous forme :
EXEC SQL <ordre SQL standard> ;
(une clause INTO peut apparaître pour SELECT ou FETCH).
Un bloc PL/SQL embarqué est défini avec :
EXEC SQL EXECUTE
<bloc PL/SQL>
c M. Mathieu
178
Bases de données
2004-2005
Les variables hôte :
– sont déclarées en début de programme dans un bloc compris entre :
BEGIN DECLARE SECTION
....
END DECLARE SECTION
– sont utilisées pour manipuler les données
– sont utilisables comme des variables C.
Exemple :
EXEC SQL BEGIN DECLARE SECTION ;
VARCHAR nom[50] ;
int sal, emp no ;
EXEC SQL END DECLARE SECTION ;
Usage :
emp no = 2000 ;
EXEC SQL SELECT anom, salaire
INTO :nom, :sal
FROM agent
WHERE id agent = :emp no ;
c M. Mathieu
179
Bases de données
2004-2005
Usage illégal :
emp no = 2000 ;
strcpy(ma table, “agent”) ;
EXEC SQL SELECT anom, salaire
INTO :nom, :sal
FROM :ma table
WHERE id agent = :emp no ;
Dans des ordres SQL le nom des objets doit être explicite !
c M. Mathieu
180
Bases de données
2004-2005
Un curseur identifie une ligne retournée d’une table dans une requête. Les instructions :
DECLARE CURSOR
OPEN
FETCH
CLOSE
permettent de déclarer, ouvrir, utiliser et fermer un curseur.
Un curseur doit avoir un nom unique et être déclaré une seule fois.
c M. Mathieu
181
Bases de données
2004-2005
Exemple :
/* Declare the cursor. All static SQL explicit cursors
* contain SELECT commands. ’salespeople’ is a SQL identifier,
* not a (C) host variable.
*/
EXEC SQL DECLARE salespeople CURSOR FOR
SELECT ENAME, SAL, COMM
FROM EMP
WHERE JOB LIKE ’SALES%’ ;
/* Open the cursor. */
EXEC SQL OPEN salespeople ;
/* Get ready to print results. */
printf("\n\nThe company’s salespeople are--\n\n") ;
printf("Salesperson Salary Commission\n") ;
printf("----------- ------ ----------\n") ;
c M. Mathieu
182
Bases de données
2004-2005
/* Loop, fetching all salesperson’s statistics.
* Cause the program to break the loop when no more
* data can be retrieved on the cursor.
*/
EXEC SQL WHENEVER NOT FOUND DO break ;
for ( ; ;)
{
EXEC SQL FETCH salespeople INTO :emp rec ptr ;
printf("%s %9.2f %12.2f\n", emp rec ptr->emp name,
emp rec ptr->salary, emp rec ptr->commission) ;
}
/* Close the cursor. */
EXEC SQL CLOSE salespeople ;
c M. Mathieu
183