Cours BD Avancée 2023-2024 PDF

BASE DE DONNEES AVANCEE DUT GI – S3 LARAQUI ABDERRAHMANE 2023 – 2024 Version non révisée Installation de Oracle database XE Lien oracle database XE : https://www.oracle.com/fr/database/technologies/xe- downloads.html Installation sql developer Lien sql developer : h...

BASE DE DONNEES AVANCEE DUT GI – S3 LARAQUI ABDERRAHMANE 2023 – 2024 Version non révisée Installation de Oracle database XE Lien oracle database XE : https://www.oracle.com/fr/database/technologies/xe- downloads.html Installation sql developer Lien sql developer : https://www.oracle.com/database/sqldeveloper/technologies/download/ Connexion à la base Se connecter a oracle en sysdba Cette requête la liste des base de données créées Une seule base de données existe : XEPDB1 Se connecter a la base de données XEPDB1 Créer un utilisateur test et lui affecter les privilèges create session et create table Connexion avec le compte utilisateur Connexion avec le compte test Tester la création de table Tester l’insertion des enregistrements Tester l’interrogation des données Blocs PL/SQL Le PL de PL/SQL signifie Procedural Language. Il s’agit d’une extension procédurale du SQL permettant d’effectuer des traitements complexes sur une base de données. DECLARE /∗ d e c l a r a t i o n de v a r i a b l e s ∗/ BEGIN /∗ i n s t r u c t i o n s a e x e c u t e r ∗/ END; Affichage Pour afficher le contenu d’une variable, les procédures DBMS_OUTPUT.PUT() et DBMS_OUTPUT.PUT_LINE() prennent en argument une valeur à afficher ou une variable dont la valeur est à afficher. Par défaut, les fonctions d’affichage sont désactivées. Il convient, à moins que vous ne vouliez rien voir s’afficher, de les activer avec la commande SQL+ SET SERVEROUTPUT ON Variables Une variable se déclare de la sorte : nom type [:= initialisation ] ; SET SERVEROUTPUT ON DECLARE c varchar2(1 5) := ‘Hello World !‘ ; BEGIN DBMS_OUTPUT.PUT_LINE (c) ; END; / Les affectations se font avec la syntaxe variable := valeur ; Types de variables Variables scalaires Types issus de SQL : CHAR, NUMBER, DATE, VARCHAR2 Types PL/SQL : BOOLEAN, SMALLINT, BINARY_INTEGER, DECIMAL, FLOAT, INTEGER, REAL, ROWID Déclaration variable scalaire nom-variable nom-du-type; Exemple : x VARCHAR2(10); nom-variable nom-table.nom-attribut%TYPE; Exemple : film exemplaire.numFilm%TYPE; Affectation On place dans une variable le résultat d’une requête en utilisant le mot-clé INTO. affecte aux variables v 1,..., v n les valeurs retournées par la requête. Par exemple Prêtez attention au fait que la requête doit retourner une et une seule ligne, sinon, une erreur se produit à l’exécution. Traitements conditionnels Le IF et le CASE fonctionnent de la même façon que dans les autres langages impératifs : Declare nb number := 5; jour varchar2(20):= ''; Begin jour := case nb when 1 then 'lundi‘ when 2 then 'mardi‘ when 3 then 'mercredi‘ when 4 then 'jeudi‘ when 5 then 'vendredi‘ when 6 then 'samedi‘ Les conditions sont les mêmes when 7 then 'dimanche‘ qu’en SQL. else 'incorrect‘ Le switch du langage C end; s’implémente DBMS_OUTPUT.PUT_LINE(jour); en PL/SQL de la façon suivante : end;  declare nb number := 1; Traitements répétitifs Begin Loop dbms_output.put_line('numero ' || nb ); LOOP... END LOOP ; permet d’implémenter les boucles nb := nb+1; exit when (nb> 10); end loop; end; L’instruction EXIT WHEN permet de quitter une boucle. Declare nb number := 1; Begin for nb in 1.. 10 loop La boucle FOR existe aussi en PL/SQL : dbms_output.put_line('numero ' || nb ); end loop; end; declare nb number := 1; Ainsi que la boucle WHILE : Begin while nb Old.Prix * 1.1 ) BEGIN :New.Prix := :Old.Prix * 1.1 ; END; Déclencheurs Exemple2: Utilisation d'un TRIGGER pour le maintien d'une contrainte d'intégrité dynamique CREATE OR REPLACE TRIGGER AUGMENTPU2 BEFORE UPDATE OF Prix ON PRODUIT FOR EACH ROW When (New.Prix > Old.Prix * 1.1 ) BEGIN RAISE_APPLICATION_ERROR ( -20999, 'Violation de la Contrainte ') ; END;  Déclencheurs Exemple3 : Utilisation d'un TRIGGER pour le maintien d'une contrainte d'intégrité statique 0 < codcli < 10000 CREATE OR REPLACE TRIGGER VERIFIERNOCLIENT BEFORE INSERT OR UPDATE OF Codecli ON CLIENT FOR EACH ROW WHEN ( New.Codecli=10000 ) BEGIN RAISE_APPLICATION_ERROR ( -20009, ' Numéro du client incorrect ' ) ; END; N.B: CHECK est préférable !  Déclencheurs Lors d'un achat, la quantité à commandé d'un produit ne peut pas dépasser la quantité en stock disponible CREATE OR REPLACE TRIGGER VERIFIERSTOCK BEFORE INSERT OR UPDATE (QteCom) ON Detail FOR EACH ROW DECLARE S Produit.Qte%type; BEGIN SELECT Qte INTO S FROM Produit WHERE CodProd=:New.CodProd; If ( :New.Qtecom> S) Then RAISE_APPLICATION_ERROR ( -20009, ' Quantité demandée non disponible') ; End if; END; /