Exercice : Gestion des intervenants [Technologie Oracle : Extensions procédurales à SQL]

Gestion des intervenants

SQL sous Oracle

Soit le MCD ci-après.

Question

Produisez le MLD relationnel.

Solution

tIntervenant (#pknom:varchar, prenom:varchar, poste:integer)
tCours (#pkannee:2000..2100, #pknum:integer, titre:varchar, type:C|TD|TP, fkintervenant=>tIntervenant, debut:date)

tIntervenant (#pknom:varchar, prenom:varchar, poste:integer)
tCours (#pkannee:2000..2100, #pknum:integer, titre:varchar, type:C|TD|TP, fkintervenant=>tIntervenant, debut:date)

Question

Produisez un code SQL qui fonctionne pour Oracle et pour PostgreSQL.

Solution

CREATE TABLE tIntervenant (
pknom VARCHAR(255) PRIMARY KEY,
prenom VARCHAR(255) NOT NULL,
poste INTEGER
);
CREATE TABLE tCours (
pkannee INTEGER check (pkannee>2000 and pkannee<2100),
pknum INTEGER,
titre VARCHAR(255),
type CHAR(2) CHECK (type='C' OR type='TD' OR type='TP') NOT NULL,
fkintervenant VARCHAR(255) REFERENCES tIntervenant(pknom) NOT NULL,
debut DATE,
PRIMARY KEY(pkannee, pknum)
);

CREATE TABLE tIntervenant (
pknom VARCHAR(255) PRIMARY KEY,
prenom VARCHAR(255) NOT NULL,
poste INTEGER
);

CREATE TABLE tCours (
pkannee INTEGER check (pkannee>2000 and pkannee<2100),
pknum INTEGER,
titre VARCHAR(255),
type CHAR(2) CHECK (type='C' OR type='TD' OR type='TP') NOT NULL,
fkintervenant VARCHAR(255) REFERENCES tIntervenant(pknom) NOT NULL,
debut DATE,
PRIMARY KEY(pkannee, pknum)
);

Remarque :

Sous Oracle il est recommandé d'utiliser le type VARCHAR2, mais pour conserver un code standard, on préférera le type standard VARCHAR.

Particularités LDD

Question

Produisez un code spécialisé pour Oracle.

Solution

CREATE TABLE tIntervenant (
pknom VARCHAR2(255) PRIMARY KEY,
prenom VARCHAR2(255) NOT NULL,
poste NUMBER(4)
);
CREATE TABLE tCours (
pkannee NUMBER(4) check (pkannee>2000 and pkannee<2100),
pknum INTEGER,
titre VARCHAR2(255),
type CHAR(2) CHECK (type='C' OR type='TD' OR type='TP') NOT NULL,
fkintervenant VARCHAR2(255) REFERENCES tIntervenant(pknom) NOT NULL,
debut DATE,
PRIMARY KEY(pkannee, pknum)
);

CREATE TABLE tIntervenant (
pknom VARCHAR2(255) PRIMARY KEY,
prenom VARCHAR2(255) NOT NULL,
poste NUMBER(4)
);

CREATE TABLE tCours (
pkannee NUMBER(4) check (pkannee>2000 and pkannee<2100),
pknum INTEGER,
titre VARCHAR2(255),
type CHAR(2) CHECK (type='C' OR type='TD' OR type='TP') NOT NULL,
fkintervenant VARCHAR2(255) REFERENCES tIntervenant(pknom) NOT NULL,
debut DATE,
PRIMARY KEY(pkannee, pknum)
);

Remarque :

On utilise le type VARCHAR2 recommandé par la documentation, et l'on peut aussi utiliser le type NUMBER pour spécifier des types numériques limités.

Question

Vérifier la création de vos tables dans le dictionnaire de données (existence de la table, description de la table, et liste des contraintes).

Indice

TABLE_NAME                   
------------------------------
TINTERVENANT                   
TCOURS                        

TABLE_NAME                   
------------------------------
TINTERVENANT                   
TCOURS

Indice

Name          Null     Type          
------------- -------- ------------- 
PKANNEE       NOT NULL NUMBER(4)     
PKNUM         NOT NULL NUMBER(38)    
TITRE                  VARCHAR2(255) 
TYPE          NOT NULL CHAR(2)       
FKINTERVENANT NOT NULL VARCHAR2(255) 
DEBUT                  DATE

Name          Null     Type          
------------- -------- ------------- 
PKANNEE       NOT NULL NUMBER(4)     
PKNUM         NOT NULL NUMBER(38)    
TITRE                  VARCHAR2(255) 
TYPE          NOT NULL CHAR(2)       
FKINTERVENANT NOT NULL VARCHAR2(255) 
DEBUT                  DATE

Indice

TABLE_NAME                     TYPE CONSTRAINT_NAME                SEARCH_CONDITION
------------------------------ ---- ------------------------------ ----------------------------------
TCOURS                         C    SYS_C00411567                  "FKINTERVENANT" IS NOT NULL
TCOURS                         C    SYS_C00411568                  pkannee>2000 and pkannee<2100
TCOURS                         C    SYS_C00411569                  type='C' OR type='TD' OR type='TP'
TCOURS                         C    SYS_C00411566                  "TYPE" IS NOT NULL
TCOURS                         P    SYS_C00411570
TCOURS                         R    SYS_C00411571
TINTERVENANT                   C    SYS_C00411564                  "PRENOM" IS NOT NULL
TINTERVENANT                   P    SYS_C00411565

TABLE_NAME                     TYPE CONSTRAINT_NAME                SEARCH_CONDITION
------------------------------ ---- ------------------------------ ----------------------------------
TCOURS                         C    SYS_C00411567                  "FKINTERVENANT" IS NOT NULL
TCOURS                         C    SYS_C00411568                  pkannee>2000 and pkannee<2100
TCOURS                         C    SYS_C00411569                  type='C' OR type='TD' OR type='TP'
TCOURS                         C    SYS_C00411566                  "TYPE" IS NOT NULL
TCOURS                         P    SYS_C00411570
TCOURS                         R    SYS_C00411571
TINTERVENANT                   C    SYS_C00411564                  "PRENOM" IS NOT NULL
TINTERVENANT                   P    SYS_C00411565

Solution

SELECT table_name FROM user_tables;
DESCRIBE tCours;
SELECT table_name, constraint_type AS type, constraint_name, search_condition
FROM   user_constraints
WHERE constraint_name NOT LIKE 'BIN$%'
ORDER BY table_name, constraint_type

SELECT table_name FROM user_tables;

DESCRIBE tCours;

SELECT table_name, constraint_type AS type, constraint_name, search_condition
FROM   user_constraints
WHERE constraint_name NOT LIKE 'BIN$%'
ORDER BY table_name, constraint_type

Question

Initialiser la base avec les données suivantes :

Stéphane Crozat est un intervenant, poste 4287.
Il fait deux cours, Introduction et Modélisation, le 1 janvier et le 8 janvier 2001.

Indice

PKANNEE P TITRE           TYPE FKINTERVENANT   DEBUT   
------- - --------------- ---- --------------- ---------
   2001 1 Introduction    C    CROZAT          01-JAN-01 
   2001 2 Modélisation    TD   CROZAT          08-JAN-01 

PKANNEE P TITRE           TYPE FKINTERVENANT   DEBUT   
------- - --------------- ---- --------------- ---------
   2001 1 Introduction    C    CROZAT          01-JAN-01 
   2001 2 Modélisation    TD   CROZAT          08-JAN-01

Solution

INSERT INTO tIntervenant (pknom, prenom, poste)
VALUES 	('CROZAT', 'Stéphane', '4287');
INSERT INTO tCours (pkannee, pknum, titre, type, debut, fkintervenant) 
VALUES 	(2001, 1, 'Introduction','C', TO_DATE('01-01-2001','DD-MM-YYYY'), 'CROZAT');
INSERT INTO tCours (pkannee, pknum, titre, type, debut, fkintervenant) 
VALUES 	(2001, 2, 'Modélisation','TD', TO_DATE('08-01-2001','DD-MM-YYYY'), 'CROZAT');
SELECT * FROM tCours;

INSERT INTO tIntervenant (pknom, prenom, poste)
VALUES 	('CROZAT', 'Stéphane', '4287');

INSERT INTO tCours (pkannee, pknum, titre, type, debut, fkintervenant) 
VALUES 	(2001, 1, 'Introduction','C', TO_DATE('01-01-2001','DD-MM-YYYY'), 'CROZAT');

INSERT INTO tCours (pkannee, pknum, titre, type, debut, fkintervenant) 
VALUES 	(2001, 2, 'Modélisation','TD', TO_DATE('08-01-2001','DD-MM-YYYY'), 'CROZAT');

SELECT * FROM tCours;

Question

Écrivez la requête SQL permettant de renvoyer pour chaque cours le jour de la semaine (lundi, mardi...) et le numéro de la semaine (1..53).

Indice

COURS DAY       WEEK
----- --------- ----
    1 monday    1    
    2 monday    2    

COURS DAY       WEEK
----- --------- ----
    1 monday    1    
    2 monday    2

Solution

SELECT pknum AS cours, TO_CHAR(debut, 'fmday') AS day, TO_CHAR(debut, 'fmww') AS week FROM tcours;

SELECT pknum AS cours, TO_CHAR(debut, 'fmday') AS day, TO_CHAR(debut, 'fmww') AS week FROM tcours;

Question

Écrivez la requête SQL permettant de renvoyer concaténés sur seule colonne le nom, le prénom et le poste entre parenthèse de chaque intervenant.

Indice

INTERVENANT
-------------------------------
STEPHANE CROZAT (4287)

INTERVENANT
-------------------------------
STEPHANE CROZAT (4287)

Solution

SELECT prenom || ' ' || pknom || ' (' || poste || ')' AS intervenant FROM tIntervenant;

SELECT prenom || ' ' || pknom || ' (' || poste || ')' AS intervenant FROM tIntervenant;

Question

Écrivez la requête SQL permettant de renvoyer la liste des cours avec leur type en toutes lettres : Cours, Travaux dirigés, Travaux pratiques.

Indice

COURS TYPE_LABEL      
----- -----------------
    1 Cours             
    2 Travaux dirigés   

COURS TYPE_LABEL      
----- -----------------
    1 Cours             
    2 Travaux dirigés

Solution

SELECT pknum AS cours,
  CASE 
    WHEN type='C' THEN 'Cours' 
    WHEN type='TD' THEN 'Travaux dirigés'
    WHEN type='TP' THEN 'Travaux pratiques'
  END AS type_label
FROM tCours

SELECT pknum AS cours,
  CASE 
    WHEN type='C' THEN 'Cours' 
    WHEN type='TD' THEN 'Travaux dirigés'
    WHEN type='TP' THEN 'Travaux pratiques'
  END AS type_label
FROM tCours

Question

Écrivez le code SQL*Plus et la requête permettant de renvoyer toutes les informations de la table tCours sur un minimum de caractères.

Indice

   A N COURS           T  INTERVENANT     DEBUT   
---- - --------------- -- --------------- ---------
2001 1 Introduction    C  CROZAT          01-JAN-01 
2001 2 Modélisation    TD CROZAT          08-JAN-01 

   A N COURS           T  INTERVENANT     DEBUT   
---- - --------------- -- --------------- ---------
2001 1 Introduction    C  CROZAT          01-JAN-01 
2001 2 Modélisation    TD CROZAT          08-JAN-01

Solution

COLUMN a FORMAT A4
COLUMN n FORMAT A1
COLUMN cours FORMAT A15
COLUMN t FORMAT A2
COLUMN intervenant FORMAT A15
SELECT 
  pkannee AS a,
  pknum AS n,
  titre AS cours,
  type AS T,
  fkintervenant AS intervenant,
  debut
FROM tCours;

COLUMN a FORMAT A4
COLUMN n FORMAT A1
COLUMN cours FORMAT A15
COLUMN t FORMAT A2
COLUMN intervenant FORMAT A15

SELECT 
  pkannee AS a,
  pknum AS n,
  titre AS cours,
  type AS T,
  fkintervenant AS intervenant,
  debut
FROM tCours;

PL/SQL sous Oracle

Pour la suite de l'exercice on introduira les données complémentaires suivantes dans la base de données.

INSERT INTO tIntervenant(pknom, prenom) VALUES ('JOUGLET', 'Antoine');

INSERT INTO tIntervenant(pknom, prenom) VALUES ('JOUGLET', 'Antoine');

Question

Écrivez une fonction PL/SQL prenant en paramètre un intervenant et renvoyant la chaîne 'OUI' s'il intervient au moins une fois dans un cours, et 'NON' sinon.

Indice

PKNOM                I     
-------------------- ------
CROZAT               OUI
JOUGLET              NON

PKNOM                I     
-------------------- ------
CROZAT               OUI
JOUGLET              NON

Indice

SELECT pknom, fIntervient(pknom) AS I FROM tIntervenant;

SELECT pknom, fIntervient(pknom) AS I FROM tIntervenant;

Indice

CREATE OR REPLACE FUNCTION fIntervient (...) 
  RETURN ...
IS
  ...;
BEGIN
  SELECT...
  IF...
END;
/

CREATE OR REPLACE FUNCTION fIntervient (...) 
  RETURN ...
IS
  ...;
BEGIN
  SELECT...

  IF...
END;
/

Solution

CREATE OR REPLACE FUNCTION fIntervient (pIntervenant VARCHAR2) 
  RETURN VARCHAR2
IS
  vNbInterventions NUMBER;
BEGIN
  SELECT COUNT(fkintervenant) INTO vNbInterventions
  FROM tCours 
  WHERE fkintervenant=pIntervenant;
  IF vNbInterventions > 0 THEN 
    RETURN 'OUI';
  ELSE
    RETURN 'NON';
  END IF;
END;
/
SELECT pknom, fIntervient(pknom) AS I FROM tIntervenant;

CREATE OR REPLACE FUNCTION fIntervient (pIntervenant VARCHAR2) 
  RETURN VARCHAR2
IS
  vNbInterventions NUMBER;
BEGIN
  SELECT COUNT(fkintervenant) INTO vNbInterventions
  FROM tCours 
  WHERE fkintervenant=pIntervenant;

  IF vNbInterventions > 0 THEN 
    RETURN 'OUI';
  ELSE
    RETURN 'NON';
  END IF;
END;
/

SELECT pknom, fIntervient(pknom) AS I FROM tIntervenant;

Question

Écrivez une procédure PL/SQL permettant d'afficher la liste de toutes les intervenants.

Indice

** Liste des intervenants **
-Stéphane Crozat
-Antoine Jouglet

** Liste des intervenants **
-Stéphane Crozat
-Antoine Jouglet

Indice

SET SERVEROUTPUT ON;
BEGIN
  pAfficheIntervenants1;
END;
/

SET SERVEROUTPUT ON;
BEGIN
  pAfficheIntervenants1;
END;
/

Indice

CREATE OR REPLACE PROCEDURE pAfficheIntervenants
IS
  CURSOR ...
  vNom ...
  vPrenom ...
BEGIN
  DBMS_OUTPUT.PUT_LINE...
  OPEN ...  
  LOOP
    FETCH...
    EXIT...
    DBMS_OUTPUT.PUT_LINE...
  END LOOP;
END;
/

CREATE OR REPLACE PROCEDURE pAfficheIntervenants
IS
  CURSOR ...
  vNom ...
  vPrenom ...
BEGIN
  DBMS_OUTPUT.PUT_LINE...
  OPEN ...  
  LOOP
    FETCH...
    EXIT...
    DBMS_OUTPUT.PUT_LINE...
  END LOOP;
END;
/

Solution

CREATE OR REPLACE PROCEDURE pAfficheIntervenants
IS
  CURSOR cIntervenants IS
    SELECT pknom, prenom FROM tIntervenant;
  vNom tIntervenant.pknom%TYPE;
  vPrenom tIntervenant.prenom%TYPE;
BEGIN
  DBMS_OUTPUT.PUT_LINE('** Liste des intervenants **');
  OPEN cIntervenants;  
  LOOP
    FETCH cIntervenants INTO vNom, vPrenom;
    EXIT WHEN cIntervenants%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('-' || INITCAP(TRIM(vPrenom)) || ' ' || INITCAP(TRIM(vNom)));
  END LOOP;
END;
/
SET SERVEROUTPUT ON;
BEGIN
  pAfficheIntervenants1;
END;
/

CREATE OR REPLACE PROCEDURE pAfficheIntervenants
IS
  CURSOR cIntervenants IS
    SELECT pknom, prenom FROM tIntervenant;
  vNom tIntervenant.pknom%TYPE;
  vPrenom tIntervenant.prenom%TYPE;
BEGIN
  DBMS_OUTPUT.PUT_LINE('** Liste des intervenants **');
  OPEN cIntervenants;  
  LOOP
    FETCH cIntervenants INTO vNom, vPrenom;
    EXIT WHEN cIntervenants%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('-' || INITCAP(TRIM(vPrenom)) || ' ' || INITCAP(TRIM(vNom)));
  END LOOP;
END;
/

SET SERVEROUTPUT ON;
BEGIN
  pAfficheIntervenants1;
END;
/

Question

Écrivez un trigger permettant de gérer la redondance entre pkannee et debut. L'on souhaite le comportement suivant :

si l'année de début du cours ne correspond pas à pkanne, alors la date de début du cours est mise à NULL
on ajoute un message sur le sortie standard.

Indice

SET SERVEROUTPUT ON;
INSERT INTO tCours (pkannee, pknum, titre, type, fkIntervenant, debut)
VALUES ('2001', 3, 'SQL', 'C', 'CROZAT', TO_DATE('15-01-2001','DD-MM-YYYY'));
UPDATE tCours
SET debut=TO_DATE('15-01-2002','DD-MM-YYYY')
WHERE pknum=3;
SELECT pkannee, pknum, debut FROM tCours;

SET SERVEROUTPUT ON;

INSERT INTO tCours (pkannee, pknum, titre, type, fkIntervenant, debut)
VALUES ('2001', 3, 'SQL', 'C', 'CROZAT', TO_DATE('15-01-2001','DD-MM-YYYY'));

UPDATE tCours
SET debut=TO_DATE('15-01-2002','DD-MM-YYYY')
WHERE pknum=3;

SELECT pkannee, pknum, debut FROM tCours;

TRIGGER trCours compiled
1 rows inserted.
1 rows updated.
Inconsistency between debut and pkannee, debut set to null
PKAN PKNUM DEBUT   
---- ----- ---------
2001     1 01-JAN-01 
2001     2 08-JAN-01 
2001     3            

TRIGGER trCours compiled
1 rows inserted.
1 rows updated.
Inconsistency between debut and pkannee, debut set to null

PKAN PKNUM DEBUT   
---- ----- ---------
2001     1 01-JAN-01 
2001     2 08-JAN-01 
2001     3

Indice

CREATE OR REPLACE TRIGGER trCours
BEFORE INSERT OR UPDATE OF debut ON tCours
FOR EACH ROW
DECLARE 
  vAnneeDebut INTEGER;
BEGIN
  vAnneeDebut := ...
  IF ...
END;
/

CREATE OR REPLACE TRIGGER trCours
BEFORE INSERT OR UPDATE OF debut ON tCours
FOR EACH ROW
DECLARE 
  vAnneeDebut INTEGER;
BEGIN
  vAnneeDebut := ...
  IF ...
END;
/

Solution

CREATE OR REPLACE TRIGGER trCours
BEFORE INSERT OR UPDATE OF debut ON tCours
FOR EACH ROW
DECLARE 
  vAnneeDebut INTEGER;
BEGIN
  vAnneeDebut := TO_NUMBER(TO_CHAR(:new.debut,'YYYY'));	
  IF NOT(vAnneeDebut = :new.pkannee) THEN
    :new.debut:=null;
    DBMS_OUTPUT.PUT_LINE('Inconsistency between debut and pkannee, debut set to null');
  END IF;
END;
/
SET SERVEROUTPUT ON;
INSERT INTO tCours (pkannee, pknum, titre, type, fkIntervenant, debut)
VALUES ('2001', 3, 'SQL', 'C', 'CROZAT', TO_DATE('15-01-2001','DD-MM-YYYY'));
UPDATE tCours
SET debut=TO_DATE('15-01-2002','DD-MM-YYYY')
WHERE pknum=3;
SELECT pkannee, pknum, debut FROM tCours;

CREATE OR REPLACE TRIGGER trCours
BEFORE INSERT OR UPDATE OF debut ON tCours
FOR EACH ROW
DECLARE 
  vAnneeDebut INTEGER;
BEGIN
  vAnneeDebut := TO_NUMBER(TO_CHAR(:new.debut,'YYYY'));	
  IF NOT(vAnneeDebut = :new.pkannee) THEN
    :new.debut:=null;
    DBMS_OUTPUT.PUT_LINE('Inconsistency between debut and pkannee, debut set to null');
  END IF;
END;
/

SET SERVEROUTPUT ON;

INSERT INTO tCours (pkannee, pknum, titre, type, fkIntervenant, debut)
VALUES ('2001', 3, 'SQL', 'C', 'CROZAT', TO_DATE('15-01-2001','DD-MM-YYYY'));

UPDATE tCours
SET debut=TO_DATE('15-01-2002','DD-MM-YYYY')
WHERE pknum=3;

SELECT pkannee, pknum, debut FROM tCours;