Consider
the following entities and their relationship.
Patient
(p_no, p_name, p_addr)
Doctor
(d_no, d_name, d_addr, city)
Relationship
between Patient and Doctor is many-to-many with descriptive attribute disease
and no_of_visits.
Constraints:primary
key, foreign key,primary key for third table(p_no, d_no, disease),p_name and
d_name should not be null.
Create
a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write
a function which will count total number of patients visiting to given doctor
for ‘Asthma’.
SQL>
set serveroutput on
SQL>
create or replace function
2
fun1(nop in varchar) return number as nofp number;
3
begin
4
select count(pname) into nofp from patient, doctor, patientdoctor where
patient.pno=patientdoctor.pno and doctor.dno=patientdoctor.dno and
disease='headache';
5 if
sql %found then
6
return(nofp);
7 else
8
return null;
9 end
if;
10 end
fun1;
11 /
Function
created.
SQL>
begin
2
dbms_output.put_line('no of patients -'||fun1('harsh'));
3 end;
4 /
no
of patients-2
PL/SQL
procedure successfully completed.
2)Write
a cursor which will display doctor wise details of patients visited to them.
(Use parameterized cursor)
SQL>
set serveroutput on
SQL>
declare
2
cursor c1(dwise doctor.dname%type)is select dname, pname, patient.paddr
from patient, doctor, p
atientdoctor
where patient.pno=patientdoctor.pno and doctor.dno=patientdoctor.dno order by
dname;
3 c
c1%rowtype;
4
begin
5 open
c1('&dwise');
6 loop
7
fetch c1 into c;
8 exit
when c1%notfound;
9
dbms_output.put_line(c.dname||' '||c.pname||' '||c.paddr);
10 end
loop;
11
close c1;
12 end;
13 /
Enter
value for dwise: harsh
old 5: open c1('&dwise');
new 5: open c1('harsh');
harsh
harsh delhi
PL/SQL
procedure successfully completed.
