Concours Technologue Session 2008
PARTIE 4 : SQL et PL/SQL
CREATION des TABLES
create table Patient (codeP number(4) primary key,
NomP varchar2(20) not null,
PrenomP varchar2(20) not null,
datenP date)
/
create table Secretaite (CodeS number (4) primary key,
NomS varchar2(20) not null,
PrenomS varchar2(15) not null)
/
Create table Medecin (CodeM number(4) priamary key,
Nomm varchar2(20) not null,
Prenom varchar2(20) not null,
SpecM varchar2(15) not null,
Titre varchar2(4) constraint valtt check (titre in ('Mr', 'Mlle', 'Mme'));
Coutc number(7,3) constraint valcc check(coutc > 0 ),
numtP number(8), numtB number(8), numtD number(8))
/
Solution
Contraintes d’intégrité :
Toutes règles implicites ou explicites que doivent suivre les données [Gar99]
– Contraintes d'entité: toute entité doit posséder un identificateur
– Contraintes de domaine : les valeurs de certains attributs doivent être prises
dans un ensemble donné
– Contraintes d'unicité : une valeur d'attribut ne peut pas
être affectée deux fois à deux entités différentes
– Contraintes générales : règle permettant de conserver la cohérence de la base
de manière générale (Référentielle, inclusion, exclusion etc..).
1. Structure Table RV
RV (DateHRV, CodeM # , CodeP# , CodeS # , EtatRV )
2. Ordre de création de la table RV
Proposé par Chaieb Chiheb MT Iset de Sousse 1/29
Concours Technologue Session 2008
Create table RV (dateHRV date constraint nndr not null,
CodeM constraint fkM references Medecin(CodeM),
CodeP constraint fkP references Patient(codeP),
CodeS Constraint fkS references Secretaire(CodeS),
EtatRV char(1) constraint valEtat check(EtatRv in ('P', 'R')),
primary key(dateHRV,CodeM))
/
Insertion des RV
Insert into RV values (to_date('01/06/08 09:00','dd/mm/yy hh:mi'), 1 , 1000 ,
100 ,'R');
Insert into RV values (to_date('12/06/08 10:15','dd/mm/yy hh:mi' ) , 2 , 1002,
300 ,'P');
Liste de RV ordonnée sur Code Med
SQL> select * from rv order by codem,codes,datehrv ;
DATEHRV CODEM CODEP CODES EtatRV
-------- ---------- ---------- ---------- -
01/01/08 1 1003 100 R
05/01/08 1 1002 100 R
10/01/08 1 1002 100 R
10/02/08 1 1000 100 R
10/02/08 1 1001 100 R
10/05/08 1 1002 100 R
01/06/08 1 1000 100 R
01/06/08 1 1001 100 R
10/06/08 1 1000 200 R
10/06/08 1 1002 200 P
10/06/08 1 1003 R
13/06/08 1 1003 P
01/01/08 2 1002 100 R
10/01/08 2 1002 100 R
12/02/08 2 1001 100 R
10/03/08 2 1000 100 R
02/06/08 2 1000 100 R
03/06/08 2 1001 200 R
10/06/08 2 1002 300 R
12/06/08 2 1002 300 P
16/06/08 2 1002 R
26/06/08 2 1002 P
DATEHRV CODEM CODEP CODES ETATRV
-------- ---------- ---------- ---------- --------
10/01/08 3 1002 100 R
10/01/08 3 1002 100 R
Proposé par Chaieb Chiheb MT Iset de Sousse 2/29
Concours Technologue Session 2008
10/02/08 3 1001 100 R
10/03/08 3 1000 100 R
10/06/08 3 1003 100 R
12/06/08 3 1002 100 R
22/05/08 3 1003 R
12/06/08 3 1003 R
16/06/08 3 1003 R
26/06/08 3 1003 P
32 rows selected.
3. SQL
a. salaire des secrétaires pour le mois de juin 08 .
Select [Link] , noms , sum(coutc*0.1) salaire_juin
from secretaire s, medecin m, rv r
where [Link]=[Link] and [Link]=[Link]
and etatrv='R' and [Link] is not null
and to_char(datehrv,'mm')='06' and to_char(datehrv,'yy')='08'
group by [Link], noms ;
CODES NOMS SALAIRE_JUIN
---------- -------------------- ------------
100 Ben Chirz 18,5
200 Ben Aghlab 7
300 Dali 2,5
SQL> Create view salaire_sec_juin (codes, noms, salaire ) as
2 Select [Link] , noms , sum(coutc*0.1)
3 from secretaire s, medecin m, rv r
4 where [Link]=[Link] and [Link]=[Link]
5 and etatrv='R' and [Link] is not null
6 and to_char(datehrv,'mm')='06' and to_char(datehrv,'yy')='08'
7 group by [Link], noms ;
View created.
SQL> select * from salaire_sec_juin;
CODES NOMS SALAIRE
---------- ------------------- ----------
100 Ben Chirz 18,5
Proposé par Chaieb Chiheb MT Iset de Sousse 3/29
Concours Technologue Session 2008
200 Ben Aghlab 7
300 Dali 2,5
Vérification : numérations sec/consultation en juin08
Select codes,datehrv, [Link],coutc , coutc*0.1 as RenumS from RV, Medecin
where [Link]=[Link] and etatrv='R' and codes is not null and
to_char(datehrv,'mm')='06' and to_char(datehrv,'yy')='08'
order by codes,codem;
CODES DATEHRV CODEM COUTC RENUMS
------ ---------- ---------- ---------- --------
100 01/06/08 1 45 4,5
100 01/06/08 1 45 4,5
100 02/06/08 2 25 2,5
100 10/06/08 3 35 3,5
100 12/06/08 3 35 3,5 Tot Secrétaire 100: 18,5
200 10/06/08 1 45 4,5
200 03/06/08 2 25 2,5 Tot secrétaire 200 : 7
300 10/06/08 2 25 2,5 Tot secrétaire 300 : 2,5
b. Les médecins ayant réalisés des RDV plus que la moyenne
étape 1: Créer une Vue qui donne le nombre de consultation par médecin :
Create View Cumul_RV_Med (codem, cumul) as Select codem, count(datehrv)
from RV where datehrv between '01/01/08' and '31/03/08'
group by codem and etatrv='R';
-- ou datehrv <= to_date('31/03/08', 'dd/mm/yy')
View created.
-- On considère que les RDV pris réalisés (R)
SQL> select * from cumul_rv_med;
CODEM CUMUL
---------- ----------
1 5
Proposé par Chaieb Chiheb MT Iset de Sousse 4/29
Concours Technologue Session 2008
2 4
3 4
Total des RV (Premier Trimestre 08) = 5+4+4= 13
Moyenne cumul RV/Med 13/3 = 4.33333
Nombre RV (P/R) par Médecin pour le premier trimestre 2008
Select codem,count(datehrv) from rv where to_char(datehrv,'mm')<='03' and
to_char(datehrv,'yy')='08' group by codem;
CODEM COUNT(DATEHRV)
----- --------------
1 5
2 4
3 4
Moyenne des cumuls de RV par médecin
SQL> Select avg (count(datehrv)) as cum_moyen from rv where
to_char(datehrv,'mm')<='03' and to_c
har(datehrv,'yy')='08' and etatrv='R' group by codem;
CUM_MOYEN
----------
4,33333333
Liste Médecin ayant cumulés des RV plus que la moyenne
SQL> select codem,count(datehrv) from rv where to_char(datehrv,'mm')<='03'
and to_char(datehrv,'yy')='08' group by codem having count(datehrv) >=
(select avg (count(datehrv)) from rv where to_char(datehrv,'mm')<='03' and
to_char(datehrv,'yy')='08' group by codem);
CODEM COUNT(DATEHRV)
---------- --------------
1 5
SQL> select [Link], nomm, prenom, specm , count(datehrv) as Cumul_Cons
from Medecin m, Rv R
where [Link]=[Link] and datehrv between '01/01/08' and '31/03/08'
group by [Link], nomm, prenom, specm
Proposé par Chaieb Chiheb MT Iset de Sousse 5/29
Concours Technologue Session 2008
having count(datehrv) >= (select avg(count(datehrv)) from rv where
etatrv='R' and
datehrv between '01/01/08' and '31/03/08' group by codem);
CODEM NOMM PRENOM SPECM CUMUL_CONS
---------- ----------------- --------------- --------------- ----------
1 AL AByadh Slimene Phsycologue 5
Create View Cumul_Sup_moy(codem, nom, prenom, specialite, cumul) as
Select [Link], nomm, prenom, specm , count(datehrv) from Medecin m, Rv
R
where [Link]=[Link] and datehrv between '01/01/08' and '31/03/08'
and etatrv='R'group by [Link], nomm, prenom, specm
having count(datehrv) >= (select avg(count(datehrv)) from rv where datehrv
between '01/01/08' and '31/03/08' and etatrv='R' group by codem);
Select * from cumul_sup_moy ;
CODEM NOMM PRENOM SPECM CUMUL_MAX
----- -------------------- --------------- --------------- ----------
1 AL AByadh Slimene Phsycologue 5
Médecins ayant cumulés le max des RV Réalisés
Select [Link], nomm, prenom, specm , count(datehrv) as Cumul_RV from
Medecin m, Rv R
where [Link]=[Link] and etatrv='R' group by [Link],
nomm,prenom,specm ;
CODEM NOMM PRENOM SPECM CUMUL_MAX
---- -------------------- --------------- --------------- ----------
1 AL AByadh Slimene Phsycologue 10
2 Ibn Sina Mohamed Chirugien 8
3 AL Baghdadi Abdallah Nerologue 9
Select [Link], nomm, prenom, specm , count(datehrv) as Cumul_Max from
Medecin m, Rv R
where [Link]=[Link] and etatrv='R' group by [Link],
nomm,prenom,specm having
count(datehrv) >= all (select count(datehrv) from rv where etatrv='R' group by
codem);
Proposé par Chaieb Chiheb MT Iset de Sousse 6/29
Concours Technologue Session 2008
CODEM NOMM PRENOM SPECM CUMUL_MAX
----- -------------------- --------------- --------------- ----------
1 AL AByadh Slimene Phsycologue 10
C. Contribution de chaque médecin au salaire des secrétaires
Solution 1 : Le Salaire des Secrétaires mutualisé à part égal entre tous les
médecins (peu n'importe s'il a eu des consultations ou non).
On cherche la contribution du médecin/secrétaire
Créer la vue Sal_sec_juin (CodeS, Sal_juin, Nbr_med)
Sal_juin : Salaire des secrétaires au mois de juin
Nbr_med : le nombre de médecins total
SQL> Create view Sal_sec_juin1 (Codes, Sal_juin,nbr_med) as
Select codes , sum(coutc*0.1) ,
(select count(codem) from Medecin ) as nbr_med from medecin
m, rv r where [Link]=[Link] and etatrv='R'
and codes is not null and to_char(datehrv,'mm')='06'
and to_char(datehrv,'yy')='08' group by codes;
View created.
SQL> select * from sal_sec_juin1;
CODES SAL_JUIN NBR_MED
---------- ---------- ----------
100 18,5 4
200 7 4
300 2,5 4
SQL> select codes, sal_juin/nbr_med as contribution from sal_sec_juin1;
CODES CONTRIBUTION
---------- ------------
100 4,625 ==Î Salaire S100 : 4*4,625 = 18,5
200 1,75 ==Î Salaire S200 : 4*1,75 = 7
300 ,625 ==Î Salaire S300 : 4* 0,625= 2,5
----------
7: Contribution d'un med pour toutes les sec
Proposé par Chaieb Chiheb MT Iset de Sousse 7/29
Concours Technologue Session 2008
2ème Cas : contribution de chaque médecin sollicité au salaire
SQL> Create view Sal_sec_juin2 (Codes, Sal_juin,nbr_med) as
Select codes , sum(coutc*0.1) sal_juin,
(select count(distinct codem) from rv ) as nbr_med from medecin
m, rv r where [Link]=[Link] and etatrv='R'
and codes is not null and to_char(datehrv,'mm')='06'
and to_char(datehrv,'yy')='08' group by codes;
View created.
SQL> select * from sal_sec_juin2;
CODES SAL_JUIN NBR_MED
---------- ---------- ----------
100 18,5 3
200 7 3
300 2,5 3
Contribution de chaque médecin actif au salaire de chaque secrétaire :
SQL >Select codes, sal_juin/nbr_med as contribution from sal_sec_juin2;
CODES CONTRIBUTION
------ ------------
100 6,16666667
200 2,33333333
300 ,833333333
Pour la secrétaire 100, le trois médecins paye chacun 6,166667
Salaire total : 3*(6.16666667+2.33333333+0.833333333) = 28
3ème Cas : pour chaque secrétaire on cherche la contribution de chacun des
médecins sollicités par de RDV passés par la secrétaire elle-même.
Etape 1 : créer la vue Sal_sec_juin3(Codes, Sal_Juin, nbr_med_sol)
Nbr_med_sol : le nombre de médecins sollicités par les secrétaires
Le Salaire de la secrétaire est mutualisé entre les médecins sujets des
consultations passées par la secrétaire
SQL> Create view Sal_sec_juin3 (Codes, Sal_juin,nbr_med) as
Select codes , sum(coutc*0.1),count(distinct [Link]) from medecin m, rv r
where [Link]=[Link] and etatrv='R'
Proposé par Chaieb Chiheb MT Iset de Sousse 8/29
Concours Technologue Session 2008
and codes is not null and to_char(datehrv,'mm')='06' and
to_char(datehrv,'yy')='08' group by codes;
View created.
SQL> select * from sal_sec_juin3;
CODES SAL_JUIN NBR_MED
---------- ---------- ----------
100 18,5 3 Î lesquels des médecins ?
200 7 2
300 2,5 1
Ajouter une vue pour connaître lesquels des médecins sujets des RV
Med_Sec_juin : (codeS, Codem )
SQL> Create view Med_Sec_juin (codes,codem)as
Select distinct codes,codem from rv
where etatrv='R' and codes is not null and
to_char(datehrv,'mm')='06' and to_char(datehrv,'yy')='08'
order by codes, codem;
View created.
SQL> select * from Med_Sec_juin;
CODES CODEM
---------- ----------
100 1
100 2
100 3 ==Î les 3 med pour la sec 100
200 1
200 2 ==Î les 2 med pour la sec 200
300 2 ==Î le med pour la sec 300
6 rows selected.
Contribution de chaque médecin dans le salaire de chaque secrétaire
Select ms. codes,codem, sal_juin/nbr_med as contribution from Med_Sec_juin
ms , sal_sec_juin2 ssj where [Link]=[Link]
SQL> Select ms. codes,codem, sal_juin/nbr_med as contribution
from Med_Sec_juin ms , sal_sec_juin2 ssj where [Link]=[Link];
Proposé par Chaieb Chiheb MT Iset de Sousse 9/29
Concours Technologue Session 2008
CODES CODEM CONTRIBUTION
---------- ---------- ------------
100 3 6,16666667
100 2 6,16666667
100 1 6,16666667 ==Î Total Sec 100 : 3*6,166667 = 18,5
200 2 3,5
200 1 3,5 ===Î Total Sec 200 : 2* 3,5 = 7
300 2 2,5 ===Î Total Sec 300 : 2,5
6 rows selected.
Cas 4 : Contribution des médecins dans le salaire total de secrétaires
Contribution Med : Sal_Tot_sec /nbr_med
Créer la vue Sal_sec_juin(Codes, Sal_Juin)
SQL> create view Sal_sec_juin (Codes, Sal_juin) as
Select codes , sum(coutc*0.1) from medecin m, rv r where
[Link]=[Link] and etatrv='R'
and codes is not null and to_char(datehrv,'mm')='06'
group by codes;
View created.
SQL> select * from sal_sec_juin ;
CODES SAL_JUIN
---------- ----------
100 18,5
200 7
300 2,5
Tot_ Salaires = 18,5+7+2,5 = 28
Contribution d'un med dans le sal_tot = Σ(Sal_sec) / Nbre Med = 28 / Nbr_med
create view Contribution(Cont) as
Select (select sum(coutc*0.1) from rv r, medecin m where
[Link]=[Link] and codes is not null and to_char(datehrv,'mm')='06'
and etatrv='R' ) / (select count(*) from medecin) as Contribution from
dual;
Select * from Contribution ;
Proposé par Chaieb Chiheb MT Iset de Sousse 10/29
Concours Technologue Session 2008
SQL> Create view Contribution_Med_Juin (contribution) as
Select (select sum(coutc*0.1) from rv r, medecin m where [Link]=[Link]
and codes is not null
and to_char(datehrv,'mm')='06' and etatrv='R' )/(select count(*) from
medecin) as Contribution from dual;
View created.
SQL> select * from Contribution_med_juin;
CONTRIBUTION
------------ (5 médecins )
5,6
SQL> delete from medecin where codem=5;
1 row deleted. (4 Médecins)
SQL> select * from Contribution_med_juin;
CONTRIBUTION
------------
7
Cas 5 : Salaire selon les nbr de RV cumulés par secrétaire
Salaire des secrétaires au mois de juin
SQL> create view Sal_sec_juin (codes,sal_juin) as
Select codes , sum(coutc*0.1) from medecin m, rv r where
[Link]=[Link] and etatrv='R'
and codes is not null and to_char(datehrv,'mm')='06'
group by codes;
View created.
SQL> select * from sal_sec_juin ;
Proposé par Chaieb Chiheb MT Iset de Sousse 11/29
Concours Technologue Session 2008
CODES SAL_JUIN
---------- ----------
100 18,5
200 7
300 2,5
Contribution de chaque médecin dans le salaire de chacune de secrétaire
Select [Link] , codes, sum(coutc*0.1) as cont from medecin m, rv r where
[Link]=[Link]
and etatrv='R' and to_char(datehrv,'mm')='06' and codes is not null
group by [Link],codes;
CODEM CODES CONT
----- ---------- ----------
1 100 9
1 200 4,5
2 100 2,5
2 200 2,5 Salaire Sec 200= 4.5+2.5=7
2 300 2,5 Salaire Sec 300= 2.5
3 100 7 Salaire Sec 100= 9+2.5+7=18.5
Create View (codem, codes, contribution) as
Select [Link] , codes, sum(coutc*0.1) as cont from medecin m, rv r where
[Link]=[Link]
and etatrv='R' and to_char(datehrv,'mm')='06' and codes is not null
group by [Link],codes;
Autre Requêtes Proposés Avec Corrigé
1. Nombre de RV réalisés par Secrétaire
SQL> select codes,count(datehrv) as nbr_rv from rv where etatrv='R' and codes is not null
group by codes;
CODES NBR_RV
-------- ----------
100 19
200 2
300 1
Uniquement En mois de Juin 2008
Proposé par Chaieb Chiheb MT Iset de Sousse 12/29
Concours Technologue Session 2008
SQL> select codes,count(datehrv) as nbr_rv from rv where etatrv='R' and codes is not null
and to_char(datehrv,'mm')='06' and to_char(datehrv,'yy')= '08' group by codes;
CODES NBR_RV
---------- ----------
100 5
200 2
300 1
2. Liste de Secrétaires ayant passées des RV pour tous les médecins
SQL> Select count(distinct codem) nbr_med, codes from rv where codes is not null group by
codes having count(distinct codem)=(select count(*) from medecin );
NBR_MED CODES
--------- ----------
3 100
Pour avoir ce résultat supprimer le médecin de code 4 de la table medecin
3. Nombre des secrétaires ayant passées des RV réalisés plus que la moyenne
Select count (datehrv) , codes from rv where etatrv='R' and codes is not null group by codes
having count(datehrv)>= (select avg(count(datehrv)) from rv where etatrv='R'
and codes is not null group by codes);
COUNT(DATEHRV) CODES
-------------- ----------
19 100
4. Les secrétaires ayant passées le plus grand nombre de RV réalisés :
SQL> Select count (datehrv) Nbr_Max_RV, codes from rv where etatrv='R' and codes is not
null group by codes having count(datehrv) >= all (select count(datehrv) from RV where
etatrv='R' and codes is not null group by codes);
NBR_MAX_RV CODES
---------- ----------
19 100
d. nbr moyen des RDV par médecin différents médecins que
chaque secrétaire leur a passé des RDV
SQL> select codes, count(datehrv)/count(distinct codem) as nbr_moy
2 from rv where codes is not null group by codes;
CODES NBR_MOY
---------- ----------
100 7
200 1,5
300 2
Proposé par Chaieb Chiheb MT Iset de Sousse 13/29
Concours Technologue Session 2008
PARTIE PL/SQL
A. Procédure pliste_rdv (nom , prénom) : lister les RDV d'un médecin donné pour la date
du jour .
Create or replace procedure Pliste_rdv(pnom in [Link]%type,pprenom in
[Link]%type)
is
cursor c_rdv is select datehrv,[Link],nomp,prenomp from
patient p,Rv R, Medecin m
where [Link]=[Link] and [Link]=[Link] and
to_char(datehrv,'dd/mm/yy') = to_char(sysdate,'dd/mm/yy') and etatrv='P'
and trim(upper([Link]))=trim(upper(pnom)) and
trim(upper([Link]))=trim(upper(pprenom));
begin
dbms_output.put_line (' Médecin : '||upper(pnom) || ' '||pprenom);
dbms_output.put_line (' DATE :'||sysdate);
dbms_output.put_line ('****** Liste des Rendez Vous****** ' );
for v_rdv in c_rdv
loop
dbms_output.put_line ('Heure RV :'|| to_char(v_rdv.datehrv,'hh:mi') || ' Patient :' ||
v_rdv.codep||' '||v_rdv.nomp|| ' '||v_rdv.prenomp);
-- ||v_rdv.nomp||'Date Heure :'||) ;
end loop;
end;
/
Test de la procédure : n'oublier pas de mettre votre server output à ON
Set serveroutput on
SQL> execute pliste_rdv ('ibn sina', 'mohamed');
****** Liste des Rendez Vous******
Médecin : IBN SINA MOHAMED
DATE :15/05/10
-----------------------------------------------------
Heure RV :10:15 Patient :1002 Rahal Zied
-----------------------------------------------------
Proposé par Chaieb Chiheb MT Iset de Sousse 14/29
Concours Technologue Session 2008
Heure RV :11:15 Patient :1002 Rahal Zied
-----------------------------------------------------
PL/SQL procedure successfully completed.
B. Programmation d'un RDV
Solution 1 :
Curseur qui ramène les rdv au jour proposé pour ce medecin par ordre desc
Si curseur vide ,
Programmer le rdv proposé au jour proposé
Sinon
Chercher le dernier RDV dans le jour proposé
Si en accord avec la page horaire
Programmer le rdv après 30 mn du dernier rdv
Sinon (en dehors de la plage ) ,
Message ' RDV à reprogrammer '
Code PLSQL de cette Solution
Set serveroutput on
Create or replace procedure Prog_rdv
(daterv date, pcodem [Link]%type,pcodep [Link]%type,HplageD
smallint,HplageF smallint)
is
vmed medecin%rowtype;
vpat patient%rowtype;
cursor lrdv is Select codem, codep,datehrv from rv1
where
to_char(datehrv,'dd/mm/yy') = to_char(daterv,'dd/mm/yy') and codem=pcodem
order by to_char(datehrv,'hh24:mi') desc ;
vrdv lrdv%rowtype;
nbrdv integer;
h smallint;
m smallint;
begin
dbms_output.put_line (' DATE :'||sysdate);
select * into vmed from Medecin where codem=pcodem;
select * into vpat from patient where codep=pcodep;
select count(datehrv) into nbrdv from rv where
to_char(datehrv,'dd/mm/yy') = to_char(daterv,'dd/mm/yy') and codem=pcodem ;
dbms_output.put_line ('Medecin : '|| [Link] || ' '|| [Link]||' '||[Link]);
Proposé par Chaieb Chiheb MT Iset de Sousse 15/29
Concours Technologue Session 2008
dbms_output.put_line ('Patient : '|| [Link] || ' '|| [Link]||' '||[Link]);
dbms_output.put_line ('Le nombre de RDV pour ce Jour est : '||nbrdv);
open lrdv;
fetch lrdv into vrdv;
if (lrdv% notfound) then -- pas de rdv pour ce jour
dbms_output.put_line ('****** Pas de RDV programmés à ce moment****** ' );
dbms_output.put_line ('RDV Programmé au :'||HplageD);
else
h:=to_number(to_char([Link],'hh24'));
m:=to_number(to_char([Link],'mi'));
dbms_output.put_line ('Dernier RDV Programmé au :'||to_char([Link],'hh24:mi') );
if (h between HplageD and HplageF) then
m:=m+30;
dbms_output.put_line ('Heure RV Programmé :'|| h||' Minute :' || m);
dbms_output.put_line ('-----------------------------------------------------');
else
dbms_output.put_line (' Plage Horaire indisponible, Reprogrammer votre RDV ');
end if;
end if;
end;
/
Affectation du Premier RDV le 26/06/08
SQL> execute prog_rdv('26/06/08',1,1000,8,12,100);
DATE :18/05/10
Medecin : Mr AL AByadh Slimene
Patient : 1000 Bettaieb Ali
Secretaire : Ben Chirz
Le nombre de RDV pour ce Jour est : 0
****** Pas de RDV programmés à ce Jour******
Le RDV sera programmé au :26/06/08 08:00
PL/SQL procedure successfully completed.
SQL> select * from rv1;
Proposé par Chaieb Chiheb MT Iset de Sousse 16/29
Concours Technologue Session 2008
DATEHRV CODEM CODEP CODES E
------------------------- ---------- ---------- ---------- -
10/06/08 1 1002 200 P
13/06/08 1 1003 P
12/06/08 2 1002 300 P
26/06/08 2 1002 P
26/06/08 3 1003 P
10/06/08 1 1002 200 P
12/06/08 2 1000 100 P
26/06/08 1 1000 100 P
8 rows selected.
Affectation d'un deuxième RDV le 26/06/08
SQL> execute prog_rdv('26/06/08',1,1001,8,12,100);
DATE :18/05/10
Medecin : Mr AL AByadh Slimene
Patient : 1001 Ben Ismaiil Ahmed
Secretaire : Ben Chirz
Le nombre de RDV pour ce Jour est : 1
Dernier RDV Programmé à :08:00
RDV sera Programmé à : 08: 30
-----RDV ENREGISTRE DANS LA BASE-----------
PL/SQL procedure successfully completed.
SQL> select * from rv1;
DATEHRV CODEM CODEP CODES E
------------------------- ---------- ---------- ---------- -
10/06/08 1 1002 200 P
13/06/08 1 1003 P
12/06/08 2 1002 300 P
26/06/08 2 1002 P
26/06/08 3 1003 P
10/06/08 1 1002 200 P
12/06/08 2 1000 100 P
26/06/08 1 1000 100 P
26/06/08 1 1001 100 P
9 rows selected.
Troisième RDV au 26/06/08
SQL> execute prog_rdv('26/06/08',1,1002,8,12,200);
DATE :18/05/10
Medecin : Mr AL AByadh Slimene
Proposé par Chaieb Chiheb MT Iset de Sousse 17/29
Concours Technologue Session 2008
Patient : 1002 Rahal Zied
Secretaire : Ben Aghlab
Le nombre de RDV pour ce Jour est : 2
Dernier RDV Programmé à :08:30
RDV sera Programmé à : 09: 00
-----RDV ENREGISTRE DANS LA BASE-----------
PL/SQL procedure successfully completed.
Select to_char(datehrv,'dd/mm/yy hh24:mi') as Dateheure , codem, codep,codes,etatrv from
rv1;
DATEHEURE CODEM CODEP CODES E
-------------- ---------- ---------- ---------- -
10/06/08 10:30 1 1002 200 P
13/06/08 09:30 1 1003 P
12/06/08 10:15 2 1002 300 P
26/06/08 11:15 2 1002 P
26/06/08 11:30 3 1003 P
10/06/08 09:00 1 1002 200 P
12/06/08 10:45 2 1000 100 P
26/06/08 08:00 1 1000 100 P
26/06/08 08:30 1 1001 100 P
26/06/08 09:00 1 1002 200 P
10 rows selected.
Plage Horaire indisponible
SQL> execute prog_rdv('26/06/08',3,1003,8,10,100);
DATE :18/05/10
Medecin : Mr AL Baghdadi Abdallah
Patient : 1003 Ibn Sibaa Tahar
Secretaire : Ben Chirz
Le nombre de RDV pour ce Jour est : 1
Dernier RDV Programmé à :11:30
Plage Horaire indisponible, Reprogrammer votre RDV
PL/SQL procedure successfully completed.
Solution 2 : Maintenue
Algorithme :
Proposé par Chaieb Chiheb MT Iset de Sousse 18/29
Concours Technologue Session 2008
Procedure prog_rdv2 ( DateProp, codeMed, codePat, codeSec, PlageD, PlageF)
Début
Curseur qui ramène tous les RDV déjà programmés au jour proposé par l'user.
Trouv=faux.
Lire le premier RDV existant dans le curseur .
Si curseur vide (pas des rdv existants à ce jour proposé )
le rdv proposé sera programmé au jour - heure proposés par l'user.
Trouv = vraie
Sinon
-- Si Heure Proposée <= Au Premier RDV
Programmer le RDV avant le Premier de 30mn ou à l'heure proposée
Sinon
Tant Que (trouv=faux et il Existe des RDV dans ce jour)
Debut
-- on est sur le rDV courant
Calculer le prochain RDV théorique RDV Actuel+30 mn
Vérifier si le prochain est pris ou non
Si proch_rdv est libre
Si le proch RDV est dans la plage Horaire
Insérer un RDV au proch RDV
Trouv=vraie
Exit ;
Finsi
Sinon // proch rdv est occupé
Message de continuation de la recherche;
Finsi
Lire le RDV suivant Programmé
Fin tant que
Si trouv=faux et fin des RDV programmés à ce jour
Créer le RDV à la page proposée et qui >= à tous les RDV Existants
Finsi
Finsi
Finsi
Fin Procédure
Code PL/SQL
set serveroutput on
Proposé par Chaieb Chiheb MT Iset de Sousse 19/29
Concours Technologue Session 2008
create or replace procedure Prog_rdv2(daterv date, pcodem
[Link]%type,pcodes number,pcodep [Link]%type,HplageD
smallint,HplageF smallint)
is
vmed medecin%rowtype;
vpat patient%rowtype;
cursor lrdv is Select codem, codep,datehrv from rv1
where
to_char(datehrv,'dd/mm/yy') = to_char(daterv,'dd/mm/yy') and codem=pcodem
order by to_char(datehrv,'hh24:mi');
vrdv lrdv%rowtype;
nbrdv integer;
h smallint;
m smallint;
hpr smallint;
mpr smallint;
ch varchar2(14);
dd date;
vnoms [Link]%type;
n integer;
trouv smallint;
begin
trouv:=0; -- au debut le rdv n'est pas encore programmé
dbms_output.put_line (' DATE :'||sysdate);
select * into vmed from Medecin where codem=pcodem;
select * into vpat from patient where codep=pcodep;
if (pcodes <>0) then
select noms into vnoms from secretaire where codes=pcodes;
else
vnoms:='****************';
end if;
nbrdv:=0;
select count(*) into nbrdv from rv1 where
to_char(datehrv,'dd/mm/yy') = to_char(daterv,'dd/mm/yy') and codem=pcodem ;
dbms_output.put_line ('Medecin : '|| [Link] || ' '|| [Link]||' '||[Link]);
dbms_output.put_line ('Patient : '|| [Link] || ' '|| [Link]||' '||[Link]);
dbms_output.put_line ('Secretaire : '||vnoms);
dbms_output.put_line ('Le nombre de RDV pour ce Jour est : '||nbrdv);
dbms_output.put_line ('*******Debut traitement ****');
open lrdv;
fetch lrdv into vrdv;
if (lrdv%notfound) then -- pas de rdv pour ce jour
Proposé par Chaieb Chiheb MT Iset de Sousse 20/29
Concours Technologue Session 2008
dbms_output.put_line ('****** Pas des RDV programmés à ce Jour****** ' );
Ch:=to_char(daterv,'dd/mm/yy')||to_char(HplageD,'09')||'00';
dd:=to_date(ch,'dd/mm/yy hh24:mi');
dbms_output.put_line ('Le RDV sera programmé au :'||to_char(dd,'dd/mm/yy
hh24:mi'));
insert into rv1 values (dd,pcodem,pcodep,pcodes,'P');
dbms_output.put_line ('-----RDV ENREGISTRE DANS LA BASE----'||to_char(dd,'dd-
mm-yy hh24:mi')||' '||pcodem||' '||pcodep);
trouv:=1;
else
-- Journée contient des RDV
h:=to_number(to_char([Link],'hh24')); -- heure du rdv courant
m:=to_number(to_char([Link],'mi')); -- minute du rdv courant
if (hplaged<=h ) then -- plageD est avant le premier RDV
if (m>= 30) then
m:= m-30;
else
h:=h-1;
m:=m+30;
end if;
-- préparation à l'enregistrement dans la base
Ch:=to_char(daterv,'dd/mm/yy')||to_char(h,'09')||to_char(m,'09');
dd:=to_date(ch,'dd/mm/yy hh24:mi');
insert into rv1 values (dd,pcodem,pcodep,pcodes,'P');
trouv:=1;
dbms_output.put_line ('---RDV ENREGISTRE DANS LA BASE : '||to_char(dd,'dd-
mm-yy hh24:mi')||' '||pcodem||' '||pcodep);
else -- plageD est après le premier rendv
while (lrdv%found and trouv=0)
loop
h:=to_number(to_char([Link],'hh24')); -- heure du rdv courant
m:=to_number(to_char([Link],'mi')); -- minute du rdv courant
dbms_output.put_line (lrdv%rowcount ||' On trouve un RDV Programmé à
:'||to_char([Link],'hh24:mi') );
-- rechercher la première disponibilité du med après ce RDV
-- calculer la date heure du prochain rdv théorique
Proposé par Chaieb Chiheb MT Iset de Sousse 21/29
Concours Technologue Session 2008
-- datehrvp=datehrv+30mn;
hpr:=h;
mpr:=m+30;
if mpr>=60 then
hpr:=h+1;
mpr:=mpr-60;
end if;
dbms_output.put_line ('Prochain RDV théorique à :'|| 'h= '||h||'hpr=
'||to_char(hpr,'09')||':' ||to_char(mpr,'09'));
Ch:=to_char(daterv,'dd/mm/yy')||to_char(hpr,'09')||to_char(mpr,'09');
dd:=to_date(ch,'dd/mm/yy hh24:mi');
n:=0;
-- rechercher si le prochain rdv théorique est pris déjà
select count(*) into n from rv1 where datehrv=dd and codem=pcodem;
if (n=0) then
-- le rdv prochain (hpr,mpr) est libre
dbms_output.put_line ('Prochain RDV théorique est libre');
if (h> HplageD and h< HplageF) then
dbms_output.put_line ('Plage Horaire convient avec la première disponibilté du
medecin');
insert into rv1 values (dd,pcodem,pcodep,pcodes,'P');
dbms_output.put_line ('-----RDV ENREGISTRE DANS LA BASE : '||to_char(dd,'dd-
mm-yy hh24:mi')||' '||pcodem||' '||pcodep);
trouv:=1;
close lrdv;
exit;
end if;
else -- n!=0
dbms_output.put_line (' Prochain RDV est indisponible ');
dbms_output.put_line (' Recherche du prochain RDV disponible ');
end if;
fetch lrdv into vrdv;
end loop;
-- le cas hplageD est sup à tous les RDV
if (trouv=0) then
Ch:=to_char(daterv,'dd/mm/yy')||to_char(HplageD,'09')||'00';
dd:=to_date(ch,'dd/mm/yy hh24:mi');
dbms_output.put_line ('Le RDV sera programmé au :'||to_char(dd,'dd/mm/yy
hh24:mi'));
insert into rv1 values (dd,pcodem,pcodep,pcodes,'P');
dbms_output.put_line ('-----RDV ENREGISTRE DANS LA BASE----'||to_char(dd,'dd-
mm-yy hh24:mi')||' '||pcodem||' '||pcodep);
trouv:=1;
Proposé par Chaieb Chiheb MT Iset de Sousse 22/29
Concours Technologue Session 2008
end if;
end if; -- hplageD <= premier rdv
end if; -- pas des rdv à ce jour
if (trouv=0) then
dbms_output.put_line ('********Pas de disponibilté pour cette palge
Horaire**********');
end if;
end;
/
commit;
Test de la procédure sur 11 RDV à programmer
Départ avec 0 RDV au jour proposé par le premier RDV
Le fichier [Link] comporte l'appel de la procédure pour réaliser 11 RDV
execute prog_rdv2('12/06/08',1,200,1000,10,12);
execute prog_rdv2('12/06/08',1,100,1001,8,12);
execute prog_rdv2('12/06/08',1,100,1002,14,16);
execute prog_rdv2('12/06/08',1,200,1003,9,12);
execute prog_rdv2('12/06/08',1,300,1000,8,12);
execute prog_rdv2('12/06/08',1,300,1002,13,15);
execute prog_rdv2('12/06/08',1,200,1003,11,16);
execute prog_rdv2('12/06/08',1,100,1000,17,19);
execute prog_rdv2('12/06/08',1,300,1000,8,13);
execute prog_rdv2('12/06/08',1,100,1000,9,18);
execute prog_rdv2('12/06/08',1,200,1000,9,12);
SQL> @lance
DATE :21/05/10
Medecin : Mr AL AByadh Slimene
Patient : 1000 Bettaieb Ali
Secretaire : Ben Aghlab
Le nombre de RDV pour ce Jour est : 0
*******Debut traitement ****
****** Pas des RDV programmés à ce Jour******
Le RDV sera programmé au :12/06/08 10:00
-----RDV ENREGISTRE DANS LA BASE----12-06-08 10:00 1 1000
PL/SQL procedure successfully completed.
Proposé par Chaieb Chiheb MT Iset de Sousse 23/29
Concours Technologue Session 2008
DATE :21/05/10
Medecin : Mr AL AByadh Slimene
Patient : 1001 Ben Ismaiil Ahmed
Secretaire : Ben Chirz
Le nombre de RDV pour ce Jour est : 1
*******Debut traitement ****
---RDV ENREGISTRE DANS LA BASE : 12-06-08 09:30 1 1001
PL/SQL procedure successfully completed.
DATE :21/05/10
Medecin : Mr AL AByadh Slimene
Patient : 1002 Rahal Zied
Secretaire : Ben Chirz
Le nombre de RDV pour ce Jour est : 2
*******Debut traitement ****
1 On trouve un RDV Programmé à :09:30
Prochain RDV théorique à :h= 9hpr= 10: 00
Prochain RDV théorique est libre
2 On trouve un RDV Programmé à :10:00
Prochain RDV théorique à :h= 10hpr= 10: 30
Prochain RDV théorique est libre
Le RDV sera programmé au :12/06/08 14:00
-----RDV ENREGISTRE DANS LA BASE----12-06-08 14:00 1 1002
PL/SQL procedure successfully completed.
DATE :21/05/10
Medecin : Mr AL AByadh Slimene
Patient : 1003 Ibn Sibaa Tahar
Secretaire : Ben Aghlab
Le nombre de RDV pour ce Jour est : 3
*******Debut traitement ****
---RDV ENREGISTRE DANS LA BASE : 12-06-08 09:00 1 1003
PL/SQL procedure successfully completed.
DATE :21/05/10
Medecin : Mr AL AByadh Slimene
Patient : 1000 Bettaieb Ali
Secretaire : Dali
Le nombre de RDV pour ce Jour est : 4
*******Debut traitement ****
---RDV ENREGISTRE DANS LA BASE : 12-06-08 08:30 1 1000
PL/SQL procedure successfully completed.
DATE :21/05/10
Medecin : Mr AL AByadh Slimene
Proposé par Chaieb Chiheb MT Iset de Sousse 24/29
Concours Technologue Session 2008
Patient : 1002 Rahal Zied
Secretaire : Dali
Le nombre de RDV pour ce Jour est : 5
*******Debut traitement ****
1 On trouve un RDV Programmé à :08:30
Prochain RDV théorique à :h= 8hpr= 09: 00
Prochain RDV est indisponible
Recherche du prochain RDV disponible
2 On trouve un RDV Programmé à :09:00
Prochain RDV théorique à :h= 9hpr= 09: 30
Prochain RDV est indisponible
Recherche du prochain RDV disponible
3 On trouve un RDV Programmé à :09:30
Prochain RDV théorique à :h= 9hpr= 10: 00
Prochain RDV théorique est libre
4 On trouve un RDV Programmé à :10:00
Prochain RDV théorique à :h= 10hpr= 10: 30
Prochain RDV théorique est libre
5 On trouve un RDV Programmé à :14:00
Prochain RDV théorique à :h= 14hpr= 14: 30
Prochain RDV théorique est libre
Plage Horaire convient avec la première disponibilté du medecin
-----RDV ENREGISTRE DANS LA BASE : 12-06-08 14:30 1 1002
PL/SQL procedure successfully completed.
DATE :21/05/10
Medecin : Mr AL AByadh Slimene
Patient : 1003 Ibn Sibaa Tahar
Secretaire : Ben Aghlab
Le nombre de RDV pour ce Jour est : 6
*******Debut traitement ****
1 On trouve un RDV Programmé à :08:30
Prochain RDV théorique à :h= 8hpr= 09: 00
Prochain RDV est indisponible
Recherche du prochain RDV disponible
2 On trouve un RDV Programmé à :09:00
Prochain RDV théorique à :h= 9hpr= 09: 30
Prochain RDV est indisponible
Recherche du prochain RDV disponible
3 On trouve un RDV Programmé à :09:30
Prochain RDV théorique à :h= 9hpr= 10: 00
Prochain RDV théorique est libre
4 On trouve un RDV Programmé à :10:00
Prochain RDV théorique à :h= 10hpr= 10: 30
Prochain RDV théorique est libre
5 On trouve un RDV Programmé à :14:00
Prochain RDV théorique à :h= 14hpr= 14: 30
Prochain RDV est indisponible
Recherche du prochain RDV disponible
Proposé par Chaieb Chiheb MT Iset de Sousse 25/29
Concours Technologue Session 2008
6 On trouve un RDV Programmé à :14:30
Prochain RDV théorique à :h= 14hpr= 15: 00
Prochain RDV théorique est libre
Plage Horaire convient avec la première disponibilté du medecin
-----RDV ENREGISTRE DANS LA BASE : 12-06-08 15:00 1 1003
PL/SQL procedure successfully completed.
DATE :21/05/10
Medecin : Mr AL AByadh Slimene
Patient : 1000 Bettaieb Ali
Secretaire : Ben Chirz
Le nombre de RDV pour ce Jour est : 7
*******Debut traitement ****
1 On trouve un RDV Programmé à :08:30
Prochain RDV théorique à :h= 8hpr= 09: 00
Prochain RDV est indisponible
Recherche du prochain RDV disponible
2 On trouve un RDV Programmé à :09:00
Prochain RDV théorique à :h= 9hpr= 09: 30
Prochain RDV est indisponible
Recherche du prochain RDV disponible
3 On trouve un RDV Programmé à :09:30
Prochain RDV théorique à :h= 9hpr= 10: 00
Prochain RDV théorique est libre
4 On trouve un RDV Programmé à :10:00
Prochain RDV théorique à :h= 10hpr= 10: 30
Prochain RDV théorique est libre
5 On trouve un RDV Programmé à :14:00
Prochain RDV théorique à :h= 14hpr= 14: 30
Prochain RDV est indisponible
Recherche du prochain RDV disponible
6 On trouve un RDV Programmé à :14:30
Prochain RDV théorique à :h= 14hpr= 15: 00
Prochain RDV est indisponible
Recherche du prochain RDV disponible
7 On trouve un RDV Programmé à :15:00
Prochain RDV théorique à :h= 15hpr= 15: 30
Prochain RDV théorique est libre
Le RDV sera programmé au :12/06/08 17:00
-----RDV ENREGISTRE DANS LA BASE----12-06-08 17:00 1 1000
PL/SQL procedure successfully completed.
DATE :21/05/10
Medecin : Mr AL AByadh Slimene
Patient : 1000 Bettaieb Ali
Secretaire : Dali
Le nombre de RDV pour ce Jour est : 8
*******Debut traitement ****
Proposé par Chaieb Chiheb MT Iset de Sousse 26/29
Concours Technologue Session 2008
---RDV ENREGISTRE DANS LA BASE : 12-06-08 08:00 1 1000
PL/SQL procedure successfully completed.
DATE :21/05/10
Medecin : Mr AL AByadh Slimene
Patient : 1000 Bettaieb Ali
Secretaire : Ben Chirz
Le nombre de RDV pour ce Jour est : 9
*******Debut traitement ****
1 On trouve un RDV Programmé à :08:00
Prochain RDV théorique à :h= 8hpr= 08: 30
Prochain RDV est indisponible
Recherche du prochain RDV disponible
2 On trouve un RDV Programmé à :08:30
Prochain RDV théorique à :h= 8hpr= 09: 00
Prochain RDV est indisponible
Recherche du prochain RDV disponible
3 On trouve un RDV Programmé à :09:00
Prochain RDV théorique à :h= 9hpr= 09: 30
Prochain RDV est indisponible
Recherche du prochain RDV disponible
4 On trouve un RDV Programmé à :09:30
Prochain RDV théorique à :h= 9hpr= 10: 00
Prochain RDV théorique est libre
5 On trouve un RDV Programmé à :10:00
Prochain RDV théorique à :h= 10hpr= 10: 30
Prochain RDV théorique est libre
Plage Horaire convient avec la première disponibilté du medecin
-----RDV ENREGISTRE DANS LA BASE : 12-06-08 10:30 1 1000
PL/SQL procedure successfully completed.
DATE :21/05/10
Medecin : Mr AL AByadh Slimene
Patient : 1000 Bettaieb Ali
Secretaire : Ben Aghlab
Le nombre de RDV pour ce Jour est : 10
*******Debut traitement ****
1 On trouve un RDV Programmé à :08:00
Prochain RDV théorique à :h= 8hpr= 08: 30
Prochain RDV est indisponible
Recherche du prochain RDV disponible
2 On trouve un RDV Programmé à :08:30
Prochain RDV théorique à :h= 8hpr= 09: 00
Prochain RDV est indisponible
Recherche du prochain RDV disponible
3 On trouve un RDV Programmé à :09:00
Prochain RDV théorique à :h= 9hpr= 09: 30
Prochain RDV est indisponible
Proposé par Chaieb Chiheb MT Iset de Sousse 27/29
Concours Technologue Session 2008
Recherche du prochain RDV disponible
4 On trouve un RDV Programmé à :09:30
Prochain RDV théorique à :h= 9hpr= 10: 00
Prochain RDV théorique est libre
5 On trouve un RDV Programmé à :10:00
Prochain RDV théorique à :h= 10hpr= 10: 30
Prochain RDV est indisponible
Recherche du prochain RDV disponible
6 On trouve un RDV Programmé à :10:30
Prochain RDV théorique à :h= 10hpr= 11: 00
Prochain RDV théorique est libre
Plage Horaire convient avec la première disponibilté du medecin
-----RDV ENREGISTRE DANS LA BASE : 12-06-08 11:00 1 1000
PL/SQL procedure successfully completed.
SQL> @l
HRV CODEM
-------------- ----------
12/06/08 08:00 1
12/06/08 08:30 1
12/06/08 09:00 1
12/06/08 09:30 1
12/06/08 10:00 1
12/06/08 10:30 1
12/06/08 11:00 1
12/06/08 14:00 1
12/06/08 14:30 1
12/06/08 15:00 1
12/06/08 17:00 1
11 rows selected.
********************************FIN EPREUVE ***************************
Quelques Recommandations sur les champs date- heure
- Pour recherche une date dans une table qui contient un champ date heure :
Select * from rv1 where to_char(datehrv, 'mm/dd/yy')= '12/06/10';
Ou encore : Select * from rv1 where to_date(to_char(datehrv, 'mm/dd/yy'))=
to_date('12/06/10', 'dd/mm/yy')
- Pour afficher la date et l'heure , utiliser to_char (champdH, 'dd/mm/yy hh24:mi') A mois
que le champ est de type timestamp. Select champDH,,, from table;
A ne pas écrire :
Select * from rv1 where datehrv=to_date('12/06/08') puisque le champ datehrv contient
une date heure. , mail plutôt écrire :
Select * from rv where to_char(datehrv,'dd/mm/yy')='12/06/08';
Proposé par Chaieb Chiheb MT Iset de Sousse 28/29
Concours Technologue Session 2008
- pour afficher les champs dateheure triés sur l'heure et pour une date donnée, on
écrit :
Select to_char(datehrv,'dd-mm-yy hh24:mi') as Date_HeureRDV , codem,..
From RV where to_char(datehrv,'dd/mm/yy')= '12/06/08' order by
To_char (datehrv,'hh24:mi');
Proposé par Chaieb Chiheb MT Iset de Sousse 29/29