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 function which will return total number of farmers harvesting given crop in a
given year.
SQL>
Set serveroutput on
SQL>
Create or replace function fun(nof in varchar) return number as tf number;
2
Begin
3Select count(fname) into tf from crop, farmer, cropfarmer where
crop.cno=cropfarmer.cno and farmer.fno=cropfarmer.fno and cname='jowar';
4 If
sql %found then
5
Return(tf);
6 Else
7
Return null;
8 End
if;
9 End;
10 /
Function
created.
SQL>
begin
2 dbms_output.put_line('total
no of farmer-'||fun('2015'));
3 end;
4 /
total
no of farmer-1
PL/SQL
procedure successfully completed.
2)Write
a cursor which will display season wise information of crops harvested by the
farmers.(Use parameterized cursor)
SQL> set serveroutput on
SQL>
declare
2 cursor c1(swise crop.season%type)is select
season,cname,pesticides,fname from crop,farmer,cropfarmer where
crop.cno=cropfarmer.cno and farmer.fno=cropfarmer.fno order by season;
3 c
c1%rowtype;
4
begin
5 open
c1('&swise');
6 loop
7
fetch c1 into c;
8 exit
when c1%notfound;
9
dbms_output.put_line(c.season||' '||c.cname||' '||c.pesticides||'
'||c.fname);
10 end
loop;
11
close c1;
12 end;
13 /
Enter
value for swise: rabi
old 5: open c1('&swise');
new 5: open c1('rabi');
kharif
jowar jkl harsh
kharif
rice mno vishal
rabi potato pqr roshan
rabi
sugarcane abc lokesh
PL/SQL
procedure successfully completed.
