Remerciez-le!

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

Bases de données avancées Master 1 Informatique 2021-2022 Jérôme Darmont https:

Bases de données avancées Master 1 Informatique 2021-2022 Jérôme Darmont https://eric.univ-lyon2.fr/jdarmont/ Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 2 Actualité du cours https://eric.univ-lyon2.fr/jdarmont/ https://eric.univ-lyon2.fr/jdarmont/ https://twitter.com/darmont_lyon2 #bda Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 3 Objectifs du cours SQL : langage de requêtes (bases de données relationnelles) – Standard – Optimiseurs de requêtes – Non procédural – Données structurées Programmation nécessaire pour : – Tâches complexes – Interfaces utilisateurs Langage PL/pgSQL Langage XQuery 80 % des données sont peu ou pas structurées – Description via le langage XML PL/pgSQL Procedural Language/PostgreSQL Structured Query Language Partie 1 Bases de données avancées 4 Introduction Bases du langage Curseurs Gestion des erreurs Déclencheurs SQL dynamique Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 5 Plan Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 6 Requêtes SQL dans un programme SQL encapsulé : Requêtes SQL incorporées dans le code source (PL/SQL, T-SQL, PL/pgSQL, Pro*C…) API : Requêtes SQL via des fonctions du langage (Java Persistence API, PHP Data Objects…) Interfaces de niveau appel : intergiciel entre le langage et le SGBD (ODBC, JDBC, ADO…) Procédures stockées : Fonctions SQL stockées dans la base de données et exécutées par le SGBD (écrites en PL/SQL, T-SQL, PL/pgSQL) C U R S E U R S SGBD relationnel-objet Licence libre (BSD) Le plus conforme au standard SQL Disponible sur de nombreuses plateformes – Divers UNIX, dont Linux et Mac OS – Windows Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 7 Choix de PostgreSQL Michael Stonebraker fr.wikipedia.org 1974 : Ingres (INteractive Graphics REtrieval System) 1985 : Postgres (post-Ingres) 1995 : Postgres95 (fonctionnalités SQL) 1996 : PostgreSQL (v6) 2021 : v13.3 Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 8 Historique de PostgreSQL T ypes structurés (enregistrements, tableaux) possibles dans les tables (relationnel étendu) Comportement stable Langage procédural PL/pgSQL proche du PL/SQL d’Oracle (permet le SQL dynamique) Interfaçage possible avec des modules externes d’autres langages (PERL, Python…) Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 9 Caractéristiques de PostgreSQL Utilisation des types de données, opérateurs et fonctions de SQL Stockage du code dans la base de données – Sécurité liée à celle du SGBD et de ses droits d’accès Exécution au sein du serveur de BD – Pas d’allers-retours entre client et serveur ⇒ Performance Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 10 Caractéristiques de PL/pgSQL A) Jusqu’ici, tout va bien. B) Je suis déjà perdu. Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 11 Sondage express Répondre sur https://toreply.univ-lille.fr Question n° 455 Introduction Bases du langage Curseurs Gestion des erreurs Déclencheurs SQL dynamique Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 12 Plan Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 13 Structuration en blocs [DECLARE -- Déclarations] BEGIN -- Instructions PL/pgSQL [EXCEPTION -- Gestion des erreurs] END [ ] : clause optionnelle (idem dans tout le document) Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 14 Variables et constantes Déclaration dans la section DECLARE d’un bloc PL/pgSQL Variables ex. dateNaissance DATE; compteur INTEGER := 0; -- Initialisation compteur2 INTEGER DEFAULT 0; -- Valeur par défaut id CHAR(5) NOT NULL := ‘AP001’; Constantes ex. tauxTVA CONSTANT REAL := 0.2; Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 15 Principaux types de données Type Description boolean, bool Booléen (vrai/faux) smallint, int2 Entier de -32768 à +32767 integer, int, int4 Entier de -2147483648 à +2147483647 bigint, int8 Entier de -9223372036854775808 à +9223372036854775807 real, float4 Réel simple précision (6 décimales) double precision, float8 Réel double précision (15 décimales) numeric, numeric(P, S), decimal, decimal (P, S) Nombre jusqu'à 131072 chiffres avant la virgule (P), 16383 après (S) character[N], char[N] Chaîne de caractères de longueur N fixe varchar, varchar[N] Chaîne de caractères de longueur variable (maximum N si précisé) text T exte long de longueur variable (nécessite des opérateurs spécifiques) date Date (ex. ‘jj/mm/aaaa’) time Heure (ex. ‘hh:mm:ss’) timestamp Date et heure (ex. ‘aaaa-mm-jj hh:mm:ss’) T ype d’une autre variable ex. credit REAL; debit credit%TYPE; T ype de l’attribut d’une table ex. numEmp EMP .EMPNO%TYPE; T ype des n-uplets d’une table ex. unEtudiant STUDENT%ROWTYPE; N-uplet indéfini (enregistrement) ex. resultat RECORD; Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 16 Référencer un type existant À utiliser au maximum ! T ableaux ex. notes NUMERIC(2, 2)[]; matrice INTEGER[][]; T ypes composites (enregistrements) ex. CREATE TYPE tEmploye AS ( -- Définition num NUMERIC, nom VARCHAR ); -- à définir hors de PL/pgSQL (commande SQL) unEmploye tEmploye; -- Instanciation Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 17 T ypes structurés Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 18 Affectation simple Variables ex. n := 0; n := n + 1; T ableaux ex. notes := ARRAY[10.2, 13.3, 15.5, 9.8]; matrice := ARRAY[ ARRAY[4, 2], ARRAY[1, 9] ]; Enregistrements ex. unEmploye := (1501, 'DARMONT'); Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 19 Lecture de valeurs de la base de données Variables ex. SELECT custname INTO nomClient FROM customer WHERE custnum = 10; SELECT ename, sal INTO nom, salaire FROM emp WHERE empno = 5000; Enregistrements ex. SELECT empno, ename INTO unEmploye FROM emp WHERE empno = 1501; SELECT * INTO resultat FROM customer WHERE custnum = 20; Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 20 Opérateurs arithmétiques et logiques Opérateurs arithmétiques + - / * ** Opérateur de concaténation || Opérateurs de comparaison = < > <= >= <> IS NULL LIKE BETWEEN IN Opérateurs logiques AND OR NOT Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 21 T ests (1/3) IF-THEN, IF-THEN-ELSE ou IF-THEN-ELSIF IF condition1 THEN -- -- Instructions PL/pgSQL [ELSIF condition2 THEN -- Instructions PL/pgSQL] [ELSE -- Instructions PL/pgSQL] END IF; Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 22 T ests (2/3) CASE simple CASE variable WHEN val1 THEN -- Instructions PL/pgSQL WHEN val2, val3 THEN -- Instructions PL/pgSQL WHEN val4 THEN -- Instructions PL/pgSQL [ELSE -- Instructions par défaut] END CASE; Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 23 T ests (3/3) CASE par intervalles CASE WHEN var BETWEEN val1 AND val2 THEN -- Instructions PL/pgSQL WHEN var BETWEEN val2 + 1 AND val3 THEN -- Instructions PL/pgSQL END CASE; Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 24 Boucles Pour FOR iterateur IN [REVERSE] min..max [BY pas] LOOP -- Instructions PL/pgSQL END LOOP; T ant que WHILE condition LOOP -- Instructions PL/pgSQL END LOOP; Répéter jusqu’à LOOP -- Instructions PL/pgSQL EXIT WHEN condition; -- C’est moche, mais END LOOP; -- pas le choix... Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 25 Boucles sur tableaux T ableau FOREACH note IN ARRAY notes LOOP -- note est un NUMERIC(2, 2) -- Instructions PL/pgSQL END LOOP; Matrice FOREACH n IN ARRAY matrice LOOP -- n est un INTEGER -- Instructions PL/pgSQL END LOOP; -- Eh oui, pas besoin de boucles imbriquées ! A) 1 B) 2 C) 3 D) 4 E) 5 Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 26 Quizz Répondre sur https://toreply.univ-lille.fr Question n° 494 Combien y a-t-il de sections dans un bloc PL/pgSQL ? Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 27 Implémentation d’un bloc Dans une fonction ex. CREATE [OR REPLACE] FUNCTION test() RETURNS VOID AS $$ -- bloc PL/pgSQL $$ LANGUAGE plpgsql; Exécution de la fonction ex. SELECT test(); -- La forme du résultat peut différer SELECT * FROM test(); -- en fonction des clients PostreSQL. Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 28 Exemple de fonction -- Calcul de prix TTC CREATE OR REPLACE FUNCTION calculPrixTTC(idProduct NUMERIC) RETURNS REAL AS $$ DECLARE tauxTVA CONSTANT REAL := 0.2; prixHT demo_product_info.list_price%TYPE; BEGIN -- Lire le prix HT SELECT list_price INTO prixHT FROM demo_product_info WHERE product_id = idProduct; -- Retourner le prix TTC RETURN prixHT * (1 + tauxTVA) ::REAL; END $$ LANGUAGE plpgsql; -- Exécution SELECT calculPrixTTC(10); Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 29 Fonction retournant plusieurs valeurs Paramètres de sortie CREATE OR REPLACE FUNCTION calculs( n1 INT, n2 INT, OUT somme INT, OUT produit INT) AS $$ -- Pas de RETURN BEGIN somme := n1 + n2; produit := n1 * n2; END $$ LANGUAGE plpgsql; SELECT calculs(4, 5); -- Sous forme d’enregistrement (valeurs uniquement) -- ou SELECT * FROM calculs(4, 5); -- Sous forme de table (avec entêtes somme et produit) Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 30 Fonction retournant plusieurs enregistrements CREATE OR REPLACE FUNCTION serie(taille INT, pas INT) RETURNS SETOF INT AS $$ DECLARE i INT; BEGIN FOR i IN 1..taille BY pas LOOP RETURN NEXT i; END LOOP; RETURN; END $$ LANGUAGE plpgsql; SELECT serie(10, 2); Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 31 Appel de fonction dans une fonction CREATE OR REPLACE FUNCTION droleDeDiv(n1 INT, n2 INT) RETURNS REAL AS $$ DECLARE s INT; p INT; BEGIN SELECT * INTO s, p FROM calculs(n1, n2); RETURN p / s ::REAL; END $$ LANGUAGE plpgsql; SELECT droleDeDiv(5, 6); Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 32 Récursivité CREATE OR REPLACE FUNCTION factorielle(n INTEGER) RETURNS INTEGER AS $$ DECLARE f INTEGER; BEGIN IF n = 1 THEN -- Condition d'arrêt RETURN 1; ELSE SELECT * INTO f FROM factorielle(n - 1); -- Appel récursif RETURN n * f; END IF; END $$ LANGUAGE plpgsql; SELECT factorielle(10); Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 33 Sondage express Répondre sur https://toreply.univ-lille.fr Question n° 454 Peut-on écrire un bloc PL/pgSQL en-dehors d’une fonction? Introduction Bases du langage Curseurs Gestion des erreurs Déclencheurs SQL dynamique Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 34 Plan Requête qui retourne un seul n-uplet – SELECT INTO – Stockage du résultat dans une ou plusieurs variables ou un enregistrement Requête qui uploads/Management/ m1info-bda.pdf