Ready Project |BCA Project | MCA Project | BBA Project | MBA Project |INDIA'S BIGGEST COLLEGE PROJECT STORE

12) 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.
2)Write a cursor which will display donor wise blood donation details. (Use parameterized cursor)


create table Donor(donor_no number primary key, donor_name varchar2(20) not null, city varchar2(20));
	
create table Blood_donation_detail (bd_no number primary key, blood_group varchar2(20), qty number, date_of_collection date,donor_no number references Donor(donor_no));
	

insert into Donor values(1,'yogesh','pune');
	
insert into Donor values(2,'dipali','pune');
	
insert into Donor values(3,'amol','mumbai');
	

insert into Blood_donation_detail values(1001,'A+',250,'02-aug-2016',1);
	
insert into Blood_donation_detail values(1002,'B+',250,'02-aug-2016',2);
	
insert into Blood_donation_detail values(1003,'AB+',250,'02-aug-2016',3);
	
insert into Blood_donation_detail values(1004,'A+',300,'04-jan-2016',1);
	
insert into Blood_donation_detail values(1005,'B+',250,'04-jan-2016',2);
	

Q1)
	
	
create or replace function f1(p_date in date)
	
     return number as
	
     p_amt number(10);
	
     begin
	
     select sum(qty) into p_amt from Blood_donation_detail  
	
     where date_of_collection=p_date and blood_group='A+';
	
     return p_amt;
	
     end;
	

declare
	
     p_date date;
	
     amt number(10);
	
     begin
	
     dbms_output.put_line('enter the date');
	
     p_date:='&p_date';
	
     amt:=f1(p_date);
	
     dbms_output.put_line('total collection is:='||amt);
	
     end;
	
Q2)
	
Declare
	
cursor c1 is select donor_name from Donor;
	
cursor c2(dname Donor.donor_name%type) is
	
select bd_no, blood_group, qty, date_of_collection
	
from Donor d,Blood_donation_detail bd
	
where d.donor_no=bd.donor_no
	
and donor_name=dname;
	
r1 c1%rowtype;
	
r2 c2%rowtype;
	
Begin
	
open c1;
	
loop
	
fetch c1 into r1;
	
exit when c1%notfound;
	
dbms_output.put_line(r1.donor_name);
	
open c2(r1.donor_name);
	
loop
	
fetch c2 into r2;
	
exit when c2%notfound;
	
dbms_output.put_line(r2.bd_no|| r2.blood_group || r2.qty || r2.date_of_collection);
	
end loop;
	
close c2;
	
end loop;
	
close c1;
	
end;

Search

Project Categories

Recent Posts

Sign In