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 procedure which will display blood group wise total amount of quantity of
blood available.
SQL>
set serveroutput on
SQL>
create or replace procedure p3(n in varchar) as cursor c1 is select qty,
bloodgroup from blooddonation order by bloodgroup;
2 c
c1%rowtype;
3
begin
4 open
c1;
5
dbms_output.put_line('bloodgroup'||' '||'qty');
6 loop
7
fetch c1 into c;
8 exit
when c1%notfound;
9
dbms_output.put_line(c.bloodgroup||' '||c.qty);
10 end
loop;
11
close c1;
12 end;
13 /
Procedure
created.
SQL>
begin
2
p3('bloodgroup');
3 end;
4 /
bloodgroup
qty
a-
3
ab+
3
b+
1
b+
1
o+
2
PL/SQL
procedure successfully completed.
2)Write
a trigger that restricts insertion of blood_donation_details having quantity
greater than 300ml. (Raise user defined exception and give appropriate message)
SQL>
set serveroutput on
SQL>
create or replace trigger t6 before insert or update on blooddonation
2 for
each row
3
begin
4
if(:new.qty>300)then
5
raise_application_error(-20001,'blood must be less than 300ml');
6 end
if;
7 end;
8 /
Trigger
created.
SQL>
insert into blooddonation values(6,'ab+',350,'31/11/2014',105);
insert
into blooddonation values(6,'ab+',350,'31/11/2014',105)
*
ERROR
at line 1:
ORA-20001:
blood must be less than 300ml
ORA-06512:
at "SCOTT.T6", line 3
ORA-04088:
error during execution of trigger 'SCOTT.T6'
