PLSQL
et
Administratio Oracle
Compte Rendu :
C r é er Une Base De Donné es
Oracle 19c Manuellement
Filière d’ingénieur:
Ingénierie Logicielle
Et
Intégration des Systèmes Informtiques
Encadré par :
- Pr.O. EL BEGGAR
Réalisé par :
- BASKANE Ikram
A n né e universitaire : 2024-2025
PARTIE 1 : Administration ORACLE
2
Création d'une base de données
L'objectif de cette manipulation est de comprendre les étapes fondamentales de la création d'une
base de données Oracle, ainsi que de configurer manuellement ses composants essentiels à
partir de zéro (from scratch).
1. Création de l'arborescence de dossiers
Description : Les dossiers nécessaires à l'organisation des fichiers de la base de données ont été
créés avec les commandes mkdir dans une invite de commandes.
Figure 1:Arborescence de dossiers
Interprétation : Cette étape organise les emplacements des fichiers de journalisation, de
récupération et des paramètres de la base pour une meilleure gestion.
2. Définition des mots de passe dans SQL*Plus
Description : Utilisation des commandes ACCEPT dans SQL*Plus pour définir les mots de
passe des utilisateurs principaux (sys, system, sysman, dbsnmp).
3
Figure 2:Définition des mots de passe
Interprétation : La sécurité des utilisateurs est
primordiale. Ces mots de passe garantissent un accès contrôlé aux privilèges élevés.
3. Création de l’instance
Description : L’instance est créée avec les commandes ORADIM et configurée pour un
démarrage automatique.
Figure 3:Création de l’instance
4
Interprétation : L’instance représente les processus et la mémoire associés à la base. Elle doit
être disponible pour interagir avec la base de données.
4. Création du fichier de paramètres [Link]
Description : Le fichier [Link] est configuré pour définir les paramètres essentiels de la
base.
Figure 4:Création du fichier de paramètres [Link]
Interprétation :
Ce fichier contient les paramètres cruciaux comme le chemin des fichiers de contrôle et de
journalisation, essentiels au bon fonctionnement de la base.
5. Création du fichier spfile
Description : Le fichier spfile est créé à partir de pfile avec SQL*Plus.
5
Figure 5:Création du fichier spfile
Interprétation : Le spfile permet une gestion dynamique des paramètres sans redémarrage de
l’instance.
6. Création du fichier de mots de passe
Description : Le fichier de mots de passe est créé avec la commande orapwd.
Interprétation : Figure 6:Création du fichier de mots de passe Ce fichier est
nécessaire pour l’authentification des
utilisateurs disposant de privilèges élevés.
7. Création de la base de données
Description : Utilisation de la commande CREATE DATABASE avec les paramètres spécifiés.
6
Figure 7:Création de la base de données
Interprétation : Cette commande crée physiquement la base de données et ses composants
principaux.
8. Création de tablespaces supplémentaires
Description : Le tablespace USERS est créé pour stocker les objets utilisateur.
Figure 8:Création de tablespaces
Interprétation : Les tablespaces organisent les données pour une gestion efficace de l’espace.
9. Création du dictionnaire de données
Description : Les scripts SQL nécessaires sont exécutés pour initialiser le dictionnaire.
7
Figure 9: Vérification des fichiers de journaux
Figure 10: Vérification des fichiers de contrôle
Interprétation : Le dictionnaire de données stocke les métadonnées nécessaires au
fonctionnement de la base.
[Link]
Description : Activation du mode ARCHIVELOG pour la sauvegarde.
8
Figure 11:Activation du Mode ARCHIVELOG
Interprétation : Le mode ARCHIVELOG permet une récupération complète en cas de panne.
Conclusion :
Cette manipulation a permis de créer une base de données Oracle fonctionnelle, en configurant
tous ses composants manuellement pour une meilleure compréhension des concepts sous-
jacents.
9
PARTIE 2 :PL/SQL
10
TP Records & Curseurs
1. Créer les blocs PLSQL
11. Affichage de tous les informations initiales de l’employé
Bloc pl/sql :
SET SERVEROUTPUT ON;
ACCEPT matricule prompt 'Veuillez saisir la matricule de l employe :'
DECLARE
v_record employees%ROWTYPE;
BEGIN
SELECT EMPLOYEE_ID ,
FIRST_NAME ,
LAST_NAME ,
EMAIL ,
PHONE_NUMBER ,
HIRE_DATE ,
JOB_ID ,
SALARY ,
COMMISSION_PCT ,
MANAGER_ID ,
DEPARTMENT_ID
into v_record
from employees
where employee_id=&matricule;
DBMS_OUTPUT.PUT_LINE('Les informations de l employé :');
DBMS_OUTPUT.PUT_LINE('ID : ' || v_record.employee_id);
DBMS_OUTPUT.PUT_LINE('Nom : ' || v_record.first_name || ' ' || v_record.last_name);
DBMS_OUTPUT.PUT_LINE('Email : ' || v_record.email);
DBMS_OUTPUT.PUT_LINE('Téléphone : ' || v_record.phone_number);
DBMS_OUTPUT.PUT_LINE('Date d embauche : ' || TO_CHAR(v_record.hire_date, 'DD-MM-YYYY'));
DBMS_OUTPUT.PUT_LINE('Poste : ' || v_record.job_id);
11
DBMS_OUTPUT.PUT_LINE('Salaire : ' || v_record.salary);
DBMS_OUTPUT.PUT_LINE('Commission : ' || NVL(TO_CHAR(v_record.commission_pct), 'N/A'));
DBMS_OUTPUT.PUT_LINE('ID du manager : ' || NVL(TO_CHAR(v_record.manager_id), 'N/A'));
DBMS_OUTPUT.PUT_LINE('ID du Department : ' || NVL(TO_CHAR(v_record.department_id),
'N/A'));
Résultat:
Les informations de l employé :
ID : 150
Nom : Peter Tucker
Email : PTUCKER
Téléphone : 011.44.1344.129268
Date d embauche : 30-01-2005
Poste : SA_REP
Salaire : 10000
Commission : ,3
ID du manager : 145
ID du Department : 80
12. Modification de l’email et le salaire d’un employé
set SERVEROUTPUT on;
ACCEPT emp_mat PROMPT 'Veuillez saisir la matricule de l employe'
ACCEPT email PROMPT 'Veuillez saisir un nouveau email '
ACCEPT salaire PROMPT 'Veuillez saisir un nouveau salaire'
DECLARE
v_record employees%ROWTYPE;
BEGIN
if &salaire > 5000 then
update employees set salary=&salaire, email='&email'
12
where employee_id=&emp_mat;
commit;
SELECT EMPLOYEE_ID ,
FIRST_NAME ,
LAST_NAME ,
EMAIL ,
PHONE_NUMBER ,
HIRE_DATE ,
JOB_ID ,
SALARY ,
COMMISSION_PCT ,
MANAGER_ID ,
DEPARTMENT_ID INTO v_record
FROM employees
WHERE employee_id = &emp_mat;
DBMS_OUTPUT.PUT_LINE('Les informations de l employé :');
DBMS_OUTPUT.PUT_LINE('ID : ' || v_record.employee_id);
DBMS_OUTPUT.PUT_LINE('Nom : ' || v_record.first_name || ' ' || v_record.last_name);
DBMS_OUTPUT.PUT_LINE('Email : ' || v_record.email);
DBMS_OUTPUT.PUT_LINE('Téléphone : ' || v_record.phone_number);
DBMS_OUTPUT.PUT_LINE('Date d embauche : ' || TO_CHAR(v_record.hire_date, 'DD-MM-YYYY'));
DBMS_OUTPUT.PUT_LINE('Poste : ' || v_record.job_id);
DBMS_OUTPUT.PUT_LINE('Salaire : ' || v_record.salary);
DBMS_OUTPUT.PUT_LINE('Commission : ' || NVL(TO_CHAR(v_record.commission_pct), 'N/A'));
DBMS_OUTPUT.PUT_LINE('ID du manager : ' || NVL(TO_CHAR(v_record.manager_id), 'N/A'));
DBMS_OUTPUT.PUT_LINE('ID du Department : ' || NVL(TO_CHAR(v_record.department_id),
'N/A'));
else
DBMS_OUTPUT.PUT_LINE('Le salaire que vous avez entrer est inferieur a 5000');
END IF;
END;
Utilisation la variable RECORD
13
SET SERVEROUTPUT ON ;
ACCEPT v_matr PROMPT 'Veuillez saisir votre matricule'
ACCEPT v_email PROMPT 'Veuillez saisir votre nouveau emial'
ACCEPT v_salaire PROMPT 'Veuillez saisir votre nouveau salaire'
DECLARE
TYPE v_record IS RECORD(
Id_employe integer,
Email varchar(20),
Salaire Number(8,2));
employe v_record;
BEGIN
if( &Salair > 5000) THEN
update employees set email='&v_email',salary=&v_salaire
where employee_id=&v_matr;
select employee_id, email,salary INTO employe from employees
where employee_id=&v_matr;
DBMS_OUTPUT.PUT_LINE('Les informations de l employé :');
DBMS_OUTPUT.PUT_LINE('ID : ' || employe.Id_employe);
DBMS_OUTPUT.PUT_LINE('Email : ' || [Link]);
DBMS_OUTPUT.PUT_LINE('Salaire : ' || [Link]);
else
DBMS_OUTPUT.PUT_LINE('Le salaire est inferieure au 5000');
END IF;
END;
14
Résultat :
Les informations de l employé :
ID : 150
Email : email3
Salaire : 30000
13. Utilisation d’une variable de session
SET SERVEROUTPUT ON;
VARIABLE v_date char(15);
BEGIN
SELECT TO_CHAR( min(HIRE_DATE),'DD-MM-YYYY')
INTO :v_date
from employees;
END;
/
print v_date ;
V_DATE
--------------------------------------------------------------------------------
13-01-2001
2. utilise un curseur explicite
SET SERVEROUTPUT ON;
DECLARE
CURSOR curseur_dep IS
SELECT d.DEPARTMENT_ID, d.DEPARTMENT_NAME, d.MANAGER_ID, d.LOCATION_ID,
COUNT(e.EMPLOYEE_ID) AS nbEmploye
FROM employees e
JOIN departments d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
GROUP BY d.DEPARTMENT_ID, d.DEPARTMENT_NAME, d.MANAGER_ID, d.LOCATION_ID
HAVING COUNT(e.EMPLOYEE_ID) = (
15
SELECT COUNT(e2.EMPLOYEE_ID)
FROM employees e2
WHERE e2.DEPARTMENT_ID = 30
);
record_dep curseur_dep%ROWTYPE;
BEGIN
OPEN curseur_dep;
LOOP
FETCH curseur_dep INTO record_dep;
EXIT WHEN curseur_dep%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('DEPARTMENT_ID: ' || record_dep.DEPARTMENT_ID ||
', DEPARTMENT_NAME: ' || record_dep.DEPARTMENT_NAME ||
', MANAGER_ID: ' || record_dep.MANAGER_ID ||
', LOCATION_ID: ' || record_dep.LOCATION_ID ||
', NbEmployees: ' || record_dep.nbEmploye);
END LOOP;
CLOSE curseur_dep;
END;
/
Résultat :
DEPARTMENT_ID: 100, DEPARTMENT_NAME: Finance, MANAGER_ID: 108, LOCATION_ID: 1700,
NbEmployees: 6
DEPARTMENT_ID: 30, DEPARTMENT_NAME: Purchasing, MANAGER_ID: 114, LOCATION_ID: 1700,
NbEmployees: 6
16
3. Ajout d’un curseur dans plsql2
SET SERVEROUTPUT ON;
DECLARE
CURSOR curseur_dep IS
SELECT d.DEPARTMENT_ID, d.DEPARTMENT_NAME, d.MANAGER_ID, d.LOCATION_ID,
COUNT(e.EMPLOYEE_ID) AS nbEmploye
FROM employees e
JOIN departments d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
GROUP BY d.DEPARTMENT_ID, d.DEPARTMENT_NAME, d.MANAGER_ID, d.LOCATION_ID
HAVING COUNT(e.EMPLOYEE_ID) = (
SELECT COUNT(e2.EMPLOYEE_ID)
FROM employees e2
WHERE e2.DEPARTMENT_ID = 30
);
record_dep curseur_dep%ROWTYPE;
BEGIN
for record_dep IN curseur_dep LOOP
DBMS_OUTPUT.PUT_LINE('DEPARTMENT_ID: ' || record_dep.DEPARTMENT_ID ||
', DEPARTMENT_NAME: ' || record_dep.DEPARTMENT_NAME ||
', MANAGER_ID: ' || record_dep.MANAGER_ID ||
', LOCATION_ID: ' || record_dep.LOCATION_ID ||
', NbEmployees: ' || record_dep.nbEmploye);
END LOOP;
END;
/
Resultat:
DEPARTMENT_ID: 100, DEPARTMENT_NAME: Finance, MANAGER_ID: 108, LOCATION_ID: 1700,
NbEmployees: 6
DEPARTMENT_ID: 30, DEPARTMENT_NAME: Purchasing, MANAGER_ID: 114, LOCATION_ID: 1700,
NbEmployees: 6
4. Modifier la boucle FOR en utilisant un curseur avec paramètre « numdep »
17
SET SERVEROUTPUT ON;
ACCEPT depnum PROMPT 'Veuillez saisir le numero deparatemnet';
DECLARE
CURSOR curseur_dep(numdep NUMBER) IS
SELECT d.DEPARTMENT_ID, d.DEPARTMENT_NAME, d.MANAGER_ID, d.LOCATION_ID,
COUNT(e.EMPLOYEE_ID) AS nbEmploye
FROM employees e
JOIN departments d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
GROUP BY d.DEPARTMENT_ID, d.DEPARTMENT_NAME, d.MANAGER_ID, d.LOCATION_ID
HAVING COUNT(e.EMPLOYEE_ID) = (
SELECT COUNT(e2.EMPLOYEE_ID)
FROM employees e2
WHERE e2.DEPARTMENT_ID =&numdep
);
record_dep curseur_dep%ROWTYPE;
BEGIN
for record_dep IN curseur_dep(&depnum) LOOP
DBMS_OUTPUT.PUT_LINE('DEPARTMENT_ID: ' || record_dep.DEPARTMENT_ID ||
', DEPARTMENT_NAME: ' || record_dep.DEPARTMENT_NAME ||
', MANAGER_ID: ' || record_dep.MANAGER_ID ||
', LOCATION_ID: ' || record_dep.LOCATION_ID ||
', NbEmployees: ' || record_dep.nbEmploye);
END LOOP;
END;
Resultat:
DEPARTMENT_ID: 70, DEPARTMENT_NAME: Public Relations, MANAGER_ID: 204, LOCATION_ID:
2700, NbEmployees: 1
DEPARTMENT_ID: 10, DEPARTMENT_NAME: Administration, MANAGER_ID: 200, LOCATION_ID: 1700,
NbEmployees: 1
18
TP EXCEPTIONS
1. Affichage des infos d un employé avec gestion des erreurs
SET SERVEROUTPUT ON;
ACCEPT num_emp PROMPT 'Veuillez saisir le numero d employe '
DECLARE
TYPE emp_info IS RECORD (
employee_id employees.employee_id%TYPE,
first_name employees.first_name%TYPE,
last_name employees.last_name%TYPE,
salary [Link]%TYPE,
department_name departments.department_name%TYPE);
info_record emp_info;
BEGIN
select employee_id, first_name, last_name,salary, department_name INTO info_record
from employees e join departments d on e.department_id=d.department_id
where employee_id=&num_emp;
DBMS_OUTPUT.PUT_LINE('ID : ' || info_record.employee_id);
DBMS_OUTPUT.PUT_LINE('Nom : ' || info_record.first_name || ' ' || info_record.last_name);
DBMS_OUTPUT.PUT_LINE('Salaire : ' || info_record.salary);
DBMS_OUTPUT.PUT_LINE('Nom departement : ' || info_record.department_name);
EXCEPTION
WHEN NO_DATA_FOUND then
DBMS_OUTPUT.PUT_LINE('Erreur : il n y a aucune enregistrement avec ce numero !');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Erreur Inconnue');
END;
19
Resultat:
ID : 100
Nom : Steven King
Salaire : 24000
Nom departement : Executive
2. Lever l'exception si le numéro d’employé saisi est nul
SET SERVEROUTPUT ON;
ACCEPT num_emp PROMPT 'Veuillez saisir le numero d employe '
DECLARE
ID_empty EXCEPTION;
TYPE emp_info IS RECORD (
employee_id employees.employee_id%TYPE,
first_name employees.first_name%TYPE,
last_name employees.last_name%TYPE,
salary [Link]%TYPE,
department_name departments.department_name%TYPE);
info_record emp_info;
BEGIN
if '&num_emp' is NULL then
RAISE ID_empty;
END IF;
select employee_id, first_name, last_name,salary, department_name INTO info_record
from employees e join departments d on e.department_id=d.department_id
where employee_id=to_number('&num_emp');
DBMS_OUTPUT.PUT_LINE('ID : ' || info_record.employee_id);
DBMS_OUTPUT.PUT_LINE('Nom : ' || info_record.first_name || ' ' || info_record.last_name);
DBMS_OUTPUT.PUT_LINE('Salaire : ' || info_record.salary);
DBMS_OUTPUT.PUT_LINE('Nom departement : ' || info_record.department_name);
EXCEPTION
20
WHEN NO_DATA_FOUND then
DBMS_OUTPUT.PUT_LINE('Erreur : il n y a aucune enregistrement avec ce numero !');
WHEN ID_empty then
DBMS_OUTPUT.PUT_LINE('Erreur : Le champ saisit est vide !');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Erreur Inconnue');
END;
3. interdit la suppression de tout département qui contient des
employés
SET SERVEROUTPUT ON;
ACCEPT num_dep PROMPT 'Veuillez saisir le numero de departement a suprime '
DECLARE
Viol_Contraint EXCEPTION;
PRAGMA EXCEPTION_INIT(Viol_Contraint,-2292);
ID_empty EXCEPTION;
BEGIN
if '&num_dep' is NULL then
RAISE ID_empty;
END IF;
delete from departments where department_id =to_number('&num_dep');
EXCEPTION
WHEN ID_empty then
DBMS_OUTPUT.PUT_LINE('Erreur : Le champ saisit est vide !');
WHEN Viol_Contraint THEN
DBMS_OUTPUT.PUT_LINE('Impossible de supprimer cet departement il y a des employe s affecte
a cet departement ');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Erreur Inconnue');
END;
21
TP PROCEDURES ET FONCTIONS STOCKEES
Procédures stockées
1. procédure stockée « supprimer_employe »
SQL> CREATE OR REPLACE PROCEDURE supprimer_employe
(v_employeeid in emp.employee_id%TYPE)
as
begin
delete from emp where employee id=v_employeeid;
end supprimer_employe;
/
procidure crite.
ISQL> execute supprimer_employe(161)
Procldure PL/SQL terminle avec succPs.
ISQL> select * from emp where employee id=101;
aucune ligne sUlectionnle
soL>
2. procédure stockée « Comm_employe »
CREATE OR REPLACE PROCEDURE comm_employe (
v_noemploye IN employees.employee_id%TYPE,
v_txtcomm IN employees.commission_pct%TYPE
)
AS
v_comm employees.commission_pct%TYPE;
comm_null EXCEPTION;
BEGIN
-- Vérification de la commission actuelle
SELECT commission_pct INTO v_comm
FROM employees
WHERE employee_id = v_noemploye;
22
IF v_comm IS NULL THEN
RAISE comm_null;
END IF;
DBMS_OUTPUT.PUT_LINE('Commission avant mise à jour: ' || v_comm);
UPDATE employees
SET commission_pct = commission_pct + v_txtcomm
WHERE employee_id = v_noemploye;
SELECT commission_pct INTO v_comm
FROM employees
WHERE employee_id = v_noemploye;
DBMS_OUTPUT.PUT_LINE('Commission après mise à jour: ' || v_comm);
EXCEPTION
WHEN comm_null THEN
DBMS_OUTPUT.PUT_LINE('Erreur : La commission est NULL.');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Erreur : Aucun employé trouvé avec cet ID.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Erreur inconnue.');
END comm_employe;
Résultat:
ProcÚdure PL/SQL terminÚe avec succps.
SQL> execute comm_employe(v_noemploye=>102,v_txtcomm=>0.2) Erreur : La commis-
sion est NULL.
ProcÚdure PL/SQL terminÚe avec succps.
SQL> execute comm_employe(v_noemploye=>145,v_txtcomm=>0.2) Commission avant
mise à jour :,4
Commission après mise à jour :,6
ProcÚdure PL/SQL terminÚe avec succps.
SU
SQL> select commission_pct from employees where employee_id=145;
COMMISSION_PCT
--------------------------
,6
SQL>
23
3. Affichage du code source de la procédure « supprimer_employe »
24
25
4. Afficher la liste des objets de l’utilisateur en cours
26
5. Afficher toutes les tables de l’utilisateur en cours
27
6. Afficher la liste des procédures stockées par l’utilisateur en cours
Créer une procédure stockée qui permet de copier le contenu d’une table dans une nouvelle
vue.
Ecrire une procédure stockée «dep_nbremp » qui récupère le nombre d’employés qu’un
département donné contient dans un paramètre de sortie outnbremp de type Number.
28
Affichage les noms des n premiers employés d’un département donné
29
Fonction stockées
1. fonction stockée «max_sal_job »
CREATE OR REPLACE FUNCTION max_sal_job (
v_idjob IN employees.job_id%TYPE
) RETURN NUMBER
AS
v_max [Link]%TYPE := 0;
BEGIN
SELECT MAX(salary) INTO v_max
FROM employees
WHERE job_id = v_idjob;
RETURN v_max;
END max_sal_job;
/
Fonction crÚÚe.
Resultat
SQL> variable max_sal number
SQL> execute :max_sal := max_sal_job('SA_MAN')
ProcÚdure PL/SQL terminÚe avec succps.
SQL> print max_sal
MAX_SAL
14000
SQL>
2. fonction stockée «dep_nbremp »
CREATE OR REPLACE FUNCTION dep_nbremp (
v_num_dep IN employees.department_id%TYPE -- Paramètre d'entrée : ID du
département
) RETURN INTEGER
AS
v_nb_employe INTEGER;
BEGIN
SELECT COUNT(employee_id) INTO v_nb_employe
30
FROM employees
WHERE department_id = v_num_dep;
RETURN v_nb_employe;
END dep_nbremp;
/
Fonction crÚÚe.
Resultat:
VARIABLE cptemp NUMBER;
EXECUTE :cptemp := dep_nbremp(90);
PRINT cptemp;
CPTEMP
----------------------
3
3. Afficher la liste des fonctions stockées créées par tous les utilisateurs.
SELECT * from DBA_OBJECTS EHERE object_type=’FUNCTION’;
31
32
4. Afficher la liste des fonctions stockées par l’utilisateur en cours
33
5. Écrire une fonction qui permet d’insérer un enregistrement dans la table employés
et qui retourne le numéro d’employé inséré. l’insertion utilise une séquence.
34
TP sur les triggers
Exercice 1 :
table 'employees'
CREATE OR REPLACE TRIGGER employe_insert
BEFORE INSERT ON employees
FOR EACH ROW
AS
BEGIN
IF :[Link] < 5000 THEN
:[Link] := 5000;
END IF;
END;
/
Resultat:
35
Exercice 2:
Exercice 3:
36
Test exercice3 :
Exercice 4:
37
Exercice 5 :
1. Création de la base de donnée
SQL> CREATE USER UNIV IDENTIFIED BY note;
Utilisateur créé.
SQL> GRANT CONNECT, RESOURCE TO UNIV;
Autorisation de privileges (GRANT) acceptée.
SQL>ALTER USER UNIVERSITE QUOTA UNILIMTED ON USERS;
Utilisateur modifie.
SQL>CONN UNIVERSITE/note;
Connecté.
CREATE TABLE ETUDIANT (
numEtud INT PRIMARY KEY,
nom VARCHAR(50),
prenom VARCHAR(50),
datenaiss DATE,
civilite VARCHAR(4) CHECK (civilite IN ('Mr', 'Mme', 'Mlle')) NOT NULL,
numsecu VARCHAR(15) NOT NULL
);
SQL> CREATE TABLE MODULE ( codMod INT PRIMARY KEY, nomMod VARCHAR(100), effecMax
INT DEFAULT 30);
Table crÚÚe.
SQL> CREATE TABLE EXAMEN (
2 codMod INT,
3 codExam INT,
4 dateExam DATE,
5 PRIMARY KEY (codMod, codExam),
6 FOREIGN KEY (codMod) REFERENCES MODULE(codMod) -- Clé étrangère vers MODULE
7 );
Table crÚÚe.
38
SQL> CREATE TABLE INSCRIPTION ( numEtud INT, codMod INT, dateInsc DATE DEFAULT
CURRENT_DATE, PRIMARY KEY (numEtud, codMod), FOREIGN KEY (numEtud) REFERENCES
ETUDIANT(numEtud), FOREIGN KEY (codMod) REFERENCES MODULE(codMod));
Table crÚÚe.
SQL> CREATE TABLE PREREQUIS ( codMod INT, codModPrereq INT, noteMin DECIMAL(5, 2) NOT
NULL, PRIMARY KEY (codMod, codModPrereq), FOREIGN KEY (codMod) REFERENCES
MODULE(codMod), FOREIGN KEY (codModPrereq) REFERENCES MODULE(codMod));
Table crÚÚe.
SQL> CREATE TABLE RESULTAT (
2 codMod INT,
3 codExam INT,
4 numEtud INT,
5 note DECIMAL(5, 2),
6 PRIMARY KEY (codMod, codExam, numEtud),
7 FOREIGN KEY (codMod, codExam) REFERENCES EXAMEN(codMod, codExam),
FOREIGN KEY (numEtud) REFERENCES ETUDIANT(numEtud) );
Table crÚÚe.
2. Créer les triggers
→Interdit la modification de la note min dans la table de prerequis
CREATE OR REPLACE TRIGGER prerequit_Minupdate
BEFORE UPDATE of noteMin ON PREREQUIS
FOR EACH ROW
BEGIN
RAISE_APPLICATION_ERROR(-20001, 'Modification de la note minimale
interdite');
END;
/
39
→Dans un module, il ne doit pas y avoir plus de effecMax élèves inscrits.
→On ne peut créer un examen pour un module que s’il y a des élèves inscrits dans ce module.
40
→Un élève s’inscrivant à un module doit avoir eu au moins la note min à tous les modules pré-requis
→Créer une vue de résultats, qui contiendra le nom et la moyenne de chaque étudiant s’il a passé
les examens de tous les modules dans lesquels il est inscrit.
CREATE OR REPLACE VIEW VUE_RESULTATS AS
SELECT [Link], [Link], [Link], AVG([Link]) AS moyenne
FROM ETUDIANT E
JOIN RESULTAT R ON [Link] = [Link]
WHERE NOT EXISTS (
SELECT 1 FROM INSCRIPTION I
WHERE [Link] = [Link]
AND NOT EXISTS (
SELECT 1 FROM RESULTAT R2
WHERE [Link] = [Link]
AND [Link] = [Link]
)
GROUP BY [Link], [Link], [Link];
/
41
→Tester la solution :
NUMETUD NOM
---------- --------------------------------------------------
PRENOM MOYENNE
-------------------------------------------------- ----------
2 Ikram
BASKANE 17
3 Fadma
JADDA 18
→Revenez sur la première contrainte : il ne doit être possible de modifier une note min dans la table
prerequis que s’il n’existe pas d’élève dont une inscription serait invalidée.
CREATE OR REPLACE TRIGGER check_update_noteMin
BEFORE UPDATE OF noteMin ON PREREQUIS
FOR EACH ROW
DECLARE
nb_invalides NUMBER;
BEGIN
SELECT COUNT(*) INTO nb_invalides
FROM INSCRIPTION I
JOIN RESULTAT R ON [Link] = [Link] AND [Link] = :[Link]
WHERE [Link] = :[Link] AND [Link] < :[Link];
IF nb_invalides > 0 THEN
RAISE_APPLICATION_ERROR(-20006, 'Impossible de modifier noteMin car cela invaliderait
certaines inscriptions.');
END IF;
END;
42
→Tester la solution :
UPDATE PREREQUIS
SET noteMin = 12
WHERE codMod = 'MATH102'
AND codModPrereq = 'MATH101';
UPDATE PREREQUIS
*
ERREUR Ó la ligne 1 :
ORA-20006: Impossible de modifier noteMin car cela invaliderait certaines
inscriptions.
ORA-06512: Ó "UNIV.CHECK_UPDATE_NOTEMIN", ligne 9
ORA-04088: erreur lors d'exÚcution du dÚclencheur 'UNIV.CHECK_UPDATE_NOTEMIN'
→Il ne doit être possible de modifier effecMax que si des étudiants ne se retrouvent pas avec une
inscription invalidée.
CREATE OR REPLACE TRIGGER check_update_effecMax
BEFORE UPDATE OF effecMax ON MODULE
FOR EACH ROW
DECLARE
nb_inscrits NUMBER;
BEGIN
SELECT COUNT(*) INTO nb_inscrits
FROM INSCRIPTION
WHERE codMod = :[Link];
IF nb_inscrits > :[Link] THEN
RAISE_APPLICATION_ERROR(-20007, 'Impossible de modifier effecMax car il y a plus d''étudiants
inscrits que la nouvelle limite.');
END IF;
END;
43
→Tester la solution :
UPDATE MODULE SET effecMax = 1 WHERE codMod = 'PHY101';
UPDATE MODULE SET effecMax = 1 WHERE codMod = 'PHY101'
*
ERREUR Ó la ligne 1 :
ORA-20007: Impossible de modifier effecMax car il y a plus d'étudiants inscrits
que la nouvelle limite.
ORA-06512: Ó "UNIV.CHECK_UPDATE_EFFECMAX", ligne 9
ORA-04088: erreur lors d'exÚcution du dÚclencheur 'UNIV.CHECK_UPDATE_EFFECMAX'
44