Consider
the following entities and their relationship.
Donor
(donor_no, donor_name, city)
Blood_donation_detail
(bd_no, blood_group, qty, date_of_collection)
Relationship
between Donor and Blood_donation_detail is one-to-many.
Constraints:primary
key, foreign key,donor_name should not be null,blood_group can be A+, A-, B+,
B-, AB+, AB-, O+, O-
Create
a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write
a function which will count total amount of blood collected for a given blood
group on given date.
SQL>
set serveroutput on
SQL>
create or replace function fun1(tbl in varchar) return number as tb number;
2
begin
3
select sum(qty) into tb from donor, blooddonation where
donor.dno=blooddonation.dno and bloodgroup='b+';
4 if
sql %found then
5
return(tb);
6 else
7
return null;
8 end
if;
9 end
fun1;
10 /
Function
created.
SQL>
begin
2
dbms_output.put_line('total blood-'||fun1('14/4/2015'));
3 end;
4 /
total
blood-2
PL/SQL
procedure successfully completed.
2)Write
a cursor which will display donor wise blood donation details. (Use
parameterized cursor)
SQL>
set serveroutput on
SQL>
declare
2 cursor c1(dwise donor.dname%type)is select
dname, bloodgroup, qty, dateofcoll from donor,blooddonation where donor.dno=blooddonation.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.bloodgroup||' '||c.qty||'
'||c.dateofcoll);
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
b+ 1 14/4/2015
lokesh
b+ 1 14/4/2015
roshan
a- 3 11/3/2012
shashikant
ab+ 3 31/11/2014
vishal
o+ 2 25/9/2013
PL/SQL
procedure successfully completed.
