Esercizi riassuntivi PL/SQL

Esercizi riassuntivi PL/SQL
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 BETWEEN v_DataI
and v_DataF and R_DATAF BETWEEN v_DataI and v_DataF
and S_COD=E_PRESTAZIONE
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;