Fascicule de TP - SGBD Avancé [PDF]

Zitiervorschau

Ministère de l'Enseignement Supérieur et de la Recherche Scientifique Université de Gabes *-*-*-*-*

Institut Supérieur de l’Informatique de Médenine

Fascicule de travaux pratiques SGBD avancé

Réalisé par :

Dr Imed ABBASSI Enseignant chercheur à ISIM ENIT, UR-OASIS, Université de Tunis El Manar, Tunis [email protected]

1

PRÉFACE

Le présent fascicule de travaux pratiques (TP) de SGBD avancé est à l’intention des étudiants de la première année en master professionnel en Ingénierie des Logiciels et Connaissances de l’Institut Supérieur de l'Informatique de Medenine (ISIM). L’objectif de ce fascicule est d’étudier et matérialiser les concepts de PL/SQL présentés dans le cours avec le SGBD Oracle.

Le fascicule comporte cinq TP avec leurs corrections qui sont réparties comme suit : •

TP 1 : Initiation à l’Oracle 10g et création d’une base de données.

•

TP 2 : Interrogation de bases de données.

•

TP 3 : La manipulation des blocs PL/SQL et des curseurs.

•

TP4 : La manipulation des sous-programmes PL/SQL (les procédures et les fonctions stockées et temporaires).

•

TP 5 : La manipulation des packages, la gestion des exceptions et les triggers.

Dans ces TP, nous utilisons les outils suivants : •

Database 10g Express Edition

•

Oracle SQL developer

2

Institut Supérieur d’Informatique de Médenine

Matière : SGBD avancé TP n°1

Enseignant : Dr ABBASSI Imed

Groupe : MP1 ILC

Partie I: L’objectif de cette partie est de savoir créer un compte utilisateur sous Oracle 10g. Ce compte sera utilisateur tout au long de ce TP. Pour cela, il faut suivre les étapes suivantes :

1) Accédez à la page d’accueil de la base de données. 2) Se connectez en tant qu’administrateur : - Username: system - Password: Isimed@2018 3) Créez votre propre compte (un nouvel utilisateur BD). Pour cela : 4) Cliquez sur l’icône Administration, ensuite cliquez sur utilisateurs de bases de données. 5) Donnez un nom à votre propre compte et définissez les privilèges requis. 6) Cliquez sur le bouton « Créer ». 7) Saisir un login et un mot de passe et cocher toutes les informations nécessaires. 8) Se déconnecter (cliquer sur le Bouton « Déconnexion » dans le coin supérieur droit de la page) et reconnecter avec votre propre login et mot de passe que vous venez de créer. 9) Pour changer un mot de passe oublié, on peut taper les commandes suivantes à travers l'invite de commandes : - Tapez connect / as sysdba; - Ensuite, tapez alter user {system|username} identified by new_password ;

Partie II: On considère une base de données nommé TP1 dont le schéma relationnel est comme suit : - EMP (Matr, NomE, Poste, DateEmb, Sup#, Salaire, Comm, NumDept#) - DEPT(NumDept, NomDept, Lieu) - PROJET (CodeP, NomP) - PARTICIPATION (Matr #, CodeP#, Fonction) Où : - Tout attribut souligné de chaque relation désigne une clé primaire, tandis que celui précédé par le symbole # indique une clé étrangère. - L’attribut « Sup » désigne le matricule du supérieur direct de l’employé. Cet attribut peut être NULL. Il s’agit d’une clé étrangère de la table EMP. - Les types des attributs sont décrits comme suit : o CodeP varchar(4) o NomP varchar(15) o NumDept number(5) o NomDept varchar(15)

3

o o o o o o o o o

Lieu varchar(20) Matr umber(4) NomE varchar(15) Poste varchar(20) DateEmb date Sup number(4) Salaire number(8,3) Comm number(8,3) Fonction varchar(15)

Question 1: Définissez les tables de la base de données sans introduire les contraintes d’intégrité. Question 2: Remplissez les tables DEPT et EMP avec les données décrites par les tableaux ci-dessous: DEPT NumDept 10 20 30 40

NomDept Financier Personnel Administratif Juridique

Lieu Sousse Tunis Sfax Sousse

PROJET CodeP NomP P01 Sports P02 Commercialisation P03 Conception

EMP Matr 1020 1030 1040 1050

NomE Belhadj Zarrouk Kallel Zouari

Poste Commercial DAF Secrétaire DG

DateEmb 06/01/02 01/06/02 02/03/01 01/01/99

Sup 1050 1050 1030

Salaire 6000 10000 2500 20000

Comm 2000 5000 8000

NumDept 20 10 10 20

PARTICIPATION Matr CodeP Fonction 1020 P02 Chef 1050 P02 Trésorier 1030 P01 Programmeur 1040 P01 Commercial Question 3: Testez l’insertion de tuples incohérents vis-à-vis des contraintes d’intégrité: a) L'Insertion d'un nouvel employé attaché à un département qui n’existe pas. b) L’insertion d'un département qui dispose du même identifiant d’un autre existant dans la base. Question 4: Suite à ces insertions, que peut-on dire sur la cohérence des données dans la base créée ? Question 5: Ajoutez à toutes les tables les contraintes d’intégrité concernant les clés primaires. Question 6: Corrigez ou supprimer les tuples incorrects. Question 7: Ajoutez à toutes les tables les contraintes d’intégrité concernant les clés étrangères.

4

Question 8: Écrivez un script (évolution.sql) qui contient les instructions nécessaires pour ajouter les colonnes suivantes (avec ALTER TABLE). Le contenu de ces colonnes sera modifié ultérieurement. Table EMP DEPT Projet

Nom, type et signification des nouvelles colonnes nbProjets NUMBER(2): Le nombre de projets d’un employé. NbEmps NUMBER(2):Le nombre d’employés d’un département. durée NUMBER(2) : La durée de réalisation d’un projet (nombre de mois).

Question 9: Vérifiez la structure et le contenu de chaque table avec DESC et SELECT. Question 10: Dans ce même script (évolution.sql), ajoutez les instructions nécessaires pour: •

augmenter la taille de la colonne nomE de la table EMP à VARCHAR(30).

•

diminuer la taille de la colonne codeP de la table Projet à VARCHAR(3).

•

tenter de diminuer la taille de la colonne nomP de la table Projet à VARCHAR(14).

Question 11: Pourquoi la commande n’est-elle pas possible ? Question 12: Vérifiez par DESC la nouvelle structure des deux tables.

5

Institut Supérieur d’Informatique de Médenine

Matière : SGBD avancé Correction du TP n°1

Enseignant : Dr ABBASSI Imed

Groupe : MP1 ILC

Partie II: Question 1: --EMP (Matr, NomE, Poste, DateEmb, Sup, Salaire, Comm, NumDept#) CREATE TABLE DEPT( NumDept Numeric(5) not null, NomDept varchar(15), Lieu varchar(20) ); --DEPT(NumDept, NomDept, Lieu) CREATE TABLE EMP( Matr Numeric(4) not null, NomE varchar(15), Poste varchar(20), DateEmb date, Sup Numeric(4), Salaire Numeric(8,3), Comm Numeric(8,3), NumDept Numeric(5) not null ); --PROJET (CodeP, NomP) CREATE TABLE PROJET( CodeP varchar(4) not null, NomP varchar(25) ); --PARTICIPATION (Matr #, CodeP#, Fonction) CREATE TABLE PARTICIPATION( Matr Numeric(4) not null, CodeP varchar(4) not null, Fonction varchar(15) );

Question 2: INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO

DEPT VALUES (10,'Financier','Sousse'); DEPT VALUES (20,'Personnel','Tunis'); DEPT VALUES (30,'Administratif','Sfax'); DEPT VALUES (40,'Juridique','Sousse'); EMP VALUES (1020,'Belhadj','Commercial','06/01/02',1050,6000,2000,20); EMP VALUES (1030,'Zarrouk','DAF','01/06/02',1050,10000,5000,10); EMP VALUES (1040,'Kallel','Secrétaire','02/03/01',1030,2500,NULL,10); EMP VALUES (1050,'Zouari','DG','01/01/99',NULL,20000,8000,20); PROJET VALUES ('P01','Sports'); PROJET VALUES ('P02','Commercialisation'); PROJET VALUES ('P03','Conception'); PARTICIPATION VALUES(1020,'P02','Chef'); PARTICIPATION VALUES(1050,'P02','Trésorier'); PARTICIPATION VALUES(1030,'P01','Programmeur');

6

INSERT INTO PARTICIPATION VALUES(1040,'P01','Commercial');

Question 3: -- Insertion d'un nouvel employé attaché à un département qui n’existe pas INSERT INTO EMP VALUES (1070,'Belhadj','Commercial','06/01/02',NULL,6000,2000,80); /* Insertion un département qui dispose du même identifiant d’un autre existant dans la base. */ INSERT INTO EMP VALUES (1070,'Belhadj','Commercial','06/01/02',1090,6000,2000,80);

Question 4: Il est claire que l’état de la base de données est incohérent à cause de l’absence de vérification des contraintes d’intégrité (clés primaires et clés étrangères).

Question 5: ALTER ALTER ALTER ALTER

TABLE TABLE TABLE TABLE

DEPT ADD CONSTRAINT pkeyDEPT PRIMARY KEY(NumDept); EMP ADD CONSTRAINT pkeyEMP PRIMARY KEY(Matr); PARTICIPATION ADD CONSTRAINT pkeyPART PRIMARY KEY(Matr,CodeP); PROJET ADD CONSTRAINT pkeyProjet PRIMARY KEY(CodeP);

Question 6: Il s’agit de supprimer les tuples ajoutés dans la question 3. DELETE FROM EMP WHERE MATR=1070; DELETE FROM EMP WHERE MATR=1020 AND NUMDEPT=20 AND POSTE='Commercial';

Question 7: ALTER TABLE EMP ADD CONSTRAINT fkeyEMP FOREIGN KEY(NumDept) REFERENCES DEPT(NumDept); ALTER TABLE PARTICIPATION ADD CONSTRAINT fkeyPART1 FOREIGN KEY(Matr) REFERENCES EMP(Matr); ALTER TABLE PARTICIPATION ADD CONSTRAINT fkeyPART2 FOREIGN KEY(CodeP) REFERENCES PROJET(CodeP); ALTER TABLE EMP ADD CONSTRAINT fkSup FOREIGN KEY (Sup) REFERENCES EMP(Matr);

Question 8: ALTER TABLE EMP ADD (nbProjets NUMERIC(2) DEFAULT 0); ALTER TABLE DEPT ADD nbEmps NUMERIC(2) DEFAULT 0; ALTER TABLE Projet ADD durée NUMERIC(2);

Question 9: DESC EMP; DESC DEPT; DESC Projet; SELECT * FROM EMP; SELECT * FROM DEPT; SELECT * FROM Projet;

Question 10: ALTER TABLE EMP MODIFY nomE VARCHAR(30); ALTER TABLE Projet MODIFY nomP VARCHAR(14); ALTER TABLE Projet MODIFY codeP VARCHAR(3);

7

Question 11: Une erreur est survenue lors de l’exécution de la deuxième commande SQL (modification de la colonne nomP de la table Projet). En effet, lorsque la table n’est pas vide, il n’est pas possible de diminuer la taille de certains attributs. Il est possible de trouver des tuples dont la taille des valeurs d’attribut à modifier est plus grande que nouvelle taille (dans notre cas, la nouvelle taille est 14). Il y a des projets existants dont le nom dépasse le 14. Pour cette raison SQL n’autorise pas ce type de modification de la structure.

Question 12: DESC EMP; DESC Projet;

8

Institut Supérieur d’Informatique de Médenine

Matière : SGBD avancé TP n°2

Enseignant : Dr ABBASSI Imed

Groupe : MP1 ILC

On considère la base de données du TP1 et composé des relations suivantes : - EMP (Matr, NomE, Poste, DateEmb, Sup, Salaire, Comm, NumDept#) - DEPT(NumDept, NomDept, Lieu) - PROJET (CodeP, NomP) - PARTICIPATION (Matr #, CodeP#, Fonction) Question 1: Écrivez un script nommé requêtesSimples.sql, permettant d’extraire, à l’aide d’instructions SELECT, les données suivantes: Q1. La liste des employés. Q2. Le nombre des employés de chaque département. Q3. Le nombre des employés participés à chaque projet. Q4. Les noms des employés dont la deuxième lettre du nom est un E. Q5. Le poste des employés ayant un salaire supérieur à 13000 DT. Q6. Les noms des employés, dont le salaire inférieur à la commission. Q7. Les noms des employés qui ne touchent pas de commission. Question 2: Écrivez un script nommé requêtesModification.sql, permettant de réaliser les tâches suivantes: Q1. Créer un nouveau département : 50, Comptabilité, Tunis Q2. Créer un nouveau département : 60, Direction Q3. Changer la ville du département 60 à Sousse. Q4. Promouvoir les PDG, dans le département 60. Q5. Augmenter de 10% le salaire de tous les employés du département 10. Q6. Ajouter l’employé M. Pascal qui vient d’être recruté comme un commercial au salaire de 1200DT et d’être enregistré sous le matricule 8010 dans le département 30. Question 3: Écrivez un script nommé requêtesJointure.sql, permettant d’effectuer les requêtes suivantes: Q1. Noms des employés et lieu où ils travaillent. Q2. Noms et Lieux des départements dans lesquels des employés touchent une commission. Q3. Noms des supérieurs directs de chaque employé qui a un supérieur (nom des employés qui ont un supérieur, suivi du nom du supérieur). Q4. Noms des projets où sont inscrits des employés des départements de Sousse.

9

Institut Supérieur d’Informatique de Médenine

Matière : SGBD avancé Correction du TP n°2

Enseignant : Dr ABBASSI Imed

Groupe : MP1 ILC

Question 1: --Q1select * from emp; --Q2select NumDept, count(matr) as Nombre_emp from emp group by NumDept; --Q3select CodeP, count(matr) as Nombre_emp from PARTICIPATION group by CodeP; --Q4select nomE from emp where nomE like '_E%'; --Q5select distinct poste from emp where salaire > 13000; --Q6select nomE from emp where salaire < comm; --Q7select nomE from emp where comm is NULL ;

Question 2: --Q1 insert into dept (NumDept, NomDept, lieu) VALUES (50 , 'Comptabilité', 'Tunis'); --Q2 insert into dept (NumDept, NomDept) VALUES (60,'Direction'); --Q3 update Dept set lieu='Sousse' where NumDept = 60; --Q4 update emp set poste='PDG', Numdept = 60 ; --Q5 update emp set salaire=salaire*1.1 where Numdept = 60 ; --Q6

10

insert into emp(Matr, NomE, Poste, Salaire, NumDept) values (8010, 'Pascal', 'Commercial',1200,30)

Question 3: --Q1 select NomE, Lieu from emp, dept where emp.NumDept=dept.NumDept; --Q2 select distinct Lieu from emp, dept where emp.NumDept=dept.NumDept and emp.comm is not null; --Q3 select e.NomE, s.NomE from emp e, emp s where e.Sup = s.Matr; --Q4 select distinct NomP from emp, dept, participation, projet where participation.CodeP = projet.CodeP and emp.Matr = participation.Matr and emp.NumDept = dept.NumDept and dept.Lieu = 'Sousse'; --Avec des sous-interrogations: select NomP from projet where CodeP in (select codeP from participation where Matr in (select Matr from emp where NumDept in (select NumDept from dept where Lieu = 'Sousse')));

11

Institut Supérieur d’Informatique de Médenine

Matière : SGBD avancé TP n°3

Enseignant : Dr ABBASSI Imed

Groupe : MP1 ILC

Soit la base de données échantillon distribuée avec Oracle et décrite par son contenu ci-dessous :

- DEPT (DEPTNO, DNAME, LOC) - EMP (EMPNO, ENAME, FONCTION, HIREDATE, SAL, COMM, #DEPTNO) On notera que :

- Les clés primaires sont soulignées - Les clés étrangères sont précédées par le symbole dièse (#). - La création explicite d’un point de restauration (SAVEPOINT) avant le début d’une transaction de modification de la base de données est fortement recommandée. Celle-ci est réalisée en utilisant la commande suivante : SAVEPOINT SPi; Question 1: Définissez en utilisant l’outil SQL*Plus d’oracle les tables DEPT et EMP. N’oubliez pas à définir les clés primaires et étrangères en tant que contraintes nommées. Question 2 : Remplissez les tables DEPT et EMP avec les données décrites par les tableaux ci-dessous:

a) Données de la table EMP DEPTNO 10 20 30 40 50 60

DNAME Financier Recherche Commercial Informatique Génie industriel Génie mécanique

LOC Tunis Sousse Tunis Sfax Tunis Monastir

b) Données de la table DEPT: EMPNO

ENAME

7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934

Sassi Kamel Ben Saleh Med Jebali Ali Touati Med Mefteh Moez Ajili Sadok Sabri Saber Hani Monia Hmida Sami Hafsa Moncef Halima Kais Nafti Hamdi Kassem Saleh Youssef Salem

FONCTION HIREDATE SAL COMM DEPTNO Ouvrier Vendeur Vendeur Directeur Vendeur Directeur Directeur Chercheur PDG Vendeur Ouvrier Ouvrier Chercheur Ouvrier

17/12/80 20/02/81 22/02/81 02/04/81 28/09/81 01/05/81 09/06/81 09/11/81 17/11/81 08/09/81 23/09/81 03/12/81 03/12/81 23/01/82

250 500 550 1500 500 1400 1550 1000 3000 500 220 240 950 250

NULL 250 500 NULL 1400 NULL NULL NULL NULL NULL NULL NULL NULL NULL

20 30 30 20 30 30 10 20 10 30 20 30 20 10

Question 3 : Changez la localisation (LOC) du département commercial de Tunis à Ben Arous.

12

Question 4 : Dans EMP, augmentez de 10 % le salaire (SAL) des vendeurs dont la commission (COMM) est supérieure à 50 % du salaire. Question 5 : Dans EMP, attribuez aux employés en poste avant le ‘01-01-82’ (HIREDATE) et ayant une commission non spécifiée (NULL) une commission égale à la moyenne des commissions. Question 6 : Annulez les trois mises à jour précédentes. Question 7 : Écrivez un bloc PL/SQL qui:

- déclare un curseur explicite paramétré (P_Deptno et P_Fonction) ramenant les identifiants, les noms et les salaires des employés travaillant dans le département P_Deptno et ayant la fonction P_Fonction. - affiche les lignes des curseurs. a) Proposez une première version utilisant une boucle LOOP. b) Proposez une deuxième version utilisant une boucle FOR. Question 8 : Écrivez un bloc PL/SQL qui permet de trouver l’employé le mieux payé.

a) Proposez une première version utilisant une boucle LOOP. b) Proposez une deuxième version utilisant une boucle FOR. Question 9 : Écrivez un bloc PL/SQL qui permet de trouver les employés dont les rémunérations (SAL+COMM éventuelle) sont supérieures à 1000.

a) Proposez une première version utilisant une boucle LOOP. b) Proposez une deuxième version utilisant une boucle FOR. Question 10 : Écrivez un bloc PL/SQL qui calcule le total de toutes les rémunérations (SAL+COMM éventuelle) des employés du département 20. Ce bloc doit également calculer le nombre d’employés dont le salaire est supérieur à 800 et le nombre de ceux ayant des commissions supérieures à leur salaire.

a) Proposez une première version utilisant une boucle LOOP. b) Proposez une deuxième version utilisant une boucle FOR. Question 11 : Étendez la table DEPT par une nouvelle colonne NBEMP NUMBER(2) représentant le nombre d’employés (0 par défaut). Question 12 : Écrivez un bloc PL/SQL qui met à jour la colonne NBEMP pour tous les départements.

a) Proposer une première version avec un curseur implicite de mise à jour de la table DEPT. b) Proposer une deuxième version avec un curseur explicite de mise à jour de la table DEPT.

13

Institut Supérieur d’Informatique de Médenine

Matière : SGBD avancé Correction du TP n°3

Enseignant : Dr ABBASSI Imed

Groupe : MP1 ILC

Question 1:

-- Création de la table

DEPT

CREATE TABLE DEPT ( DEPTNO NUMBER(10) NOT NULL, DNAME VARCHAR2(20), LOC VARCHAR2(20), CONSTRAINT pkeyDEPT PRIMARY KEY(DEPTNO) ); -- Création de la table EMP CREATE TABLE EMP( EMPNO NUMBER(10) NOT NULL, ENAME VARCHAR2(20), FONCTION VARCHAR2(20), HIREDATE DATE, SAL REAL, COMM REAL, DEPTNO NUMBER(10) NOT NULL, CONSTRAINT pkeyEMP PRIMARY KEY(EMPNO), CONSTRAINT kkeyEMP FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO) ); Question 2 :

-- Script SQL d’insertion des données de la table DEPT BEGIN INSERT INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO INTO

DEPT DEPT DEPT DEPT DEPT DEPT

VALUES(10,'Financier','Tunis'); VALUES(20,'Recherche','Sousse'); VALUES(30,'Commercial','Tunis'); VALUES(40,'Informatique','Sfax'); VALUES(50,'Génie industriel','Tunis'); VALUES(60,'Génie mécanique','Monastir');

END ; -- Script SQL d’insertion des données de la table EMP BEGIN INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO INTO INTO INTO

EMP EMP EMP EMP EMP EMP EMP EMP

VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES

(7369,'Sassi Kamel','Ouvrier','17-12-80',250,NULL,20); (7499,'Ben Saleh Med','Vendeur','20-02-81',500,250,30); (7521,'Jebali Ali','Vendeur','22-02-81',550,500,30); (7566,'Touati Med','Directeur','02-04-81',1500,NULL,20); (7654,'Mefteh Moez','Vendeur','28-09-81',500,1400,30); (7698,'Ajili Sadok','Directeur','01-05-81',1400,NULL,30); (7782,'Sabri Saber','Directeur','09-06-81',1550,NULL,10); (7788,'Hani Monia','Chercheur','09-11-81',1000,NULL,20);

14

INSERT INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO INTO

EMP EMP EMP EMP EMP EMP

VALUES VALUES VALUES VALUES VALUES VALUES

(7839,'Hmida Sami','PDG','17-11-81',3000,NULL,10); (7844,'Hafsa Moncef','Vendeur','08-09-81',500,NULL,30); (7876,'Halima Kais','Ouvrier','23-09-81',220,NULL,20); (7900,'Nafti Hamdi','Ouvrier','03-12-81',240,NULL,30); (7902,'Kassem Saleh','Chercheur','03-12-81',950,NULL,20); (7934,'Youssef Salem','Ouvrier','23-01-82',250,NULL,10);

END;

Question 3 : BEGIN SAVEPOINT SP1; UPDATE DEPT SET LOC='Ben Arous' WHERE DNAME= 'Commercial'; END ; Question 4 : BEGIN SAVEPOINT SP2; UPDATE EMP SET SAL=SAL+SAL*0.1 WHERE FONCTION='Vendeur' AND COMM >= SAL*0.5; END; Question 5 : Dans EMP, attribuez aux employés en poste avant le ‘01-01-82’ (HIREDATE) et ayant une commission non spécifiée (NULL) une commission égale à la moyenne des commissions. BEGIN SAVEPOINT SP3; UPDATE EMP SET COMM= (SELECT AVG(COMM) FROM EMP WHERE COMM IS NOT NULL) WHERE COMM IS NULL; END; Question 6 : Annulez les trois mises à jour précédentes. ROLLBACK TO SAVEPOINT SP1; Question 7 :

--une première version utilisant une boucle LOOP. DECLARE CURSOR v_emp_cursor(P_Deptno EMP.Deptno%TYPE, P_Fonction EMP.FONCTION%TYPE) IS SELECT EMP.Empno, EMP.FONCTION, EMP.SAL FROM EMP WHERE EMP.Deptno=P_Deptno AND EMP.FONCTION LIKE P_Fonction; emp_record v_emp_cursor%ROWTYPE; BEGIN OPEN v_emp_cursor(20,'Ouvrier'); DBMS_OUTPUT.PUT_LINE(RPAD('No.Emp',10)||RPAD('FONCTION',10)||LPAD('SALAIRE',10)); LOOP FETCH v_emp_cursor INTO emp_record; EXIT WHEN v_emp_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE(RPAD(emp_record.Empno,10)||RPAD(emp_record.FONCTION,10)||LPAD(emp_record.SAL,10)); END LOOP;

15

CLOSE v_emp_cursor; END;

-- Une deuxième version utilisant une boucle FOR. SET SERVEROUTPUT ON; DECLARE CURSOR v_emp_cursor(P_Deptno EMP.Deptno%TYPE, P_Fonction EMP.FONCTION%TYPE) IS SELECT EMP.Empno, EMP.FONCTION, EMP.SAL FROM EMP WHERE EMP.Deptno=P_Deptno AND EMP.FONCTION LIKE P_Fonction; BEGIN DBMS_OUTPUT.PUT_LINE(RPAD('No.Emp',10)||RPAD('FONCTION',10)||LPAD('SALAIRE',10)); FOR emp_record IN v_emp_cursor(20,'Ouvrier') LOOP DBMS_OUTPUT.PUT_LINE(RPAD(emp_record.Empno,10)||RPAD(emp_record.FONCTION,10)||LPAD(emp_record.SAL,10)); END LOOP; END;

Question 8 :

--une première version utilisant une boucle LOOP. DECLARE CURSOR v_emp_cursor IS SELECT * FROM EMP; emp_record v_emp_cursor%ROWTYPE; v_salaire_max EMP.SAL%TYPE; BEGIN DBMS_OUTPUT.PUT_LINE(RPAD('No.Emp',10)||RPAD('ENAME',15)||RPAD('FONCTION',10)||LPAD('SALAIRE',10)); SELECT MAX(SAL) INTO v_salaire_max FROM EMP; OPEN v_emp_cursor; LOOP FETCH v_emp_cursor INTO emp_record; EXIT WHEN v_emp_cursor%NOTFOUND; IF emp_record.SAL=v_salaire_max THEN DBMS_OUTPUT.PUT_LINE(RPAD(emp_record.Empno,10)||RPAD(emp_record.ENAME,15)||RPAD(emp_record.FONCTION,10)||LPAD(emp_record.SAL,10)); END IF; END LOOP; CLOSE v_emp_cursor; END;

-- Une deuxième version utilisant une boucle FOR. SET SERVEROUTPUT ON; DECLARE CURSOR emp_cursor IS SELECT EMPNO, ENAME, FONCTION, SAL FROM EMP; v_salaire_max EMP.SAL%TYPE; BEGIN SELECT MAX(SAL) INTO v_salaire_max FROM EMP; DBMS_OUTPUT.PUT_LINE(RPAD('No.Emp',10)||RPAD('ENAME',15)||RPAD('FONCTION',10)||LPAD('SALAIRE',10)); FOR emp_record IN emp_cursor LOOP IF emp_record.SAL=v_salaire_max THEN DBMS_OUTPUT.PUT_LINE(RPAD(emp_record.Empno,10)||RPAD(emp_record.ENAME,15)||RPAD(emp_record.FONCTION,10)||LPAD(emp_record.SAL,10));

16

END IF; END LOOP; END; Question 9 :

--Une première version utilisant une boucle LOOP. DECLARE CURSOR v_emp_cursor IS SELECT * FROM EMP; emp_record v_emp_cursor%ROWTYPE; BEGIN DBMS_OUTPUT.PUT_LINE(RPAD('No.Emp',10)||RPAD('FONCTION',10)||RPAD('SALAIRE',15)||LPAD('COMM',15)); OPEN v_emp_cursor; LOOP FETCH v_emp_cursor INTO emp_record; EXIT WHEN v_emp_cursor%NOTFOUND; IF (emp_record.SAL+emp_record.COMM) >= 1000 THEN DBMS_OUTPUT.PUT_LINE(RPAD(emp_record.Empno,10)||RPAD(emp_record.FONCTION,10)||RPAD(emp_record.SAL,15)||LPAD(emp_record.COMM,15)); END IF; END LOOP; CLOSE v_emp_cursor; END;

-- Une deuxième version utilisant une boucle FOR. SET SERVEROUTPUT ON; DECLARE CURSOR v_emp_cursor IS SELECT * FROM EMP; BEGIN DBMS_OUTPUT.PUT_LINE(RPAD('No.Emp',10)||RPAD('FONCTION',10)||RPAD('SALAIRE',15)||LPAD('COMM',15)); FOR emp_record IN v_emp_cursor LOOP IF (emp_record.SAL+emp_record.COMM) >= 1000 THEN DBMS_OUTPUT.PUT_LINE(RPAD(emp_record.Empno,10)||RPAD(emp_record.FONCTION,10)||RPAD(emp_record.SAL,15)||LPAD(emp_record.COMM,15)); END IF; END LOOP; END; Question 10 :

--Une première version utilisant une boucle LOOP. SET SERVEROUTPUT ON; DECLARE CURSOR v_emp_cursor (P_Deptno EMP.DEPTNO%TYPE) IS SELECT EMPNO,FONCTION,SAL,COMM FROM EMP WHERE DEPTNO=P_Deptno; emp_record v_emp_cursor%ROWTYPE; v_nombre_emp_comm NUMBER(10):=0; v_nombre_emp_sal NUMBER(10):=0; v_rem REAL:=0; BEGIN

17

OPEN v_emp_cursor(30); LOOP FETCH v_emp_cursor INTO emp_record; EXIT WHEN v_emp_cursor%NOTFOUND; IF (emp_record.COMM IS NOT NULL) THEN v_rem:=v_rem+(emp_record.SAL+emp_record.COMM); END IF; IF (emp_record.SAL) >= 800 THEN v_nombre_emp_sal:=v_nombre_emp_sal+1; END IF; IF (emp_record.COMM IS NOT NULL AND emp_record.COMM >= emp_record.SAL) THEN v_nombre_emp_comm:=v_nombre_emp_comm+1; END IF; END LOOP; DBMS_OUTPUT.PUT_LINE('La valeur totale des rémunérations est '||v_rem); DBMS_OUTPUT.PUT_LINE('Le nombre d''employés dont le salaire est supérieur à 800 est '||v_nombre_emp_sal); DBMS_OUTPUT.PUT_LINE('Le nombre d''employés ayant des commissions supérieures à leur salaire est '||v_nombre_emp_comm); CLOSE v_emp_cursor; END;

-- Une deuxième version utilisant une boucle FOR. SET SERVEROUTPUT ON; DECLARE CURSOR v_emp_cursor (P_Deptno EMP.DEPTNO%TYPE) IS SELECT * FROM EMP WHERE DEPTNO=P_Deptno; v_nombre_emp_comm NUMBER(10):=0; v_nombre_emp_sal NUMBER(10):=0; v_rem REAL:=0; BEGIN FOR emp_record IN v_emp_cursor (20) LOOP IF (emp_record.COMM IS NOT NULL) THEN v_rem:=v_rem+(emp_record.SAL+emp_record.COMM); END IF; IF (emp_record.SAL) >= 800 THEN v_nombre_emp_sal:=v_nombre_emp_sal+1; END IF; IF (emp_record.COMM IS NOT NULL AND emp_record.COMM >= emp_record.SAL) THEN v_nombre_emp_comm:=v_nombre_emp_comm+1; END IF; END LOOP; DBMS_OUTPUT.PUT_LINE('La valeur totale des rémunérations est '||v_rem); DBMS_OUTPUT.PUT_LINE('Le nombre d''employés dont le salaire est supérieur à 800 est '||v_nombre_emp_sal); DBMS_OUTPUT.PUT_LINE('Le nombre d''employés ayant des commissions supérieures à leur salaire est '||v_nombre_emp_comm); END; Question 11 : ALTER TABLE DEPT ADD (NBEMP NUMBER(2) DEFAULT 0); Question 12 :

-- Solution 1 avec un curseur implicite de mise à jour

18

BEGIN SAVEPOINT SP4; UPDATE DEPT SET NBEMP=(SELECT COUNT(*) FROM EMP WHERE DEPT.DEPTNO=EMP.DEPTNO); END;

-- Solution 2 avec un curseur explicite de mise à jour DECLARE CURSOR v_dept_cursor IS SELECT * FROM DEPT FOR UPDATE OF NBEMP NOWAIT; BEGIN FOR dept_record IN v_dept_cursor LOOP UPDATE DEPT SET NBEMP=(SELECT COUNT(*) FROM EMP WHERE dept_record.DEPTNO=EMP.DEPTNO) WHERE CURRENT OF v_dept_cursor; END LOOP; END;

19

Institut Supérieur d’Informatique de Médenine

Matière : SGBD avancé TP n°4

Enseignant : Dr ABBASSI Imed

Groupe : MP1 ILC

Soit la base de données échantillon distribuée avec Oracle et décrite par son contenu ci-dessous : - DEPT (DEPTNO, DNAME, LOC) - EMP (EMPNO, ENAME, FONCTION, HIREDATE, SAL, COMM, #DEPTNO) On notera que : - Les clés primaires sont soulignées - Les clés étrangères sont précédées par le symbole dièse (#). Partie I : Suppose que la table DEPT et EMP n’ont pas de clés primaires, le serveur Oracle n’est pas en mesure de vérifier l’unicité des colonnes DEPTNO et EMPNO. Question 1: Proposez une fonction stockée PKEY_DEPTNO_CHECKER qui permet d’implanter le contrôle de l’unité de la colonne DEPTNO durant l’insertion d’un nouveau département. La fonction retourne 1 en cas de violation de la règle d’unicité de la colonne DEPTNO, et 0 sinon. a) Proposez une première version utilisant un curseur explicite. b) Proposez une deuxième version utilisant un curseur implicite. Question 1: Proposez un bloc PL/SQL qui permet de tester la fonction PKEY_DEPTNO_CHECKER. Question 2: Proposez une fonction stockée PKEY_EMP_CHECKER qui permet d’implanter le contrôle de l’unité de la colonne EMPNO durant l’insertion d’un nouveau département. La fonction retourne 1 en cas de violation de la règle d’unicité de la colonne EMPNO, et 0 sinon. a) Proposez une première version utilisant un curseur explicite. b) Proposez une deuxième version utilisant un curseur implicite. Question 3: Proposez un bloc PL/SQL qui permet de tester la fonction PKEY_EMP_CHECKER. Question 4: Sous SQL*Plus d’oracle, ajoutez la contrainte déclarative PRIMARY KEY aux tables DEPT et EMP. Question

5:

Supprimer

les

fonctions

stockées

PKEY_DEPTNO_CHECKER

et

PKEY_EMP_CHECKER du dictionnaire de données d’Oracle. Question 6: Proposez un bloc PL/SQL permettant d’insertion d’un département tout en prévoyant une exception qui tient compte de la présence de la contrainte de l’unité de la colonne DEPTNO.

20

Question 7: Proposez un bloc PL/SQL permettant d’insertion d’un employé tout en prévoyant une exception qui tient compte de la présence de la contrainte de l’unité de la colonne EMPNO.

Partie II : Dans cette partie, on suppose que les tables DEPT et EMP ne sont pas liées par une contrainte d’intégrité référentielle déclarative (FOREIGN KEY). On souhaite implanter cette règle par un sous-programme PL/SQL. Question 1: Proposez une fonction stockée FKEY_EMP_CHECKER qui permet la simulation de la contrainte FOREIGN KEY lors d’une opération d’insertion d’un employé. La fonction doit retourner 1 en cas de violation de règle d’intégrité référentielle de la table EMP, et 0 sinon. Question 2: Proposez un bloc PL/SQL qui permet de tester la fonction FKEY_EMP_CHECKER. Question 3: Sous SQL*Plus d’oracle, ajoutez la contrainte d’intégrité référentielle déclarative à la table EMP. Question 4: Supprimer la fonction stockée FKEY_EMP_CHECKER du dictionnaire de données d’Oracle. Question 5 : Proposez un bloc PL/SQL permettant d’insertion d’un employé tout en prévoyant une exception qui tient compte de la présence de la contrainte d’intégrité référentielle déclarative de la table EMP.

21

Institut Supérieur d’Informatique de Médenine

Matière : SGBD avancé Correction du TP n°4

Enseignant : Dr ABBASSI Imed

Groupe : MP1 ILC

Partie I : Question 1:

-- une première version utilisant un curseur explicite. create or replace FUNCTION PKEY_DEPT_CHECKER(P_DEPTNO NUMBER) RETURN BOOLEAN IS V_DEPTNO NUMBER; BEGIN SELECT DEPTNO INTO V_DEPTNO FROM DEPT WHERE DEPTNO=V_DEPTNO; RETURN true; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN false; END PKEY_DEPT_CHECKER;

-- une deuxième version utilisant un curseur implicite create or replace FUNCTION PKEY_DEPT_CHECKER(P_DEPTNO NUMBER) RETURN BOOLEAN IS v_dept DEPT%ROWTYPE; existe BOOLEAN:=FALSE; CURSOR C IS SELECT * FROM DEPT WHERE DEPTNO = P_DEPTNO; BEGIN OPEN C; FETCH C INTO v_dept; IF(C%FOUND) THEN existe:=TRUE; END IF; CLOSE C; RETURN existe; END PKEY_DEPT_CHECKER; Question 2: SET SERVEROUTPUT ON; BEGIN IF (PKEY_DEPT_CHECKER(80)) THEN DBMS_OUTPUT.PUT_LINE('Le département existe'); ELSE DBMS_OUTPUT.PUT_LINE('Le département n''existe pas'); END IF; END; Question 3: la solution est fortement similaire à celle proposée dans la question 1.

Question 4:

22

SET SERVEROUTPUT ON; BEGIN IF (PKEY_EMP_CHECKER(7830)) THEN DBMS_OUTPUT.PUT_LINE('L''employé existe'); ELSE DBMS_OUTPUT.PUT_LINE(' L''employé département n''existe pas'); END IF; END; Question 5: ALTER TABLE DEPT ADD CONSTRAINT PKEY_DEPT PRIMARY KEY (DEPTNO); ALTER TABLE EMP ADD CONSTRAINT PKEYEMP PRIMARY KEY (EMPNO); Question 6: DROP FUNCTION PKEY_DEPTNO_CHECKER; DROP FUNCTION PKEY_EMP_CHECKER; Question 7: SET SERVEROUTPUT ON; BEGIN INSERT INTO DEPT VALUES(10,'Financier','Tunis'); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE('Insertion rejetée: Département existe déjà.'); END; Question 8: SET SERVEROUTPUT ON; BEGIN INSERT INTO EMP VALUES (7698,'Ajili Sadok','Directeur','01-05-81',1400,NULL,30); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE('Insertion rejetée: employé existe déjà.'); END; Partie II : Question 1: create or replace FUNCTION FKEY_EMP_CHECKER(P_DEPTNO NUMBER) RETURN BOOLEAN IS V_DEPTNO NUMBER; BEGIN SELECT DEPTNO INTO V_DEPTNO FROM DEPT WHERE DEPTNO=V_DEPTNO; RETURN true; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN false; END KKEY_EMP_CHECKER;

23

Question 2: SET SERVEROUTPUT ON; BEGIN IF (FKEY_EMP_CHECKER(80)) THEN DBMS_OUTPUT.PUT_LINE('La contrainte d''intégrité est respectée.'); ELSE DBMS_OUTPUT.PUT_LINE('La contrainte d''intégrité non respectée.'); END IF; END; Question 3: la solution est similaire à celle proposée dans la question 5 de la partie I. Question 4: la solution est similaire à celle proposée dans la question 6 de la partie I. Question 5: SET SERVEROUTPUT ON; BEGIN INSERT INTO EMP VALUES (7898,'Ajili Sadok','Directeur','01-05-81',1400,NULL,70); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE('Insertion rejetée: employé existe déjà.'); WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Insertion rejetée: le département n''existe pas.'); END;

24

Institut Supérieur d’Informatique de Médenine

A.U. 201 8/2019 TP n°5

Enseignant : Dr ABBASSI Imed

Groupe : MP1 ILC

Soit la base de données échantillon distribuée avec Oracle et décrite par son contenu ci-dessous : - DEPT (DEPTNO, DNAME, LOC, NBE) - EMP (EMPNO, ENAME, FONCTION, HIREDATE, SAL, COMM, #DEPTNO) Notez bien que la colonne NBE doit être initialisée à 0. Question 1: Proposez une procédure stockée permettant d’insérer un nouveau département. Question 2: Proposez une procédure stockée permettant d’insérer un nouvel employé. Question 3: Proposez une procédure stockée qui détermine l’employé le mieux payé d’un département quelconque tout en prévoyant des exceptions possibles. Question 4: Proposez une procédure stockée qui permet de trouver les employés qui ont un salaire supérieur à une valeur passée en paramètre. Ce bloc doit tenir compte des exceptions possibles. Question 5: Proposez une procédure stockée qui affiche les rémunérations des employés, et qui tient compte des exceptions possibles. Question 6: Proposez une procédure stockée qui permet de trouver les employés dont les rémunérations (SAL+COMM éventuelle) sont supérieures à une valeur passée en paramètre. Ce bloc doit tenir compte des exceptions possibles. Question 7: Proposez un package PKG_Gestion_EMP qui permet contient les sous-programmes suivants : a) Une fonction de recherche d’un employé dont l’identifiant est passé en paramètre. b) Une procédure d’insertion d’un nouvel employé. c) Une procédure de suppression d’un employé. d) Une procédure de mise de l’état d’un employé.

Question 8: Proposez un package PKG_Gestion_DEPT qui permet contient les sous-programmes suivants : a) Une fonction de recherche d’un département dont l’identifiant est passé en paramètre. b) Une procédure d’insertion d’un nouveau département. c) Une procédure de suppression d’un département. d) Une procédure de modification d’un département. Question 9: Proposez un trigger nommé TRG_UPDATE_NBEMP permettant de mettre à jour automatiquement la colonne NBEMP de la table DEPT.

25

Institut Supérieur d’Informatique de Médenine

A.U. 201 8/2019 Correction de TP n°5

Enseignant : Dr ABBASSI Imed

Groupe : MP1 ILC

Question 1: CREATE OR REPLACE PROCEDURE INSERER_DEPT(P_deptno IN NUMBER, P_DNAME IN VARCHAR2, P_LOC IN VARCHAR2, STATUS OUT BOOLEAN ) IS BEGIN INSERT INTO DEPT VALUES(P_deptno,P_DNAME,P_LOC); STATUS:=TRUE; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN STATUS:=FALSE; END INSERER_DEPT;

Question 2: CREATE OR REPLACE PROCEDURE INSERER_EMP(P_EMPNO IN NUMBER, P_ENAME IN VARCHAR2, P_FONCTION IN VARCHAR2, P_HIREDATE IN DATE, P_SAL IN NUMBER, P_COMM IN NUMBER, P_DEPTNO IN NUMBER, STATUS OUT BOOLEAN ) IS BEGIN INSERT INTO EMP VALUES (P_EMPNO,P_ENAME,P_FONCTION,P_HIREDATE,P_SAL,P_COMM,P_DEPTNO); STATUS:=TRUE; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN STATUS:=FALSE; WHEN OTHERS THEN STATUS:=FALSE; END INSERER_EMP;

Question 3: CREATE OR REPLACE PROCEDURE EMP_MIEUX_PAYE (deptid IN NUMBER) IS v_emp EMP%ROWTYPE; sal_max EMP.SAL%TYPE; BEGIN SELECT max(SAL) INTO sal_max FROM EMP WHERE DEPTNO=deptid; SELECT * INTO v_emp FROM EMP WHERE SAL=sal_max; DBMS_OUTPUT.PUT_LINE(RPAD('No.Emp',10)

26

||RPAD('FONCTION',10) ||LPAD('SALAIRE',10)); DBMS_OUTPUT.PUT_LINE(RPAD(v_emp.empno,10) ||RPAD(v_emp.FONCTION,10) ||LPAD(v_emp.SAL,10)); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Le département n''existe pas'); END EMP_MIEUX_PAYE;

Question 4: CREATE OR REPLACE PROCEDURE EMP_WITH_SUP_SAL (P_SAL IN NUMBER) IS CURSOR C IS SELECT * FROM EMP WHERE SAL> P_SAL; BEGIN DBMS_OUTPUT.PUT_LINE(RPAD('Empno',10) ||RPAD('NAME',10) ||RPAD('FONCTION',10) ||LPAD('SAL',10) ); FOR rec IN C LOOP DBMS_OUTPUT.PUT_LINE(RPAD(rec.Empno,10) ||RPAD(rec.ENAME,10) ||RPAD(rec.FONCTION,10) ||LPAD(rec.SAL,10) ); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('Il n''existe aucun employé ayant un salaire supérieur à 800.'); END EMP_WITH_SUP_SAL;

Question 5: CREATE OR REPLACE PROCEDURE REM_EMP IS CURSOR C IS SELECT * FROM EMP; BEGIN DBMS_OUTPUT.PUT_LINE(RPAD('Empno',10) ||RPAD('NAME',10) ||RPAD('FONCTION',10) ||LPAD('REM',10) ); FOR rec IN C LOOP DBMS_OUTPUT.PUT(RPAD(rec.Empno,10) ||RPAD(rec.ENAME,10) ||RPAD(rec.FONCTION,10) ); IF (rec.COMM IS NOT NULL) THEN DBMS_OUTPUT.PUT_LINE(LPAD((rec.SAL+rec.COMM),10)); ELSE DBMS_OUTPUT.PUT_LINE(LPAD((rec.SAL),10)); END IF;

27

END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('Il n''existe aucun employé ayant un salaire supérieur à 800.'); END;

Question 6: la solution est similaire à la procédure proposée dans la question 4.

Question 7: --La spécification du package PKG_Gestion_EMP CREATE OR REPLACE PACKAGE PKG_Gestion_EMP IS FUNCTION CHECK_EMP(empid IN NUMBER) RETURN BOOLEAN; PROCEDURE INSERER_EMP(P_EMPNO IN NUMBER, P_ENAME IN VARCHAR2, P_FONCTION IN VARCHAR2, P_HIREDATE IN DATE, P_SAL IN NUMBER, P_COMM IN NUMBER, P_DEPTNO IN NUMBER, STATUS OUT BOOLEAN ); PROCEDURE SUPPRIMER_EMP (empid IN NUMBER, STATUS OUT BOOLEAN); END PKG_Gestion_EMP;

-- L’implémentation du package PKG_Gestion_EMP: create or replace PACKAGE BODY PKG_Gestion_EMP IS FUNCTION CHECK_EMP(empid IN NUMBER) RETURN BOOLEAN IS V_DEPTNO NUMBER; BEGIN SELECT DEPTNO INTO V_DEPTNO FROM DEPT WHERE DEPTNO=empid; RETURN true; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN false; END; PROCEDURE INSERER_EMP(P_EMPNO IN NUMBER,P_ENAME IN VARCHAR2,P_FONCTION IN VARCHAR2,P_HIREDATE IN DATE, P_SAL IN NUMBER,P_COMM IN NUMBER, P_DEPTNO IN NUMBER) IS BEGIN IF (CHECK_EMP(P_EMPNO)=TRUE) THEN INSERT INTO EMP VALUES (P_EMPNO,P_ENAME,P_FONCTION,P_HIREDATE,P_SAL,P_COMM,P_DEPTNO); END IF; END; PROCEDURE SUPPRIMER (empid IN NUMBER) IS BEGIN IF (CHECK_EMP(empid)=TRUE) THEN DELETE FROM EMP WHERE EMPNO=empid; END IF; END; END PKG_Gestion_EMP;

28

Question 8: la solution est similaire à la procédure proposée dans la question 7. Question 9: CREATE OR REPLACE TRIGGER TRG_UPDATE_NBEMP AFTER INSERT OR DELETE OR UPDATE ON EMP FOR EACH ROW BEGIN IF (INSERTING) THEN UPDATE DEPT SET NBEMP=NBEMP+1 WHERE DEPTNO=:NEW.DEPTNO; ELSIF (UPDATING) THEN UPDATE DEPT SET NBEMP=NBEMP-1 WHERE DEPTNO=:OLD.DEPTNO; UPDATE DEPT SET NBEMP=NBEMP+1 WHERE DEPTNO=:NEW.DEPTNO; ELSE UPDATE DEPT SET NBEMP=NBEMP-1 WHERE DEPTNO=:OLD.DEPTNO; END IF; END TRG_UPDATE_NBEMP;

29