Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

Développement d'applications avec les Bases de données Partie 1 : PL/SQL Bases

Développement d'applications avec les Bases de données Partie 1 : PL/SQL Bases de données relationnelles Polytech Marseille Département Informatique 3ème année Architecture client-serveur (simple) 2 Bases de données et architectures client-serveur Bases de données et architectures client-serveur 3 4 Exemple "vols-réservations" Tables AVIONS(NumAv, NomAv, CapAv, VilleAv) PILOTES(NumPil, NomPil, NaisPil, VillePil) VOLS(NumVol,VilleD, VilleA, DateD, DateA, #NumPil, #NumAv, CoutVol) CLIENTS(NumCl, NomCl, NumRueCl, NomRueCl, CodePosteCl, VilleCl) DEFCLASSES(#NumVol, Classe, CoeffPlace, CoeffPrix) RESERVATIONS(#NumCl, #NumVol, #Classe, NbPlaces) 5 Exemple "vols-réservations" Attributs Les numéros servant de clé sont des entiers, à l'exception du numéro de vol NumVol qui est une chaîne de caractères commençant par la lettre 'V'. CapAv, NaisPil, NbPlaces, NumRueCl, CodePosteCl sont des entiers. CoutVol, CoeffPlace et CoeffPrix sont des nombres décimaux : – CoeffPlace, dans l'intervalle [0,1], donne le pourcentage de places existant dans la Classe concernée, pourcentage relatif à CapAv, capacité totale de l'avion. – CoeffPrix, supérieur ou égal à 1, donne le coefficient multiplicatif à appliquer à CoutVol pour obtenir le prix réel d'un voyage dans la classe voulue. CoutVol est donc le prix minimal d'une place, sur le vol concerné. DateD et DateA sont des dates, comportant le jour et l'heure voulue. 6 Exemple "vols-réservations" Création de AVIONS et PILOTES CREATE TABLE AVIONS( NumAv NUMBER(4) CONSTRAINT pk_avion PRIMARY KEY, NomAv VARCHAR2(20), CapAv NUMBER(4) CONSTRAINT dom_capav_avion CHECK (CapAv>0), VilleAv VARCHAR2(15) ); CREATE TABLE PILOTES( NumPil NUMBER(4) CONSTRAINT pk_pilote PRIMARY KEY, NomPil VARCHAR2(20) CONSTRAINT nn_nom_pilote NOT NULL, NaisPil NUMBER(4) CONSTRAINT dom_nais_pilote CHECK (NaisPil>1900), VillePil VARCHAR2(15) ); 7 Exemple "vols-réservations" Création de CLIENTS CREATE TABLE CLIENTS( NumCl NUMBER(5) CONSTRAINT pk_client PRIMARY KEY, NomCl VARCHAR2(20) , NumRueCl NUMBER(3) CONSTRAINT numrue_client CHECK (NumRueCl > 0), NomRueCl VARCHAR2(50), CodePosteCl NUMBER(5) CONSTRAINT codepostal_client CHECK (CodePosteCl > 0), VilleCl VARCHAR2(15) ); 8 Exemple "vols-réservations" Création de VOLS CREATE TABLE VOLS( NumVol VARCHAR2(5) CONSTRAINT pk_vol PRIMARY KEY, VilleD VARCHAR2(15) CONSTRAINT nn_villed_vol NOT NULL, VilleA VARCHAR2(15) CONSTRAINT nn_villea_vol NOT NULL, DateD DATE, DateA DATE, NumPil NUMBER(4) CONSTRAINT pil_ref_vol REFERENCES PILOTES(NumPil), NumAv NUMBER(4) CONSTRAINT avion_ref_vol REFERENCES AVIONS(NumAv), CoutVol NUMBER(10) CONSTRAINT dom_coutvol_vol CHECK (CoutVol>0), CONSTRAINT dom_numvol_vol CHECK(NumVol LIKE 'V%'), CONSTRAINT dates_vol CHECK (DateD < DateA), CONSTRAINT villes_vol CHECK (VilleD != VilleA) ); 9 Exemple "vols-réservations" Création de DEFCLASSES CREATE TABLE DEFCLASSES( NumVol VarChar2(5) CONSTRAINT vol_ref_defclasse references VOLS(NumVol), Classe VARCHAR2(12), CoeffPlace NUMBER(3,2) CONSTRAINT nn_coeffplace_defclasse NOT NULL, CoeffPrix NUMBER(3,2) CONSTRAINT coeffprix_defclasse CHECK (CoeffPrix >= 1), CONSTRAINT pk_defclasse PRIMARY KEY (NumVol,Classe), CONSTRAINT quota CHECK ((CoeffPrix <= 2 AND CoeffPlace <= 0.5) OR CoeffPrix > 2), CONSTRAINT coeffplace_defclasse CHECK (CoeffPlace BETWEEN 0 AND 1), ); 10 Exemple "vols-réservations" Création de RESERVATIONS CREATE TABLE RESERVATIONS( NumCl NUMBER(5) CONSTRAINT client_ref_reserv REFERENCES CLIENTS(NumCl), NumVol VARCHAR2(5), Classe VARCHAR2(12), NbPlaces NUMBER(4) CONSTRAINT nn_nbplaces_reserv NOT NULL, CONSTRAINT pk_reserv PRIMARY KEY (NumCl, NumVol, Classe), CONSTRAINT classe_ref_reserv FOREIGN KEY (NumVol,Classe) REFERENCES DEFCLASSES(NumVol,Classe), CONSTRAINT dom_nbplaces_reserv CHECK (NbPlaces >0) ); 11 Exemple "vols-réservations" Autres contraintes d'intégrité Contrainte 1 : une réservation ne peut pas être passée sur un vol dont le départ a déjà eu lieu. Contrainte 2 : il est impossible de supprimer une réservation relative à un vol en cours. Contrainte 3 : pour chaque vol, la somme des coefficients CoeffPlace de chaque classe doit être inférieure ou égale à 1. Contrainte 4 : à un instant donné, un pilote assure au plus un vol. Contrainte 5 : à un instant donné, un avion est au plus utilisé pour un vol. Etc. 12 1 application client lourd (c, java, ...) 1 application web (php, j2ee, ...) 2 applications mobiles (ios, android, ...) ... Exemple "vols-réservations" Applications 13 Rechercher des informations sur un vol Afficher tous les vols (en cours, ou un jour donné) Réserver des places sur un vol Ajouter un nouveau vol Ajouter un nouveau client Etc. Exemple "vols-réservations" Fonctionnalités 14 Utilisateur = personne ou application Identification Gestion des privilèges / Droits d'accès Exemple "vols-réservations" Utilisateurs et droits 15 Dans les applications ? – Non... Dans le SGBD ? – Oui, mais comment ? avec un langage procédural comme PL/SQL, PL/PgSQL, T-SQL, SQL/PSM, ... Exemple "vols-réservations" Implantation des contraintes PL/SQL Procedural Language / Structured Query Language 17 SOUTOU, C. "SQL pour Oracle", Eyrolles, 2008 (3ème édition). BIZOI, R. "PL/SQL pour Oracle 10g", Eyrolles, 2006. DATE, C. "Introduction aux bases de données", Vuibert, 2004 (8ème édition). GARDARIN, G. "Bases de données", Eyrolles, 2003. Bibliographie Rappel SQL se divise en : DDL (Data Definition Language) Pour créer le schéma de la BD DML (Data Manipulation Language) Pour créer/supprimer/mettre à jour les données DQL (Data Query Language) Pour l'extraction de données DCL (Data Control Language) Pour gérer les droits, gérer les transactions. 18 19 SQL n’est pas procédural...  Les SGBD fournissent une extension du langage SQL (instructions de branchement conditionnel, instructions de répétition, affectations, …) PL/SQL pour ORACLE T-SQL pour SQL Server PL/pgSQL pour PostgreSQL SQL/PSM (issu de la norme SQL2003) pour MySQL Introduction 20 Intérêt Pour la facilité et l’efficacité de développement : – gérer le contexte et lier entre elles plusieurs requêtes, – créer des bibliothèques de procédures cataloguées réutilisables Pour l’efficacité de l’application : – diminuer le volume des échanges entre client et serveur (un programme PL/SQL est exécuté sur le serveur) 21 PL/SQL Aperçu Structure d'un programme Variables, structures de contrôle Curseurs, interaction avec la base Sous-programmes (procédures, fonctions) Exceptions Déclencheurs (triggers) 22 Structure d'un programme Programme PL/SQL = bloc DECLARE section de déclarations section optionnelle ... BEGIN traitement, avec d’éventuelles directives SQL section obligatoire ... EXCEPTION gestion des erreurs retournées par le SGBDR section optionnelle ... END; 23 Structure d'un programme Blocs imbriqués Portée d’un identificateur : un descendant peut accéder aux identificateurs déclarés par un parent, pas l’inverse 24 Identificateurs et commentaires Identificateur (variable, curseur, exception, etc.) : – Commence par une lettre – Peut contenir : lettres, chiffres, $, #, _ – Interdits : &, -, /, espace – Jusqu’à 30 caractères – Insensible à la casse ! (nompilote = NomPILOTE) Commentaires : -- Commentaire sur une seule ligne /* Commentaire sur plusieurs lignes */ 25 Variables Types de variables PL/SQL : – Scalaires : par exemple NUMBER(5,2), VARCHAR2, DATE, BOOLEAN, ... – Composites : %ROWTYPE, RECORD, TABLE – Référence : REF – LOB (Large Object jusqu’à 4 Go ; pointeur si externe) Un programme PL/SQL peut également manipuler des variables non PL/SQL : – Variables de substitution ou globales définies dans SQL*Plus – Variables hôtes (définies dans d’autres programmes et utilisées par le programme PL/SQL) Toute variable PL/SQL doit obligatoirement être déclarée dans une section DECLARE avant utilisation 26 Variables scalaires Syntaxe de déclaration : Identificateur [CONSTANT] type [[NOT NULL] {:= | DEFAULT} expression]; – CONSTANT : c’est une constante (sa valeur ne peut pas changer, doit être initialisée lors de la déclaration) – NOT NULL : on ne peut pas lui affecter une valeur nulle (en cas de tentative, une exception VALUE_ERROR est levée) – Initialisation : affectation := ou DEFAULT Pas de déclaration multiple dans PL/SQL ! number1, number2 NUMBER; ← déclaration incorrecte ! Autre possibilité pour affecter une valeur à une variable SELECT ... INTO identificateur FROM ... ; 27 Nouveaux types PL/SQL Nouveaux types prédéfinis : – BINARY_INTEGER : entiers signés entre –231 et 231 – PLS_INTEGER : entiers signés entre –231 et 231 ; plus performant que NUMBER et BINARY_INTEGER au niveau des opérations arithmétiques ; en cas de dépassement, exception levée Sous-types PL/SQL : restriction d’un type de base – Prédéfinis : CHARACTER, INTEGER, NATURAL, POSITIVE, FLOAT, SMALLINT, SIGNTYPE, etc. – Utilisateur (restriction : précision ou taille maximale) : SUBTYPE nomSousType IS typeBase [(contrainte)] [NOT NULL]; – Exemple de sous-type utilisateur : SUBTYPE numInsee IS NUMBER(13) NOT NULL; 28 Base de données utilisée pour les exemples 29 Variables scalaires : exemple DECLARE villeDepart CHAR(10) := ‘Paris’; villeArrivee CHAR(10); numVolAller CONSTANT CHAR := ‘AF8’; numVolRetour CHAR(10); BEGIN SELECT Ville_arrivee INTO villeArrivee FROM vol WHERE Numvol = numVolAller; vol aller numVolRetour := ‘AF9’; INSERT INTO vol VALUES (numVolRetour, NULL, NULL, villeArrivee, villeDepart); vol retour END; 30 Conversions Conversions implicites : – Lors du calcul d’une expression ou d’une affectation – Si la conversion n’est pas autorisée, une exception est levée Conversions explicites : 31 Quelques conversions implicites 32 Déclaration %TYPE Déclarer une variable de même type que – Une colonne (attribut) d’une table existante : nomNouvelleVariable NomTable.NomColonne %TYPE [{:= | DEFAULT} expression]; – Une autre variable, déclarée précédemment : nomNouvelleVariable nomAutreVariable%TYPE [{:= | DEFAULT} expression]; Cette propagation du type permet de réduire le nombre de changements à apporter au code PL/SQL en cas de modification des types de certaines colonnes 33 Déclaration %TYPE : exemple DECLARE nomPilote pilote.Nom%TYPE; /* table pilote, colonne nom */ nomCoPilote nomPilote%TYPE; BEGIN ... SELECT Nom INTO nomPilote FROM pilote WHERE matricule = 1; SELECT Nom INTO nomCoPilote FROM pilote WHERE matricule = 2; ... END; 34 Variables %ROWTYPE Déclarer une variable composite du même type que les n- uplets d’une table : nomNouvelleVariable NomTable%ROWTYPE; Les composantes de la variables composite, identifiées par nomNouvelleVariable.nomColonne, sont du même type que les colonnes correspondantes de la table Les contraintes NOT NULL déclarées au niveau des colonnes de la table ne sont pas transmises aux uploads/Geographie/ bd-cm6.pdf