Consider
the following entities and their relationship.
Researcher
(r_no, r_name, r_city)
Research_Paper
(rp_no, rp_title, rp_subject, rp_level)
Relationship
between Researcher and Research_Paper is many-to-many with descriptive
attribute year.
Constraints:
primary key, foreign key,r_name and rp_title should not be null,rp_subject can
be computer or electronics or finance.rp_level can be state, national or
international.
Create
a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write
a function which will return name of subject for which maximum numbers of
national level papers were presented by researcher in year 2013.
SQL>
set serveroutput on
SQL>
create or replace function fun1(ns in varchar) return varchar as nmofs varchar(15);
2
begin
3
select (rpsubject) into nmofs from researchpaper, researcherpaper where
researchpaper.rpno=researcherpaper.rpno and year=2012;
4 if
sql %found then
5
return(nmofs);
6 else
7
return null;
8 end
if;
9 end
;
10 /
Function
created.
SQL>
begin
2
dbms_output.put_line('subject is '||fun1('national'));
3 end;
4 /
subject
is finance
PL/SQL
procedure successfully completed.
2)Write
a cursor which will display rp_level wise and researcher wise details of
research paper presented by them.
SQL>
set serveroutput on
SQL>
declare
2
cursor c1(rwise researcher.rname%type)is select rptitle, rpsubject,
rplevel, rname from researcher, researchpaper, researcherpaper where
researcher.rno=researcherpaper.rno and researchpaper.rpno=researcherpaper.rpno
order by rplevel;
3 c
c1%rowtype;
4
begin
5 open
c1('&rwise');
6 loop
7
fetch c1 into c;
8 exit
when c1%notfound;
9
dbms_output.put_line(c.rname||' '||c.rptitle||' '||c.rpsubject||'
'||c.rplevel);
10 end
loop;
11
close c1;
12 end;
13 /
Enter
value for rwise: lokesh
old 5: open c1('&rwise');
new 5: open c1('lokesh');
lokesh
hardware computer international
PL/SQL
procedure successfully completed.
