Esercizi riassuntivi PL/SQL I 1) Sia dato il seguente schema

Esercizi riassuntivi PL/SQL I
1) Sia dato il seguente schema relazionale che modella parte del database del SI di un ospedale:
PAZIENTI(CF,Nome,Cognome,Sesso,DataN)
PRESTAZIONI(Cod Nome,Costo)
RICOVERI(Paziente:PAZIENTI,DataInizio,DataFine)
EROGAZIONI(Paziente:Paziente:RICOVERI,DataRicovero:DataInizio:RICOVERI,
Prestazione:PRESTAZIONI)
a) Si definisca la base di dati su ORACLE
b) Si scriva la stored procedure Costi che, dato in input un intervallo temporale, calcoli il
ricovero più costoso come quello che massimizza la seguente funzione di costo
SE Durata < 7 gg --> F = 20 + Sum(Costo) - Durata*2
SE Durata >= 7 gg --> F = 15 + Sum(Costo) - Durata*1.5
In output si forniscano tutti i dati ad esso relativi (dati paziente, dati ricovero, e dati erogazioni).
La Durata si considera espressa in giorni. Attenzione ad escludere dal computo i ricoveri non
ancora conclusi.
2) Sia dato il seguente schema relazionale che modella parte del database del DB di un call center:
CLIENTI(IdCliente, Nome, Cognome, DataN, Telefono, TipoContratto)
OPERATORI(IdOp, Nome, Cognome, DataN, Livello)
PROBLEMI(Codice, Descrizione, Classe, Difficoltà)
CHIAMATE(IdChiamata, Cliente:CLIENTI, Operatore:OPERATORI, Problema:PROBLEMI,
DataApertura, Risultato)
OPERAZIONI(Chiamata:CHIAMATE, DataOra, Durata, Descrizione)
a) Si definisca la base di dati su ORACLE
b) Si scriva la stored procedure RatingOperatori(v_data date) che stila una classifica
delle performance degli operatori a partire v_data e restituisce in output quelli con valutazione
negativa. La valutazione è calcolata in base al seguente algoritmo
Valutazione= (VPos-Vneg)/( VPos+Vneg)
 VPos è la quantità di tempo speso da ogni operatore per gestire chiamate conclusesi
positivamente (Risultato=1) e pesate per il livello di difficoltà del problema relativo
(Difficoltà ]0,..1]).
 VNeg è uguale a VPos ma per le chiamate conclusesi negativamente (Risultato = -1)
Le chiamata non ancora conclusesi hanno il campo Risultato = 0 e non devono essere
considerate nel conteggio.
3) Sia dato il seguente schema relazionale che modella la porzione del database dell’ARPA relativo
al monitoraggio dell’inquinamento:
REGIONI(Nome, NumAbitanti)
CITTA(Nome, Regione:REGIONI, NumAbitanti, Distretto:DISTRETTI)
PARAMETRI(Citta:CITTA, Regione:CITTA, Parametro, TipoParametro, Descrizione, Valore)
DISTRETTI(Nome, Descrizione)
OUTPUT(Citta:CITTA, Regione:CITTA, Valore)
a) Si definisca la base di dati su ORACLE
b) Si scriva la procedura Vivibile(VRegione) che inserisca in OUTPUT la graduatoria delle 5
città della regione passata in input che massimizzano il principio di vivibilità così definito: (1) si
individuino le 10 città della regione che minimizzano la media dei parametri di tipo
“Inquinamento” (2) tra queste si costruisca la graduatoria in base alla formula
DimAreeVerdi 
NumCentriCulturali 
1 

NumAbitanti 
10

dove DimAreeVerdi, NumCentriCulturali sono istanze dell’attributo Parametro.
Soluzioni esercizi riassuntivi PL/SQL
Esercizio 1
create table PAZIENTI (
P_CF varchar2(16),
P_NOME varchar2(20),
P_COGNOME varchar2(20),
P_SESSO int,
P_DATAN date,
primary key (P_CF));
create table PRESTAZIONI(
S_COD int,
S_NOME varchar2(50),
S_COSTO float,
primary key (S_COD));
create table RICOVERI (
R_PAZIENTE varchar2(16),
R_DATAI date,
R_DATAF date,
primary key (R_PAZIENTE,R_DATAI),
foreign key (R_PAZIENTE) references PAZIENTI (P_CF));
create table EROGAZIONI (
E_PAZIENTE varchar2(16),
E_DATARIC date,
E_PRESTAZIONE int,
primary key (E_PAZIENTE,E_DATARIC,E_PRESTAZIONE),
foreign key (E_PAZIENTE,E_DATARIC) references RICOVERI(R_PAZIENTE,R_DATAI),
foreign key (E_PRESTAZIONE) references PRESTAZIONI (S_COD));
create or replace procedure Costi(v_DataI date, v_DataF date) IS
CURSOR ricoveri_cursor IS
select R_PAZIENTE,R_DATAI,R_DATAF,sum(S_COSTO)
from RICOVERI,EROGAZIONI,PRESTAZIONI
where R_PAZIENTE=E_PAZIENTE and R_DATAI=E_DATARIC and R_DATAI>=v_DataI and
R_DATAF<=v_DataF
and S_COD=E_PRESTAZIONE and R_DATAF IS NOT NULL
group by R_PAZIENTE,R_DATAI,R_DATAF;
v_costo float;
v_durata INT;
v_DurataMax INT;
v_max float :=0;
v_Data date;
v_CF PAZIENTI.P_CF%TYPE;
c_CF PAZIENTI.P_CF%TYPE;
c_DataI RICOVERI.R_DATAI%TYPE;
c_DataF RICOVERI.R_DATAF%TYPE;
c_Costo PRESTAZIONI.S_COSTO%TYPE;
r_paziente PAZIENTI%rowtype;
BEGIN
open ricoveri_cursor;
loop
fetch ricoveri_cursor into c_CF,c_DataI,c_DataF,c_Costo;
exit when ricoveri_cursor%NOTFOUND;
v_Durata:=c_DataF-C_DataI;
if (v_Durata<7) then
v_costo:=20 + c_Costo -2*(v_Durata);
else
v_costo:=15 + c_Costo -1.5*(v_Durata);
end if;
if (v_costo>v_max) then
v_max:=v_costo;
v_DurataMax:=v_Durata;
v_CF:=c_CF;
v_Data:=c_DataI;
end if;
end loop;
close ricoveri_cursor;
DBMS_OUTPUT.PUT_LINE('Il ricovero più costoso è durato ' || v_DurataMax || '
giorni, il costo é ' || v_max || ' in data ' || v_Data);
select * INTO r_paziente
from PAZIENTI
where P_CF=c_CF;
DBMS_OUTPUT.PUT_LINE('Il ricovero è relativo a ' || r_paziente.P_NOME || ' ' ||
r_paziente.P_COGNOME);
END Costi;
Esercizio 2
create table CLIENTI (
C_ID int,
C_NOME varchar2(20),
C_COGNOME varchar2(20),
C_DATAN date,
C_TELEFONO varchar2(50),
C_TIPOCONTR varchar2(20),
primary key (C_ID));
create table OPERATORI (
O_ID int,
O_NOME varchar2(20),
C_COGNOME varchar2(20),
O_DATAN date,
O_LIVELLO int,
primary key (O_ID));
create table PROBLEMI (
P_COD varchar2(16),
P_DESCRIZIONE varchar2(50),
P_CLASSE varchar2(20),
P_DIFF float,
primary key (P_COD));
create table CHIAMATE(
C_ID int,
C_CLIENTE int,
C_OPERATORE int,
C_PROBLEMA varchar2(16),
C_APERTURA date,
C_RISULTATO int,
primary key (C_ID),
foreign key (C_CLIENTE) references CLIENTI(C_ID),
foreign key (C_OPERATORE) references OPERATORI(O_ID),
foreign key (C_PROBLEMA) references PROBLEMI(P_COD));
create table OPERAZIONI(
R_CHIAMATA int,
R_DATAORA date,
R_DURATA int,
R_DESCRIZIONE varchar2(50),
primary key (R_CHIAMATA,R_DATAORA),
foreign key (R_CHIAMATA) references CHIAMATE(C_ID));
create or replace procedure RatingOperatori(v_Data date) IS
CURSOR cursor_operatori IS
select distinct C_OPERATORE
from
CHIAMATE
where C_APERTURA>=v_Data and C_RISULTATO!=0;
c_OP CHIAMATE.C_OPERATORE%TYPE;
v_pos float;
v_neg float;
v_valutazione float;
BEGIN
open cursor_operatori;
loop
fetch cursor_operatori into c_OP;
exit when cursor_operatori%NOTFOUND;
v_pos :=0;
select sum(R_DURATA*P_DIFF) into v_pos
from
CHIAMATE, OPERAZIONI,PROBLEMI
where C_ID=R_CHIAMATA and C_PROBLEMA=P_COD and C_RISULTATO=1
and C_OPERATORE=c_OP and C_APERTURA>=v_Data;
v_neg :=0;
select sum(R_DURATA*P_DIFF) into v_neg
from
CHIAMATE, OPERAZIONI,PROBLEMI
where C_ID=R_CHIAMATA and C_PROBLEMA=P_COD and C_RISULTATO=-1
and C_OPERATORE=c_OP and C_APERTURA>=v_Data;
if (v_Pos+V_Neg<>0) then
v_Valutazione:=(v_Pos-V_Neg)/(v_Pos+V_Neg);
else
v_Valutazione:=0;
end if;
if (v_Valutazione<0) then
DBMS_OUTPUT.PUT_LINE('L''operatore ' || c_OP
|| 'ha ottenuto una negativa pari a:' || v_Valutazione);
end if;
end loop;
close cursor_operatori;
END
RatingOperatori;
Esercizio 3
create or replace procedure Vivibile (VRegione varchar2) is
--recupero di città e calcolo media ordinati
cursor c_cursor is
select Citta,avg(Valore) as Media
from Parametri
where Regione=Vregione and
TipoParametro='inquinamento'
group by Citta
order by Media asc;
--recupero della classifica ordinata per Valore
cursor c_output is
select *
from Output
where Regione=Vregione
order by Valore desc;
v_cursor c_cursor%ROWTYPE;
v_output c_output%ROWTYPE;
v_parametri number(10);
v_verdi number(10);
v_centri number(10);
v_abitanti number(10);
v_vivibilita number(10,5);
n number(10);
begin
n:=1;
open c_cursor;
while(n<=10)
loop
--recupero di ogni citta
fetch c_cursor into v_cursor;
exit when c_cursor%NOTFOUND;
--recupero di aree verdi
select Valore
into v_verdi
from Parametri
where Parametro='dimareeverdi' and
Citta=v_cursor.Citta;
--recupero di centri culturali
select Valore
into v_centri
from Parametri
where Parametro='numcentriculturali' and
Citta=v_cursor.Citta;
--recupero di numero di abitanti
select NumAbitanti
into v_abitanti
from Citta
where Nome=v_cursor.Citta;
--calcolo della vivibilita
v_vivibilita:=(v_verdi/v_abitanti) * (1 + (v_centri/10));
--inserisco in Output
insert into Output
values(v_cursor.Citta,Vregione,v_vivibilita);
--aggiornamento del counter
n:=n+1;
end loop;
close c_cursor;
n:=1;
open c_output;
--salto delle prime 5
while(n<=5)
loop
fetch c_output into v_output;
exit when c_output%NOTFOUND;
n:=n+1;
end loop;
--cancellazione di tutte le altre
loop
fetch c_output into v_output;
exit when c_output%NOTFOUND;
delete from Output
where Citta=v_output.Citta and
Regione=v_output.Regione;
end loop;
close c_output;
end;