IFT2821Base de données
Chapitre 6SQL
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
2
Plan du cours
Introduction Architecture Modèles de données Modèle relationnel Algèbre relationnelle SQL Conception Fonctions avancées Concepts avancées PL/SQL
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
3
Introduction Permet de retrouver et de manipuler les
données Dérivé de SEQUEL 2 (76) lui-même dérivé
de SQUARE (75) Proposé par IBM (82 puis 87) Première version normalisée SQL1 (ISO89) Deuxième version normalisée SQL2 (ISO92) SQL3 en cours de normalisation Sert de couche basse aux L4G (par
exemple Access)
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
4
Langage SQL
Partie LDD– Conceptuel : CREATE SCHEMA, TABLE,
…– Externe : CREATE VIEW, GRANT,…– Interne : CREATE INDEX, CLUSTER,…
Partie LMD– SELECT, INSERT, DELETE, UPDATE
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
5
Environnement SQL
Environnement SQL
Catalogue Ventes
Schéma Ventes.Exportation
TableVentes.Exportation.Article
View
Schéma c2.s1
Catalogue c2
Schéma Ventes.s2Table
c1.s2.t1Ventes.Exportation.ArticleSport
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
6
Environnement SQL (utilisateur)
Identificateur d ’utilisateur– authorizationID
Mécanisme d’authentification– e.g. mot de passe
Utilisateur possède privilèges Exemple
– CREATE user joe IDENTIFIED BY jpw– CONNECT joe/jpw
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
7
Environnement SQL (Schéma)
DATABASE ~ catalogue– une instance Oracle monte une
DATABASE à la fois Nom du SCHEMA
– = authorizationID du propriétaire (Oracle)– CREATE SCHEMA AUTHORIZATION blair
– CONNECT balir/cmoi@blair [AS sysDBA]
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
8
Environnement SQL (interprétation de requêtes)
Création d ’une table ou schéma Transmise à l ’interprète du LDD
– vérification– création de la table
• schéma stocké dans dictionnaire de données• allocation des structures physiques
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
9
Environnement SQL (Dictionnaire)
BD relationnelle : contient les méta-données d ’un CATALOG
DEFINITION_SCHEMA– tables
INFORMATION_SCHEMA– VIEWS sur les tables du
DEFINITION_SCHEMA
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
10
Environnement SQL (Dictionnaire) SCHEMATA
– les SCHEMA créés par CURRENT_USER DOMAINS
– les DOMAIN accessibles par CURRENT_USER ou PUBLIC TABLES
– les noms des tables accessibles par CURRENT_USER ou PUBLIC VIEWS
– les vues accessibles par CURRENT_USER ou PUBLIC COLUMNS
– les colonnes des TABLE accessibles par CURRENT_USER ou PUBLIC TABLE_CONSTRAINTS
– contraintes des TABLE créées par CURRENT_USER CHECK_CONSTRAINTS
– contraintes CHECK des TABLE créées par CURRENT_USER TABLE_PRIVILEGES
– privilèges accordés par CURRENT_USER, à CURRENT_USER, ou à PUBLIC
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
11
Environnement SQL (Dictionnaire)
SQL> SELECT Table_Name 2 FROM DICTIONARY 3 WHERE Table_Name like '%TABLE%' 4 /
TABLE_NAME------------------------------ALL_ALL_TABLESALL_NESTED_TABLESALL_OBJECT_TABLESALL_PART_TABLESALL_TABLESALL_UPDATABLE_COLUMNSUSER_ALL_TABLESUSER_NESTED_TABLESUSER_OBJECT_TABLESUSER_PART_TABLESUSER_QUEUE_TABLESUSER_TABLESUSER_TABLESPACESUSER_UPDATABLE_COLUMNSTABLE_PRIVILEGES
15 ligne(s) sélectionnée(s).
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
12
Type SQL
Numérique exact– INTEGER (ou INT) :2,3,5,..,299,.. – SMALLINT 2, 3, 459,…– NUMERIC(p, c) (ou DECIMAL(p, c) ou
DEC(p, c))• Nombre décimal avec p chiffres significatifs et c
chiffres après le point• Exemples : 2.5, 456.342, 6
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
13
Type SQL
Numérique approximatif– REAL : Point flottant
• Exemples : 3.27E-4, 24E5
– DOUBLE PRECISION : Point flottant à double précision
• Exemples : 3.27265378426E-4, 24E12
– FLOAT(n) :Point flottant (précision minimale est de n chiffres pour la mantisse)
• Exemples : 3.27E-4, 24E5
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
14
Type SQL
Chaîne de caractères– CHARACTER(n) (ou CHAR(n)) :Chaîne de
caractère de taille fixe égale à n
• Exemples : ’Adresse', 'Paul LeGrand ’ – CHARACTER VARYING (n) (ou VARCHAR(n)) :
Taille variable (max de n caractères)
– NATIONAL CHARACTER(n) :Ensemble de caractères alternatif spécifique à l'implémentation
– NATIONAL CHARACTER VARYING(n) :Taille variable
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
15
Type SQL
Date et temps – DATE : année (quatre chiffres), mois (2 chiffres) et jour
(2 chiffres)• Exemple : DATE '1998-08-25 ’• ALTER SESSION SET NLS_DATE_FORMAT =
‘ DD/MM/YYYY ’
– TIME[(p)] : heure (2 chiffres), minutes (2 chiffres), secondes (2 +p chiffres)
• Exemple : TIME '14:04:32.25 ’
– TIMESTAMP[(p)] : DATE + TIME• Exemple : TIMESTAMP '1998-08-25 14:04:32.25 ’
– INTERVAL : Représente un intervalle de temps • Exemple : INTERVAL '2' DAY
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
16
Type SQL
Booléen – BIT (n) : Vecteur de n bits.
• Exemples : B'00100110', X'9F ’
– BIT VARYING (n) : taille variable (max = n)– Données de grande taille
• BINARY LARGE OBJECT (n) (BLOB(n)) n : taille en octets (ex: 1024, 5K, 3M, 2G)
– Exemple : X ’52CF4 ’ (hexadecimal)
• CHARACTER LARGE OBJECT (n) (CLOB(n))
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
17
Type SQL (Oracle)
NUMBER(p,[c])– numérique exact; p entre 1 et 38, c doit être entre -84 et
+127 (défaut, c =0)
VARCHAR2(n) : n 4000 RAW(n) :
– Binaire de taille n octets (n 2000) LONG(n) :
– Chaîne de caractères de taille variable (n 2G). Maximum une colonne LONG par table
LONG RAW(n)– Binaire de taille variable (n 2G). Maximum une colonne
de type LONG RAW par table
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
18
Type SQL (Oracle)
ROWID : identifiant de ligne composé de
– identificateur de fichier– identificateur de bloc relatif au fichier– identificateur de ligne relatif au bloc
Conversions implicites Type SQL2 Type OracleCHARACTER (n), CHAR (n) CHAR (n)NUMERIC (p,s), DECIMAL (p,s), DEC (p,s) NUMBER (p,s)INTEGER, INT, SMALLINT NUMBER (38)FLOAT (p) FLOAT (p)REAL FLOAT (63)DOUBLE PRECISION FLOAT (126)VARCHAR(n), CHARACTER VARYING(n) VARCHAR2 (n)
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
19
Définition des données
Domaine– ExempleCREATE DOMAIN SSS_TYPE AS CHAR(9) (sql2)
CREATE TYPE COULEUR AS OBJECT (Oracle)
nomCouleur CHAR(6) DEFAULT 'vert' CONSTRAINT COULEUR_VALIDE CHECK (VALUE IN 'rouge', 'blanc', 'vert', 'bleu', 'noir');
– Destruction d’un domaineDROP TYPE domaine [RESTRICT | CASCADE]
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
20
Définition des données
Définition d’une table (1)CREATE TABLE <table name>
(<column name> <column type> [<attribute constraint>]
{, <column name> <column type> [<attribute constraints>] } [<table constraint> {,<table constraint>}])
Contraintes d’intégrité sur une table– NOT NULL– UNIQUE ou PRIMARY KEY– FOREIGN KEY– REFERENCES– CHECK
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
21
Définition des données
Définition d’une table (2)CREATE TABLE nomDeLaTable(spécificationDeColonne[,spécificationDeColonne]...[,spécificationDeContrainte]...)
nomColonne [ type|domaine] [DEFAULTvaleurDeDéfaut][NULL | NOT NULL] [UNIQUE | PRIMARY KEY][REFERENCES nomTable[listeColonnes]][[CONSTRAINT nomContrainte] CHECK(conditionSQL)]
[CONSTRAINT nomContrainte]{PRIMARY KEY listeColonnes|[ON DELETE {NO ACTION|CASCADE|SET NULL|SET DEFAULT}][ON UPDATE {NO ACTION|CASCADE|SET NULL|SET DEFAULT}]|CHECK ( conditionSQL)}
[[NOT] DEFERRABLE INITIALLY {DEFERRED|IMMEDIATE}]
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
22
Définition des données
Exemple
CREATE TABLE DEPARTMENT
(DNAME VARCHAR(15) NOT NULL,
DNUMBER INT,
MGRSSN CHAR(9) DEFAULT '888665555',
MGRSTARTDATE DATE,
CONSTRAINT DEPTPK PRIMARY KEY(DNUMBER),
CONSTRAINT DEPTSK UNIQUE (DNAME)
CONSTRAINT DEPTMGRFK FOREIGN KEY(MGRSSN) REFERENCES EMPLOYE(SSN) ON DELETE
CASCADE);
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
23
Définition des données Table
– Avec contraintes d ’intégrité
Exemple (SQL 86)CREATE TABLE COMMANDE (
NC NUMBER UNIQUE NOT NULL,
NV NUMBER NOT NULLQUANTITE NUMBER(6))
Exemple (SQL 89)CREATE TABLE COMMANDE (
NC NUMBER PRIMARY KEY, NV NUMBER NOT NULL REFERENCES VIN, QUANTITE NUMBER(6) CHECK(QUANTITE > 0))
--------------------------------------------------------------------------------------------------
NC NUMBER, PRIMARY KEY (NC), NV NUMBER NOT NULL, FOREIGN KEY (NV) REFERENCES VIN,
SQL 92
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
24
Définition des données
Modification du schéma d’une table (1)ALTER TABLE <table> ADD <attribut> <type>, <attribut> <type>, ...
– Exemple :
ALTER TABLE DEPARTEMENT ADD DATE_CREATION DATE ;
ALTER TABLE EMPLOYE DROP ADDRESS CASCADE; ALTER TABLE EMPLOYE DROP CONSTRAINT EMPSUPERFK
CASCADE;
Suppression d’une tableDROP TABLE <table>
– Exemple :DROP TABLE EMPLOYE ;
DROP TABLE DEPENDENT CASCADE;
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
25
Définition des données
Modification du schéma d’une table (2)
ALTER TABLE nomTable{ADD COLUMN spécificationColonneDROP COLUMN nomColonne [RESTRICT|CASCADE]|ADD spécificationContrainte|DROP nomContrainte [RESTRICT|CASCADE]|ALTER nomColonne SET DEFAULT valeurDéfaut|ALTER nomColonne DROP DEFAULT}
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
26
Accès aux données
Clause SELECTSELECT <attributs>
FROM <liste de tables>
[WHERE <condition>]
[GROUP BY <groupe(s) attribut(s) >]
[HAVING <condition de groupe>]
[ORDER BY <liste attributs>];
Exemple :SELECT BDATE, ADRESS
FROM EMPLOYE
WHERE FNAME='Jhon' AND LNAME='Smith';
BDATE ADRESS--------- -------------------------------------------------01-SEP-65 731Fondren Huston, TX
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
27
Accès aux données
Exemple– Donner les noms et adresses des employés?
SELECT BDATE, ADRESS
FROM EMPLOYEBDATE ADRESS
--------- ------------------------------------------------
10-OCT-37 450 Stone, Huston, TX
08-DEC-55 638 Voss, Huston , TX
20-JUN-41 291 Berry, Bellaire, TX
15-SEP-62 975FireOak, Humble,TX
31-JUL-72 5631Rice, Humble, TX
19-JUL-68 3321 Castle,Spring, TX
29-MAR-69 980, Dallas, Huston, TX
01-SEP-65 731Fondren Huston, TX
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
28
Accès aux données
Clause Where non spécifiée– Que fait la requête suivante ?
SELECT *
FROM EMPLOYE, DEPARTMENT ;
Clause Where mal spécifiée !!!– Que fait la requête suivante ?
SELECT LNAME, FNAME, DNUMDER
FROM EMPLOYE, DEPARTMENT
WHERE DNUMBER = 5;
Produit cartésien des tables EMPLOYE et DEPARTMENT
? ? ? ? ? ? ? ?
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
29
Accès aux données
Exemple– Donner les noms et adresses des employés du
département de recherche ?
SELECT FNAME, LNAME, ADRESS
FROM EMPLOYE, DEPARTMENT
WHERE DNAME='Research' AND DNUMBER=DNO;
FNAME LNAME ADRESS--------------- --------------- ------------------------------Franklin Wong 638 Voss, Huston , TXRamesh Narayan 975FireOak, Humble,TXJoyce English 5631Rice, Humble, TXJhon Smith 731Fondren Huston, TX
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
30
Accès aux données Attributs ambiguës
Exemple :– Donner les noms et adresses des employés qui travaillent au
département de recherche ? (Supposons que l’attribut DNO de la table EMPLOYE s’appelle DNUMBER)
SELECT FNAME, EMPLOYE.LNAME, ADRESS
FROM EMPLOYE, DEPARTMENT
WHERE DEPARTMENT.DNAME = 'Research' AND DEPARTMENT.DNUMBER=EMPLOYE.DNUMBER;
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
31
Accès aux données Attributs ambiguës
Exemple :– Donner le nom et prénom de chaque employé ainsi que le
nom et le prénom de son superviseur immédiat ?
SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME
FROM EMPLOYE E, EMPLOYE S
WHERE E.SUPERSSN=S.SSN;
FNAME LNAME FNAME LNAME--------------- --------------- --------------- ---------------Franklin Wong James BorgJennifer Wallace James BorgRamesh Narayan Franklin WongJoyce English Franklin WongAlicia Zelaya Jennifer WallaceAhmad Jabbar Jennifer WallaceJhon Smith Franklin Wong
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
32
Accès aux données Tables & Ensembles
Duplication des uplets :
– Duplication permise des uplets
SELECT ALL SALARY
FROM EMPLOYE ;
– Duplication non permise des uplets
SELECT DISTINCT SALARY
FROM EMPLOYE ;
SALARY---------- 55000 40000 43000 38000 25000 25000 25000 30000
SALARY--------- 25000 30000 38000 40000 43000 55000
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
33
Accès aux données Tables & Ensembles
Union: Donner la liste des projets dont Smith est implique comme employé ou comme directeur du département qui contrôle ces projets ?
(SELECT DISTINCT PNUMBER
FROM PROJECT, DEPARTMENT, EMPLOYE
WHERE DNUM = DNUMBER AND MGRSSN = SSN AND LNAME= 'Smith')
UNION
(SELECT DISTINCT PNUMBER
FROM PROJECT, WORKS_ON, EMPLOYE
WHERE PNUMBER = PNO AND ESSN = SSN AND LNAME = 'Smith');
PNUMBER------------- 1 2
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
34
Accès aux données
Fonctions de calcul– SUM, AVG, MAX, MIN
SELECT SUM (SALARY), MAX (SALARY), MIN (SALARY),
AVG (SALARY)
FROM EMPLOYE;
SUM(SALARY) MAX(SALARY) MIN(SALARY) AVG(SALARY)------------------- ------------------- -------------------- --------------------281000 55000 25000 35125
– Quel est le nombre des employés ?SELECT COUNT (*)
FROM EMPLOYE; COUNT(*)---------- 8
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
35
Accès aux données
Fonctions de calcul– Que fait la requête suivante ?
SELECT LNAME, FNAME
FROM EMPLOYE
WHERE (SELECT COUNT (*)
FROM DEPENDENT
WHERE SSN=ESSN) >= 2;
Retourne nom et prénom des employés qui ont deux dépendants
et plus.
LNAME FNAME--------------- ---------------Smith Jhon
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
36
Accès aux données
Fonctions d’agrégation– GROUP BY, HAVING
Pour chaque projet retrouver son nom, son numéro et le nombre des employés qui y ont participé ?
SELECT PNUMBER, PNAME, COUNT (*)
FROM PROJECT, WORKS_ON
WHERE PNUMBER=PNO
GROUP BY PNUMBER, PNAME; PNUMBER PNAME COUNT(*)--------------- ----------- ---------- 1 ProductX 2 2 ProductY 3 3 ProductZ 2 10 Computerization 3 20 Reorganization 3 30 Newbenefits 3
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
37
Accès aux données
Fonctions d’agrégation– GROUP BY, HAVING
Pour chaque projet qui implique plus de deux employés retrouver son nom, son numéro et le nombre d’employés qui y participent ?
SELECT PNUMBER, PNAME, COUNT (*)
FROM PROJECT, WORKS_ON
WHERE PNUMBER = PNO
GROUP BY PNUMBER, PNAME
HAVING COUNT (*) > 2; PNUMBER PNAME COUNT(*)--------------- ----------- ---------- 2 ProductY 3 10 Computerization 3 20 Reorganization 3 30 Newbenefits 3
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
38
Accès aux données Chaînes de caractères, opérateurs
arithmétiques et ordonnancement– LIKE, BETWEEN, ORDER BY
Retrouver tous les employés qui ont une adresse a Huston, Texas?
SELECT FNAME, LNAME
FROM EMPLOYE
WHERE ADRESS LIKE '%Huston, TX%';
Retrouver tous les employés qui sont nés durant les années
1950 ?
SELECT FNAME, LNAME
FROM EMPLOYE
WHERE BDATE LIKE'________5_';
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
39
Accès aux données Chaînes de caractères, opérateurs
arithmétiques et ordonnancement
Retrouver le salaire de chaque employé si on augmente de 10% les employés qui ont participé dans le projet ‘ProjectX’?
SELECT FNAME, LNAME, 1.1*SALARY
FROM EMPLOYE, WORKS_ON, PROJECT
WHERE SSN=ESSN AND PNO=PNUMBER AND PNAME='ProjectX';
– Que fait la requête suivante ?SELECT *
FROM EMPLOYE
WHERE (SALARY BETWEEN 30000 AND 40000) AND DNO = 5;
FNAME LNAME 1.1*SALARY--------------- --------------- ----------Jhon Smith 33000Joyce English 27500
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
40
Accès aux données Chaînes de caractères, opérateurs
arithmétiques et ordonnancement
Retrouver la liste des employés qui sont impliqués dans des projets ; Cette liste doit triée par département (descendant), nom et prénom(ascendant).
SELECT DNAME, LNAME, FNAME, PNAME
FROM DEPARTMENT, EMPLOYE, WORKS_ON, PROJECT
WHERE DNUMBER=DNO AND SSN=ESSN AND PNO=PNUMBER
ORDER BY DNAME DESC, LNAME ASC, FNAME ASC;
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
41
Accès aux données Chaînes de caractères, opérateurs
arithmétiques et ordonnancementDNAME LNAME FNAME PNAME--------------- --------------- --------------- ---------------Research English Joyce ProductXResearch English Joyce ProductYResearch Narayan Ramesh ProductZResearch Smith Jhon ProductXResearch Smith Jhon ProductYResearch Wong Franklin ProductYResearch Wong Franklin ProductZResearch Wong Franklin ComputerizationResearch Wong Franklin ReorganizationHeadquarters Borg James ReorganizationAdministration Jabbar Ahmad ComputerizationAdministration Jabbar Ahmad NewbenefitsAdministration Wallace Jennifer NewbenefitsAdministration Wallace Jennifer ReorganizationAdministration Zelaya Alicia NewbenefitsAdministration Zelaya Alicia Computerization
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
42
Accès aux données Sous requêtes et comparaison d’ensembles
– IN, SOME, ANY, ALL, EXIST, UNIQUE, NOT
Retrouver le NAS des employé qui ont travaillé dans les mêmes projets et les mêmes nombres d ’heures que John Smith (NAS : 123456789) ?
SELECT DISTINCT ESSN
FROM WORKS_ON
WHERE (PNO, HOURS) IN
(SELECT PNO, HOURS FROM WORKS_ON WHERE ESSN='123456789');
Qu’obtient-on si on remplacer IN par =SOME puis par =ANY ?
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
43
Accès aux données Sous requêtes et comparaison d’ensembles
– (IN, NOT) SOME, ANY, ALL, (EXIST, NOT)Retrouver les noms des employé qui gagnent un salaire supérieur a tous ceux qui travaillent dans le département de recherche?
SELECT LNAME, FNAME
FROM EMPLOYEE
WHERE SALARY > ALL
(SELECT SALARY FROM EMPLOYEE WHERE DNO=5);
LNAME FNAME--------------- ---------------Borg JamesWallace Jennifer
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
44
Accès aux données Sous requêtes et comparaison d’ensembles
– IN, SOME, ANY, ALL, EXIST, NOT
Retrouver les noms des employé qui n’ont pas de dépendant?
SELECT FNAME, LNAME
FROM EMPLOYE
WHERE NOT EXISTS
(SELECT *
FROM DEPENDENT, EMPLOYE
WHERE SSN=ESSN);
Retrouver les noms des managers qui ont plus d’un dépendant?
FNAME LNAME--------------- --------James BorgRamesh NarayanJoyce EnglishAlicia ZelayaAhmad Jabbar
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
45
Accès aux données Jointure de tables
– JOIN, NATURAL JOIN
Retrouver les noms et les adresses des employé qui travaillent dans le département de recherche ?
SELECT FNAME, LNAME, ADDRESS
FROM (EMPLOYE JOIN DEPARTMENT ON DNO=DNUMBER)
WHERE NAME=‘Research’;
SELECT FNAME, LNAME, ADDRESS
FROM (EMPLOYE NATURAL JOIN (DEPARTMENT AS DEPT (DNAME, DNO, MSSN, MSDATE)))
WHERE DNAME=‘Research;
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
46
Manipulation des données
InsertionINSERT INTO <table> [ (<liste_colonnes>)]
{VALUES (<liste_valeurs>) | <requete>}
INSERT INTO EMPLOYEE
VALUES ('Richard','Marini', '653298653', '30-dec-1962','98 Oak Forest,Katy,TX','M', 37000, '987654321', 4);
INSERT INTO EMPLOYE (FNAME, LNAME, DNO, SSN)
VALUES ('Richard', 'Marini', 4, '653298653');
INSERT INTO DEPTS_INFO (DEPT_NAME, NO_OF_EMPS, TOTAL_SAL)
SELECT DNAME, COUNT (*), SUM (SALARY)
FROM (DEPARTMENT JOIN EMPLOYE ON DNUMBER=DNO)
GROUP BY DNAME;
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
47
Manipulation des données Mise à jour
UPDATE <table>
SET {<nom_colonne> = <expression>}+
WHERE {<condition>}
UPDATE PROJECT
SET PLOCATION = ‘Bellaire’, DNUM = 5
WHERE PNUMBER=10;
UPDATE EMPLOYE SET SALARY = SALARY *1.1
WHERE DNO IN
(SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME = 'Research');
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
48
Manipulation des données
SuppressionDELETE FROM <table>
[WHERE {<condition> ]
DELETE FROM EMPLOYE
WHERE SSN=‘123456789’;
DELETE FROM EMPLOYE
WHERE DNO IN
(SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME='Research');
– Que fait la requête suivante ?DELETE FROM EMPLOYE;
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
49
Opérations ensemblistes Différence
Les employés qui n ’ont pas de dépendants.
select ssn as Nass from employe
minusselect essn from dependent;
Nass --------- 453453453 666884444 888665555 987987987 999887777
- Que fait la requête suivante ?select essn from dependent;minusselect ssn as Nass from employe
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
50
Opérations ensemblistes Différence
Les noms et prénoms des employés qui n’ont pas de dépendants.
select lname as prenom
from employe
where ssn in
(select ssn as Nass from employe
minus
select essn from dependent);
ou
select lname as prenom
from employe, (select ssn as Nass from employe
minus
select essn from dependent)
where ssn = Nass;
PRENOM-----------EnglishNarayan
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
51
Opérations ensemblistes
IntersectionLes employés qui ont des dépendants.
select ssn as Nass from employe
intersectselect essn from dependent;
Nass---------123456789
334455559
87654321
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
52
Opérations dérivées Division (1)
– Exemple : quel sont les employées qui ont participé au mêmes
projets que « John Smith »
=
WORKS_ON ESSN PNO123456789 1123456789 2666884444 3453453453 1453453453 2333445555 2333445555 3333445555 10333445555 20999887777 30999887777 10987987987 10987987987 30987654321 30987654321 20888665555 20
Smith_team ESSN123456789453453453
Smith_Projects PNO12
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
53
Opérations ensemblistes
Division (2)
select fname as Prenom, lname as Nom
from employe e
where not exists
(select pno from works_on
where essn = '123456789 ’
minus
select pno
from works_on where essn = e.ssn) ;
PRENOM NOM
--------------- ---------------
Joyce English
Jhon Smith
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
54
Opérations ensemblistes
Division (2 Ayoye !!!)
select fname as Prenom, lname as Nom
from employe
where not exists
(select pno
from works_on w1
where essn = '123456789 ’
and not exists
(select pno
from works_on w2
where essn = ssn and w1.pno = w2.pno)) ;
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
55
SQL intégré
Introduction
– SQL peut être intégré dans un langage hôte (C, COBOL, PL/1, PASCAL, JAVA, etc.)
– Étude du cas du langage C
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
56
SQL intégré
Principes– Tout instruction SQL commence par
l’expression EXEC SQL pour la distinguer des autres instructions du langage hôte
– Différents types d’instructions• déclarations• connexion• traitement
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
57
SQL intégré
Déclarations (variables de communication)– Elle se fait dans la DECLARE SECTION. Celle ci
commence par l'ordreEXEC SQL BEGIN DECLARE SECTION;
et se termine parEXEC SQL END DECLARE SECTION;
– ExempleEXEC SQL BEGIN DECLARE SECTION;
int pempno ;
char pname[11];
int pdeptno;
EXEC SQL END DECLARE SECTION;
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
58
SQL intégré
Déclarations (variables de communication)– Utilisation dans SQL
EXEC SQL SELECT DEPTO, ENAME
INTO :pdeptno, :pname FROM EMP
WHERE EMPNO = :pempno;
• Variables précédées de ":" pour les distinguer des noms des attributs
– Utilisation dans Cstrcpy(pname,"Martin") ;
– Les types possibles pour ces variables sont ceux compatibles avec ORACLE (entiers, réels, chaînes de caractères)
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
59
SQL intégré
Connexion– La connexion à une base ORACLE se fait par
l'ordre SQL :EXEC SQL CONNECT :username IDENTIFIED BY :password;
• username et password sont des variables déclarées dans la section déclaration
– Exemple EXEC SQL BEGIN DECLARE SECTION;
VARCHAR username[20];
VARCHAR password[20];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE sqlca.h;
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
60
SQL intégré
Connexion– Exemple (suite)
main()
{
strcpy(username,"login_oracle");
/* Copie du username*/
strcpy(password,"motdepasse_oracle");
/* Copie du mot de passe */
EXEC SQL CONNECT :username IDENTIFIED BY :password;
…
}
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
61
SQL intégré
Traitements– Mise à jour
• "mettre à jour le salaire dans la relation employé"EXEC SQL UPDATE EMP
SET SAL = :salaire
WHERE EMPNO=301;
– SuppressionEXEC SQL DELETE FROM EMP WHERE EMPNO = :empno;
– Création d ’une tableEXEC SQL CREATE TABLE EMP_TEST
(EMPNO NUMBER, ENAME CHAR(15), JOB CHAR(10));
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
62
SQL intégré
Traitements– Sélection, cas du INTO
• S ’applique quand le SELECT retourne un seul n-upletEXEC SQL SELECT job, sal
INTO :fonction, :salaire
FROM EMP
WHERE empno=301;
– Sélection, utilisation d’un curseur• S ’applique quand le SELECT retourne un ensemble
de n-uplets• Un curseur est une structure de données contenant
tous les n-uplets retournés par la commande SELECT• Cette structure se manipule comme un fichier
séquentiel
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
63
SQL intégré
Traitements– Association du curseur à un SELECT
EXEC SQL DECLARE C CURSOR FOR
SELECT job, salaire
FROM EMP;
– Ouverture du curseurEXEC SQL OPEN C;
• A l ’ouverture, le premier n-uplet est pointé
– Fermeture du curseurEXEC SQL CLOSE C;
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
64
SQL intégré
Traitements– Accès aux autres n-uplets de manière
séquentielle• Se fait par l ’instruction FETCH
EXEC SQL FETCH C INTO :fonction, :salaire;
• On ne peut pas reculer dans le curseur• Pour accéder de nouveau aux n-uplets, il faut fermer
et rouvrir le curseur
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
65
SQL intégré
Commandes dynamiques– Il est possible d’exécuter des commandes SQL
inconnues au moment de l‘écriture du programme
– Il existe pour cela quatre méthode• Commandes sauf SELECT sans variables (EXECUTE
IMMEDIATE)• Commandes sauf SELECT avec un nombre de
variables fixe (PREPARE, EXECUTE)• Commandes avec un nombre de variables variable
(PREPARE, DECLARE, OPEN, FETCH)• SELECT complètement dynamique
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
66
SQL intégré
Commandes dynamiques
– Commandes sans variables EXEC SQL EXECUTE IMMEDIATE :modif;
– Commandes avec un nombre de variables fixeEXEC SQL PREPARE S1 FROM :chaîne;
EXEC SQL EXECUTE S1 USING :variable1,:variable2,
...;
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
67
SQL intégré
Directives de traitement d'erreurEXEC SQL WHENEVER [SQLERROR /
SQLWARNING / NOT FOUND] [STOP / CONTINUE / GO TO étiquette];
– Ces directives correspondent donc à 3 événements ORACLE :
• SQLERROR : erreur ORACLE • SQLWARNING : "warning" ORACLE• NOT FOUND : curseur vide ou fini
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
68
SQL intégré
Directives de traitement d'erreurEXEC SQL WHENEVER [SQLERROR /
SQLWARNING / NOT FOUND] [STOP / CONTINUE / GO TO étiquette];
– Les actions possibles sont :• STOP : le programme se termine et la transaction est
« abortée »,• CONTINUE : le programme continue en séquence,• GO TO : le programme se branche à l'adresse indiquée.
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
69
SQL intégré
Directives de traitement d'erreur– La portée d'une directive WHENEVER va jusqu'à
la directive WHENEVER suivante (ou la fin de programme) dans l'ordre du texte source PRO*C (et non pas dans l'ordre d'exécution).
– L'erreur classique dans la manipulation des SQL WHENEVER est la suivante :
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
70
SQL intégréroutine a
{ ...
EXEC SQL WHENEVER ERROR GOTO toto;
...
toto : ...
}
routine b
{ ...
EXEC SQL INSERT ...
} /* donc rien est dit dans b pour SQL WHENEVER */
– Par conséquent, au sein de la routine b, on garde les dernières directives rencontrées, donc celles de la routine a. Or l'étiquette toto est locale à a et donc inconnue dans b.
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
71
SQL intégré
Solutions
– par exemple avoir systématiquement des étiquettes globales
– définir localement dans chaque routine les directives d'erreurs.
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
72
SQL intégré
Gestion des transactions – Une transaction est assimilée à une exécution
d'un programme. Le début de transaction est implicite (c'est le début de programme), et la fin est soit implicite
• (erreur non récupérée par un WHENEVER : annulation, ou fin de programme : validation)
• soit explicite. Les ordres de fin de transaction explicites sont :
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
73
SQL intégré
Gestion des transactions
EXEC SQL COMMIT WORK [RELEASE];
– Valide les mises à jour. L'option RELEASE désalloue toutes les ressources ORACLE et réalise la déconnexion de la base
EXEC SQL ROLLBACK WORK [RELEASE];
– Annule les mises à jour
IntroductionArchitectureModèles de donnéesModèle relationnelAlgèbre relationnelleSQLConceptionFonctions avancéesConcepts avancéesModèle des objetsBD à objets
74
SQL intégré
Gestion des transactions
– Pour éviter les problèmes de conflit entre le ROLLBACK et les directives WHENEVER, il est prudent d'utiliser le ROLLBACK comme suit :
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL WHENEVER SQLWARNING CONTINUE;
EXEC SQL ROLLBACK WORK;