6 Le langage SQL Structured Query Language n
- Slides: 116
6 Le langage SQL Structured Query Language n Standard établi pour SGBD relationnel n Partie LDD n – – – n Conceptuel : CREATE SCHEMA, TABLE, . . . Externe : CREATE VIEW, GRANT, … Interne : CREATE INDEX, CLUSTER, . . . Partie LMD – 13/09/2021 SELECT, INSERT, DELETE, UPDATE © Robert Godin. Tous droits réservés. 1
Quelques liens n Standard SQL: – – n n n http: //www. jcc. com/SQLPages/jccs_sql. htm http: //www. cssinfo. com/ncitsgate. html Tutoriel et liens: http: //www. contrib. andrew. cmu. edu/~shadow/sql. html Syntaxe SQL 2 : http: //www. contrib. andrew. cmu. edu/~shadow/sql 2 bnf. aug 92. txt http: //www. sql-zone. com/ 13/09/2021 © Robert Godin. Tous droits réservés. 2
Origine n IBM Research (San Jose) – – n SQUARE – – n développement du prototype System/R (Astrahan et al. , 1976) algèbre relationnelle ~ anglais (Boyce, Chamberlin, King & Hammer, 1975) SEQUEL – – 13/09/2021 Structured English QUEry Language (Chamberlin, Astrahan, Eswaran, Chamberlin, Griffiths & Lorie, 1976) © Robert Godin. Tous droits réservés. 3
Standard ANSI/ISO n SQL 86 – n SQL 89 (SQL, SQL 1) – n niveau minimal supporté SQL 92 (SQL 2) – – – n version préliminaire support accru de l ’intégrité trois niveaux : entrée (SQL 1+), intermédiaire, complet de plus en plus supporté SQL 99? (SQL 3) – – 13/09/2021 extension objet, TRIGGER, ROLE, SQL/PSM, . . . support très variable © Robert Godin. Tous droits réservés. 4
6. 1 Spécification schéma relationnel avec SQL (LDD SQL) n Niveau conceptuel – Schéma des tables (TABLE) n – CREATE TABLE. Domaines (DOMAIN) n SQL 2 – Contraintes d'intégrité n 13/09/2021 intermédiaire : CREATE DOMAIN PRIMARY KEY, FOREIGN KEY, UNIQUE KEY, CHECK, ASSERTION, TRIGGER © Robert Godin. Tous droits réservés. 5
Spécification schéma relationnel avec SQL (suite) n Niveau externe – Vues (VIEW) n – définie à partir d'autres tables (CREATE VIEW). Elle apparaît à l'utilisateur comme une table normale alors qu'elle est en réalité dérivée à partir d'autres tables normales ou virtuelles. Privilèges d'accès n GRANT n Niveau interne – 13/09/2021 non standardisé (e. g. CREATE INDEX) © Robert Godin. Tous droits réservés. 6
6. 1. 1 Environnement, catalogue, schéma et utilisateur SQL n Cluster – 13/09/2021 ensemble de catalogues accessibles à une session © Robert Godin. Tous droits réservés. 7
Dialecte Oracle n DATABASE ~ catalogue – n Nom du SCHEMA – n une instance Oracle monte une DATABASE à la fois = authorization. ID du propriétaire CLUSTER Oracle – – 13/09/2021 cluster de l ’environnement SQL 2 méthode d ’organisation de données par grappe © Robert Godin. Tous droits réservés. 8
6. 1. 2 Création du schéma d'une table en SQL (CREATE TABLE) n Forme simple n Transmise à l ’interprète du LDD – – vérification création de la table n schéma stocké dans dictionnaire de données n allocation des structures physiques – 13/09/2021 clause non standardisée pour organisation primaire © Robert Godin. Tous droits réservés. 9
Syntaxe générale du CREATE TABLE n Syntaxe de spécification. De. Colonne n Syntaxe de spécification. De. Contrainte 13/09/2021 © Robert Godin. Tous droits réservés. 10
6. 1. 3 Types SQL n Numérique – INTEGER (ou INT) n n – n Petit entier (précision non standardisée) Exemples : 2, 3, 459 NUMERIC(p, c) (ou DECIMAL(p, c) ou DEC(p, c)) n n 13/09/2021 Entier (précision non standardisée) Exemples : 2, 3, 459 SMALLINT n – exact Nombre décimal avec p chiffres significatifs (excluant le point) et c chiffres après le point Exemples : 2. 5, 456. 342, 6 © Robert Godin. Tous droits réservés. 11
Types SQL n Numérique – REAL n n – Point flottant (précision non standardisée) Exemples : 3. 27 E-4, 24 E 5 DOUBLE PRECISION n n – Point flottant à double précision (non standardisée) Exemples : 3. 27265378426 E-4, 24 E 12 FLOAT(n) n Point flottant – n 13/09/2021 approximatif précision minimale est de n chiffres pour la mantisse Exemples : 3. 27 E-4, 24 E 5 © Robert Godin. Tous droits réservés. 12
Types SQL n Chaîne de caractères (VARYING et NATIONAL : SQL 2 intermédiaire) – CHARACTER(n) (ou CHAR(n)) n n – CHARACTER VARYING (n) (ou VARCHAR(n)) n – Taille variable Création d ’ensembles de caractères alternatifs n n 13/09/2021 Ensemble de caractères alternatif spécifique à l'implémentation NATIONAL CHARACTER VARYING(n) n – Taille variable (max de n caractères) NATIONAL CHARACTER(n) n – Chaîne de caractère de taille fixe égale à n Exemples : 'G. Lemoyne-Allaire', 'Paul L''Heureux ’ CREATE CHARACTER SET COLLATION : relation d ’ordre des caractères © Robert Godin. Tous droits réservés. 13
Types SQL n Date et temps (SQL 2 intermédiaire; précision p: SQL 2 complet) – DATE n n – TIME[(p)] n n – n DATE + TIME Exemple : TIMESTAMP '1998 -08 -25 14: 04: 32. 25' INTERVAL n 13/09/2021 heure (2 chiffres), minutes (2 chiffres), secondes (2 +p chiffres) Exemple : TIME '14: 04: 32. 25' TIMESTAMP[(p)] n – année (quatre chiffres), mois (2 chiffres) et jour (2 chiffres) Exemple : DATE '1998 -08 -25' Représente un intervalle de temps. © Robert Godin. Tous droits réservés. 14
Types SQL n Booléen (SQL 2 complet) – BIT (n) n n – BIT VARYING (n) n n taille variable (max = n) Binaire long (SQL 3) – BINARY LARGE OBJECT (n) (BLOB(n)) n n n Vecteur de n bits. Exemples : B'00100110', X'9 F' n : taille en octets (ex: 1024, 5 K, 3 M, 2 G) Exemple : X ’ 52 CF 4 ’ (hexadecimal) Longue chaîne de caractère (SQL 3) – – 13/09/2021 CHARACTER LARGE OBJECT (n) (CLOB(n)) NATIONAL CHARACTER LARGE OBJECT (n) (NCLOB(n)) © Robert Godin. Tous droits réservés. 15
6. 1. 4 Suppression d’une table (DROP TABLE) n RESTRICT – rejet si élément dépendant existe n ex: n FOREIGN KEY CASCADE – 13/09/2021 supprime élément dépendant © Robert Godin. Tous droits réservés. 16
6. 1. 5 Modification du schéma de table (ALTER TABLE) n Syntaxe 13/09/2021 © Robert Godin. Tous droits réservés. 17
6. 1. 6 Le dictionnaire de données SQL (INFORMATION_SCHEMA) Normalisé en SQL 2 n BD relationnelle n – n DEFINITION_SCHEMA – n contient les méta-données d ’un CATALOG tables INFORMATION_SCHEMA – 13/09/2021 VIEWS sur les tables du DEFINITION_SCHEMA © Robert Godin. Tous droits réservés. 18
Exemples de VIEWS du INFORMATION_SCHEMA n SCHEMATA – n DOMAINS – n contraintes CHECK des TABLE créées par CURRENT_USER ASSERTIONS – n contraintes des TABLE créées par CURRENT_USER CHECK_CONSTRAINTS – n les colonnes des TABLE accessibles par CURRENT_USER ou PUBLIC TABLE_CONSTRAINTS – n les vues accessibles par CURRENT_USER ou PUBLIC COLUMNS – n les noms des tables accessibles par CURRENT_USER ou PUBLIC VIEWS – n les DOMAIN accessibles par CURRENT_USER ou PUBLIC TABLES – n les SCHEMA créés par CURRENT_USER ASSERTION créées par CURRENT_USER TABLE_PRIVILEGES – 13/09/2021 privilèges accordés par CURRENT_USER, à CURRENT_USER, ou à PUBLIC © Robert Godin. Tous droits réservés. 19
Dictionnaire de données Oracle avec SQL*plus 13/09/2021 © Robert Godin. Tous droits réservés. 20
Recherche d ’une table du dictionnaire de données 13/09/2021 © Robert Godin. Tous droits réservés. 21
6. 2 Requêtes SQL (SELECT) n Syntaxe de requête. SQL n Syntaxe du select. SQL n . . . 13/09/2021 © Robert Godin. Tous droits réservés. 22
6. 2. 1 Projection d'une table et la clause DISTINCT n Produire les no. Client et date. Commande de toutes les Commandes Multi-ensemble ! 13/09/2021 © Robert Godin. Tous droits réservés. 23
Clause DISTINCT n Produire les no. Client et date. Commande de toutes les Commandes 13/09/2021 no. Client, date. Commande (Commande) © Robert Godin. Tous droits réservés. 24
6. 2. 2 Sélection sur une table (WHERE) n Sélectionner les Articles dont le prix est inférieur à $20. 00 et le numéro est supérieur à 30 prix. Unitaire < 20. 00 ET no. Article > 30 (Article) 13/09/2021 © Robert Godin. Tous droits réservés. 25
Syntaxe de condition. SQL n Syntaxe (incomplète) de la condition. Simple : 13/09/2021 © Robert Godin. Tous droits réservés. 26
Condition. SQL - BETWEEN n Sélectionner les Commandes du mois de juin de l'année 2000 13/09/2021 © Robert Godin. Tous droits réservés. 27
Condition. SQL - IN n Sélectionner les Commandes du Client dont le no. Client est 10 ou 40 ou 80 13/09/2021 © Robert Godin. Tous droits réservés. 28
Condition. SQL - LIKE n Sélectionner les Clients dont le nom. Client contient le mot Le n 2 ième lettre du nom. Client = o et dernière lettre est un k 13/09/2021 © Robert Godin. Tous droits réservés. 29
Condition. SQL - IS NOT NULL n Sélectionner les Articles dont la description n'est pas une valeur nulle 13/09/2021 © Robert Godin. Tous droits réservés. 30
6. 2. 3 Sélectionprojection sur une table n Produire les no. Client et date. Commande des Commandes dont la date est supérieure au 05/07/2000 Laboratoire Créer le schéma de la BD Plein. De. Foin : Schema. Ventes. Plein. De. Foin. sql Exercices 1 a), b) , c) , n), o) 13/09/2021 © Robert Godin. Tous droits réservés. 31
6. 2. 4 Produit cartésien avec SELECT-FROM n Produire toutes les combinaisons possibles de lignes de Client et de Commande. . . Client Commande 13/09/2021 © Robert Godin. Tous droits réservés. 32
6. 2. 5 Jointure naturelle avec SELECT-FROM-WHERE n Produire les informations au sujet des Clients et de leurs Commandes Client. no. Client, nom. Client, no. Téléphone, no. Commande, date. Commande ( Client. no. Cliente = Commande. no. Client (Client Commande)) 13/09/2021 © Robert Godin. Tous droits réservés. 33
6. 2. 6 Jointure avec JOIN (SQL 2) n Produire les informations au sujet des Clients et de leurs Commandes Client Commande n Jointure- (si noms de colonnes de jointure sont différents) 13/09/2021 © Robert Godin. Tous droits réservés. 34
4. 2. 7 n Jointure de plusieurs tables Sélectionner les nom. Client des Clients qui ont commandé au moins un plant d'herbe à puce nom. Client ( description = “Herbe à puce ” (Client Commande Ligne. Commande Article)) 13/09/2021 © Robert Godin. Tous droits réservés. 35
6. 2. 8 Formulations équivalentes, performance et indépendance des données n ~Algèbre relationnelle n Ordre quelconque des tables du FROM – n n la plupart du temps… AND commutatif… Processus d ’évaluation de requête 13/09/2021 © Robert Godin. Tous droits réservés. 36
6. 2. 9 Définition d'un alias (clause AS) n ~ renommer ( ) Laboratoire Exercices 1 d) e) f) h) 13/09/2021 © Robert Godin. Tous droits réservés. 37
Auto-jointure n Quels sont les Clients qui ont le même numéro de téléphone? Client. no. Client, Client 2. no. Client, ( Client. no. Téléphone = Client 2. no. Téléphone (Client 2 (Client))) no. Client, no. Client 2 (Client 2(no. Client 2, nom. Client 2, no. Téléphone) (Client)) 13/09/2021 © Robert Godin. Tous droits réservés. 38
6. 2. 10 Jointure externe (OUTER JOIN) n Produire les informations au sujet des Clients et de leurs Commandes incluant les informations sur les Clients qui n’ont pas placé de Commande Client = Commande n Oracle – 13/09/2021 « + » après colonne pour inclure la valeur NULL © Robert Godin. Tous droits réservés. 39
6. 2. 11 Opérations ensemblistes (UNION, INTERSECT, EXCEPT) n Produire les noms et numéros de téléphone des Employés qui sont aussi des Clients de la pépinière Laboratoire Exercices 1 g) i) j) 13/09/2021 © Robert Godin. Tous droits réservés. 40
6. 2. 12 Expressions générales sur les colonnes n La liste des no. Article avec le prix. Unitaire avant et après inclusion de la taxe de 15% 13/09/2021 © Robert Godin. Tous droits réservés. 41
6. 2. 12 Expressions (suite) n Produire le détail de chacun des Articles commandés la Commande #1 incluant le prix total avant et après la taxe de 15% pour chacun des Articles commandés 13/09/2021 © Robert Godin. Tous droits réservés. 42
Expression sur colonne du WHERE n Les Articles dont le prix. Unitaire incluant la taxe de 15% est inférieur à $16. 00 13/09/2021 © Robert Godin. Tous droits réservés. 43
Opérateurs n Conversions automatiques entre types compatibles 13/09/2021 © Robert Godin. Tous droits réservés. 44
Pseudo-colonnes n Les Commandes de la journée 13/09/2021 © Robert Godin. Tous droits réservés. 45
Sélection par un CASE n Produire la quantité qui a été livrée pour l'Article #50 de la Commande #4 13/09/2021 © Robert Godin. Tous droits réservés. 46
Quelques fonctions SQL 2 n POSITION(patron IN chaîne) CHARACTER_LENGTH(chaîne) OCTET_LENGTH (chaîne) BIT_LENGTH(chaîne) EXTRACT(champ FROM date. Ou. Time) SUBSTRING (chaîne FROM indice. Début FOR nombre. Caractères) UPPER | LOWER (chaîne) TRIM ([LEADING|TRAILING|BOTH] caractère FROM chaîne) CAST(expression AS type) … n Voir documentation du SGBD n n n n n 13/09/2021 © Robert Godin. Tous droits réservés. 47
6. 2. 13 Expressions et conditions sur les valeurs nulles (NULL) n Arithmétique – n opérande NULL => NULL Comparaison (>, <, …) – 13/09/2021 opérande NULL => UNKNOWN © Robert Godin. Tous droits réservés. 48
NULL pour les nuls ? n Si x est NULL – 13/09/2021 UNKNOWN OR UNKNOWN = UNKNOWN n pas dans le résultat ! © Robert Godin. Tous droits réservés. 49
6. 2. 14 Fonctions de groupe n Le nombre d'Articles différents à vendre ainsi que le prix. Unitaire moyen des Articles 13/09/2021 © Robert Godin. Tous droits réservés. 50
suite 13/09/2021 © Robert Godin. Tous droits réservés. 51
6. 2. 15 Partition d'une table avec la clause GROUP BY n Produire le nombre de Commandes passées par chacun des Clients qui ont passé au moins une Commande 13/09/2021 © Robert Godin. Tous droits réservés. 52
Pour chacune des Ligne. Commande pour lesquelles au moins une Livraison a été effectuée, produire le no. Commande et no. Article, la quantité totale livrée et le nombre de Livraisons effectuées 13/09/2021 © Robert Godin. Tous droits réservés. 53
6. 2. 16 n Clause HAVING Produire le nombre de Commandes passées par chacun des Clients qui ont passé deux Commandes ou plus 13/09/2021 © Robert Godin. Tous droits réservés. 54
Produire le nombre de Commandes passées par chacun des Clients qui ont passé deux Commandes ou plus après le 02/06/2000 13/09/2021 © Robert Godin. Tous droits réservés. 55
6. 2. 17 Tri du résultat (ORDER BY) n Les Clients en ordre alphabétique du nom Laboratoire Exercices 1 q) s) t) v) 13/09/2021 © Robert Godin. Tous droits réservés. 56
6. 2. 18 SELECT imbriqué 6. 2. 18. 1 Opération élément de (IN) n Les Clients qui ont passé au moins une Commande Client Commande 13/09/2021 © Robert Godin. Tous droits réservés. 57
Ligne à plusieurs colonnes n Chercher les Ligne. Commandes pour lesquelles au moins une Livraison a été effectuée 13/09/2021 © Robert Godin. Tous droits réservés. 58
6. 2. 18. 2 SELECT imbriqué qui retourne une ligne n Sélectionner les Commandes du Client Hugh Paycheck n Exception si plusieurs lignes retournées par SELECT imbriqué 13/09/2021 © Robert Godin. Tous droits réservés. 59
6. 2. 18. 3 SELECT imbriqué corrélé n Produire les informations au sujet des Clients qui ont passé au moins une Commande Référence à une colonne non locale 13/09/2021 © Robert Godin. Tous droits réservés. 60
6. 2. 18. 4 Test d’ensemble vide (EXISTS) n Produire les informations au sujet des Clients qui ont passé au moins une Commande 13/09/2021 © Robert Godin. Tous droits réservés. 61
6. 2. 18. 5 Test de double (UNIQUE) n Vérifier s'il y a plus d’un Client qui porte le même nom n Clients qui ont passé au moins deux Commandes 13/09/2021 © Robert Godin. Tous droits réservés. 62
6. 2. 18. 6 Quantificateurs (ALL, SOME/ANY) n Commandes passées après la dernière Livraison (date ultérieure) n Commandes passées après au moins une des Livraisons 13/09/2021 © Robert Godin. Tous droits réservés. 63
6. 2. 18. 7 Test d'inclusion entre deux tables et division n T 1 T 2 13/09/2021 © Robert Godin. Tous droits réservés. 64
Quelles sont les no. Commande des Commandes qui incluent tous les Articles dont le prix. Unitaire est $10. 99 13/09/2021 © Robert Godin. Tous droits réservés. Lab Exercices 1 e) f) j) k) p) u) m) 65
6. 2. 18. 8 SELECT imbriqué dans le FROM n Produire les no. Client et date. Commande des Commandes dont la date. Commande est supérieure au 05/07/2000 Laboratoire Exercices 2 a) b) 13/09/2021 © Robert Godin. Tous droits réservés. 66
6. 2. 19 Récursivité en SQL 3 13/09/2021 © Robert Godin. Tous droits réservés. 67
6. 3 Opérations de mise à jour des tables en SQL n. Insert n. Delete n. Update 13/09/2021 © Robert Godin. Tous droits réservés. 68
6. 3. 1 Insertion dans une table (INSERT) n Insérer une nouvelle ligne dans la table Client n Changer l ’ordre de défaut 13/09/2021 © Robert Godin. Tous droits réservés. 69
Insertion d ’une partie des colonnes 13/09/2021 © Robert Godin. Tous droits réservés. 70
Insertion à partir d ’un SELECT n Produire les lignes de Détail. Livraison pour la Livraison #106 à partir des Ligne. Commandes de la Commande #7 13/09/2021 © Robert Godin. Tous droits réservés. 71
6. 3. 2 Suppression de lignes (DELETE) n Supprimer toutes lignes de la table Client n Supprimer le Client #70 de la table Client n Supprimer les Clients qui n'ont passé de Commande 13/09/2021 © Robert Godin. Tous droits réservés. 72
6. 3. 3 Modification de lignes (UPDATE) n Changer le no. Téléphone du Client #10 pour (222)222 -2222 n Augmenter tous les prix. Unitaires des Articles de 10% n Modification de plusieurs colonnes à la fois Laboratoire Exercices 1 x) y) z) 13/09/2021 © Robert Godin. Tous droits réservés. 73
6. 3. 4 Gestion des transactions en SQL n COMMIT WORK – n ROLLBACK WORK – n annule la transaction en cours Début de transaction implicite – – n confirme la transaction en cours début de session fin de la précédente Commande LDD provoque un COMMIT 13/09/2021 © Robert Godin. Tous droits réservés. 74
6. 4 Niveau externe du schéma en SQL n Gestion de la sécurité – n GRANT Tables virtuelles – 13/09/2021 VIEWS © Robert Godin. Tous droits réservés. 75
6. 4. 1. 1 n Sécurité en SQL (GRANT) Identification et authentification Identification des utilisateurs – authorization. ID n PUBLIC n Authentification – – n : tous les utilisateurs mot de passe … Oracle – SYS, SYSTEM n CREATE 13/09/2021 USER authorization. ID. . . © Robert Godin. Tous droits réservés. 76
6. 4. 1. 2 n privilège : n objet : 13/09/2021 Privilèges © Robert Godin. Tous droits réservés. 77
Exemples 13/09/2021 © Robert Godin. Tous droits réservés. 78
Privilèges (suite) n Commandes LDD – n Création d ’une VIEW sur T – n SELECT sur T FOREIGN KEY sur T – n propriétaire du schéma privilège REFERENCES sur T ROLE – – 13/09/2021 ensemble de privilèges SQL 3, Oracle © Robert Godin. Tous droits réservés. 79
6. 4. 1. 3 Suppression de privilèges 13/09/2021 © Robert Godin. Tous droits réservés. 80
6. 4. 2 Table virtuelle (VIEW) 13/09/2021 © Robert Godin. Tous droits réservés. 81
6. 4. 2. 1 Implémentation des tables virtuelles n Résolution des vues par modification de requête 13/09/2021 © Robert Godin. Tous droits réservés. 82
Résolution des vues par matérialisation n Table stockée n Redondance n Maintenance de la cohérence n Meilleure performance du SELECT n Moins bonne performance des mises à jour n Entrepôts de données 13/09/2021 © Robert Godin. Tous droits réservés. 83
6. 4. 2. 2 Mise à jour de tables virtuelles n Une seule table – – – 13/09/2021 sans DISTINCT colonnes simples pas de SELECT imbriqué © Robert Godin. Tous droits réservés. 84
Exemple de mise à jour par modification de requête 13/09/2021 © Robert Godin. Tous droits réservés. 85
6. 4. 2. 3 Problèmes de mise à jour d'une table virtuelle n Sémantique incohérente. . . 13/09/2021 © Robert Godin. Tous droits réservés. 86
Rejet de mise à jour incohérente avec WITH CHECK OPTION 13/09/2021 © Robert Godin. Tous droits réservés. 87
6. 4. 2. 4 Hiérarchie de tables virtuelles 13/09/2021 © Robert Godin. Tous droits réservés. 88
6. 4. 2. 5 Renommer les colonnes d'une VIEW N. B. Modification interdite 13/09/2021 © Robert Godin. Tous droits réservés. 89
6. 4. 2. 6 Indépendance logique des données et encapsulation par les tables virtuelles n Catalogue prix. Unitaire) n Inventaire (no. Article, quantitéEn. Stock) 13/09/2021 (no. Article, © Robert Godin. Tous droits réservés. description, 90
6. 4. 2. 7 Sécurité par les tables virtuelles 13/09/2021 © Robert Godin. Tous droits réservés. 91
6. 5 Schéma interne n Non standardisé – – n organisation primaire de la table organisations secondaires (INDEX) Voir Volume II 13/09/2021 © Robert Godin. Tous droits réservés. 92
6. 6 Interface entre SQL et un programme n SQL incomplet n Défaut d'impédance (impedance mismatch) 13/09/2021 – modèle de données BD – modèle de données du langage de programmation © Robert Godin. Tous droits réservés. 93
Librairie spécialisée (Call. Level Interface - SQL/CLI) n API spécifique au SGBD – n n non portable Standard de facto ODBC – développé par Microsoft pour le C – aligné sur X/Open, ISO CLI – portable – pilote ODBC pour client/serveur – JDBC pour Java SQL 3 : SQL/CLI 13/09/2021 © Robert Godin. Tous droits réservés. 94
SQL enchâssé (Embedded SQL) Code SQL dans le source du langage hôte n Syntaxe spéciale n Pré-compilation n – n Oracle : pro*C/C++, pro*COBOL, …, JSQL Moins portable – – pré-compilateur spécifique au SGBD traduit en API du SGBD n JSQL 13/09/2021 ->API standard JDBC © Robert Godin. Tous droits réservés. 95
Extension procédurale à SQL (Persistent Stored Modules SQL/PSM) n SQL + – – n structures de contrôle procédures, fonctions, packages support direct des types SQL pour les variables exécution au niveau serveur de BD Oracle – 13/09/2021 PL/SQL © Robert Godin. Tous droits réservés. 96
6. 6. 1 SQL enchâssé n Syntaxe n Insertion d'une ligne dans la table Client 13/09/2021 © Robert Godin. Tous droits réservés. 97
Variables partagées n Déclaration n Utilisation de variables partagées n Vérification d ’exceptions avec SQLSTATE 13/09/2021 © Robert Godin. Tous droits réservés. 98
6. 6. 1. 1 unique 13/09/2021 SELECT à ligne © Robert Godin. Tous droits réservés. 99
6. 6. 1. 2 13/09/2021 Curseur SQL © Robert Godin. Tous droits réservés. 100
6. 6. 1. 3 Mises à jour par curseur 13/09/2021 © Robert Godin. Tous droits réservés. 101
6. 6. 1. 4 13/09/2021 SQL dynamique © Robert Godin. Tous droits réservés. 102
6. 6. 2 Connexion (CONNECT) 13/09/2021 © Robert Godin. Tous droits réservés. 103
6. 6. 2 Connexion (CONNECT) n Syntaxe du CONNECT n Exemple avec SQL enchâssé en C n Pour passer d ’un connexion à l ’autre n Pour terminer une connexion 13/09/2021 © Robert Godin. Tous droits réservés. 104
6. 6. 3 JDBC API standard pour JAVA n Ensemble de classes n Besoin d ’installer un pilote JDBC dans l ’environnement JAVA n 13/09/2021 © Robert Godin. Tous droits réservés. 105
6. 6. 3. 1 Architecture pour les pilotes JDBC 13/09/2021 © Robert Godin. Tous droits réservés. 106
6. 6. 3. 2 Etablissement d'une connection n Charger les pilotes JDBC d ’ Oracle – – N. B. La librairie des pilotes Oracle doit être accessible à la machine virtuelle Java. Voir http: //www. info. uqam. ca/~godin/livres. html Avec JDK 1. 1 (problème avec bloc statique) utiliser : n n Driver. Manager. register. Driver (new oracle. jdbc. driver. Oracle. Driver()); Établir une connexion avec le pilote OCI 8 pour un serveur Oracle local – 13/09/2021 OCI 8 est un pilote de type 2 © Robert Godin. Tous droits réservés. 107
6. 6. 3. 4 Exécution d'une opération de mise à jour (INSERT, DELETE, UPDATE) 13/09/2021 © Robert Godin. Tous droits réservés. 108
6. 6. 3. 5 SELECT 13/09/2021 Exécution d'un © Robert Godin. Tous droits réservés. 109
6. 6. 3. 6 Gestion des transactions Par défaut : auto-commit n Pour modifier n n Pour un commit explicite : n Pour un rollback : 13/09/2021 © Robert Godin. Tous droits réservés. 110
Result. Set scrollable, sensitive et updatable n n sous JDBC 2. 0 Paramètres à la création du Statement – ex: une. Connection. create. Statement (Result. Set. TYPE_SCROLL_SENSITIVE, Result. Set. CONCUR_UPDATABLE) n n Scrollable – first(), last(), before. First(), after. Last(), next(), previous(), relative(n), absolute(n) Sensitive – n voit des mises à jour en cours Updatable – update. XXX(), update. Row(), delete. Row(), insert. Row(), move. To. Insert. Row() 13/09/2021 © Robert Godin. Tous droits réservés. 111
6. 6. 3. 7 Gestion des exceptions n Mécanisme try/catch de JAVA 13/09/2021 © Robert Godin. Tous droits réservés. 112
Exemple d ’applette avec pilote JDBC type 4 (thin) 13/09/2021 © Robert Godin. Tous droits réservés. 113
6. 6. 3. 8 Compilation et exécutions multiples avec la classe Prepared. Statement n Compilation + exécution combinée au execute. Query n Compilation au prepare. Statement n Compilation avec paramètres 13/09/2021 © Robert Godin. Tous droits réservés. 114
Considérations client/serveur n n n Coût important de communication Limiter le nombre d ’appels au serveur de BD Solutions – combiner plusieurs opérations SQL en une n – combiner plusieurs opérations dans une procédure/fonction (stored) exécutée au niveau serveur n n n si possible extension procédurale (SQL/PSM, Oracle PL/SQL) support d ’un langage hôte (JAVA, C , C++, COBOL, …) JDBC 2. 0 – Statement peut contenir un lot d ’énoncés (batch update) n 13/09/2021 un. Enoncé. add. Batch(), un. Enoncé. execute. Batch() © Robert Godin. Tous droits réservés. 115
Chercher le nom. Client et no. Téléphone d ’un Client n Eviter : n Utiliser plutôt : 13/09/2021 © Robert Godin. Tous droits réservés. 116