Cours 5 PDF - Base de données et interopérabilité

Base de données et interopérabilité Département Informatique École supérieure d'ingénieurs Léonard-de-Vinci (ESILV) 19 Avril, 2024 Ruiwen HE LANGAGE PROCÉDURAL SQL PÔLE LÉ ONARD DE VINCI [email protected] i n t e r o p é r a b i l i t é & B D D 3 Plan Blocs Variables Instructions Structures de co...

Base de données et interopérabilité Département Informatique École supérieure d'ingénieurs Léonard-de-Vinci (ESILV) 19 Avril, 2024 Ruiwen HE LANGAGE PROCÉDURAL SQL PÔLE LÉ ONARD DE VINCI [email protected] i n t e r o p é r a b i l i t é & B D D 3 Plan Blocs Variables Instructions Structures de contrôle Curseurs Gestion des erreurs Procédures/fonctions stockées Packages Triggers PÔLE LÉ ONARD DE VINCI Calcul de la note finale avec les requêtes MySQL B D D & i n t e r o p é r a b i l i t é [email protected] Exemple 4 PÔLE LÉ ONARD DE VINCI Calcul de la note finale avec les requêtes MySQL Si on a une fonction B D D & i n t e r o p é r a b i l i t é [email protected] Exemple 5 PÔLE LÉ ONARD DE VINCI [email protected] i n t e r o p é r a b i l i t é & B D D 6 PL/SQL Langage procédural plus portable Un script SQL Développeur peut contenir des blocs de sousprogrammes en PL/SQL Traitement de transactions Construction de procédures ou fonctions stockées qui améliorent le mode client-serveur par stockage des procédures ou fonctions souvent utilisées au niveau serveur Gestion des erreurs Construction de triggers (ou déclencheurs) PÔLE LÉ ONARD DE VINCI [email protected] Un programme ou une procédure PL/SQL est un ensemble de un ou plusieurs blocs. Chaque bloc comporte trois sections : Section déclaration Section corps du bloc Section traitement des erreurs B D D & i n t e r o p é r a b i l i t é Structure d’un bloc 7 PÔLE LÉ ONARD DE VINCI [email protected] PL/SQL Block Types Blocs anonymes Non stockés dans oracle Exécutés une seule fois Blocs nommés B D D & i n t e r o p é r a b i l i t é Bloc anonyme portant un nom. Généralement exécuté une seule fois. Sous-programmes Procédures, fonctions ou packages Ces blocs sont stockés dans oracle et exécutés plusieurs fois. Exécution explicite. Triggers (ou déclencheurs) Stockés dans oracle. Exécution implicite lorsqu'un événement déclencheur se produit. 8 PÔLE LÉ ONARD DE VINCI [email protected] i n t e r o p é r a b i l i t é & B D D Section déclaration Définit et initialise les variables et les curseurs utilisés dans le bloc. Contient la description des structures et des variables utilisées dans le bloc Section facultative Commence par le mot clé DECLARE 9 PÔLE LÉ ONARD DE VINCI [email protected] i n t e r o p é r a b i l i t é & B D D 10 Section corps du bloc Utilise des commandes de contrôle de flux pour exécuter les commandes et assigner des valeurs aux variables déclarées. Contient les instructions du programme et éventuellement, à la fin, la section de traitement des erreurs Section obligatoire Introduite par le mot clé BEGIN Se termine par le mot clé END PÔLE LÉ ONARD DE VINCI [email protected] Section facultative Introduite par le mot clé EXCEPTION Gestion personnalisée des erreurs. B D D & i n t e r o p é r a b i l i t é Section traitement des erreurs 11 PÔLE LÉ ONARD DE VINCI [email protected] i n t e r o p é r a b i l i t é & B D D 12 Syntaxe DECLARE déclaration BEGIN corps-du-bloc EXCEPTION traitement-des-erreurs END; / ← A ajouter obligatoirement dans l’exécution d’un script PÔLE LÉ ONARD DE VINCI [email protected] i n t e r o p é r a b i l i t é & B D D 13 Exemple DECLARE x VARCHAR2(10); BEGIN x := 'Bonjour’; DBMS_OUTPUT.PUT_LINE(x); END; / PÔLE LÉ ONARD DE VINCI [email protected] DECLARE erreurEx EXCEPTION; PROVINCE OEHR_LOCATIONS.STATE_PROVINCE%TYPE; ADDRESS OEHR_LOCATIONS.STREET_ADDRESS%TYPE; B D D & i n t e r o p é r a b i l i t é Exemple (2) 14 PÔLE LÉ ONARD DE VINCI [email protected] i n t e r o p é r a b i l i t é & B D D 15 Exemple (2) suite BEGIN SELECT STATE_PROVINCE, STREET_ADDRESS INTO PROVINCE, ADDRESS FROM OEHR_LOCATIONS WHERE LOCATION_ID = 1000; IF PROVINCE IS NULL THEN RAISE erreurEx; END IF; DBMS_OUTPUT.PUT_LINE (ADDRESS || ' OK'); PÔLE LÉ ONARD DE VINCI [email protected] EXCEPTION WHEN erreurEx THEN DBMS_OUTPUT.PUT_LINE('Error: State Province is NULL'); END B D D & i n t e r o p é r a b i l i t é Exemple (2) fin 16 PÔLE LÉ ONARD DE VINCI [email protected] Variables scalaires Types composés Enregistrement (record) Table B D D & i n t e r o p é r a b i l i t é Types de variables 17 PÔLE LÉ ONARD DE VINCI [email protected] i n t e r o p é r a b i l i t é & B D D 18 Variables scalaires Types issus de SQL : CHAR, NUMBER, DATE, VARCHAR2 Types PL/SQL : BOOLEAN, TIMESTAMP , BINARY_INTEGER, DECIMAL, FLOAT, INTEGER, REAL, ROWID DATETIME N’existe pas dans PLSQL, remplacé par DATE Les variables hôtes sont préfixées par « : » PÔLE LÉ ONARD DE VINCI [email protected] i n t e r o p é r a b i l i t é & B D D 19 Déclaration des variables scalaires nom-variable nom-du-type; Exemple : x VARCHAR2(10); nom-variable nom-table.nom-attribut%TYPE; Exemple : note sc.score%TYPE; PÔLE LÉ ONARD DE VINCI [email protected] i n t e r o p é r a b i l i t é & B D D 20 Variables (scalaires ou composées) Valeur initiale : nom-variable nom-type := valeur; Constante : nom-variable nom-type DEFAULT valeur; ou nom-variable CONSTANT nom-type := valeur; PÔLE LÉ ONARD DE VINCI [email protected] Explicite avec TO_CHAR, TO_DATE, TO_NUMBER, RAWTOHEX, HEXTORAW Implicites, par conversion automatique B D D & i n t e r o p é r a b i l i t é Conversion de type 21 PÔLE LÉ ONARD DE VINCI [email protected] i n t e r o p é r a b i l i t é & B D D 22 Exemple DECLARE v_num NUMBER := 123.45; v_str VARCHAR2(20); BEGIN v_str := TO_CHAR(v_num); DBMS_OUTPUT.PUT_LINE('v_num en tant que chaîne de caractères : ' || v_str); END; PÔLE LÉ ONARD DE VINCI [email protected] Soit par référence à une structure de table ou de curseur en utilisant ROWTYPE : nom-variable nom-table%ROWTYPE; nom-variable nom-curseur%ROWTYPE; B D D & i n t e r o p é r a b i l i t é Déclaration pour un enregistrement 23 PÔLE LÉ ONARD DE VINCI [email protected] i n t e r o p é r a b i l i t é & B D D 24 Exemple DECLARE cn OEHR_COUNTRIES%ROWTYPE; BEGIN SELECT * INTOcn FROMOEHR_COUNTRIES WHERE COUNTRY_ID= ‘FR’; DBMS_OUTPUT.PUT_LINE(cn. COUNTRY_NAME|| ' ' || cn. REGION_ID); END; PÔLE LÉ ONARD DE VINCI [email protected] i n t e r o p é r a b i l i t é & B D D 25 Déclaration pour un enregistrement (2) Soit par énumération des rubriques qui la composent. Cela se fait en deux étapes : Déclaration du type enregistrement TYPE nom-du-type-record IS RECORD ( nom-attribut1 type-attribut1, nom-attribut2 typeattribut2,...); Déclaration de la variable de type enregistrement nom-variable nom-du-type-record; PÔLE LÉ ONARD DE VINCI [email protected] i n t e r o p é r a b i l i t é DECLARE TYPEorder_recordISRECORD( CIOEHR_COUNTRIES.COUNTRY_ID%TYPE, COUNTRY_NAME VARCHAR2(20), REGION_ID INTEGER ); B D D & Exemple 26 PÔLE LÉ ONARD DE VINCI [email protected] Ordorder_record; BEGIN SELECT*INTOOrd FROMOEHR_COUNTRIES WHERECOUNTRY_ID ='FR'; B D D & i n t e r o p é r a b i l i t é Exemple suite 27 DBMS_OUTPUT.PUT_LINE(Ord.CI ||''|| Ord.COUNTRY_NAME||''|| Ord.REGION_ID); END; PÔLE LÉ ONARD DE VINCI [email protected] Structure composée d’éléments d’un même type scalaire L’accès à un élément de la table s’effectue grâce à un indice, ou clé primaire Cet index est déclaré de type BINARY_INTEGER (valeurs entières signées) B D D & i n t e r o p é r a b i l i t é Tables 28 PÔLE LÉ ONARD DE VINCI [email protected] Deux étapes : Déclaration du type de l’élément de la table Déclaration de la variable de type table B D D & i n t e r o p é r a b i l i t é Déclaration pour une table 29 PÔLE LÉ ONARD DE VINCI [email protected] i n t e r o p é r a b i l i t é & B D D 30 Déclaration pour une table (2) Déclaration du type de l’élément de la table : TYPE nom-du-type-table IS TABLEOF type-argument INDEX BY BINARY_INTEGER; Déclaration de la variable de type table : nom-variable nom-du-type-table; PÔLE LÉ ONARD DE VINCI [email protected] i n t e r o p é r a b i l i t é & B D D 31 Exemple DECLARE TYPE tabNom IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER; tableNom tabNom; i BINARY_INTEGER; BEGIN tableNom(5) := 'Dupont'; i := 10; tableNom(i) := 'Dupond'; DBMS_OUTPUT.PUT_LINE(tableNom(10)); PÔLE LÉ ONARD DE VINCI END; [email protected] Structure alternative Structure répétitives B D D & i n t e r o p é r a b i l i t é Structures de contrôle 32 PÔLE LÉ ONARD DE VINCI [email protected] En PL/SQL, les structures alternatives sont utilisées pour prendre des décisions conditionnelles et exécuter différents blocs de code en fonction d'une condition donnée. IF-THEN : Utilisé pour exécuter un bloc de code si une condition est vraie. B D D & i n t e r o p é r a b i l i t é Structures alternatives 33 PÔLE LÉ ONARD DE VINCI IF-THEN-ELSE : Utilisé pour exécuter un bloc de code si une condition est vraie et un autre bloc de code si la condition est fausse. B D D & i n t e r o p é r a b i l i t é [email protected] Structures alternatives (2) 34 PÔLE LÉ ONARD DE VINCI CASE : Utilisé pour évaluer une expression et exécuter différents blocs de code en fonction de la valeur de l'expression. B D D & i n t e r o p é r a b i l i t é [email protected] Structures alternatives (3) 35 PÔLE LÉ ONARD DE VINCI [email protected] En PL/SQL, les structures de répétition (ou boucles) sont utilisées pour exécuter un bloc de code plusieurs fois jusqu'à ce qu'une condition spécifique soit remplie. BOUCLE WHILE : Utilisée pour répéter un bloc de code tant qu'une condition est vraie B D D & i n t e r o p é r a b i l i t é Structure répétitives 36 PÔLE LÉ ONARD DE VINCI BOUCLE FOR : Utilisée pour répéter un bloc de code un nombre spécifique de fois. B D D & i n t e r o p é r a b i l i t é [email protected] Structure répétitives (2) où valeur_initiale et valeur_finale sont des valeurs numériques définissant la plage de répétition. 37 PÔLE LÉ ONARD DE VINCI [email protected] BOUCLE LOOP : Utilisée pour créer une boucle infinie qui peut être interrompue par une instruction EXIT lorsque certaines conditions sont remplies. B D D & i n t e r o p é r a b i l i t é Structure répétitives (3) 38 PÔLE LÉ ONARD DE VINCI [email protected] i n t e r o p é r a b i l i t é & B D D 39 Les curseurs En PL/SQL (Procedural Language/Structured Query Language), les curseurs sont des structures de programmation utilisées pour parcourir les résultats d'une requête SQL, ligne par ligne. Ils sont utilisés dans les blocs de code PL/SQL pour manipuler des enregistrements de base de données de manière itérative. Les curseurs en PL/SQL offrent une flexibilité pour traiter les résultats des requêtes SQL dans des applications Oracle. Ils sont couramment utilisés dans les procédures stockées, les fonctions et les déclencheurs pour traiter les enregistrements un par un. PÔLE LÉ ONARD DE VINCI Exemple [email protected] Dans cet exemple : B D D & i n t e r o p é r a b i l i t é Un curseur nommé c_employee est déclaré pour sélectionner les employés d'un certain département. Des variables v_emp_id, v_first_name et v_last_name sont déclarées pour stocker les données des employés récupérées à partir du curseur. Le curseur est ouvert avec l'instruction OPEN. Les données sont récupérées une par une à l'aide de l'instruction FETCH dans une boucle LOOP. La boucle se termine lorsque toutes les lignes ont été récupérées avec l'instruction EXIT WHEN c_employee%NOTFOUND. Enfin, le curseur est fermé avec l'instruction CLOSE. 40 PÔLE LÉ ONARD DE VINCI [email protected] FETCH c_employee INTO v_emp_id, v_first_name, v_last_name; FETCH est utilisé pour récupérer les données des colonnes employee_id, first_name et last_name du curseur c_employee dans les variables v_emp_id, v_first_name et v_last_name respectivement. B D D & i n t e r o p é r a b i l i t é FETCH 41 PÔLE LÉ ONARD DE VINCI [email protected] Déclaration: CURSOR c_employee (dept_id NUMBER) IS SELECT employee_id, first_name, last_name FROM employees WHERE department_id = dept_id; Ouverture d’un curseur: OPEN c_employee(v_department_id); B D D & i n t e r o p é r a b i l i t é Exemple (2) Pour les paramètres, association par position ou par nom sous la forme: OPEN c_employee(dept_id