RDBMS 8 Slips
Q.1) Consider the following entities and their relationship. [Marks 40]
University (u_no, u_name, u_city)
College (c_no, c_name, c_city, year_of_establishment )
Relationship between University and College is one-to-many
Constraints: primary key, foreign key,
u_name and c_name should not be null.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1) Write a package, which consists of one procedure and one function. Pass university
number as a parameter to procedure and display details of that university. Pass city as
a parameter to a function and return total number of colleges in given city.
2) Write a cursor which will display university wise their college details. (Use
parameterized cursor)
3) Write a procedure which will display year wise details of colleges belongs to given
University.
4) Write a trigger that restricts insertion of college having year of establishment greater
than current year. (Raise user defined exception and give appropriate message)
create table univ02
(
u_no number(5) primary key,
u_name varchar2(20),
u_city varchar2(20)
);
create table clg03
(
c_no number(5) primary key,
c_name varchar2(20),
c_city varchar2(20),
year number(10),
u_no number(5) references univ02(u_no)
);
insert into univ02 values(1,'pune univsity','pune');
insert into univ02 values(2,'bharti univsity','pune');
insert into univ02 values(3,'mumbai univsity','mumbai');
insert into univ02 values(4,'delhi univsity','delhi');
insert into univ02 values(5,'bihar univsity','bihar');
insert into clg03 values(1011,'Abhinav E.M.S','pune','2009',1);
insert into clg03 values(1012,'Bharti Vidhyapeeth','pune' ,'2010',2);
insert into clg03 values(1013,'Mumbra High School','mumbai','2005',3);
insert into clg03 values(1014,'AISSMS college','pune','2002',4);
insert into clg03 values(1015,'Aryans school','pune','2010',5);
.....................................................................................................................................
Q.1) Write a package, which consists of one procedure and one function. Pass university
number as a parameter to procedure and display details of that university. Pass city as
a parameter to a function and return total number of colleges in given city.
PACKAGE
create or replace package uni_pack as
procedure puni(pno in number);
function funi(vcity in varchar2) return number;
end uni_pack;
/
create or replace package body uni_pack
as
procedure puni(pno in number) as
cursor c1 is select univ02.* from univ02,clg03
where univ02.u_no=clg03.u_no;
x c1%rowtype;
begin
for x in c1
loop
dbms_output.put_line(x.u_no||' '||x.u_name||' '||x.u_city);
end loop;
end puni;
function funi(vcity in varchar2)
return number
as
cnt number;
begin
select count(clg03.c_no) into cnt from univ02,clg03
where univ02.u_no=clg03.u_no;
if SQL%found then
return cnt;
else
return 0;
end if;
end funi;
end uni_pack;
/
Package body created.
SQL> select uni_pack.funi('pune') from dual;
UNI_PACK.FUNI('PUNE')
---------------------
5
Package body created.
SQL> execute uni_pack.puni(1);
1 pune univ02sity pune
2 bharti univ02sity pune
3 mumbai univ02sity mumbai
4 delhi univ02sity delhi
5 bihar univ02sity bihar
PL/SQL procedure successfully complet
.....................................................................................................................................
Q.2) Write a cursor which will display university wise their college details. (Use
parameterized cursor)
CURSOR
declare
cursor c8(v_name varchar2) is select clg03.* from univ02,clg03
where univ02.u_no=clg03.u_no and
univ02.u_name=v_name;
vno clg03.c_no%type;
vname clg03.c_name%type;
vcity clg03.c_city%type;
vyear clg03.year%type;
vuno univ02.u_no%type;
c_name univ02.u_name%type;
begin
c_name:=&c_name;
open c8(c_name);
loop
fetch c8 into vno,vname,vcity,vyear,vuno;
exit when c8%notfound;
dbms_output.put_line(vno||' '||vname||' '||vcity||' '||vyear||' '||vuno);
end loop;
close c8;
end;
/
21 /
Enter value for c_name: 'pune univsity'
old 12: c_name:=&c_name;
new 12: c_name:='pune univsity';
1011 Abhinav E.M.S pune 2006 1
PL/SQL procedure successfully completed.
SQL>
.........................................................................................................................................................................................................................
....................................................................................................................................
Q.3) Write a procedure which will display year wise details of colleges belongs to given
University.
PROCEDURE
create or replace procedure p8
(yr in number)
as
cursor c8 is select u_name,clg03.* from univ02,clg03
where univ02.u_no=clg03.u_no and
clg03.year='2010';
x c8%rowtype;
begin
for x in c8
loop
dbms_output.put_line(x.u_name||' '||x.c_no||' '||x.c_name||' '||x.c_city||' '||x.year||' '||x.u_no);
end loop;
end p8;
/
Procedure created.
SQL> execute p8('2009','pune univ02sity');
PL/SQL procedure successfully completed.
....................................................................................................................................
Q.4) Write a trigger that restricts insertion of college having year of establishment greater
than current year. (Raise user defined exception and give appropriate message)
TRIGGER
create or replace trigger t30
before
insert on clg03
for each row
declare
a varchar(20);
b varchar(20);
begin
a:=to_char(:new.year,'yyyy');
b:=to_char(sysdate,'yyyy');
if(a>b) then
raise_application_error('-20010','college having year of etsablisment is greater than current year');
end if;
end t30;
/
SQL> insert into colg values(1011,'Abhinav E.M.S','pune',2007,1);
insert into colg values(1011,'Abhinav E.M.S','pune',2007,1)
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "HR.T20", line 5
ORA-04088: error during execution of trigger 'HR.T20'
Tags:
RDBMS