Licence SMI 2007 – 2008

B.OUAHBI

PL/SQL

NUMBER(4);

VARCHAR2(10);

Exercice 1 :

Parmi les déclarations de variables suivantes, déterminer celles qui sont incorrectes :

A - DECLARE

v_id

Correcte

B - DECLARE

v_x,v_y,v_z

Incorrecte : un seul identifiant par ligne

C - DECLARE

v_date_naissance DATE NOT NULL;

Incorrecte : une valeur NOT NULL doit être initialisée

D - DECLARE

v_en_stock

Incorrecte : 1 n’est pas une valeur booléenne

E - DECLARE

emp_record

Incorrecte : EMP_RECORD_TYPE doit être déclaré

F - DECLARE

TYPE

INDEX BY BINARY_INTEGER;

dept_table_nom

Correcte

type_table_nom IS TABLE OF VARCHAR2(20)

type_table_nom;

emp_record_type;

BOOLEAN := 1;

Exercice 2 :

1 - Créer un bloc PL/SQL pour insérer un nouveau département dans la table DEPARTEMENTS

a) Utiliser la séquence DEPT_ID_SEQ pour générer un numéro de département. Créer un paramètre pour le nom

du département. Laisser le numéro de région à NULL.

Fichier p2q1.sql

ACCEPT p_dept_nom PROMPT ‘Entrer un nom de département : ‘

BEGIN

INSERT INTO departements(id, nom, region_id)

VALUES (dept_id_seq.NEXTVAL, ‘&p_dept_nom’, NULL);

COMMIT;

END;

b) Exécuter le bloc PL/SQL avec la valeur “Santé” pour le nom du département.

SQL> start p2q1

Entrer un nom de département : Santé

PL/SQL procedure successfully completed.

c) Afficher le nouveau département créé

SQL> SELECT * FROM departements

2 WHERE nom = 'Santé';

ID NOM REGION_ID

--------- ------------------------- ---------

82 Santé

2 - Créer un bloc PL/SQL pour supprimer le département créé précédemment

a) Créer un paramètre pour le numéro de département. Faire afficher à l’écran le nombre de lignes affectées.

Fichier p2q2.sql

ACCEPT p_dept_id PROMPT 'Entrer un numéro de département : '

VARIABLE g_mess VARCHAR2(30)

DECLARE

v_resultat NUMBER(2);

BEGIN

DELETE FROM departements

WHERE id = &p_dept_id;

v_resultat := SQL%ROWCOUNT;

:g_mess := TO_CHAR(v_resultat)||' ligne(s) supprimée(s).';

COMMIT;

END;

PRINT g_mess

b) Tester le bloc. Que se passe-t-il si on saisit un numéro de département qui n’existe pas?

SQL> start p2q2

Entrer un numéro de département : 134

PL/SQL procedure successfully completed.

G_MESS

--------------------------------

0 ligne(s) supprimée(s).

Si on saisit un numéro de département qui a des employés?

SQL> start p2q2

Entrer un numéro de département : 31

DECLARE

ERROR at line 1:

ORA-02292: integrity constraint (BOHEZ.EMPLOYES_DEPT_ID_FK) violated - child record found

ORA-06512: at line 4

c) Tester le bloc avec le département Santé (82)

SQL> start p2q2

Entrer un numéro de département : 82

PL/SQL procedure successfully completed.

G_MESS

--------------------------------

1 ligne(s) supprimée(s).

d) Vérifier que le département n’existe plus

SQL> SELECT *

2 FROM departements

3 WHERE id = 82;

no rows selected

Exercice 3 :

1 - Créer un bloc PL/SQL permettant de mettre à jour le pourcentage de commission d’un employé en fonction du

total de ses ventes

Cet exercice nécessite la suppression de la contrainte sur la colonne commission de la table EMPLOYES :

SQL> ALTER TABLE employes

2 DROP CONSTRAINT employes_commission_ck;

a) Créer un paramètre qui reçoit un numéro d’employé

Trouver la somme totale de toutes les commandes traitées par cet employé

Mettre à jour le pourcentage de commission de l’employé :

si la somme est inférieure à 100,000 passer la commission à 10
si la somme est comprise entre 100,000 et 1,000,000 inclus passer la commission à 15
si la somme excède 1,000,000 passer la commission à 20
si aucune commande n’existe pour cet employé, mettre la commission à 0

Valider la modification (commit)

Fichier p3q1.sql.

ACCEPT p_id PROMPT 'Entrer un numéro de vendeur : '

DECLARE

v_somme_total NUMBER(11,2);

v_comm employes.commission%TYPE;

BEGIN

SELECT

INTO

FROM

WHERE

commandes

vendeur_id = &p_id;

v_somme_total

SUM(total)

IF v_somme_total < 100000 THEN

v_comm := 10;

ELSIF v_somme_total <= 1000000 THEN

v_comm := 15;

ELSIF v_somme_total > 1000000 THEN

v_comm := 20;

ELSE

v_comm := 0;

END IF;

UPDATE employes

SET commission = v_comm

WHERE id = &p_id;

COMMIT;

END;

b) Tester le bloc et visualiser les résultats (on teste avec les employés 1,11,12,14)

SQL> SELECT id, commission

2 FROM employes

3 WHERE id IN (1,11,12,14);

ID COMMISSION

--------- ----------

14 10

12 15

11 20

1 0

2 - Créer un bloc PL/SQL qui boucle pour chaque région (le numéro des régions va de 1 à 5) afin de modifier le

code de solvabilité de tous les clients. Ne pas valider (pas de commit).

Si le numéro de région est pair mettre la solvabilité à EXCELLENTE (même si elle l’est déjà), sinon mettre à

BONNE pour les numéros de région impairs.

Une fois les lignes modifiées, trouvez combien de lignes ont été mises à jour.

Afficher les résultats suivants en fonction du nombre de lignes modifiées :

l Si moins de trois lignes ont été modifiées, afficher : ‘Moins de trois lignes ont été

modifiées pour la région x’ (x étant le numéro de la région).

l Sinon afficher : ‘y lignes ont été modifiées pour la région x’(y étant le nombre de lignes

modifiées).

Annuler les modifications (rollback).

Fichier p3q2.sql.

VARIABLE g_mess VARCHAR2(500)

DECLARE

v_sortie

v_modifies NUMBER(2);

v_solvable VARCHAR2(25);

c_peu

VARCHAR2(500);

CONSTANT VARCHAR2(100)

:= 'Moins de 3 lignes ont été modifiées pour la région ';

BEGIN

FOR i IN 1..5 LOOP

IF MOD(i,2) <> 0 THEN

v_solvable := 'EXCELLENTE';

ELSE

v_solvable := 'BONNE';

END IF;

UPDATE clients

SET solvabilite = v_solvable

WHERE region_id = i;

v_modifies := SQL%ROWCOUNT;

IF v_modifies < 3 THEN

v_sortie := v_sortie||c_peu||TO_CHAR(i)||CHR(10);

ELSE

v_sortie := v_sortie||TO_CHAR(v_modifies)||

' lignes ont été modifiées pour la région '||TO_CHAR(i)||CHR(10);

END IF;

END LOOP;

:g_mess := v_sortie;

END;

PRINT g_mess

Exercice 4 :

Créer un bloc PL/SQL qui détermine les employés de plus haut salaire.

a) Créer pour cet exercice une nouvelle table pour stocker les employés et leurs salaires

SQL> CREATE TABLE meilleurs

2 (nom

VARCHAR2(25),

3 salaire NUMBER(11,2));

b) Utiliser un paramètre pour prendre une valeur n en entrée pour identifier les n meilleurs.

Ecrire une boucle WHILE avec curseur pour récupérer le nom et salaire des n meilleurs employés selon leur

salaire dans la table EMPLOYES

Enregistrer les noms et salaires dans la table MEILLEURS.

On suppose qu’aucun employé n’a le même salaire qu’un autre.

c) Tester le bloc avec différents cas tels que n=0 ou n supérieur au nombre total d’employés (25).

Vider la table MEILLEURS après chaque test.

Fichier p4q1.sql.

ACCEPT p_n PROMPT 'Entrer une valeur numérique : '

DECLARE

CURSOR emp_cursor IS

SELECT nom, salaire

FROM employes

WHERE salaire IS NOT NULL

ORDER BY salaire DESC;

emp_record emp_cursor%ROWTYPE;

BEGIN

OPEN emp_cursor;

FOR i IN 1..&p_n

LOOP

FETCH emp_cursor INTO emp_record;

INSERT INTO meilleurs(nom, salaire)

VALUES (emp_record.nom,emp_record.salaire);

$2,500

$1,550

$1,525

$1,515

END LOOP;

CLOSE emp_cursor;

COMMIT;

END;

SELECT nom,TO_CHAR(salaire,'fm$9,999,999') salaire FROM meilleurs;

TRUNCATE TABLE meilleurs

Test du bloc avec n=4

SQL> start p4q1

Entrer une valeur numérique : 4

PL/SQL procedure successfully completed.

NOM SALAIRE

----------------------------- -------------

Velasquez

Ropeburn

Nguyen

Sedeghi

Table truncated.

Test du bloc avec n=0

SQL> start p4q1

Entrer une valeur numérique : 0

PL/SQL procedure successfully completed.

no rows selected

Table truncated.

Test du bloc avec n=30

SQL> START p4q1

Entrer une valeur numérique : 30

PL/SQL procedure successfully completed.

NOM

-------------------------

Velasquez

Ropeburn

Nguyen

Sedeghi

Giljum

SALAIRE

-----------

$2,500

$1,550

$1,525

$1,515

$1,490

Ngao

$1,450

Quick-To-See $1,450

$1,450

Dumas

$1,400

Nagayama

$1,400

Maduro

$1,400

Magee

$1,307

Havel

$1,300

Catchpole

$1,250

Menchu

$1,200

Urguhart

$1,200

Nozaki

$1,100

Biri

$1,100

Schwartz

$940

Smith

$860

Dancs

$850

Markarian

$800

Chang

$795

Patel

$795

Patel

$750

Newman

$750

Newman

$750

Newman

$750

Newman

$750

Newman

$750

30 rows selected.

Table truncated.

Exercice 5 :

Modifier le bloc PL/SQL fourni pour gérer les exceptions.

Le traitement essaie de mettre à jour des numéros de région pour des départements existants.

a) Charger le fichier p5qa.sql.

ACCEPT p_dept_id PROMPT 'Numéro de département : '

ACCEPT p_nom_region PROMPT 'Nom de région : '

VARIABLE g_mess VARCHAR2(50)

DECLARE

v_region_id regions.id%TYPE;

BEGIN

SELECT

INTO

FROM

WHERE UPPER(nom)=UPPER('&p_nom_region');

UPDATE

SET region_id=v_region_id

WHERE

:g_mess := 'Le département : '||TO_CHAR(&p_dept_id)||

’ est affecté à la région '||TO_CHAR(v_region_id);

v_region_id

regions

id = &p_dept_id;

departements

COMMIT;

END;

PRINT g_mess

b) Exécuter le bloc avec comme valeur 50 pour le numéro de département et US pour le nom de région.

SQL> start p5qa

Numéro de département : 50

Nom de région : US

DECLARE

ERROR at line 1:

ORA-01403: no data found

ORA-06512: at line 5

G_MESS

-----------------------------------------------------------------

c) Sauvegarder le fichier p5qa.sql sous le nom p5q1.sql. Modifier p5q1.sql pour écrire un traitement d’exception

pour l’anomalie constatée afin de passer un message à l’utilisateur lorsque la région spécifiée n’existe pas.

ACCEPT p_dept_id PROMPT 'Numéro de département : '

ACCEPT p_nom_region PROMPT 'Nom de région : '

VARIABLE g_mess VARCHAR2(50)

DECLARE

v_region_id regions.id%TYPE;

BEGIN

SELECT

INTO

FROM

WHERE UPPER(nom)=UPPER('&p_nom_region');

UPDATE

SET region_id=v_region_id

WHERE

:g_mess := 'Le département : '||TO_CHAR(&p_dept_id)||

’ est affecté à la région '||TO_CHAR(v_region_id);

v_region_id

regions

id = &p_dept_id;

departements

COMMIT;

EXCEPTION

WHEN NO_DATA_FOUND THEN

ROLLBACK;

:g_mess := '&p_nom_region'||' : région inexistante.';

END;

PRINT g_mess

SQL> start p5q1

Numéro de département : 50

Nom de région : US

PL/SQL procedure successfully completed.

G_MESS

---------------------------------------------------------------------

US : région inexistante.

d) Exécuter le bloc avec comme valeur 31 pour le numéro de département et Asie pour le nom de région.

SQL> START p5q1

Numéro de département : 31

Nom de région : Asie

DECLARE

ERROR at line 1:

ORA-00001: unique constraint (BOHEZ.DEPARTEMENTS_NOM_ET_REGION_UK) violated

ORA-06512: at line 9

G_MESS

---------------------------------------------------------------------------

e) Ecrire un traitement d’exception pour l’anomalie constatée afin de passer un message à l’utilisateur lorsque la

région spécifiée est déjà référencée par un département du même nom

ACCEPT p_dept_id PROMPT 'Numéro de département : '

ACCEPT p_nom_region PROMPT 'Nom de région : '

VARIABLE g_mess VARCHAR2(80)

DECLARE

v_dept

v_region_id regions.id%TYPE;

BEGIN

SELECT

INTO

FROM

WHERE UPPER(nom)=UPPER('&p_nom_region');

UPDATE

SET region_id=v_region_id

WHERE

:g_mess := 'Le département : '||TO_CHAR(&p_dept_id)||

’ est affecté à la région '||TO_CHAR(v_region_id);

v_region_id

regions

VARCHAR2(20);

id = &p_dept_id;

departements

COMMIT;

EXCEPTION

WHEN NO_DATA_FOUND THEN

ROLLBACK;

:g_mess := '&p_nom_region'||' : région inexistante.';

WHEN DUP_VAL_ON_INDEX THEN

ROLLBACK;

SELECT

INTO

FROM

WHERE

AND nom =

nom||' (N°'||TO_CHAR(id)||')'

v_dept

departements

region_id = v_region_id

(SELECT

FROM

WHERE

nom

departements

id = &p_dept_id);

:g_mess := 'Il existe déjà un département '||v_dept||

' pour la région '||'&p_nom_region';

END;

PRINT g_mess

e) Suite

SQL> start p5q1

Numéro de département : 31

Nom de région : Asie

PL/SQL procedure successfully completed.

G_MESS

--------------------------------------------------------------------

Il existe déjà un département Ventes (N°34) pour la région Asie

f) Exécuter le bloc avec comme valeur 99 pour le numéro de département et Europe pour le nom de la région

SQL> start p5q1

Numéro de département : 99

Nom de région : Europe

PL/SQL procedure successfully completed.

G_MESS

----------------------------------------------------------------------------------

Le département : 99 est affecté à la région 5

Anomalie constatée : la procédure ne remarque pas que le département 99 n’existe pas.

g) Ecrire un traitement d’exception pour l’anomalie constatée afin de passer un message à l’utilisateur lorsque le

numéro de département spécifié n’existe pas.

Rappel : penser à utiliser l’attribut SQL%NOTFOUND et déclencher une exception manuellement

ACCEPT p_dept_id PROMPT 'Numéro de département : '

ACCEPT p_nom_region PROMPT 'Nom de région : '

VARIABLE g_mess VARCHAR2(80)

DECLARE

v_dept

v_region_id regions.id%TYPE;

e_count

BEGIN

SELECT

INTO

FROM

WHERE UPPER(nom)=UPPER('&p_nom_region');

UPDATE

SET region_id=v_region_id

WHERE

id = &p_dept_id;

IF SQL%NOTFOUND THEN

RAISE e_count;

END IF;

:g_mess := 'Le département : '||TO_CHAR(&p_dept_id)||

' est affecté à la région '||TO_CHAR(v_region_id);

v_region_id

regions

VARCHAR2(20);

EXCEPTION;

departements

COMMIT;

EXCEPTION

WHEN NO_DATA_FOUND THEN

ROLLBACK;

:g_mess := '&p_nom_region'||' : région inexistante.';

WHEN DUP_VAL_ON_INDEX THEN

ROLLBACK;

SELECT

nom||' (N°'||TO_CHAR(id)||')'

INTO

FROM

WHERE

AND nom =

v_dept

departements

region_id = v_region_id

(SELECT

FROM

WHERE

nom

departements

id = &p_dept_id);

:g_mess := 'Il existe déjà un département '||v_dept||

' pour la région '||'&p_nom_region';

WHEN e_count THEN

ROLLBACK;

:g_mess := 'Le département numéro '||TO_CHAR(&p_dept_id)||' n''existe pas.';

END;

PRINT g_mess

g) Suite

SQL> start p5q1

Numéro de département : 99

Nom de région : Europe

PL/SQL procedure successfully completed.

G_MESS

--------------------------------------------------------------------

Le département numéro 99 n'existe pas

Structure et Données des Tables

SQL>desc departements

Name

--------------------------------------

NOM

REGION_ID

Null?

----------------

NOT NULL

Type

--------------------------

NUMBER(7)

VARCHAR2(25)

NUMBER(7)

SQL>SELECT * FROM departements

------

NOM

------------------------------------------

Finance

Ventes

Opérations

Administration

REGION_ID

-----------------

Null?

---------------

NOT NULL

SQL>desc Commandes

Name

-----------------------

CLIENT_ID

DATE_COMM

DATE_EXP

VENDEUR_ID

TOTAL

TYPE_PAIEMENT

COMM_REMPLIE

Null?

NOT NULL

SQL>desc employes

Name

----------------------------- -------------------------

NOM

PRENOM

USERID

DATE_EMB

COMMENTAIRES

RESPONSABLE_ID

POSTE

DEPT_ID

SALAIRE

COMMISSION

Type

-------------------------

NUMBER(7)

DATE

NUMBER(7)

NUMBER(11,2)

VARCHAR2(8)

VARCHAR2(1)

Type

----------------------------

NUMBER(7)

VARCHAR2(25)

VARCHAR2(8)

DATE

VARCHAR2(255)

NUMBER(7)

VARCHAR2(25)

NUMBER(7)

NUMBER(11,2)

NUMBER(4,2)

SQL>SELECT * FROM employes

---

NOM

------------

Velasquez

Ngao

Nagayama

Quick-To-See

Ropeburn

Urguhart

Menchu

Biri

Catchpole

Havel

Magee

Giljum

Sedeghi

Nguyen

Dumas

Maduro

Smith

Nozaki

Patel

Newman

Markarian

Chang

Patel

Dancs

Schwartz

PRENOM USERID

------------ ----------

cvelasqu

Carmen

lngao

LaDoris

mnagayam

Midor

mquickto

Mark

aropebur

Audrey

murguhar

Molly

rmenchu

Roberta

Ben

bbiri

Antoinette acatchpo

mhavel

Marta

cmagee

Colin

hgiljum

Henry

ysedeghi

Yasmin

mnguyen

Mai

adumas

André

emaduro

Elena

gsmith

George

anozaki

Akira

vpatel

Vikram

Chad

cnewman

Alexander amarkari

echang

Eddie

rpatel

Radha

bdancs

Bela

sschwart

Sylvie

DATE_EMB

----------------

03-03-90

08-03-90

17-06-91

07-04-90

04-03-90

18-01-91

14-05-91

07-04-90

09-02-92

27-02-91

14-05-90

18-01-92

18-02-91

22-01-92

09-10-91

07-02-92

08-03-90

02-02-91

06-08-91

21-07-91

26-05-91

30-11-90

17-10-90

17-03-91

09-05-91

C M

-- --

POSTE

------------------

Président

VP, Opérations

VP, Ventes

VP, Finance

VP, Administrat.

Resp. Magasin

Représentant

Magasinier

----

---

12.5

17.5

SAL

------

2500

1450

1400

1450

1550

1200

1250

1100

1300

1307

1400

1490

1515

1525

1450

1400

940

1200

795

750

850

800

795

860

1100