v ues n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
V ues PowerPoint Presentation
Download Presentation
V ues

Loading in 2 Seconds...

play fullscreen
1 / 51

V ues - PowerPoint PPT Presentation


  • 63 Views
  • Uploaded on

V ues. Vues. Table deriveé des tables existants (table de base) Accès adapte a besoin d'application (couche externe) Stockage des requêtes Protection des donneés Utilization Recherche comme table de base MAJ largement limiteé. CREATE VIEW.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'V ues' - piper


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide2
Vues
  • Table deriveé des tables existants (table de base)
    • Accès adapte a besoin d'application (couche externe)
    • Stockage des requêtes
    • Protection des donneés
  • Utilization
    • Recherche comme table de base
    • MAJ largement limiteé
create view
CREATE VIEW

CREATE VIEWcapitale_olympique(année, pays, capitale)

ASSELECT année, JO.pays, capitale

FROM JO, Pays

WHERE JO.pays = Pays.nom

utilisation des vues
Utilisation des Vues
  • SELECTDISTINCT capitale FROMcapitale_olympique ;
  • UPDATE capitale_olympiqueSET capitale = 'Berlin' WHERE pays = 'RDA';
maj des vues
MAJ des vues
  • Les modifications sont possibles si la requête ne contient pas de
    • Jointure
    • Opérateur d’agrégation
    • Attribut calculé dans le SELECT
    • UNION, INTERSECT, DIFFERENCE, DISTINCT, GROUP BY, ORDER BY
exemple
Exemple
  • CREATE VIEW pays_europenneAS SELECT * FROM PaysWHERE continent = 'Europe' ;
  • UPDATE pays_europenneSET capitale = 'Berlin' WHERE nom = 'RDA';

Le changement est propagé vers la table de base!

exemple maj impossible

DELETE ?

Exemple – MAJ impossible
  • CREATE VIEW comparaison AS SELECT P1.nom, P1.population, P2.nom, P2.populationFROM Pays P1, Pays P2WHERE P1.population > P2.population ;

Il n'existe aucune possibilité d‘éliminer seulementce tuple et de maintenir la consistance avec les tables de base

vue materialis e
Vue Materialisée
  • Normalement, seulement la définition de la vue est stockée
  • Vue materialisée
    • Crée une novelle table contenant les résultats de la requête utilisée dans la définition de la vue
    • Si les tables de base changent: MAJ de la vue matérialisée

CREATE MATERIALIZED VIEW capitale_olympique(année, pays, capitale)REFRESH FAST ON COMMITASSELECT année, JO.pays, capitale FROM JO, Pays WHERE JO.pays = Pays.nom ;

commandes sur les vues
Commandes sur les vues
  • CREATE [OR REPLACE] VIEW nom_vue [ ( nv_nom_col)*] AS requête [WITH READ ONLY] [WITH CHECK OPTION] ;
    • OR REPLACE: remplace une vue existante
    • WITH READ ONLY: interdit les MAJ
    • WITH CHECK OPTION: insertions et modifications seulement si le tuple résultant est sélectionné par la vue
  • DROP VIEW nom_vue;
  • RENAME nom_vue TO nv_nom;
  • ALTER VIEW nom_vue COMPILE;
    • Recalcule une vue
contraintes d int grit
Contraintes d'intégrité
  • Plusieurs possibilités pour les maintenir
    • Définition des tables
    • Assertions
    • Triggers
    • Vues
rappel
Rappel
  • Contrainte sur une colonne (contrainte-col)
    • [NOT] NULL
    • UNIQUE
    • PRIMARY KEY
  • Contraintes sur une table (contrainte-table)
    • UNIQUE (nom-col)*
    • PRIMARY KEY (nom-col)*
    • FOREIGN KEY (nom-col)* REFERENCES nom_table [(nom-col)*]
identifiant externe
Identifiant externe
  • Maintenir l’intégrité référentielle
    • Lors de suppressions ou modifications d'un tuple dont l'identifiant est référencé par un identifiant externe
  • Exemple de problème
    • Pays(nom, capitale, population, surface)JO(lieu, pays, année)
    • Suppression d'un tuple de Pays:DELETE FROM Pays WHERE nom='Grece';
maintenir integrite referentielle
Maintenir integrite referentielle
  • Possibilités
    • Interdire l'opération
    • Mettre les valeurs de l'identifiant externe à NULL ou à une valeur défaut
    • En cas du suppression: éliminer aussi le tuple avec l'identifiant externe
    • En cas du modification: modifier également la valeur de l'identifiant externe
exemple1
Exemple

CREATE TABLE JO

( année DECIMAL(4,0), lieu VARCHAR(15),

pays VARCHAR(20), PRIMARY KEY (année),

FOREIGN KEY (pays) REFERENCES Pays (nom)

ON DELETE SET NULL

ON UPDATE CASCADE

);

contraintes complexes
Contraintes complexes
  • Maintenir les conditions logique sur les tuples
  • Exemple
    • Pays(nom, capitale, population, surface)JO(lieu, pays, année)
    • JO.année  1896
    • 3 jeux olympiques au maximum dans le même pays
assertion simple
Assertion simple
  • Spécification de la condition pendant la définition de la table
    • La condition est contrôlée avec chaque modification ou insertion
  • Exemple

CREATE TABLE JO

( année DECIMAL(4,0), lieu VARCHAR(15),

pays VARCHAR(20), PRIMARY KEY (année),

FOREIGN KEY (pays) REFERENCES Pays (nom)

ON DELETE SET NULL

ON UPDATE CASCADE

CHECK(année  1896) );

assertion avec requ te
Assertion avec requête

CREATE TABLE JO

( année DECIMAL(4,0), lieu VARCHAR(15),

pays VARCHAR(20), PRIMARY KEY (année),

FOREIGN KEY (pays) REFERENCES Pays (nom)

ON DELETE SET NULL

ON UPDATE CASCADE,

CHECK(année  1896)

CHECK(3  ALL (SELECT COUNT(*) FROM JO

GROUP BY pays) );

assertion globale
Assertion globale
  • Condition sur plusiers tables
    • Definition independant d'une table
  • Exemple: deux jeux olympiques consécutifs ne se déroulent pas sur le même continent
  • CREATE ASSERTION JO_consecutive CHECKNOT EXISTS (SELECT * FROM JO JO1,JO JO2, Pays P1, Pays P2WHERE JO1.pays=P1.nom AND JO2.pays=P2.nom AND JO1.année=JO2.année+4 AND P1.continent=P2.continent);

Les assertions complexes sont très coûteuses !

trigger
Trigger
  • Concept de base de données active
  • Exécuter une opération suite à un changement survenu dans la base de données
    • Maintenir les contraintes d'intégrité
    • Logging et auditing des changements
    • Calculer des valeurs dérivées
    • Maintenir des règles de business
    • Maintenir des données répliquées et des vues complexes materialisées etc.
ev nement condition action
Evénement – Condition - Action
  • Un trigger est activé par un événement
    • Insertion, suppression ou modification sur une table
  • Si le trigger est activé, une condition est évaluée
    • Prédicat ou requête
    • Une requête est vraie si le résultat n'est pas vide
  • Si la condition est vraie l'action est exécutée
    • Insertion, suppression ou modification de la base de données (ou programme externe)
exemple2

événement

condition

action

Exemple
  • CREATE TRIGGER MAJ_Pays AFTER UPDATE population ON Pays FOR EACH ROW WHEN surface > 0 BEGIN UPDATE Pays SET pop_avg = population/surface; END;
ex cution d un trigger
Exécution d'un trigger
  • Avant, après ou au lieu de l'événement
    • AFTER | BEFORE | INSTEAD
  • Invocation de l'événement une fois par table ou pour tous les tuples affectés
    • [FOR EACH ROW]
  • Association avec une transaction
    • A la fin de la transaction en cours
    • Transaction séparée
r f rences aux valeurs modifi es
Références aux valeurs modifiées
  • CREATE TRIGGER MAJ_Log AFTER UPDATE ON PaysFOR EACH ROW WHEN (new.surface <> old.surface) BEGIN INSERT INTO Pays_log (nom, surface) VALUES (:new.nom, 'surface_changée'); END;
syntaxe des triggers sql 1999
Syntaxe des triggers (SQL 1999)

CREATE TRIGGER [OR REPLACE] nom-du-trigger

BEFORE | AFTER | INSTEAD OF

INSERT | UPDATE | DELETE OF noms-d'attributs ON nom-table

[FOR EACH ROW]

WHEN (condition)

<bloc PL/SQL ou programme Java ou C >

attention
Attention
  • Les triggers sont très utiles et puissants
  • Mais: une utilisation prudente est nécessaire
  • Attention aux
    • Invocations de cascade de triggers
    • Utilisations excessives des ressources
    • Difficultés pour prévoir les conséquences
embedded sql but
Embedded SQL - but
  • Inclure des commandes SQL dans un programme (Java, C, Cobol, Ada, Fortran, Pascal, …)
    • compléter SQL avec la puissance d’expressivité d’un langage de programmation
    • Interactions avec l’utilisateur, systèmes périphériques, …
  • Exemples
    • calculs, requêtes récursives, conversions, …
principe d utilisation
Principe d’utilisation
  • Ecrire dans un LP quelconque un programme normal contenant en plus des ordres SQL
    • Le langage qui “accueille” les ordres SGBD:le langagehôte
    • Les ordres accueillis: ordresinclus
  • Gérer les éventuels problèmes de communication entre le programme et le SGBD
    • Transmission des données entre le langage hôte et SQL
ordres inclus
Ordres inclus
  • Inclusion
    • Syntaxe dépend du langage hôte
    • En général: EXEC SQL <expression SQL> END-EXEC
    • En C: EXEC SQL <expression SQL> ;
    • En Java: #SQL { <expression SQL> };
  • Exécution
    • Précompilateur SQL détecte l’expressions SQL incluse
    • remplacée par un appel à une fonction de la bibliothèque de communication SGBD - langage hôte
    • transmise au SGBD lors de l’exécution du programme par cette fonction
transmission des donn es
Transmission des données
  • Echange entre le SGBD et le programme réalisé au travers d’une zone de communication
    • Zone de communication = ensemble de variables
  • Variables d’échange utilisateur (hôtes)
    • données (insertions, interrogation)
    • information du programme pour le SGBD
  • Variables spéciales SGBD
    • informations du SGBD pour le programme
exemple requ te langage h te c

Transmission de données

Exemple – requête (langage hôte C)

EXEC SQL BEGIN DECLARE SECTION;

char titre[15]; /* titre obtenu par un utilisateur */

real sal; /* salaire */

EXEC SQL END DECLARE SECTION;

/* Code (omis) pour obtenir un titre */

EXEC SQL SELECT Salaire

INTO:sal

FROM Compensation

WHERE Titre = :titre;

/* Code (omis) pour imprimer le resultat */

Déclaration

des

variables

communes

transmission d un ensemble de donn es
Transmission d’un ensemble de données
  • Le résultat d'une requête est une ensemble de données
    • Le langage hôte ne connaît pas le concept d'ensemble
    • Incompatibilité des langages ("Language impendance mismatch")
  • Tampon (Cursor)
    • zone mémoire nommée du programme à laquelle une requête est associée
    • taille dynamique réglée à l’exécution
    • sert à contenir les n-uplets résultant de requête
tampon

Avant 1. tuple

1. tuple

.

.

.

dernier tuple

Apres dernier tuple

Tampon
  • Principe d’utilisation
    • Déclaration du tampon (DECLARE)
    • Remplissage du tampon en une seule fois par exécution de la requête (OPEN)
    • Récupération des n-uplets du tampon un par un (FETCH)
    • Libération du tampon (CLOSE)
utilisation d une tampon
Utilisation d'une tampon

EXEC SQL BEGIN DECLARE SECTION;

<Déclaration des variables communes>

EXEC SQL END DECLARE SECTION;

EXEC SQL DECLARE <cursor-name> [options] CURSOR FOR <query> [options];

EXEC SQL OPEN <cursor-name> ;

while(condition) {

EXEC SQL FETCH FROM <cursor-name> INTO <shared-variable(s)> ;

if(tuple existe) traiter le tuple else break }

EXEC SQL CLOSE <cursor-name> ;

exemple3
Exemple

EXEC SQL DECLARE tamp1 CURSOR FOR

SELECT nom, prenom

FROM Etudiant

WHERE cle = :cle;

EXEC SQL OPEN tamp1

EXEC SQL FETCH FROM tamp1 INTO :nom, :prénom;

EXEC SQL CLOSE tamp1;

options declaration
Options - Declaration

EXEC SQL DECLARE <cursor-name>[INSENSITIVE][SCROLL] CURSOR FOR <query>[ORDER BY <attribute(s)>][FOR READ ONLY];

  • INSENSITIVE
    • le tampon est insensible aux changements dans la relation durant l’ouverture
  • SCROLL
    • utilisation des options FETCH
  • ORDER BY
    • trie l'ordre des tuples
  • FOR READ ONLY
    • l'accès ne change pas le contenu des tuples
options acc s
Options - Accés

EXEC SQL FETCH [FROM] [ NEXT | PRIOR | FIRST | LAST | RELATIVE[+|-]n | ABSOLUTE[+|-]n ]<cursor-name>

[INTO <var1>,…, <varn>]

termination des sql statements
Termination des SQL statements
  • Unité de travail (= transaction) commence avec le premier ordre SQL ou avec COMMIT/ROLLBACK
  • COMMIT
    • Si on veut rendre le résultat de la transaction permanent
    • Syntaxe: EXEC SQL COMMIT;
  • ROLLBACK
    • Si on veut rejeter le résultat de la transaction
    • Syntax : EXEC SQL ROLLBACK;
gestion des exceptions whenever
Gestion des exceptions : WHENEVER
  • Gestion automatique des erreurs et warnings
    • teste tous les évènements qui suivent
    • En général, un WHENEVER avant le premier ordre SQL exécutable
  • Syntaxe EXEC SQL WHENEVER <évènement> <action>;
whenever
WHENEVER
  • Evénement
    • SQLERROR, SQLWARNING
    • NOT FOUND : si pas de n-uplet trouvé
  • Action
    • CONTINUE, STOP (Rollback)
    • GOTO branch (à éviter), DO f()
    • DO BREAK et DO CONTINUE (à utiliser pour des boucles)
exemple mise a jour
Exemple – mise a jour

#include <stdio.h>

EXEC SQL INCLUDE SQLCA;

main() {

EXEC SQL WHENEVER SQLERROR GOTO error:

EXEC SQL CONNECT TO Company;

EXEC SQL BEGIN DECLARE SECTION;

int pno1, pno2; /* two project numbers */

int amount; /* amount to be transferred */

EXEC SQL END DECLARE SECTION;

/* Code (omitted) to read the project numbers and amount */

EXEC SQL UPDATE Project

SET Budget = Budget + :amount

WHERE Pno = :pno2;

EXEC SQL UPDATE Project

SET Budget = Budget - :amount

WHERE Pno = :pno1;

EXEC SQL COMMIT RELEASE;

return(0);

error:

printf(“update failed, sqlcode = %ld\n”, SQLCODE);

EXEC SQL ROLLBACK RELEASE;

return(-1);

}

sql dynamique
SQL dynamique
  • Si l'ordre SQL exact n'est pas connu lors du développement de l'application
    • SQL n’autorise pas les variables tables ou colonnes
  • Possibilité d’avoir le texte de l’ordre SQL entier contenu dans une variable
  • Syntaxe: EXEC SQL EXECUTE IMMEDIATE<Texte_ordre_SQL>;
exemple4
Exemple

EXEC SQL BEGIN DECLARE SECTION;

char req[] = “INSERT INTO EMP VALUES(‘E13’, ‘Jean Blanc’,...)”;

EXEC SQL END DECLARE SECTION;

EXEC SQL EXECUTE IMMEDIATE :req;

  • Limitations
    • :reqne peut pas retourner de résultat
    • :reqne peut pas contenir de paramètres
    • Chaque fois que :req est exécutée, elle est également compilée  overhead important
ex cution pr par e
Exécution préparée
  • Forme générale

EXEC SQL PREPARE stmt FROM :string

  • Règle
    • :stringpeut retourner des résultats d'une requête
    • :stringpeut contenir des paramètres
    • stmtn’est pas une variable hôte, mais un identificateur de l’ordre utilisé par le pré-processeur
exemple5
Exemple

EXEC SQL BEGIN DECLARE SECTION ;

char tup[] = “INSERT INTO EMP VALUES(‘E13’, ‘Jean Blanc’,...)”;

EXEC SQL END DECLARE SECTION;

EXEC SQL PREPARE S1 FROM :tup;

EXEC SQL EXECUTE S1;

...

EXEC SQL EXECUTE S1;

param trisation
Paramétrisation
  • Utiliser des caractères joker - ? – là où les chaînes de caractères peuvent apparaître
    • "INSERT INTO Emp VALUES(?, ?, ?, ?)"
    • pas à la place d’un nom de relation, de colonne, etc
  • Utiliser des variables hôtes dans la clause USING pour indiquer les valeurs des paramètres
    • EXEC SQL EXECUTE S1 USING :eno, :ename, :title, :city
    • USING ne peut pas être employé avec EXECUTE IMMEDIATE
exemple6
Exemple

EXEC SQL BEGIN DECLARE SECTION;

char tup[] = “INSERT INTO Emp VALUES (?,?,?,?);

char eno[3], enom[15], titre[10], ville[12];

EXEC SQL END DECLARE SECTION;

EXEC SQL PREPARE S1 FROM :tup;

/* obtenir les valeurs de :eno, etc. */

EXEC SQL EXECUTE S1 USING :eno, :ename, :titre, :ville;

tampons dynamiques
Tampons dynamiques
  • Si le résultatd'une requête est une ensemble, alors utiliser des tampons dynamiques
  • Définire des tampons dynamiques similaires à leurs contreparties statiques, mais utiliser
    • USING pour fournir les paramètres de la rêquete

EXEC SQL DECLARE <cursor-name> CURSOR FOR stmt;

EXEC SQL OPEN <cursor-name> USING :var1 [,…,:varn];

EXEC SQL FETCH <cursor-name> INTO :out1 [,…,:outk];

EXEC SQL CLOSE <cursor-name> ;

exemple7
Exemple

EXEC SQL BEGIN DECLARE SECTION;

char resp[10]; /* input: responsable */

char pno[3]; /* output: numéro du projet */

real avg-dur; /* output: durée moyenne */

char s[] = “SELECT Pno, AVG(Dur) FROM Travaille_sur WHERE Resp = ‘?’ GROUP BY Pno, Eno HAVING COUNT(*) > 2”;

EXEC SQL END DECLARE SECTION;

EXEC SQL PREPARE S1 FROM :s;

EXEC SQL DECLARE durée CURSOR FORS1;

/* obtenir la valeur du :resp */

EXEC SQL OPEN durée USING :resp;

EXEC SQL WHENEVER NOT FOUND DO BREAK;

while(1) {

EXEC SQL FETCH FROM durée INTO:pno, :avg-dur

traiter le tuple;

}

EXEC SQL CLOSE durée