Remerciez-le!

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

Conception de bases de données http://bdd.crzt.fr Technologie Oracle : Extensio

Conception de bases de données http://bdd.crzt.fr Technologie Oracle : Extensions procédurales à SQL Paternité - Partage des Conditions Initiales à l'Identique : http://creativecommons.org/licenses/by-sa/2.0/fr/ STÉPHANE CROZAT 25 juillet 2014 Table des matières I - Théorie : Oracle 7 A. Introduction à Oracle.....................................................................................7 1. Exemple complet BD "Gestion des intervenants"...................................................................7 2. Présentation...................................................................................................................12 3. Particularités LDD............................................................................................................13 4. Dictionnaire de données...................................................................................................14 5. Les séquences................................................................................................................15 6. Particularités LMD...........................................................................................................16 7. Fonctions SQL à connaître................................................................................................17 8. Fonctions de traitement des dates.....................................................................................19 9. SQL*Plus.......................................................................................................................20 10. Accès inter-schémas......................................................................................................21 11. Fenêtrage des données..................................................................................................22 B. Le langage procédural PL/SQL......................................................................23 1. Présentation du PL/SQL....................................................................................................23 2. Structure d'un bloc PL/SQL...............................................................................................23 3. Variables........................................................................................................................24 4. Affichage à l'écran...........................................................................................................25 5. Structures de contrôle.....................................................................................................25 6. Blocs PL/SQL : Procédure, fonction, bloc anonyme...............................................................25 7. Affectation par une requête SELECT INTO...........................................................................27 8. Exercice.........................................................................................................................28 9. Curseurs PL/SQL.............................................................................................................28 10. Exercice.......................................................................................................................30 11. Gestion d'exception.......................................................................................................30 12. Durée de tournage d'un film...........................................................................................31 C. Les triggers................................................................................................32 1. Principes des triggers.......................................................................................................32 2. Manipulation des anciennes et nouvelles valeurs dans les triggers.........................................34 3. Prédicats d'événement au sein des triggers........................................................................35 4. Exercice.........................................................................................................................36 D. Prise en main de Oracle SQL Developer.........................................................36 1. Installation de SQL Developer...........................................................................................36 2. Connexion avec SQL Developer.........................................................................................36 3. Naviguer dans le catalogue de SQL Developer.....................................................................37 4. Exécuter des requêtes SQL avec SQL Developer..................................................................38 5. Écrire du PL/SQL avec SQL Developer................................................................................40 6. Exécution de fichiers SQL.................................................................................................42 Stéphane Crozat (Contributions : Dritan Nace, Yacine Challal, Antoine Vincent) 3 II - Pratique : SQL sous Oracle 43 A. Migration d'une base Access vers Oracle........................................................43 B. Séquence...................................................................................................46 C. Extension Médiathèque................................................................................47 D. Transactions...............................................................................................47 E. Questions avancées en SQL..........................................................................48 F. Maintenance de la base................................................................................49 G. Vue...........................................................................................................49 III - Pratique : PL/SQL sous Oracle 51 A. Fonctions stockées......................................................................................51 B. Curseurs....................................................................................................52 C. Triggers.....................................................................................................53 IV - Application : Oracle 55 A. Dictionnaire de données...............................................................................55 B. SQL sous Oracle..........................................................................................56 V - Test : Oracle 59 VI - Questions-réponses sur Oracle 63 VII - En résumé : Oracle 65 VIII - Bibliographie commentée sur Oracle 67 Questions de synthèse 69 Solution des exercices 73 Solution des exercices 85 Signification des abréviations 89 Bibliographie 91 Webographie 93 Index 95 Stéphane Crozat (Contributions : Dritan Nace, Yacine Challal, Antoine Vincent) 4 Théorie : Oracle Stéphane Crozat (Contributions : Dritan Nace, Yacine Challal, Antoine Vincent) 5 I - Théorie : Oracle I Introduction à Oracle 7 Le langage procédural PL/SQL 24 Les triggers 35 Prise en main de Oracle SQL Developer 39 A. Introduction à Oracle Objectifs Utiliser un SGBD professionnel largement répandu Expérimenter l'usage du langage SQL Savoir créer et interroger une base de données sous Oracle Connaître les commandes de base de SQL*Plus Savoir utiliser le dictionnaire de données 1. Exemple complet BD "Gestion des intervenants" Exemple : Modèle conceptuel MCD "Cours et Intervenants" Exemple : Schéma relationnel 1 tIntervenant (#pknom, prenom, poste) 2 tCours (#pkannee, #pknum, titre, type, fkintervenant=>tIntervenant, debut, \fin) Stéphane Crozat (Contributions : Dritan Nace, Yacine Challal, Antoine Vincent) 7 Exemple : Création des tables 1 CREATE TABLE tIntervenant ( 2 pknom varchar2(20) PRIMARY KEY, 3 prenom varchar2(20) NOT NULL, 4 poste number(4) 5 ); 6 7 CREATE TABLE tCours ( 8 pkannee number(4) check (pkannee>2000 and pkannee<2100), 9 pknum number(2), 10 titre varchar2(50), 11 type char(2) CHECK (type='C' or type='TD' or type='TP') NOT NULL, 12 fkintervenant varchar2(20) REFERENCES tIntervenant(pknom) NOT NULL, 13 debut date, 14 fin date, 15 PRIMARY KEY(pkannee, pknum) 16 ); Exemple : Utilisation du catalogue 1 SELECT table_name FROM user_tables; 2 DESCRIBE tCours; 1 TABLE_NAME 2 ------------------------------ 3 TINTERVENANT 4 TCOURS 5 6 Name Null Type 7 ------------- -------- ------------ 8 PKANNEE NOT NULL NUMBER(4) 9 PKNUM NOT NULL NUMBER(2) 10 TITRE VARCHAR2(50) 11 TYPE NOT NULL CHAR(2) 12 FKINTERVENANT NOT NULL VARCHAR2(20) 13 DEBUT DATE 14 FIN DATE Exemple : Création d'une séquence 1 CREATE SEQUENCE tCoursSeq; 2 3 SELECT sequence_name FROM user_sequences; 1 SEQUENCE_NAME 2 ------------------------ 3 TCOURSSEQ Exemple : Initialisation des données 1 INSERT INTO tIntervenant (pknom, prenom, poste) 2 VALUES ('CROZAT', 'Stéphane', '4287'); 3 4 INSERT INTO tCours (pkannee, pknum, titre, type, debut, fkintervenant) 5 VALUES ('2003', tCoursSeq.NEXTVAL, 'Introduction','C', '01-JAN- 2001', 'CROZAT'); 6 Théorie : Oracle Stéphane Crozat (Contributions : Dritan Nace, Yacine Challal, Antoine Vincent) 8 7 INSERT INTO tCours (pkannee, pknum, titre, type, debut, fkintervenant) 8 VALUES ('2003', tCoursSeq.NEXTVAL, 'Modélisation','TD', '02-JAN- 2001', 'CROZAT'); 9 10 SELECT pknum, pkannee, titre FROM tCours; 1 PKNUM PKANNEE TITRE 2 ----- ------- ----------------- 3 1 2003 Introduction 4 2 2003 Modélisation Exemple : Question avec concaténation 1 SELECT trim(prenom) || ' ' || trim(pknom) || ' (' || trim(poste) || ')' FROM tIntervenant; 1 TRIM(PRENOM)||''||TRIM(PKNOM)||'('||TRIM(POSTE)||')' 2 -------------------------------------------------------------------- ---------------- 3 STEPHANE CROZAT (4287) Exemple : Question avec CASE 1 SELECT pknum, 2 CASE 3 WHEN type='C' THEN 'Cours' 4 WHEN type='TD' THEN 'Travaux dirigés' 5 END AS type_label, 6 debut 7 FROM tCours 1 PKNUM TYPE_LABEL DEBUT 2 ----- --------------- --------- 3 1 Cours 01-JAN-01 4 2 Travaux dirigés 02-JAN-01 Exemple : Question avec date 1 SELECT pknum, TO_CHAR(debut, 'fmday') FROM tcours; 1 PKNUM TO_CHAR(DEBUT,'FMDAY') 2 ----- ---------------------- 3 1 monday 4 2 tuesday Exemple : Insert avec date 1 INSERT INTO tCours (pkannee, pknum, titre, type, debut, fkintervenant) 2 VALUES ('2003', tCoursSeq.NEXTVAL, 'Relationnel','C', TO_DATE('08- 01-2001','DD-MM-YYYY'), 'CROZAT'); 3 4 SELECT debut FROM tCours; 1 DEBUT Théorie : Oracle Stéphane Crozat (Contributions : Dritan Nace, Yacine Challal, Antoine Vincent) 9 2 --------- 3 01-JAN-01 4 02-JAN-01 5 08-JAN-01 Exemple : Fonction et affectation par une requête 1 CREATE OR REPLACE FUNCTION fIntervient (pIntervenant varchar2) 2 RETURN varchar2 3 IS 4 vNbInterventions number; 5 BEGIN 6 SELECT Count(fkintervenant) INTO vNbInterventions 7 FROM tCours 8 WHERE fkintervenant=pIntervenant; 9 10 IF vNbInterventions > 0 THEN 11 RETURN 'OUI'; 12 ELSE 13 RETURN 'NON'; 14 END IF; 15 END; 16 / 17 18 INSERT INTO tIntervenant(pknom, prenom) VALUES ('JOUGLET', 'Antoine'); 19 20 SELECT pknom, fIntervient(pknom) AS I FROM tIntervenant; 1 PKNOM I 2 -------------------- ------ 3 CROZAT OUI 4 JOUGLET NON Exemple : Curseur 1 CREATE OR REPLACE PROCEDURE pAfficheIntervenants1 2 IS 3 CURSOR cIntervenants IS 4 SELECT pknom, prenom FROM tIntervenant; 5 vNom tIntervenant.pknom%TYPE; 6 vPrenom tIntervenant.prenom%TYPE; 7 BEGIN 8 DBMS_OUTPUT.PUT_LINE('** Liste des intervenants 1 **'); 9 OPEN cIntervenants; 10 LOOP 11 FETCH cIntervenants INTO vNom, vPrenom; 12 EXIT WHEN cIntervenants%NOTFOUND; 13 DBMS_OUTPUT.PUT_LINE('-' || INITCAP(TRIM(vPrenom)) || ' ' || INITCAP(TRIM(vNom))); 14 END LOOP; 15 END; 16 / 17 18 CREATE OR REPLACE PROCEDURE pAfficheIntervenants2 19 IS 20 CURSOR cIntervenants IS 21 SELECT pknom, prenom FROM tIntervenant; 22 BEGIN 23 DBMS_OUTPUT.PUT_LINE('** Liste des intervenants 2 **'); 24 FOR i IN cIntervenants LOOP 25 DBMS_OUTPUT.PUT_LINE('-' || INITCAP(TRIM(i.prenom)) || ' ' || INITCAP(TRIM(i.pknom))); 26 END LOOP; Théorie : Oracle Stéphane Crozat (Contributions : Dritan Nace, Yacine Challal, Antoine Vincent) 10 27 END; 28 / 29 30 SET SERVEROUTPUT ON; 31 BEGIN 32 pAfficheIntervenants1; 33 DBMS_OUTPUT.PUT_LINE(''); 34 pAfficheIntervenants2; 35 END; 36 / 1 ** Liste des intervenants 1 ** 2 -Stéphane Crozat 3 -Antoine Jouglet 4 5 ** Liste des intervenants 2 ** 6 -Stéphane Crozat 7 -Antoine Jouglet Exemple : Gestion d'exception 1 CREATE OR REPLACE PROCEDURE pInsertIntervenant (pNom varchar2, pPrenom varchar2) 2 IS 3 BEGIN 4 INSERT INTO tIntervenant (pknom, prenom) 5 VALUES (pNom, pPrenom); 6 EXCEPTION 7 WHEN DUP_VAL_ON_INDEX then 8 DBMS_OUTPUT.PUT_LINE('Intervenant déjà existant : ' || pNom); 9 WHEN OTHERS THEN 10 RAISE; 11 END; 12 / 13 14 SET SERVEROUTPUT ON 15 16 BEGIN 17 DBMS_OUTPUT.PUT_LINE('*** Programme indépendant ***'); 18 pInsertIntervenant('JOUGLET', 'Antoine'); 19 pInsertIntervenant('VINCENT', 'Antoine'); 20 pAfficheIntervenants; 21 END; 22 / 1 *** Programme indépendant *** 2 Intervenant déjà existant : JOUGLET 3 ** Liste des intervenants 1 ** 4 -Stéphane Crozat 5 -Antoine Jouglet 6 -Antoine Vincent Exemple : Trigger de calcul de valeur dérivée 1 CREATE OR REPLACE TRIGGER trCours 2 BEFORE INSERT OR UPDATE OF debut ON tCours 3 FOR EACH ROW 4 BEGIN 5 :new.fin := :new.debut+5; 6 END; 7 / 8 9 UPDATE tCours Théorie : Oracle Stéphane Crozat (Contributions : Dritan Nace, Yacine Challal, Antoine Vincent) 11 10 SET debut=TO_DATE('15-01-2001','DD-MM-YYYY') 11 WHERE pknum=3; 12 13 INSERT INTO tCours (pkannee, pknum, titre, type, fkIntervenant, debut) 14 VALUES ('2003', tCoursSeq.NEXTVAL, 'SQL', 'C', 'CROZAT', TO_DATE('22- 01-2001','DD-MM-YYYY')); 15 16 SELECT pknum, debut, fin FROM tCours; 1 PKNUM DEBUT FIN 2 ----- --------- --------- 3 1 01-JAN-01 4 2 02-JAN-01 5 3 15-JAN-01 20-JAN-01 6 4 22-JAN-01 27-JAN-01 Exemple : Trigger d'archivage de données 1 CREATE TABLE tIntervenantSav ( 2 pknom varchar2(20) PRIMARY KEY, 3 prenom varchar2(20) NOT NULL 4 ); 5 6 CREATE OR REPLACE TRIGGER trIntervenant 7 BEFORE DELETE OR INSERT ON tIntervenant 8 FOR EACH ROW 9 BEGIN 10 IF DELETING THEN 11 INSERT INTO tIntervenantSav VALUES (:old.pknom, :old.prenom); 12 ELSIF INSERTING THEN 13 DELETE FROM tIntervenantSav WHERE pknom = :new.pknom; 14 END IF; 15 END; 16 / 17 18 DELETE FROM tCours; 19 DELETE FROM tIntervenant; 20 SELECT * FROM tIntervenantSav; 1 PKNOM PRENOM 2 -------------------- -------------------- 3 CROZAT Stéphane 4 JOUGLET Antoine 5 VINCENT Antoine 1 INSERT INTO tIntervenant (pknom, prenom, poste) 2 VALUES ('CROZAT', 'Stéphane', '4287'); 3 4 SELECT * FROM tIntervenantSav; 1 PKNOM PRENOM 2 -------------------- -------------------- 3 JOUGLET Antoine 4 VINCENT Antoine 2. Présentation  Le premier SGBDR commercialisé en 1979  Il occupe la première place dans le marché des SGBDR (avec uploads/Management/cours-et-exercices-oracle-pl-sql.pdf