Consider
the following entities and their relationship.
Crop
(c_no, c_name, c_season, pesticides)
Farmer
(f_no, f_name, f_location)
Relationship
between Crop and Farmer is many-to-many with descriptive attribute year.
Constraints:primary
key, foreign key,primary key for third table(c_no, f_no, year),c_name and
f_name should not be null,c_season can be rabi or kharif.
Create
a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write
a procedure which will display crop detail harvested by given farmer in given
year.
SQL>
set serveroutput on
SQL>
create or replace procedure p1(n in varchar) as cursor c1 is select cname,
season, pesticides, fname, year from crop, farmer, cropfarmer where crop.cno=cropfarmer.cno
and farmer.fno=cropfarmer.fno and year='14 apr 2015';
2 c
c1%rowtype;
3
begin
4 open
c1;
5 dbms_output.put_line('cname'||'
'||'season'||' '||'pesticides');
6 loop
7
fetch c1 into c;
8 exit
when c1%notfound;
9
if(c.fname=n) then
10
dbms_output.put_line(c.cname||' '||c. season ||' '||c.pesticides);
11 end
if;
12 end
loop;
13
close c1;
14 end;
15 /
Procedure
created.
SQL>
begin
2
p1('harsh');
3 end;
4 /
cname
season pesticides
jowar
kharif jkl
PL/SQL
procedure successfully completed.
2)Write
a trigger which will restricts insertion or updation of crop_farmer table
having year greater than current year. (Raise user defined exception and give
appropriate message)
SQL>
set serveroutput on
SQL>
create or replace trigger t4 before insert or update on cropfarmer
2 for
each row
3
declare
4 d1
varchar(10);
5 d2
varchar(10);
6
begin
7
d1:=to_char(:new.year,'yyyy');
8
d2:=to_char(sysdate,'yyyy');
9
if(d1>d2) then
10
raise_application_error(-20001,'year should be less than current
date.');
11 end
if;
12 end;
13 /
Trigger
created.
SQL>
insert into cropfarmer values(106,6, '31 oct 2016');
insert
into cropfarmer values(106,6, '31 oct 2016')
*
ERROR
at line 1:
ORA-20001:
year should be less than current date.
ORA-06512:
at "SCOTT.T4", line 8
ORA-04088:
error during execution of trigger 'SCOTT.T4'
